DEFAULT keyword performance

I have a function which performs a query and returns a table. The one
parameter that can get passed in is a date which defaults to NULL.
There is an IF statement in the function that will set the paramter to
an actual date if null. If I call the function while passing in a date
the function comes back a second or 2 later. But if I pass in DEFAULT
to the function, the same query takes 8 minutes. See code below and
sample call below.

CREATE FUNCTION fCalculateProfitLossFromClearing (
@.TradeDate DATETIME = NULL
)
RETURNS @.t TABLE (
[TradeDate] DATETIME,
[Symbol] VARCHAR(15),
[Identity] VARCHAR(15),
[Exchange] VARCHAR(5),
[Account] VARCHAR(10),
[Value] DECIMAL(18, 6)
)
AS
BEGIN
-- Use previous trading date if none specified
IF @.TradeDate IS NULL
SET @.TradeDate = Supporting.dbo.GetPreviousTradeDate()

-- Make the query
INSERT @.t
SELECT
@.TradeDate,
tblTrade.[Symbol],
tblTrade.[Identity],
tblTrade.[Exchange],
tblTrade.[Account],
SUM((CASE tblTrade.[Side] WHEN 'B' THEN -ABS(tblTrade.[Quantity])
ELSE ABS(tblTrade.[Quantity]) END) * (tblPos.[ClosingPrice] -
tblTrade.[Price])) AS [Value]
FROM
Historical.dbo.ClearingTrade tblTrade
LEFT JOIN Historical.dbo.ClearingPosition tblPos ON (@.TradeDate =
tblPos.[TradeDate] AND tblTrade.[Symbol] = tblPos.[Symbol] AND
tblTrade.[Identity] = tblPos.[Identity])
WHERE
([TradeTimestamp] >= @.TradeDate AND [TradeTimestamp] < DATEADD(DAY,
1, @.TradeDate))
GROUP BY tblTrade.[Symbol],tblTrade.[Identity],tblTrade.[Exchange],tblTrade.[Account]

RETURN
END

If I call the function as

SELECT * FROM fCalculateProfitLossFromClearing('09/25/2003')

it returns in 2 seconds.

If I call the function as

SELECT * FROM fCalculateProfitLossFromClearing(DEFAULT)

in which GetPreviousTradeDate() will set @.TradeDate to 09/25/2003 it
returns in 8 minutes.[posted and mailed, please reply in news]

Jason (JayCallas@.hotmail.com) writes:
> I have a function which performs a query and returns a table. The one
> parameter that can get passed in is a date which defaults to NULL.
> There is an IF statement in the function that will set the paramter to
> an actual date if null. If I call the function while passing in a date
> the function comes back a second or 2 later. But if I pass in DEFAULT
> to the function, the same query takes 8 minutes. See code below and
> sample call below.

The query seems familiar. :-)

The reason for this is that when SQL Server builds the query plan,
it considers the value of the input parameter. When you provide an
explicit date, SQL Server can consult the statistics for the table
and see that the value you provided is very selective, and use the
index.

But if you provide NULL, SQL Server will build the query plan on that
assumption. Obviously a NULL value would return no rows, but SQL Server
never makes any assumptions that could yield incorrect results. Since
you WHERE condition is for a range, SQL Server appears to prefer to
scan the table, than using a non-clustered index.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||>
> The query seems familiar. :-)
> The reason for this is that when SQL Server builds the query plan,
> it considers the value of the input parameter. When you provide an
> explicit date, SQL Server can consult the statistics for the table
> and see that the value you provided is very selective, and use the
> index.
> But if you provide NULL, SQL Server will build the query plan on that
> assumption. Obviously a NULL value would return no rows, but SQL Server
> never makes any assumptions that could yield incorrect results. Since
> you WHERE condition is for a range, SQL Server appears to prefer to
> scan the table, than using a non-clustered index.

I hate the restart this thread but I have hit a brick wall...

I am at a lose of whether to creat functions or stored procedures (or
even views).

The below questions/issues are based on a need to return information
on a particular date for one to many symbols.

For my example lets say 15 symbols. You could do a query like Symbol =
'a' OR Symbol = 'b' OR Symbol... but it would make more sense to do
Symbol IN ('a','b',...))

I would also like to give my functions and stored procedures to use a
default date if none is specified. I created a function named
GetPreviousTradeDate() which does this based on a calendar.

SO here is how I see it.

Stored procedures seem to be the fastest in terms of returning data
back. But they seem to be limited in the sense that they can return
ONE row or ALL the rows since you cannot pass in a variable list of
symbols. You also cannot use the SP as part of a query. You could just
return all the rows back to the client and do filter or searching on
that end but that does not seem efficient or professional.

A function also does not let you pass in a variable list of symbols
but at least you can use it in a query. You could do something like
SELECT * FROM TheFunction() WHERE Symbol IN ('a','b',...). All this
happens at the server side and only the needed rows gets sent back.

But functions seem to have MAJOR performance problems when default
values are passed in. When I pass in a specific date the query takes a
few seconds but when I pass in DEFAULT and set the date to the results
of the GetPreviousTradeDate() function the query takes anywhere from 8
minutes to 15 minutes. (This even happens if I do not use the
GetPreviousTradeDate() function and set my parameter to a hard-coded
value)

Any thoughts or comments would be appreciated.|||Jason (JayCallas@.hotmail.com) writes:
> A function also does not let you pass in a variable list of symbols
> but at least you can use it in a query. You could do something like
> SELECT * FROM TheFunction() WHERE Symbol IN ('a','b',...). All this
> happens at the server side and only the needed rows gets sent back.

I happen to have an article on my web site that discusses this case
in detail. You don't have to read all of it, but you can just get
the function you need at
http://www.algonet.se/~sommar/array...html#iterative.

> But functions seem to have MAJOR performance problems when default
> values are passed in. When I pass in a specific date the query takes a
> few seconds but when I pass in DEFAULT and set the date to the results
> of the GetPreviousTradeDate() function the query takes anywhere from 8
> minutes to 15 minutes. (This even happens if I do not use the
> GetPreviousTradeDate() function and set my parameter to a hard-coded
> value)

The difference is not always that big, but apparently your query is
sensitive for this. I suggest that you split up the procedure in two:

EXEC outer_sp @.date = NULL datetime
IF @.date IS NULL
SELECT @.date = dbo.yourfunctionfordefault()
EXEC inner_sp @.date

And then inner_sp includes the actual query.

For a long treatise on this subject, search on Google news for articles
by Bart Duncan (a escalation engineer at Microsoft) and the phrase
"parameter sniffing".

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||JayCallas@.hotmail.com (Jason) wrote in message news:<f01a7c89.0310141510.28e9c846@.posting.google.com>...
> I hate the restart this thread but I have hit a brick wall...
> I am at a lose of whether to creat functions or stored procedures (or
> even views).
> The below questions/issues are based on a need to return information
> on a particular date for one to many symbols.
> For my example lets say 15 symbols. You could do a query like Symbol =
> 'a' OR Symbol = 'b' OR Symbol... but it would make more sense to do
> Symbol IN ('a','b',...))
> I would also like to give my functions and stored procedures to use a
> default date if none is specified. I created a function named
> GetPreviousTradeDate() which does this based on a calendar.
> SO here is how I see it.
> Stored procedures seem to be the fastest in terms of returning data
> back. But they seem to be limited in the sense that they can return
> ONE row or ALL the rows since you cannot pass in a variable list of
> symbols. You also cannot use the SP as part of a query. You could just
> return all the rows back to the client and do filter or searching on
> that end but that does not seem efficient or professional.
> A function also does not let you pass in a variable list of symbols
> but at least you can use it in a query. You could do something like
> SELECT * FROM TheFunction() WHERE Symbol IN ('a','b',...). All this
> happens at the server side and only the needed rows gets sent back.
> But functions seem to have MAJOR performance problems when default
> values are passed in. When I pass in a specific date the query takes a
> few seconds but when I pass in DEFAULT and set the date to the results
> of the GetPreviousTradeDate() function the query takes anywhere from 8
> minutes to 15 minutes. (This even happens if I do not use the
> GetPreviousTradeDate() function and set my parameter to a hard-coded
> value)
> Any thoughts or comments would be appreciated.

Since the stored procedure has both the speed and the ability to use
default values without performance hit, would it be normal practice or
efficient to send separate queries for each symbol to the stored
procedure? This could result in anywhere from a few to several hundred
calls at a time.|||Jason (JayCallas@.hotmail.com) writes:
> Since the stored procedure has both the speed and the ability to use
> default values without performance hit, would it be normal practice or
> efficient to send separate queries for each symbol to the stored
> procedure? This could result in anywhere from a few to several hundred
> calls at a time.

That does not seem like a good idea. Certainly more efficient to get
data for all symbols at once. See my previous post for suggestions.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||>> A function also does not let you pass in a variable list of symbols
but at least you can use it in a query ... Any thoughts or comments
would be appreciated. <<

Ever try putting the list of symbols into a one column table and using
an "IN (SELECT parm FROM Parmlist)" instead?

