From varchar(max) to xml

I have a table with 2 columns. Column a(varchar(max)) and column b(xml).
Column a contains the following data:
Col1;Col2
New York;USA
Rio;Brasil
Tokio;Japan
The first line contains the column header, the following the data.
The data should be transferred to column b with the following xml-structure:
<Col1>New York</Col1><Col2>USA</Col2>
<Col1>Rio</Col1><Col2>Brasil</Col2>
<Col1>Tokio</Col1><Col2>Japan</Col2>
The number of columns and the column names are various.
Any ideas?
Thanks psychodad71
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...er-xml/200606/1It looks like this would require a lot of string manipulation. Although I
believe it could be done with T-SQL, the string functions are a little limit
ed.
I'd suggest you use the CLR.
I'll give it a shot myself when I get some time and I'll post an update.
Denis Ruckebusch
http://blogs.msdn.com/denisruc
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"psychodad71 via webservertalk.com" <u2248@.uwe> wrote in message
news:6232e7ee08949@.uwe...
>I have a table with 2 columns. Column a(varchar(max)) and column b(xml).
> Column a contains the following data:
> Col1;Col2
> New York;USA
> Rio;Brasil
> Tokio;Japan
> The first line contains the column header, the following the data.
> The data should be transferred to column b with the following xml-structur
e:
> <Col1>New York</Col1><Col2>USA</Col2>
> <Col1>Rio</Col1><Col2>Brasil</Col2>
> <Col1>Tokio</Col1><Col2>Japan</Col2>
> The number of columns and the column names are various.
> Any ideas?
> Thanks psychodad71
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...er-xml/200606/1|||I can't think of a nice set-based method of doing this, but you could use
procedural code to do it. I agree with Denis, this should probably be done
in the front end. But that said, here's a little procedural sample. Note
that I don't have SQL 2005 on the computer I'm at right now so I had to put
this thing together on SQL 2000. It should run properly on SQL 2005 as
well. It's *extremely* procedural and assumes that the TestInput table has
a numeric id for each row, row 0 being the column names and all other rows
containing data. The procedural nature of this type of code makes me think
you'd be a lot better off doing it on the front end though:
-- Create a "Numbers" table and an inline UDF that uses it to parse your
-- comma-delimited string. Run this section one time.
SELECT TOP 10000 number = IDENTITY(INT, 1, 1)
INTO Numbers
FROM syscomments a1
CROSS JOIN syscomments a2
-- Add Primary Key to Numbers table
ALTER TABLE Numbers
ALTER COLUMN Number INT NOT NULL
ALTER TABLE Numbers
ADD CONSTRAINT PK_Numbers PRIMARY KEY (Number)
-- Create inline UDF
GO
CREATE FUNCTION dbo.ParseDelimitedList (@.list AS NVARCHAR(4000))
RETURNS TABLE
AS
RETURN (
SELECT Number, LTRIM(RTRIM(CASE Number
WHEN 1 THEN SUBSTRING(@.list, 1,
CASE WHEN CHARINDEX(';', @.list, Number + 1) > 0 THEN
CHARINDEX(';', @.list, Number + 1) - 1
ELSE LEN(@.list) - CHARINDEX(';', @.list, Number + 1)
END)
ELSE SUBSTRING(@.list, Number + 1,
CASE WHEN CHARINDEX(';', @.list, Number + 1) > 0 THEN
CHARINDEX(';', @.list, Number + 1) - Number - 1
ELSE LEN(@.list)
END)
END)) AS Value
FROM Numbers
WHERE (SUBSTRING(@.list, Number, 1) = ';' OR Number = 1)
)
GO
-- End of the Numbers table/UDF initialization.
CREATE TABLE TestInput([id] INT PRIMARY KEY,
a VARCHAR(8000),
b VARCHAR(8000))
INSERT INTO TestInput([id], a)
SELECT 0, 'Col1;Col2'
UNION SELECT 1, 'New York;USA'
UNION SELECT 2, 'Rio;Brasil'
UNION SELECT 3, 'Tokio;Japan'
DECLARE @.sql VARCHAR(8000)
DECLARE @.temp_str VARCHAR(8000)
DECLARE @.cols TABLE ([id_num] INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
[col_name] VARCHAR(8000))
SELECT @.temp_str = a
FROM TestInput
WHERE [id] = 0
INSERT INTO @.cols([col_name])
SELECT Value
FROM dbo.ParseDelimitedList(@.temp_str)
ORDER BY [Number]
DECLARE @.col_count INT
SELECT @.col_count = MAX([id_num])
FROM @.cols
DECLARE @.vals TABLE ([id_num] INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
[value] VARCHAR(8000))
DECLARE @.id INT
SELECT @.id = 1
DECLARE @.i INT
WHILE @.id <= (SELECT MAX([id]) FROM TestInput)
BEGIN
SELECT @.temp_str = a
FROM TestInput
WHERE [id] = @.id
IF NOT(@.temp_str IS NULL)
BEGIN
INSERT INTO @.vals([value])
SELECT [Value]
FROM dbo.ParseDelimitedList(@.temp_str)
ORDER BY [Number]
SELECT @.temp_str = ''
SELECT @.i = 1
WHILE @.i <= @.col_count
BEGIN
SELECT @.temp_str = @.temp_str + '<' +
(
SELECT [col_name]
FROM @.cols
WHERE [id_num] = @.i
) + '>'
SELECT @.temp_str = @.temp_str +
(
SELECT COALESCE([value], '')
FROM @.vals
WHERE [id_num] = @.i + (@.id - 1) * @.col_count
)
SELECT @.temp_str = @.temp_str + '</' +
(
SELECT [col_name]
FROM @.cols
WHERE [id_num] = @.i
) + '>'
SELECT @.i = @.i + 1
END
UPDATE TestInput
SET b = @.temp_str
WHERE [id] = @.id
END
SELECT @.id = @.id + 1
END
SELECT *
FROM @.vals
SELECT *
FROM TestInput
"psychodad71 via webservertalk.com" <u2248@.uwe> wrote in message
news:6232e7ee08949@.uwe...
>I have a table with 2 columns. Column a(varchar(max)) and column b(xml).
> Column a contains the following data:
> Col1;Col2
> New York;USA
> Rio;Brasil
> Tokio;Japan
> The first line contains the column header, the following the data.
> The data should be transferred to column b with the following
> xml-structure:
> <Col1>New York</Col1><Col2>USA</Col2>
> <Col1>Rio</Col1><Col2>Brasil</Col2>
> <Col1>Tokio</Col1><Col2>Japan</Col2>
> The number of columns and the column names are various.
> Any ideas?
> Thanks psychodad71
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...er-xml/200606/1|||Ooops, the "syscomments" references will need to be changed for SQL 2005 to
"sys.comments".
"Mike C#" <xyz@.xyz.com> wrote in message
news:ORxRYYwlGHA.2056@.TK2MSFTNGP03.phx.gbl...
>I can't think of a nice set-based method of doing this, but you could use
>procedural code to do it. I agree with Denis, this should probably be done
>in the front end. But that said, here's a little procedural sample. Note
>that I don't have SQL 2005 on the computer I'm at right now so I had to put
>this thing together on SQL 2000. It should run properly on SQL 2005 as
>well. It's *extremely* procedural and assumes that the TestInput table has
>a numeric id for each row, row 0 being the column names and all other rows
>containing data. The procedural nature of this type of code makes me think
>you'd be a lot better off doing it on the front end though:
> -- Create a "Numbers" table and an inline UDF that uses it to parse your
> -- comma-delimited string. Run this section one time.
> SELECT TOP 10000 number = IDENTITY(INT, 1, 1)
> INTO Numbers
> FROM syscomments a1
> CROSS JOIN syscomments a2
> -- Add Primary Key to Numbers table
> ALTER TABLE Numbers
> ALTER COLUMN Number INT NOT NULL
> ALTER TABLE Numbers
> ADD CONSTRAINT PK_Numbers PRIMARY KEY (Number)
> -- Create inline UDF
> GO
> CREATE FUNCTION dbo.ParseDelimitedList (@.list AS NVARCHAR(4000))
> RETURNS TABLE
> AS
> RETURN (
> SELECT Number, LTRIM(RTRIM(CASE Number
> WHEN 1 THEN SUBSTRING(@.list, 1,
> CASE WHEN CHARINDEX(';', @.list, Number + 1) > 0 THEN
> CHARINDEX(';', @.list, Number + 1) - 1
> ELSE LEN(@.list) - CHARINDEX(';', @.list, Number + 1)
> END)
> ELSE SUBSTRING(@.list, Number + 1,
> CASE WHEN CHARINDEX(';', @.list, Number + 1) > 0 THEN
> CHARINDEX(';', @.list, Number + 1) - Number - 1
> ELSE LEN(@.list)
> END)
> END)) AS Value
> FROM Numbers
> WHERE (SUBSTRING(@.list, Number, 1) = ';' OR Number = 1)
> )
> GO
> -- End of the Numbers table/UDF initialization.
> CREATE TABLE TestInput([id] INT PRIMARY KEY,
> a VARCHAR(8000),
> b VARCHAR(8000))
> INSERT INTO TestInput([id], a)
> SELECT 0, 'Col1;Col2'
> UNION SELECT 1, 'New York;USA'
> UNION SELECT 2, 'Rio;Brasil'
> UNION SELECT 3, 'Tokio;Japan'
> DECLARE @.sql VARCHAR(8000)
> DECLARE @.temp_str VARCHAR(8000)
> DECLARE @.cols TABLE ([id_num] INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
> [col_name] VARCHAR(8000))
> SELECT @.temp_str = a
> FROM TestInput
> WHERE [id] = 0
> INSERT INTO @.cols([col_name])
> SELECT Value
> FROM dbo.ParseDelimitedList(@.temp_str)
> ORDER BY [Number]
> DECLARE @.col_count INT
> SELECT @.col_count = MAX([id_num])
> FROM @.cols
> DECLARE @.vals TABLE ([id_num] INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
> [value] VARCHAR(8000))
> DECLARE @.id INT
> SELECT @.id = 1
> DECLARE @.i INT
> WHILE @.id <= (SELECT MAX([id]) FROM TestInput)
> BEGIN
> SELECT @.temp_str = a
> FROM TestInput
> WHERE [id] = @.id
> IF NOT(@.temp_str IS NULL)
> BEGIN
> INSERT INTO @.vals([value])
> SELECT [Value]
> FROM dbo.ParseDelimitedList(@.temp_str)
> ORDER BY [Number]
> SELECT @.temp_str = ''
> SELECT @.i = 1
> WHILE @.i <= @.col_count
> BEGIN
> SELECT @.temp_str = @.temp_str + '<' +
> (
> SELECT [col_name]
> FROM @.cols
> WHERE [id_num] = @.i
> ) + '>'
> SELECT @.temp_str = @.temp_str +
> (
> SELECT COALESCE([value], '')
> FROM @.vals
> WHERE [id_num] = @.i + (@.id - 1) * @.col_count
> )
> SELECT @.temp_str = @.temp_str + '</' +
> (
> SELECT [col_name]
> FROM @.cols
> WHERE [id_num] = @.i
> ) + '>'
> SELECT @.i = @.i + 1
> END
> UPDATE TestInput
> SET b = @.temp_str
> WHERE [id] = @.id
> END
> SELECT @.id = @.id + 1
> END
> SELECT *
> FROM @.vals
> SELECT *
> FROM TestInput
>
>
> "psychodad71 via webservertalk.com" <u2248@.uwe> wrote in message
> news:6232e7ee08949@.uwe...
>|||psychodad71 via webservertalk.com wrote:
> I have a table with 2 columns. Column a(varchar(max)) and column b(xml).
> Column a contains the following data:
> Col1;Col2
> New York;USA
> Rio;Brasil
> Tokio;Japan
> The first line contains the column header, the following the data.
> The data should be transferred to column b with the following xml-structur
e:
> <Col1>New York</Col1><Col2>USA</Col2>
> <Col1>Rio</Col1><Col2>Brasil</Col2>
> <Col1>Tokio</Col1><Col2>Japan</Col2>
> The number of columns and the column names are various.
> Any ideas?
If your database supports access to external scripting languages, dump
column a out and pass it through the following filter
awk -F\; 'BEGIN {ORS=""}
{if(NR==1)n=split($0,gi);else{for(i=1;i<=NF;++i)print "<" gi[i] ">" $i
"</" gi[i] ">";print "\n"}}'
and read the result into column b. The GNU awk processor for Windows can
be downloaded from http://gnuwin32.sourceforge.net/packages/gawk.htm
///Peter|||Your table will need something to identity the first row, so I added an iden
tity
col. The first row inserted is assumed to be a column header.
CREATE TABLE #t1
(
id int identity primary key,
city VARCHAR(50),
xdata xml DEFAULT ''
)
INSERT INTO #t1 (city) values ('Col1;Col2;Col3;Col4')
INSERT INTO #t1 (city) values ('New York;Boston;Chicago;USA')
INSERT INTO #t1 (city) values ('Rio;Bla;Sao Paulo;Brasil')
INSERT INTO #t1 (city) values ('Tokio;Nagasaki;ABCD;Japan')
INSERT INTO #t1 (city) values ('Tokio;Nagasaki;Japan')
INSERT INTO #t1 (city) values ('Tokio;Nagasaki;ABCD;EF;Japan')
The city column has a compound value in it. You can use recursion to "unflat
ten"
this into a table with one row per city. Once you have done that you can
compose that table into xml, using recursion again.
-- start by making CTE of elementNames
WITH elementNames
AS
(
SELECT TOP(1) id, 1 as colNum, LEFT(city+';', CHARINDEX(';', city+';')-1)
as colName, RIGHT(city+';', LEN(city+';')-CHARINDEX(';', city+';')) as remai
n
from #t1
ORDER BY id
UNION ALL
SELECT t.id, en.colNum + 1 as colNum, LEFT(en.remain, CHARINDEX(';', en.rema
in)-1)
as single, RIGHT(en.remain, LEN(en.remain)-CHARINDEX(';', en.remain)) as
remain from elementNames en
JOIN #t1 AS t ON t.id = en.id
WHERE LEN(remain)>0
),
-- now recurse to "unflatten" the composite value in the city column
pos
AS
(
-- find the first city
SELECT id, 1 as colNum, LEFT(city+';', CHARINDEX(';', city+';')-1) as single
,
RIGHT(city+';', LEN(city+';')-CHARINDEX(';', city+';')) as remain from #t1
WHERE id not in (select id from elementNames)
UNION ALL
-- find the rest
SELECT id, colNum + 1 as colNum,
LEFT(remain, CHARINDEX(';', remain)-1) as single, RIGHT(remain, LEN(remain)-
CHARINDEX(';',
remain))
as remain from pos
where LEN(remain) > 0
and id not in (select id from elementNames)
),
-- now compose xml of of the expanded table
compose
as
(
SELECT p.id, p.colNum, CAST('<' + e.colName + '>' + p.single + '</' + e.colN
ame
+ '>'
as VARCHAR(MAX)) as xdata from pos AS p
JOIN elementNames AS e ON p.colNum = e.colNum
where p.colNum = 1
UNION ALL
SELECT p.id, p.colNum, CAST(c.xdata + '<' + e.colName + '>' + p.single +
'</' + e.colName + '>'
as VARCHAR(MAX)) as xdata from pos AS p
JOIN elementNames AS e ON p.colNum = e.colNum
JOIN compose AS c on p.colNum = c.colNum+1 and p.id = c.id
)
-- use composed xml to update the original table
UPDATE #t1 set xdata = (SELECT xdata from compose where #t1.id = compose.id
AND compose.colNum = (SELECT MAX(colNum) from compose as c WHERE c.id = #t1.
id)
)
Then to test the results:
SELECT * FROM #t1
1 Col1;Col2;Col3;Col4 NULL
2 New York;Boston;Chicago;USA <Col1>New
York</Col1><Col2>Boston</Col2><Col3>Chicago</Col3><Col4>USA</Col4>
3 Rio;Bla;Sao Paulo;Brasil <Col1>Rio</Co
l1><Col2>Bla</Col2><Col3>Sao
Paulo</Col3><Col4>Brasil</Col4>
4 Tokio;Nagasaki;ABCD;Japan <Col1>Tokio</
Col1><Col2>Nagasaki</Col2><Col3>ABCD</Col3><Col4>Japan</Col4>
5 Tokio;Nagasaki;Japan <Col1>Tokio</
Col1><Col2>Nagasaki</Col2><Col3>Japan</Col3>
6 Tokio;Nagasaki;ABCD;EF;Japan <Col1>Tokio</
Col1><Col2>Nagasaki</Col2><Col3>ABCD</Col3><Col4>EF</Col4>
Note that this works as even when the number of column headings do not match
the number of cities, though the results might not be what you want.
Dan

