R

How do i replace that character in a derived column ?

Some rows have that character in one or more columns. If i just write this

column == "" ? Unknown : column

where the character is inside "" (can't write it here) the task will just succes without ever doing anything. The output says something like "the dataflow task had no tasks.....", which seems like a bug.

Use a conditional statement.

If [column] contains this value, replace it with another, else leave the value alone:

[column] == "A" ? "B" : [column]|||

i know but it's not a common character. Look at the Subject of this question!!!

It's a square character -> <-

It's a non XML valid character

|||

I think thats a new line character.

If you are using an OLEDB source for your data, you can just do a trim() on the column to get rid of it.

|||

If you know the Unicode character value for it, you can use an escape sequence:

"\xhhhh"

where hhhh is the Unicode character value.

Thank
Mark

|||trim() doesn't works. It's doesn't remove the character!|||

hmm and if it's not a unicode character ?

Try putting this in a derived comlumn

REPLACE(TRIM(TXT)," ","")

the task will then complete with this in the output.

Warning: 0x80047034 at Data Flow Task, DTS.Pipeline: The DataFlow task has no components. Add components or remove the task.

|||

Use the REPLACE function, and the Unicode escape sequenece syntax Mark described.

It is a unicode character, as all comparisons are done as Unicode inside the SSIS expression parser, it cannot be anything else as far as SSIS is concerned. You need to find out what that is, and specify it in the REPLACE.

|||

Well but if the character > < is used in a replace within a dataflowtask in ssis, it automatic removes whatever flow you might have build inside that dataflow. In my opinion that seems like a bug..

if you want to do a replace in a sql task, you can't use a direct input (the sql task will then complete as if nothing was typed inside the task). You have to use a file connection for the query, so it seems like this character is the character from hell... :-)

So if you don't have the escape sequenece for this character (can't find it, since you can't search for it :-) ) you'll have to load the entire table to a temp table and do an ordinary replace in a sql query

|||

jam281 wrote:

it automatic removes whatever flow you might have build inside that dataflow.

Can you elaborate on exactly what you mean by "whatever flow you might have build".

-Jamie

|||Download any one of the many free hex editors on the Internet and open up a line of your source in it. Use the hex editor to find the hex value of the character in question. Go from there in your replace function.|||

"Can you elaborate on exactly what you mean by "whatever flow you might have build".

Allright. Try to create a new package - Add a dataflow and open it. Inside the dataflow create an oledb source - point to a table and map it.

Put in a derived column and a do a replace on one of the column like:

CENTRE == " " ? "Unknown" : CENTRE

put in a ole db destination - connect all tree

try to run it and it completes without doing anything. save the package and close the project.

Open the project again and your dataflow is now suddently empty.

The same problem apply if you have copied a sql query inside a sql task and it contains somewhere. The sql task will the execute witout doing anything...

|||

hmm found out that its a char(2) character.

select char(2)

gives that character. Now how do you replace char(2) in a derived column

|||

Let me know if using Unicode escape sequence as follows works for you:

CENTRE == "\x0002" ? "Unknown" : CENTRE

Thanks
Mark

|||

Mark Durley wrote:

Let me know if using Unicode escape sequence as follows works for you:

CENTRE == "\x0002" ? "Unknown" : CENTRE

Thanks
Mark

It worked thanks!!!

R

How do i replace that character in a derived column ?

Some rows have that character in one or more columns. If i just write this

column == "" ? Unknown : column

where the character is inside "" (can't write it here) the task will just succes without ever doing anything. The output says something like "the dataflow task had no tasks.....", which seems like a bug.

Use a conditional statement.

If [column] contains this value, replace it with another, else leave the value alone:

[column] == "A" ? "B" : [column]|||

i know but it's not a common character. Look at the Subject of this question!!!

It's a square character -> <-

It's a non XML valid character

|||

I think thats a new line character.

If you are using an OLEDB source for your data, you can just do a trim() on the column to get rid of it.

|||

If you know the Unicode character value for it, you can use an escape sequence:

"\xhhhh"

where hhhh is the Unicode character value.

Thank
Mark

|||trim() doesn't works. It's doesn't remove the character!|||

hmm and if it's not a unicode character ?

Try putting this in a derived comlumn

REPLACE(TRIM(TXT)," ","")

the task will then complete with this in the output.

Warning: 0x80047034 at Data Flow Task, DTS.Pipeline: The DataFlow task has no components. Add components or remove the task.

|||

Use the REPLACE function, and the Unicode escape sequenece syntax Mark described.

It is a unicode character, as all comparisons are done as Unicode inside the SSIS expression parser, it cannot be anything else as far as SSIS is concerned. You need to find out what that is, and specify it in the REPLACE.

|||

Well but if the character > < is used in a replace within a dataflowtask in ssis, it automatic removes whatever flow you might have build inside that dataflow. In my opinion that seems like a bug..

if you want to do a replace in a sql task, you can't use a direct input (the sql task will then complete as if nothing was typed inside the task). You have to use a file connection for the query, so it seems like this character is the character from hell... :-)

So if you don't have the escape sequenece for this character (can't find it, since you can't search for it :-) ) you'll have to load the entire table to a temp table and do an ordinary replace in a sql query

|||

jam281 wrote:

it automatic removes whatever flow you might have build inside that dataflow.

Can you elaborate on exactly what you mean by "whatever flow you might have build".

-Jamie

|||Download any one of the many free hex editors on the Internet and open up a line of your source in it. Use the hex editor to find the hex value of the character in question. Go from there in your replace function.|||

"Can you elaborate on exactly what you mean by "whatever flow you might have build".

Allright. Try to create a new package - Add a dataflow and open it. Inside the dataflow create an oledb source - point to a table and map it.

Put in a derived column and a do a replace on one of the column like:

CENTRE == " " ? "Unknown" : CENTRE

put in a ole db destination - connect all tree

try to run it and it completes without doing anything. save the package and close the project.

Open the project again and your dataflow is now suddently empty.

The same problem apply if you have copied a sql query inside a sql task and it contains somewhere. The sql task will the execute witout doing anything...

|||

hmm found out that its a char(2) character.

select char(2)

gives that character. Now how do you replace char(2) in a derived column

|||

Let me know if using Unicode escape sequence as follows works for you:

CENTRE == "\x0002" ? "Unknown" : CENTRE

Thanks
Mark

|||

Mark Durley wrote:

Let me know if using Unicode escape sequence as follows works for you:

CENTRE == "\x0002" ? "Unknown" : CENTRE

Thanks
Mark

It worked thanks!!!

Quick TSQL?

Hey all... I have a few tables that I am joining and need to know how to set a value from the return to a different column:

example:

SELECT

Prospect.ProspectName AS P1, AccountShipTo.ShipToName AS [Account Name], ProposalHeader.PropHCity AS City, ProposalHeader.PropHState AS State,

ProposalHeader.PropHNumb AS [Proposal ID], ProposalHeader.PropHRevNumb AS Rev, ProposalHeader.PropHCreateDate AS [Creation Date],

ProposalHeader.update_timestamp AS [Last Edit Date]

FROM ProposalHeader LEFT OUTER JOIN

AccountShipTo ON ProposalHeader.PropHShipTo = LTRIM(AccountShipTo.ShipToCust) LEFT OUTER JOIN

Prospect ON ProposalHeader.PropHBillTo = LTRIM(Prospect.ProspectNumb)

WHERE (ProposalHeader.Alias = N'billb')

GROUP BY ProposalHeader.PropHRevNumb, AccountShipTo.ShipToName, ProposalHeader.PropHCity, ProposalHeader.PropHState,

ProposalHeader.PropHNumb, ProposalHeader.PropHCreateDate, ProposalHeader.update_timestamp, Prospect.ProspectName

ORDER BY [Proposal ID]

I need P1 value (Test - Timberline Corp)to be in the Account Name column (NULL)... any ideas?

P1 Account Name City State Proposal ID Rev Creation Date Last Edit Date

NULL Samples, Inc. High Point NC Samples1 1 2007-07-25 2007-07-30

Test - Timberline Corp NULL Rapid City SD test1 1 2007-07-31 2007-07-31

(2 row(s) affected)

Any help would be appreciated... thanks!

Have you tried coalesce(AccountShipTo.ShipToName, Prospect.ProspectName) or isnull(AccountShipTo.ShipToName, Prospect.ProspectName)
|||

Very coo! Thanks...

Did this....

SELECT COALESCE (AccountShipTo.ShipToName, Prospect.ProspectName) AS [Account Name]

Quick SQL question...

I'm trying to change every value of a certain column in a table by adding an
extra character to it:
UPDATE event_details
SET code_event = (SELECT code_event + '0' FROM event_details)
But I know I need some sort of join to do this but I'm not sure what. Can
any one advise please?
--
Cheers,
elzikoelziko
CREATE TABLE #Temp
(
Col VARCHAR(10)
)
GO
INSERT INTO #Temp VALUES ('A')
INSERT INTO #Temp VALUES ('B')
INSERT INTO #Temp VALUES ('C')
GO
SELECT * FROM #Temp
GO
UPDATE #Temp SET Col=Col+'0'
"elziko" <elziko@.NOTSPAMMINGyahoo.co.uk> wrote in message
news:eQLPHRt#DHA.2808@.TK2MSFTNGP10.phx.gbl...
> I'm trying to change every value of a certain column in a table by adding
an
> extra character to it:
> UPDATE event_details
> SET code_event = (SELECT code_event + '0' FROM event_details)
> But I know I need some sort of join to do this but I'm not sure what. Can
> any one advise please?
> --
> Cheers,
> elziko
>|||> CREATE TABLE #Temp
I need to do this without using CREATE TABLE
Any ideas?
--
Cheers,
elziko|||elziko
I was creating table for repro your question because you did not post DDL.
Just use UPDATE Tablename.............
"elziko" <elziko@.NOTSPAMMINGyahoo.co.uk> wrote in message
news:#BHQ$tt#DHA.2184@.TK2MSFTNGP12.phx.gbl...
> > CREATE TABLE #Temp
> I need to do this without using CREATE TABLE
> Any ideas?
> --
> Cheers,
> elziko
>|||Declare @.NewCharacter VarChar (20)
Declare @.OldCharcter VarChar (20)
Declare @.AddCharacter VarChar (20)
Set @.AddCharacter = '0'
Select @.OldCharcter = code_event FROM event_details
Select @.NewCharacter = @.OldCharcter + @.AddCharacter
Print @.NewCharacter
Update event_details set code_event = @.NewCharacter
"elziko" <elziko@.NOTSPAMMINGyahoo.co.uk> wrote in message
news:eQLPHRt%23DHA.2808@.TK2MSFTNGP10.phx.gbl...
> I'm trying to change every value of a certain column in a table by adding
an
> extra character to it:
> UPDATE event_details
> SET code_event = (SELECT code_event + '0' FROM event_details)
> But I know I need some sort of join to do this but I'm not sure what. Can
> any one advise please?
> --
> Cheers,
> elziko
>|||Thanks, but how would I only update the rows who are already only seven
characters long? Where would I put the where clause?
--
Cheers,
elziko|||Declare @.NewCharacter VarChar (20)
Declare @.OldCharcter VarChar (20)
Declare @.AddCharacter VarChar (20)
Set @.AddCharacter = '0'
Select @.OldCharcter = code_event FROM event_details where LEN(code_event)
= 7
Select @.NewCharacter = @.OldCharcter + @.AddCharacter
Print @.NewCharacter
Update event_details set code_event = @.NewCharacter where LEN(code_event) =7
I am not sure exactly what you are trying to achieve. The above code will
add a '0' to every code_event that is 7 characters long. If your 7
character/digit code_events are different then I would suggest using a
cursor.
Cheers,
Andre
"elziko" <elziko@.NOTSPAMMINGyahoo.co.uk> wrote in message
news:403b730c$0$21923$afc38c87@.news.easynet.co.uk...
> Thanks, but how would I only update the rows who are already only seven
> characters long? Where would I put the where clause?
> --
> Cheers,
> elziko
>|||> I am not sure exactly what you are trying to achieve. The above code will
> add a '0' to every code_event that is 7 characters long. If your 7
> character/digit code_events are different then I would suggest using a
> cursor.
Yeah thats exactly what I want to do and thats where I initially put the
WHERE clauses before replying to you! But I get the following error:
MED0017 0
Server: Msg 8152, Level 16, State 9, Line 13
String or binary data would be truncated.
The statement has been terminated.
When I do the print of the NewCharacter it seems to have a space in it but
the column I'm updating is only 8 chars long so it looks like this is the
problem? Where is that space coming from? Or is something else wrong here?
Thanks a lot for your help.
--
Cheers,
elziko

Quick SQL question...

I'm trying to change every value of a certain column in a table by adding an
extra character to it:
UPDATE event_details
SET code_event = (SELECT code_event + '0' FROM event_details)
But I know I need some sort of join to do this but I'm not sure what. Can
any one advise please?
Cheers,
elziko> CREATE TABLE #Temp
I need to do this without using CREATE TABLE
Any ideas?
Cheers,
elziko|||elziko
I was creating table for repro your question because you did not post DDL.
Just use UPDATE Tablename.............
"elziko" <elziko@.NOTSPAMMINGyahoo.co.uk> wrote in message
news:#BHQ$tt#DHA.2184@.TK2MSFTNGP12.phx.gbl...
> I need to do this without using CREATE TABLE
> Any ideas?
> --
> Cheers,
> elziko
>|||Declare @.NewCharacter VarChar (20)
Declare @.OldCharcter VarChar (20)
Declare @.AddCharacter VarChar (20)
Set @.AddCharacter = '0'
Select @.OldCharcter = code_event FROM event_details
Select @.NewCharacter = @.OldCharcter + @.AddCharacter
Print @.NewCharacter
Update event_details set code_event = @.NewCharacter
"elziko" <elziko@.NOTSPAMMINGyahoo.co.uk> wrote in message
news:eQLPHRt%23DHA.2808@.TK2MSFTNGP10.phx.gbl...
> I'm trying to change every value of a certain column in a table by adding
an
> extra character to it:
> UPDATE event_details
> SET code_event = (SELECT code_event + '0' FROM event_details)
> But I know I need some sort of join to do this but I'm not sure what. Can
> any one advise please?
> --
> Cheers,
> elziko
>|||Thanks, but how would I only update the rows who are already only seven
characters long? Where would I put the where clause?
Cheers,
elziko|||Declare @.NewCharacter VarChar (20)
Declare @.OldCharcter VarChar (20)
Declare @.AddCharacter VarChar (20)
Set @.AddCharacter = '0'
Select @.OldCharcter = code_event FROM event_details where LEN(code_event)
= 7
Select @.NewCharacter = @.OldCharcter + @.AddCharacter
Print @.NewCharacter
Update event_details set code_event = @.NewCharacter where LEN(code_event) =
7
I am not sure exactly what you are trying to achieve. The above code will
add a '0' to every code_event that is 7 characters long. If your 7
character/digit code_events are different then I would suggest using a
cursor.
Cheers,
Andre
"elziko" <elziko@.NOTSPAMMINGyahoo.co.uk> wrote in message
news:403b730c$0$21923$afc38c87@.news.easynet.co.uk...
> Thanks, but how would I only update the rows who are already only seven
> characters long? Where would I put the where clause?
> --
> Cheers,
> elziko
>|||> I am not sure exactly what you are trying to achieve. The above code will
> add a '0' to every code_event that is 7 characters long. If your 7
> character/digit code_events are different then I would suggest using a
> cursor.
Yeah thats exactly what I want to do and thats where I initially put the
WHERE clauses before replying to you! But I get the following error:
MED0017 0
Server: Msg 8152, Level 16, State 9, Line 13
String or binary data would be truncated.
The statement has been terminated.
When I do the print of the NewCharacter it seems to have a space in it but
the column I'm updating is only 8 chars long so it looks like this is the
problem? Where is that space coming from? Or is something else wrong here?
Thanks a lot for your help.
Cheers,
elziko

quick SELECT statement question

Hello all!
I have a date/time column on my table. How can I write a select statement
to pick only items from todays date? I looked, but can't find an answer.
SELECT * User FROM TABLE WHERE datecolumn = "todays date"
Thanks!
RudySELECT
*
FROM
Table
WHERE
CONVERT(CHAR(8), datecolumn, 112) = CONVERT(CHAR(8),
GETDATE(), 112)
Due to the time that is stored in a datatime datatype, you need to strip the
time out as above.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Rudy" <Rudy@.discussions.microsoft.com> wrote in message
news:5E70E4E8-EB91-4DBF-8489-5F9E3A5A204C@.microsoft.com...
> Hello all!
> I have a date/time column on my table. How can I write a select statement
> to pick only items from todays date? I looked, but can't find an answer.
> SELECT * User FROM TABLE WHERE datecolumn = "todays date"
> Thanks!
> Rudy|||Hi Mike!
WOW!! I wouild have never figured that out. Thanks!!
Rudy
"Mike Epprecht (SQL MVP)" wrote:

> SELECT
> *
> FROM
> Table
> WHERE
> CONVERT(CHAR(8), datecolumn, 112) = CONVERT(CHAR(8),
> GETDATE(), 112)
> Due to the time that is stored in a datatime datatype, you need to strip t
he
> time out as above.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Rudy" <Rudy@.discussions.microsoft.com> wrote in message
> news:5E70E4E8-EB91-4DBF-8489-5F9E3A5A204C@.microsoft.com...
>
>|||Hi
Or
SELECT
*
FROM
Table
WHERE
datecolumn >= (CONVERT(CHAR(8), GETDATE(), 112) + '
00:00:00.000'
AND datecolumn <= (CONVERT(CHAR(8), GETDATE(), 112) + '
23:59:59.997'
The 1st one can not use an index if that is the only predicate in the where
clause as each row needs to be evaluated.
The 2nd one could use an index, but make sure that you use ' 23:59:59.997'
and not ' 23:59:59.999' as .999 can not be represented in datetime, so it
rounds itself to 00:00:00.000, the next day
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Rudy" <Rudy@.discussions.microsoft.com> wrote in message
news:2202B61C-5A3A-4FEB-B0E4-6FC0480FB1EA@.microsoft.com...
> Hi Mike!
> WOW!! I wouild have never figured that out. Thanks!!
> Rudy
> "Mike Epprecht (SQL MVP)" wrote:
>|||In addition to Mike's comments, you might want to red more about the subject
at:
http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rudy" <Rudy@.discussions.microsoft.com> wrote in message
news:5E70E4E8-EB91-4DBF-8489-5F9E3A5A204C@.microsoft.com...
> Hello all!
> I have a date/time column on my table. How can I write a select statement
> to pick only items from todays date? I looked, but can't find an answer.
> SELECT * User FROM TABLE WHERE datecolumn = "todays date"
> Thanks!
> Rudy

Quick Question... How to use Default Values (after allowing NULL)

Hello,

I have a BIT column which accepts NULL values.

What would be a good method to allow an INSERT (or UPDATE) statement to insert NULL into this column but then automatically change the NULL to 0 (zero). In other words, test for NULLs after INSERT (or UPDATE) and change the value to 0 (zero).

Not exactly sure how to do this with a Trigger. Also, what is that [Formula] option used for (column properties in the Table Design view)... and would this apply with my problem?

Thanks,Look up CREATE TRIGGER in Books Online, and pay special attention to the INSERTED and DELETED virtual table concepts. Then within your trigger:

update YourTable
set YourValue = 0
from YourTable
inner join INSERTED on YourTable.PKEY = INSERTED.PKEY
where YourValue is null

But really, you should be doing your inserts through a stored procedure which uses ISNULL([NewValue], 0)|||Thx for the quick response.

I'll give it a shot.

Quick question about rowguid column

Is there any issue with assigning the primary key to a rowguid column?
Thoughts?
WBIt depends...
http://www.aspfaq.com/show.asp?id=2504
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"WB" <none> wrote in message news:OOk1gk2HFHA.2456@.TK2MSFTNGP09.phx.gbl...
> Is there any issue with assigning the primary key to a rowguid column?
> Thoughts?
> WB
>|||Yes....
1. Creates a VERY Wide Index that is inefficient (Compared to INT for
example)
2. IF this is also the Clustered Index (I recommend against), Then ALL
Indexes must propogate this GUID in their lookup tables
3. Clustering on GUID can cause page splits,etc
Company I recently worked at just added an identity column to all tables in
DB and clustered on the Ident instead of the GUID. Inserts of records sped
up tremendously. I cant quote the exact number of records, bu the "Process"
went form 17 minutes to literally seconds...
Food for thought
Some companies (My current employer for example) want to use a GUID type of
identifier as opposed to integers for whatever reason. So what they do, to
ensure that the GUID values are Monotonically increasing in value (to avoid
page splits), is the create their own custom "GUID" type data generator in
which the algorithm ensure incrementing values.
Greg Jackson
PDX, Oregon
"WB" <none> wrote in message news:OOk1gk2HFHA.2456@.TK2MSFTNGP09.phx.gbl...
> Is there any issue with assigning the primary key to a rowguid column?
> Thoughts?
> WB
>|||Cluster Index definition on surrogate keys are ALWAYS a bad idea, INT or
GUID. Usually, the Business Key the surrogate is proxiing for is a better
Cluster Index candidate.
Sincerely,
Anthony Thomas
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:uaC7za3HFHA.1948@.TK2MSFTNGP14.phx.gbl...
Yes....
1. Creates a VERY Wide Index that is inefficient (Compared to INT for
example)
2. IF this is also the Clustered Index (I recommend against), Then ALL
Indexes must propogate this GUID in their lookup tables
3. Clustering on GUID can cause page splits,etc
Company I recently worked at just added an identity column to all tables in
DB and clustered on the Ident instead of the GUID. Inserts of records sped
up tremendously. I cant quote the exact number of records, bu the "Process"
went form 17 minutes to literally seconds...
Food for thought
Some companies (My current employer for example) want to use a GUID type of
identifier as opposed to integers for whatever reason. So what they do, to
ensure that the GUID values are Monotonically increasing in value (to avoid
page splits), is the create their own custom "GUID" type data generator in
which the algorithm ensure incrementing values.
Greg Jackson
PDX, Oregon
"WB" <none> wrote in message news:OOk1gk2HFHA.2456@.TK2MSFTNGP09.phx.gbl...
> Is there any issue with assigning the primary key to a rowguid column?
> Thoughts?
> WB
>|||"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:uPDlrHwKFHA.3340@.TK2MSFTNGP14.phx.gbl...
> Cluster Index definition on surrogate keys are ALWAYS a bad idea, INT or
> GUID. Usually, the Business Key the surrogate is proxiing for is a better
> Cluster Index candidate.
There's no such thing as "usually". There are plenty of applications
for either type of clustering key -- and clustering on a sequential integer
can be especially beneficial in many situations. You should be very careful
with that assumption.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--|||Name one.
Sincerely,
Anthony Thomas
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:OYusbfwKFHA.3132@.TK2MSFTNGP12.phx.gbl...
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:uPDlrHwKFHA.3340@.TK2MSFTNGP14.phx.gbl...
> Cluster Index definition on surrogate keys are ALWAYS a bad idea, INT or
> GUID. Usually, the Business Key the surrogate is proxiing for is a better
> Cluster Index candidate.
There's no such thing as "usually". There are plenty of applications
for either type of clustering key -- and clustering on a sequential integer
can be especially beneficial in many situations. You should be very careful
with that assumption.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--|||"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:%23gGre3wKFHA.1176@.TK2MSFTNGP12.phx.gbl...
> Name one.
Inserting data.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--|||Yes, and Inserting Data in Business Key order shouldn't cause an inordinate
amount of page splits if you have your fill factors and database maintenance
routines set correctly. And, having the Cluster Index on the Business Key
will far outweigh any split issues compared to any scans you may encounter
and gather the covering aspects of the clustered index plus it is typlically
the Business Key that most range queries are conducted on.
Care to try again? Are you really telling me that page splits is the BEST
reason you can come up with? Please. Are you really telling me that was
the whole design goal with having Clustered Indexes over Heaps to begin
with? You've got to be kidding me.
Can I get a witness?
Sincerely,
Anthony Thomas
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:OuxM5KxKFHA.4052@.tk2msftngp13.phx.gbl...
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:%23gGre3wKFHA.1176@.TK2MSFTNGP12.phx.gbl...
> Name one.
Inserting data.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--|||"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:eCMwAS5KFHA.2860@.TK2MSFTNGP10.phx.gbl...
> Can I get a witness?
No. Never worked in a 24-7 (4-nines, and we're trying for 5 this year),
high-volume OLTP environment, have you? Good luck maintaining those fill
factors when you have no maintenence window.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--|||This is a multi-part message in MIME format.
--=_NextPart_000_08EF_01C52BAD.4FA001B0
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: 7bit
Would agree that the DBREINDEX can be troublesome in highly available
systems. But the fill factors should be static from a design perspective.
Also, the DBREINDEX is semi-online in that it only needs to obtain exclusive
table access only on the table it is currently running against. Running the
Cluster Index rebuild WITH DROP EXISTING can speed up the process.
But then, again, that is a management issue. The 5 9's do NOT refer to
TOTAL TIME, but to SLA time. And, EVERY SYSTEM HAS TO HAVE AN SLA, which
would include a maintenance window. If you don't have an APPROPRIATE SLA,
then your 5 9's don't mean very much. But, yes, we maintain 5, and even 6,
9's of availability, but properly measured against reasonable SLAs that have
actually been documented and constructed with the various Business Units
that are demanding the system availabilities.
I suppose that if your SLAs DEMANDED 24x7x52, with NO MAINTENANCE, as
delusional as the Business Units may be, then sacrificing performance over
page splits would be one reason to not care about the Cluster Index
placement. But, then, why bother and just use a HEAP. You're not going to
get much better performance from an ill-chosen Clustered Index. Some, but
not much.
Sincerely,
Anthony Thomas
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23Ra$B$8KFHA.2252@.TK2MSFTNGP15.phx.gbl...
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:eCMwAS5KFHA.2860@.TK2MSFTNGP10.phx.gbl...
>
> Can I get a witness?
No. Never worked in a 24-7 (4-nines, and we're trying for 5 this
year),
high-volume OLTP environment, have you? Good luck maintaining those fill
factors when you have no maintenence window.
--
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
--=_NextPart_000_08EF_01C52BAD.4FA001B0
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Would agree that the DBREINDEX can be =troublesome in highly available systems. But the fill factors should be static =from a design perspective. Also, the DBREINDEX is semi-online in that it =only needs to obtain exclusive table access only on the table it is currently =running against. Running the Cluster Index rebuild WITH DROP EXISTING can =speed up the process.
But then, again, that is a management =issue. The 5 9's do NOT refer to TOTAL TIME, but to SLA =time. And, EVERY SYSTEM HAS TO HAVE AN SLA, which would include a maintenance =window. If you don't have an APPROPRIATE SLA, then your 5 9's don't mean very much. But, yes, we maintain 5, and even 6, 9's of availability, =but properly measured against reasonable SLAs that have actually been =documented and constructed with the various Business Units that are demanding the =system availabilities.
I suppose that if your SLAs DEMANDED =24x7x52, with NO MAINTENANCE, as delusional as the Business Units may be, then sacrificing performance over page splits would be one reason to not care =about the Cluster Index placement. But, then, why bother and just use a HEAP. You're not going to get much better performance from an =ill-chosen Clustered Index. Some, but not much.
Sincerely,
Anthony Thomas
--
"Adam Machanic" wrote in message news:%23Ra$B$8KFHA.=2252@.TK2MSFTNGP15.phx.gbl..."Anthony Thomas" wrote in messagenews:eCMwAS5KFHA.2860=@.TK2MSFTNGP10.phx.gbl...>> Can I get a witness? No. Never =worked in a 24-7 (4-nines, and we're trying for 5 this year),high-volume OLTP environment, have you? Good luck maintaining those =fillfactors when you have no maintenence window.-- Adam MachanicSQL =Server MVPhttp://www.datamanipulation.net<=/A>--

---

--=_NextPart_000_08EF_01C52BAD.4FA001B0--|||Since you brought it up...well, and I brought it up, the topic of SLAs and
availability metrics is an interestin topic in its own right.
Craig Mullins has some good articles on the topic you should check out.
http://www.craigsmullins.com/dbta_006.htm
Sincerely,
Anthony Thomas
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:eXByZ$9KFHA.3832@.TK2MSFTNGP12.phx.gbl...
Would agree that the DBREINDEX can be troublesome in highly available
systems. But the fill factors should be static from a design perspective.
Also, the DBREINDEX is semi-online in that it only needs to obtain exclusive
table access only on the table it is currently running against. Running the
Cluster Index rebuild WITH DROP EXISTING can speed up the process.
But then, again, that is a management issue. The 5 9's do NOT refer to
TOTAL TIME, but to SLA time. And, EVERY SYSTEM HAS TO HAVE AN SLA, which
would include a maintenance window. If you don't have an APPROPRIATE SLA,
then your 5 9's don't mean very much. But, yes, we maintain 5, and even 6,
9's of availability, but properly measured against reasonable SLAs that have
actually been documented and constructed with the various Business Units
that are demanding the system availabilities.
I suppose that if your SLAs DEMANDED 24x7x52, with NO MAINTENANCE, as
delusional as the Business Units may be, then sacrificing performance over
page splits would be one reason to not care about the Cluster Index
placement. But, then, why bother and just use a HEAP. You're not going to
get much better performance from an ill-chosen Clustered Index. Some, but
not much.
Sincerely,
Anthony Thomas
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23Ra$B$8KFHA.2252@.TK2MSFTNGP15.phx.gbl...
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:eCMwAS5KFHA.2860@.TK2MSFTNGP10.phx.gbl...
> Can I get a witness?
No. Never worked in a 24-7 (4-nines, and we're trying for 5 this year),
high-volume OLTP environment, have you? Good luck maintaining those fill
factors when you have no maintenence window.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--|||http://www.dbta.com/columnists/craig_mullins/dba_corner_0902.html
http://www.craigsmullins.com/dbta_026.htm
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:OH8L1F%23KFHA.2748@.TK2MSFTNGP09.phx.gbl...
Since you brought it up...well, and I brought it up, the topic of SLAs and
availability metrics is an interestin topic in its own right.
Craig Mullins has some good articles on the topic you should check out.
http://www.craigsmullins.com/dbta_006.htm
Sincerely,
Anthony Thomas
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:eXByZ$9KFHA.3832@.TK2MSFTNGP12.phx.gbl...
Would agree that the DBREINDEX can be troublesome in highly available
systems. But the fill factors should be static from a design perspective.
Also, the DBREINDEX is semi-online in that it only needs to obtain exclusive
table access only on the table it is currently running against. Running the
Cluster Index rebuild WITH DROP EXISTING can speed up the process.
But then, again, that is a management issue. The 5 9's do NOT refer to
TOTAL TIME, but to SLA time. And, EVERY SYSTEM HAS TO HAVE AN SLA, which
would include a maintenance window. If you don't have an APPROPRIATE SLA,
then your 5 9's don't mean very much. But, yes, we maintain 5, and even 6,
9's of availability, but properly measured against reasonable SLAs that have
actually been documented and constructed with the various Business Units
that are demanding the system availabilities.
I suppose that if your SLAs DEMANDED 24x7x52, with NO MAINTENANCE, as
delusional as the Business Units may be, then sacrificing performance over
page splits would be one reason to not care about the Cluster Index
placement. But, then, why bother and just use a HEAP. You're not going to
get much better performance from an ill-chosen Clustered Index. Some, but
not much.
Sincerely,
Anthony Thomas
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23Ra$B$8KFHA.2252@.TK2MSFTNGP15.phx.gbl...
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:eCMwAS5KFHA.2860@.TK2MSFTNGP10.phx.gbl...
> Can I get a witness?
No. Never worked in a 24-7 (4-nines, and we're trying for 5 this year),
high-volume OLTP environment, have you? Good luck maintaining those fill
factors when you have no maintenence window.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--|||This is a multi-part message in MIME format.
--=_NextPart_000_0092_01C52BD8.4B71D9D0
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
You should probably read Kimberly Tripp's Q&A site:
http://www.sqlskills.com/ConsolidatedQA.asp
This will clarify a lot of things for you, I think. Heaps are certainly =not a good choice for insert performance, as gaps will be filled if rows =are deleted. And I'm confused about your comment regarding sacrificing =performance -- clustering on an IDENTITY or other sequential key will do =the opposite in many cases. Insert, and in many cases read performance =will both benefit. I've run extensive tests to prove this (I'm a load =testing fanatic) and you'll find upon reading that web page that =Kimberly apparently agrees with me. I'm not sure what basis your =arguments have, but you may want to run some tests for yourself.
Thanks for the links on SLAs -- I will send them to our business team =:-)
-- Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message =news:eXByZ$9KFHA.3832@.TK2MSFTNGP12.phx.gbl...
Would agree that the DBREINDEX can be troublesome in highly available =systems. But the fill factors should be static from a design =perspective. Also, the DBREINDEX is semi-online in that it only needs =to obtain exclusive table access only on the table it is currently =running against. Running the Cluster Index rebuild WITH DROP EXISTING =can speed up the process.
But then, again, that is a management issue. The 5 9's do NOT refer =to TOTAL TIME, but to SLA time. And, EVERY SYSTEM HAS TO HAVE AN SLA, =which would include a maintenance window. If you don't have an =APPROPRIATE SLA, then your 5 9's don't mean very much. But, yes, we =maintain 5, and even 6, 9's of availability, but properly measured =against reasonable SLAs that have actually been documented and =constructed with the various Business Units that are demanding the =system availabilities.
I suppose that if your SLAs DEMANDED 24x7x52, with NO MAINTENANCE, as =delusional as the Business Units may be, then sacrificing performance =over page splits would be one reason to not care about the Cluster Index =placement. But, then, why bother and just use a HEAP. You're not going =to get much better performance from an ill-chosen Clustered Index. Some, =but not much.
Sincerely,
Anthony Thomas
--
--=_NextPart_000_0092_01C52BD8.4B71D9D0
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

You should probably read Kimberly =Tripp's Q&A site:
http://www.sqlskills.com/ConsolidatedQA.asp">http://www.sqlskills=.com/ConsolidatedQA.asp
This will clarify a lot of things =for you, I think. Heaps are certainly not a good choice for insert =performance, as gaps will be filled if rows are deleted. And I'm confused about =your comment regarding sacrificing performance -- clustering on an IDENTITY =or other sequential key will do the opposite in many cases. Insert, and in =many cases read performance will both benefit. I've run extensive tests =to prove this (I'm a load testing fanatic) and you'll find upon reading =that web page that Kimberly apparently agrees with me. I'm not sure =what basis your arguments have, but you may want to run some tests for yourself.
Thanks for the links on SLAs -- I will =send them to our business team :-)
-- Adam =MachanicSQL Server MVPhttp://www.datamanipulation.net<=/A>--
"Anthony Thomas" wrote in =message news:eXByZ$9KFHA.3832=@.TK2MSFTNGP12.phx.gbl...
Would agree that the DBREINDEX can =be troublesome in highly available systems. But the fill factors =should be static from a design perspective. Also, the DBREINDEX is =semi-online in that it only needs to obtain exclusive table access only on the table =it is currently running against. Running the Cluster Index rebuild =WITH DROP EXISTING can speed up the process.

But then, again, that is a =management issue. The 5 9's do NOT refer to TOTAL TIME, but to SLA =time. And, EVERY SYSTEM HAS TO HAVE AN SLA, which would include a maintenance window. If you don't have an APPROPRIATE SLA, then your 5 9's =don't mean very much. But, yes, we maintain 5, and even 6, 9's of =availability, but properly measured against reasonable SLAs that have actually been =documented and constructed with the various Business Units that are demanding the =system availabilities.

I suppose that if your SLAs =DEMANDED 24x7x52, with NO MAINTENANCE, as delusional as the Business Units may be, then sacrificing performance over page splits would be one reason to not =care about the Cluster Index placement. But, then, why bother and just use =a HEAP. You're not going to get much better performance from an =ill-chosen Clustered Index. Some, but not much.

Sincerely,


Anthony Thomas

--

---

--=_NextPart_000_0092_01C52BD8.4B71D9D0--|||Okay. I took you up on your suggestions.
Yes, I read Kimberly Tripp's Q&A and you two agree, to an extent. She has a
basis for "Defaulting" to clustering the PK IDENTITY regardless if it is a
surrogate key or not. She also admits that this will create a "hot spot" in
the data file but assumes the end-users will immediately want to retrieve
this information after insertion and that the hot spots is mitigated by
having the recent insert already in cache. She also conceedes that the
Business Key would be an alternative Cluster Index candidate in some
situations.
I also took your advice and ran my own tests. Here's what I discovered:
The page splits happen only as a course of inserts; so, table types that
have many more inserts versus other CRUD or Query operations may benefit for
what you two are suggesting.
I also agree with the fact that HEAPs are detrimental for the myriad of
reasons you and Kimberly point out.
However, I still disagree with the notion of using IDENTITY Clustered
Indexes as a matter of default. Here is why:
The majority of tables are not of the type the two of you suggest would be
beneficial for using an IDENTITY attribute. The majority of tables are of
the reference type. For example, a Customer, Author, Title, Orders, and the
various other look up and reference kinds of tables. Once entered, these
tables are queried and/or joined for reference information far more often in
query type statements than they ever are versus the intitial INSERT.
Moreover, my previous comments ring true. That more often than not, a
SELECT * or at least many of the columns for these reference tables are
included. In which case, the Clustered Index is chosen more often than not
because it is a covering index. However, these types of tables typically
JOIN and are FILTERED by the very Business Key that defines the Unique
Contraint. When the Clustered Index is the surrogate IDENTITY you end up
forcing a Clustered Index Scan whereas having the Unique Constraint as the
Clustered Index, you end up with a Clustered Index Seek, which is more
efficient.
So, as a matter of "default," you will cover more tables if you choose the
Business Key as the Cluster Index instead of the surrogate IDENTITY and will
end up with more efficient queries.
So, what to do with the handful of transactional tables, that are low in
number of tables, but high in the quantity of data? My tests have shown
that the page splits is controlled more by the FILL FACTOR, as I suggested,
than by choosing the surrogate key over the Business Key. Now, you are
correct in that choosing increasing keys will force the splits at the ends,
which Kimberly and I both agree will create a local "hot spot." What we
disagree on is whether or not this is desirable. Next, having an
appropriate FILL Factor and choosing the Business Key for the Cluster Index,
can distribute this "hot spot" activity throughout the database files. I
contend that this is more desirable and have found that even with the
default FILL Factor causes few splits than using the surrogate key as the
Cluster Index.
Finally, there are situations, only with the transaction tables, that the
IDENTITY is NOT a surrogate, but the Business Key itself. In these
situations, only, I think we agree to use this as the Cluster Key, but only
because our definitions have coincided, not because of any concession on
either of our part.
I am sorry, but the tests I have just conducted, although emperical and
limited, at least suggest that what I am say bears truth. I would be
curious to know the particulars of your tests in order that I attempt to
reproduce your results.
Thanks for all of your time.
Sincerely,
Anthony Thomas
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:uha51IALFHA.568@.TK2MSFTNGP09.phx.gbl...
You should probably read Kimberly Tripp's Q&A site:
http://www.sqlskills.com/ConsolidatedQA.asp
This will clarify a lot of things for you, I think. Heaps are certainly not
a good choice for insert performance, as gaps will be filled if rows are
deleted. And I'm confused about your comment regarding sacrificing
performance -- clustering on an IDENTITY or other sequential key will do the
opposite in many cases. Insert, and in many cases read performance will
both benefit. I've run extensive tests to prove this (I'm a load testing
fanatic) and you'll find upon reading that web page that Kimberly apparently
agrees with me. I'm not sure what basis your arguments have, but you may
want to run some tests for yourself.
Thanks for the links on SLAs -- I will send them to our business team :-)
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:eXByZ$9KFHA.3832@.TK2MSFTNGP12.phx.gbl...
Would agree that the DBREINDEX can be troublesome in highly available
systems. But the fill factors should be static from a design perspective.
Also, the DBREINDEX is semi-online in that it only needs to obtain exclusive
table access only on the table it is currently running against. Running the
Cluster Index rebuild WITH DROP EXISTING can speed up the process.
But then, again, that is a management issue. The 5 9's do NOT refer to
TOTAL TIME, but to SLA time. And, EVERY SYSTEM HAS TO HAVE AN SLA, which
would include a maintenance window. If you don't have an APPROPRIATE SLA,
then your 5 9's don't mean very much. But, yes, we maintain 5, and even 6,
9's of availability, but properly measured against reasonable SLAs that have
actually been documented and constructed with the various Business Units
that are demanding the system availabilities.
I suppose that if your SLAs DEMANDED 24x7x52, with NO MAINTENANCE, as
delusional as the Business Units may be, then sacrificing performance over
page splits would be one reason to not care about the Cluster Index
placement. But, then, why bother and just use a HEAP. You're not going to
get much better performance from an ill-chosen Clustered Index. Some, but
not much.
Sincerely,
Anthony Thomas|||On Tue, 22 Mar 2005 07:05:30 -0600, Anthony Thomas wrote:
(snip)
(snip)
>Moreover, my previous comments ring true. That more often than not, a
>SELECT * or at least many of the columns for these reference tables are
>included. In which case, the Clustered Index is chosen more often than not
>because it is a covering index. However, these types of tables typically
>JOIN and are FILTERED by the very Business Key that defines the Unique
>Contraint. When the Clustered Index is the surrogate IDENTITY you end up
>forcing a Clustered Index Scan whereas having the Unique Constraint as the
>Clustered Index, you end up with a Clustered Index Seek, which is more
>efficient.
(snip)
Hi Anthony,
Yes, they are often filtered by the business key that defines the unique
constraint.
No, they are hardly ever joined by that business key. The whole point of
introducing an IDENTITY surrogate key is to use that integer value for
all references instead of the (often longer, sometimes spanned) business
key. So I'd expect joins to be using the identity surrogate key.
If most queries include a filter on the business key, AND the optimizer
decides to use that filter first in it's execution plan, than a
clustered index on the business key would be better than clustering on
the surrogate key. But if many queries don't filter the business key
(because the filters are on columns in other tables, and this table is
only joined in to display some extra columns), OR even if the queries do
filter on the business key, but the optimizer decides that the best plan
will filter other tables first, then join this table and apply the
remainging filter conditions, then the clustered index on the identity
column would be best.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:887141lbp95i5ibkgarn8t1ga3812gpbs0@.4ax.com...
> If most queries include a filter on the business key, AND the optimizer
> decides to use that filter first in it's execution plan, than a
> clustered index on the business key would be better than clustering on
> the surrogate key. But if many queries don't filter the business key
> (because the filters are on columns in other tables, and this table is
> only joined in to display some extra columns), OR even if the queries do
> filter on the business key, but the optimizer decides that the best plan
> will filter other tables first, then join this table and apply the
> remainging filter conditions, then the clustered index on the identity
> column would be best.
Excellent point :)
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--|||Ok. A few replies...and I'll try to keep this short to minimize the
responding topics.
1. You are corrent in that I mispoke concerning a Joining on the Business
Key. Yes, that is the whole point of the surrogate, but as the predominant
restriction condition is still true.
2. A Cluster Index on the surrogate key WILL NOT make the join process
faster. Think about it. If you have to process a MERGE or INNER LOOP join,
with either a prefetch or not, it will have to do a matched seek. Both a
clustered index and a non-clustered index would be sufficient for the
singleton select lookups. Your suggestion that IDENTITY CLUSTERED INDEXES
are more efficient JOIN candidates is hyped but not supported by evidence.
3. Yes, queries against the reference tables are my chief concern because I
see this every day with vended solutions that use the PK IDENTITY Clustered
Index approach, and it kills the systems. Chiefly because the bulk of the
queries, like Customers, etc., are searched on Name Ranges, which the
IDENTITY Clustered Primary Key is the worst candidate.
4. This is a matter of "default." The very Q & A Kimberly wrote says this
explicitly, the very link you provided. And, it is what vendor after vendor
after vendor is shoving into the market place WITHOUT giving it a second
thought much less thought per individual table. Plus, the tool, SQL Server,
does this by default if you do not have the foresight to tell it otherwise.
5. The Orders table being a reference table is a hybrid, it is usually the
Order Details tables that I was thinking of when I was attempting to
distinguish between the two, reference and transaction, respectively. Note,
I have ONLY been talking about OLTP databases, just the table styles within
an OLTP database.
6. My tests showed that the IDENTITY CLUSTERED INDEX had MORE SPLITS, even
on a transaction type table than a Business Key. Now, this boggled my mind;
it was almost 3 to 1 greater. But think about it; these are B-Tree indexes.
If you use an increasing attribute, especially a monitonically increasing
one like an IDENTITY, you've unbalanced the index; you are making it
lop-sided. You end up causing more Intermediate Node splits more often than
if you had randomly split the leafs throughout the table. Now, there may be
a better explaination, but from the first pass tests I conducted, this is
what I saw.
7. The initial question was regarding INSERTS, splits, versus the payoff of
using the Business Key for queries. So, my tests have been first conducted
on individual tables. I am looking into multi-table, with relationships,
types of tests to conduct next. For now, however, here is what I did.
CREATE TABLE MyTable
(MyID INT IDENTITY NOT NULL
PRIMARY KEY CLUSTERED
,MyName VARCHAR(30) NOT NULL
UNIQUE NONCLUSTERED
,MyDescription VARCHAR(50) NOT NULL
,MyCreateDate DATETIME NOT NULL
DEFAULT (GETDATE())
)
CREATE NONCLUSTERED INDEX IX01_MyTable
ON MyTable(MyCreateDate)
Now, I ran several passes changing the Clustered Index attribute, single
column, and then reconducting the test.
The test was to monitor the split behavior, both at the leaf as well as the
node level and then look at the fragmentation once completed with the
inserts of 1,000,000 records. Each insert was a single row at a time to
simulate individual transacitons.
The MyID was IDENTITY; so, no explicit value inserted. MyName was preceeded
with a CHAR(x) value randomly created, then the length was varied between
the single character to the maximum value. MyDescription was just garbage,
but the length was randomly filled. MyCreateDate was allowed to assume the
transaction time default.
After the inserts and fragmentation analysis, a set of predefined queries
were ran with the execution plan, client statistics, I/O and Time
statistics.
The queries were:
SELECT * FROM MyTable
SELECT * FROM MyTable WHERE MyID = 550000
SELECT * FROM MyTable WHERE MyName LIKE 'M%'
SELECT * FROM MyTable WHERE MyName = 'M'
SELECT * FROM MyTable WHERE MyDate BETWEEN <1/2 way through the run time>
and <3/4 way through the run time>
SELECT * FROM MyTable WHERE MyDate = <a randomly selected time from the run
time>
SELECT MyName, MyCreateDate FROM MyTable
SELECT MyName, MyCreateDate FROM MyTable WHERE MyID = 550000
SELECT MyName, MyCreateDate FROM MyTable WHERE MyName LIKE 'M%'
SELECT MyName, MyCreateDate FROM MyTable WHERE MyName = 'M'
SELECT MyName, MyCreateDate FROM MyTable WHERE MyDate BETWEEN <1/2 way
through the run time> and <3/4 way through the run time>
SELECT MyName, MyCreateDate FROM MyTable WHERE MyDate = <a randomly selected
time from the run time>
I made two passes through these, once by just letting it run, the other by
running the DBCC FREEPROCCACHE and DBCC DROPCLEANBUFFERS in between
statements.
This results were saved and then the whole thing with the same constraints
but just a diffenent choice of clustered index: IDENTITY (MyID), Business
Key (MyName), and then an increasing but not necessarily unique attribute
(MyCreateDate).
I did my analysis on both sides, the fragmentation and efficiency, time and
I/O statistics, of the inserts, and the execution of the above queries.
At the moment, I am attempting to summarize the results, given they are
quite extensive. However, the examination still bears some of what everyone
else is saying, but there are hidden dangers, the Extent Fragmentation for
one. And, the Business Key still produced the best executions for these
queries.
Now, I would not claim that these tests are scientific research, but they
did give me better insight in to what I had originaly had considered, but
nothing would suggest that I detract from my initial statement, that as a
matter of default, which is what most solutions that are present to me are,
the Business Key is still the best candidate for the Clustered Index, which,
as the DBA, is one of the few, but most effective influence I can apply to
an already designed solution.
Sincerely,
Anthony Thomas
--
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:OZP1pBzLFHA.1956@.TK2MSFTNGP15.phx.gbl...
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:887141lbp95i5ibkgarn8t1ga3812gpbs0@.4ax.com...
> If most queries include a filter on the business key, AND the optimizer
> decides to use that filter first in it's execution plan, than a
> clustered index on the business key would be better than clustering on
> the surrogate key. But if many queries don't filter the business key
> (because the filters are on columns in other tables, and this table is
> only joined in to display some extra columns), OR even if the queries do
> filter on the business key, but the optimizer decides that the best plan
> will filter other tables first, then join this table and apply the
> remainging filter conditions, then the clustered index on the identity
> column would be best.
Excellent point :)
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--

Quick question about rowguid column

Is there any issue with assigning the primary key to a rowguid column?
Thoughts?
WBIt depends...
http://www.aspfaq.com/show.asp?id=2504
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"WB" <none> wrote in message news:OOk1gk2HFHA.2456@.TK2MSFTNGP09.phx.gbl...
> Is there any issue with assigning the primary key to a rowguid column?
> Thoughts?
> WB
>|||Yes....
1. Creates a VERY Wide Index that is inefficient (Compared to INT for
example)
2. IF this is also the Clustered Index (I recommend against), Then ALL
Indexes must propogate this GUID in their lookup tables
3. Clustering on GUID can cause page splits,etc
Company I recently worked at just added an identity column to all tables in
DB and clustered on the Ident instead of the GUID. Inserts of records sped
up tremendously. I cant quote the exact number of records, bu the "Process"
went form 17 minutes to literally seconds...
Food for thought
Some companies (My current employer for example) want to use a GUID type of
identifier as opposed to integers for whatever reason. So what they do, to
ensure that the GUID values are Monotonically increasing in value (to avoid
page splits), is the create their own custom "GUID" type data generator in
which the algorithm ensure incrementing values.
Greg Jackson
PDX, Oregon
"WB" <none> wrote in message news:OOk1gk2HFHA.2456@.TK2MSFTNGP09.phx.gbl...
> Is there any issue with assigning the primary key to a rowguid column?
> Thoughts?
> WB
>|||Cluster Index definition on surrogate keys are ALWAYS a bad idea, INT or
GUID. Usually, the Business Key the surrogate is proxiing for is a better
Cluster Index candidate.
Sincerely,
Anthony Thomas
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:uaC7za3HFHA.1948@.TK2MSFTNGP14.phx.gbl...
Yes....
1. Creates a VERY Wide Index that is inefficient (Compared to INT for
example)
2. IF this is also the Clustered Index (I recommend against), Then ALL
Indexes must propogate this GUID in their lookup tables
3. Clustering on GUID can cause page splits,etc
Company I recently worked at just added an identity column to all tables in
DB and clustered on the Ident instead of the GUID. Inserts of records sped
up tremendously. I cant quote the exact number of records, bu the "Process"
went form 17 minutes to literally seconds...
Food for thought
Some companies (My current employer for example) want to use a GUID type of
identifier as opposed to integers for whatever reason. So what they do, to
ensure that the GUID values are Monotonically increasing in value (to avoid
page splits), is the create their own custom "GUID" type data generator in
which the algorithm ensure incrementing values.
Greg Jackson
PDX, Oregon
"WB" <none> wrote in message news:OOk1gk2HFHA.2456@.TK2MSFTNGP09.phx.gbl...
> Is there any issue with assigning the primary key to a rowguid column?
> Thoughts?
> WB
>|||"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:uPDlrHwKFHA.3340@.TK2MSFTNGP14.phx.gbl...
> Cluster Index definition on surrogate keys are ALWAYS a bad idea, INT or
> GUID. Usually, the Business Key the surrogate is proxiing for is a better
> Cluster Index candidate.
There's no such thing as "usually". There are plenty of applications
for either type of clustering key -- and clustering on a sequential integer
can be especially beneficial in many situations. You should be very careful
with that assumption.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--|||Name one.
Sincerely,
Anthony Thomas
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:OYusbfwKFHA.3132@.TK2MSFTNGP12.phx.gbl...
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:uPDlrHwKFHA.3340@.TK2MSFTNGP14.phx.gbl...
> Cluster Index definition on surrogate keys are ALWAYS a bad idea, INT or
> GUID. Usually, the Business Key the surrogate is proxiing for is a better
> Cluster Index candidate.
There's no such thing as "usually". There are plenty of applications
for either type of clustering key -- and clustering on a sequential integer
can be especially beneficial in many situations. You should be very careful
with that assumption.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--|||"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:%23gGre3wKFHA.1176@.TK2MSFTNGP12.phx.gbl...
> Name one.
Inserting data.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--|||Yes, and Inserting Data in Business Key order shouldn't cause an inordinate
amount of page splits if you have your fill factors and database maintenance
routines set correctly. And, having the Cluster Index on the Business Key
will far outweigh any split issues compared to any scans you may encounter
and gather the covering aspects of the clustered index plus it is typlically
the Business Key that most range queries are conducted on.
Care to try again? Are you really telling me that page splits is the BEST
reason you can come up with? Please. Are you really telling me that was
the whole design goal with having Clustered Indexes over Heaps to begin
with? You've got to be kidding me.
Can I get a witness?
Sincerely,
Anthony Thomas
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:OuxM5KxKFHA.4052@.tk2msftngp13.phx.gbl...
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:%23gGre3wKFHA.1176@.TK2MSFTNGP12.phx.gbl...
> Name one.
Inserting data.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--|||"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:eCMwAS5KFHA.2860@.TK2MSFTNGP10.phx.gbl...
> Can I get a witness?
No. Never worked in a 24-7 (4-nines, and we're trying for 5 this year),
high-volume OLTP environment, have you? Good luck maintaining those fill
factors when you have no maintenence window.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--|||http://www.dbta.com/columnists/crai...orner_0902.html
http://www.craigsmullins.com/dbta_026.htm
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:OH8L1F%23KFHA.2748@.TK2MSFTNGP09.phx.gbl...
Since you brought it up...well, and I brought it up, the topic of SLAs and
availability metrics is an interestin topic in its own right.
Craig Mullins has some good articles on the topic you should check out.
http://www.craigsmullins.com/dbta_006.htm
Sincerely,
Anthony Thomas
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:eXByZ$9KFHA.3832@.TK2MSFTNGP12.phx.gbl...
Would agree that the DBREINDEX can be troublesome in highly available
systems. But the fill factors should be static from a design perspective.
Also, the DBREINDEX is semi-online in that it only needs to obtain exclusive
table access only on the table it is currently running against. Running the
Cluster Index rebuild WITH DROP EXISTING can speed up the process.
But then, again, that is a management issue. The 5 9's do NOT refer to
TOTAL TIME, but to SLA time. And, EVERY SYSTEM HAS TO HAVE AN SLA, which
would include a maintenance window. If you don't have an APPROPRIATE SLA,
then your 5 9's don't mean very much. But, yes, we maintain 5, and even 6,
9's of availability, but properly measured against reasonable SLAs that have
actually been documented and constructed with the various Business Units
that are demanding the system availabilities.
I suppose that if your SLAs DEMANDED 24x7x52, with NO MAINTENANCE, as
delusional as the Business Units may be, then sacrificing performance over
page splits would be one reason to not care about the Cluster Index
placement. But, then, why bother and just use a HEAP. You're not going to
get much better performance from an ill-chosen Clustered Index. Some, but
not much.
Sincerely,
Anthony Thomas
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23Ra$B$8KFHA.2252@.TK2MSFTNGP15.phx.gbl...
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:eCMwAS5KFHA.2860@.TK2MSFTNGP10.phx.gbl...
> Can I get a witness?
No. Never worked in a 24-7 (4-nines, and we're trying for 5 this year),
high-volume OLTP environment, have you? Good luck maintaining those fill
factors when you have no maintenence window.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--

Quick question about rowguid column

Is there any issue with assigning the primary key to a rowguid column?
Thoughts?
WB
It depends...
http://www.aspfaq.com/show.asp?id=2504
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"WB" <none> wrote in message news:OOk1gk2HFHA.2456@.TK2MSFTNGP09.phx.gbl...
> Is there any issue with assigning the primary key to a rowguid column?
> Thoughts?
> WB
>
|||Yes....
1. Creates a VERY Wide Index that is inefficient (Compared to INT for
example)
2. IF this is also the Clustered Index (I recommend against), Then ALL
Indexes must propogate this GUID in their lookup tables
3. Clustering on GUID can cause page splits,etc
Company I recently worked at just added an identity column to all tables in
DB and clustered on the Ident instead of the GUID. Inserts of records sped
up tremendously. I cant quote the exact number of records, bu the "Process"
went form 17 minutes to literally seconds...
Food for thought
Some companies (My current employer for example) want to use a GUID type of
identifier as opposed to integers for whatever reason. So what they do, to
ensure that the GUID values are Monotonically increasing in value (to avoid
page splits), is the create their own custom "GUID" type data generator in
which the algorithm ensure incrementing values.
Greg Jackson
PDX, Oregon
"WB" <none> wrote in message news:OOk1gk2HFHA.2456@.TK2MSFTNGP09.phx.gbl...
> Is there any issue with assigning the primary key to a rowguid column?
> Thoughts?
> WB
>
|||Cluster Index definition on surrogate keys are ALWAYS a bad idea, INT or
GUID. Usually, the Business Key the surrogate is proxiing for is a better
Cluster Index candidate.
Sincerely,
Anthony Thomas

"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:uaC7za3HFHA.1948@.TK2MSFTNGP14.phx.gbl...
Yes....
1. Creates a VERY Wide Index that is inefficient (Compared to INT for
example)
2. IF this is also the Clustered Index (I recommend against), Then ALL
Indexes must propogate this GUID in their lookup tables
3. Clustering on GUID can cause page splits,etc
Company I recently worked at just added an identity column to all tables in
DB and clustered on the Ident instead of the GUID. Inserts of records sped
up tremendously. I cant quote the exact number of records, bu the "Process"
went form 17 minutes to literally seconds...
Food for thought
Some companies (My current employer for example) want to use a GUID type of
identifier as opposed to integers for whatever reason. So what they do, to
ensure that the GUID values are Monotonically increasing in value (to avoid
page splits), is the create their own custom "GUID" type data generator in
which the algorithm ensure incrementing values.
Greg Jackson
PDX, Oregon
"WB" <none> wrote in message news:OOk1gk2HFHA.2456@.TK2MSFTNGP09.phx.gbl...
> Is there any issue with assigning the primary key to a rowguid column?
> Thoughts?
> WB
>
|||"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:uPDlrHwKFHA.3340@.TK2MSFTNGP14.phx.gbl...
> Cluster Index definition on surrogate keys are ALWAYS a bad idea, INT or
> GUID. Usually, the Business Key the surrogate is proxiing for is a better
> Cluster Index candidate.
There's no such thing as "usually". There are plenty of applications
for either type of clustering key -- and clustering on a sequential integer
can be especially beneficial in many situations. You should be very careful
with that assumption.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
|||Name one.
Sincerely,
Anthony Thomas

"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:OYusbfwKFHA.3132@.TK2MSFTNGP12.phx.gbl...
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:uPDlrHwKFHA.3340@.TK2MSFTNGP14.phx.gbl...
> Cluster Index definition on surrogate keys are ALWAYS a bad idea, INT or
> GUID. Usually, the Business Key the surrogate is proxiing for is a better
> Cluster Index candidate.
There's no such thing as "usually". There are plenty of applications
for either type of clustering key -- and clustering on a sequential integer
can be especially beneficial in many situations. You should be very careful
with that assumption.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
|||"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:%23gGre3wKFHA.1176@.TK2MSFTNGP12.phx.gbl...
> Name one.
Inserting data.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
|||Yes, and Inserting Data in Business Key order shouldn't cause an inordinate
amount of page splits if you have your fill factors and database maintenance
routines set correctly. And, having the Cluster Index on the Business Key
will far outweigh any split issues compared to any scans you may encounter
and gather the covering aspects of the clustered index plus it is typlically
the Business Key that most range queries are conducted on.
Care to try again? Are you really telling me that page splits is the BEST
reason you can come up with? Please. Are you really telling me that was
the whole design goal with having Clustered Indexes over Heaps to begin
with? You've got to be kidding me.
Can I get a witness?
Sincerely,
Anthony Thomas

"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:OuxM5KxKFHA.4052@.tk2msftngp13.phx.gbl...
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:%23gGre3wKFHA.1176@.TK2MSFTNGP12.phx.gbl...
> Name one.
Inserting data.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
|||"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:eCMwAS5KFHA.2860@.TK2MSFTNGP10.phx.gbl...
> Can I get a witness?
No. Never worked in a 24-7 (4-nines, and we're trying for 5 this year),
high-volume OLTP environment, have you? Good luck maintaining those fill
factors when you have no maintenence window.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
|||http://www.dbta.com/columnists/craig...rner_0902.html
http://www.craigsmullins.com/dbta_026.htm

"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:OH8L1F%23KFHA.2748@.TK2MSFTNGP09.phx.gbl...
Since you brought it up...well, and I brought it up, the topic of SLAs and
availability metrics is an interestin topic in its own right.
Craig Mullins has some good articles on the topic you should check out.
http://www.craigsmullins.com/dbta_006.htm
Sincerely,
Anthony Thomas

"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:eXByZ$9KFHA.3832@.TK2MSFTNGP12.phx.gbl...
Would agree that the DBREINDEX can be troublesome in highly available
systems. But the fill factors should be static from a design perspective.
Also, the DBREINDEX is semi-online in that it only needs to obtain exclusive
table access only on the table it is currently running against. Running the
Cluster Index rebuild WITH DROP EXISTING can speed up the process.
But then, again, that is a management issue. The 5 9's do NOT refer to
TOTAL TIME, but to SLA time. And, EVERY SYSTEM HAS TO HAVE AN SLA, which
would include a maintenance window. If you don't have an APPROPRIATE SLA,
then your 5 9's don't mean very much. But, yes, we maintain 5, and even 6,
9's of availability, but properly measured against reasonable SLAs that have
actually been documented and constructed with the various Business Units
that are demanding the system availabilities.
I suppose that if your SLAs DEMANDED 24x7x52, with NO MAINTENANCE, as
delusional as the Business Units may be, then sacrificing performance over
page splits would be one reason to not care about the Cluster Index
placement. But, then, why bother and just use a HEAP. You're not going to
get much better performance from an ill-chosen Clustered Index. Some, but
not much.
Sincerely,
Anthony Thomas

"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23Ra$B$8KFHA.2252@.TK2MSFTNGP15.phx.gbl...
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:eCMwAS5KFHA.2860@.TK2MSFTNGP10.phx.gbl...
> Can I get a witness?
No. Never worked in a 24-7 (4-nines, and we're trying for 5 this year),
high-volume OLTP environment, have you? Good luck maintaining those fill
factors when you have no maintenence window.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net

Quick Dumb Question

Quick Dumb Question
I was wondering if there was a way to revel what format a query returns
per column.
I am running into the situation where I am pulling data from a huge
list, and occasionally I get the error "Implicit conversion from data
type sql_variant to varchar is not allowed. Use the CONVERT function to
run this query." When inserting it into a table. When I run the
command without the insert it return all the information without the
error. So now I need to go hunt what column has the incorrect format
and what format to change it to or need to run CONVERT on that
particular item in the query
Hope that made sense.SELECT COLUMN_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Your Table'
Should give you a listing of the columns in a table, and their datatypes.
"Matthew" wrote:

> Quick Dumb Question
> I was wondering if there was a way to revel what format a query returns
> per column.
> I am running into the situation where I am pulling data from a huge
> list, and occasionally I get the error "Implicit conversion from data
> type sql_variant to varchar is not allowed. Use the CONVERT function to
> run this query." When inserting it into a table. When I run the
> command without the insert it return all the information without the
> error. So now I need to go hunt what column has the incorrect format
> and what format to change it to or need to run CONVERT on that
> particular item in the query
> Hope that made sense.
>

quick date conversion - not hard!

im trying to convert my column of data type datetime. this is an example of what the data looks like:

2007-01-24 10:01:29.710

i want to format it as so

200701241001

ive tried this so far i think im sort of on the right track

RIGHT('000000000000' + CONVERT(varchar(20), CONVERT(int, sr.ResponseDate)),12) AS FinalDate

but its not outputting exactly what i want

thanks
andreas

Quote:

Originally Posted by andreas2410

im trying to convert my column of data type datetime. this is an example of what the data looks like:

2007-01-24 10:01:29.710

i want to format it as so

200701241001

ive tried this so far i think im sort of on the right track

RIGHT('000000000000' + CONVERT(varchar(20), CONVERT(int, sr.ResponseDate)),12) AS FinalDate

but its not outputting exactly what i want

thanks
andreas

im also thinking that i will need when i try convert

to put it into the style code either

code 20 or 120 yyyy-mm-dd hh:mi:ss(24h)

so thats 24 hour which is what i need and then rip the separators

or in code 126 below so theres no spaces, but i dont think thats 24 hr representation

code 126 yyyy-mm-dd Thh:mm:ss.mmm(no spaces)

Queued Updating Subscribers Question

When I looked into setting this up I received a message that an identity column would be added to all my tables for this type of replication.
Wouldn't this result in my having to change all code that touches these tables to take the new column into account?
Queued Updating is new to me, I am trying to learn the best replication option for our reporting database, but am a little confused.
Any/All help is appreciated!
Thanx!
JLS,
if the identity column is already there on the publisher, it is transferred
to the subscriber but no new identity columns will be created. On each
indetity column the column is designated as Identity Yes (Not for
Replication). This ensures that the replication process can insert values
into the column, and for an insert on the subscriber itself SQL Server can
have values allocated as per normal. To avoid clashes, identity ranges are
allocated to publisher and each subscriber, each node having different
seeds; these ranges and the allocating of new ranges is configurable at the
publication level.
HTH,
Paul Ibison
|||I'm sorry Paul, I don't follow. I have been setting up and tearing down
replication every which way from Sunday, so everything is sort of running
together.
I changed all my identity columns on the Subscriber to Yes(Not for
Replication), I don't really have any issue here. It is my understanding
that what happens here is the value from the Publisher is popped into this
field on the Subscriber, and that's the way I would want it to work as I
don't intend to have any updates occurring on the Subscriber.
When I selected Queued Updating as an option, in the Identity warning screen
I saw a new warning that stated a new column would be added, and every table
I am replicating was listed, and the new column is a replication column.
The warning also stated that this may cause INSERT to fail & cause the table
to become larger.
Can you explain this in "For Dummies who haven't had enough coffee yet this
morning" terms?
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23dXpbRuTEHA.1548@.TK2MSFTNGP11.phx.gbl...
> JLS,
> if the identity column is already there on the publisher, it is
transferred
> to the subscriber but no new identity columns will be created. On each
> indetity column the column is designated as Identity Yes (Not for
> Replication). This ensures that the replication process can insert values
> into the column, and for an insert on the subscriber itself SQL Server can
> have values allocated as per normal. To avoid clashes, identity ranges are
> allocated to publisher and each subscriber, each node having different
> seeds; these ranges and the allocating of new ranges is configurable at
the
> publication level.
> HTH,
> Paul Ibison
>
|||JLS,
the column you are referring to is not an identity column -
it is a GUID. This is added and may cause tsql to fail
when it doesn't have an explicit column list eg
insert into table1
select * from replicatetable
If you are using Queued Updating Subscribers and are
letting replication do the initialization for you, you
don't need to alter identity columns on the subscriber -
they'll be set correctly for you.
HTH,
Paul Ibison
ps if you don't intend having subscribers update the data,
then why not use standard transactional replication?
|||Ah, ok I get it now. Thanx!!!!
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:1ae5301c44eee$b71dc120$a001280a@.phx.gbl...
> JLS,
> the column you are referring to is not an identity column -
> it is a GUID. This is added and may cause tsql to fail
> when it doesn't have an explicit column list eg
> insert into table1
> select * from replicatetable
> If you are using Queued Updating Subscribers and are
> letting replication do the initialization for you, you
> don't need to alter identity columns on the subscriber -
> they'll be set correctly for you.
> HTH,
> Paul Ibison
> ps if you don't intend having subscribers update the data,
> then why not use standard transactional replication?
>