Default fillfactor for indexes

Is there a way to change the default faillfactor for a table or its nonclustered indexes without rebuilding it?Hi Jeffrey
Fillfactor only applies when an index is being built; it is not maintained.
So changing it on an existing index wouldn't mean anything. What are you
trying to accomplish?
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Jeffrey" <anonymous@.discussions.microsoft.com> wrote in message
news:0B210F3F-F1E4-4857-A60C-41867F6EB1E8@.microsoft.com...
> Is there a way to change the default faillfactor for a table or its
nonclustered indexes without rebuilding it?

Default Field Value

Hello,
I have a table with a filed set up to use a default Field value of
(GetDate()) but is also allowed nulls. However, The default value for this
field has stopped being set automatically. Has anyone experienced this
problem before? Does anyone know why and/or how to fix it?
-Scott ElgramScott
Can you show us your query?
create table #t
(
i int not null primary key,
dt datetime null default getdate()--Allow Nulls
)
insert into #t (i) values (20)
select * from #t
insert into #t (i,dt) values (30,null)
select * from #t
drop table #t
"Scott Elgram" <SElgram@.verifpoint.com> wrote in message
news:eOTlFYNyEHA.2676@.TK2MSFTNGP12.phx.gbl...
> Hello,
> I have a table with a filed set up to use a default Field value of
> (GetDate()) but is also allowed nulls. However, The default value for
this
> field has stopped being set automatically. Has anyone experienced this
> problem before? Does anyone know why and/or how to fix it?
> --
> -Scott Elgram
>|||Well, The main source of INSERTs to this table is a program that was written
in Delphi and uses ADO. The section that does the insert is the following
if you are familiar with it;
--Begin Code--
With ADOQuery1 do
begin
SQL.Text := 'SELECT * FROM [DTable] WHERE [ID] = 0;';
Open;
Insert;
FieldByName('PlanID').Value := PlanID;
FieldByName('ProvID').Value := ProvID;
FieldByName('VerifBy').Value := VerifBy;
FieldByName('VerifDate').Value := VerifDate;
FieldByName('Type').Value := DocType;
(FieldByName('Image') AS TBlobField).loadfromStream(Blob);
FieldByName('PacketIndexID').Value := PktID;
Post;
Close;
end;
--End Code--
The field in question is not in this code but should be set to the current
date/time when this bit is executed. Some other strange things are
happening with he ID field in this table too. The ID field is set to Auto
Increment by 1 but every time something is inserted it just by almost 200
sometimes. Any help with that issue would be greatly appreciated as well.
-Scott
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:etAuV1jyEHA.3808@.tk2msftngp13.phx.gbl...
> Scott
> Can you show us your query?
> create table #t
> (
> i int not null primary key,
> dt datetime null default getdate()--Allow Nulls
> )
> insert into #t (i) values (20)
> select * from #t
> insert into #t (i,dt) values (30,null)
> select * from #t
> drop table #t
>
> "Scott Elgram" <SElgram@.verifpoint.com> wrote in message
> news:eOTlFYNyEHA.2676@.TK2MSFTNGP12.phx.gbl...
> this
>

Default Field Value

Hello,
I have a table with a filed set up to use a default Field value of
(GetDate()) but is also allowed nulls. However, The default value for this
field has stopped being set automatically. Has anyone experienced this
problem before? Does anyone know why and/or how to fix it?
-Scott Elgram
Scott
Can you show us your query?
create table #t
(
i int not null primary key,
dt datetime null default getdate()--Allow Nulls
)
insert into #t (i) values (20)
select * from #t
insert into #t (i,dt) values (30,null)
select * from #t
drop table #t
"Scott Elgram" <SElgram@.verifpoint.com> wrote in message
news:eOTlFYNyEHA.2676@.TK2MSFTNGP12.phx.gbl...
> Hello,
> I have a table with a filed set up to use a default Field value of
> (GetDate()) but is also allowed nulls. However, The default value for
this
> field has stopped being set automatically. Has anyone experienced this
> problem before? Does anyone know why and/or how to fix it?
> --
> -Scott Elgram
>
|||Well, The main source of INSERTs to this table is a program that was written
in Delphi and uses ADO. The section that does the insert is the following
if you are familiar with it;
--Begin Code--
With ADOQuery1 do
begin
SQL.Text := 'SELECT * FROM [DTable] WHERE [ID] = 0;';
Open;
Insert;
FieldByName('PlanID').Value := PlanID;
FieldByName('ProvID').Value := ProvID;
FieldByName('VerifBy').Value := VerifBy;
FieldByName('VerifDate').Value := VerifDate;
FieldByName('Type').Value := DocType;
(FieldByName('Image') AS TBlobField).loadfromStream(Blob);
FieldByName('PacketIndexID').Value := PktID;
Post;
Close;
end;
--End Code--
The field in question is not in this code but should be set to the current
date/time when this bit is executed. Some other strange things are
happening with he ID field in this table too. The ID field is set to Auto
Increment by 1 but every time something is inserted it just by almost 200
sometimes. Any help with that issue would be greatly appreciated as well.
-Scott
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:etAuV1jyEHA.3808@.tk2msftngp13.phx.gbl...
> Scott
> Can you show us your query?
> create table #t
> (
> i int not null primary key,
> dt datetime null default getdate()--Allow Nulls
> )
> insert into #t (i) values (20)
> select * from #t
> insert into #t (i,dt) values (30,null)
> select * from #t
> drop table #t
>
> "Scott Elgram" <SElgram@.verifpoint.com> wrote in message
> news:eOTlFYNyEHA.2676@.TK2MSFTNGP12.phx.gbl...
> this
>

Default Field Value

Hello,
I have a table with a filed set up to use a default Field value of
(GetDate()) but is also allowed nulls. However, The default value for this
field has stopped being set automatically. Has anyone experienced this
problem before? Does anyone know why and/or how to fix it?
--
-Scott ElgramScott
Can you show us your query?
create table #t
(
i int not null primary key,
dt datetime null default getdate()--Allow Nulls
)
insert into #t (i) values (20)
select * from #t
insert into #t (i,dt) values (30,null)
select * from #t
drop table #t
"Scott Elgram" <SElgram@.verifpoint.com> wrote in message
news:eOTlFYNyEHA.2676@.TK2MSFTNGP12.phx.gbl...
> Hello,
> I have a table with a filed set up to use a default Field value of
> (GetDate()) but is also allowed nulls. However, The default value for
this
> field has stopped being set automatically. Has anyone experienced this
> problem before? Does anyone know why and/or how to fix it?
> --
> -Scott Elgram
>|||Well, The main source of INSERTs to this table is a program that was written
in Delphi and uses ADO. The section that does the insert is the following
if you are familiar with it;
--Begin Code--
With ADOQuery1 do
begin
SQL.Text := 'SELECT * FROM [DTable] WHERE [ID] = 0;';
Open;
Insert;
FieldByName('PlanID').Value := PlanID;
FieldByName('ProvID').Value := ProvID;
FieldByName('VerifBy').Value := VerifBy;
FieldByName('VerifDate').Value := VerifDate;
FieldByName('Type').Value := DocType;
(FieldByName('Image') AS TBlobField).loadfromStream(Blob);
FieldByName('PacketIndexID').Value := PktID;
Post;
Close;
end;
--End Code--
The field in question is not in this code but should be set to the current
date/time when this bit is executed. Some other strange things are
happening with he ID field in this table too. The ID field is set to Auto
Increment by 1 but every time something is inserted it just by almost 200
sometimes. Any help with that issue would be greatly appreciated as well.
-Scott
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:etAuV1jyEHA.3808@.tk2msftngp13.phx.gbl...
> Scott
> Can you show us your query?
> create table #t
> (
> i int not null primary key,
> dt datetime null default getdate()--Allow Nulls
> )
> insert into #t (i) values (20)
> select * from #t
> insert into #t (i,dt) values (30,null)
> select * from #t
> drop table #t
>
> "Scott Elgram" <SElgram@.verifpoint.com> wrote in message
> news:eOTlFYNyEHA.2676@.TK2MSFTNGP12.phx.gbl...
> > Hello,
> > I have a table with a filed set up to use a default Field value of
> > (GetDate()) but is also allowed nulls. However, The default value for
> this
> > field has stopped being set automatically. Has anyone experienced this
> > problem before? Does anyone know why and/or how to fix it?
> >
> > --
> > -Scott Elgram
> >
> >
>

Default field to another field value on db level

Hi All,

I need to create a new field on a table and have that field default to another field value in that same table. Is there a way to do this w/ a default constraint rather than adding a trigger to the table? If i can't use a default constraint does anyone have a template trigger i could use? Below is an example of what i'm trying to do (Field_C is the new field and i want it to use Field_A value if no other value is specified on insert). Any help would be greatly appreciated.

alter table FOO add Field_C varchar(50) not null constraint FOO_default DEFAULT Field_A

thanks,
Davethis worked for me.....

CREATE Trigger TRG_FOO_default_INS
on dbo.FOO
for Insert
as

Declare @.default_FieldC varchar(50)