> I have a table with 2 columns. Column a(varchar(max)) and column
> b(xml). Column a contains the following data:
> Col1;Col2
> New York;USA
> Rio;Brasil
> Tokio;Japan
> The first line contains the column header, the following the data.
> The data should be transferred to column b with the following
> xml-structure:
> <Col1>New York</Col1><Col2>USA</Col2>
> <Col1>Rio</Col1><Col2>Brasil</Col2>
> <Col1>Tokio</Col1><Col2>Japan</Col2>
> The number of columns and the column names are various.
> Any ideas?
> Thanks psychodad71
>|||For some reason I see that some of the xml like stuff I have shown in this q
uery seems to be lost once it is posted. I have attached a text file version
of it.
Dan|||Alternatively, using a numbers table
as in http://www.aspfaq.com/show.asp?id=2516
you can do this
;
with Headers(id,rn,ColName)
as(
select id,
rank() over(order by Number),
ltrim(substring(city,
Number,
charindex(';',
city + ';',
Number) - Number))
from #t1
inner join Numbers on Number between 1 and len(city) + 1
and substring(';' + city, Number, 1) = ';'
where id=1),
Cities(id,rn,City)
as(
select id,
rank() over(partition by id order by Number),
ltrim(substring(city,
Number,
charindex(';',
city + ';',
Number) - Number))
from #t1
inner join Numbers on Number between 1 and len(city) + 1
and substring(';' + city, Number, 1) = ';'
where id>1)
update #t1
set xdata=(select cast('<'+h.ColName+'>'+c.City+'</'+h.ColName+'>' as
xml)
from Headers h
inner join Cities c on c.rn=h.rn
where c.id=#t1.id
for xml path(''))
select * from #t1
Regards
Mark|||Slight correction.
where c.id=#t1.id
for xml path(''))
should be
where c.id=#t1.id
order by c.rn
for xml path(''))

