print @variable probem

[I got a serious problem on print @.variable, please be kind to take a look]
CREATE PROCEDURE dbo.Get_seainvno
@.prefix varchar(20),
@.countno int,
@.period varchar(6),
@.result int output
AS
DECLARE @.testing int
IF @.countno > 0
BEGIN
SELECT number AS result
FROM arinvNo
WHERE prefix = @.prefix
print @.result <--I can show
SELECT @.testing = @.result %100
print @.testing <----It
can't show the variable in SQL analayzer ? Pls help
IF (@.result % 100) >= 90
BEGIN
UPDATE arinvNo
SET number = number + 75
WHERE prefix = @.prefix
END
ELSE
BEGIN
UPDATE arinvNo
SET number = number + 1
WHERE prefix = @.prefix
ENDDid you put out the length of the result, to see wheter it exsists ?
Additionaly it would put some identifier around this:
print 'The result after is:' + Cast(ISNULL(@.result,'Value is NULL') as
varchar(20))
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Ray" <someone@.microsoft.com> schrieb im Newsbeitrag
news:%23wiSwouSFHA.3152@.TK2MSFTNGP12.phx.gbl...
> [I got a serious problem on print @.variable, please be kind to take a
> look]
> CREATE PROCEDURE dbo.Get_seainvno
> @.prefix varchar(20),
> @.countno int,
> @.period varchar(6),
> @.result int output
> AS
> DECLARE @.testing int
> IF @.countno > 0
> BEGIN
> SELECT number AS result
> FROM arinvNo
> WHERE prefix = @.prefix
> print @.result <--I can show
> SELECT @.testing = @.result %100
> print @.testing
> <----It can't show
> the variable in SQL analayzer ? Pls help
> IF (@.result % 100) >= 90
> BEGIN
> UPDATE arinvNo
> SET number = number + 75
> WHERE prefix = @.prefix
> END
> ELSE
> BEGIN
> UPDATE arinvNo
> SET number = number + 1
> WHERE prefix = @.prefix
> END
>|||Ray
I try and always use a SET when settting variables. The reason for this is
when the select returns a null you won't get unexpected or null results. I
know you can use isnull or set concat_null_yields_null but it's a lot of
work around for something that could have been prevented in the first place.
Coming from a C background I also like to always initialise variables. Even
if it's not needed.
Instead of select @.var = col from table
I would use
Set @.var = 0
I would use set @.var = (select col from table)
But to come back to your problem without changing your code... what's the
parameters your running this procedure with. I'm sure you are no noddy but
you sure you want to use % modulus , when you modulus something is will
always return everything n - 1 when mod n thus 99 - 91 will fall through you
r
loop. Is this suppose to happen ?
"Ray" wrote:

> [I got a serious problem on print @.variable, please be kind to take a look
]
> CREATE PROCEDURE dbo.Get_seainvno
> @.prefix varchar(20),
> @.countno int,
> @.period varchar(6),
> @.result int output
> AS
> DECLARE @.testing int
> IF @.countno > 0
> BEGIN
> SELECT number AS result
> FROM arinvNo
> WHERE prefix = @.prefix
> print @.result <--I can show
> SELECT @.testing = @.result %100
> print @.testing <----
It
> can't show the variable in SQL analayzer ? Pls help
> IF (@.result % 100) >= 90
> BEGIN
> UPDATE arinvNo
> SET number = number + 75
> WHERE prefix = @.prefix
> END
> ELSE
> BEGIN
> UPDATE arinvNo
> SET number = number + 1
> WHERE prefix = @.prefix
> END
>
>|||Ray
I try and always use a SET when settting variables. The reason for this is
when the select returns a null you won't get unexpected or null results. I
know you can use isnull or set concat_null_yields_null but it's a lot of
work around for something that could have been prevented in the first place.
Coming from a C background I also like to always initialise variables. Even
if it's not needed.
Instead of select @.var = col from table
I would use
Set @.var = 0
I would use set @.var = (select col from table)
But to come back to your problem without changing your code... what's the
parameters your running this procedure with. I'm sure you are no noddy but
you sure you want to use % modulus , when you modulus something is will
always return everything n - 1 when mod n thus 99 - 91 will fall through you
r
loop. Is this suppose to happen ?
"Ray" wrote:

> [I got a serious problem on print @.variable, please be kind to take a look
]
> CREATE PROCEDURE dbo.Get_seainvno
> @.prefix varchar(20),
> @.countno int,
> @.period varchar(6),
> @.result int output
> AS
> DECLARE @.testing int
> IF @.countno > 0
> BEGIN
> SELECT number AS result
> FROM arinvNo
> WHERE prefix = @.prefix
> print @.result <--I can show
> SELECT @.testing = @.result %100
> print @.testing <----
It
> can't show the variable in SQL analayzer ? Pls help
> IF (@.result % 100) >= 90
> BEGIN
> UPDATE arinvNo
> SET number = number + 75
> WHERE prefix = @.prefix
> END
> ELSE
> BEGIN
> UPDATE arinvNo
> SET number = number + 1
> WHERE prefix = @.prefix
> END
>
>|||On Wed, 27 Apr 2005 13:30:19 +0800, Ray wrote:

>[I got a serious problem on print @.variable, please be kind to take a look]
(snip)
> SELECT number AS result
> FROM arinvNo
> WHERE prefix = @.prefix
>print @.result <--I can show
(snip)
Hi Ray,
I'm not sure why this PRINT shows anything, nor what it shows - but it
won't be the number just retrieved from the arinvNo table.
I think you want this syntax:
SELECT @.result = number
FROM arinvNo
WHERE prefix = @.prefix
(Note the changed SELECT clause - I added the @. to indicate a variable and
changed the syntax to make it an assignment SELECT statement).
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

PrimaryKey and unique

