Cluster ratio? Cluster factor?

MS SQL Server 2000
let's say there is a table ORDERS with a clustered index on
(order_date, some other column). Also there is a non-clustered index on
shipment_date. Since most orders are shipped within 3 business days,
the data is stored almost ordered by shipment_date.
Most rows for the same shipment date are stored on adjacent data pages.
There is another index on zipcode, which does not correlate with order
date at all. Is there anything I can read from system views to tell the
difference? In Oracle/DB2 I can read cluster factor/cluster ratio.
TIAHi
You probably need DBCC SHOWCONTIG, you can check out the documentation in
Books online. If you want more information on this sort of thing you may als
o
want to read "Inside SQL Server 2000" by Kalen Delaney ISBN 0-7356-0998-5 an
d
Ken Henderson's "The Guru's guide to SQL Server Architecture and
Internals" ISBN 0-201-7004706
John
"ford_desperado@.yahoo.com" wrote:

> MS SQL Server 2000
> let's say there is a table ORDERS with a clustered index on
> (order_date, some other column). Also there is a non-clustered index on
> shipment_date. Since most orders are shipped within 3 business days,
> the data is stored almost ordered by shipment_date.
> Most rows for the same shipment date are stored on adjacent data pages.
> There is another index on zipcode, which does not correlate with order
> date at all. Is there anything I can read from system views to tell the
> difference? In Oracle/DB2 I can read cluster factor/cluster ratio.
> TIA
>

CLR TVF -> Using Datareader...