from SQL 2005 to SQL 2000

Is there an easy way to import table data from SQL 2005 to SQL 2000?
Here's the story:
I want to use SQL 2005 b/c I like varchar(max) over TEXT and several other features. However there is another developer with a SQL 2000 DB who needs to be able to pull data from my tables. If I use something like varchar(max), xml, etc what would the other developer need to do to import my data?
I know that going in the reverse direction is possible, pushing data from a SQL 2005 DB to a SQL 2000 ( http://msdn2.microsoft.com/en-us/library/ms191212.aspx )
I'm mostly a programmer slowly turning into a dba so please excuse me if I'm missing something obvious.
Thanks.
--Alex

Alex,

If you want to transfer data from SQL 2005 database into SQL 2000 database BCP as you pointed out is the only option. You can use bcp from SQL 2000 installation and it will see datatypes that are compatible with SQL 2000. varchar(max) and xml I believe will be seen as text.

If you want to access data from SQL 2005 instance on SQL 2000 instance (without moving the data between instances) you can do so with linked servers with the same restriction as above (new datatypes will be seen as old ones on SQL 2000).

Regards,
Boris.

from clause

How to write Select..From with declared variable?
I want to do something like this..
declare @.table varchar(50)
set @.table = 'Users'
Select name
From @.table
HrckoAre you talking about a table variable? Look it up in the Books
OnLine; basic syntax is
DECLARE @.table TABLE (name varchar(100))
INSERT INTO @.table (name)
VALUES('Tom')
SELECT name FROM @.table
HTH,
Stu|||Hi
Dynamic SQL:
http://www.sommarskog.se/dynamic_sql.html
--
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/
"Hrcko" <hrvoje.voda2@.zg.htnet.hr> wrote in message
news:dn2bcb$4j9$1@.ss405.t-com.hr...
> How to write Select..From with declared variable?
> I want to do something like this..
> declare @.table varchar(50)
> set @.table = 'Users'
> Select name
> From @.table
> Hrcko
>

Freetext Search - SQL CONTAINS (column,"R-483*") FAILS

Hi
I would like to search for "R-483" phase using SQL CONTAINS
function :
DECLARE @.i_FreeText Varchar(255)
SET @.i_FreeText= '"r-483*"'
SELECT TOP 10 * from iBlockFreeText
where
(@.i_FreeText is null or contains(iBlockFreeText.Content,
@.i_FreeText))
, but it fails. It's okay when i use
SET @.i_FreeText= '"r-483"' (without asterix)
It's also okay when I search for
SET @.i_FreeText= '"filip-483*"'
I thought it's noise character problem, but after deleting noise words
in noise.* files nothing has changed... I have to use double quots ""
due to exact phases with spaces ex. "exact phase".
Can somebody help me ? Thank's in advance
Ragards
Filip Fiolka
This works for me. Exactly what do you mean by fails?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Filip Fiolka" <filip@.lac.gda.pl> wrote in message
news:f1eb0b6.0501070414.153a4007@.posting.google.co m...
> Hi
> I would like to search for "R-483" phase using SQL CONTAINS
> function :
> DECLARE @.i_FreeText Varchar(255)
> SET @.i_FreeText= '"r-483*"'
> SELECT TOP 10 * from iBlockFreeText
> where
> (@.i_FreeText is null or contains(iBlockFreeText.Content,
> @.i_FreeText))
> , but it fails. It's okay when i use
> SET @.i_FreeText= '"r-483"' (without asterix)
> It's also okay when I search for
> SET @.i_FreeText= '"filip-483*"'
> I thought it's noise character problem, but after deleting noise words
> in noise.* files nothing has changed... I have to use double quots ""
> due to exact phases with spaces ex. "exact phase".
> Can somebody help me ? Thank's in advance
> Ragards
> Filip Fiolka
|||Filip,
Sure, I can. Could you post the full output of -- SELECT @.@.version -- as
this information is most helpful in troubleshooting SQL FTS issues. Did you
delete all words in the language specific noise word file or just the single
letters? What noise word file did you delete the words from? Did you run a
Full Population after deleting these words?
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Filip Fiolka" <filip@.lac.gda.pl> wrote in message
news:f1eb0b6.0501070414.153a4007@.posting.google.co m...
> Hi
> I would like to search for "R-483" phase using SQL CONTAINS
> function :
> DECLARE @.i_FreeText Varchar(255)
> SET @.i_FreeText= '"r-483*"'
> SELECT TOP 10 * from iBlockFreeText
> where
> (@.i_FreeText is null or contains(iBlockFreeText.Content,
> @.i_FreeText))
> , but it fails. It's okay when i use
> SET @.i_FreeText= '"r-483"' (without asterix)
> It's also okay when I search for
> SET @.i_FreeText= '"filip-483*"'
> I thought it's noise character problem, but after deleting noise words
> in noise.* files nothing has changed... I have to use double quots ""
> due to exact phases with spaces ex. "exact phase".
> Can somebody help me ? Thank's in advance
> Ragards
> Filip Fiolka
|||Thank's for reply. I realized that I cannot delete all noise words from
noise.* file- I should leave space at last. After that I could find my
'"R-483*"' product. But I cannot still see the reason why :
CONTAINS(column,'R-438')
and
CONTAINS(column,'"R-438"')
works, but
CONTAINS(column,'"R-438*"') fails when "r" is a noise word?!
Perhaps You will be able to explain that.
SELECT @.@.version :
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on
Windows NT 5.2 (Build 3790: )
Thank's in advance
Filip Fiolka
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||You're welcome, Filip Fiolka,
This is why I ask for the @.@.version information in nearly all of my initial
replies. You have SQL Server 2000 SP3 installed on Windows Server 2003
(Windows NT 5.2) and this OS Platform uses a new wordbreaker dll
(langwbrk.dll) that breaks the string "R-438" as follows:
Original text: 'R-438'
IWordSink::PutWord: cwcSrcLen 1, cwcSrcPos 0, cwc 1, 'R'
IWordSink::PutAltWord: cwcSrcLen 3, cwcSrcPos 2, cwc 3, '438'
IWordSink::PutWord: cwcSrcLen 3, cwcSrcPos 2, cwc 5, 'NN438'
NN438 indicates that Win2003 treats this as a number. In the FT-enable
table's column text, do you have other rows of values, such as "R-4385" or
"R-43800" or is the trailing values alphanumeric values?
Thanks,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"FIlip FIolka" <filip@.lac.gda.pl> wrote in message
news:eWU#n6u9EHA.3624@.TK2MSFTNGP10.phx.gbl...
> Thank's for reply. I realized that I cannot delete all noise words from
> noise.* file- I should leave space at last. After that I could find my
> '"R-483*"' product. But I cannot still see the reason why :
> CONTAINS(column,'R-438')
> and
> CONTAINS(column,'"R-438"')
> works, but
> CONTAINS(column,'"R-438*"') fails when "r" is a noise word?!
> Perhaps You will be able to explain that.
> SELECT @.@.version :
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on
> Windows NT 5.2 (Build 3790: )
> Thank's in advance
> Filip Fiolka
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
|||There are no other values like %R-438%. "R-483" is a specyfic name, so
there are no trailing values of that ("R-483" is never a prefix). My
FT-enable table's column rows consist of file start content.
Thanks
Filip Fiolka
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Filip,
Ok, then the Windows Server 2003 (Win2003) wordbreaker dll - langwbrk.dll -
is treating this number "438" as a number:
IWordSink::PutAltWord: cwcSrcLen 3, cwcSrcPos 2, cwc 3, '438'
IWordSink::PutWord: cwcSrcLen 3, cwcSrcPos 2, cwc 5, 'NN438'
and not allowing any stemming of this number and therefore ignoring the
trailing wildcard "*". Specifically, when this query is executed:
CONTAINS(column,'"R-438*"')
and "R" is a noise word, the hyphen or dash "-" is thrown away and all that
is left is to return rows that contain the number 438. This behavior is
specific to Win2003 and its wordbreaker langwbrk.dll
Hope that helps,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"FIlip FIolka" <filip@.lac.gda.pl> wrote in message
news:ejOnLPh#EHA.2580@.TK2MSFTNGP15.phx.gbl...
> There are no other values like %R-438%. "R-483" is a specyfic name, so
> there are no trailing values of that ("R-483" is never a prefix). My
> FT-enable table's column rows consist of file start content.
> Thanks
> Filip Fiolka
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!

Freaking column names

I'm dealing with a database with tables that have freaking columns.
Partial DDL:
Create table [tbl IMN] ([Ave Cost-Mn] varchar(10))
-- yeah, this column contains string value

Now, I'd like to rename all these freaking columns without special
charaters like '-', 'whitespace' etc systematically (meaning loop
through all tables and columns dynamically). It seems that the
sp_rename proc can't handle some function call or ...?
e.g.
exec sp_rename '[tbl-IMN].[Ave Cost-Mn]',replace('[Ave
Cost-Mn]','-',''),'COLUMN'

better if it can work,
exec sp_rename '[tbl-IMN].[Ave Cost-Mn]',replace('[Ave Cost-Mn]','-|
',''),'COLUMN'

TIANickName (dadada@.rock.com) writes:
> I'm dealing with a database with tables that have freaking columns.
> Partial DDL:
> Create table [tbl IMN] ([Ave Cost-Mn] varchar(10))
> -- yeah, this column contains string value
> Now, I'd like to rename all these freaking columns without special
> charaters like '-', 'whitespace' etc systematically (meaning loop
> through all tables and columns dynamically). It seems that the
> sp_rename proc can't handle some function call or ...?
> e.g.
> exec sp_rename '[tbl-IMN].[Ave Cost-Mn]',replace('[Ave
> Cost-Mn]','-',''),'COLUMN'
> better if it can work,
> exec sp_rename '[tbl-IMN].[Ave Cost-Mn]',replace('[Ave Cost-Mn]','-|
> ',''),'COLUMN'

Correct. In difference to most other languages, you cannot pass
expressions as parameters to stored procedures. You can only psss
constants and variables.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||How about this? Rather than a stored procedure, generate a script to
run against all of your columns, like so:

SELECT 'exec sp_rename ''' + Table_Name + '.'
+ Column_name + ''', ''' +
REPLACE(COLUMN_NAME, '-', '') + ''', ''COLUMN'''
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE '%-%'

Cut and paste the results of the query into a new QA window and execute
the statements.

HTH,
Stu|||Actually, I could do something like
select @.colTemp = Replace(col,'-','')
then
exec sp_rename '[tbl-IMN].[Ave Cost-Mn]',@.colTemp,'COLUMN'

I thought about that before posting but I did not try (just don't know
why somtimes I'm so freaking lazy).

Thanks for the note though.

Don|||Interesting idea, however, I don't think it works.|||Worked in my test bed; what error did you get? Just to make sure that
I was clear, the above command will NOT execute the stored procedure;
it'll just generate a script of all the columns in all the tables in
your database with a '-' in the name, like so:

exec sp_rename 'SPLAT.Check-ID', 'CheckID', 'COLUMN'
exec sp_rename 'SPLAT.Check-ID2', 'CheckID2', 'COLUMN'

You have to cut and paste that script into a new window in query
analyzer to actually execute the changes.

You could, of course, modify the SQL statement to actually execute the
stored procedure for you; I just prefer to do it this way so I can
visually check what I'm about to execute.

Stu|||Yeah, I see. Problem resolved. Thanks though.
Stu wrote:
> Worked in my test bed; what error did you get? Just to make sure that
> I was clear, the above command will NOT execute the stored procedure;
> it'll just generate a script of all the columns in all the tables in
> your database with a '-' in the name, like so:
> exec sp_rename 'SPLAT.Check-ID', 'CheckID', 'COLUMN'
> exec sp_rename 'SPLAT.Check-ID2', 'CheckID2', 'COLUMN'
> You have to cut and paste that script into a new window in query
> analyzer to actually execute the changes.
> You could, of course, modify the SQL statement to actually execute the
> stored procedure for you; I just prefer to do it this way so I can
> visually check what I'm about to execute.
> Stu

Fragmentation with large varchar column

Hi all,
I have a interesting situation with a table with a nvarchar column that
contains a xml string.
The average size of this field is about 2100 bytes. This means that I will
have few records per page. The primary key (with a clustered index) is in a
field not sequential.
In this way almost in all inserts I will have a page split and the
fragmentation grows dramatically.
Of course I tried to change the clustered index to a sequential field (a
datetime with a getdate() as default).
This avoid the fragmentation, but in my case causes many deadlocks (the
application has a high level of concurrency), because the I force all insert
s
to be in the last page.
We want to solve this problem with a minimum impact to the application and
to avoid that the high speed of fragmentation (the system is 24X7 and the
rebuild index has a high cost).
One idea is to change the datatype of this field to text.
In my opinion this would get better the fragmentation question, but I don't
know if the reading cost of this field would be problematic. This field is
always inserted (not updated) and is read totaly (without like or comparison
clauses).
Could you give some sugesstions or tips?
Thank you very much
Alexandre Calderaro
MSDBA
Avanade Italy> Of course I tried to change the clustered index to a sequential field (a
> datetime with a getdate() as default).
> This avoid the fragmentation, but in my case causes many deadlocks (the
> application has a high level of concurrency), because the I force all
> inserts
> to be in the last page.
The deadlocks are probably not due to the hotspot at the end of the table.
It may be that the indexing change introduced scans and this increased
deadlock likelihood. Did you recreate the primary key as non-clustered?
Fragmentation is only one piece of the performance puzzle. A clustered
index on an increasing value like datetime or IDENTITY is good for insert
performance and may also be good for scans/joins on the clustered key.
However, you need to consider the overall mix of queries to determine the
best indexing strategy, especially in a highly transactional environment.
It may be that the PK is the best choice for the clustered index, even at
the cost of fragmentation.
BTW, you can use DBCC INDEXDEFRAG to defragment in a 24x7 environment. For
SQL 2000, there is a post-SP4 hotfix to address INDEXDEFRAG locking issues.
Hope this helps.
Dan Guzman
SQL Server MVP
"Alex" <Alex@.discussions.microsoft.com> wrote in message
news:22D85878-C15E-4F79-9D17-41A72CE609B3@.microsoft.com...
> Hi all,
> I have a interesting situation with a table with a nvarchar column that
> contains a xml string.
> The average size of this field is about 2100 bytes. This means that I will
> have few records per page. The primary key (with a clustered index) is in
> a
> field not sequential.
> In this way almost in all inserts I will have a page split and the
> fragmentation grows dramatically.
> Of course I tried to change the clustered index to a sequential field (a
> datetime with a getdate() as default).
> This avoid the fragmentation, but in my case causes many deadlocks (the
> application has a high level of concurrency), because the I force all
> inserts
> to be in the last page.
> We want to solve this problem with a minimum impact to the application and
> to avoid that the high speed of fragmentation (the system is 24X7 and the
> rebuild index has a high cost).
> One idea is to change the datatype of this field to text.
> In my opinion this would get better the fragmentation question, but I
> don't
> know if the reading cost of this field would be problematic. This field is
> always inserted (not updated) and is read totaly (without like or
> comparison
> clauses).
> Could you give some sugesstions or tips?
> Thank you very much
> Alexandre Calderaro
> MSDBA
> Avanade Italy
>|||Thanks Dan,
I recreated the PK to a non-clustered index before change the clustered
index to a sequential field.
I was almost secure that the deadlock problem could be the hostspot, because
I have few rows per page. And that's the reason that I thoght about to chang
e
the datatype of the varchar field to text. Do you think that this could be
helpful?
We use IndexDefrag, but we have more than one file and this operation
doesn't migrates data between files. A consideration would be to have just
one file.
I have to control if our client installed this fix that you have mentioned.
Thanks again!
Alexandre
"Dan Guzman" wrote:

> The deadlocks are probably not due to the hotspot at the end of the table.
> It may be that the indexing change introduced scans and this increased
> deadlock likelihood. Did you recreate the primary key as non-clustered?
> Fragmentation is only one piece of the performance puzzle. A clustered
> index on an increasing value like datetime or IDENTITY is good for insert
> performance and may also be good for scans/joins on the clustered key.
> However, you need to consider the overall mix of queries to determine the
> best indexing strategy, especially in a highly transactional environment.
> It may be that the PK is the best choice for the clustered index, even at
> the cost of fragmentation.
> BTW, you can use DBCC INDEXDEFRAG to defragment in a 24x7 environment. Fo
r
> SQL 2000, there is a post-SP4 hotfix to address INDEXDEFRAG locking issues
.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Alex" <Alex@.discussions.microsoft.com> wrote in message
> news:22D85878-C15E-4F79-9D17-41A72CE609B3@.microsoft.com...
>
>|||> Of course I tried to change the clustered index to a sequential field (a
> datetime with a getdate() as default).
> This avoid the fragmentation, but in my case causes many deadlocks (the
> application has a high level of concurrency), because the I force all
> inserts
> to be in the last page.
The deadlocks are probably not due to the hotspot at the end of the table.
It may be that the indexing change introduced scans and this increased
deadlock likelihood. Did you recreate the primary key as non-clustered?
Fragmentation is only one piece of the performance puzzle. A clustered
index on an increasing value like datetime or IDENTITY is good for insert
performance and may also be good for scans/joins on the clustered key.
However, you need to consider the overall mix of queries to determine the
best indexing strategy, especially in a highly transactional environment.
It may be that the PK is the best choice for the clustered index, even at
the cost of fragmentation.
BTW, you can use DBCC INDEXDEFRAG to defragment in a 24x7 environment. For
SQL 2000, there is a post-SP4 hotfix to address INDEXDEFRAG locking issues.
Hope this helps.
Dan Guzman
SQL Server MVP
"Alex" <Alex@.discussions.microsoft.com> wrote in message
news:22D85878-C15E-4F79-9D17-41A72CE609B3@.microsoft.com...
> Hi all,
> I have a interesting situation with a table with a nvarchar column that
> contains a xml string.
> The average size of this field is about 2100 bytes. This means that I will
> have few records per page. The primary key (with a clustered index) is in
> a
> field not sequential.
> In this way almost in all inserts I will have a page split and the
> fragmentation grows dramatically.
> Of course I tried to change the clustered index to a sequential field (a
> datetime with a getdate() as default).
> This avoid the fragmentation, but in my case causes many deadlocks (the
> application has a high level of concurrency), because the I force all
> inserts
> to be in the last page.
> We want to solve this problem with a minimum impact to the application and
> to avoid that the high speed of fragmentation (the system is 24X7 and the
> rebuild index has a high cost).
> One idea is to change the datatype of this field to text.
> In my opinion this would get better the fragmentation question, but I
> don't
> know if the reading cost of this field would be problematic. This field is
> always inserted (not updated) and is read totaly (without like or
> comparison
> clauses).
> Could you give some sugesstions or tips?
> Thank you very much
> Alexandre Calderaro
> MSDBA
> Avanade Italy
>|||Thanks Dan,
I recreated the PK to a non-clustered index before change the clustered
index to a sequential field.
I was almost secure that the deadlock problem could be the hostspot, because
I have few rows per page. And that's the reason that I thoght about to chang
e
the datatype of the varchar field to text. Do you think that this could be
helpful?
We use IndexDefrag, but we have more than one file and this operation
doesn't migrates data between files. A consideration would be to have just
one file.
I have to control if our client installed this fix that you have mentioned.
Thanks again!
Alexandre
"Dan Guzman" wrote:

> The deadlocks are probably not due to the hotspot at the end of the table.
> It may be that the indexing change introduced scans and this increased
> deadlock likelihood. Did you recreate the primary key as non-clustered?
> Fragmentation is only one piece of the performance puzzle. A clustered
> index on an increasing value like datetime or IDENTITY is good for insert
> performance and may also be good for scans/joins on the clustered key.
> However, you need to consider the overall mix of queries to determine the
> best indexing strategy, especially in a highly transactional environment.
> It may be that the PK is the best choice for the clustered index, even at
> the cost of fragmentation.
> BTW, you can use DBCC INDEXDEFRAG to defragment in a 24x7 environment. Fo
r
> SQL 2000, there is a post-SP4 hotfix to address INDEXDEFRAG locking issues
.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Alex" <Alex@.discussions.microsoft.com> wrote in message
> news:22D85878-C15E-4F79-9D17-41A72CE609B3@.microsoft.com...
>
>|||Alex,
Have you identified the objects/processes involved in the deadlocks?
May be the deadlocks are related to the way you are accessing the tables.
INF: Analyzing and Avoiding Deadlocks in SQL Server
http://support.microsoft.com/defaul...kb;en-us;169960
Tracing Deadlocks
http://www.sqlservercentral.com/col...ngdeadlocks.asp
AMB
"Alex" wrote:
[vbcol=seagreen]
> Thanks Dan,
> I recreated the PK to a non-clustered index before change the clustered
> index to a sequential field.
> I was almost secure that the deadlock problem could be the hostspot, becau
se
> I have few rows per page. And that's the reason that I thoght about to cha
nge
> the datatype of the varchar field to text. Do you think that this could be
> helpful?
> We use IndexDefrag, but we have more than one file and this operation
> doesn't migrates data between files. A consideration would be to have just
> one file.
> I have to control if our client installed this fix that you have mentioned
.
> Thanks again!
> Alexandre
> "Dan Guzman" wrote:
>|||Alex,
Have you identified the objects/processes involved in the deadlocks?
May be the deadlocks are related to the way you are accessing the tables.
INF: Analyzing and Avoiding Deadlocks in SQL Server
http://support.microsoft.com/defaul...kb;en-us;169960
Tracing Deadlocks
http://www.sqlservercentral.com/col...ngdeadlocks.asp
AMB
"Alex" wrote:
[vbcol=seagreen]
> Thanks Dan,
> I recreated the PK to a non-clustered index before change the clustered
> index to a sequential field.
> I was almost secure that the deadlock problem could be the hostspot, becau
se
> I have few rows per page. And that's the reason that I thoght about to cha
nge
> the datatype of the varchar field to text. Do you think that this could be
> helpful?
> We use IndexDefrag, but we have more than one file and this operation
> doesn't migrates data between files. A consideration would be to have just
> one file.
> I have to control if our client installed this fix that you have mentioned
.
> Thanks again!
> Alexandre
> "Dan Guzman" wrote:
>|||The links Alejandro posted can help identify the problem queries and
deadlocking resource. Take a look at the execution plans of the queries
involved in the deadlock as this might help identify the reason for the
resource contention.
Changing varchar to text (or nvarchar to ntext) will certainly improve
density. However, queries that reference the column will incur an
additional i/o. It depends on your workload mix whether or not this is the
right thing to do.
Hope this helps.
Dan Guzman
SQL Server MVP
"Alex" <Alex@.discussions.microsoft.com> wrote in message
news:FD074D4F-879A-4BDD-892C-F7BB455A97C3@.microsoft.com...[vbcol=seagreen]
> Thanks Dan,
> I recreated the PK to a non-clustered index before change the clustered
> index to a sequential field.
> I was almost secure that the deadlock problem could be the hostspot,
> because
> I have few rows per page. And that's the reason that I thoght about to
> change
> the datatype of the varchar field to text. Do you think that this could be
> helpful?
> We use IndexDefrag, but we have more than one file and this operation
> doesn't migrates data between files. A consideration would be to have just
> one file.
> I have to control if our client installed this fix that you have
> mentioned.
> Thanks again!
> Alexandre
> "Dan Guzman" wrote:
>|||The links Alejandro posted can help identify the problem queries and
deadlocking resource. Take a look at the execution plans of the queries
involved in the deadlock as this might help identify the reason for the
resource contention.
Changing varchar to text (or nvarchar to ntext) will certainly improve
density. However, queries that reference the column will incur an
additional i/o. It depends on your workload mix whether or not this is the
right thing to do.
Hope this helps.
Dan Guzman
SQL Server MVP
"Alex" <Alex@.discussions.microsoft.com> wrote in message
news:FD074D4F-879A-4BDD-892C-F7BB455A97C3@.microsoft.com...[vbcol=seagreen]
> Thanks Dan,
> I recreated the PK to a non-clustered index before change the clustered
> index to a sequential field.
> I was almost secure that the deadlock problem could be the hostspot,
> because
> I have few rows per page. And that's the reason that I thoght about to
> change
> the datatype of the varchar field to text. Do you think that this could be
> helpful?
> We use IndexDefrag, but we have more than one file and this operation
> doesn't migrates data between files. A consideration would be to have just
> one file.
> I have to control if our client installed this fix that you have
> mentioned.
> Thanks again!
> Alexandre
> "Dan Guzman" wrote:
>

Fragmentation with large varchar column

Hi all,
I have a interesting situation with a table with a nvarchar column that
contains a xml string.
The average size of this field is about 2100 bytes. This means that I will
have few records per page. The primary key (with a clustered index) is in a
field not sequential.
In this way almost in all inserts I will have a page split and the
fragmentation grows dramatically.
Of course I tried to change the clustered index to a sequential field (a
datetime with a getdate() as default).
This avoid the fragmentation, but in my case causes many deadlocks (the
application has a high level of concurrency), because the I force all inserts
to be in the last page.
We want to solve this problem with a minimum impact to the application and
to avoid that the high speed of fragmentation (the system is 24X7 and the
rebuild index has a high cost).
One idea is to change the datatype of this field to text.
In my opinion this would get better the fragmentation question, but I don't
know if the reading cost of this field would be problematic. This field is
always inserted (not updated) and is read totaly (without like or comparison
clauses).
Could you give some sugesstions or tips?
Thank you very much
Alexandre Calderaro
MSDBA
Avanade Italy> Of course I tried to change the clustered index to a sequential field (a
> datetime with a getdate() as default).
> This avoid the fragmentation, but in my case causes many deadlocks (the
> application has a high level of concurrency), because the I force all
> inserts
> to be in the last page.
The deadlocks are probably not due to the hotspot at the end of the table.
It may be that the indexing change introduced scans and this increased
deadlock likelihood. Did you recreate the primary key as non-clustered?
Fragmentation is only one piece of the performance puzzle. A clustered
index on an increasing value like datetime or IDENTITY is good for insert
performance and may also be good for scans/joins on the clustered key.
However, you need to consider the overall mix of queries to determine the
best indexing strategy, especially in a highly transactional environment.
It may be that the PK is the best choice for the clustered index, even at
the cost of fragmentation.
BTW, you can use DBCC INDEXDEFRAG to defragment in a 24x7 environment. For
SQL 2000, there is a post-SP4 hotfix to address INDEXDEFRAG locking issues.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Alex" <Alex@.discussions.microsoft.com> wrote in message
news:22D85878-C15E-4F79-9D17-41A72CE609B3@.microsoft.com...
> Hi all,
> I have a interesting situation with a table with a nvarchar column that
> contains a xml string.
> The average size of this field is about 2100 bytes. This means that I will
> have few records per page. The primary key (with a clustered index) is in
> a
> field not sequential.
> In this way almost in all inserts I will have a page split and the
> fragmentation grows dramatically.
> Of course I tried to change the clustered index to a sequential field (a
> datetime with a getdate() as default).
> This avoid the fragmentation, but in my case causes many deadlocks (the
> application has a high level of concurrency), because the I force all
> inserts
> to be in the last page.
> We want to solve this problem with a minimum impact to the application and
> to avoid that the high speed of fragmentation (the system is 24X7 and the
> rebuild index has a high cost).
> One idea is to change the datatype of this field to text.
> In my opinion this would get better the fragmentation question, but I
> don't
> know if the reading cost of this field would be problematic. This field is
> always inserted (not updated) and is read totaly (without like or
> comparison
> clauses).
> Could you give some sugesstions or tips?
> Thank you very much
> Alexandre Calderaro
> MSDBA
> Avanade Italy
>|||Thanks Dan,
I recreated the PK to a non-clustered index before change the clustered
index to a sequential field.
I was almost secure that the deadlock problem could be the hostspot, because
I have few rows per page. And that's the reason that I thoght about to change
the datatype of the varchar field to text. Do you think that this could be
helpful?
We use IndexDefrag, but we have more than one file and this operation
doesn't migrates data between files. A consideration would be to have just
one file.
I have to control if our client installed this fix that you have mentioned.
Thanks again!
Alexandre
"Dan Guzman" wrote:
> > Of course I tried to change the clustered index to a sequential field (a
> > datetime with a getdate() as default).
> > This avoid the fragmentation, but in my case causes many deadlocks (the
> > application has a high level of concurrency), because the I force all
> > inserts
> > to be in the last page.
> The deadlocks are probably not due to the hotspot at the end of the table.
> It may be that the indexing change introduced scans and this increased
> deadlock likelihood. Did you recreate the primary key as non-clustered?
> Fragmentation is only one piece of the performance puzzle. A clustered
> index on an increasing value like datetime or IDENTITY is good for insert
> performance and may also be good for scans/joins on the clustered key.
> However, you need to consider the overall mix of queries to determine the
> best indexing strategy, especially in a highly transactional environment.
> It may be that the PK is the best choice for the clustered index, even at
> the cost of fragmentation.
> BTW, you can use DBCC INDEXDEFRAG to defragment in a 24x7 environment. For
> SQL 2000, there is a post-SP4 hotfix to address INDEXDEFRAG locking issues.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Alex" <Alex@.discussions.microsoft.com> wrote in message
> news:22D85878-C15E-4F79-9D17-41A72CE609B3@.microsoft.com...
> > Hi all,
> >
> > I have a interesting situation with a table with a nvarchar column that
> > contains a xml string.
> > The average size of this field is about 2100 bytes. This means that I will
> > have few records per page. The primary key (with a clustered index) is in
> > a
> > field not sequential.
> > In this way almost in all inserts I will have a page split and the
> > fragmentation grows dramatically.
> > Of course I tried to change the clustered index to a sequential field (a
> > datetime with a getdate() as default).
> > This avoid the fragmentation, but in my case causes many deadlocks (the
> > application has a high level of concurrency), because the I force all
> > inserts
> > to be in the last page.
> > We want to solve this problem with a minimum impact to the application and
> > to avoid that the high speed of fragmentation (the system is 24X7 and the
> > rebuild index has a high cost).
> > One idea is to change the datatype of this field to text.
> > In my opinion this would get better the fragmentation question, but I
> > don't
> > know if the reading cost of this field would be problematic. This field is
> > always inserted (not updated) and is read totaly (without like or
> > comparison
> > clauses).
> > Could you give some sugesstions or tips?
> > Thank you very much
> >
> > Alexandre Calderaro
> > MSDBA
> > Avanade Italy
> >
>
>|||Alex,
Have you identified the objects/processes involved in the deadlocks?
May be the deadlocks are related to the way you are accessing the tables.
INF: Analyzing and Avoiding Deadlocks in SQL Server
http://support.microsoft.com/default.aspx?scid=kb;en-us;169960
Tracing Deadlocks
http://www.sqlservercentral.com/columnists/skumar/tracingdeadlocks.asp
AMB
"Alex" wrote:
> Thanks Dan,
> I recreated the PK to a non-clustered index before change the clustered
> index to a sequential field.
> I was almost secure that the deadlock problem could be the hostspot, because
> I have few rows per page. And that's the reason that I thoght about to change
> the datatype of the varchar field to text. Do you think that this could be
> helpful?
> We use IndexDefrag, but we have more than one file and this operation
> doesn't migrates data between files. A consideration would be to have just
> one file.
> I have to control if our client installed this fix that you have mentioned.
> Thanks again!
> Alexandre
> "Dan Guzman" wrote:
> > > Of course I tried to change the clustered index to a sequential field (a
> > > datetime with a getdate() as default).
> > > This avoid the fragmentation, but in my case causes many deadlocks (the
> > > application has a high level of concurrency), because the I force all
> > > inserts
> > > to be in the last page.
> >
> > The deadlocks are probably not due to the hotspot at the end of the table.
> > It may be that the indexing change introduced scans and this increased
> > deadlock likelihood. Did you recreate the primary key as non-clustered?
> >
> > Fragmentation is only one piece of the performance puzzle. A clustered
> > index on an increasing value like datetime or IDENTITY is good for insert
> > performance and may also be good for scans/joins on the clustered key.
> > However, you need to consider the overall mix of queries to determine the
> > best indexing strategy, especially in a highly transactional environment.
> > It may be that the PK is the best choice for the clustered index, even at
> > the cost of fragmentation.
> >
> > BTW, you can use DBCC INDEXDEFRAG to defragment in a 24x7 environment. For
> > SQL 2000, there is a post-SP4 hotfix to address INDEXDEFRAG locking issues.
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > "Alex" <Alex@.discussions.microsoft.com> wrote in message
> > news:22D85878-C15E-4F79-9D17-41A72CE609B3@.microsoft.com...
> > > Hi all,
> > >
> > > I have a interesting situation with a table with a nvarchar column that
> > > contains a xml string.
> > > The average size of this field is about 2100 bytes. This means that I will
> > > have few records per page. The primary key (with a clustered index) is in
> > > a
> > > field not sequential.
> > > In this way almost in all inserts I will have a page split and the
> > > fragmentation grows dramatically.
> > > Of course I tried to change the clustered index to a sequential field (a
> > > datetime with a getdate() as default).
> > > This avoid the fragmentation, but in my case causes many deadlocks (the
> > > application has a high level of concurrency), because the I force all
> > > inserts
> > > to be in the last page.
> > > We want to solve this problem with a minimum impact to the application and
> > > to avoid that the high speed of fragmentation (the system is 24X7 and the
> > > rebuild index has a high cost).
> > > One idea is to change the datatype of this field to text.
> > > In my opinion this would get better the fragmentation question, but I
> > > don't
> > > know if the reading cost of this field would be problematic. This field is
> > > always inserted (not updated) and is read totaly (without like or
> > > comparison
> > > clauses).
> > > Could you give some sugesstions or tips?
> > > Thank you very much
> > >
> > > Alexandre Calderaro
> > > MSDBA
> > > Avanade Italy
> > >
> >
> >
> >|||The links Alejandro posted can help identify the problem queries and
deadlocking resource. Take a look at the execution plans of the queries
involved in the deadlock as this might help identify the reason for the
resource contention.
Changing varchar to text (or nvarchar to ntext) will certainly improve
density. However, queries that reference the column will incur an
additional i/o. It depends on your workload mix whether or not this is the
right thing to do.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Alex" <Alex@.discussions.microsoft.com> wrote in message
news:FD074D4F-879A-4BDD-892C-F7BB455A97C3@.microsoft.com...
> Thanks Dan,
> I recreated the PK to a non-clustered index before change the clustered
> index to a sequential field.
> I was almost secure that the deadlock problem could be the hostspot,
> because
> I have few rows per page. And that's the reason that I thoght about to
> change
> the datatype of the varchar field to text. Do you think that this could be
> helpful?
> We use IndexDefrag, but we have more than one file and this operation
> doesn't migrates data between files. A consideration would be to have just
> one file.
> I have to control if our client installed this fix that you have
> mentioned.
> Thanks again!
> Alexandre
> "Dan Guzman" wrote:
>> > Of course I tried to change the clustered index to a sequential field
>> > (a
>> > datetime with a getdate() as default).
>> > This avoid the fragmentation, but in my case causes many deadlocks (the
>> > application has a high level of concurrency), because the I force all
>> > inserts
>> > to be in the last page.
>> The deadlocks are probably not due to the hotspot at the end of the
>> table.
>> It may be that the indexing change introduced scans and this increased
>> deadlock likelihood. Did you recreate the primary key as non-clustered?
>> Fragmentation is only one piece of the performance puzzle. A clustered
>> index on an increasing value like datetime or IDENTITY is good for insert
>> performance and may also be good for scans/joins on the clustered key.
>> However, you need to consider the overall mix of queries to determine the
>> best indexing strategy, especially in a highly transactional environment.
>> It may be that the PK is the best choice for the clustered index, even at
>> the cost of fragmentation.
>> BTW, you can use DBCC INDEXDEFRAG to defragment in a 24x7 environment.
>> For
>> SQL 2000, there is a post-SP4 hotfix to address INDEXDEFRAG locking
>> issues.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Alex" <Alex@.discussions.microsoft.com> wrote in message
>> news:22D85878-C15E-4F79-9D17-41A72CE609B3@.microsoft.com...
>> > Hi all,
>> >
>> > I have a interesting situation with a table with a nvarchar column that
>> > contains a xml string.
>> > The average size of this field is about 2100 bytes. This means that I
>> > will
>> > have few records per page. The primary key (with a clustered index) is
>> > in
>> > a
>> > field not sequential.
>> > In this way almost in all inserts I will have a page split and the
>> > fragmentation grows dramatically.
>> > Of course I tried to change the clustered index to a sequential field
>> > (a
>> > datetime with a getdate() as default).
>> > This avoid the fragmentation, but in my case causes many deadlocks (the
>> > application has a high level of concurrency), because the I force all
>> > inserts
>> > to be in the last page.
>> > We want to solve this problem with a minimum impact to the application
>> > and
>> > to avoid that the high speed of fragmentation (the system is 24X7 and
>> > the
>> > rebuild index has a high cost).
>> > One idea is to change the datatype of this field to text.
>> > In my opinion this would get better the fragmentation question, but I
>> > don't
>> > know if the reading cost of this field would be problematic. This field
>> > is
>> > always inserted (not updated) and is read totaly (without like or
>> > comparison
>> > clauses).
>> > Could you give some sugesstions or tips?
>> > Thank you very much
>> >
>> > Alexandre Calderaro
>> > MSDBA
>> > Avanade Italy
>> >
>>