select @.default_FieldC = Field_C from inserted

If (@.default_FieldC is null) or (@.default_FieldC = '')
BEGIN
Update FOO set Field_C = Field_A where PK_ID in (select PK_ID from inserted)
END -- update externalname

default date in sql2k

Having a table with two datetime columns(date_ & time_. One to store the date and the other to store the time (my client's design).

When I insert a date alone to the date_ column the time defaults to 12:00:00 AM (as expected).
But I have a problem when inserting / updating the time in the time_ column. When i insert the time from my asp application / query analyzer the date defaults to 1900-1-1(expected). When i insert the time from enterprise manager the date defaults to 1899-12-30.

Can anybody explain me why the date defaults to 1899-12-30 in enterprise manager

thanksThe following article explains this in detail:

article (http://www.databasejournal.com/features/mssql/article.php/1494281)

If you need further discussion, let me know and I will give you my 2 cents as to what is occurring.sql

Default cursor in SQL 2000

Hello,

I am writing an application which I would like to use a server cursor on. I have noticed that when I try to access a table that I have created using a server cursor with my app, I have problems getting the information. It says that the cursor doesn't support bookmarks. But, when I access the Employees table on the Northwind database with the same settings, everything is fine. Is there some sort of option I'm missing with my tables? Some default cursor or something? Is there a way to tell what cursors are defined with the Northwind DB? Any help would be great, thanks!Check if your table has a primary key|||that was it, thank you very much!

Originally posted by kukuk
Check if your table has a primary key|||Would something similar also go for the dynamic cursor? I setup the primary keys which works great for Keyset type. But I get the old 'Dataset does not support bookmarks' when trying a dynamic cursor on the same dataset. I've tried both MSDASQL and SQLOLEDB providers, both gave errors.

Originally posted by Thread77
that was it, thank you very much!sql

Default constraints

Does anyone know a query that will return the value defined on a default constraint for a database table.column ?

So, if I have table :

create table #bill (
column1 int not null,
column2 char(4) default 'AAAA'
)

Something that would give me the 'AAAA' back ?

Thanks,

BillSelect column_default
from Information_Schema.Columns
where table_name = 'table_name' AND
column_name = 'column'|||Thank you very much,

Billsql

DEFAULT constraint not working

I have a 32 column table. Every column is NOT NULL, and all but the first have DEFAULT constraints. In particular, the 21st column has such a constraint. I also have a stored proc which truncates the table and then loads it with a SELECT from a view, like this

TRUNCATE TABLE NDRS_Call_Data_Table

INSERT INTO NDRS_Call_Data_Table(<column-list>)
SELECT <column-list> FROM NDRS_Call_Data_View

The view is a complex join of several other tables. When I run the proc, I get the error:

"Cannot insert the value NULL into column 'CC_Time', table 'Tomcat_prod.dbo.NDRS_Call_Data_Table'; column does not allow nulls. INSERT fails."

BUT, if I simply do this from Query Analyzer.....

INSERT INTO NDRS_Calls_Data_Table(data_indicator)
VALUES('Z')

it works. The default constraint on the CC_Time column works correctly and supplies the default value of '0 ' as it should. (the column is a CHAR(2) column, despite the name suggesting it is datetime)

Recompiling the view and the stored procedure does not help. Anybody else seen this? Is this a known bug?You constraint works fine. I believe the null value comes from the view. As you said, the view was created from a complex join. Somewhere in the resultset it returns a null value in the view. I would run the query and check the resultset closely.|||Got it. Misunderstanding on the operation of DEFAULT constraints. They don't override explicit nulls if you specify the column in the insert. They only provide values if you leave out the column in the column list. Thanks|||Hi,

Just for my understanding.

Is column "CC_Time" included in <column-list> in your insert statement or not ?

If not, do you have any "after insert" triggers on table "Call_Data_Table" ?

CVM.|||Originally posted by cvandemaele
Hi,

Just for my understanding.

Is column "CC_Time" included in <column-list> in your insert statement or not ?

If not, do you have any "after insert" triggers on table "Call_Data_Table" ?

CVM.

Well, forget about this last post. Just getting a cup of coffee and WHAM problem solved.|||Further research shows that this is ANSI standard behavior. A NOT NULL column with a DEFAULT will not use the default if the column is specified in the insert's column list and NULL is provided. Besides being counter-intuitive, frankly this strikes me as dumb. It really gets in the way of using the INSERT ... SELECT syntax|||To solve the problems of nulls in your view, you can use isnull(a, b) where a is the selected column and b is the default value for that column.

example:

select isnull (title, 'No title') as title, au_lname
from authors a left join titleauthor ta on a.au_id = ta.au_id left join titles t on t.title_id = ta.title_id|||True.

Undortunately, when using INSERT ... SELECT you can't say
SELECT ..., ISNULL(column, DEFAULT), ...

the way you can use the DEFAULT keyword like this...

INSERT
VALUES (x,y,z,DEFAULT,p,q,r)

You have to know what the default value is, and explicitly put it in as the 2nd parameter of the ISNULL function. So if you ever change the default constraint, you'll have to go back and change all the canned queries, too.|||you can declare a variable for each column that you want default to be inserted in place of null, and then initialize them according to each variable data type:

declare @.value int
select @.value = cast(replace(replace(m.text, ')', ''), '(', '') as int)
from syscomments m,syscolumns c,sysobjects o
where c.id=object_id('dbo.your_table')
and c.name=('your_column_name')
and o.type='d'
and m.id=c.cdefault
and m.id=o.id

DEFAULT constraint name

Hello,
We currently define defaults for to selected columns in tables with CREATE
TABLE command. SQL Server 2000 creates a name for each default, so it may
look like this:
DF__Xyz__Abc__59FA5E80
where 'Xyz' is part of table name, 'Abc' is part of column's name and last
part is generated by SQL Server.
So, my question is:
What would be a good approach to generate table conversion
script when we need to change a table structure, so that the script can
work in another database.
Thanks,
VitaliyI'm not sure what you are asking. If you name the constraints in the first place, you know what the
name is and won't have any problems further down the line...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Vitalik" <address@.domain.com> wrote in message news:uXOSeYmqFHA.3352@.TK2MSFTNGP14.phx.gbl...
> Hello,
> We currently define defaults for to selected columns in tables with CREATE
> TABLE command. SQL Server 2000 creates a name for each default, so it may
> look like this:
> DF__Xyz__Abc__59FA5E80
> where 'Xyz' is part of table name, 'Abc' is part of column's name and last
> part is generated by SQL Server.
> So, my question is:
> What would be a good approach to generate table conversion
> script when we need to change a table structure, so that the script can
> work in another database.
> Thanks,
> Vitaliy
>|||If you want to find the name of the default constraint for a particular
column, use something like this:
SELECT o2.name
FROM sysobjects o1 INNER JOIN syscolumns c ON c.id=o1.id
INNER JOIN sysobjects o2 ON o2.parent_obj=o1.id AND c.colid=o2.info
WHERE o2.type='D' AND c.name='YourColumn' AND o1.name='YourTable'
Of course, the best strategy would be to give a name for the defaults
at the time they are created, like this:
CREATE TABLE YourTable (
...
YourColumn int CONSTRAINT ConstraintName DEFAULT (0)
...
)
Razvan|||Thanks Tibor for the quick reply.
Based on your answer I believe I have some work cut out for me :(
Unfortunately, we designed all our CREATE TABLE commands using simple
syntax:
ColumnName datatype DEFAULT (value)
and now it bites us since conversion script generated against one database
may not work in another database. I said "may", because in situations when
another database was created from 1st db backup, the names will match and
life is good. I just googled up some info, that I hope will help me.
Thanks,
Vitaliy
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23q8ASmmqFHA.2696@.TK2MSFTNGP11.phx.gbl...
> I'm not sure what you are asking. If you name the constraints in the first
place, you know what the
> name is and won't have any problems further down the line...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Vitalik" <address@.domain.com> wrote in message
news:uXOSeYmqFHA.3352@.TK2MSFTNGP14.phx.gbl...
> > Hello,
> >
> > We currently define defaults for to selected columns in tables with
CREATE
> > TABLE command. SQL Server 2000 creates a name for each default, so it
may
> > look like this:
> >
> > DF__Xyz__Abc__59FA5E80
> >
> > where 'Xyz' is part of table name, 'Abc' is part of column's name and
last
> > part is generated by SQL Server.
> >
> > So, my question is:
> >
> > What would be a good approach to generate table conversion
> > script when we need to change a table structure, so that the script can
> > work in another database.
> >
> > Thanks,
> > Vitaliy
> >
> >
>|||Try,
select
object_name([id]) as table_name,
col_name([id], colid) as column_name,
object_name(constid) const_name
from
sysconstraints
where
objectproperty(constid, 'IsDefaultCnst') = 1
go
if you need to filter for a specific table and column, use:
...
where
objectproperty(constid, 'IsDefaultCnst') = 1
and [id] = object_id('dbo.orders')
and col_name([id], colid) = 'Freight'
I wish I can do this using information_schema.
AMB
"Vitalik" wrote:
> Hello,
> We currently define defaults for to selected columns in tables with CREATE
> TABLE command. SQL Server 2000 creates a name for each default, so it may
> look like this:
> DF__Xyz__Abc__59FA5E80
> where 'Xyz' is part of table name, 'Abc' is part of column's name and last
> part is generated by SQL Server.
> So, my question is:
> What would be a good approach to generate table conversion
> script when we need to change a table structure, so that the script can
> work in another database.
> Thanks,
> Vitaliy
>
>|||I see you problem... Use the suggestions that other has posted to get the current name of the
constraint. You can then use dynamic SQL to drop the constraint. And then add it back with a known
name. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Vitalik" <address@.domain.com> wrote in message news:%23pD8PFnqFHA.1032@.TK2MSFTNGP12.phx.gbl...
> Thanks Tibor for the quick reply.
> Based on your answer I believe I have some work cut out for me :(
> Unfortunately, we designed all our CREATE TABLE commands using simple
> syntax:
> ColumnName datatype DEFAULT (value)
> and now it bites us since conversion script generated against one database
> may not work in another database. I said "may", because in situations when
> another database was created from 1st db backup, the names will match and
> life is good. I just googled up some info, that I hope will help me.
> Thanks,
> Vitaliy
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:%23q8ASmmqFHA.2696@.TK2MSFTNGP11.phx.gbl...
>> I'm not sure what you are asking. If you name the constraints in the first
> place, you know what the
>> name is and won't have any problems further down the line...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Vitalik" <address@.domain.com> wrote in message
> news:uXOSeYmqFHA.3352@.TK2MSFTNGP14.phx.gbl...
>> > Hello,
>> >
>> > We currently define defaults for to selected columns in tables with
> CREATE
>> > TABLE command. SQL Server 2000 creates a name for each default, so it
> may
>> > look like this:
>> >
>> > DF__Xyz__Abc__59FA5E80
>> >
>> > where 'Xyz' is part of table name, 'Abc' is part of column's name and
> last
>> > part is generated by SQL Server.
>> >
>> > So, my question is:
>> >
>> > What would be a good approach to generate table conversion
>> > script when we need to change a table structure, so that the script can
>> > work in another database.
>> >
>> > Thanks,
>> > Vitaliy
>> >
>> >
>

Default column causing problems!

Hi everybody,

Iam migrating a table called Vendors from sql 2005 to flatfile.

but it end with error message that the default column is causing problem.

the table is as follows,

CREATE TABLE VENDORS

(

RECORDTYPECHAR(5)DEFAULT'VNDRS' NULL,

SETIDCHAR(5)NOT NULL,

VENDORIDCHAR(10)NOT NULL,

VENDORNAMESHORTCHAR(14)NOT NULL,

VENDORNAMESEQNUMINTNULL,

NAME1CHAR(40)NOT NULL,

NAME2CHAR(40)NULL,

REMITVENDORCHAR(10)NULL,

CUSTSETIDCHAR(5)NULL,

CUSTIDCHAR(15)NULL,

ENTEREDBYCHAR(8)NULL,

ARNUMCHAR(15)NULL,

OLDVENDORIDCHAR(15)NULL,

WTHDSWCHAR(1)NOT NULL,

VATSWCHAR(1)NOT NULL,

NAME1ACCHAR(40)NULL,

NAME2ACCHAR(40)NULL,

PRIMARYVENDORCHAR(10)NULL,

LASTACTIVITYDTDATETIMENULL,

HUBZONECHAR(1)NOT NULL,

EEOCERTIFDTDATETIMENULL,

VENDORAFFILIATECHAR(5)NULL

)

any idea as what i need to do?

pls help out.

Thanks and Regards,

sg

You haven't told us anything. What's the error and what are you doing to get that error?|||Why did you create a new thread? This is a duplicate. Please send all responses to the original: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1074510&SiteID=1|||

Phil Brammer wrote:

Why did you create a new thread? This is a duplicate. Please send all responses to the original: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1074510&SiteID=1

The 2 threads have been merged together

|||

Rafael Salas wrote:

Phil Brammer wrote:

Why did you create a new thread? This is a duplicate. Please send all responses to the original: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1074510&SiteID=1

The 2 threads have been merged together

Thanks! You're the man.|||

Hi Phil,

it says the column recordtype which is the default colummn in my case is failing.

so the rows are not getting copied into flat file .

moreover it aslo says that maxerrorcount is 1 and no.of errors exceeded the maxerrorcount,i even tried to change the

maxerrorcount by going to the properties tab.

but still iam getting the error repeatedly.

pls help.

regards,

sg

|||

swan_sg wrote:

Hi Phil,

it says the column recordtype which is the default colummn in my case is failing.

so the rows are not getting copied into flat file .

moreover it aslo says that maxerrorcount is 1 and no.of errors exceeded the maxerrorcount,i even tried to change the

maxerrorcount by going to the properties tab.

but still iam getting the error repeatedly.

pls help.

regards,

sg

Please don't summarize the error in your own words. Please copy and paste the error here. Also, how do you have your data flow setup? Please identify all of the steps and what components you have in your data flow.

Thanks,

Phil

default column causing problem!

Hi everybody,

Iam migrating a table called Vendors from sql 2005 to flatfile.

but it end with error message that the default column is causing problem.

the table is as follows,

CREATE TABLE VENDORS

(

RECORDTYPECHAR(5)DEFAULT'VNDRS' NULL,

SETIDCHAR(5)NOT NULL,

VENDORIDCHAR(10)NOT NULL,

VENDORNAMESHORTCHAR(14)NOT NULL,

VENDORNAMESEQNUMINTNULL,

NAME1CHAR(40)NOT NULL,

NAME2CHAR(40)NULL,

REMITVENDORCHAR(10)NULL,

CUSTSETIDCHAR(5)NULL,

CUSTIDCHAR(15)NULL,

ENTEREDBYCHAR(8)NULL,

ARNUMCHAR(15)NULL,

OLDVENDORIDCHAR(15)NULL,

WTHDSWCHAR(1)NOT NULL,

VATSWCHAR(1)NOT NULL,

NAME1ACCHAR(40)NULL,

NAME2ACCHAR(40)NULL,

PRIMARYVENDORCHAR(10)NULL,

LASTACTIVITYDTDATETIMENULL,

HUBZONECHAR(1)NOT NULL,

EEOCERTIFDTDATETIMENULL,

VENDORAFFILIATECHAR(5)NULL

)

any idea as what i need to do?

pls help out.

Thanks and Regards,

sg

You haven't told us anything. What's the error and what are you doing to get that error?|||Why did you create a new thread? This is a duplicate. Please send all responses to the original: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1074510&SiteID=1|||

Phil Brammer wrote:

Why did you create a new thread? This is a duplicate. Please send all responses to the original: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1074510&SiteID=1

The 2 threads have been merged together

|||

Rafael Salas wrote:

Phil Brammer wrote:

Why did you create a new thread? This is a duplicate. Please send all responses to the original: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1074510&SiteID=1

The 2 threads have been merged together

Thanks! You're the man.|||

Hi Phil,

it says the column recordtype which is the default colummn in my case is failing.

so the rows are not getting copied into flat file .

moreover it aslo says that maxerrorcount is 1 and no.of errors exceeded the maxerrorcount,i even tried to change the

maxerrorcount by going to the properties tab.

but still iam getting the error repeatedly.

pls help.

regards,

sg

|||

swan_sg wrote:

Hi Phil,

it says the column recordtype which is the default colummn in my case is failing.

so the rows are not getting copied into flat file .

moreover it aslo says that maxerrorcount is 1 and no.of errors exceeded the maxerrorcount,i even tried to change the

maxerrorcount by going to the properties tab.

but still iam getting the error repeatedly.

pls help.

regards,

sg

Please don't summarize the error in your own words. Please copy and paste the error here. Also, how do you have your data flow setup? Please identify all of the steps and what components you have in your data flow.

Thanks,

Phil

sql

default clustered index when selecting primary key

Hi,
I have a list of table which I need to replicate to another database. First
of all, why do I need a primary key to achieve replication (sorry I am new
with replication)
Then I've noticed that if I just click the primary key button from EM on
Design table, then it defaults to a clustered index on the column I've
selected. Does anybody knwow why it defaults to clustered? I am not too kee
n
on clustered indexes for my database.
Thanks,
Panos.Panos Stavroulis. wrote:

> Hi,
> I have a list of table which I need to replicate to another database. Firs
t
> of all, why do I need a primary key to achieve replication (sorry I am new
> with replication)
Without a key the database can't uniquely identify a row in a table.
Every table should have a key. Why would you want a table without one?

> Then I've noticed that if I just click the primary key button from EM on
> Design table, then it defaults to a clustered index on the column I've
> selected. Does anybody knwow why it defaults to clustered? I am not too k
een
> on clustered indexes for my database.
I don't think there's a very good reason why it defaults to clustered.
You can easily change the setting so the default doesn't matter very
much. Also, I would not usually create indexes or keys in Enterprise
Manager. EM is very inefficient in the way it implements schema
changes. Usually it's better to write TSQL for your schema mods. EM
will even script the change for you to review if that helps you get
started.
Most of the time it does pay to have a clustered index on every table.
Tables without clustered indexes should be the exception rather than
the rule.
David Portas
SQL Server MVP
--|||> Does anybody knwow why it defaults to clustered?
A design decision MS made some 12 years ago. Perhaps because you can only ha
ve one CL IX on a table
and you can only have one PK for a table? Important to notice is that you ar
e free to override that
default.

> I am not too keen
> on clustered indexes for my database.
Why not? Clustered indexes are a very important performance tools, and most
DBAs avoid heap tables
and try to decide carefully which index to be the clustered index.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Panos Stavroulis." <PanosStavroulis@.discussions.microsoft.com> wrote in mes
sage
news:E07ADE7B-5424-43E1-AE34-936AF21D27AC@.microsoft.com...
> Hi,
> I have a list of table which I need to replicate to another database. Firs
t
> of all, why do I need a primary key to achieve replication (sorry I am new
> with replication)
> Then I've noticed that if I just click the primary key button from EM on
> Design table, then it defaults to a clustered index on the column I've
> selected. Does anybody knwow why it defaults to clustered? I am not too k
een
> on clustered indexes for my database.
> Thanks,
> Panos.|||You are opening a good subject here. Well the reason I don't use clustered
indexes for many tables is because I don't need to do a select statements
where col_id between 10 and 200 etc if column col_id was my primary key.
I normally select individual records. In fact which way do you think it's
faster? if I have a non clustered index and select where col_id = 100 or a
clustered index?
Thanks.
"Tibor Karaszi" wrote:

> A design decision MS made some 12 years ago. Perhaps because you can only
have one CL IX on a table
> and you can only have one PK for a table? Important to notice is that you
are free to override that
> default.
>
> Why not? Clustered indexes are a very important performance tools, and mos
t DBAs avoid heap tables
> and try to decide carefully which index to be the clustered index.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Panos Stavroulis." <PanosStavroulis@.discussions.microsoft.com> wrote in m
essage
> news:E07ADE7B-5424-43E1-AE34-936AF21D27AC@.microsoft.com...
>|||> I normally select individual records. In fact which way do you think it's
> faster? if I have a non clustered index and select where col_id = 100 or a
> clustered index?
A clustered index will be marginally faster.
But are you really saying that you have no range queries (lower selectivity
search arguments) at all
in the database? What about joins? Grouping? Sorting? Clustered indexes can
beneficial for a wide
type of operations.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Panos Stavroulis." <PanosStavroulis@.discussions.microsoft.com> wrote in mes
sage
news:4AFF7C5A-C43F-4C27-AA5B-9A6F65DAAE6E@.microsoft.com...
> You are opening a good subject here. Well the reason I don't use clustered
> indexes for many tables is because I don't need to do a select statements
> where col_id between 10 and 200 etc if column col_id was my primary key.
> I normally select individual records. In fact which way do you think it's
> faster? if I have a non clustered index and select where col_id = 100 or a
> clustered index?
> Thanks.
> "Tibor Karaszi" wrote:
>|||I don't think you quite grasp the point of a clustered index.
I really don't feel like going into it right now, but let's look at your
example:
With a nonclustered index several index pages must be searched before
reaching the 100 value, while with a clustered index this is done in two
quick steps:
1) find the page where the 100 value resides; and
2) only go to that page.
ML
http://milambda.blogspot.com/|||The explanation that I remember best from an old DBA friend of mine is
that a clustered index is like page numbers in a book, whereas
non-clustered indexes are like the index in the back; although the
analogy is not a perfect fit, it does explain the relationship between
clustered and nonclustered indexes. Without page numbers in a
sequential order (clustering), it's tough to find the topic you're
looking for.
Stu|||Thanks for the answers. OK how about this.
We have a query that joins 2 tables, file & file_detail linked by column
col_id which is integer. If I was making a query to select all files and
their detail which are between 100 and 1000, then I would expect that this
query will be faster if I had a clustered index on the table.
However, I personally would expect if the query was "give me file 250" then
the non-clustered solution will be faster? Do you agree with this. How about
covering on non-clustered indexes, I would expect the non clustered option t
o
be faster. Thank you.
"Tibor Karaszi" wrote:

> A clustered index will be marginally faster.
> But are you really saying that you have no range queries (lower selectivit
y search arguments) at all
> in the database? What about joins? Grouping? Sorting? Clustered indexes ca
n beneficial for a wide
> type of operations.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Panos Stavroulis." <PanosStavroulis@.discussions.microsoft.com> wrote in m
essage
> news:4AFF7C5A-C43F-4C27-AA5B-9A6F65DAAE6E@.microsoft.com...
>|||> We have a query that joins 2 tables, file & file_detail linked by column
> col_id which is integer. If I was making a query to select all files and
> their detail which are between 100 and 1000, then I would expect that this
> query will be faster if I had a clustered index on the table.
Cluster on which table and which column(s)? It is possible that a clustered
index o the fireign key
column in the file_detail table will improve that join, but that depends on
a lot of other factors.

> However, I personally would expect if the query was "give me file 250" the
n
> the non-clustered solution will be faster?
Non-clustered index on what? For a query with high-selectivity, a clustered
index on the search
column will still outperform a nonc-lustered (but the ihger selectivity, the
more marginally) as SQL
Server doesn't have to fetch each row in the datapage. Unless the non-cluste
red index covers the
query, of course.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Panos Stavroulis." <PanosStavroulis@.discussions.microsoft.com> wrote in mes
sage
news:CE190BBB-A52C-4D06-9AA8-8E33D983B643@.microsoft.com...
> Thanks for the answers. OK how about this.
> We have a query that joins 2 tables, file & file_detail linked by column
> col_id which is integer. If I was making a query to select all files and
> their detail which are between 100 and 1000, then I would expect that this
> query will be faster if I had a clustered index on the table.
> However, I personally would expect if the query was "give me file 250" the
n
> the non-clustered solution will be faster? Do you agree with this. How abo
ut
> covering on non-clustered indexes, I would expect the non clustered option
to
> be faster. Thank you.
>
> "Tibor Karaszi" wrote:
>|||Sorry meant to say clustered index on col_id on both tables in one case and
nonclustered index on both tables in the second.
So basically basically since for most of the queries you need more
information (columns) than the columns contained in the index (covering
situation) then it's better to create a clustered index on the table as long
as the data arrive in the database in a sequential order and you don't get
page breaks.
Also do you have a view on clustered indexes on tables which are over 8K
long, ie maximum size of a page? Thanks.
"Tibor Karaszi" wrote:

> Cluster on which table and which column(s)? It is possible that a clustere
d index o the fireign key
> column in the file_detail table will improve that join, but that depends o
n a lot of other factors.
>
> Non-clustered index on what? For a query with high-selectivity, a clustere
d index on the search
> column will still outperform a nonc-lustered (but the ihger selectivity, t
he more marginally) as SQL
> Server doesn't have to fetch each row in the datapage. Unless the non-clus
tered index covers the
> query, of course.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Panos Stavroulis." <PanosStavroulis@.discussions.microsoft.com> wrote in m
essage
> news:CE190BBB-A52C-4D06-9AA8-8E33D983B643@.microsoft.com...
>

De-dupe question

Hi,
I need some help to de-duping an orders table. I have an orderitems table
containing OrderID, OrderItemID, (plus a few other columns), and we need to
clean
the data prior to migration. We have quite a few duplicates of orders, and
in moving to a template, or set based model, we want to create typical group
s
from the data.
If I can provide an OrderID as a parameter, can anyone let me know how I can
retrieve the set of all precisely matching candidates. The problem I'm
running into is that
I keep getting partial matches back, for example if OrderID 1 has OrderItems
1, 2 and 3, and OrderID 2 has OrderItems 2 and 3, I get OrderID 2 back as a
match for OrderID 1.
The query I'm using is as follows:
select * from OrderHistory where OrderItemID in (select OrderItemID from
OrderHistory where OrderID = 846863) and OrderID <> 846863.
This seems to match on full or subset rather than on complete match.
The columns of interest are (OrderID int, OrderItemID int)
Any help is greatly appreciated.
Thanks
mkCould you post DDL, sample data, and sample output?
http://www.aspfaq.com/5006
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"mk" <mk@.discussions.microsoft.com> wrote in message
news:E85B6C9F-EC19-4632-9E79-40ABE570AED3@.microsoft.com...
> Hi,
> I need some help to de-duping an orders table. I have an orderitems table
> containing OrderID, OrderItemID, (plus a few other columns), and we need
to
> clean
> the data prior to migration. We have quite a few duplicates of orders,
and
> in moving to a template, or set based model, we want to create typical
groups
> from the data.
> If I can provide an OrderID as a parameter, can anyone let me know how I
can
> retrieve the set of all precisely matching candidates. The problem I'm
> running into is that
> I keep getting partial matches back, for example if OrderID 1 has
OrderItems
> 1, 2 and 3, and OrderID 2 has OrderItems 2 and 3, I get OrderID 2 back as
a
> match for OrderID 1.
> The query I'm using is as follows:
> select * from OrderHistory where OrderItemID in (select OrderItemID from
> OrderHistory where OrderID = 846863) and OrderID <> 846863.
> This seems to match on full or subset rather than on complete match.
> The columns of interest are (OrderID int, OrderItemID int)
> Any help is greatly appreciated.
> Thanks
> mk|||CREATE TABLE [dbo].[OrderHistory] (
[OrderID] [int] NOT NULL ,
[OrderItemID] [int] NOT NULL
) ON [defgrp]
GO
There are other metadata columns but they are not relevant to this question.
A sample of the data I'm using is:
OrderID OrderItemID
846863 191731
846863 201746
846864 191731
846864 201746
846864 201747
846865 191731
846865 201746
846866 191732
846866 201747
846867 191732
846867 201747
846868 191732
846868 201747
My lastest SQL (getting more bloated and convoluted):
declare @.tot int
select @.tot = count(*) from _IntOrder where OrderID = 846863
select distinct i.OrderID from _IntOrder i where i.OrderItemID in (select
OrderItemID from _IntOrder where OrderID = 846863) and i.OrderID <> 846863
group by i.OrderID
having count(i.OrderItemID) = count(*)
I was hoping that this query would match the sets of Orders with the same
number of orderitems as 846863, where the sets were in 864863's membership,
i.e. identical orderitems, identical numbers of orderitems. As you can see
from the results this is not the case, I get 846864 as a match even though i
t
actually has an extra OrderItem - 201747.
Anyway, any help you can provide is greatly appreciated.
Kind regards,
mk
"Adam Machanic" wrote:

> Could you post DDL, sample data, and sample output?
> http://www.aspfaq.com/5006
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "mk" <mk@.discussions.microsoft.com> wrote in message
> news:E85B6C9F-EC19-4632-9E79-40ABE570AED3@.microsoft.com...
> to
> and
> groups
> can
> OrderItems
> a
>
>|||I think this should do it:
select o2.orderid
from orderhistory o1
full join orderhistory o2 on o1.orderid = 846863
and o2.orderitemid = o1.orderitemid
and o2.orderid <> o1.orderid
group by o2.orderid
having count(o1.orderitemid) = count(o2.orderitemid)
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"mk" <mk@.discussions.microsoft.com> wrote in message
news:5A0AFFDF-86D3-423F-A03A-A9FD40AB7448@.microsoft.com...
> CREATE TABLE [dbo].[OrderHistory] (
> [OrderID] [int] NOT NULL ,
> [OrderItemID] [int] NOT NULL
> ) ON [defgrp]
> GO
> There are other metadata columns but they are not relevant to this
question.
> A sample of the data I'm using is:
> OrderID OrderItemID
> 846863 191731
> 846863 201746
> 846864 191731
> 846864 201746
> 846864 201747
> 846865 191731
> 846865 201746
> 846866 191732
> 846866 201747
> 846867 191732
> 846867 201747
> 846868 191732
> 846868 201747
> My lastest SQL (getting more bloated and convoluted):
> declare @.tot int
> select @.tot = count(*) from _IntOrder where OrderID = 846863
> select distinct i.OrderID from _IntOrder i where i.OrderItemID in (select
> OrderItemID from _IntOrder where OrderID = 846863) and i.OrderID <> 846863
> group by i.OrderID
> having count(i.OrderItemID) = count(*)
> I was hoping that this query would match the sets of Orders with the same
> number of orderitems as 846863, where the sets were in 864863's
membership,
> i.e. identical orderitems, identical numbers of orderitems. As you can
see
> from the results this is not the case, I get 846864 as a match even though
it
> actually has an extra OrderItem - 201747.
> Anyway, any help you can provide is greatly appreciated.
> Kind regards,
> mk
> "Adam Machanic" wrote:
>
table
need
orders,
I
I'm
as
from|||Hi,
Thanks for the response. Unfortunately this approach returns 846863 and
846865 as matches for 846864. The solution below is actually working for al
l
combinations, its just damn ugly, if anyone has any suggestions to clean it
up and improve performance or efficiency I'd very much appreciate it:
declare @.Order int, @.num int
declare @.vals table(ID1 int)
select @.Order = 846866
insert @.vals (ID1) select orderitemid from orderhistory where orderid =
@.Order
select @.num = count(*) from @.vals
select o.orderid from orderhistory o full outer join @.vals v on
o.orderitemid = v.id1
where o.orderid <> @.Order
group by o.orderid having count(o.orderitemid) = @.num and count(id1) = @.nu
m
Regards,
mk
"Adam Machanic" wrote:

> I think this should do it:
>
> select o2.orderid
> from orderhistory o1
> full join orderhistory o2 on o1.orderid = 846863
> and o2.orderitemid = o1.orderitemid
> and o2.orderid <> o1.orderid
> group by o2.orderid
> having count(o1.orderitemid) = count(o2.orderitemid)
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "mk" <mk@.discussions.microsoft.com> wrote in message
> news:5A0AFFDF-86D3-423F-A03A-A9FD40AB7448@.microsoft.com...
> question.
> membership,
> see
> it
> table
> need
> orders,
> I
> I'm
> as
> from
>
>|||Here is a method I believe works... I had to re-read some texts on
Relational Division to get back into the right mindset. It's definitely a
rough thing to get your head around! I recommend you check out this post by
Joe Celko:
http://groups-beta.google.com/group...b1b2c
bb
To solve your problem, I plugged in the Exact Division pattern he provides:
SELECT o1.orderid
FROM orderhistory AS o1
LEFT OUTER JOIN
orderhistory AS o2
ON o1.orderitemid = o2.orderitemid
and o2.orderid = 846863
GROUP BY o1.orderid
HAVING COUNT(o1.orderid) = (SELECT COUNT(orderitemid) FROM orderhistory
where orderid=846863)
AND COUNT(o2.orderitemid) = (SELECT COUNT(orderitemid) FROM orderhistory
where orderid=846863)
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"mk" <mk@.discussions.microsoft.com> wrote in message
news:286E524E-8D3C-4FDF-A972-E069B76AB01E@.microsoft.com...
> Hi,
> Thanks for the response. Unfortunately this approach returns 846863 and
> 846865 as matches for 846864. The solution below is actually working for
all
> combinations, its just damn ugly, if anyone has any suggestions to clean
it
> up and improve performance or efficiency I'd very much appreciate it:
> declare @.Order int, @.num int
> declare @.vals table(ID1 int)
> select @.Order = 846866
> insert @.vals (ID1) select orderitemid from orderhistory where orderid =
> @.Order
> select @.num = count(*) from @.vals
> select o.orderid from orderhistory o full outer join @.vals v on
> o.orderitemid = v.id1
> where o.orderid <> @.Order
> group by o.orderid having count(o.orderitemid) = @.num and count(id1) =
@.num
>
> Regards,
> mk
>
> "Adam Machanic" wrote:
>
(select
846863
same
can
though
orderitems
we
typical
how
problem
back
OrderItemID
match.|||Thanks Adam, you're a star. That worked a treat (with a minor mod to preven
t
return of original OrderID), and thankfully avoids the hideous, horrendous
and shamefully embarassing use of the temporary table datatype!!!
Thanks also for the link, its certainly not easy to get good information on
these kind of subjects.
regards,
mk
"Adam Machanic" wrote:

> Here is a method I believe works... I had to re-read some texts on
> Relational Division to get back into the right mindset. It's definitely a
> rough thing to get your head around! I recommend you check out this post
by
> Joe Celko:
> http://groups-beta.google.com/group...b1b
2cbb
> To solve your problem, I plugged in the Exact Division pattern he provides
:
>
> SELECT o1.orderid
> FROM orderhistory AS o1
> LEFT OUTER JOIN
> orderhistory AS o2
> ON o1.orderitemid = o2.orderitemid
> and o2.orderid = 846863
> GROUP BY o1.orderid
> HAVING COUNT(o1.orderid) = (SELECT COUNT(orderitemid) FROM orderhistory
> where orderid=846863)
> AND COUNT(o2.orderitemid) = (SELECT COUNT(orderitemid) FROM orderhistor
y
> where orderid=846863)
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "mk" <mk@.discussions.microsoft.com> wrote in message
> news:286E524E-8D3C-4FDF-A972-E069B76AB01E@.microsoft.com...
> all
> it
> @.num
> (select
> 846863
> same
> can
> though
> orderitems
> we
> typical
> how
> problem
> back
> OrderItemID
> match.
>
>

Dedupe Query

Below, I have some T-SQL to create a table, add some sample records and do a dedupe query. This all works but performance is poor on large data sets and I was wondering if someone had any optimization tips.

Thanks to blindman for helping me develop this version.

Some background: the staging table has undeduped records about users. I want to dedupe and get the record with the most fields. I don't want to mix fields from different records. The staging table is currently unindexed but that can be changed.

CREATE TABLE StagingRecords
(
EmailAddress VARCHAR(75) NULL,
FirstName VARCHAR(255) NULL,
LastName VARCHAR(255) NULL,
StreetAddress VARCHAR(255) NULL,
City VARCHAR(255) NULL,
State VARCHAR(255) NULL,
ZipCode VARCHAR(255) NULL,
RecordID INT IDENTITY NOT NULL
)

INSERT INTO StagingRecords (EmailAddress, FirstName, LastName, StreetAddress, City, State, ZipCode)
VALUES ('usera@.hotmail.com', 'John', 'Doe', NULL, NULL, NULL, NULL)
INSERT INTO StagingRecords (EmailAddress, FirstName, LastName, StreetAddress, City, State, ZipCode)
VALUES ('usera@.hotmail.com', 'Abe', 'Abelman', NULL, NULL, 'MI', NULL)
INSERT INTO StagingRecords (EmailAddress, FirstName, LastName, StreetAddress, City, State, ZipCode)
VALUES ('usera@.hotmail.com', 'Zach', 'Zedcynsky', NULL, NULL, 'TX', NULL)
INSERT INTO StagingRecords (EmailAddress, FirstName, LastName, StreetAddress, City, State, ZipCode)
VALUES ('usera@.hotmail.com', 'Mary', 'Jane', NULL, NULL, NULL, NULL)

INSERT INTO StagingRecords (EmailAddress, FirstName, LastName, StreetAddress, City, State, ZipCode)
VALUES ('zzz@.yahoo.com', 'Cletus', 'Van Damme', NULL, NULL, NULL, NULL)
INSERT INTO StagingRecords (EmailAddress, FirstName, LastName, StreetAddress, City, State, ZipCode)
VALUES ('zzz@.yahoo.com', 'Alfonse', 'Ackbar', NULL, NULL, 'AL', '12345')
INSERT INTO StagingRecords (EmailAddress, FirstName, LastName, StreetAddress, City, State, ZipCode)
VALUES ('zzz@.yahoo.com', 'Zoom', 'Zuckerman', NULL, NULL, 'NJ', '54321')
INSERT INTO StagingRecords (EmailAddress, FirstName, LastName, StreetAddress, City, State, ZipCode)
VALUES ('zzz@.yahoo.com', 'Mary', 'Jane', NULL, 'Springfield', NULL, NULL)

SELECT
NULLCountTable.MinNULLCount, IDTable.TargetRecordID,
StagingRecords.EmailAddress, StagingRecords.FirstName, StagingRecords.LastName, StagingRecords.StreetAddress, StagingRecords.City, StagingRecords.State, StagingRecords.ZipCode
FROM
(SELECT EmailAddress
, MIN(CASE WHEN StagingRecords.FirstName IS NULL THEN 1 ELSE 0 END
+ CASE WHEN StagingRecords.LastName IS NULL THEN 1 ELSE 0 END
+ CASE WHEN StagingRecords.StreetAddress IS NULL THEN 1 ELSE 0 END
+ CASE WHEN StagingRecords.City IS NULL THEN 1 ELSE 0 END
+ CASE WHEN StagingRecords.State IS NULL THEN 1 ELSE 0 END
+ CASE WHEN StagingRecords.ZipCode IS NULL THEN 1 ELSE 0 END) AS MinNULLCount
FROM StagingRecords
GROUP BY EmailAddress) AS NULLCountTable
INNER JOIN
(SELECT Min(RecordID) AS TargetRecordID, EmailAddress
, (CASE WHEN StagingRecords.FirstName IS NULL THEN 1 ELSE 0 END
+ CASE WHEN StagingRecords.LastName IS NULL THEN 1 ELSE 0 END
+ CASE WHEN StagingRecords.StreetAddress IS NULL THEN 1 ELSE 0 END
+ CASE WHEN StagingRecords.City IS NULL THEN 1 ELSE 0 END
+ CASE WHEN StagingRecords.State IS NULL THEN 1 ELSE 0 END
+ CASE WHEN StagingRecords.ZipCode IS NULL THEN 1 ELSE 0 END) AS NULLCount
FROM StagingRecords
GROUP BY EmailAddress, (CASE WHEN StagingRecords.FirstName IS NULL THEN 1 ELSE 0 END
+ CASE WHEN StagingRecords.LastName IS NULL THEN 1 ELSE 0 END
+ CASE WHEN StagingRecords.StreetAddress IS NULL THEN 1 ELSE 0 END
+ CASE WHEN StagingRecords.City IS NULL THEN 1 ELSE 0 END
+ CASE WHEN StagingRecords.State IS NULL THEN 1 ELSE 0 END
+ CASE WHEN StagingRecords.ZipCode IS NULL THEN 1 ELSE 0 END)) AS IDTable
ON (NULLCountTable.EmailAddress = IDTable.EmailAddress AND NULLCountTable.MinNULLCount = IDTable.NULLCount)
INNER JOIN StagingRecords ON (StagingRecords.EmailAddress = IDTable.EmailAddress AND StagingRecords.RecordID = IDTable.TargetRecordID)This has been running on an undeduped table of 170 million records for over 2.5 hours and has yet to output a single row.

Recently, people in this forum said I should use set based solutions such as this over cursors. I should get dramatic/exponential performance gains. And if I wasn't seeing that, then I wasn't doing it right. Well, I must not be doing this right so I'm asking for help.

This seems like it must do extra logic and sorting that the cursor based code doesn't have to do. For example, the cursor code doesn't need unique staging record IDs and never has to join on them or perform a fraction of the joining of this query.

The provided SQL should be say to run and experiment with on a tempdb.

Any help is much appreciated.|||There are probably more efficient solutions, but this approach is about 4 times faster than blindmans more complex query
if you build the view and indexes (which may make the whole thing a wash in the end)

Also, you suggested that your cursor solution appeared faster. It may return some rows faster than blindmans query, but
his suggestion is still fairly efficient. It will read the table 2-3 times but iterating through each row with a cursor,
even if you read each row only once, will still be MUCH SLOWER to complete the entire process.

SET CONCAT_NULL_YIELDS_NULL ON
SET ARITHABORT ON

--Create a view with a calculated colum for count of null records
CREATE VIEW v_StagingRecords WITH SCHEMABINDING AS
SELECT EmailAddress,
CASE WHEN FirstName IS NULL THEN 1 ELSE 0 END
+ CASE WHEN LastName IS NULL THEN 1 ELSE 0 END
+ CASE WHEN StreetAddress IS NULL THEN 1 ELSE 0 END
+ CASE WHEN City IS NULL THEN 1 ELSE 0 END
+ CASE WHEN State IS NULL THEN 1 ELSE 0 END
+ CASE WHEN ZipCode IS NULL THEN 1 ELSE 0 END NullCount, RecordID
FROM dbo.StagingRecords

--3 seconds. 1700 reads
CREATE UNIQUE CLUSTERED INDEX vSR_IDX on v_StagingRecords (RecordID)
CREATE INDEX v_EN_IDX on v_StagingRecords (EmailAddress, NullCount)

--100,000 row table. 0.6 seconds, 800 reads vs 2.8 seconds, 2200 reads for original query.
SELECT * FROM v_StagingRecords WHERE RecordID IN
(
SELECT (SELECT TOP 1 RecordID FROM v_StagingRecords WITH (NOEXPAND)
WHERE EmailAddress = o.EmailAddress Order BY NullCount ASC) RecordID
FROM v_StagingRecords o WITH (NOEXPAND)
GROUP BY EmailAddress
)
ORDER BY EmailAddress|||I loaded 17 million rows into a test table and built the view, indexes, and ran the query. It took 16 minutes in total, 101 seconds for the query itself. I tried blindmans query on the same data it it took 113, seconds so scrap my suggestion. I might play with it a little more and see if I can come up with anything better.

Dedub query

I am trying to join two table using a primary key, my problem is that one table has multiple listing of that primary key, I only want to join to the primary key once. Can anyone show me how this can be done?

Table1

acct_no sale_am tran_cd

123 50 2

123 54 1

113 20 9

124 30 7

Table2

acct_no exp_am res_am

123 50 20

113 24 30

124 60 10

What I need:

acct_no sum(sale_am) sum(exp_am) sum(res_am)

123 104 50 20

113 20 24 30

124 30 60 10

Thanks

There are several possibilities. And each one could provide different resultsets.

Please let us know what is your expected output and we can better assist you.

|||

Assuming you want the latest tran_cd value, you can just use a derived table and the ROW_NUMBER() windowed function:

select *

from (select acct_no, sale_am, row_number() over (partition by acct_no order by tran_cd desc) as rowNbr

from table1) as table1
join table2
on table1.acct_no = table2.acct_no

and table1.rowNbr = 1

If this is something that you do often, especially something that needs a lot of performance, I might consider implementing a current_row_flag in your table to denote the row you want to usually use (especially if those rows don't change much)

|||

Code Snippet

createtable #Table1( acct_no int, sale_am money, tran_cd int)

insertinto #Table1

select 123, 50, 2

union allselect 123, 54, 1

union allselect 113, 20, 9

union allselect 124, 30, 7

createtable #Table2( acct_no int, exp_am money, res_am money)

insertinto #Table2

select 123, 50, 20

union allselect 113, 24, 30

union allselect 124, 60, 10

select t1.acct_no,sum(t1.sale_am)as sale_am,

sum(t2.exp_am)as exp_am,sum(t2.res_am)as res_am

from #Table1 t1

innerjoin #Table2 t2

on t1.acct_no = t2.acct_no

groupby t1.acct_no

sql

Decryption within an application

I need to encrypt one column of data in a single table and I pretty much
have all the operations figured out, including maintaining both the
encrpyted data and a one way hash for searches. I have a view which
decrypts the data properly when the symmetric key has been opened (and
obviously returns null when the key is not open).
I want the view to return the decrypted data only when the user is accessing
the database from a single application. This application maintains a single
database connection per session. My thought was to open the key when the
database connection is established by the application and close it when the
application exits, thereby granting access only through the application. Is
that an acceptable practice?
If I do that, should I protect the key with a password that is then compiled
in the application so that I can open the key? This means that every
installation will have a key protected by the same password. Or is there a
better way to do that?
Thanks for any help."Chuck Reif" <creif@.nomail.metopera.org> wrote in message
news:uvmlanAmHHA.4852@.TK2MSFTNGP03.phx.gbl...
>I need to encrypt one column of data in a single table and I pretty much
>have all the operations figured out, including maintaining both the
>encrpyted data and a one way hash for searches. I have a view which
>decrypts the data properly when the symmetric key has been opened (and
>obviously returns null when the key is not open).
> I want the view to return the decrypted data only when the user is
> accessing the database from a single application. This application
> maintains a single database connection per session. My thought was to
> open the key when the database connection is established by the
> application and close it when the application exits, thereby granting
> access only through the application. Is that an acceptable practice?
> If I do that, should I protect the key with a password that is then
> compiled in the application so that I can open the key? This means that
> every installation will have a key protected by the same password. Or is
> there a better way to do that?
> Thanks for any help.
Well, when the key is opened it's specific to a session. So you could have
several sessions opening up the same key simultaneously and I wouldn't think
you'd encounter any problems. Of course you will probably want to do some
thorough testing to be sure, and also make sure you don't take a performance
hit there. I wouldn't recommend storing the key hard-coded in your
application. How about using the Automatic Key Management feature of SQL
2005? The only real downside to it is that all sysadmins can then decrypt
your data (if that's a concern for you - it is for some folks).|||I'm not neccesarily opposed to the sysadmin being able to decrypt the data,
but I don't want any other user outside of the application to have access to
the data. So what I can't figure out (even with SS key management) is how
to open the key only when connecting from the application, unless I compile
a password into the code.
Any thoughts on that would be helpful.
Thanks.
"Mike C#" <xyz@.xyz.com> wrote in message
news:uH0ftcPmHHA.4624@.TK2MSFTNGP04.phx.gbl...
> "Chuck Reif" <creif@.nomail.metopera.org> wrote in message
> news:uvmlanAmHHA.4852@.TK2MSFTNGP03.phx.gbl...
> Well, when the key is opened it's specific to a session. So you could
> have several sessions opening up the same key simultaneously and I
> wouldn't think you'd encounter any problems. Of course you will probably
> want to do some thorough testing to be sure, and also make sure you don't
> take a performance hit there. I wouldn't recommend storing the key
> hard-coded in your application. How about using the Automatic Key
> Management feature of SQL 2005? The only real downside to it is that all
> sysadmins can then decrypt your data (if that's a concern for you - it is
> for some folks).
>|||With automatic key management you should be able to connect to the
application and open the symmetric keys without a password. You can use
GRANT to grant permissions to users on your keys, certificates, etc. Here's
an article with some samples that demonstrate encryption/decryption without
passwords, thanks to automatic key management:
[url]http://www.sqlservercentral.com/columnists/mcoles/sql2005symmetricencryption.asp[/
url]
You might also want to look into the DecryptByKeyAutoAsymKey and
DecryptByKeyAutoCert functions that combine the DecryptBy... functions with
OPEN SYMMETRIC KEY automatically.
"Chuck Reif" <creif@.nomail.metopera.org> wrote in message
news:%23GeqmRWmHHA.596@.TK2MSFTNGP06.phx.gbl...
> I'm not neccesarily opposed to the sysadmin being able to decrypt the
> data, but I don't want any other user outside of the application to have
> access to the data. So what I can't figure out (even with SS key
> management) is how to open the key only when connecting from the
> application, unless I compile a password into the code.
> Any thoughts on that would be helpful.
> Thanks.
> "Mike C#" <xyz@.xyz.com> wrote in message
> news:uH0ftcPmHHA.4624@.TK2MSFTNGP04.phx.gbl...
>|||Thanks so much for your help, but I must be dense. If I grant permission to
the users or use automatic key management, then it seems to me that the data
can be encrypted outside of my application by a non-sa user. If I only want
the application to display the unencrypted data, I can't see how this
automatic approach works.
That is why I took the approach of having the application open the key.
Sort of like the old application-role security.
But I would love to find a better way.
"Mike C#" <xyz@.xyz.com> wrote in message
news:Od0T8%23amHHA.4772@.TK2MSFTNGP05.phx.gbl...
> With automatic key management you should be able to connect to the
> application and open the symmetric keys without a password. You can use
> GRANT to grant permissions to users on your keys, certificates, etc.
> Here's an article with some samples that demonstrate encryption/decryption
> without passwords, thanks to automatic key management:
> http://www.sqlservercentral.com/col...ion.asp

> You might also want to look into the DecryptByKeyAutoAsymKey and
> DecryptByKeyAutoCert functions that combine the DecryptBy... functions
> with OPEN SYMMETRIC KEY automatically.
> "Chuck Reif" <creif@.nomail.metopera.org> wrote in message
> news:%23GeqmRWmHHA.596@.TK2MSFTNGP06.phx.gbl...
>|||"Chuck Reif" <creif@.nomail.metopera.org> wrote in message
news:%23C1qbs$mHHA.668@.TK2MSFTNGP05.phx.gbl...
> Thanks so much for your help, but I must be dense. If I grant permission
> to the users or use automatic key management, then it seems to me that the
> data can be encrypted outside of my application by a non-sa user. If I
> only want the application to display the unencrypted data, I can't see how
> this automatic approach works.
Anyone who has the username and password used by the application to log into
the database would have the ability to decrypt the encrypted data.
Alternatively, if a password is stored in the application, anyone with a hex
editor could decrypt the encrypted data outside of the application. The
only way I can think of to force decryption only through the application
would be to encrypt only in the application. But then you take on the
responsibility of encryption key management yourself. I don't know of any
magic bullet to ensure that data encrypted using SQL Server can only be
accessed via a specific front-end application interface.

> That is why I took the approach of having the application open the key.
> Sort of like the old application-role security.
Anyone with a hex editor could conceivably locate a password stored in an
application and use it to decrypt data. Linking it to a specific Windows
login puts the burden of encryption key management back on the operating
system. That's basically the main difference; do you want to manage your
own passwords, or do you want to let Windows and SQL Server manage your
passwords?

> But I would love to find a better way.
Biometrics?

Decryption Question

Hi,

I am encrypting a column in a table. When I decrypt, everything is great if the data contained in column is less than 30 chars. If it has more than 30 chars, I still get 30 chars decrypted. Not sure what I am doing wrong. Below are my details:

OPEN SYMMETRIC KEY sk_XEncryption
DECRYPTION BY CERTIFICATE cert_sk_X

SELECT ISNULL(CONVERT(NVARCHAR,DECRYPTBYKEY(columnA)),'')
FROM table1

CLOSE SYMMETRIC KEY sk_XEncryption

Additional details:

"columnA" above is of VARBINARY(4000) datatype. I have enven tried changing the column to VARBINARY(MAX). The data is ecrypted with a symmetric key (algorithm = triple_des ). Any tips are appreciated. TIA.

Does it help if you specify the length for the NVARCHAR output?
For example:
SELECT ISNULL(CONVERT(NVARCHAR(255),DECRYPTBYKEY(columnA)),'')
FROM table1
|||That helped!!! Thanks a lot.

Decrypting Password of SysxLogins Table - SQL Database.

Hi,

Is there any way of decrypting password value stored in sysxlogins table of SQL database?

Thx in Adv

This is a SQL Server 2000 table that is no longer available in SQL Server 2005. The password value normally contains a SHA1 hash of the password (unless the password is for a login created in a previous version and maintained through upgrade to SQL Server 2000). If the value is NULL and the login is a SQL login, it means that the password is empty. Otherwise, to determine the password, you would have to do a brute force attack on the hash. If the password is weak, a brute force attack will be quite successful, so it's very important to have strong passwords.

In SQL Server 2005, password strength can be enforced on Windows 2003 systems to follow the Windows password policy settings. Also, these password hashes can only be seen by a sysadmin now. Furthermore, empty passwords cannot be as easily identified, they have a hash as well rather than showing up as NULL.

Thanks

Laurentiu

sql