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-identity Primary Key

I need to be able to increment a Primary Key of type int without using
IDENTITY.
The reason is that I need to be able to Archive and restore data to this
table, and maintain the Primary Key.
Anyway, I setup a trigger to get the Max(MyIDfield) which works fine when
inserting single records into Table1.
The problem is when I need to insert new records from a select statement.
The trigger doesn't work for a "set" type insert.
I searched examples, but everything I found only supported single inserts.
Here's an example of the table structure.
CREATE TABLE Table1(
MyIDfield int NOT NULL PRIMARY KEY,
MyText varchar(50)
)
CREATE TABLE Table2(
MyIDfield int NOT NULL PRIMARY KEY,
MyText varchar(50)
)
As I explained, I need to..
[1] have unique Primary Keys [MyIDfield] across both tables
[2] be able to insert thousands of records into Table1 using an
insert/select query [trigger solution preferred]
[3] NOT use IDENTITY, in case I need to restore archived records [move back
from Table2 to Table1]
Thanks for any help,
ChrisYou can use the IDENTITY property, archive and restore data and still
maintain the primary key. Deleting records does not reset the IDENTITY
property (unless you use TRUNCATE).
Ben Nevarez, MCDBA, OCP
Database Administrator
"Chris" wrote:

> I need to be able to increment a Primary Key of type int without using
> IDENTITY.
> The reason is that I need to be able to Archive and restore data to this
> table, and maintain the Primary Key.
> Anyway, I setup a trigger to get the Max(MyIDfield) which works fine when
> inserting single records into Table1.
> The problem is when I need to insert new records from a select statement.
> The trigger doesn't work for a "set" type insert.
> I searched examples, but everything I found only supported single inserts.
> Here's an example of the table structure.
> CREATE TABLE Table1(
> MyIDfield int NOT NULL PRIMARY KEY,
> MyText varchar(50)
> )
> CREATE TABLE Table2(
> MyIDfield int NOT NULL PRIMARY KEY,
> MyText varchar(50)
> )
> As I explained, I need to..
> [1] have unique Primary Keys [MyIDfield] across both tables
> [2] be able to insert thousands of records into Table1 using an
> insert/select query [trigger solution preferred]
> [3] NOT use IDENTITY, in case I need to restore archived records [move ba
ck
> from Table2 to Table1]
> Thanks for any help,
> Chris
>
>|||And forgot to mention that you can restore records maintaining their primary
key using SET IDENTITY_INSERT ON (or DTS/SSIS with 'Enable identity insert'
checked).
Ben Nevarez, MCDBA, OCP
Database Administrator
"Ben Nevarez" wrote:
> You can use the IDENTITY property, archive and restore data and still
> maintain the primary key. Deleting records does not reset the IDENTITY
> property (unless you use TRUNCATE).
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "Chris" wrote:
>|||That's the ticket! I've heard of that, but it never came to mind. Never
had a case where I had to use SET IDENTITY_INSERT ON.
Thanks for the help! Perfect solution.
"Ben Nevarez" <bnevarez@.sjm.com> wrote in message
news:217620C1-DAF2-45E5-8660-DD23BE1A8B3C@.microsoft.com...
> And forgot to mention that you can restore records maintaining their
primary
> key using SET IDENTITY_INSERT ON (or DTS/SSIS with 'Enable identity
insert'
> checked).
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "Ben Nevarez" wrote:
>
this
when
statement.
inserts.
[move back|||Chris (rooster575@.hotmail.com) writes:
> I need to be able to increment a Primary Key of type int without using
> IDENTITY.
> The reason is that I need to be able to Archive and restore data to this
> table, and maintain the Primary Key.
> Anyway, I setup a trigger to get the Max(MyIDfield) which works fine when
> inserting single records into Table1.
> The problem is when I need to insert new records from a select statement.
> The trigger doesn't work for a "set" type insert.
> I searched examples, but everything I found only supported single inserts.
Bounce the data over a temp table with an IDENTITY column, and the
MAX value to the IDENTITY column.
If you are on SQL 2005, you could use the Row_number() function and be
saved the temp table.
I assume that the trigger is an INSTEAD OF trigger?
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

nonclustered index fields

Hi folks,
I was wondering if it should be taken as a rule of thumb to allways avoid
the us of the fields that conform the primary key when we are designing any
nonclustered index, as in reality they allready have this key in "their
inside".
For example, say we have a pk composed of the fields: date, id
and then we create an index1 with fields : field1, date
and yet another index2 with just field: field1
Then, whenever I search in this simplistic example for field 1 between a
range of dates I allways get better performance with just the simple index.
The question is, will it happen allways or will it depend on the particular
queries and or situations?
Thanks in advance,
Tristan."Tristan" <Tristan@.discussions.microsoft.com> wrote in message
news:68D78CF8-E360-4365-9AE6-7CEA2D1C6C68@.microsoft.com...
> Hi folks,
> I was wondering if it should be taken as a rule of thumb to allways avoid
> the us of the fields that conform the primary key when we are designing
> any
> nonclustered index, as in reality they allready have this key in "their
> inside".
> For example, say we have a pk composed of the fields: date, id
> and then we create an index1 with fields : field1, date
> and yet another index2 with just field: field1
> Then, whenever I search in this simplistic example for field 1 between a
> range of dates I allways get better performance with just the simple
> index.
> The question is, will it happen allways or will it depend on the
> particular
> queries and or situations?
>
It depends. Often, for instance, you will have a non-clustered index on the
trailing column of a two-column clustered primary key. in your example a
non-clustered index on id would probably be appropriate to enable lookups by
ID. Even though the ID is replicated in the index leaf data, since it is
not the leading column in the clustered primary key, the clustered primary
key does not provide an efficient access path for lookups or sorting by ID.
David|||Tristan
You are confusing PK with clustered index. They are not the same thing.
Clustered index keys are contained in every NC index, but the clustered
index might not be on the primary key column(s).
However, if we assume you meant clustered index when you said pk, then you
answered your own questions. It completely depends on your queries and your
data distributions. A very general rule of thumb is that if you do a lot of
modifications, you want to keep your indexes to a minimum, so you wouldn't
have indexes with overlapping keys. But if you do mainly SELECTs, more
indexes can be useful, and having different leading columns can be a BIG
help. However, in your specific example, your index1 and index2 will be
identical in every way, so there is little reason to have both of them.
I wrote an article for SQL Server Magazine about a year ago on the reasons
why you might want to explicitly list one of your clustered index columns in
your nc index definitions.
http://www.sqlmag.com/Article/Artic...rver_44807.html
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Tristan" <Tristan@.discussions.microsoft.com> wrote in message
news:68D78CF8-E360-4365-9AE6-7CEA2D1C6C68@.microsoft.com...
> Hi folks,
> I was wondering if it should be taken as a rule of thumb to allways avoid
> the us of the fields that conform the primary key when we are designing
> any
> nonclustered index, as in reality they allready have this key in "their
> inside".
> For example, say we have a pk composed of the fields: date, id
> and then we create an index1 with fields : field1, date
> and yet another index2 with just field: field1
> Then, whenever I search in this simplistic example for field 1 between a
> range of dates I allways get better performance with just the simple
> index.
> The question is, will it happen allways or will it depend on the
> particular
> queries and or situations?
> Thanks in advance,
> Tristan.|||Thanks lot Kalen & David for your replies. Indeed I pretendet to say
clustered when I used pk, thanks por pointing that out. Your answers have
being most clarifiying, thanks again,
Tristan.
"Kalen Delaney" wrote:

> Tristan
> You are confusing PK with clustered index. They are not the same thing.
> Clustered index keys are contained in every NC index, but the clustered
> index might not be on the primary key column(s).
> However, if we assume you meant clustered index when you said pk, then you
> answered your own questions. It completely depends on your queries and you
r
> data distributions. A very general rule of thumb is that if you do a lot o
f
> modifications, you want to keep your indexes to a minimum, so you wouldn't
> have indexes with overlapping keys. But if you do mainly SELECTs, more
> indexes can be useful, and having different leading columns can be a BIG
> help. However, in your specific example, your index1 and index2 will be
> identical in every way, so there is little reason to have both of them.
> I wrote an article for SQL Server Magazine about a year ago on the reasons
> why you might want to explicitly list one of your clustered index columns
in
> your nc index definitions.
> http://www.sqlmag.com/Article/Artic...rver_44807.html
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Tristan" <Tristan@.discussions.microsoft.com> wrote in message
> news:68D78CF8-E360-4365-9AE6-7CEA2D1C6C68@.microsoft.com...
>
>

Non-Cluster Index Primary Key

I inherited a SQL Server 2000 database that has performance issues. I
noticed the primary keys are set up as non-cluster indexes.
By default the table primary key is created as cluster indexes. Could this
cause a database performance problems if these are large tables and access
often.
If the table were set up with the primary key as a non-cluster index?
Thanks,There is no requirement that the PK (or any index for that matter) be
clustered. But it is recommended that every table have a clustered index.
Which one depends on how you are using it. You have to narrow down the
performance issues much more before anyone can answer this question. See if
these help:
http://www.microsoft.com/sql/techin.../perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.c...mance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.c...rmance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/d.../>
on_24u1.asp
Disk Monitoring
Andrew J. Kelly SQL MVP
"Joe K." <JoeK@.discussions.microsoft.com> wrote in message
news:17DC89CC-A77A-486E-BADF-60AC39B5A973@.microsoft.com...
> I inherited a SQL Server 2000 database that has performance issues. I
> noticed the primary keys are set up as non-cluster indexes.
> By default the table primary key is created as cluster indexes. Could this
> cause a database performance problems if these are large tables and access
> often.
> If the table were set up with the primary key as a non-cluster index?
>
> Thanks,

Non-Cluster Index Primary Key

I inherited a SQL Server 2000 database that has performance issues. I
noticed the primary keys are set up as non-cluster indexes.
By default the table primary key is created as cluster indexes. Could this
cause a database performance problems if these are large tables and access
often.
If the table were set up with the primary key as a non-cluster index?
Thanks,There is no requirement that the PK (or any index for that matter) be
clustered. But it is recommended that every table have a clustered index.
Which one depends on how you are using it. You have to narrow down the
performance issues much more before anyone can answer this question. See if
these help:
http://www.microsoft.com/sql/techinfo/administration/2000/perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.com/sql_server_performance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.com/best_sql_server_performance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_perfmon_24u1.asp
Disk Monitoring
--
Andrew J. Kelly SQL MVP
"Joe K." <JoeK@.discussions.microsoft.com> wrote in message
news:17DC89CC-A77A-486E-BADF-60AC39B5A973@.microsoft.com...
> I inherited a SQL Server 2000 database that has performance issues. I
> noticed the primary keys are set up as non-cluster indexes.
> By default the table primary key is created as cluster indexes. Could this
> cause a database performance problems if these are large tables and access
> often.
> If the table were set up with the primary key as a non-cluster index?
>
> Thanks,

Non-Cluster Index Primary Key

I inherited a SQL Server 2000 database that has performance issues. I
noticed the primary keys are set up as non-cluster indexes.
By default the table primary key is created as cluster indexes. Could this
cause a database performance problems if these are large tables and access
often.
If the table were set up with the primary key as a non-cluster index?
Thanks,
There is no requirement that the PK (or any index for that matter) be
clustered. But it is recommended that every table have a clustered index.
Which one depends on how you are using it. You have to narrow down the
performance issues much more before anyone can answer this question. See if
these help:
http://www.microsoft.com/sql/techinf...perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.co...ance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.co...mance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/de...rfmon_24u1.asp
Disk Monitoring
Andrew J. Kelly SQL MVP
"Joe K." <JoeK@.discussions.microsoft.com> wrote in message
news:17DC89CC-A77A-486E-BADF-60AC39B5A973@.microsoft.com...
> I inherited a SQL Server 2000 database that has performance issues. I
> noticed the primary keys are set up as non-cluster indexes.
> By default the table primary key is created as cluster indexes. Could this
> cause a database performance problems if these are large tables and access
> often.
> If the table were set up with the primary key as a non-cluster index?
>
> Thanks,

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:
>

No temp table

Is it possible to rewrite this delete so that a temp table isn't used?
key_1 and date_key form the primary key. I want to remove any rows with the
same key_1 and a date_key greater than today's date.
Cheers!
declare @.today datetime
select @.today = getdate()
select key_1, date_key
into #temp
from my_table
where date_key > @.today
group by key_1 having count(*) > 1
delete my_table
from #temp, my_table m
where #temp.date_key = m.date_key
and #temp.key_1 = m.key_1Gyruss wrote:
> Is it possible to rewrite this delete so that a temp table isn't used?
> key_1 and date_key form the primary key. I want to remove any rows
> with the same key_1 and a date_key greater than today's date.
> Cheers!
> declare @.today datetime
> select @.today = getdate()
> select key_1, date_key
> into #temp
> from my_table
> where date_key > @.today
> group by key_1 having count(*) > 1
>
> delete my_table
> from #temp, my_table m
> where #temp.date_key = m.date_key
> and #temp.key_1 = m.key_1
Untested:
Delete From my_table
Where date_key > @.today
and key_1 IN (
Select key_1
From my_table
Where date_key > @.today
group by key_1 having count(*) > 1)
David Gugick
Imceda Software
www.imceda.com|||DELETE FROM My_table
WHERE EXISTS
(SELECT *
FROM My_table AS M
WHERE M.date_key = Mytable.date_key
AND M..key_1 = Mytable.key_1);
You probably want to learn SQL and stop using the dialect with the FROM
clause and joins in it. The results are unpredictable as well as not
portable.|||The SELECT statement you posted isn't legal so I'm not certain what you
intended. You also didn't tell us the primary key, which might have
helped. Do you have a key?
DELETE FROM my_table
WHERE date_key > CURRENT_TIMESTAMP
AND EXISTS
(SELECT *
FROM my_table AS M
WHERE key_1 = my_table.key_1
AND date_key <= CURRENT_TIMESTAMP)
David Portas
SQL Server MVP
--|||>> I want to remove any rows with the same key_1 and a date_key greater
than today's date <<
Opps! I read to mean that if there is a group with all members in the
future, we leave them alone. If there is a group with all members in
the past or present, we leave them alone. If there is a group with
members in the past or present or future, we delete the future members.
DELETE FROM My_table
WHERE Mytable.date_key > CURRENT_TIMESTAMP
AND EXISTS
(SELECT *
FROM My_table AS M
WHERE AND M.key_1 = Mytable.key_1
AND M.date_key <= CURRENT_TIMESTAMP );|||On Sat, 26 Feb 2005 09:50:04 +1100, Gyruss wrote:

>Is it possible to rewrite this delete so that a temp table isn't used?
>key_1 and date_key form the primary key. I want to remove any rows with th
e
>same key_1 and a date_key greater than today's date.
>Cheers!
>declare @.today datetime
>select @.today = getdate()
> select key_1, date_key
> into #temp
> from my_table
> where date_key > @.today
>group by key_1 having count(*) > 1
>
>delete my_table
> from #temp, my_table m
> where #temp.date_key = m.date_key
> and #temp.key_1 = m.key_1
>
Hi Gyruss,
I'm . Your text says "any rows with the same key_1 and a
date_key greater than todays's date". But the query says "any rows with
the same key_1 and a data_key greater than today's date, unless only one
such row exists for this key_1".
Try this one. I didn't test it, since you didn't post CREATE TABLE and
INSERT statements to test it with.
DELETE my_table
WHERE date_key > CURRENT_TIMESTAMP
AND (SELECT COUNT(*)
FROM my_table AS a
WHERE a.key_1 = my_table.key_1
AND a.date_key > CURRENT_TIMESTAMP) > 1
This mimics your query. If the text description is accurate, you can
reduce this to
DELETE my_table
WHERE date_key > CURRENT_TIMESTAMP
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)