how can I design a table to get unique values in a column within the PK
columns?
Example:
Client (int, PK)
User (int, PK
Userid(char...)
userid's shoud be unique for a client. Other clients are allowed to have
same userid's as other clients. A second user of a client cannot have the
same userid as other user of this client.
thanks for any help.
Looks like you have it almost right:
alter table MyTable
add
constraint PK_MyTable primary key (ClientID, UserID)
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Michael Haberichter" <Haberichter@.community.nospam> wrote in message
news:ED15E772-0327-4C98-8FBD-92AE3717D1FB@.microsoft.com...
how can I design a table to get unique values in a column within the PK
columns?
Example:
Client (int, PK)
User (int, PK
Userid(char...)
userid's shoud be unique for a client. Other clients are allowed to have
same userid's as other clients. A second user of a client cannot have the
same userid as other user of this client.
thanks for any help.
sql

Primary keys, indexes and speed

My table consists of several key fields (i.e. used in a complex primary key)
and some other fields where I do aggregation:
CREATE TABLE t
(
id INT NOT NULL,
ts DATETIME NOT NULL,
k1 INT NOT NULL,
k2 INT NOT NULL,
...
a1 FLOAT NOT NULL,
a2 FLOAT NOT NULL,
...
PRIMARY KEY (id, ts, k1, k2)
)
A SELECT query I execute on this table looks like:
SELECT k1, k2, SUM(a1) AS s1, SUM(a2) AS s2 FROM t
WHERE id = SomeNumber AND ts > StartDate AND ts <= EndDate
GROUP BY k1, k2 ORDER BY s1 DESC
As you see, id and ts are used in WHERE and k1 and k2 - in GROUP.
Do I need to add some more indexes to improve speed of retrieving
or above PRIMARY KEY is enough?It seems to be a good candidate for an indexed view ,isn't it? Read about it
in the BOL
"Tumurbaatar S." <nospam_tumur@.magicnet.mn> wrote in message
news:e5qJD2oOFHA.1884@.TK2MSFTNGP15.phx.gbl...
> My table consists of several key fields (i.e. used in a complex primary
key)
> and some other fields where I do aggregation:
> CREATE TABLE t
> (
> id INT NOT NULL,
> ts DATETIME NOT NULL,
> k1 INT NOT NULL,
> k2 INT NOT NULL,
> ...
> a1 FLOAT NOT NULL,
> a2 FLOAT NOT NULL,
> ...
> PRIMARY KEY (id, ts, k1, k2)
> )
> A SELECT query I execute on this table looks like:
> SELECT k1, k2, SUM(a1) AS s1, SUM(a2) AS s2 FROM t
> WHERE id = SomeNumber AND ts > StartDate AND ts <= EndDate
> GROUP BY k1, k2 ORDER BY s1 DESC
> As you see, id and ts are used in WHERE and k1 and k2 - in GROUP.
> Do I need to add some more indexes to improve speed of retrieving
> or above PRIMARY KEY is enough?
>|||Have you tried it yet? Or are you just guessing? Do you have a lot of
other columns? Since you have this as the clustered index, if it is the
ONLY index involved in any query, it might be enough.
Either way, you should test it out and see what happens. A lot depends on
what else you do with the table, because it can be too costly to add a bunch
of indexes to a table to speed up one query and hurt the others.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Tumurbaatar S." <nospam_tumur@.magicnet.mn> wrote in message
news:e5qJD2oOFHA.1884@.TK2MSFTNGP15.phx.gbl...
> My table consists of several key fields (i.e. used in a complex primary
> key)
> and some other fields where I do aggregation:
> CREATE TABLE t
> (
> id INT NOT NULL,
> ts DATETIME NOT NULL,
> k1 INT NOT NULL,
> k2 INT NOT NULL,
> ...
> a1 FLOAT NOT NULL,
> a2 FLOAT NOT NULL,
> ...
> PRIMARY KEY (id, ts, k1, k2)
> )
> A SELECT query I execute on this table looks like:
> SELECT k1, k2, SUM(a1) AS s1, SUM(a2) AS s2 FROM t
> WHERE id = SomeNumber AND ts > StartDate AND ts <= EndDate
> GROUP BY k1, k2 ORDER BY s1 DESC
> As you see, id and ts are used in WHERE and k1 and k2 - in GROUP.
> Do I need to add some more indexes to improve speed of retrieving
> or above PRIMARY KEY is enough?
>|||Tumurbaatar S.,
What about the execution plan, can you post it?
set showplan_text on
go
SELECT k1, k2, SUM(a1) AS s1, SUM(a2) AS s2 FROM t
WHERE id = SomeNumber AND ts > StartDate AND ts <= EndDate
GROUP BY k1, k2 ORDER BY s1 DESC
go
set showplan_text off
go
AMB
P.S. Your last name remind me a student in my classroom, when I was in
college (studying Physics), he was from Mongolia.
"Tumurbaatar S." wrote:

> My table consists of several key fields (i.e. used in a complex primary ke
y)
> and some other fields where I do aggregation:
> CREATE TABLE t
> (
> id INT NOT NULL,
> ts DATETIME NOT NULL,
> k1 INT NOT NULL,
> k2 INT NOT NULL,
> ...
> a1 FLOAT NOT NULL,
> a2 FLOAT NOT NULL,
> ...
> PRIMARY KEY (id, ts, k1, k2)
> )
> A SELECT query I execute on this table looks like:
> SELECT k1, k2, SUM(a1) AS s1, SUM(a2) AS s2 FROM t
> WHERE id = SomeNumber AND ts > StartDate AND ts <= EndDate
> GROUP BY k1, k2 ORDER BY s1 DESC
> As you see, id and ts are used in WHERE and k1 and k2 - in GROUP.
> Do I need to add some more indexes to improve speed of retrieving
> or above PRIMARY KEY is enough?
>
>|||Since ts predicate is a range, I'd try changing the clustered PK index to
put ts first, instead of second... That might make a substantial
improvement...
"Tumurbaatar S." wrote:

> My table consists of several key fields (i.e. used in a complex primary ke
y)
> and some other fields where I do aggregation:
> CREATE TABLE t
> (
> id INT NOT NULL,
> ts DATETIME NOT NULL,
> k1 INT NOT NULL,
> k2 INT NOT NULL,
> ...
> a1 FLOAT NOT NULL,
> a2 FLOAT NOT NULL,
> ...
> PRIMARY KEY (id, ts, k1, k2)
> )
> A SELECT query I execute on this table looks like:
> SELECT k1, k2, SUM(a1) AS s1, SUM(a2) AS s2 FROM t
> WHERE id = SomeNumber AND ts > StartDate AND ts <= EndDate
> GROUP BY k1, k2 ORDER BY s1 DESC
> As you see, id and ts are used in WHERE and k1 and k2 - in GROUP.
> Do I need to add some more indexes to improve speed of retrieving
> or above PRIMARY KEY is enough?
>
>|||Yes, it is. But my application and db is designed to
work with any (at least, with many) OLEDB/ADO compliant
databases. And indexed view, I think, is MS SQL feature.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ehYzaRpOFHA.2132@.TK2MSFTNGP14.phx.gbl...
> It seems to be a good candidate for an indexed view ,isn't it? Read about
> it
> in the BOL
>
> "Tumurbaatar S." <nospam_tumur@.magicnet.mn> wrote in message
> news:e5qJD2oOFHA.1884@.TK2MSFTNGP15.phx.gbl...
> key)
>|||Yes, I tried. My server app works (24/7) and regularly adds new records.
And other, client, app retrieves these records to analyze. No update/delete
happens on the table. Only insert and select. And no many variants
of select, just one I posted above.
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:e47gILqOFHA.2252@.TK2MSFTNGP15.phx.gbl...
> Have you tried it yet? Or are you just guessing? Do you have a lot of
> other columns? Since you have this as the clustered index, if it is the
> ONLY index involved in any query, it might be enough.
> Either way, you should test it out and see what happens. A lot depends on
> what else you do with the table, because it can be too costly to add a
> bunch of indexes to a table to speed up one query and hurt the others.
> --
> ----
--
> Louis Davidson - drsql@.hotmail.com
> SQL Server MVP
> Compass Technology Management - www.compass.net
> Pro SQL Server 2000 Database Design -
> http://www.apress.com/book/bookDisplay.html?bID=266
> Blog - http://spaces.msn.com/members/drsql/
> Note: Please reply to the newsgroups only unless you are interested in
> consulting services. All other replies may be ignored :)
> "Tumurbaatar S." <nospam_tumur@.magicnet.mn> wrote in message
> news:e5qJD2oOFHA.1884@.TK2MSFTNGP15.phx.gbl...
>|||Ok, I will try.
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:36D28CF4-7839-4F8B-ACDC-D4D23DA61AA9@.microsoft.com...
> Since ts predicate is a range, I'd try changing the clustered PK index to
> put ts first, instead of second... That might make a substantial
> improvement...
>
> "Tumurbaatar S." wrote:
>|||On Tue, 12 Apr 2005 14:25:52 +0800, Tumurbaatar S. wrote:

>See attachment. There's an actual table structure, a query I used and
>a resulting plan.
Hi Tumurbaatar,
I think the query will be executed at about the maximum possible speed.
The clustered index that is created because of the PRIMARY KEY
constraint is optimal for this query. (Note - near the end of this
message, I'll discuss one possible improvement).
Allow me to use an analogy to explain: imagine that you have a phone
book of a large town, were all people are listed in alphabetic order of
the surname. Further, people with the same surname are ordered by first
name. In database terms, the phone book is a table with clustered index
on (Surname, FirstName).
Now suppose you are given the task of finding all people with Surname
'Smith' and whose first name starts with H, I or J. That's not hard -
you quickly flip to the page where the Smith's are listed, skip to the
first whose name starts with H, then start scanning all names - and you
stop as soon as you encounter a Smith whose first name starts with K.
But what if your task had been to find all people with first name 'John'
and surname starting with K, L, or M? In that case, you'd have to scan a
much larger proportion of the phone book (the complete listing of all
surnames starting with K, L, or M, to be precise). That's why I don't
think that CBretana's suggestion will improve the speed of your query.

>By the way, I understood nothing there.
The execution plan, you mean? Okay, take it easy. The best way to
interpret an execution plan is from innermost to outermost. This plan is
quite straightforward, actually :-)
The last line says "Clustered Index S". That is the process I
described above (in the phone book analogy) - the database uses the
index' structure to go straight to the first row with intid = 1 and ts >
'2005-4-1 00:00', then starts processing rows until it is past the last
row with intid = 1 and ts <= '2005-4-1 00:30'.
The line before that is a sort step. This is used to satisfy the group
by clause - after the sort, all rows that need to be grouped together
are together. (There are more strategies the optimizer can use for a
group by, but in this case the optimizer expects the sorting to be the
fastest).
The Stream Aggregate step takes the sorted output and calculates one row
from each group. If you inspect the entire line on your plan, you'll see
the three aggregate expressions you used in the query appear here. Note
that each result is given a name (Expr1002 through Expr1004).
And the final step is another sort step - this time to satisfy the ORDER
BY you specified. Note that the plan says to order by [Expr1004] - and
if you check the Stream Aggregate step, you'll see that Expr1004 is
exactly the name given to the expression you use in the ORDER BY.
I promised a possible improvement. I can't say if this works or not. The
idea is to eliminate one of the sort steps, at the cost of having to
scan a much bigger part of the table. If only a small number of rows in
your table satisfy the criteria in the WHERE clause, you can expect to
see performance plummetting. But if the WHERE clause is not very
selective and you were already reading most of your data anyway, then
this suggestion might help:
Change the table definition to:
CREATE TABLE dilink
(
intid SMALLINT NOT NULL,
ts SMALLDATETIME NOT NULL,
dr INT NOT NULL,
daddr INT NOT NULL,
ib FLOAT NOT NULL,
ob FLOAT NOT NULL,
CONSTRAINT dilink_intidtsdrdaddr
PRIMARY KEY (dr, daddr, intid, ts),
CONSTRAINT dilink_intidts
FOREIGN KEY (intid, ts)
REFERENCES didata (intid, ts) ON DELETE CASCADE
)
The only thing I changed is the order of columns in the primary key
constraint. This will als affect the order of columns in the index that
gets created for this constraint. The effect will be that the database
now has to scan the whole table to find the rows that match the WHERE
clause, but that the rows found will already be in order of dr, daddr
so that there is no need to sort in order to satsify the GROUP BY.
I must add that I *expect* this version to suck. It will only help you
if your table has very unusual data distribution!
If performance of your query is really critical, you'd be better advised
to change this query into an indexed view. That will result in instant
results when you want to see this data - but at the price of slower
inserts, updates and deletes (as SQL Server has to do extra work to keep
the indexed view current).
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Many thanks!
The most important criteria is a database size, SELECT speed
is the second one, so may be I will keep a current structure.
Thanks again!
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:kgao51t7i2cv1simi0o9p975bv01m9cbuv@.
4ax.com...
> On Tue, 12 Apr 2005 14:25:52 +0800, Tumurbaatar S. wrote:
>
> Hi Tumurbaatar,
> I think the query will be executed at about the maximum possible speed.
> The clustered index that is created because of the PRIMARY KEY
> constraint is optimal for this query. (Note - near the end of this
> message, I'll discuss one possible improvement).
> Allow me to use an analogy to explain: imagine that you have a phone
> book of a large town, were all people are listed in alphabetic order of
> the surname. Further, people with the same surname are ordered by first
> name. In database terms, the phone book is a table with clustered index
> on (Surname, FirstName).
> Now suppose you are given the task of finding all people with Surname
> 'Smith' and whose first name starts with H, I or J. That's not hard -
> you quickly flip to the page where the Smith's are listed, skip to the
> first whose name starts with H, then start scanning all names - and you
> stop as soon as you encounter a Smith whose first name starts with K.
> But what if your task had been to find all people with first name 'John'
> and surname starting with K, L, or M? In that case, you'd have to scan a
> much larger proportion of the phone book (the complete listing of all
> surnames starting with K, L, or M, to be precise). That's why I don't
> think that CBretana's suggestion will improve the speed of your query.
>
> The execution plan, you mean? Okay, take it easy. The best way to
> interpret an execution plan is from innermost to outermost. This plan is
> quite straightforward, actually :-)
> The last line says "Clustered Index S". That is the process I
> described above (in the phone book analogy) - the database uses the
> index' structure to go straight to the first row with intid = 1 and ts >
> '2005-4-1 00:00', then starts processing rows until it is past the last
> row with intid = 1 and ts <= '2005-4-1 00:30'.
> The line before that is a sort step. This is used to satisfy the group
> by clause - after the sort, all rows that need to be grouped together
> are together. (There are more strategies the optimizer can use for a
> group by, but in this case the optimizer expects the sorting to be the
> fastest).
> The Stream Aggregate step takes the sorted output and calculates one row
> from each group. If you inspect the entire line on your plan, you'll see
> the three aggregate expressions you used in the query appear here. Note
> that each result is given a name (Expr1002 through Expr1004).
> And the final step is another sort step - this time to satisfy the ORDER
> BY you specified. Note that the plan says to order by [Expr1004] - and
> if you check the Stream Aggregate step, you'll see that Expr1004 is
> exactly the name given to the expression you use in the ORDER BY.
>
> I promised a possible improvement. I can't say if this works or not. The
> idea is to eliminate one of the sort steps, at the cost of having to
> scan a much bigger part of the table. If only a small number of rows in
> your table satisfy the criteria in the WHERE clause, you can expect to
> see performance plummetting. But if the WHERE clause is not very
> selective and you were already reading most of your data anyway, then
> this suggestion might help:
> Change the table definition to:
> CREATE TABLE dilink
> (
> intid SMALLINT NOT NULL,
> ts SMALLDATETIME NOT NULL,
> dr INT NOT NULL,
> daddr INT NOT NULL,
> ib FLOAT NOT NULL,
> ob FLOAT NOT NULL,
> CONSTRAINT dilink_intidtsdrdaddr
> PRIMARY KEY (dr, daddr, intid, ts),
> CONSTRAINT dilink_intidts
> FOREIGN KEY (intid, ts)
> REFERENCES didata (intid, ts) ON DELETE CASCADE
> )
> The only thing I changed is the order of columns in the primary key
> constraint. This will als affect the order of columns in the index that
> gets created for this constraint. The effect will be that the database
> now has to scan the whole table to find the rows that match the WHERE
> clause, but that the rows found will already be in order of dr, daddr
> so that there is no need to sort in order to satsify the GROUP BY.
> I must add that I *expect* this version to suck. It will only help you
> if your table has very unusual data distribution!
>
> If performance of your query is really critical, you'd be better advised
> to change this query into an indexed view. That will result in instant
> results when you want to see this data - but at the price of slower
> inserts, updates and deletes (as SQL Server has to do extra work to keep
> the indexed view current).
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