Hi
I'm trying to create a CLR-TVF which should do some stuff (in my sample it's
just getting the syscolumns name column for the database _ODS).
I’ve got this error:
An error occurred while getting new row from user defined Table Valued
Function :
System.InvalidOperationException: Data access is not allowed in this
context. Either the context is a function or method not marked with
DataAccessKind.Read or SystemDataAccessKind.Read, is a callback to obtain
data from FillRow method of a Table Valued Function, or is a UDT validation
method.
Question: Is it really not possible to make a sql-query within the C#-Part
of the CLR? I know, I could easily do it using a StoredProc…but I need a T
VF.
Thanks for your help…
Here is the code:
[Microsoft.SqlServer.Server.SqlFunction
(FillRowMethodName = "FillRow2",
DataAccess = DataAccessKind.Read,
TableDefinition = "fld_colname NVARCHAR(4000)" )]
public static IEnumerable LoadSysColumns(string str2)
{
return str2;
}
public static void FillRow2(object row,
out string str2)
{
// creating a connection using the current sqlserver-context
using (SqlConnection connection = new SqlConnection("context
connection=true"))
{
connection.Open();
SqlCommand sqlCommand = connection.CreateCommand();
sqlCommand.CommandText = "select NAME from _ODS.dbo.SYSCOLUMNS";
SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
sqlDataReader.Read();
str2 = sqlDataReader.GetValue(0).ToString();
}
}"Dominic" <Dominic@.discussions.microsoft.com> wrote in message
news:7638A0AE-0976-4DAC-8512-93FB84C048A4@.microsoft.com...
> Hi
> I'm trying to create a CLR-TVF which should do some stuff (in my sample
> it's
> just getting the syscolumns name column for the database _ODS).
> I've got this error:
> An error occurred while getting new row from user defined Table Valued
> Function :
> System.InvalidOperationException: Data access is not allowed in this
> context. Either the context is a function or method not marked with
> DataAccessKind.Read or SystemDataAccessKind.Read, is a callback to obtain
> data from FillRow method of a Table Valued Function, or is a UDT
> validation
> method.
> Question: Is it really not possible to make a sql-query within the C#-Part
> of the CLR? I know, I could easily do it using a StoredProc.but I need a
> TVF.
> Thanks for your help.
> Here is the code:
> [Microsoft.SqlServer.Server.SqlFunction
> (FillRowMethodName = "FillRow2",
> DataAccess = DataAccessKind.Read,
> TableDefinition = "fld_colname NVARCHAR(4000)" )]
> public static IEnumerable LoadSysColumns(string str2)
> {
> return str2;
> }
> public static void FillRow2(object row,
> out string str2)
> {
> // creating a connection using the current sqlserver-context
> using (SqlConnection connection = new SqlConnection("context
> connection=true"))
> {
> connection.Open();
> SqlCommand sqlCommand = connection.CreateCommand();
> sqlCommand.CommandText = "select NAME from
> _ODS.dbo.SYSCOLUMNS";
> SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
> sqlDataReader.Read();
> str2 = sqlDataReader.GetValue(0).ToString();
> }
> }
>
Any data access should be in the LoadSysColumns method, not in the
FillRowMethod. In LoadSysColumns just fill a List<string> and return that.
SQL will enumerate it and pass each member to your FillRowMethod, EG:
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Collections;
using System.Collections.Generic;
public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction
(FillRowMethodName = "FillRow2",
DataAccess = DataAccessKind.None,
SystemDataAccess = SystemDataAccessKind.Read,
TableDefinition = "fld_colname NVARCHAR(4000)")]
public static IEnumerable LoadSysColumns()
{
List<string> names = new List<string>();
using (SqlConnection connection = new SqlConnection("context
connection=true"))
{
connection.Open();
SqlCommand sqlCommand = connection.CreateCommand();
sqlCommand.CommandText = "select NAME from dbo.SYSCOLUMNS";
using (SqlDataReader sqlDataReader = sqlCommand.ExecuteReader())
while (sqlDataReader.Read())
{
names.Add(sqlDataReader.GetValue(0).ToString());
}
}
return names;
}
public static void FillRow2(object row, out string str2)
{
str2 = (string)row;
}
};
David|||Thanks David...works fine!
"David Browne" wrote:

> "Dominic" <Dominic@.discussions.microsoft.com> wrote in message
> news:7638A0AE-0976-4DAC-8512-93FB84C048A4@.microsoft.com...
> Any data access should be in the LoadSysColumns method, not in the
> FillRowMethod. In LoadSysColumns just fill a List<string> and return that
.
> SQL will enumerate it and pass each member to your FillRowMethod, EG:
>
> using System;
> using System.Data;
> using System.Data.SqlClient;
> using System.Data.SqlTypes;
> using Microsoft.SqlServer.Server;
> using System.Collections;
> using System.Collections.Generic;
> public partial class UserDefinedFunctions
> {
> [Microsoft.SqlServer.Server.SqlFunction
> (FillRowMethodName = "FillRow2",
> DataAccess = DataAccessKind.None,
> SystemDataAccess = SystemDataAccessKind.Read,
> TableDefinition = "fld_colname NVARCHAR(4000)")]
> public static IEnumerable LoadSysColumns()
> {
> List<string> names = new List<string>();
> using (SqlConnection connection = new SqlConnection("context
> connection=true"))
> {
> connection.Open();
> SqlCommand sqlCommand = connection.CreateCommand();
> sqlCommand.CommandText = "select NAME from dbo.SYSCOLUMNS";
> using (SqlDataReader sqlDataReader = sqlCommand.ExecuteReader())
> while (sqlDataReader.Read())
> {
> names.Add(sqlDataReader.GetValue(0).ToString());
> }
> }
> return names;
> }
> public static void FillRow2(object row, out string str2)
> {
> str2 = (string)row;
> }
> };
>
> David
>
>

CLR Table Value Function Insert Into Table Variable

I have a simple clr tvf that splits a string and returns a two column table. When I try and insert the results of this tvf into another table variable I get the error below. Can anyone help me on this one? What a huge letdown if clr tvf cannot be insert into table variables. Inserting into a temp table works fine.

-- BTW I am on the September CTP with VS 2005 RC

Thanks,
Adam

Error Message

Msg 8624, Level 16, State 1, Line 2

Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services.

Below is the SQL I am using to test

declare @.t table(a int, b nvarchar(128))
insert into @.t
select * from dbo.Split('Hello,GoodBye', ',', 1)

Function Definition

CREATE FUNCTION [dbo].[Split](@.value [nvarchar](4000), @.seperator [nvarchar](32) = N',', @.removeEmptyEntries [bit] = 1)
RETURNS TABLE ([Position] [int] NULL,[Value] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL) WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [Sit.Sql.Cdw].[Sit.Sql.Cdw.Split].[InitMethod]
GO

Source Code

using System;
using System.Data.Sql;
using Microsoft.SqlServer.Server;
using System.Collections;
using System.Data.SqlTypes;

namespace Sit.Sql.Cdw
{
public class Split
{
public struct Splitter
{
public int pos;
public String value;
}

[SqlFunction(FillRowMethodName= "FillRow", Name="Split", TableDefinition="Position int, Value nvarchar(1000)")]
public static IEnumerable InitMethod(String value, String seperator, bool removeEmptyEntries)
{
Splitter[] sVals;
String[] vals = value.Split( new string[1] { seperator }, (removeEmptyEntries) ?
StringSplitOptions.RemoveEmptyEntries : StringSplitOptions.None);
sVals = new Splitter[vals.Length];
for(int i = 0; i < vals.Length; i++)
{
sVals.pos = i + 1;
sVals.value = vals;
}
return sVals;
}

public static void FillRow(Object obj, out SqlInt32 Position, out SqlChars Value)
{
Splitter s = (Splitter)obj;
Position = new SqlInt32(s.pos);
Value = new SqlChars(s.value);
}
}
}

I just realized my mistake after posting...
I originally called the function with default parameters dbo.Split('Hello,Goodbye', default, default) which produces the error. Substituting the default parameter with an actual value runs fine.

Closest in Column

What SQL statement do I use to find what is the closest value in a data
set to the value I have.
For example I need to search in a column for the closest value that
exists to what I have:
E.g. my database has
X
1.2
1.3
2.6
1.0
2.5
1.4
1.7
I have 1.5 so I need to a query that return 1.4.
Any help would be appreciated
Thanks
TarryThis may not be very efficient unless you have an index on the column
X, but...
Could you query for 2 numbers, for a given SearchNumber to search for:
A. Largest number smaller than SearchNumber
B. Smallest number larger than SearchNumber
And then select whichever is closer to SearchNumber, A or B.
Basically:
SELECT
CASE
WHEN (@.SearchNumber - T.Smaller < T.Larger - @.SearchNumber) THEN
T.Smaller
ELSE T.Larger
END
FROM
(
SELECT MAX(T1.X) AS Smaller, MIN(T2.X) AS Larger
FROM MyTable T1
FULL OUTER JOIN MyTable T2 ON T1.X = T2.X
WHERE T1.X <= @.SearchNumber AND T2.X >= @.SearchNumber
) T|||Lubdha Khandelwal wrote:
> This may not be very efficient unless you have an index on the column
> X, but...
> Could you query for 2 numbers, for a given SearchNumber to search for:
> A. Largest number smaller than SearchNumber
> B. Smallest number larger than SearchNumber
> And then select whichever is closer to SearchNumber, A or B.
> Basically:
> SELECT
> CASE
> WHEN (@.SearchNumber - T.Smaller < T.Larger - @.SearchNumber) THEN
> T.Smaller
> ELSE T.Larger
> END
> FROM
> (
> SELECT MAX(T1.X) AS Smaller, MIN(T2.X) AS Larger
> FROM MyTable T1
> FULL OUTER JOIN MyTable T2 ON T1.X = T2.X
> WHERE T1.X <= @.SearchNumber AND T2.X >= @.SearchNumber
> ) T
Lubha
I think we should remove <= from the query and join condition also
should be changed.
it should be
SELECT
CASE
WHEN (@.SearchNumber - T.Smaller < T.Larger - @.SearchNumber) THEN
T.Smaller
ELSE T.Larger
END
FROM
(
SELECT MAX(T1.X) AS Smaller, MIN(T2.X) AS Larger
FROM MyTable T1
FULL OUTER JOIN MyTable T2 ON T1.X < T2.X
WHERE T1.X < @.SearchNumber AND T2.X > @.SearchNumber
) T
Or
SELECT @.SearchNumber - T.Smaller,T.larger - @.SearchNumber,
CASE
WHEN (@.SearchNumber - T.Smaller < T.larger - @.SearchNumber ) THEN
T.Smaller
ELSE T.Larger
END
FROM
(
select (select max(x) from mytable where x < @.searchnumber) smaller,
(select min(x) from mytable where x > @.searchnumber) larger
) T
Regards
Amish Shah|||> SELECT
> CASE
> WHEN (@.SearchNumber - T.Smaller < T.Larger - @.SearchNumber) THEN
> T.Smaller
> ELSE T.Larger
> END
> FROM
> (
> SELECT MAX(T1.X) AS Smaller, MIN(T2.X) AS Larger
> FROM MyTable T1
> FULL OUTER JOIN MyTable T2 ON T1.X < T2.X
> WHERE T1.X < @.SearchNumber AND T2.X > @.SearchNumber
> ) T
>
But then you're not checking for equality. What if @.SearchNumber itself
exists in column X, shouldn't it return @.SearchNumber?|||Lubdha Khandelwal wrote:
> > SELECT
> > CASE
> > WHEN (@.SearchNumber - T.Smaller < T.Larger - @.SearchNumber) THEN
> > T.Smaller
> > ELSE T.Larger
> > END
> > FROM
> > (
> > SELECT MAX(T1.X) AS Smaller, MIN(T2.X) AS Larger
> > FROM MyTable T1
> > FULL OUTER JOIN MyTable T2 ON T1.X < T2.X
> > WHERE T1.X < @.SearchNumber AND T2.X > @.SearchNumber
> > ) T
> >
>
> But then you're not checking for equality. What if @.SearchNumber itself
> exists in column X, shouldn't it return @.SearchNumber?
Ok , but for join also you should join it on < not on =
Here is gives null when using = for joins.
create table mytable(x decimal(10,2))
insert into mytable values(1)
insert into mytable values(2)
insert into mytable values(3)
insert into mytable values(4)
insert into mytable values(5)
declare @.searchnumber decimal(10,2)
set @.searchnumber = 2.5
SELECT t.smaller, t.larger,
CASE
WHEN (@.SearchNumber - T.Smaller < T.Larger - @.SearchNumber) THEN
T.Smaller
ELSE T.Larger
END
FROM
(
SELECT MAX(T1.X) AS Smaller, MIN(T2.X) AS Larger
FROM MyTable T1
FULL OUTER JOIN MyTable T2 ON T1.X = T2.X
WHERE T1.X <= @.SearchNumber AND T2.X >= @.SearchNumber
) T
Regards
Amish Shah|||CREATE TABLE #temp (
Value NUMERIC(8, 2)
)
INSERT INTO #temp (Value) VALUES ( 1.2 )
INSERT INTO #temp (Value) VALUES ( 1.3 )
INSERT INTO #temp (Value) VALUES ( 2.6 )
INSERT INTO #temp (Value) VALUES ( 1.0 )
INSERT INTO #temp (Value) VALUES ( 2.5 )
INSERT INTO #temp (Value) VALUES ( 1.4 )
INSERT INTO #temp (Value) VALUES ( 1.7 )
DECLARE @.Target NUMERIC(8, 2)
SELECT @.Target = 1.5
SELECT *
FROM #temp
WHERE CAST(ABS(Value - @.Target) AS NUMERIC(8, 2)) = (SELECT
MIN(CAST(ABS(Value - @.Target) AS NUMERIC(8, 2))) FROM #temp)|||Actually, I came across another way to get the closest in a column...
SELECT TOP 1 *
FROM MyTable
ORDER BY ABS(X - @.SearchNumber) ASC|||Lubdha Khandelwal wrote:
> Actually, I came across another way to get the closest in a column...
> SELECT TOP 1 *
> FROM MyTable
> ORDER BY ABS(X - @.SearchNumber) ASC
Very nice! Painfully simple! Made me slap my forehead twice...

Closest in Column

What SQL statement do I use to find what is the closest value in a data
set to the value I have.
For example I need to search in a column for the closest value that
exists to what I have:
E.g. my database has
X
1.2
1.3
2.6
1.0
2.5
1.4
1.7
I have 1.5 so I need to a query that return 1.4.
Any help would be appreciated
Thanks
TarryThis may not be very efficient unless you have an index on the column
X, but...
Could you query for 2 numbers, for a given SearchNumber to search for:
A. Largest number smaller than SearchNumber
B. Smallest number larger than SearchNumber
And then select whichever is closer to SearchNumber, A or B.
Basically:
SELECT
CASE
WHEN (@.SearchNumber - T.Smaller < T.Larger - @.SearchNumber) THEN
T.Smaller
ELSE T.Larger
END
FROM
(
SELECT MAX(T1.X) AS Smaller, MIN(T2.X) AS Larger
FROM MyTable T1
FULL OUTER JOIN MyTable T2 ON T1.X = T2.X
WHERE T1.X <= @.SearchNumber AND T2.X >= @.SearchNumber
) T|||Lubdha Khandelwal wrote:

> This may not be very efficient unless you have an index on the column
> X, but...
> Could you query for 2 numbers, for a given SearchNumber to search for:
> A. Largest number smaller than SearchNumber
> B. Smallest number larger than SearchNumber
> And then select whichever is closer to SearchNumber, A or B.
> Basically:
> SELECT
> CASE
> WHEN (@.SearchNumber - T.Smaller < T.Larger - @.SearchNumber) THEN
> T.Smaller
> ELSE T.Larger
> END
> FROM
> (
> SELECT MAX(T1.X) AS Smaller, MIN(T2.X) AS Larger
> FROM MyTable T1
> FULL OUTER JOIN MyTable T2 ON T1.X = T2.X
> WHERE T1.X <= @.SearchNumber AND T2.X >= @.SearchNumber
> ) T
Lubha
I think we should remove <= from the query and join condition also
should be changed.
it should be
SELECT
CASE
WHEN (@.SearchNumber - T.Smaller < T.Larger - @.SearchNumber) THEN
T.Smaller
ELSE T.Larger
END
FROM
(
SELECT MAX(T1.X) AS Smaller, MIN(T2.X) AS Larger
FROM MyTable T1
FULL OUTER JOIN MyTable T2 ON T1.X < T2.X
WHERE T1.X < @.SearchNumber AND T2.X > @.SearchNumber
) T
Or
SELECT @.SearchNumber - T.Smaller,T.larger - @.SearchNumber,
CASE
WHEN (@.SearchNumber - T.Smaller < T.larger - @.SearchNumber ) THEN
T.Smaller
ELSE T.Larger
END
FROM
(
select (select max(x) from mytable where x < @.searchnumber) smaller,
(select min(x) from mytable where x > @.searchnumber) larger
) T
Regards
Amish Shah|||
> SELECT
> CASE
> WHEN (@.SearchNumber - T.Smaller < T.Larger - @.SearchNumber) THEN
> T.Smaller
> ELSE T.Larger
> END
> FROM
> (
> SELECT MAX(T1.X) AS Smaller, MIN(T2.X) AS Larger
> FROM MyTable T1
> FULL OUTER JOIN MyTable T2 ON T1.X < T2.X
> WHERE T1.X < @.SearchNumber AND T2.X > @.SearchNumber
> ) T
>
But then you're not checking for equality. What if @.SearchNumber itself
exists in column X, shouldn't it return @.SearchNumber?|||Lubdha Khandelwal wrote:

>
> But then you're not checking for equality. What if @.SearchNumber itself
> exists in column X, shouldn't it return @.SearchNumber?
Ok , but for join also you should join it on < not on =
Here is gives null when using = for joins.
create table mytable(x decimal(10,2))
insert into mytable values(1)
insert into mytable values(2)
insert into mytable values(3)
insert into mytable values(4)
insert into mytable values(5)
declare @.searchnumber decimal(10,2)
set @.searchnumber = 2.5
SELECT t.smaller, t.larger,
CASE
WHEN (@.SearchNumber - T.Smaller < T.Larger - @.SearchNumber) THEN
T.Smaller
ELSE T.Larger
END
FROM
(
SELECT MAX(T1.X) AS Smaller, MIN(T2.X) AS Larger
FROM MyTable T1
FULL OUTER JOIN MyTable T2 ON T1.X = T2.X
WHERE T1.X <= @.SearchNumber AND T2.X >= @.SearchNumber
) T
Regards
Amish Shah|||CREATE TABLE #temp (
Value NUMERIC(8, 2)
)
INSERT INTO #temp (Value) VALUES ( 1.2 )
INSERT INTO #temp (Value) VALUES ( 1.3 )
INSERT INTO #temp (Value) VALUES ( 2.6 )
INSERT INTO #temp (Value) VALUES ( 1.0 )
INSERT INTO #temp (Value) VALUES ( 2.5 )
INSERT INTO #temp (Value) VALUES ( 1.4 )
INSERT INTO #temp (Value) VALUES ( 1.7 )
DECLARE @.Target NUMERIC(8, 2)
SELECT @.Target = 1.5
SELECT *
FROM #temp
WHERE CAST(ABS(Value - @.Target) AS NUMERIC(8, 2)) = (SELECT
MIN(CAST(ABS(Value - @.Target) AS NUMERIC(8, 2))) FROM #temp)|||Actually, I came across another way to get the closest in a column...
SELECT TOP 1 *
FROM MyTable
ORDER BY ABS(X - @.SearchNumber) ASC|||Lubdha Khandelwal wrote:
> Actually, I came across another way to get the closest in a column...
> SELECT TOP 1 *
> FROM MyTable
> ORDER BY ABS(X - @.SearchNumber) ASC
Very nice! Painfully simple! Made me slap my forehead twice...

Click to Sort Column Headings

Is there a table property that allows for this? or Do I have to build that
into my query and requery everytime I want to sort? Can someone provide an
example?Rich,
Thanks for the response. I don't see the attached project. Can you post
the link to where you found the project or provide a detailed description on
how to do it (please be as descript as possible...I am noob)?
Thanks!
"Rich Millman" wrote:
> No, there is no table property for this.
> It is not difficult.
> First thing to understand is that you don't sort in your query, you let RS
> do the sorting.
> Then in your column headings you provide actions to open your report,
> passing in parameters that control the sorting.
> Attached is a project that you can use as a sample. I didn't write it, but
> it helped me.
>
> "Neo" <Neo@.discussions.microsoft.com> wrote in message
> news:3FC1A47E-4D46-4604-981D-01DE508D134B@.microsoft.com...
> > Is there a table property that allows for this? or Do I have to build
> > that
> > into my query and requery everytime I want to sort? Can someone provide
> > an
> > example?
>
>|||If you are using Outlook express a paper clip shows the attachment. The
posting did have an attachment with it.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Neo" <Neo@.discussions.microsoft.com> wrote in message
news:B964369A-A97D-4C66-AE47-F6F670051B74@.microsoft.com...
> Rich,
> Thanks for the response. I don't see the attached project. Can you post
> the link to where you found the project or provide a detailed description
on
> how to do it (please be as descript as possible...I am noob)?
> Thanks!
> "Rich Millman" wrote:
> > No, there is no table property for this.
> > It is not difficult.
> > First thing to understand is that you don't sort in your query, you let
RS
> > do the sorting.
> > Then in your column headings you provide actions to open your report,
> > passing in parameters that control the sorting.
> >
> > Attached is a project that you can use as a sample. I didn't write it,
but
> > it helped me.
> >
> >
> > "Neo" <Neo@.discussions.microsoft.com> wrote in message
> > news:3FC1A47E-4D46-4604-981D-01DE508D134B@.microsoft.com...
> > > Is there a table property that allows for this? or Do I have to build
> > > that
> > > into my query and requery everytime I want to sort? Can someone
provide
> > > an
> > > example?
> >
> >
> >|||Sorry, I was using the Webbased MS Technet. I opened up the newsgroup with
newsgroup viewer and now have the file.
Thanks!
"Bruce L-C [MVP]" wrote:
> If you are using Outlook express a paper clip shows the attachment. The
> posting did have an attachment with it.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Neo" <Neo@.discussions.microsoft.com> wrote in message
> news:B964369A-A97D-4C66-AE47-F6F670051B74@.microsoft.com...
> > Rich,
> >
> > Thanks for the response. I don't see the attached project. Can you post
> > the link to where you found the project or provide a detailed description
> on
> > how to do it (please be as descript as possible...I am noob)?
> >
> > Thanks!
> >
> > "Rich Millman" wrote:
> >
> > > No, there is no table property for this.
> > > It is not difficult.
> > > First thing to understand is that you don't sort in your query, you let
> RS
> > > do the sorting.
> > > Then in your column headings you provide actions to open your report,
> > > passing in parameters that control the sorting.
> > >
> > > Attached is a project that you can use as a sample. I didn't write it,
> but
> > > it helped me.
> > >
> > >
> > > "Neo" <Neo@.discussions.microsoft.com> wrote in message
> > > news:3FC1A47E-4D46-4604-981D-01DE508D134B@.microsoft.com...
> > > > Is there a table property that allows for this? or Do I have to build
> > > > that
> > > > into my query and requery everytime I want to sort? Can someone
> provide
> > > > an
> > > > example?
> > >
> > >
> > >
>
>

Cleartext - > cipher text. Field lengths?

Suppose I store cleartext strings in a field declared as varchar(100). Is there any way to know the minimum varbinary column sze to use for the encrypted data? (e.g. should it be varbinary(100) or (200)?, (8000)?). I'm sure it's algorithm specific but I don't know what factors influence the final length.

TIA,

Barkingdog

For SQL Server 2005 you can find the information in the following article “SQL Server 2005 Encryption – Encryption and data length limitations” (http://blogs.msdn.com/yukondoit/archive/2005/11/24/496521.aspx).

As I described in the article, the formula to estimate the ciphertext length is based on the current implementation overhead (headers, key GUID & padding). I would personally recommend having some extra room (1-2 blocks) in case the implementation changes in future releases or in case your plaintext grows enough overtime to require 1 more block of space.

-Raul Garcia

SDE/T

SQL Server Engine

clearing values from a column

hey all,
how would i clear values for a column for all rows but not delete the row?
thanks,
rodcharHi,
Use Update command and set the column value with null
UPDATE TABLE
SET COL1 = NULL
Thanks
Hari
SQL Server MVP
"rodchar" wrote:

> hey all,
> how would i clear values for a column for all rows but not delete the row?
> thanks,
> rodchar|||"Clear" meaning what' You have to decide, for each datatype (for each
Column) what value will mean "Cleared" For a chacacter type column that's
not hard, Just an Empty String, I guess makes the most sense, but for
numeric or date columns, it's bit harder, The value you choose may be a
meaningful value in rows that are not "Cleared"
If the Columns allow Nulls, you could use null, but that's not really what
null iod supposed to be for, (it means UNKNOWN) so I wouldn;t recommend that
,
(UNless you MEAN Unknown when you say "CLeared")
"rodchar" wrote:

> hey all,
> how would i clear values for a column for all rows but not delete the row?
> thanks,
> rodchar|||You can do:
--if column is defined as nullable
update tb
set col=null
-oj
"rodchar" <rodchar@.discussions.microsoft.com> wrote in message
news:D8369B7B-3634-4A8B-88FC-23D948D18D53@.microsoft.com...
> hey all,
> how would i clear values for a column for all rows but not delete the row?
> thanks,
> rodchar