Nonprintable / encrypted characters is not found

Hi All

I have a table with one column :

CREATE TABLE test2
( a char(15)
primary KEY CLUSTERED )

The column a is filled with encrypted data
(contains control and extended characters).
On my test, the select statement for one row
in table test2 does not always work sucessfully.

Below is my unsucessful statement for the second
row followed by 9 rows data. Each row is displayed
in 2 versions, text and VB ascii code.

select * from test2
where a = '\0<[\
|^[\]}{;\'

----
Row-1
%;>.[
,)]/\-,=/
37;59;62;46;91;10;44;41;93;47;92;45;44;61;47;
Row-2
\0<[\
|^[\]}{;\
92;48;60;91;92;10;124;94;91;92;93;125;123;59;92;
Row-3
\0<[\ ,)? {=\?
92;48;60;91;92;11;44;41;63;3;123;127;61;92;63;
Row-4
\0<[\ \%:_`- ]_
92;48;60;91;92;11;92;37;58;95;96;45;5;93;95;
Row-5
\0<[\ \^:& }{;\
92;48;60;91;92;11;92;94;58;38;7;125;123;59;92;
Row-6
\0[*_1>\_\ }{@.+
92;48;91;42;95;49;62;92;95;92;7;125;123;64;43;
Row-7
].>^/. ]-=}<)^&
93;46;62;94;47;46;8;93;45;61;125;60;41;94;38;
Row-8
]0_^{
}^~_|~{^_
93;48;95;94;123;10;125;94;126;95;124;126;123;94;95 ;
Row-9
{
}{31{2|02~||
123;10;125;123;127;51;49;123;50;124;48;50;126;124; 124;
----

I have 3 questions for all of you :
- Why SQL2000 can not find the second row (certain row)?
- Can SQL2000 handle character 0 to 255 ?
- Does my encrypted method produce bad data for SQL2000?

Thanks in advance

Anita Hery

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!"Anita" <anonymous@.devdex.com> wrote in message
news:4029728d$0$196$75868355@.news.frii.net...
> Hi All
> I have a table with one column :
> CREATE TABLE test2
> ( a char(15)
> primary KEY CLUSTERED )
> The column a is filled with encrypted data
> (contains control and extended characters).
> On my test, the select statement for one row
> in table test2 does not always work sucessfully.
> Below is my unsucessful statement for the second
> row followed by 9 rows data. Each row is displayed
> in 2 versions, text and VB ascii code.
> select * from test2
> where a = '\0<[\
> |^[\]}{;\'
> ----
> Row-1
> %;>.[
> ,)]/\-,=/
> 37;59;62;46;91;10;44;41;93;47;92;45;44;61;47;
> Row-2
> \0<[\
> |^[\]}{;\
> 92;48;60;91;92;10;124;94;91;92;93;125;123;59;92;
> Row-3
> \0<[\ ,)? {=\?
> 92;48;60;91;92;11;44;41;63;3;123;127;61;92;63;
> Row-4
> \0<[\ \%:_`- ]_
> 92;48;60;91;92;11;92;37;58;95;96;45;5;93;95;
> Row-5
> \0<[\ \^:& }{;\
> 92;48;60;91;92;11;92;94;58;38;7;125;123;59;92;
> Row-6
> \0[*_1>\_\ }{@.+
> 92;48;91;42;95;49;62;92;95;92;7;125;123;64;43;
> Row-7
> ].>^/. ]-=}<)^&
> 93;46;62;94;47;46;8;93;45;61;125;60;41;94;38;
> Row-8
> ]0_^{
> }^~_|~{^_
> 93;48;95;94;123;10;125;94;126;95;124;126;123;94;95 ;
> Row-9
> {
> }{31{2|02~||
> 123;10;125;123;127;51;49;123;50;124;48;50;126;124; 124;
> ----
> I have 3 questions for all of you :
> - Why SQL2000 can not find the second row (certain row)?
> - Can SQL2000 handle character 0 to 255 ?
> - Does my encrypted method produce bad data for SQL2000?
> Thanks in advance
> Anita Hery
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

1. I don't know, but the most obvious reason is that your search string
doesn't exactly match the encrypted string. Did you enter the SELECT
statement exactly as above? If so, then the issue could be that SQL ignores
whitespace, so you would need something like this to handle the newline:

select * from test2
where a = '\0<[\' + char(10) + '|^[\]}{;\'

2. Yes

3. As long as the encrypted string is in a character set supported by the
server (and you could use Unicode if necessary), then there shouldn't be any
specific issues - it's up to your application to do the
encrpytion/decryption.

Simon|||"Simon Hayes" <sql@.hayes.ch> wrote in message
news:402a7e11$1_3@.news.bluewin.ch...
> "Anita" <anonymous@.devdex.com> wrote in message
> news:4029728d$0$196$75868355@.news.frii.net...
> > Hi All
> > I have a table with one column :
> > CREATE TABLE test2
> > ( a char(15)
> > primary KEY CLUSTERED )
> > The column a is filled with encrypted data
> > (contains control and extended characters).
> > On my test, the select statement for one row
> > in table test2 does not always work sucessfully.
> > Below is my unsucessful statement for the second
> > row followed by 9 rows data. Each row is displayed
> > in 2 versions, text and VB ascii code.
> > select * from test2
> > where a = '\0<[\
> > |^[\]}{;\'
> > ----
> > Row-1
> > %;>.[
> > ,)]/\-,=/
> > 37;59;62;46;91;10;44;41;93;47;92;45;44;61;47;
> > Row-2
> > \0<[\
> > |^[\]}{;\
> > 92;48;60;91;92;10;124;94;91;92;93;125;123;59;92;
> > Row-3
> > \0<[\ ,)? {=\?
> > 92;48;60;91;92;11;44;41;63;3;123;127;61;92;63;
> > Row-4
> > \0<[\ \%:_`- ]_
> > 92;48;60;91;92;11;92;37;58;95;96;45;5;93;95;
> > Row-5
> > \0<[\ \^:& }{;\
> > 92;48;60;91;92;11;92;94;58;38;7;125;123;59;92;
> > Row-6
> > \0[*_1>\_\ }{@.+
> > 92;48;91;42;95;49;62;92;95;92;7;125;123;64;43;
> > Row-7
> > ].>^/. ]-=}<)^&
> > 93;46;62;94;47;46;8;93;45;61;125;60;41;94;38;
> > Row-8
> > ]0_^{
> > }^~_|~{^_
> > 93;48;95;94;123;10;125;94;126;95;124;126;123;94;95 ;
> > Row-9
> > {
> > }{31{2|02~||
> > 123;10;125;123;127;51;49;123;50;124;48;50;126;124; 124;
> > ----
> > I have 3 questions for all of you :
> > - Why SQL2000 can not find the second row (certain row)?
> > - Can SQL2000 handle character 0 to 255 ?
> > - Does my encrypted method produce bad data for SQL2000?
> > Thanks in advance
> > Anita Hery
> > *** Sent via Developersdex http://www.developersdex.com ***
> > Don't just participate in USENET...get rewarded for it!
> 1. I don't know, but the most obvious reason is that your search string
> doesn't exactly match the encrypted string. Did you enter the SELECT
> statement exactly as above? If so, then the issue could be that SQL
ignores
> whitespace, so you would need something like this to handle the newline:
> select * from test2
> where a = '\0<[\' + char(10) + '|^[\]}{;\'
> 2. Yes
> 3. As long as the encrypted string is in a character set supported by the
> server (and you could use Unicode if necessary), then there shouldn't be
any
> specific issues - it's up to your application to do the
> encrpytion/decryption.
> Simon

Oops - it's not at all correct to say that SQL ignores whitespace; I was
thinking of trailing spaces there for some reason. What may have happened is
that Query Analyzer interpreted your newline as ASCII 13 (carriage return):

select ascii('
')

This gives 13, but you need 10, according to what you posted, so the select
query I suggested above should hopefully be what you need.

Simon|||Simon,

Thanks for your reply.

I show the exact data in ascii code. I am sure the source
of my problem is not about mistyping. I first found the
problem in my VB application. The following is a part of it.

Sub dotest()
Dim i, X, j, s
s = "select * from test2"
Set rs = cn.OpenRecordset(s, dbOpenDynaset)
For i = 1 To rs.RecordCount
Debug.Print rs!a
X = ""
For j = 1 To Len(rs!a)
X = X & Asc(Mid(rs!a, j, 1)) & ";"
Next
Debug.Print X

s = "select * from test2 where a = '" & rs!a & "'"
Set rs1 = cn.OpenRecordset(s, dbOpenDynaset)
If rs1.RecordCount = 0 Then
Debug.Print "==not found=="
End If
rs.movenext
Next
End Sub

Anita Hery

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Anita,

I ran the following SQL Script and VBScript and it returned both rows. Does
this work in your environment? In any case, you might consider storing the
encrypted value as binary rather than character data.

CREATE TABLE test2
( a char(15)
primary KEY CLUSTERED )

DECLARE @.Value1 char(15)
SET @.Value1 =
CAST(0x253B3E2E5B0A2C295D2F5C2D2C3D2F AS char(15))
DECLARE @.Value2 char(15)
SET @.Value2 =
CAST(0x5C303C5B5C0A7C5E5B5C5D7D7B3B5C AS char(15))

INSERT INTO test2 VALUES(@.Value1)
INSERT INTO test2 VALUES(@.Value2)

SELECT
CAST(a AS binary(15)),
a FROM test2
WHERE a IN(@.Value1, @.Value2)

' test VBScript
Set cn = CreateObject("ADODB.Connection")
cn.Open "Provider=SQLOLEDB.1;" _ &
"Data Source=MyServer;" _ &
"Initial Catalog=MyDatabase;" _ &
"Integrated Security=SSPI"

dotest()
WScript.Echo "Done"

Sub dotest()
Dim i, X, j, s
s = "select * from test2"
Set rs = cn.Execute(s)
Do While rs.EOF = false
WScript.Echo rs.Fields("a")
X = ""
For j = 1 To Len(rs.Fields("a"))
X = X & Asc(Mid(rs.Fields("a"), j, 1)) & ";"
Next
WScript.Echo X

s = "select * from test2 where a = '" & rs.Fields("a") & "'"
Set rs1 = cn.Execute(s)
If rs1.RecordCount = 0 Then
WScript.Echo "==not found=="
End If
rs.movenext
Loop
End Sub

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Anita" <anonymous@.devdex.com> wrote in message
news:402aaa48$0$199$75868355@.news.frii.net...
> Simon,
> Thanks for your reply.
> I show the exact data in ascii code. I am sure the source
> of my problem is not about mistyping. I first found the
> problem in my VB application. The following is a part of it.
> Sub dotest()
> Dim i, X, j, s
> s = "select * from test2"
> Set rs = cn.OpenRecordset(s, dbOpenDynaset)
> For i = 1 To rs.RecordCount
> Debug.Print rs!a
> X = ""
> For j = 1 To Len(rs!a)
> X = X & Asc(Mid(rs!a, j, 1)) & ";"
> Next
> Debug.Print X
> s = "select * from test2 where a = '" & rs!a & "'"
> Set rs1 = cn.OpenRecordset(s, dbOpenDynaset)
> If rs1.RecordCount = 0 Then
> Debug.Print "==not found=="
> End If
> rs.movenext
> Next
> End Sub
> Anita Hery
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!|||Dan Guzman,

Thanks for your reply.

Currently I am working with DAO library / ODBC.
I am not familiar with VB script. So, I tried
using ADO library 2.7 and made a small modification
like this:

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim qd As New ADODB.Command
Dim s As String

Private Sub Form_Load()
cn.ConnectionString = "PROVIDER=SQLOLEDB;
SERVER=srv2003;UID=sqlreg;PWD=cas;DATABASE=cas1"
cn.Open
dotest
End Sub

Sub dotest()
Dim i, X, j, s
s = "select * from test2"
Set rs = cn.Execute(s)
i = 0
Do While rs.EOF = False
i = i + 1
Debug.Print "Row " & i & " ----"
Debug.Print rs.Fields("a")
X = ""
For j = 1 To Len(rs.Fields("a"))
X = X & Asc(Mid(rs.Fields("a"), j, 1)) & ";"
Next
Debug.Print X

s = "select * from test2 where a = '" &
rs.Fields("a") & "'"
Set rs1 = cn.Execute(s)
If rs1.EOF Then ' .RecordCount = 0 Then
Debug.Print "==not found=="
End If
rs.MoveNext
Loop
End Sub

If I use my original data, the above code will
produce the same problem on the same row (row 2).
Below is the output list:

====================
Row 1 ----
%;>.[
,)]/\-,=/
37;59;62;46;91;10;44;41;93;47;92;45;44;61;47;
Row 2 ----
\0<[\
|^[\]}{;\
92;48;60;91;92;10;124;94;91;92;93;125;123;59;92;
==not found==
Row 3 ----
\0<[\ ,)? {=\?
92;48;60;91;92;11;44;41;63;3;123;127;61;92;63;
Row 4 ----
\0<[\ \%:_`- ]_
92;48;60;91;92;11;92;37;58;95;96;45;5;93;95;
Row 5 ----
\0<[\ \^:& }{;\
92;48;60;91;92;11;92;94;58;38;7;125;123;59;92;
Row 6 ----
\0[*_1>\_\ }{@.+
92;48;91;42;95;49;62;92;95;92;7;125;123;64;43;
Row 7 ----
].>^/. ]-=}<)^&
93;46;62;94;47;46;8;93;45;61;125;60;41;94;38;
Row 8 ----
]0_^{
}^~_|~{^_
93;48;95;94;123;10;125;94;126;95;124;126;123;94;95 ;
Row 9 ----
{
}{31{2|02~||
123;10;125;123;127;51;49;123;50;124;48;50;126;124; 124;
=======================

The row 2 can be accessed by the query like this:

select * from test2
where a =
char(92)+char(48)+char(60)+char(91)+char(92)+char( 10)+
char(124)+char(94)+char(91)+char(92)+char(93)+char (125)+
char(123)+char(59)+char(92)

But, I will not use this way.

Anita

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Hi, Anita.

I was able to recreate your problem and narrowed it down to the
backslash/newline sequence in the character string. As a workaround, I used
a parameter rather than an embedded literal value. This technique also
eliminates the need to escape other special characters like single quotes.
Example below.

Dim cmd As New ADODB.Command
Dim parm1 As ADODB.parameter
cmd.CommandText = "SELECT * FROM test2 WHERE a = ?"
cmd.ActiveConnection = cn
Set parm1 = cmd.CreateParameter("@.Parm1", adChar, 1, 15, s)
cmd.Parameters.Append parm1
cmd.Execute

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Anita" <anonymous@.devdex.com> wrote in message
news:402b81e0$0$202$75868355@.news.frii.net...
> Dan Guzman,
> Thanks for your reply.
> Currently I am working with DAO library / ODBC.
> I am not familiar with VB script. So, I tried
> using ADO library 2.7 and made a small modification
> like this:
> Dim cn As New ADODB.Connection
> Dim rs As New ADODB.Recordset
> Dim qd As New ADODB.Command
> Dim s As String
> Private Sub Form_Load()
> cn.ConnectionString = "PROVIDER=SQLOLEDB;
> SERVER=srv2003;UID=sqlreg;PWD=cas;DATABASE=cas1"
> cn.Open
> dotest
> End Sub
> Sub dotest()
> Dim i, X, j, s
> s = "select * from test2"
> Set rs = cn.Execute(s)
> i = 0
> Do While rs.EOF = False
> i = i + 1
> Debug.Print "Row " & i & " ----"
> Debug.Print rs.Fields("a")
> X = ""
> For j = 1 To Len(rs.Fields("a"))
> X = X & Asc(Mid(rs.Fields("a"), j, 1)) & ";"
> Next
> Debug.Print X
> s = "select * from test2 where a = '" &
> rs.Fields("a") & "'"
> Set rs1 = cn.Execute(s)
> If rs1.EOF Then ' .RecordCount = 0 Then
> Debug.Print "==not found=="
> End If
> rs.MoveNext
> Loop
> End Sub
> If I use my original data, the above code will
> produce the same problem on the same row (row 2).
> Below is the output list:
> ====================
> Row 1 ----
> %;>.[
> ,)]/\-,=/
> 37;59;62;46;91;10;44;41;93;47;92;45;44;61;47;
> Row 2 ----
> \0<[\
> |^[\]}{;\
> 92;48;60;91;92;10;124;94;91;92;93;125;123;59;92;
> ==not found==
> Row 3 ----
> \0<[\ ,)? {=\?
> 92;48;60;91;92;11;44;41;63;3;123;127;61;92;63;
> Row 4 ----
> \0<[\ \%:_`- ]_
> 92;48;60;91;92;11;92;37;58;95;96;45;5;93;95;
> Row 5 ----
> \0<[\ \^:& }{;\
> 92;48;60;91;92;11;92;94;58;38;7;125;123;59;92;
> Row 6 ----
> \0[*_1>\_\ }{@.+
> 92;48;91;42;95;49;62;92;95;92;7;125;123;64;43;
> Row 7 ----
> ].>^/. ]-=}<)^&
> 93;46;62;94;47;46;8;93;45;61;125;60;41;94;38;
> Row 8 ----
> ]0_^{
> }^~_|~{^_
> 93;48;95;94;123;10;125;94;126;95;124;126;123;94;95 ;
> Row 9 ----
> {
> }{31{2|02~||
> 123;10;125;123;127;51;49;123;50;124;48;50;126;124; 124;
> =======================
> The row 2 can be accessed by the query like this:
> select * from test2
> where a =
> char(92)+char(48)+char(60)+char(91)+char(92)+char( 10)+
> char(124)+char(94)+char(91)+char(92)+char(93)+char (125)+
> char(123)+char(59)+char(92)
> But, I will not use this way.
> Anita
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!|||Dan,

You are absolutely right.

I think my problem is about query translation
on client side (VB). Perhaps, it is a bug
from Microsoft.

If the query is sent using direct method, like :
s = "select * from test2 where a = '" & _
rs.Fields("a") & "'"
Set rs1 = cn.Execute(s)
then it is not guaranteed to work.

I made QA trial to prove that my problem was not
caused by SQL2000 :

CREATE TABLE test3
( a char(15)
primary KEY CLUSTERED,
b char(1) )

INSERT INTO test3
SELECT a, '2' as b FROM test2

--set b='1' on the second row
UPDATE test3 SET b = '1'
WHERE a =
char(92)+char(48)+char(60)+char(91)+char(92)+char( 10)+
char(124)+char(94)+char(91)+char(92)+char(93)+char (125)+
char(123)+char(59)+char(92)

DECLARE @.Value1 char(15)

SELECT @.value1 = a
FROM test3 where b = '1'

--successful second row access
SELECT * FROM test3 where a = @.Value1

Thanks for your reply

Anita Hery

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||SQL Server MVP Steve Kass pointed out to me that this behavior is described
in MSKB 164291
<http://support.microsoft.com/default.aspx?scid=kb;en-us;164291>.
Basically, SQL Server interprets a backslash followed a newline as a literal
continuation escape sequence so these characters are ignored in the literal
string. You can repro this in Query Analyzer with the following script:

SELECT 'Continu\
ed string'
GO

Although this behavior is also described in the Books Online in the Embedded
SQL for C section <esqlforc.chm::/ec_6_epr_02_101f.htm>, it is not mentioned
elsewhere as it probably should. A doc bug has been filed so the
documentation issue should be addressed in the future.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Anita" <anonymous@.devdex.com> wrote in message
news:403007ad$0$195$75868355@.news.frii.net...
> Dan,
> You are absolutely right.
> I think my problem is about query translation
> on client side (VB). Perhaps, it is a bug
> from Microsoft.
> If the query is sent using direct method, like :
> s = "select * from test2 where a = '" & _
> rs.Fields("a") & "'"
> Set rs1 = cn.Execute(s)
> then it is not guaranteed to work.
> I made QA trial to prove that my problem was not
> caused by SQL2000 :
> CREATE TABLE test3
> ( a char(15)
> primary KEY CLUSTERED,
> b char(1) )
> INSERT INTO test3
> SELECT a, '2' as b FROM test2
> --set b='1' on the second row
> UPDATE test3 SET b = '1'
> WHERE a =
> char(92)+char(48)+char(60)+char(91)+char(92)+char( 10)+
> char(124)+char(94)+char(91)+char(92)+char(93)+char (125)+
> char(123)+char(59)+char(92)
> DECLARE @.Value1 char(15)
> SELECT @.value1 = a
> FROM test3 where b = '1'
> --successful second row access
> SELECT * FROM test3 where a = @.Value1
> Thanks for your reply
> Anita Hery
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

Non-deterministic Clustered Index for Indexed View

Hi all,
I am trying to create an indexed view to do aggregation on a table of
Payments. The table has columns as follows:
CREATE TABLE [Payments] (
[PmtKey] [int] IDENTITY (1, 1) NOT NULL ,
[PmtAmt] [smallmoney] NOT NULL ,
[PmtDate] [smalldatetime] NOT NULL ,
[Voided] [bit] NOT NULL CONSTRAINT [DF_Payments_Voided] DEFAULT (0),
CONSTRAINT [PK_Payments] PRIMARY KEY CLUSTERED
(
[PmtKey]
) ON [PRIMARY]
) ON [PRIMARY]
I am trying to create an indexed view as follows:
CREATE VIEW dbo.vwPayments_ByDate
WITH SCHEMABINDING
AS
SELECT
CAST(DATEDIFF(DAY,0,PmtDate) AS SMALLDATETIME) AS PmtDate,
SUM(PmtAmt) AS DaysPmts,
COUNT_BIG (*) AS Expr1
FROM dbo.Payments
WHERE (Voided = 0)
GROUP BY CAST(DATEDIFF(DAY,0,PmtDate) AS SMALLDATETIME)
I am doing the cast to try to get all payments grouped by the same date,
ignoring the time portion.
When I go to add a unique clustered index to this view,
CREATE UNIQUE CLUSTERED
INDEX [PK_vwPmts_ByDate] ON [dbo].[vwPayments_ByDate] ([PmtDate])
WITH
FILLFACTOR = 85
I am told that the column PmtDate is non-detereministic or too imprecise.
I understand the requirement that the indices in an indexed view cannot be
nullable and must be deterministic. Is there another way to cast PmtDate
that will allow me to do the grouping and aggregation I am trying to achieve
?
Thanks.
--
John> Is there another way to cast PmtDate
> that will allow me to do the grouping and aggregation I am trying to
> achieve?
CAST is non-deterministic when used with smalldatetime. Try CONVERT with a
style parameter instead:
CONVERT(smalldatetime, DATEDIFF(DAY, 0, PmtDate), 112)
Hope this helps.
Dan Guzman
SQL Server MVP
"JT" <Jthayer@.online.nospam> wrote in message
news:2DA1D643-75AC-4B61-AE2C-E08C3085F5A7@.microsoft.com...
> Hi all,
> I am trying to create an indexed view to do aggregation on a table of
> Payments. The table has columns as follows:
> CREATE TABLE [Payments] (
> [PmtKey] [int] IDENTITY (1, 1) NOT NULL ,
> [PmtAmt] [smallmoney] NOT NULL ,
> [PmtDate] [smalldatetime] NOT NULL ,
> [Voided] [bit] NOT NULL CONSTRAINT [DF_Payments_Voided] DEFAULT (0),
> CONSTRAINT [PK_Payments] PRIMARY KEY CLUSTERED
> (
> [PmtKey]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> I am trying to create an indexed view as follows:
> CREATE VIEW dbo.vwPayments_ByDate
> WITH SCHEMABINDING
> AS
> SELECT
> CAST(DATEDIFF(DAY,0,PmtDate) AS SMALLDATETIME) AS PmtDate,
> SUM(PmtAmt) AS DaysPmts,
> COUNT_BIG (*) AS Expr1
> FROM dbo.Payments
> WHERE (Voided = 0)
> GROUP BY CAST(DATEDIFF(DAY,0,PmtDate) AS SMALLDATETIME)
> I am doing the cast to try to get all payments grouped by the same date,
> ignoring the time portion.
> When I go to add a unique clustered index to this view,
> CREATE UNIQUE CLUSTERED
> INDEX [PK_vwPmts_ByDate] ON [dbo].[vwPayments_ByDate] ([PmtDate])
> WITH
> FILLFACTOR = 85
> I am told that the column PmtDate is non-detereministic or too imprecise.
> I understand the requirement that the indices in an indexed view cannot be
> nullable and must be deterministic. Is there another way to cast PmtDate
> that will allow me to do the grouping and aggregation I am trying to
> achieve?
> Thanks.
> --
> John|||JT (Jthayer@.online.nospam) writes:
> I am trying to create an indexed view as follows:
> CREATE VIEW dbo.vwPayments_ByDate
> WITH SCHEMABINDING
> AS
> SELECT
> CAST(DATEDIFF(DAY,0,PmtDate) AS SMALLDATETIME) AS PmtDate,
> SUM(PmtAmt) AS DaysPmts,
> COUNT_BIG (*) AS Expr1
> FROM dbo.Payments
> WHERE (Voided = 0)
> GROUP BY CAST(DATEDIFF(DAY,0,PmtDate) AS SMALLDATETIME)
Dan posted a solution, but it works only on SQL 2005. (I've tested).
On SQL 2000 you may have to let it suffice with:
CREATE VIEW dbo.vwPayments_ByDate
WITH SCHEMABINDING
AS
SELECT CONVERT(char(8), PmtDate, 112) AS PmtDate,
SUM(PmtAmt) AS DaysPmts,
COUNT_BIG (*) AS Expr1
FROM dbo.Payments
WHERE (Voided = 0)
GROUP BY CONVERT(char(8), PmtDate, 112)
Thus, you get PmtDate as a char(8) column instead.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks Dan,
From BOL:
CONVERT: Deterministic unless used with datetime, smalldatetime, or
sql_variant. The datetime and smalldatetime data types are deterministic if
the style parameter is also specified.
In theory, it would sure seem that your solution should work. In practice,
I get the same error. Any other thoughts?
--
John
"Dan Guzman" wrote:

> CAST is non-deterministic when used with smalldatetime. Try CONVERT with
a
> style parameter instead:
> CONVERT(smalldatetime, DATEDIFF(DAY, 0, PmtDate), 112)
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "JT" <Jthayer@.online.nospam> wrote in message
> news:2DA1D643-75AC-4B61-AE2C-E08C3085F5A7@.microsoft.com...
>
>|||It looks like you are using SQL 2000.
Erland posted one method that will work in SQL 2000. Here's an extention of
that technique that will return a smalldatetime:
CONVERT(smalldatetime, CONVERT(char(8), PmtDate, 112), 112) AS PmtDate
Hope this helps.
Dan Guzman
SQL Server MVP
"JT" <Jthayer@.online.nospam> wrote in message
news:11FDCEC3-F22E-4572-B211-AF822FCCC5AC@.microsoft.com...
> Thanks Dan,
> From BOL:
> CONVERT: Deterministic unless used with datetime, smalldatetime, or
> sql_variant. The datetime and smalldatetime data types are deterministic
> if
> the style parameter is also specified.
> In theory, it would sure seem that your solution should work. In
> practice,
> I get the same error. Any other thoughts?
> --
> John
>
> "Dan Guzman" wrote:
>|||Dan and Erland,
Thank you both for your posts and wisdom. I independently got it working by
getting rid of the 0 and explicitly setting 1/1/1900 as the index date for
SQL Server's Datediff function. For example:
CREATE VIEW dbo.vwPayments_ByDate
WITH SCHEMABINDING
AS
SELECT
CONVERT(smalldatetime, DATEDIFF([DAY], CONVERT(DATETIME, '1900-01-01
00:00:00', 102), PmtDate), 101) AS PmtDate,
SUM(PmtAmt) AS DaysPmts,
COUNT_BIG (*) AS Expr1
FROM dbo.Payments
WHERE (Voided = 0)
GROUP BY CONVERT(smalldatetime, DATEDIFF([DAY], CONVERT(DATETIME,
'1900-01-01 00:00:00', 102), PmtDate), 101)
Pretty darn ugly, if I may say so. I like your method better.
Thanks again.
John
"Dan Guzman" wrote:

> It looks like you are using SQL 2000.
> Erland posted one method that will work in SQL 2000. Here's an extention
of
> that technique that will return a smalldatetime:
> CONVERT(smalldatetime, CONVERT(char(8), PmtDate, 112), 112) AS PmtDate
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "JT" <Jthayer@.online.nospam> wrote in message
> news:11FDCEC3-F22E-4572-B211-AF822FCCC5AC@.microsoft.com...
>
>|||On second thought, I think I will stick with my convoluted method as it
preserves the column as smalldatetime datatype, which will be helpful in
sorting records for reporting. Sorting dates cast as Char(8) gives you all
of the January's, then the February's when converted to style 101, which is
what I use in my reports.
Thanks again.
--
John
"Erland Sommarskog" wrote:

> JT (Jthayer@.online.nospam) writes:
> Dan posted a solution, but it works only on SQL 2005. (I've tested).
> On SQL 2000 you may have to let it suffice with:
> CREATE VIEW dbo.vwPayments_ByDate
> WITH SCHEMABINDING
> AS
> SELECT CONVERT(char(8), PmtDate, 112) AS PmtDate,
> SUM(PmtAmt) AS DaysPmts,
> COUNT_BIG (*) AS Expr1
> FROM dbo.Payments
> WHERE (Voided = 0)
> GROUP BY CONVERT(char(8), PmtDate, 112)
> Thus, you get PmtDate as a char(8) column instead.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||Let's start with everything is wrong. You have an IDENTITY column, no
key, and use assembly language style BIT flags and proprietary MONEY
data types in spite of the math errors in it. The payments are not
posted to an account or an invoice? Wild guess at a valid design
CREATE TABLE Payments
(invoice_nbr INTEGER NOT NULL,
payment_nbr INTEGER NOT NULL,
PRIMARY KEY (invoice_nbr, payment_nbr),
pmt_amt DECIMAL (12,4) NOT NULL,
pmt_date DATETIME NOT NULL
CHECK (pmt_date = CAST (FLOOR(CAST(pmt_date AS FLOAT)) AS
DATETIME)), -- other ways to do this, too
pmt_status INTEGER NOT NULL);
What if you had a relational approach and not allow bad data that has
to be clean out later? Good DDL will save you from complex kludges.|||Thanks for the enlightenment! You forgot the Volkswagen lecture, as the vie
w
is named vw... The actual table is considerably different than the
simplified example I posted. Seriously, though, thanks for your input,
especially about the datetime column. Something to consider...
--
John
"--CELKO--" wrote:

> Let's start with everything is wrong. You have an IDENTITY column, no
> key, and use assembly language style BIT flags and proprietary MONEY
> data types in spite of the math errors in it. The payments are not
> posted to an account or an invoice? Wild guess at a valid design
> CREATE TABLE Payments
> (invoice_nbr INTEGER NOT NULL,
> payment_nbr INTEGER NOT NULL,
> PRIMARY KEY (invoice_nbr, payment_nbr),
> pmt_amt DECIMAL (12,4) NOT NULL,
> pmt_date DATETIME NOT NULL
> CHECK (pmt_date = CAST (FLOOR(CAST(pmt_date AS FLOAT)) AS
> DATETIME)), -- other ways to do this, too
> pmt_status INTEGER NOT NULL);
>
> What if you had a relational approach and not allow bad data that has
> to be clean out later? Good DDL will save you from complex kludges.
>|||I pikced up a slogan from Graeme Simsion, who is a data quality and
design guru -- "Mop the floor, but then fix the leak!" . I am getting
a presentation on advanced DDL ready for PASS this year. DML gets all
the glory, but good DDL does the real work.
And one day, I will figure out DCL trick.
s

NONCLUSTERED?

CREATE NONCLUSTERED INDEX
Hi, What's diff between NONCLUSTERED and CLUSTERED? thanksClustered indexes physically order the data in the tables (think of a Yellow
pages, which is basically ordered by lastname, firstname)
Non-clustered is additional data with pointers to the actual data (think of
all your technical books, with those index pages at the back)
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"js" <js@.someone@.hotmail.com> wrote in message
news:O%23KQoGUYFHA.3040@.TK2MSFTNGP14.phx.gbl...
> CREATE NONCLUSTERED INDEX
> Hi, What's diff between NONCLUSTERED and CLUSTERED? thanks
>|||See:
http://msdn.microsoft.com/library/d...asp?frame=true
And the subtopics...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"js" <js@.someone@.hotmail.com> wrote in message news:O%23KQoGUYFHA.3040@.TK2MSFTNGP14.phx.gbl
..
> CREATE NONCLUSTERED INDEX
> Hi, What's diff between NONCLUSTERED and CLUSTERED? thanks
>

Nonclustered Indexes

Is this true or false based on reading this:
http://msdn.microsoft.com/library/en...asp?frame=true
If a table has a nonclustered index AND a clustered index the
NONCLUSTERED index will use the clustered index key as a row locater?
Meaning SQL has to
1. Decide based on the execution plan whether or not it should use the
non clustered index
2. Scan the nonclustered index to get to the leaf node which contains
the clustered index key
3. Scan the clustered index by that key to get to its leaf node ( which
will be the data page )
4. Find the row within that page
Am I correct?
The clustered index IS the table order. Therefore it becomes the lookup key
for any non-clustered index operation. Steps 2. and 3. should be Search,
not Scan operations on the nonclustered and clustered indexes. Searching
for an item in a modified B-tree structure is a very fast operation. FYI,
the operation in step 3 is called a bookmark lookup.
One of the optimizer's challenges is deciding when to skip the index use and
just scan the table. If the nonclustered index is poorly selective or the
filter criteria returns a very large result set or returns a large
proportion of the underlying table, SQL may decide that the "extra hop" to
do the bookmark lookups is slower than a single pass table scan.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"g3000" <carlton_gregory@.yahoo.com> wrote in message
news:1141933932.804305.235590@.u72g2000cwu.googlegr oups.com...
> Is this true or false based on reading this:
> http://msdn.microsoft.com/library/en...asp?frame=true
> If a table has a nonclustered index AND a clustered index the
> NONCLUSTERED index will use the clustered index key as a row locater?
> Meaning SQL has to
> 1. Decide based on the execution plan whether or not it should use the
> non clustered index
> 2. Scan the nonclustered index to get to the leaf node which contains
> the clustered index key
> 3. Scan the clustered index by that key to get to its leaf node ( which
> will be the data page )
> 4. Find the row within that page
> Am I correct?
>
|||Thanks Geoff for your reply.
Now a nonclustered index on a heap table uses the combination of
File ID, page number, and number of row on the page as the ROW ID in
its leaf node.
The docs state that the row locator when a clustered is available "is
the clustered index key for the row"
Does the above mean that the clustered index key takes you DIRECTLY to
the row or it takes you directly to the DATA PAGE that contains the
row?
Because from my understanding the leaf node of a clustered index IS the
data page.
|||For resolving data lookups, Row and Data Page containing the row are
identical concepts. SQL loads and saves data in pages. Going from a page
to a row within a page is a trivial exercise and the two are sometimes used
interchangably when talking about lookup operations. And you are correct,
the leaf level IS the data row for a clustered index.
Note that a clustered index doesn't have to be unique. Earlier versions of
SQL (6.5 and before) worked better when the clustered index was not unique.
That changed with SQL 7.0 and true row-level locking. Now, the default for
SQL is to create a clustered index out of the Primary Key. This is not a
requirement and can be overridden at design time. When the clustered index
is not unique, a uniquifier is added to each row so the index lookup
functions work correctly.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"g3000" <carlton_gregory@.yahoo.com> wrote in message
news:1141938175.459830.100580@.i39g2000cwa.googlegr oups.com...
> Thanks Geoff for your reply.
> Now a nonclustered index on a heap table uses the combination of
> File ID, page number, and number of row on the page as the ROW ID in
> its leaf node.
> The docs state that the row locator when a clustered is available "is
> the clustered index key for the row"
> Does the above mean that the clustered index key takes you DIRECTLY to
> the row or it takes you directly to the DATA PAGE that contains the
> row?
> Because from my understanding the leaf node of a clustered index IS the
> data page.
>
|||One issue not touched upon. Clustered indexes in general, are not a
great idea for most tables.
Non-clustered indexes have relatively low overhead, super fast
addition, update, and delete capabilities, and are extremely fast to
traverse.
Clustered indexes take HUGE hits when you modifiy a column in the
index, potentially when you insert a lot into the middle of the table,
but are equivalent for deletes.
An interesting side note. The non-clustered index finds itself very
quickly into cache for even the largest of tables. Not true of a
clustered index.
And pay attention to the size of the columns in the clustered index.
Pick large columns, and your performance will be seriously downgraded.
In general, if you will ALWAYS be pulling multiple rows of sequential
data, with sequential ALWAYS being defined exactly teh same, then
clustered can make sense. As an example, time stamped data, whree you
need 1000 rows at a time.
Pulling a single row, or reporting data based upon several variables,
and the non-clustered index will typically outperform the clustered
index in all manners.
regards,
doug
|||Perhaps an example.
Public library, the fiction section. The clustered index is the
placement on the shelves due to the author's last name. Really nice if
ALL you EVER did was check out one author's books. You can walk right
to the correct shelf, and grab your books.
OTOH, the card catalogue is the non-clustered index. You know the
subject, or title, you don't start scanning book shelves.
it is MUCH quicker to go to teh card catalogue, get the author's name
and book name, and go to the shelf to get teh right book.
IMO, an EFFICIENT library would just throw the books on any old shelf,
noting where you stuffed it, and updating the card catalogue. Then, on
lookups, book losses, or additions, you just go to teh catalogue, find
the shelf/position, adn get your book.
regards,
doug
|||So basically the clustered index is best when the selectivity is HIGH?
and the queries against the tables are generally the same ( like a
reference table )
non-clustered indexes are good when you are doing something like a
search on a catalog of parts ( or a library )
So seems to me more often then not non clustered will be the best
choice.
Why is the overhead so high for clustered?
|||A poorly selected Clustered index can cause performance degradation. A
well-chosen clustered index will improve performance.
Your example illustrates a poorly chosen Clustered order. Here is a good
one:
Books are assigned a sequential number as they are purchased. They are
stocked on the shelves in that numbered order. Card catalog is used to find
books according to pre-determined criteria. Note that the physical ordering
of the books has nothing to do with most catalog requests. Only when
checking books by purchase date would the indexes align, and then only by
coincidence. This improves insert performance since all the insertion work
happens in the same part of the library. In most libraries, the more recent
a book or periodical, the more frequently it is accessed. A smart librarian
would put those high traffic books near each other and in an easily accesed
place (think cache).
Just because SQL creates a clustered index on the Primary Key by default,
doesn't mean those two entities are irrevocably tied together. A clustered
index is a physical construct. A Primary Key is a logical database design
component. They can be implemented differently.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Doug" <drmiller100@.hotmail.com> wrote in message
news:1142203270.733531.260280@.e56g2000cwe.googlegr oups.com...
> Perhaps an example.
> Public library, the fiction section. The clustered index is the
> placement on the shelves due to the author's last name. Really nice if
> ALL you EVER did was check out one author's books. You can walk right
> to the correct shelf, and grab your books.
> OTOH, the card catalogue is the non-clustered index. You know the
> subject, or title, you don't start scanning book shelves.
> it is MUCH quicker to go to teh card catalogue, get the author's name
> and book name, and go to the shelf to get teh right book.
> IMO, an EFFICIENT library would just throw the books on any old shelf,
> noting where you stuffed it, and updating the card catalogue. Then, on
> lookups, book losses, or additions, you just go to teh catalogue, find
> the shelf/position, adn get your book.
> regards,
> doug
>
|||so in your example, you'd have a clustered index on the identity key,
and this woudl be more efficient then have a non-clustered index?
I disagree. I would suggest having no clustered index would be faster.
the key would be "shorter." Inserts faster.
I am curious as to the logic that a clustered index would make this
scenario faster.
|||Yes Im curious also.
I looking for an aswer as to why the overhead is so high just because
its physical construct.
If I load physical in the same data page then most likely if its high
transaction on that table the same data pages should be found in the
cache.
Obviously the process for searching for an data page with free space
within an extent is slow for a clustered index?
Should clustered index be used when rows will be selected "together"
most of the time? Meaning a low cardinality column?
So in a employee database all sex columns with a "Male" value can have
a clustred index because those records will be selected together?

non-clusted index and space

Hi I understand clustered and non-clustered indexes
increase the amount of disk space used i have a table that
is 200mb and wondered if I added a non-clsted index how
much extra space that would use and is it added to the
table size or somewhere else.
thanks for any help
MikeyMikey:
As for how much space an index will use, a lot depends on the type and
number of columns in the index. If you
EXEC sp_spaceused 'tablename'
before and after you create the index you can get a pretty good idea
of how much space the index requires.
Non clustered indexes would not change the size of the table, while a
clustered index 'becomes' the table.
HTH,
Scott
http://www.OdeToCode.com
On Thu, 8 Jan 2004 05:24:01 -0800, "Mikey"
<anonymous@.discussions.microsoft.com> wrote:

quote:

---

>Hi I understand clustered and non-clustered indexes
>increase the amount of disk space used i have a table that
>is 200mb and wondered if I added a non-clsted index how
>much extra space that would use and is it added to the
>table size or somewhere else.
>thanks for any help
>Mikey

---

Non unique Clustered index

I have 1,000,000 records there are unique by member(int),vin(20),stock(18).
there are 1200 unique members all updates are done by the primary key
(member,vin,stock). My customer does not want to add an identity column.
There is only a non clustered unique PK on the table, no clustered index.
I am wondering which would be better
1. Put a unique Clustered PK constraint on the 40 byte fields
member(int),vin(20),stock(18) (indexes would be large)
or
2 Put a non Clustered index on member id (4 bytes)(let sql add the
identifier(4 bytes))and put the Primary Key on member(int),vin(20),stock(18)
as a unique non
clustered constraint.
This table has heavy updates(no Pkey fields updated ) and inserts at night
in batch (15,000 updates 5,000 inserts approx per night).
There is currently no clustered index and there is no way to control
fragmentation.
Thanks,
Jon A
If you have only one index on a table, it's usually best to be clustered.
The downside to a wide clustered index is that the clustered index keys are
stored in non-clustered indexes as well. This is not an issue when you
don't have non-clustered indexes, though.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jon A" <JonA@.discussions.microsoft.com> wrote in message
news:E1406DAF-36A7-46AB-9EFD-27F942A59B51@.microsoft.com...
>I have 1,000,000 records there are unique by member(int),vin(20),stock(18).
> there are 1200 unique members all updates are done by the primary key
> (member,vin,stock). My customer does not want to add an identity column.
> There is only a non clustered unique PK on the table, no clustered index.
> I am wondering which would be better
> 1. Put a unique Clustered PK constraint on the 40 byte fields
> member(int),vin(20),stock(18) (indexes would be large)
> or
> 2 Put a non Clustered index on member id (4 bytes)(let sql add the
> identifier(4 bytes))and put the Primary Key on
> member(int),vin(20),stock(18)
> as a unique non
> clustered constraint.
> This table has heavy updates(no Pkey fields updated ) and inserts at night
> in batch (15,000 updates 5,000 inserts approx per night).
> There is currently no clustered index and there is no way to control
> fragmentation.
> --
> Thanks,
> Jon A
|||Jon A wrote:
> I have 1,000,000 records there are unique by
> member(int),vin(20),stock(18). there are 1200 unique members all
> updates are done by the primary key (member,vin,stock). My customer
> does not want to add an identity column. There is only a non
> clustered unique PK on the table, no clustered index.
> I am wondering which would be better
> 1. Put a unique Clustered PK constraint on the 40 byte fields
> member(int),vin(20),stock(18) (indexes would be large)
> or
> 2 Put a non Clustered index on member id (4 bytes)(let sql add the
> identifier(4 bytes))and put the Primary Key on
> member(int),vin(20),stock(18) as a unique non
> clustered constraint.
> This table has heavy updates(no Pkey fields updated ) and inserts at
> night in batch (15,000 updates 5,000 inserts approx per night).
> There is currently no clustered index and there is no way to control
> fragmentation.
I would argue that because you have a natural key as your PK, you're
likely to see page splitting if you use a clustered index. Since this is
a nightly batch, it may not matter. But then again, having a clustered
index on this table may not matter either, depnding on how the SELECTS
and UPDATES look.
I do agree with Dan. That is, it's best for most, if not all, tables to
have a clustered index. But to add one without a careful investigation
of the table and how it's used is necessary. Just as you would consider
what columns would best make use of a clustered index during database
design, you should perform the same due diligence now.
Look at your queries and table access. See how the data is updated. Is
it more than one row at a time? Is it ever changing a column value that
could be in the clustered index? What do the inserts look like? Are they
adding rows with column values that will most likely cause spage
splitting and slower insert performance at night? Look at the SELECTS on
the table. Do you ever return more than one row at a time? If so, what
criteria determine the rows returned? Do you have ORDER BY statements in
your queries? Do they really need to be there?
If you can post more information about how the table is used, we may be
able to offer more advice.
David Gugick
Imceda Software
www.imceda.com
|||Hi Jon,
I am not really sure what problem you are trying to solve. Is there a
problem?
If the potential problem is fragmentation control, then you could simply
add and drop a clustered index (on any column) during a service window.
If you do that periodically, fragmentation should be under control.
The rest depends on the queries you are using. A 40-byte index in itself
doesn't cause problems. If Insert and Delete performance during the day
is not an issue, then you could safely make the Primary Key index
clustered. And even with the proper fillfactors, Insert performance
shouldn't be a problem.
Having a clustered index can help Select performance on ranges a lot
(for example, the range member = <somevalue>). For high selectivity
Selects, a clustered index does not add much value.
HTH,
Gert-Jan
Jon A wrote:
> I have 1,000,000 records there are unique by member(int),vin(20),stock(18).
> there are 1200 unique members all updates are done by the primary key
> (member,vin,stock). My customer does not want to add an identity column.
> There is only a non clustered unique PK on the table, no clustered index.
> I am wondering which would be better
> 1. Put a unique Clustered PK constraint on the 40 byte fields
> member(int),vin(20),stock(18) (indexes would be large)
> or
> 2 Put a non Clustered index on member id (4 bytes)(let sql add the
> identifier(4 bytes))and put the Primary Key on member(int),vin(20),stock(18)
> as a unique non
> clustered constraint.
> This table has heavy updates(no Pkey fields updated ) and inserts at night
> in batch (15,000 updates 5,000 inserts approx per night).
> There is currently no clustered index and there is no way to control
> fragmentation.
> --
> Thanks,
> Jon A
|||An IDENTITY is a lousy candidate for a Clustered Index, almost as horrible
as allowing a table without a Clustered Index at all (a heap).
Heaps are large and, as you've noticed, do not allow you to as easily
control your index rebuild (defragmentaiton) as easily. First of all, the
Clustered Index itself adds no space to the table; its only the use of the
key as a pointer in the other indexes that can grow your non-clustered
indexes. However, consider how large the ROWID is as the alternative to the
clustered index key.
As far as uniqueness, if the Clustered Index is not unique, SQL Server will
make it so by appending a GUID to the key to force it to be unique. Weigh
that against the composite index length, not to mention the size of the heap
alternative.
As to the IDENTITY, if you use one, NEVER make it a clustered index, unless
there are absolutely no other candidates. When will you EVER query an
IDENTITY by range? Also, if you use an IDENTITY, this is normally used as a
surrogate, as in your case, which does not remove the uniqueness requirement
of the business key you would be replacing as the Primary Key. So, better
add an UNIQUE non-Clustered Constraint to the original candidate(s).
Sincerely,
Anthony Thomas

"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:422B03AC.344C5898@.toomuchspamalready.nl...
Hi Jon,
I am not really sure what problem you are trying to solve. Is there a
problem?
If the potential problem is fragmentation control, then you could simply
add and drop a clustered index (on any column) during a service window.
If you do that periodically, fragmentation should be under control.
The rest depends on the queries you are using. A 40-byte index in itself
doesn't cause problems. If Insert and Delete performance during the day
is not an issue, then you could safely make the Primary Key index
clustered. And even with the proper fillfactors, Insert performance
shouldn't be a problem.
Having a clustered index can help Select performance on ranges a lot
(for example, the range member = <somevalue>). For high selectivity
Selects, a clustered index does not add much value.
HTH,
Gert-Jan
Jon A wrote:
> I have 1,000,000 records there are unique by
member(int),vin(20),stock(18).
> there are 1200 unique members all updates are done by the primary key
> (member,vin,stock). My customer does not want to add an identity column.
> There is only a non clustered unique PK on the table, no clustered index.
> I am wondering which would be better
> 1. Put a unique Clustered PK constraint on the 40 byte fields
> member(int),vin(20),stock(18) (indexes would be large)
> or
> 2 Put a non Clustered index on member id (4 bytes)(let sql add the
> identifier(4 bytes))and put the Primary Key on
member(int),vin(20),stock(18)
> as a unique non
> clustered constraint.
> This table has heavy updates(no Pkey fields updated ) and inserts at night
> in batch (15,000 updates 5,000 inserts approx per night).
> There is currently no clustered index and there is no way to control
> fragmentation.
> --
> Thanks,
> Jon A
|||I added the clustered PK index as (member(int),vin(20),stock(18)). And with
adjustment the page splitting is minimal. But the Table is now 3x the size it
was previously.
My question is this in general terms. What is the problems / overhead of a
non unique clustered index? Is this a bad thing? I have never had a case
where I would do that. But as a result of this problem I am now curious.
"David Gugick" wrote:
|||I wouldn't expect changing the PK from non-clustered to clustered to
increase space requirements. In fact, I would think the space would
decrease. Are you certain there are no non-clustered indexes on the table?
You can double check with sp_helpindex.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jon A" <JonA@.discussions.microsoft.com> wrote in message
news:E551392D-6A3D-4176-B1EB-7847AB685B51@.microsoft.com...
>I added the clustered PK index as (member(int),vin(20),stock(18)). And with
> adjustment the page splitting is minimal. But the Table is now 3x the size
> it
> was previously.
> My question is this in general terms. What is the problems / overhead of a
> non unique clustered index? Is this a bad thing? I have never had a case
> where I would do that. But as a result of this problem I am now curious.
> "David Gugick" wrote:
>
|||My sources: http://www.sql-server-performance.co...ed_indexes.asp
say that the uniqueifier used in a non-unique clustered index is a 4 byte value, as opposed to a (16 byte) GUID. Is there any other support either way? I can't find any in BOL.
jg

Quote:

...Originally posted by Anthony Thomas
As far as uniqueness, if the Clustered Index is not unique, SQL Server will
make it so by appending a GUID to the key to force it to be unique...

Non unique Clustered index

I have 1,000,000 records there are unique by member(int),vin(20),stock(18).
there are 1200 unique members all updates are done by the primary key
(member,vin,stock). My customer does not want to add an identity column.
There is only a non clustered unique PK on the table, no clustered index.
I am wondering which would be better
1. Put a unique Clustered PK constraint on the 40 byte fields
member(int),vin(20),stock(18) (indexes would be large)
or
2 Put a non Clustered index on member id (4 bytes)(let sql add the
identifier(4 bytes))and put the Primary Key on member(int),vin(20),stock(18)
as a unique non
clustered constraint.
This table has heavy updates(no Pkey fields updated ) and inserts at night
in batch (15,000 updates 5,000 inserts approx per night).
There is currently no clustered index and there is no way to control
fragmentation.
--
Thanks,
Jon AIf you have only one index on a table, it's usually best to be clustered.
The downside to a wide clustered index is that the clustered index keys are
stored in non-clustered indexes as well. This is not an issue when you
don't have non-clustered indexes, though.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Jon A" <JonA@.discussions.microsoft.com> wrote in message
news:E1406DAF-36A7-46AB-9EFD-27F942A59B51@.microsoft.com...
>I have 1,000,000 records there are unique by member(int),vin(20),stock(18).
> there are 1200 unique members all updates are done by the primary key
> (member,vin,stock). My customer does not want to add an identity column.
> There is only a non clustered unique PK on the table, no clustered index.
> I am wondering which would be better
> 1. Put a unique Clustered PK constraint on the 40 byte fields
> member(int),vin(20),stock(18) (indexes would be large)
> or
> 2 Put a non Clustered index on member id (4 bytes)(let sql add the
> identifier(4 bytes))and put the Primary Key on
> member(int),vin(20),stock(18)
> as a unique non
> clustered constraint.
> This table has heavy updates(no Pkey fields updated ) and inserts at night
> in batch (15,000 updates 5,000 inserts approx per night).
> There is currently no clustered index and there is no way to control
> fragmentation.
> --
> Thanks,
> Jon A|||Jon A wrote:
> I have 1,000,000 records there are unique by
> member(int),vin(20),stock(18). there are 1200 unique members all
> updates are done by the primary key (member,vin,stock). My customer
> does not want to add an identity column. There is only a non
> clustered unique PK on the table, no clustered index.
> I am wondering which would be better
> 1. Put a unique Clustered PK constraint on the 40 byte fields
> member(int),vin(20),stock(18) (indexes would be large)
> or
> 2 Put a non Clustered index on member id (4 bytes)(let sql add the
> identifier(4 bytes))and put the Primary Key on
> member(int),vin(20),stock(18) as a unique non
> clustered constraint.
> This table has heavy updates(no Pkey fields updated ) and inserts at
> night in batch (15,000 updates 5,000 inserts approx per night).
> There is currently no clustered index and there is no way to control
> fragmentation.
I would argue that because you have a natural key as your PK, you're
likely to see page splitting if you use a clustered index. Since this is
a nightly batch, it may not matter. But then again, having a clustered
index on this table may not matter either, depnding on how the SELECTS
and UPDATES look.
I do agree with Dan. That is, it's best for most, if not all, tables to
have a clustered index. But to add one without a careful investigation
of the table and how it's used is necessary. Just as you would consider
what columns would best make use of a clustered index during database
design, you should perform the same due diligence now.
Look at your queries and table access. See how the data is updated. Is
it more than one row at a time? Is it ever changing a column value that
could be in the clustered index? What do the inserts look like? Are they
adding rows with column values that will most likely cause spage
splitting and slower insert performance at night? Look at the SELECTS on
the table. Do you ever return more than one row at a time? If so, what
criteria determine the rows returned? Do you have ORDER BY statements in
your queries? Do they really need to be there?
If you can post more information about how the table is used, we may be
able to offer more advice.
--
David Gugick
Imceda Software
www.imceda.com|||Hi Jon,
I am not really sure what problem you are trying to solve. Is there a
problem?
If the potential problem is fragmentation control, then you could simply
add and drop a clustered index (on any column) during a service window.
If you do that periodically, fragmentation should be under control.
The rest depends on the queries you are using. A 40-byte index in itself
doesn't cause problems. If Insert and Delete performance during the day
is not an issue, then you could safely make the Primary Key index
clustered. And even with the proper fillfactors, Insert performance
shouldn't be a problem.
Having a clustered index can help Select performance on ranges a lot
(for example, the range member = <somevalue>). For high selectivity
Selects, a clustered index does not add much value.
HTH,
Gert-Jan
Jon A wrote:
> I have 1,000,000 records there are unique by member(int),vin(20),stock(18).
> there are 1200 unique members all updates are done by the primary key
> (member,vin,stock). My customer does not want to add an identity column.
> There is only a non clustered unique PK on the table, no clustered index.
> I am wondering which would be better
> 1. Put a unique Clustered PK constraint on the 40 byte fields
> member(int),vin(20),stock(18) (indexes would be large)
> or
> 2 Put a non Clustered index on member id (4 bytes)(let sql add the
> identifier(4 bytes))and put the Primary Key on member(int),vin(20),stock(18)
> as a unique non
> clustered constraint.
> This table has heavy updates(no Pkey fields updated ) and inserts at night
> in batch (15,000 updates 5,000 inserts approx per night).
> There is currently no clustered index and there is no way to control
> fragmentation.
> --
> Thanks,
> Jon A|||An IDENTITY is a lousy candidate for a Clustered Index, almost as horrible
as allowing a table without a Clustered Index at all (a heap).
Heaps are large and, as you've noticed, do not allow you to as easily
control your index rebuild (defragmentaiton) as easily. First of all, the
Clustered Index itself adds no space to the table; its only the use of the
key as a pointer in the other indexes that can grow your non-clustered
indexes. However, consider how large the ROWID is as the alternative to the
clustered index key.
As far as uniqueness, if the Clustered Index is not unique, SQL Server will
make it so by appending a GUID to the key to force it to be unique. Weigh
that against the composite index length, not to mention the size of the heap
alternative.
As to the IDENTITY, if you use one, NEVER make it a clustered index, unless
there are absolutely no other candidates. When will you EVER query an
IDENTITY by range? Also, if you use an IDENTITY, this is normally used as a
surrogate, as in your case, which does not remove the uniqueness requirement
of the business key you would be replacing as the Primary Key. So, better
add an UNIQUE non-Clustered Constraint to the original candidate(s).
Sincerely,
Anthony Thomas
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:422B03AC.344C5898@.toomuchspamalready.nl...
Hi Jon,
I am not really sure what problem you are trying to solve. Is there a
problem?
If the potential problem is fragmentation control, then you could simply
add and drop a clustered index (on any column) during a service window.
If you do that periodically, fragmentation should be under control.
The rest depends on the queries you are using. A 40-byte index in itself
doesn't cause problems. If Insert and Delete performance during the day
is not an issue, then you could safely make the Primary Key index
clustered. And even with the proper fillfactors, Insert performance
shouldn't be a problem.
Having a clustered index can help Select performance on ranges a lot
(for example, the range member = <somevalue>). For high selectivity
Selects, a clustered index does not add much value.
HTH,
Gert-Jan
Jon A wrote:
> I have 1,000,000 records there are unique by
member(int),vin(20),stock(18).
> there are 1200 unique members all updates are done by the primary key
> (member,vin,stock). My customer does not want to add an identity column.
> There is only a non clustered unique PK on the table, no clustered index.
> I am wondering which would be better
> 1. Put a unique Clustered PK constraint on the 40 byte fields
> member(int),vin(20),stock(18) (indexes would be large)
> or
> 2 Put a non Clustered index on member id (4 bytes)(let sql add the
> identifier(4 bytes))and put the Primary Key on
member(int),vin(20),stock(18)
> as a unique non
> clustered constraint.
> This table has heavy updates(no Pkey fields updated ) and inserts at night
> in batch (15,000 updates 5,000 inserts approx per night).
> There is currently no clustered index and there is no way to control
> fragmentation.
> --
> Thanks,
> Jon A|||I added the clustered PK index as (member(int),vin(20),stock(18)). And with
adjustment the page splitting is minimal. But the Table is now 3x the size it
was previously.
My question is this in general terms. What is the problems / overhead of a
non unique clustered index? Is this a bad thing? I have never had a case
where I would do that. But as a result of this problem I am now curious.
"David Gugick" wrote:|||I wouldn't expect changing the PK from non-clustered to clustered to
increase space requirements. In fact, I would think the space would
decrease. Are you certain there are no non-clustered indexes on the table?
You can double check with sp_helpindex.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Jon A" <JonA@.discussions.microsoft.com> wrote in message
news:E551392D-6A3D-4176-B1EB-7847AB685B51@.microsoft.com...
>I added the clustered PK index as (member(int),vin(20),stock(18)). And with
> adjustment the page splitting is minimal. But the Table is now 3x the size
> it
> was previously.
> My question is this in general terms. What is the problems / overhead of a
> non unique clustered index? Is this a bad thing? I have never had a case
> where I would do that. But as a result of this problem I am now curious.
> "David Gugick" wrote:
>

Non unique Clustered index

I have 1,000,000 records there are unique by member(int),vin(20),stock(18).
there are 1200 unique members all updates are done by the primary key
(member,vin,stock). My customer does not want to add an identity column.
There is only a non clustered unique PK on the table, no clustered index.
I am wondering which would be better
1. Put a unique Clustered PK constraint on the 40 byte fields
member(int),vin(20),stock(18) (indexes would be large)
or
2 Put a non Clustered index on member id (4 bytes)(let sql add the
identifier(4 bytes))and put the Primary Key on member(int),vin(20),stock(18)
as a unique non
clustered constraint.
This table has heavy updates(no Pkey fields updated ) and inserts at night
in batch (15,000 updates 5,000 inserts approx per night).
There is currently no clustered index and there is no way to control
fragmentation.
--
Thanks,
Jon AIf you have only one index on a table, it's usually best to be clustered.
The downside to a wide clustered index is that the clustered index keys are
stored in non-clustered indexes as well. This is not an issue when you
don't have non-clustered indexes, though.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jon A" <JonA@.discussions.microsoft.com> wrote in message
news:E1406DAF-36A7-46AB-9EFD-27F942A59B51@.microsoft.com...
>I have 1,000,000 records there are unique by member(int),vin(20),stock(18).
> there are 1200 unique members all updates are done by the primary key
> (member,vin,stock). My customer does not want to add an identity column.
> There is only a non clustered unique PK on the table, no clustered index.
> I am wondering which would be better
> 1. Put a unique Clustered PK constraint on the 40 byte fields
> member(int),vin(20),stock(18) (indexes would be large)
> or
> 2 Put a non Clustered index on member id (4 bytes)(let sql add the
> identifier(4 bytes))and put the Primary Key on
> member(int),vin(20),stock(18)
> as a unique non
> clustered constraint.
> This table has heavy updates(no Pkey fields updated ) and inserts at night
> in batch (15,000 updates 5,000 inserts approx per night).
> There is currently no clustered index and there is no way to control
> fragmentation.
> --
> Thanks,
> Jon A|||Jon A wrote:
> I have 1,000,000 records there are unique by
> member(int),vin(20),stock(18). there are 1200 unique members all
> updates are done by the primary key (member,vin,stock). My customer
> does not want to add an identity column. There is only a non
> clustered unique PK on the table, no clustered index.
> I am wondering which would be better
> 1. Put a unique Clustered PK constraint on the 40 byte fields
> member(int),vin(20),stock(18) (indexes would be large)
> or
> 2 Put a non Clustered index on member id (4 bytes)(let sql add the
> identifier(4 bytes))and put the Primary Key on
> member(int),vin(20),stock(18) as a unique non
> clustered constraint.
> This table has heavy updates(no Pkey fields updated ) and inserts at
> night in batch (15,000 updates 5,000 inserts approx per night).
> There is currently no clustered index and there is no way to control
> fragmentation.
I would argue that because you have a natural key as your PK, you're
likely to see page splitting if you use a clustered index. Since this is
a nightly batch, it may not matter. But then again, having a clustered
index on this table may not matter either, depnding on how the SELECTS
and UPDATES look.
I do agree with Dan. That is, it's best for most, if not all, tables to
have a clustered index. But to add one without a careful investigation
of the table and how it's used is necessary. Just as you would consider
what columns would best make use of a clustered index during database
design, you should perform the same due diligence now.
Look at your queries and table access. See how the data is updated. Is
it more than one row at a time? Is it ever changing a column value that
could be in the clustered index? What do the inserts look like? Are they
adding rows with column values that will most likely cause spage
splitting and slower insert performance at night? Look at the SELECTS on
the table. Do you ever return more than one row at a time? If so, what
criteria determine the rows returned? Do you have ORDER BY statements in
your queries? Do they really need to be there?
If you can post more information about how the table is used, we may be
able to offer more advice.
David Gugick
Imceda Software
www.imceda.com|||Hi Jon,
I am not really sure what problem you are trying to solve. Is there a
problem?
If the potential problem is fragmentation control, then you could simply
add and drop a clustered index (on any column) during a service window.
If you do that periodically, fragmentation should be under control.
The rest depends on the queries you are using. A 40-byte index in itself
doesn't cause problems. If Insert and Delete performance during the day
is not an issue, then you could safely make the Primary Key index
clustered. And even with the proper fillfactors, Insert performance
shouldn't be a problem.
Having a clustered index can help Select performance on ranges a lot
(for example, the range member = <somevalue> ). For high selectivity
Selects, a clustered index does not add much value.
HTH,
Gert-Jan
Jon A wrote:
> I have 1,000,000 records there are unique by member(int),vin(20),stock(18)
.
> there are 1200 unique members all updates are done by the primary key
> (member,vin,stock). My customer does not want to add an identity column.
> There is only a non clustered unique PK on the table, no clustered index.
> I am wondering which would be better
> 1. Put a unique Clustered PK constraint on the 40 byte fields
> member(int),vin(20),stock(18) (indexes would be large)
> or
> 2 Put a non Clustered index on member id (4 bytes)(let sql add the
> identifier(4 bytes))and put the Primary Key on member(int),vin(20),stock(1
8)
> as a unique non
> clustered constraint.
> This table has heavy updates(no Pkey fields updated ) and inserts at night
> in batch (15,000 updates 5,000 inserts approx per night).
> There is currently no clustered index and there is no way to control
> fragmentation.
> --
> Thanks,
> Jon A|||An IDENTITY is a lousy candidate for a Clustered Index, almost as horrible
as allowing a table without a Clustered Index at all (a heap).
Heaps are large and, as you've noticed, do not allow you to as easily
control your index rebuild (defragmentaiton) as easily. First of all, the
Clustered Index itself adds no space to the table; its only the use of the
key as a pointer in the other indexes that can grow your non-clustered
indexes. However, consider how large the ROWID is as the alternative to the
clustered index key.
As far as uniqueness, if the Clustered Index is not unique, SQL Server will
make it so by appending a GUID to the key to force it to be unique. Weigh
that against the composite index length, not to mention the size of the heap
alternative.
As to the IDENTITY, if you use one, NEVER make it a clustered index, unless
there are absolutely no other candidates. When will you EVER query an
IDENTITY by range? Also, if you use an IDENTITY, this is normally used as a
surrogate, as in your case, which does not remove the uniqueness requirement
of the business key you would be replacing as the Primary Key. So, better
add an UNIQUE non-Clustered Constraint to the original candidate(s).
Sincerely,
Anthony Thomas
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:422B03AC.344C5898@.toomuchspamalready.nl...
Hi Jon,
I am not really sure what problem you are trying to solve. Is there a
problem?
If the potential problem is fragmentation control, then you could simply
add and drop a clustered index (on any column) during a service window.
If you do that periodically, fragmentation should be under control.
The rest depends on the queries you are using. A 40-byte index in itself
doesn't cause problems. If Insert and Delete performance during the day
is not an issue, then you could safely make the Primary Key index
clustered. And even with the proper fillfactors, Insert performance
shouldn't be a problem.
Having a clustered index can help Select performance on ranges a lot
(for example, the range member = <somevalue> ). For high selectivity
Selects, a clustered index does not add much value.
HTH,
Gert-Jan
Jon A wrote:
> I have 1,000,000 records there are unique by
member(int),vin(20),stock(18).
> there are 1200 unique members all updates are done by the primary key
> (member,vin,stock). My customer does not want to add an identity column.
> There is only a non clustered unique PK on the table, no clustered index.
> I am wondering which would be better
> 1. Put a unique Clustered PK constraint on the 40 byte fields
> member(int),vin(20),stock(18) (indexes would be large)
> or
> 2 Put a non Clustered index on member id (4 bytes)(let sql add the
> identifier(4 bytes))and put the Primary Key on
member(int),vin(20),stock(18)reen">
> as a unique non
> clustered constraint.
> This table has heavy updates(no Pkey fields updated ) and inserts at night
> in batch (15,000 updates 5,000 inserts approx per night).
> There is currently no clustered index and there is no way to control
> fragmentation.
> --
> Thanks,
> Jon A|||I added the clustered PK index as (member(int),vin(20),stock(18)). And with
adjustment the page splitting is minimal. But the Table is now 3x the size i
t
was previously.
My question is this in general terms. What is the problems / overhead of a
non unique clustered index? Is this a bad thing? I have never had a case
where I would do that. But as a result of this problem I am now curious.
"David Gugick" wrote:|||I wouldn't expect changing the PK from non-clustered to clustered to
increase space requirements. In fact, I would think the space would
decrease. Are you certain there are no non-clustered indexes on the table?
You can double check with sp_helpindex.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jon A" <JonA@.discussions.microsoft.com> wrote in message
news:E551392D-6A3D-4176-B1EB-7847AB685B51@.microsoft.com...
>I added the clustered PK index as (member(int),vin(20),stock(18)). And with
> adjustment the page splitting is minimal. But the Table is now 3x the size
> it
> was previously.
> My question is this in general terms. What is the problems / overhead of a
> non unique clustered index? Is this a bad thing? I have never had a case
> where I would do that. But as a result of this problem I am now curious.
> "David Gugick" wrote:
>

non clustered index on heap

What would be the implications in terms performance and i/o operations, if a non clustered index created on heap ie. without a clustered index on table.It depends. There is no straight-forward answer. It depends on the schema, row size etc. Generally speaking, it is recommended to have a clustered index on every table especially so for large tables. There are however cases where you can get the best bulk insert performance by inserting into a heap vs clustered index. Also the more indexes you have on a table slower the performance. Search in MSDN for the whitepaper of bulk load that should give some ideas on one aspect of this problem.

Non Clustered Index

Hi,
Is it advisable to create a Non Clustered Index in "ALLow NULL" column?
Thanks,
Rahul JhaDepends on the requirement.

To turn it around - should a nullable column prohibit an index? No :)|||for that matter it's also possible to create a clustered index on a nullable column.|||but problems will arise if you make your index unique on a nullable column.|||but problems will arise if you make your index unique on a nullable column.

Not really, you will only be allowed on null "value"

which makes no sense

http://weblogs.sqlteam.com/brettk/archive/2005/04/20/4592.aspx

non clustered index

Can anyone tell me why Enterprise manager-Generate SQL
scripts utility does not generate scripts for non
clustered index? Thanks.
Because you didn't check "Script indexes" on the Options tab of the Generate
SQL Scripts dialog?
http://www.aspfaq.com/
(Reverse address to reply.)
"Bill" <fei0405@.yahoo.com> wrote in message
news:0e3801c49cf5$0493c450$a501280a@.phx.gbl...
> Can anyone tell me why Enterprise manager-Generate SQL
> scripts utility does not generate scripts for non
> clustered index? Thanks.
|||Or you missed it in the Alter Table section?
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ekeFnbPnEHA.3996@.TK2MSFTNGP09.phx.gbl...
> Because you didn't check "Script indexes" on the Options tab of the
Generate
> SQL Scripts dialog?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Bill" <fei0405@.yahoo.com> wrote in message
> news:0e3801c49cf5$0493c450$a501280a@.phx.gbl...
>
|||Thank you. I did checked "Script indexes" on the Options
tab. I don't see Alter Table section. Can you navigate me
a little bit? Thank you so much.

>--Original Message--
>Or you missed it in the Alter Table section?
>"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote
in message[vbcol=seagreen]
>news:ekeFnbPnEHA.3996@.TK2MSFTNGP09.phx.gbl...
Options tab of the
>Generate
>
>.
>
|||> Thank you. I did checked "Script indexes" on the Options
> tab.
Which index(es) are you expecting to get scripted? If they're the
system-generated ones (WA_SYS...) they won't be generated because these are
created by the system based on stats. If you're having a problem scripting
something you created, then show us how you created it and exactly what you
are doing that's failing.
|||Make sure you have a non-clustered index. The best would be create one.
Then check by right clicking the table, All Tasks, Manage Indexes, look at
whether there is one with clustered as no.
Then right click the table, all tasks, generate sql script, option tab,
check the script indexes box.
"Bill" <anonymous@.discussions.microsoft.com> wrote in message
news:1bae01c49cf8$e65243f0$a301280a@.phx.gbl...[vbcol=seagreen]
> Thank you. I did checked "Script indexes" on the Options
> tab. I don't see Alter Table section. Can you navigate me
> a little bit? Thank you so much.
> in message
> Options tab of the
|||Thank you very much.
Following the steps in your first paragraph, I only got
the scrip on one UNIQUE CLUSTERED index:
CREATE UNIQUE CLUSTERED
INDEX [PS_RF_ATTR_INSP] ON [dbo].[PS_RF_ATTR_INSP]
([SETID], [INST_PROD_ID], [MARKET], [ATTRIBUTE_ID])
WITH
FILLFACTOR = 90
,DROP_EXISTING
ON [PRIMARY]
When I issue command "sp_helpindex PS_RF_ATTR_INSP
go" I got more nonclustered index names as below:
index_name index_description
index_keys
PS_RF_ATTR_INSP clustered, unique located on PRIMARY
SETID, INST_PROD_ID, MARKET,ATTRIBUTE_ID
index_name index_description
index_keys
PS0RF_INST_PROD nonclustered located on PRIMARY
BO_ID_CUST, SETID, INST_PROD_ID
PS1RF_INST_PROD nonclustered located on PRIMARY
BO_ID_CONTACT, SETID, INST_PROD_ID
... ...
How could I get the scripts for those nonclustered indexes
for the table?

>--Original Message--
>Make sure you have a non-clustered index. The best would
be create one.
>Then check by right clicking the table, All Tasks, Manage
Indexes, look at
>whether there is one with clustered as no.
>Then right click the table, all tasks, generate sql
script, option tab,
>check the script indexes box.
>"Bill" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:1bae01c49cf8$e65243f0$a301280a@.phx.gbl...
me[vbcol=seagreen]
SQL
>
>.
>

Non Clustered Index

Hi ,
When I creare a non clustered index , I get error as show below ,
Server: Msg 1904, Level 16, State 1, Line 1
Cannot specify more than 16 column names for statistics or index key list.
21 specified.
SQL Server only support up to 16 key values ?
Travis Tan
First off, this question should be posted in
Microsoft.public.sqlserver.programming. Clustering is a technology which
allows individual computers to share the same data and back each other up to
prevent system failures. Clients connect to a virtual server, whose
resources float between nodes which form the cluster.
Clustered indexes are when the data is clustered or grouped in a predefined
format or order for rapid retrieval of ranges of data.
16 columns makes your index very large and I suspect inefficient. You may be
able to use an indexed view to group your data in different orders for your
particular usage. And yes, clustered indexes only support a maximum of 16
columns or keys in SQL 200x.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Travis" <Travis@.discussions.microsoft.com> wrote in message
news:79F89C55-B258-40D3-93EE-B02C12676125@.microsoft.com...
> Hi ,
> When I creare a non clustered index , I get error as show below ,
> Server: Msg 1904, Level 16, State 1, Line 1
> Cannot specify more than 16 column names for statistics or index key list.
> 21 specified.
> SQL Server only support up to 16 key values ?
> --
> Travis Tan
|||Yes. Why are you trying to create an index with 21 columns in it? That is
quite a bit beyond overkill.
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
"Travis" <Travis@.discussions.microsoft.com> wrote in message
news:79F89C55-B258-40D3-93EE-B02C12676125@.microsoft.com...
> Hi ,
> When I creare a non clustered index , I get error as show below ,
> Server: Msg 1904, Level 16, State 1, Line 1
> Cannot specify more than 16 column names for statistics or index key list.
> 21 specified.
> SQL Server only support up to 16 key values ?
> --
> Travis Tan
|||Hi
Wrong newsgroup, crossposted to microsoft.public.sqlserver.programming
Why would you want to create a compound index of 16 or more columns? Your
query has to be very specific to be able to use it and the overhead
maintaining it will be high too.
If you have a table, with columns A-Z and you build and index on A, B, C, D
(in that column sequence), a where clause on A, B, C could use the index, a
query on column B can't, neither can a query on C, D. Column A always has to
be involved as it is the 1st sort sequence for the column.
Maybe the DB design is not optimal if you need to go to such extremes.
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/
"Travis" <Travis@.discussions.microsoft.com> wrote in message
news:79F89C55-B258-40D3-93EE-B02C12676125@.microsoft.com...
> Hi ,
> When I creare a non clustered index , I get error as show below ,
> Server: Msg 1904, Level 16, State 1, Line 1
> Cannot specify more than 16 column names for statistics or index key list.
> 21 specified.
> SQL Server only support up to 16 key values ?
> --
> Travis Tan

non clustered index

Can anyone tell me why Enterprise manager-Generate SQL
scripts utility does not generate scripts for non
clustered index? Thanks.Because you didn't check "Script indexes" on the Options tab of the Generate
SQL Scripts dialog?
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Bill" <fei0405@.yahoo.com> wrote in message
news:0e3801c49cf5$0493c450$a501280a@.phx.gbl...
> Can anyone tell me why Enterprise manager-Generate SQL
> scripts utility does not generate scripts for non
> clustered index? Thanks.|||Or you missed it in the Alter Table section?
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ekeFnbPnEHA.3996@.TK2MSFTNGP09.phx.gbl...
> Because you didn't check "Script indexes" on the Options tab of the
Generate
> SQL Scripts dialog?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Bill" <fei0405@.yahoo.com> wrote in message
> news:0e3801c49cf5$0493c450$a501280a@.phx.gbl...
> > Can anyone tell me why Enterprise manager-Generate SQL
> > scripts utility does not generate scripts for non
> > clustered index? Thanks.
>|||Thank you. I did checked "Script indexes" on the Options
tab. I don't see Alter Table section. Can you navigate me
a little bit? Thank you so much.
>--Original Message--
>Or you missed it in the Alter Table section?
>"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote
in message
>news:ekeFnbPnEHA.3996@.TK2MSFTNGP09.phx.gbl...
>> Because you didn't check "Script indexes" on the
Options tab of the
>Generate
>> SQL Scripts dialog?
>> --
>> http://www.aspfaq.com/
>> (Reverse address to reply.)
>>
>>
>> "Bill" <fei0405@.yahoo.com> wrote in message
>> news:0e3801c49cf5$0493c450$a501280a@.phx.gbl...
>> > Can anyone tell me why Enterprise manager-Generate SQL
>> > scripts utility does not generate scripts for non
>> > clustered index? Thanks.
>>
>
>.
>|||> Thank you. I did checked "Script indexes" on the Options
> tab.
Which index(es) are you expecting to get scripted? If they're the
system-generated ones (WA_SYS...) they won't be generated because these are
created by the system based on stats. If you're having a problem scripting
something you created, then show us how you created it and exactly what you
are doing that's failing.|||Make sure you have a non-clustered index. The best would be create one.
Then check by right clicking the table, All Tasks, Manage Indexes, look at
whether there is one with clustered as no.
Then right click the table, all tasks, generate sql script, option tab,
check the script indexes box.
"Bill" <anonymous@.discussions.microsoft.com> wrote in message
news:1bae01c49cf8$e65243f0$a301280a@.phx.gbl...
> Thank you. I did checked "Script indexes" on the Options
> tab. I don't see Alter Table section. Can you navigate me
> a little bit? Thank you so much.
> >--Original Message--
> >Or you missed it in the Alter Table section?
> >
> >"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote
> in message
> >news:ekeFnbPnEHA.3996@.TK2MSFTNGP09.phx.gbl...
> >> Because you didn't check "Script indexes" on the
> Options tab of the
> >Generate
> >> SQL Scripts dialog?
> >>
> >> --
> >> http://www.aspfaq.com/
> >> (Reverse address to reply.)
> >>
> >>
> >>
> >>
> >> "Bill" <fei0405@.yahoo.com> wrote in message
> >> news:0e3801c49cf5$0493c450$a501280a@.phx.gbl...
> >> > Can anyone tell me why Enterprise manager-Generate SQL
> >> > scripts utility does not generate scripts for non
> >> > clustered index? Thanks.
> >>
> >>
> >
> >
> >.
> >|||Thank you very much.
Following the steps in your first paragraph, I only got
the scrip on one UNIQUE CLUSTERED index:
CREATE UNIQUE CLUSTERED
INDEX [PS_RF_ATTR_INSP] ON [dbo].[PS_RF_ATTR_INSP]
([SETID], [INST_PROD_ID], [MARKET], [ATTRIBUTE_ID])
WITH
FILLFACTOR = 90
,DROP_EXISTING
ON [PRIMARY]
When I issue command "sp_helpindex PS_RF_ATTR_INSP
go" I got more nonclustered index names as below:
index_name index_description
index_keys
PS_RF_ATTR_INSP clustered, unique located on PRIMARY
SETID, INST_PROD_ID, MARKET,ATTRIBUTE_ID
index_name index_description
index_keys
PS0RF_INST_PROD nonclustered located on PRIMARY
BO_ID_CUST, SETID, INST_PROD_ID
PS1RF_INST_PROD nonclustered located on PRIMARY
BO_ID_CONTACT, SETID, INST_PROD_ID
... ...
How could I get the scripts for those nonclustered indexes
for the table?
>--Original Message--
>Make sure you have a non-clustered index. The best would
be create one.
>Then check by right clicking the table, All Tasks, Manage
Indexes, look at
>whether there is one with clustered as no.
>Then right click the table, all tasks, generate sql
script, option tab,
>check the script indexes box.
>"Bill" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1bae01c49cf8$e65243f0$a301280a@.phx.gbl...
>> Thank you. I did checked "Script indexes" on the Options
>> tab. I don't see Alter Table section. Can you navigate
me
>> a little bit? Thank you so much.
>> >--Original Message--
>> >Or you missed it in the Alter Table section?
>> >
>> >"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote
>> in message
>> >news:ekeFnbPnEHA.3996@.TK2MSFTNGP09.phx.gbl...
>> >> Because you didn't check "Script indexes" on the
>> Options tab of the
>> >Generate
>> >> SQL Scripts dialog?
>> >>
>> >> --
>> >> http://www.aspfaq.com/
>> >> (Reverse address to reply.)
>> >>
>> >>
>> >>
>> >>
>> >> "Bill" <fei0405@.yahoo.com> wrote in message
>> >> news:0e3801c49cf5$0493c450$a501280a@.phx.gbl...
>> >> > Can anyone tell me why Enterprise manager-Generate
SQL
>> >> > scripts utility does not generate scripts for non
>> >> > clustered index? Thanks.
>> >>
>> >>
>> >
>> >
>> >.
>> >
>
>.
>

Non Clustered Effect

Hi ,
When I finish to create one non clustering index on of of my table , I
found out that the size of the hard disk increase amazingly.
I decide to drop the this index but the hard disk size did not back to
original size before that. What should I do next ? Please help
Travis Tan
Travis wrote:
> Hi ,
> When I finish to create one non clustering index on of of my table
> , I found out that the size of the hard disk increase amazingly.
> I decide to drop the this index but the hard disk size did not back
> to original size before that. What should I do next ? Please help
It's possible to configure automatic grow for database files and in this
way, when a db need space for an object (table or index) it take from os. If
you delete an object the space previously allocated won't be shrunk (unless
you set auto_shrink option but it's better to avoid this).
You may use DBCC SHRINKDATABASE or, better, DBCC SHRINKFILE to reduce the
space used by a database. See BOL for more details and for sintax of DBCC
commands.
Bye
PS: "clustering" in the name of this newsgroup means "Clustering technology"
and not as clustered (or not clustered) index...
Luca Bianchi
Microsoft MVP - SQL Server
http://mvp.support.microsoft.com