Primary Key Index

Hi,

I have a table in my SQL 2000 database called utContact, this has a primary called ContactID (int, idenitity), by default this should have a clustered index on it, but when I go to view the indexes on the table it shows the primary key index as non clustered.

How can this be?

SQL server also will not let me change the index to clustered, but I need to as this is causing a lot of table scans in query execution plans.

Any help or advice on this matter would be greatly appreciated.

CheersOriginally posted by SQLSlammer
Hi,

I have a table in my SQL 2000 database called utContact, this has a primary called ContactID (int, idenitity), by default this should have a clustered index on it, but when I go to view the indexes on the table it shows the primary key index as non clustered.

How can this be?

SQL server also will not let me change the index to clustered, but I need to as this is causing a lot of table scans in query execution plans.

Any help or advice on this matter would be greatly appreciated.

Cheers
there can be only one clustered index on a table. by default when u add a primary key a clustered index is created, but if there is already a clustered index present on the table, a non clustered index will be created. Check if there exists a clustered index on any other column in the table.|||Hi,

there is no other clustered index, they are all non clustered.|||can u post the script for the table?|||Here you go|||it works for me.
i created the table as per your script and made contact id as the primary key, it creates a clustered index on the column.|||Yeah it would do,

But for some reason mine shows as a non clustered index, this is not a new table, it was created before I joined the company.

What I really need to know is how do I convert this index into a clustered index without losing any data.|||Have you tried dropping all indexes and pk's and reapplying them?

primary key data type

Where is the biggest difference in performance between a 'int' ID and
a 'nvarchar' ID on big tables?
On 7 Feb 2007 21:30:03 -0800, retima@.gmail.com wrote:

>Where is the biggest difference in performance between a 'int' ID and
>a 'nvarchar' ID on big tables?
The PK is used as a final pointer by all other indexes on the table,
so whatever the size difference between the int and average nvarchar
(plus length indicator), may be multiplied, and makes other indexes
somewhat less dense, which is in general a bad thing.
J.
|||On Feb 8, 7:39 am, JXStern <JXSternChange...@.gte.net> wrote:
> On 7 Feb 2007 21:30:03 -0800, ret...@.gmail.com wrote:
>
> The PK is used as a final pointer by all other indexes on the table,
> so whatever the size difference between the int and average nvarchar
> (plus length indicator), may be multiplied, and makes other indexes
> somewhat less dense, which is in general a bad thing.
> J.
thanks J.
Can you direct me to an example which is more specific?
(a certain query that would prove it with numbers)
|||Yaniv,shalom
http://www.sql-server-performance.com/datatypes.asp
<yaniv.harpaz@.gmail.com> wrote in message
news:1170919960.359095.315430@.p10g2000cwp.googlegr oups.com...
> On Feb 8, 7:39 am, JXStern <JXSternChange...@.gte.net> wrote:
> thanks J.
> Can you direct me to an example which is more specific?
> (a certain query that would prove it with numbers)
>
|||I think that you mixed up primary key and clustered index. None
clustered index are using the clustered index's keys as a pointer to
the data. Of course if the primary key is clustered then all the none
clustered index will use the PK as the pointer to the data, but many
times there is another clustered index (and sometimes there is no
clustered index at all).
Adi
On Feb 8, 7:39 am, JXStern <JXSternChange...@.gte.net> wrote:
> On 7 Feb 2007 21:30:03 -0800, ret...@.gmail.com wrote:
>
> The PK is used as a final pointer by all other indexes on the table,
> so whatever the size difference between the int and average nvarchar
> (plus length indicator), may be multiplied, and makes other indexes
> somewhat less dense, which is in general a bad thing.
> J.
|||An int occupies 4 bytes, while nvarchar occupies 2 bytes per character. An
int goes from -2billion to +2 billion (ish) so smallint might be more
appropriate (2 bytes and +/- 32k ish). See
http://www.databasejournal.com/features/mssql/article.phpr/2212141 for exact
sizes. The reason I'm talking about the space occupied, is that this is one
crucial factor in determining the datatype - fewer pages to search = faster
searches. What I find confusing though is that these datatypes are not
really comparable as they'll store different kinds of information, and
perhaps you are questioning whether to use a surrogate key or a key with
business meaning, which is a different discussion and one you'll find many
threads on in this and the programming newsgroup.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||On Feb 8, 10:22 am, "Paul Ibison" <Paul.Ibi...@.Pygmalion.Com> wrote:
> An int occupies 4 bytes, while nvarchar occupies 2 bytes per character. An
> int goes from -2billion to +2 billion (ish) so smallint might be more
> appropriate (2 bytes and +/- 32k ish). Seehttp://www.databasejournal.com/features/mssql/article.phpr/2212141for exact
> sizes. The reason I'm talking about the space occupied, is that this is one
> crucial factor in determining the datatype - fewer pages to search = faster
> searches. What I find confusing though is that these datatypes are not
> really comparable as they'll store different kinds of information, and
> perhaps you are questioning whether to use a surrogate key or a key with
> business meaning, which is a different discussion and one you'll find many
> threads on in this and the programming newsgroup.
> Cheers,
> Paul Ibison SQL Server MVP,www.replicationanswers.com
Hi Paul,
In one of the systems I'm reviewing, most of the tables have nvarchar
data types because it's "comfortable" for programming and reports. I
want to convince them in the best way, that this move has problems and
might be very expensive to fix in the future.
|||OK - then if the column doesn't contain any 'meaningful' business data and
is just used to identify the records, I'd recommend using the appropriate
whole-number datatype. As a programmer I find them just as easy to use as
nvarchars. A lot of DBAs use surrogate PK keys with identity columns which
has other benefits - no need to design an algorithm for separate values.
Cheers,
Paul Ibison SQL Server MVP,www.replicationanswers.com
|||> The PK is used as a final pointer by all other indexes on the table,
Not really - it is the clustered index key that is used for bookmark
lookups. The clustered index is not necessarily the primary key.
Hope this helps.
Dan Guzman
SQL Server MVP
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:4pdls2125tpm4oln46ijq7lil8ht9i611b@.4ax.com...
> On 7 Feb 2007 21:30:03 -0800, retima@.gmail.com wrote:
>
> The PK is used as a final pointer by all other indexes on the table,
> so whatever the size difference between the int and average nvarchar
> (plus length indicator), may be multiplied, and makes other indexes
> somewhat less dense, which is in general a bad thing.
> J.
>
|||On 8 Feb 2007 00:10:19 -0800, "Adi" <adicohn@.hotmail.com> wrote:

> I think that you mixed up primary key and clustered index.
Ooops, yes I did.
Sorry for any confusion.
To try the original question again, if the PK is not clustered, just
being a PK, then the only impact is as any less dense index is that
much less efficient, and I suppose its use (like any index's use) by
FK's, would also consume that many more bytes of space.
I'll happily use char(8) or even varchar(24) as PKs, heck, I'll use
compound PKs even longer, as required, unless it's some hugely active
system where you have to squeeze out the last bits of performance.
And of course, most databases I walk into, are already using clustered
identity ints (or bigints more recently) as PKs on most tables ...
hence my sloppy answer last night.
J.

> None
>clustered index are using the clustered index's keys as a pointer to
>the data. Of course if the primary key is clustered then all the none
>clustered index will use the PK as the pointer to the data, but many
>times there is another clustered index (and sometimes there is no
>clustered index at all).
>Adi
>On Feb 8, 7:39 am, JXStern <JXSternChange...@.gte.net> wrote:
>

primary key data type

Where is the biggest difference in performance between a 'int' ID and
a 'nvarchar' ID on big tables?On 7 Feb 2007 21:30:03 -0800, retima@.gmail.com wrote:

>Where is the biggest difference in performance between a 'int' ID and
>a 'nvarchar' ID on big tables?
The PK is used as a final pointer by all other indexes on the table,
so whatever the size difference between the int and average nvarchar
(plus length indicator), may be multiplied, and makes other indexes
somewhat less dense, which is in general a bad thing.
J.|||On Feb 8, 7:39 am, JXStern <JXSternChange...@.gte.net> wrote:
> On 7 Feb 2007 21:30:03 -0800, ret...@.gmail.com wrote:
>
> The PK is used as a final pointer by all other indexes on the table,
> so whatever the size difference between the int and average nvarchar
> (plus length indicator), may be multiplied, and makes other indexes
> somewhat less dense, which is in general a bad thing.
> J.
thanks J.
Can you direct me to an example which is more specific?
(a certain query that would prove it with numbers)|||Yaniv,shalom
http://www.sql-server-performance.com/datatypes.asp
<yaniv.harpaz@.gmail.com> wrote in message
news:1170919960.359095.315430@.p10g2000cwp.googlegroups.com...
> On Feb 8, 7:39 am, JXStern <JXSternChange...@.gte.net> wrote:
> thanks J.
> Can you direct me to an example which is more specific?
> (a certain query that would prove it with numbers)
>|||I think that you mixed up primary key and clustered index. None
clustered index are using the clustered index's keys as a pointer to
the data. Of course if the primary key is clustered then all the none
clustered index will use the PK as the pointer to the data, but many
times there is another clustered index (and sometimes there is no
clustered index at all).
Adi
On Feb 8, 7:39 am, JXStern <JXSternChange...@.gte.net> wrote:
> On 7 Feb 2007 21:30:03 -0800, ret...@.gmail.com wrote:
>
> The PK is used as a final pointer by all other indexes on the table,
> so whatever the size difference between the int and average nvarchar
> (plus length indicator), may be multiplied, and makes other indexes
> somewhat less dense, which is in general a bad thing.
> J.|||An int occupies 4 bytes, while nvarchar occupies 2 bytes per character. An
int goes from -2billion to +2 billion (ish) so smallint might be more
appropriate (2 bytes and +/- 32k ish). See
http://www.databasejournal.com/feat...le.phpr/2212141 for exact
sizes. The reason I'm talking about the space occupied, is that this is one
crucial factor in determining the datatype - fewer pages to search = faster
searches. What I find confusing though is that these datatypes are not
really comparable as they'll store different kinds of information, and
perhaps you are questioning whether to use a surrogate key or a key with
business meaning, which is a different discussion and one you'll find many
threads on in this and the programming newsgroup.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||On Feb 8, 10:22 am, "Paul Ibison" <Paul.Ibi...@.Pygmalion.Com> wrote:
> An int occupies 4 bytes, while nvarchar occupies 2 bytes per character. An
> int goes from -2billion to +2 billion (ish) so smallint might be more
> appropriate (2 bytes and +/- 32k ish). Seehttp://www.databasejournal.com/f
eatures/mssql/article.phpr/2212141for exact
> sizes. The reason I'm talking about the space occupied, is that this is on
e
> crucial factor in determining the datatype - fewer pages to search = faste
r
> searches. What I find confusing though is that these datatypes are not
> really comparable as they'll store different kinds of information, and
> perhaps you are questioning whether to use a surrogate key or a key with
> business meaning, which is a different discussion and one you'll find many
> threads on in this and the programming newsgroup.
> Cheers,
> Paul Ibison SQL Server MVP,www.replicationanswers.com
Hi Paul,
In one of the systems I'm reviewing, most of the tables have nvarchar
data types because it's "comfortable" for programming and reports. I
want to convince them in the best way, that this move has problems and
might be very expensive to fix in the future.|||OK - then if the column doesn't contain any 'meaningful' business data and
is just used to identify the records, I'd recommend using the appropriate
whole-number datatype. As a programmer I find them just as easy to use as
nvarchars. A lot of DBAs use surrogate PK keys with identity columns which
has other benefits - no need to design an algorithm for separate values.
Cheers,
Paul Ibison SQL Server MVP,www.replicationanswers.com|||> The PK is used as a final pointer by all other indexes on the table,
Not really - it is the clustered index key that is used for bookmark
lookups. The clustered index is not necessarily the primary key.
Hope this helps.
Dan Guzman
SQL Server MVP
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:4pdls2125tpm4oln46ijq7lil8ht9i611b@.
4ax.com...
> On 7 Feb 2007 21:30:03 -0800, retima@.gmail.com wrote:
>
> The PK is used as a final pointer by all other indexes on the table,
> so whatever the size difference between the int and average nvarchar
> (plus length indicator), may be multiplied, and makes other indexes
> somewhat less dense, which is in general a bad thing.
> J.
>|||On 8 Feb 2007 00:10:19 -0800, "Adi" <adicohn@.hotmail.com> wrote:

> I think that you mixed up primary key and clustered index.
Ooops, yes I did.
Sorry for any confusion.
To try the original question again, if the PK is not clustered, just
being a PK, then the only impact is as any less dense index is that
much less efficient, and I suppose its use (like any index's use) by
FK's, would also consume that many more bytes of space.
I'll happily use char(8) or even varchar(24) as PKs, heck, I'll use
compound PKs even longer, as required, unless it's some hugely active
system where you have to squeeze out the last bits of performance.
And of course, most databases I walk into, are already using clustered
identity ints (or bigints more recently) as PKs on most tables ...
hence my sloppy answer last night.
J.

> None
>clustered index are using the clustered index's keys as a pointer to
>the data. Of course if the primary key is clustered then all the none
>clustered index will use the PK as the pointer to the data, but many
>times there is another clustered index (and sometimes there is no
>clustered index at all).
>Adi
>On Feb 8, 7:39 am, JXStern <JXSternChange...@.gte.net> wrote:
>

primary key data type

Where is the biggest difference in performance between a 'int' ID and
a 'nvarchar' ID on big tables?On 7 Feb 2007 21:30:03 -0800, retima@.gmail.com wrote:
>Where is the biggest difference in performance between a 'int' ID and
>a 'nvarchar' ID on big tables?
The PK is used as a final pointer by all other indexes on the table,
so whatever the size difference between the int and average nvarchar
(plus length indicator), may be multiplied, and makes other indexes
somewhat less dense, which is in general a bad thing.
J.|||On Feb 8, 7:39 am, JXStern <JXSternChange...@.gte.net> wrote:
> On 7 Feb 2007 21:30:03 -0800, ret...@.gmail.com wrote:
> >Where is the biggest difference in performance between a 'int' ID and
> >a 'nvarchar' ID on big tables?
> The PK is used as a final pointer by all other indexes on the table,
> so whatever the size difference between the int and average nvarchar
> (plus length indicator), may be multiplied, and makes other indexes
> somewhat less dense, which is in general a bad thing.
> J.
thanks J.
Can you direct me to an example which is more specific?
(a certain query that would prove it with numbers)|||I think that you mixed up primary key and clustered index. None
clustered index are using the clustered index's keys as a pointer to
the data. Of course if the primary key is clustered then all the none
clustered index will use the PK as the pointer to the data, but many
times there is another clustered index (and sometimes there is no
clustered index at all).
Adi
On Feb 8, 7:39 am, JXStern <JXSternChange...@.gte.net> wrote:
> On 7 Feb 2007 21:30:03 -0800, ret...@.gmail.com wrote:
> >Where is the biggest difference in performance between a 'int' ID and
> >a 'nvarchar' ID on big tables?
> The PK is used as a final pointer by all other indexes on the table,
> so whatever the size difference between the int and average nvarchar
> (plus length indicator), may be multiplied, and makes other indexes
> somewhat less dense, which is in general a bad thing.
> J.|||Yaniv,shalom
http://www.sql-server-performance.com/datatypes.asp
<yaniv.harpaz@.gmail.com> wrote in message
news:1170919960.359095.315430@.p10g2000cwp.googlegroups.com...
> On Feb 8, 7:39 am, JXStern <JXSternChange...@.gte.net> wrote:
>> On 7 Feb 2007 21:30:03 -0800, ret...@.gmail.com wrote:
>> >Where is the biggest difference in performance between a 'int' ID and
>> >a 'nvarchar' ID on big tables?
>> The PK is used as a final pointer by all other indexes on the table,
>> so whatever the size difference between the int and average nvarchar
>> (plus length indicator), may be multiplied, and makes other indexes
>> somewhat less dense, which is in general a bad thing.
>> J.
> thanks J.
> Can you direct me to an example which is more specific?
> (a certain query that would prove it with numbers)
>|||An int occupies 4 bytes, while nvarchar occupies 2 bytes per character. An
int goes from -2billion to +2 billion (ish) so smallint might be more
appropriate (2 bytes and +/- 32k ish). See
http://www.databasejournal.com/features/mssql/article.phpr/2212141 for exact
sizes. The reason I'm talking about the space occupied, is that this is one
crucial factor in determining the datatype - fewer pages to search = faster
searches. What I find confusing though is that these datatypes are not
really comparable as they'll store different kinds of information, and
perhaps you are questioning whether to use a surrogate key or a key with
business meaning, which is a different discussion and one you'll find many
threads on in this and the programming newsgroup.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||On Feb 8, 10:22 am, "Paul Ibison" <Paul.Ibi...@.Pygmalion.Com> wrote:
> An int occupies 4 bytes, while nvarchar occupies 2 bytes per character. An
> int goes from -2billion to +2 billion (ish) so smallint might be more
> appropriate (2 bytes and +/- 32k ish). Seehttp://www.databasejournal.com/features/mssql/article.phpr/2212141for exact
> sizes. The reason I'm talking about the space occupied, is that this is one
> crucial factor in determining the datatype - fewer pages to search = faster
> searches. What I find confusing though is that these datatypes are not
> really comparable as they'll store different kinds of information, and
> perhaps you are questioning whether to use a surrogate key or a key with
> business meaning, which is a different discussion and one you'll find many
> threads on in this and the programming newsgroup.
> Cheers,
> Paul Ibison SQL Server MVP,www.replicationanswers.com
Hi Paul,
In one of the systems I'm reviewing, most of the tables have nvarchar
data types because it's "comfortable" for programming and reports. I
want to convince them in the best way, that this move has problems and
might be very expensive to fix in the future.|||OK - then if the column doesn't contain any 'meaningful' business data and
is just used to identify the records, I'd recommend using the appropriate
whole-number datatype. As a programmer I find them just as easy to use as
nvarchars. A lot of DBAs use surrogate PK keys with identity columns which
has other benefits - no need to design an algorithm for separate values.
Cheers,
Paul Ibison SQL Server MVP,www.replicationanswers.com|||> The PK is used as a final pointer by all other indexes on the table,
Not really - it is the clustered index key that is used for bookmark
lookups. The clustered index is not necessarily the primary key.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:4pdls2125tpm4oln46ijq7lil8ht9i611b@.4ax.com...
> On 7 Feb 2007 21:30:03 -0800, retima@.gmail.com wrote:
>>Where is the biggest difference in performance between a 'int' ID and
>>a 'nvarchar' ID on big tables?
> The PK is used as a final pointer by all other indexes on the table,
> so whatever the size difference between the int and average nvarchar
> (plus length indicator), may be multiplied, and makes other indexes
> somewhat less dense, which is in general a bad thing.
> J.
>|||On 8 Feb 2007 00:10:19 -0800, "Adi" <adicohn@.hotmail.com> wrote:
> I think that you mixed up primary key and clustered index.
Ooops, yes I did.
Sorry for any confusion.
To try the original question again, if the PK is not clustered, just
being a PK, then the only impact is as any less dense index is that
much less efficient, and I suppose its use (like any index's use) by
FK's, would also consume that many more bytes of space.
I'll happily use char(8) or even varchar(24) as PKs, heck, I'll use
compound PKs even longer, as required, unless it's some hugely active
system where you have to squeeze out the last bits of performance.
And of course, most databases I walk into, are already using clustered
identity ints (or bigints more recently) as PKs on most tables ...
hence my sloppy answer last night.
J.
> None
>clustered index are using the clustered index's keys as a pointer to
>the data. Of course if the primary key is clustered then all the none
>clustered index will use the PK as the pointer to the data, but many
>times there is another clustered index (and sometimes there is no
>clustered index at all).
>Adi
>On Feb 8, 7:39 am, JXStern <JXSternChange...@.gte.net> wrote:
>> On 7 Feb 2007 21:30:03 -0800, ret...@.gmail.com wrote:
>> >Where is the biggest difference in performance between a 'int' ID and
>> >a 'nvarchar' ID on big tables?
>> The PK is used as a final pointer by all other indexes on the table,
>> so whatever the size difference between the int and average nvarchar
>> (plus length indicator), may be multiplied, and makes other indexes
>> somewhat less dense, which is in general a bad thing.
>> J.
>|||On 8 Feb 2007 01:35:13 -0800, retima@.gmail.com wrote:
>In one of the systems I'm reviewing, most of the tables have nvarchar
>data types because it's "comfortable" for programming and reports. I
>want to convince them in the best way, that this move has problems and
>might be very expensive to fix in the future.
I'm rather sympathetic to that "comfortable" approach, it's often just
a classic database design, too. And ask Celko about reengineering a
database just to get rid of natural PKs (chuckle). Out of curiosity,
do they cluster these PKs?
If they're less than, oh, nvarchar(20), and hopefully average rather
shorter than that, I probably wouldn't worry about it, unless the
databases are huge, the transaction rate very high, or the servers
maxed out on RAM and very short of performance.
With a modern, moderate server having four processors (two dual-core),
4gb of RAM, and SCSI-level IO throughput, a solidly designed database
system can deliver a *lot* of performance, even if the PKs are not
minimal.
And the programmer's comfort can be a major factor on some systems,
too. Though I suppose it's been years since I've used that particular
argument, I've just surrendered to the surrogates.
Josh

Primary Key Auto Increment

Hi, my table's primary key is int(4), auto increment with the seed 1, if I got 3 data inside, means the id should be 1, 2, 3

id fname lname

==============================

1 Stephen Saw

2 Ali Baba

3 ABC DEF

suppose new record will have id of 4. Let say I deleted data with id 3, before adding new record, the new id will be 4 instead of 3, how can I made the new id to become 3 instead of 4 after delete the id 3's data. Is it possible to insert new record with id of 2 if I delete the Ali's record, but it will continue with id 4, or is there any method for me to reorder the id? Thanks in advanced.

u can do it , but its not advisable, as it will require extra processing...

still...u can either write a script to find the smallest possible id which is free... and insert at that...

or..dont actually delete the key..mark it as deleted....say update the name as deleted..then find the smallest id with name 'deleted' and add the new record there using update instead of insert....

these r a few turnarounds..u may find more.....

|||

If it is only for last row then you can use the following query,

create table mytable
(
id int primary key identity(1,1),
name varchar(100)
)

Insert Into mytable values('Test Data 1');
Insert Into mytable values('Test Data 2');
Insert Into mytable values('Test Data 3');
Insert Into mytable values('Test Data 4');

Delete From mytable Where id=4

IF IDENT_CURRENT('mytable') = (Select max(id) from mytable)
Begin
SET IDENTITY_INSERT mytable OFF;
Insert INto mytable values('Inserted Implicit');
End
else
Begin
SET IDENTITY_INSERT mytable ON;
Insert INto mytable(Id,name) select max(id) + IDENT_INCR('mytable'),'Inserted Explict' From mytable;
SET IDENTITY_INSERT mytable OFF;
End

|||As has been said, we could write you some clever script that does this. There will be a lot to it.

AND

If something goes wrong somewhere your going to have a nightmare. What if an order was placed by someone with ID of 4, then you wanted to delete it and 5 became 4... you would have to accound for moving the ID of the orders too or your data would be very washy.

To be honest I would highly reccommend against it.

Let us know how you wish us to progress this further.|||You DON'T.

The identity is a unique identifier, NOT a sequential number.

Why do you care?

Primary key

I have order table with ORDER_ID [int] IDENTITY (1, 1) NOT NULL ,
as Primary key and by default also clustered index.
I use this ID in my INNER JOINS with order items to connect them.
I have also orderDate column in my order table, which is datetime field..
A lot of my queries include search or order condition by date, for example,
simplified one:
SELECT * FROM ORDERS o INNER JOIN ORDER_ITEMS i ON o.ORDER_ID=i.ORDER_ID
WHERE o.orderDate>='20050212' AND o.orderDate<'20050228' ORDER BY
o.orderDate
Now I would like to speed up the execution of this query.
I have 3 options:
1: orderDate as Primary key (it will be clustered index) and ORDER_ID not in
any index
2: orderDate+ORDER_ID as Primary key
3:orderDate as Primary key and nonclustered index on ORDER_ID column
Now, date will be in clustered index and select will be much faster, because
date is usually in where and order parts of query.
Order_ID is usually only in join conditions, so, I think it's not so
important to be as clustered index - if, than it should be append to date
column and both will present clustered index.
What is yours opinion?
Any suggestions, expirience with that?
Thank you,
SimonLet's start with basics. An IDENTITY columns can not be a key by
definition. It is not an attribute in the data model, but an exposed
physical locator for the physical storage of the data. It cannot be
validated or verified.
Newbies use it because they don't know what a key is and this looks
like a pointer or record number. Next, rows are not records and columns
are not fields.
It looks like you use date *ranges*, so a clustered index on the date
column would help quite a bit.
But order_id sounds like the natural key for an Orders table (once you
make it a real data type, add a check digit or validation rule, etc.).
There are primary indexes -- those required to enforce business rules
(UNIQUE, PRIMARY KEY) and secondary indexes -- those added for
performance. You have one of each.
As an aside, other products like Sybase will see the PK-FK relationship
between Orders and OrderItems and build a pointer structure that will
"pre-join" them and things will much faster.
In SQL Server you currently have to add indexing to the referencing
table on your own. The original design of SQL Server was done by
people who mapped tables to single files rather than viewing the schema
as a whole. This is why I keep beating people up about confusing
files/records/fields with tables/rows/columns; a bad mental model leads
to bad code.|||Everything Joe Celko said. I'll add a suggestion for generating your order
number (which you *should* be using as a PK). Your order number should be a
"smart key" -- ie, it will actually convey info about your order, unlike
Order #1702. Here's an exmple technique.
Find the magnitude of average orders per day and add one. If you see 40-70
orders, then your magnitude will be three.
You have two options from here. I'd go by your customer/order ratio. If it
is above 0.1 (which I would guess), then don't cater to only a handful of
customers, so a date is better to embed in the order number. Otherwise,
you'll want to embed the customer number.
Normal Ratio: Y{1,2}DDD-S+
Low Ration: C+-S+
Let's explore ...
Y is for year. You can go one or two digits (5 for 2005 or 05 for 2005). It
all depends on how long you need to remember orders.
DDD is day of year. By doing this instead of MMDD, you save a digit. Not
for disk space, for short term memory.
S+ is the daily sequence number. The extra magnitude is for growth.
C+ is your customer number
- helps to split the number (mentally) and make it easier to remember.
Now when you cluster your PK (Order Num), it's ordered by the info you need.
And you can always index the other (Date).
-- Alex Papadimoulis
"simon" wrote:

> I have order table with ORDER_ID [int] IDENTITY (1, 1) NOT NULL ,
> as Primary key and by default also clustered index.
> I use this ID in my INNER JOINS with order items to connect them.
> I have also orderDate column in my order table, which is datetime field..
> A lot of my queries include search or order condition by date, for example
,
> simplified one:
> SELECT * FROM ORDERS o INNER JOIN ORDER_ITEMS i ON o.ORDER_ID=i.ORDER_ID
> WHERE o.orderDate>='20050212' AND o.orderDate<'20050228' ORDER BY
> o.orderDate
> Now I would like to speed up the execution of this query.
> I have 3 options:
> 1: orderDate as Primary key (it will be clustered index) and ORDER_ID not
in
> any index
> 2: orderDate+ORDER_ID as Primary key
> 3:orderDate as Primary key and nonclustered index on ORDER_ID column
> Now, date will be in clustered index and select will be much faster, becau
se
> date is usually in where and order parts of query.
> Order_ID is usually only in join conditions, so, I think it's not so
> important to be as clustered index - if, than it should be append to date
> column and both will present clustered index.
> What is yours opinion?
> Any suggestions, expirience with that?
> Thank you,
> Simon
>
>
>|||> Everything Joe Celko said. I'll add a suggestion for generating your order
> number (which you *should* be using as a PK). Your order number should be
> a
I don't know. I agree with your order number, I just still like to use
identity values for primary keys, with a unique key on things like this
order_id. There are quite a few positive reasons to do so (performance
being one, and development pattern simplification being another) and really,
as long as you have a natural key, it is an exceptionally useful way to have
a non-changing key. And there is never a need to modify a primary key,
which is usually a real pain.
I have never heard an argument against identities that made enough sense to
balance out the ease of use. I certainly will never agree that they are
"exposed physical locators" but I will agree that they cannot be "validated
or verified" as Joe Celko has said. If they were physical locators they
would change as the physical storage of a row was moved. They aren't. They
are not a value I would share with the user, but a convienience in
development that keeps key size managable.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Alex Papadimoulis" <alexRemovePi@.pa3.14padimoulis.com> wrote in message
news:0C0B35F4-07FC-416F-BA3E-C398C4D30525@.microsoft.com...
> Everything Joe Celko said. I'll add a suggestion for generating your order
> number (which you *should* be using as a PK). Your order number should be
> a
> "smart key" -- ie, it will actually convey info about your order, unlike
> Order #1702. Here's an exmple technique.
> Find the magnitude of average orders per day and add one. If you see 40-70
> orders, then your magnitude will be three.
> You have two options from here. I'd go by your customer/order ratio. If it
> is above 0.1 (which I would guess), then don't cater to only a handful of
> customers, so a date is better to embed in the order number. Otherwise,
> you'll want to embed the customer number.
> Normal Ratio: Y{1,2}DDD-S+
> Low Ration: C+-S+
> Let's explore ...
> Y is for year. You can go one or two digits (5 for 2005 or 05 for 2005).
> It
> all depends on how long you need to remember orders.
> DDD is day of year. By doing this instead of MMDD, you save a digit. Not
> for disk space, for short term memory.
> S+ is the daily sequence number. The extra magnitude is for growth.
> C+ is your customer number
> - helps to split the number (mentally) and make it easier to remember.
> Now when you cluster your PK (Order Num), it's ordered by the info you
> need.
> And you can always index the other (Date).
> -- Alex Papadimoulis
> "simon" wrote:
>|||On Fri, 4 Mar 2005 10:54:26 -0600, Louis Davidson wrote:

> I certainly will never agree that they are
> "exposed physical locators" but I will agree that they cannot be "validate
d
> or verified" as Joe Celko has said. If they were physical locators they
> would change as the physical storage of a row was moved. They aren't.
Mr. Celko's use of the word "physical" is still a higher level than what
most people think of as physical. It's higher than magnetic spins on the
hard drive; higher than sectors on the hard drive; higher than bytes in the
file on the filesystem; higher even than the structure inside a DAT file.
Anything that can't be ported from one platform to the next strictly with
SQL statements is "physical" in Mr. Celko's view, because it's part of the
implementation that might change in the next release of the software. Since
the Identity() attribute of a column is specific to MS SQL Server, and
requires internal code to run at the time of insert, it's physical in that
sense. And, since the identity value may be different depending on the
order of inserts (think of an INSERT INTO tbl1 SELECT blah FROM tbl2 ...
the query optimizer may reorder however it likes), it really has nothing
whatsoever to do with the values in the row (or as Mr. Celko would want me
to think, nothing to do with the actual identity of the entity that the
table represents).
As a surrogate key, yes, they're awfully convenient. But I've found that
when I take the trouble to use real keys in my schema, all my code ends up
simplified, not complexified.|||Thanks very much Ross, for your translation of what Joe means when he says
'physical'. It drives me up the wall every time I read a message from him
telling someone that an IDENTITY is a physical locator for the physical
storage. I don't think Joe understands anything about SQL Server real
physical storage. But now knowing that Joe means something entirely
different when he uses that term, I will stop pulling my hair out. But we
will have to be ever vigilant to make sure new users know that when used by
Joe, 'physical' does not mean what they think it means.
Thanks again...
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Ross Presser" <rpresser@.imtek.com> wrote in message
news:1xbcau9ino4a7$.dlg@.rpresser.invalid...
> On Fri, 4 Mar 2005 10:54:26 -0600, Louis Davidson wrote:
>
> Mr. Celko's use of the word "physical" is still a higher level than what
> most people think of as physical. It's higher than magnetic spins on the
> hard drive; higher than sectors on the hard drive; higher than bytes in
> the
> file on the filesystem; higher even than the structure inside a DAT file.
> Anything that can't be ported from one platform to the next strictly with
> SQL statements is "physical" in Mr. Celko's view, because it's part of the
> implementation that might change in the next release of the software.
> Since
> the Identity() attribute of a column is specific to MS SQL Server, and
> requires internal code to run at the time of insert, it's physical in that
> sense. And, since the identity value may be different depending on the
> order of inserts (think of an INSERT INTO tbl1 SELECT blah FROM tbl2 ...
> the query optimizer may reorder however it likes), it really has nothing
> whatsoever to do with the values in the row (or as Mr. Celko would want me
> to think, nothing to do with the actual identity of the entity that the
> table represents).
> As a surrogate key, yes, they're awfully convenient. But I've found that
> when I take the trouble to use real keys in my schema, all my code ends up
> simplified, not complexified.|||>> I'll add a suggestion for generating your order number (which you
Can you explain how and why it is beneficial to use a "smart key" for an
identifier? Are you aware of any drawbacks of using such "smart" or
intelligent keys?
Anith|||>> But we will have to be ever vigilant to make sure new users know that
Then OTOH, we will have to be aware that the ones using the terms "physical
table", "physical row", "physical column" etc do not mean what they think it
means either :-)
Anith|||Anith,
According to some (such as Louis, who replied earlier), the draw backs are:
> performance
> storage size
> they can change
But let's think about each of those. Are they "real" problems?
Performance. Did you know, your app can shave maybe 30ns if you forgo
database technology altogether. And loops (for, while, etc) -- you can easil
y
save a few clock cycles by not using them. Never accept "performance" as a
reason for doing something unless there are real world data to back this
claim up. I could make silly performance articles about AutoID as well -- th
e
system has to take extra cycles to generate the ID, check that you don't try
to insert it, etc.
Storage Size. The key I suggested was 8 bytes (YDDDSSSS). This would allow
for 999 orders a day for 10 years. That's well over 3 million orders. If you
wanted to do the same with an auto ID, you'd need a bigint (8 bytes).
Whoops. Let's compare a smaller key (YDDDSS, 6 bytes) versus int (4 bytes).
Even if we max out the int (at 2.15 Million), we save a whopping 4.3 Million
bytes. How about we just delete "solitare" instead of worrying about this?
They can change. Oh this is my favorite. How many times has amazon.com told
you "dear customer, we're sorry, but your order number has changed from
21040204-a34 to 24030204-a34." Find me a case where your PK will change, and
I'll show you a poorly designed system.
AutoIDs should remain in MS Access. They're good for one thing -- whipping
together a quick and dirty database. The whole point of "Relational"
databases is to allow data to relate to other data by the data iteself (keys
)
instead of these artificial AutoIDs.
-- Alex Papadimoulis
"Anith Sen" wrote:

> Can you explain how and why it is beneficial to use a "smart key" for an
> identifier? Are you aware of any drawbacks of using such "smart" or
> intelligent keys?
> --
> Anith
>
>|||I also react to the "academic" view of Primary Keys... One argument that
always irritates me is the idea that Identitys are bad because they have no
relationship or connection to the entity in the row... (It is not an
attribute in the data model) But then I will see it argued by the same
individual, that Social Security Number, or CustomerNo, or PartNumber (all
constructed artificuially by a third party) IS an appropriate key! (... But
order_id sounds like the natural key...)
Any value that uniquely identifies a row, imho, is a suitable candidate for
a Key. That value Must be constructed... either from meaningful data, or
from non-meaningful data. If you choose to construct it from meaningful dat
a
(Attributes in the data model) Then you ALWAYS have the problem of picking a
n
attribute (or set of attributes) whose values are least likely to change -
AND the isssue of propagating changes when the real-world values of the
attributes for that row DO change, (And they always will - because nothing i
n
the real-world is 100% fixed, no matter what the academics think.)
If you choose a non-meaningful key, how it is constructed - whether you use
a Identity, or some arbitrary algorithm, doesn't really matter, as long as
you can guarantee uniqueness. And Identities do that quite nicely.
"Anith Sen" wrote:

> Then OTOH, we will have to be aware that the ones using the terms "physica
l
> table", "physical row", "physical column" etc do not mean what they think
it
> means either :-)
> --
> Anith
>
>

Primary Key

Hi,
I'm looking for a way to make a primary key control in a int column (in othe
r words the app will manage the primary key value).
What's the best way to make that in SQL Server 2005? Most important, there's
a patter that allow many DB systems (like Oracle, DB2,
Firebird...)?
Thanks
MaxIf you're doing it from CLR code, consider using SMO.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Max Andr Bndchen" <nospam@.microsoft.com> wrote in message
news:utIrWfrPGHA.312@.TK2MSFTNGP12.phx.gbl...
Hi,
I'm looking for a way to make a primary key control in a int column (in
other words the app will manage the primary key value).
What's the best way to make that in SQL Server 2005? Most important, there's
a patter that allow many DB systems (like Oracle, DB2,
Firebird...)?
Thanks
Max|||> I'm looking for a way to make a primary key control in a int column (in
> other words the app will manage the primary key value).
If the application will manage (calculate and populate?) the key values,
then all you must do is add the int column and then add the primary key
constraint.

> Most important, there's a patter that allow many DB systems (like Oracle,
> DB2, Firebird...)?
Could you clarify the above question?
"Max Andr Bndchen" <nospam@.microsoft.com> wrote in message
news:utIrWfrPGHA.312@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I'm looking for a way to make a primary key control in a int column (in
> other words the app will manage the primary key value).
> What's the best way to make that in SQL Server 2005? Most important,
> there's a patter that allow many DB systems (like Oracle, DB2,
> Firebird...)?
> Thanks
> Max
>

PRIMARY KEY

Is there any difference between defining a primary key this way:
personID int not null
PRIMARY KEY CLUSTERED(personID)
and this way:
CONSTRAINT PK_CP_personID PRIMARY KEY CLUSTERED(personID)
'DAC,
With the first you're going to end up with a system generated name for the
PRIMARY KEY that will not match your environment's naming convention.
HTH
Jerry
"DazedAndConfused" <AceMagoo61@.yahoo.com> wrote in message
news:e7aazttuFHA.3752@.TK2MSFTNGP09.phx.gbl...
> Is there any difference between defining a primary key this way:
> personID int not null
> PRIMARY KEY CLUSTERED(personID)
> and this way:
> CONSTRAINT PK_CP_personID PRIMARY KEY CLUSTERED(personID)
> '
>|||Well, in the second way you get to choose the name for the constraint.
The first syntax is called a 'column level constraint' and the second is
called a 'table level constraint'. The two you have shown here will behave
internally exactly the same way.
With a table level constraint, you can have a composite key; a column level
constraint is on a single column.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"DazedAndConfused" <AceMagoo61@.yahoo.com> wrote in message
news:e7aazttuFHA.3752@.TK2MSFTNGP09.phx.gbl...
> Is there any difference between defining a primary key this way:
> personID int not null
> PRIMARY KEY CLUSTERED(personID)
> and this way:
> CONSTRAINT PK_CP_personID PRIMARY KEY CLUSTERED(personID)
> '
>|||assuming that both are in valid statements, you get a randomly named key
in the 1st and a name of your choosing in the 2nd.
other than that, i don't believe so.
DazedAndConfused wrote:

>Is there any difference between defining a primary key this way:
>personID int not null
>PRIMARY KEY CLUSTERED(personID)
>and this way:
>CONSTRAINT PK_CP_personID PRIMARY KEY CLUSTERED(personID)
>'
>
>|||Yes. One has a system generated constraint name and the other has a user
specified constraint name. I prefer to use the user specified constraint
name because it makes it easier to coerce the optimizer to use the implicit
index for the primary key constraint--WITH(INDEX(PK_CP_personID))--and to
query sysobjects and sysindexes during troubleshooting.
"DazedAndConfused" <AceMagoo61@.yahoo.com> wrote in message
news:e7aazttuFHA.3752@.TK2MSFTNGP09.phx.gbl...
> Is there any difference between defining a primary key this way:
> personID int not null
> PRIMARY KEY CLUSTERED(personID)
> and this way:
> CONSTRAINT PK_CP_personID PRIMARY KEY CLUSTERED(personID)
> '
>|||Thank you,
When I go into entrerprise managers Diagram, under Indexs/Keys tab there is
a check box for Create Unique and two Radio buttons:
Constraint and Index
What is the difference between constraint and index?
How would I create an index and why?
"Trey Walpole" <treypoNOle@.comSPAMcast.net> wrote in message
news:eXKz5ztuFHA.728@.TK2MSFTNGP10.phx.gbl...
> assuming that both are in valid statements, you get a randomly named key
> in the 1st and a name of your choosing in the 2nd.
> other than that, i don't believe so.
> DazedAndConfused wrote:
>|||I think you can create foreign key relationships if you use a unique
constraint instead of a unique index, but I'm not absolutely certain about
that. From a performance standpoint, there is no difference, because a
unique constraint always creates a unique index.
"DazedAndConfused" <AceMagoo61@.yahoo.com> wrote in message
news:uHcGY$tuFHA.3676@.TK2MSFTNGP10.phx.gbl...
> Thank you,
> When I go into entrerprise managers Diagram, under Indexs/Keys tab there
is
> a check box for Create Unique and two Radio buttons:
> Constraint and Index
> What is the difference between constraint and index?
> How would I create an index and why?
>
> "Trey Walpole" <treypoNOle@.comSPAMcast.net> wrote in message
> news:eXKz5ztuFHA.728@.TK2MSFTNGP10.phx.gbl...
>|||A constraint is a logical construct. By defining a constraint you are
telling SQL Server how you want your data to behave, and how you want the
system to control it. A PK constrains the data to uniqueness, and
non-nullability. A unique constraint constrains the data to uniqueness.
Right now, SQL Server physically enforces your constraint requirements by
building an index, but theoretically, it could enforce the requirements in
another way.
An index is a physical construct that is primarily used for performance
reasons (but is also used to provide the enforcement of uniqueness
constraints). There are all kinds of reasons to build indexes, and you
should find a good book or site on SQL Server query tuning to get more
information about the design and use of indexes.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"DazedAndConfused" <AceMagoo61@.yahoo.com> wrote in message
news:uHcGY$tuFHA.3676@.TK2MSFTNGP10.phx.gbl...
> Thank you,
> When I go into entrerprise managers Diagram, under Indexs/Keys tab there
> is a check box for Create Unique and two Radio buttons:
> Constraint and Index
> What is the difference between constraint and index?
> How would I create an index and why?
>
> "Trey Walpole" <treypoNOle@.comSPAMcast.net> wrote in message
> news:eXKz5ztuFHA.728@.TK2MSFTNGP10.phx.gbl...
>|||fyi: an FK can use either
Brian Selzer wrote:

>I think you can create foreign key relationships if you use a unique
>constraint instead of a unique index, but I'm not absolutely certain about
>that.
>
>|||Right now I have a lot of tables that have a foreign key constraint in a
column named "updateby" that must have an entry in the "users" table in the
"userID" column. Does that mean I have indexes all over the place? Am I
creating a mess and a lot of overhead? I am trying to find out if you can go
too far with relationships and constraints.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:eS%23$$IuuFHA.3548@.tk2msftngp13.phx.gbl...
>A constraint is a logical construct. By defining a constraint you are
>telling SQL Server how you want your data to behave, and how you want the
>system to control it. A PK constrains the data to uniqueness, and
>non-nullability. A unique constraint constrains the data to uniqueness.
>Right now, SQL Server physically enforces your constraint requirements by
>building an index, but theoretically, it could enforce the requirements in
>another way.
> An index is a physical construct that is primarily used for performance
> reasons (but is also used to provide the enforcement of uniqueness
> constraints). There are all kinds of reasons to build indexes, and you
> should find a good book or site on SQL Server query tuning to get more
> information about the design and use of indexes.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "DazedAndConfused" <AceMagoo61@.yahoo.com> wrote in message
> news:uHcGY$tuFHA.3676@.TK2MSFTNGP10.phx.gbl...
>

Preventing ALL text SQL Injection by removing single-quotes ?

I need to ask the user for some string (not INT) input... to
build a T-SQL "find" query in SQL Server 2000.
I do *NOT* need the user to ever search for "'"... so I'm removing the
single-quote character entirely.

> s = Replace(s, "'", "")
Can someone give a working example where SQL INJECTION would still be
possible?Hi Susan
You may want to read:
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23
http://www.sommarskog.se/dynamic_sql.html#Security2
John
"Susan S via droptable.com" <forum@.droptable.com> wrote in message
news:50B826701CBE0@.droptable.com...
>I need to ask the user for some string (not INT) input... to
> build a T-SQL "find" query in SQL Server 2000.
> I do *NOT* need the user to ever search for "'"... so I'm removing the
> single-quote character entirely.
>
> Can someone give a working example where SQL INJECTION would still be
> possible?|||I can't find a single line of that article that *SHOWS* how a
string, fully stripped of all single-quotes... can be used for
Injection.
I'm not saying that it can't.
I just need someone to post the actual code.
Ever wonder why no one has the code?
Message posted via http://www.droptable.com|||Hi
You are assuming a certain business rule is applied which is probably
quite rare. You may be able to provide dropdowns if your values are so
specific and remove the need to type in anything!!
The parameterised query option is generic and can cater for all
sutuations, it should also be fast.
John|||John... that wasn't the question at all.
I *DO* have a need to search for text without containing any single-quotes.
(I couldn't possible have a dropdown box with 1000 different searches in it.
)
Why not simple say "I don't know" instead of answering a totally different
question?
John Bell wrote:
>Hi
>You are assuming a certain business rule is applied which is probably
>quite rare. You may be able to provide dropdowns if your values are so
>specific and remove the need to type in anything!!
>The parameterised query option is generic and can cater for all
>sutuations, it should also be fast.
>John
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...curity/200507/1|||"Susan S via droptable.com" <forum@.droptable.com> wrote in message
news:510817BAE9A30@.droptable.com...
> John... that wasn't the question at all.
> I *DO* have a need to search for text without containing any
> single-quotes.
> (I couldn't possible have a dropdown box with 1000 different searches in
> it.)
> Why not simple say "I don't know" instead of answering a totally different
> question?
You don't provide us with an example of your code that builds dynamic SQL so
we can only guess as to what might or might not work. Have you considered
char(39) being mixed in somehow?
But the larger question is, what is your aversion to parameterizing your
queries? If you were truly concerned about SQL injection, that's the route
you would go. Will you feel safe if nobody here can come up with an
example? Will that mean that it can't be done? You'd learn more hanging
out in hacker's circles, if that is your approach..
So, sorry if it offends you, and I'll admit that "I don't know" of a
specific example, but the real answer to your rhetorical question is:
parameterize, then you won't need to worry about it, there are no reasons it
can't be done, only excuses.
Good Luck,
Mark
[vbcol=seagreen]
> John Bell wrote:|||Well I started out the other day writing down a list from the sql server
perspective, but they all seem to start with the programmer making a
blunder. There are so many ways you can facilitate your site getting hacked
that it is really quite absurd. This is where a lot of the challenge exists.
If ever in the design of the system someone has said "It will never do such
and such" so you haven't coded defensively for the day you will have say
multiple sites on the one server (as an EG) then you have introduced time
bombs.
The most important thing about security is like so many things - expend your
effort where it will have most benefit.
I suggest you check your program code and ensure that no strings can
overflow buffers, that you use parameterised queries, that you check every
string not just for single quotes but also script and other HTML tags, for
code that can be evaluated to quotes, script, or html, that wherever
possible you remove as much need for free text entry as you can, do not
reflect errors verbatum back to the user - IE avoid replay attacks - do not
store data ever without sanitising it, do not show data with it being
santised (again), make everything as type safe as possible, normalise your
database properly, use maximum error and warning reporting when compiling,
use proper error handling, test the system thoroughly, try to break it, do
not accept warnings during compiles, and so on.
Add to that, use a DMZ, secure your web server, secure your database server,
enforce strong security... strong passwords,... I think you get the idea.
Now, look at your original question and ask yourself "was it responsible"?
Or do you think you can now check off the single quote character in
isolation? It is never a done task.
I suggest you re-read John's answer and references as a starter.
"Mark J. McGinty" <mmcginty@.spamfromyou.com> wrote in message
news:uX1WrSUhFHA.3256@.TK2MSFTNGP12.phx.gbl...
> "Susan S via droptable.com" <forum@.droptable.com> wrote in message
> news:510817BAE9A30@.droptable.com...
> You don't provide us with an example of your code that builds dynamic SQL
> so we can only guess as to what might or might not work. Have you
> considered char(39) being mixed in somehow?
> But the larger question is, what is your aversion to parameterizing your
> queries? If you were truly concerned about SQL injection, that's the
> route you would go. Will you feel safe if nobody here can come up with an
> example? Will that mean that it can't be done? You'd learn more hanging
> out in hacker's circles, if that is your approach..
> So, sorry if it offends you, and I'll admit that "I don't know" of a
> specific example, but the real answer to your rhetorical question is:
> parameterize, then you won't need to worry about it, there are no reasons
> it can't be done, only excuses.
>
> Good Luck,
> Mark
>
>
>
>|||Oh, of course, the biggest sin of them all - as Mark points out. Never use
dynamic SQL. You never need to for a correctly designed database.
"Mark J. McGinty" <mmcginty@.spamfromyou.com> wrote in message
news:uX1WrSUhFHA.3256@.TK2MSFTNGP12.phx.gbl...
> "Susan S via droptable.com" <forum@.droptable.com> wrote in message
> news:510817BAE9A30@.droptable.com...
> You don't provide us with an example of your code that builds dynamic SQL
> so we can only guess as to what might or might not work. Have you
> considered char(39) being mixed in somehow?
> But the larger question is, what is your aversion to parameterizing your
> queries? If you were truly concerned about SQL injection, that's the
> route you would go. Will you feel safe if nobody here can come up with an
> example? Will that mean that it can't be done? You'd learn more hanging
> out in hacker's circles, if that is your approach..
> So, sorry if it offends you, and I'll admit that "I don't know" of a
> specific example, but the real answer to your rhetorical question is:
> parameterize, then you won't need to worry about it, there are no reasons
> it can't be done, only excuses.
>
> Good Luck,
> Mark
>
>
>
>|||Wow.. it was just *ONE* simple request:
Post some *ACTUAL* code... that can cause SQL injection... where *ALL* singl
e
quote
characters have been removed from the string input by the user.
Seems like everyone is talking about eveything *OTHER* than that 1 simple
request.:

> Why not use another method instead?
> Why not fully secure your servers instead?
> Why not post your code?
> What are you using this for?
> You might needs users to enter single quotes some day.
> Why not write/use long parameterized methods instead?
> What if it's an INT, not a VarChar field?
> Why don't you go back and change 1000s of lines of code that you already w
rote?
> Let's talk about some totally different problems, instead of this one.
Sheezesss. That wasn't the question here. Here it is again:
s = GetUsersString() ' Get the input
s = Replace(s, "'", "") ' Remove all single-quotes
sql = "SELECT * FROM MyTable WHERE MyField='" & s & "'" ' Execute this
The Question:
In *ONE* line... without *ANY* explaination... what text would the user ente
r
as a value for "s",
that would break the above code sample?
So simple. No one can answer that? Or prove it can't be done?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...curity/200507/1|||"Susan S via droptable.com" <forum@.droptable.com> wrote in message
news:5131626AFB930@.droptable.com...
> Wow.. it was just *ONE* simple request:
Considering all the good and relevant (despite your views) advice you've
received, it's obvious that you just don't get it; so go ahead and feel as
though you've written safe, secure code. Clearly, your mind is made up. I
think you've insulted the NG with your narrow-minded replies quite enough
for one thread.
What you refuse to accept is that it doesn't matter whether or not anyone in
this or any NG can provide you with an example. It simply doesn't matter.
It proves/disproves nothing. It does not mean it can't be done. You want
someone to prove for you that it *can* be done, otherwise you'll assume it
can't? That is a reckless approach at best.
So you choose to believe what you want to believe -- ok, fine. You chose to
ignore widely accepted best practices and now you want to justify that
choice -- that's all up to you. But don't expect the NG to validate those
choices, because -- and you know this as well as we do -- there is a body of
knowledge that says your chosen methodology is not the most secure, PERIOD,
bottom line, end of story.
And given today's Internet climate, why anyone would choose to do something
any way other than the most secure defies all reason. That's what we're on
about. That's why we can't get behind your over-simplification of the
issues.
And now I suppose you'll post another rude, frustrated, closed-minded reply
because I didn't stick to a one line answer... whatever... best of luck to
you too.
-Mark