Please Help Just Upsized Access To Sql And Now Code Doesnt Work

here is the code i was using from a command button which basically used the first and last name fields to make a primary key called chart number. now it doesnt work and i belive it is in the dim rs as dao.recordset statement because this data now resides on an sql server. here is the code snippet.

Public Function chartlookup()
Dim db As DAO.Database <--believe to be the problem
Dim rs As DAO.Recordset <-- belive to be the problem
Dim SQL As String
Dim NewNum As Integer
Dim NeWChartNum As String

SQL = "Select max(Cint(Right([chartnumber],6))) As RecNum From tblpatientinfo WHERE UCase(Left([chartnumber],5)) = '" & UCase(Left([Forms]![fpatient]![lname], 3)) & UCase(Left([Forms]![fpatient]![fname], 2)) & "'"

Set db = CurrentDb() <-- -problem
Set rs = db.OpenRecordset(SQL) <--problem

If IsNull([Forms]![fpatient]![chartnumber]) = False Then GoTo 400

If rs.EOF = False Then
If IsNull(rs!RecNum) = False Then
NewNum = rs!RecNum + 1
Else
NewNum = 1
End If
End If
'If NewNum = 1 Then

[Forms]![fpatient]![chartnumber] = UCase(Left([Forms]![fpatient]![lname], 3)) & UCase(Left([Forms]![fpatient]![fname], 2)) & Format(NewNum, "000000")
'End If

400 End Function

basically if you typw in john smith this code would put smijo00001 into chartnumber field now i get a run-time error 91.What error are you receiving?

Also be advised that UCase is not supported in MS SQL, you should use UPPER, nor is Cint.

You have a few access specific casts there that are going to cause trouble for you.|||Change you dao to ado - using recordset and/or connection objects.|||Originally posted by Teddy
What error are you receiving?

Also be advised that UCase is not supported in MS SQL, you should use UPPER, nor is Cint.

You have a few access specific casts there that are going to cause trouble for you.

im receiving a run-time error 91 in the set rs= db.recordset(sql) statement but i know it coming from previous dim db as dao.database and dim rs as dao.recordset. i dont know how to convert this vb code to work with sql. maybe dim rs as sql.recordset and dim db as sql.database . please forgive my ignorance but i am new to sql and i dont know the syntax for sql. by the way thank you for the advice with ucase and cint not working . i tried a previous suggestion and turned dim rs as ado.recordset but the only option i get is adobe.recordset am i missing a reference .|||You need to use the ms ado 2.x library reference. Using adodb.recordset and adodb.connection - however, in your case you only need adodb.recordset.|||Hey
Who one saying that ODBC Connections not possible by DAO?

[QUOTE][SIZE=1]Originally posted by opcbriley
here is the code i was using from a command button which basically used the first and last name fields to make a primary key called chart number. now it doesnt work and i belive it is in the dim rs as dao.recordset statement because this data now resides on an sql server. here is the code snippet.

Public Function chartlookup()
Dim wrkODBC As Workspace
Dim conPubs As Connection
Dim rs As DAO.Recordset <-- belive to be the problem
Dim SQL As String
Dim NewNum As Integer
Dim NeWChartNum As String

SQL = "Select max(Cint(Right([chartnumber],6))) As RecNum From tblpatientinfo WHERE UCase(Left([chartnumber],5)) = '" & UCase(Left([Forms]![fpatient]![lname], 3)) & UCase(Left([Forms]![fpatient]![fname], 2)) & "'"

Set wrkODBC = CreateWorkspace("NewODBCWorkspace", "admin", "", dbUseODBC)
Set conPubs = wrkODBC.OpenConnection("costing", dbDriverNoPrompt)
.........

You may also open connection by using this code
remaining code will remain same
plz use it n reply me
thx|||You can use odbc - but don't - you are much better off using oledb (or use tds in .net). Better performance, resource usage and flexibility/functionality.|||yeah i know that OLEDB performance much better that dao but person asked abt DAO libraray n no one was there to reply by DAO libraray so I hv ginven reply by DAO library|||i appreciate all your help i was able to switch to ado connection i finally learned the syntax last night after about hour of reading. that code is working perfect now.

now on to find all my other problems that i have to change to be compatible with sql. i sure hope sql is worth all this trouble.

Playing with database mirroring

I started to play with mirroring with a fresh SQLServer 2005 but I can't get
it to work. This is the scenario:
server1.mydomain - Primary - Hosts "MyDatabase" - Primary Windows 2003
domain controller
server2.mydomain - Future mirror - Secondary Windows 2003 domain controller
server2.mydomain/secondary - a secondary instance for the witness (I don't
have a third machine to test)
1) I backup MyDatabase with SQL Management Studio (SMS).
2) I restore the database from the file (backup.bak) into server2 with
NORECOVERY (SMS shows "Recovering...")
3) From server1 I use the "Configure Security Wizard" to create endpoints in
ports 6000 for server1, 6000 for server2, 6001 for server2/secondary.
Endpoints are succesfully created.
4) All three servers use the same accout (MYDOMAIN\Administrator), used to
create the endpoints.
5) Then in SMS I click "Start Mirroring" and, after 10 sec. aprox, I get an
error "TCP://server1.mydomain.com:6000" could not be reached".
6) In server2, SMS now shows "In recovery" for Mydatabase, but mirroring is
not working. If I click "Start Mirroring" in server1 again, I get a "Neither
the partner nor the witness server instance for database MyDatabase is
available"
What is the problem? From Server1 I tried telneting server2:6000, and
server2:6001 and both connections are accepted. I also tried setting
encryption on/off but I can't fix the errors.
Any ideas? Thanks a lot!
Check the Surface Area configuration and make sure that each instance has
remote connections enabled.
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
"Kirsten" <noreply@.nospam.com> wrote in message
news:OXsjAkVFGHA.2036@.TK2MSFTNGP14.phx.gbl...
>I started to play with mirroring with a fresh SQLServer 2005 but I can't
>get it to work. This is the scenario:
> server1.mydomain - Primary - Hosts "MyDatabase" - Primary Windows 2003
> domain controller
> server2.mydomain - Future mirror - Secondary Windows 2003 domain
> controller
> server2.mydomain/secondary - a secondary instance for the witness (I don't
> have a third machine to test)
> 1) I backup MyDatabase with SQL Management Studio (SMS).
> 2) I restore the database from the file (backup.bak) into server2 with
> NORECOVERY (SMS shows "Recovering...")
> 3) From server1 I use the "Configure Security Wizard" to create endpoints
> in ports 6000 for server1, 6000 for server2, 6001 for server2/secondary.
> Endpoints are succesfully created.
> 4) All three servers use the same accout (MYDOMAIN\Administrator), used to
> create the endpoints.
> 5) Then in SMS I click "Start Mirroring" and, after 10 sec. aprox, I get
> an error "TCP://server1.mydomain.com:6000" could not be reached".
> 6) In server2, SMS now shows "In recovery" for Mydatabase, but mirroring
> is not working. If I click "Start Mirroring" in server1 again, I get a
> "Neither the partner nor the witness server instance for database
> MyDatabase is available"
> What is the problem? From Server1 I tried telneting server2:6000, and
> server2:6001 and both connections are accepted. I also tried setting
> encryption on/off but I can't fix the errors.
> Any ideas? Thanks a lot!
>
|||Remote connections are enabled. Other ideas?
Thanks a lot.
"Michael Hotek" <mike@.solidqualitylearning.com> wrote in message
news:%23N5dhRWFGHA.3920@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Check the Surface Area configuration and make sure that each instance has
> remote connections enabled.
> --
> Mike
> Mentor
> Solid Quality Learning
> http://www.solidqualitylearning.com
>
> "Kirsten" <noreply@.nospam.com> wrote in message
> news:OXsjAkVFGHA.2036@.TK2MSFTNGP14.phx.gbl...
don't[vbcol=seagreen]
endpoints[vbcol=seagreen]
to
>
|||Done. Something was wrong with DNS. I tried with TCP://192.168.0.xxx
instead of name and it worked.
Cheers.
"Kirsten" <email@.emailnospam.com> escribi en el mensaje
news:u4H4zBYFGHA.1676@.TK2MSFTNGP09.phx.gbl...
> Remote connections are enabled. Other ideas?
> Thanks a lot.
> "Michael Hotek" <mike@.solidqualitylearning.com> wrote in message
> news:%23N5dhRWFGHA.3920@.TK2MSFTNGP09.phx.gbl...
> don't
> endpoints
> to
>
sql

placing system table filegroup and Log files on same drive

Is it better to create a seperate filegroup for all user tables ? And if so
should all the system tables that belong to the Primary filegroup reside on
the same drive with the log file . I am using SQL 2000. Which system tables
get hit the most daily for any inserts,updates and deletes that may be
occuring on the server
ThanksDepends on the disk configuration.
I believe it is generally recommended to separate the log file from any data
files, i.e., separate physical disks... however, if you have a multiple
channel controller, with high thoroughput on each channel, you may be able
to get by w/ having the log and the data on the same logical disk.
I believe that all system tables must reside in PRIMARY, I'd have to verify.
If that is true, it doesn't matter what system tables get inserts, updates,
deletes. However, you are taking away some fault tolerance if you put the
system tables and the log files on the same physical disk.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%23AKD5sEXDHA.1872@.TK2MSFTNGP12.phx.gbl...
> Is it better to create a seperate filegroup for all user tables ? And if
so
> should all the system tables that belong to the Primary filegroup reside
on
> the same drive with the log file . I am using SQL 2000. Which system
tables
> get hit the most daily for any inserts,updates and deletes that may be
> occuring on the server
> Thanks
>|||You also generally want log files on RAID 1 or 10 as they are continuously
sequentially writing.
Thanks,
Greg H
"Martin Schmid" <martinschmid@.sbcglobal.net.nospam> wrote in message
news:eoDKm2EXDHA.2212@.TK2MSFTNGP09.phx.gbl...
> Depends on the disk configuration.
> I believe it is generally recommended to separate the log file from any
data
> files, i.e., separate physical disks... however, if you have a multiple
> channel controller, with high thoroughput on each channel, you may be able
> to get by w/ having the log and the data on the same logical disk.
> I believe that all system tables must reside in PRIMARY, I'd have to
verify.
> If that is true, it doesn't matter what system tables get inserts,
updates,
> deletes. However, you are taking away some fault tolerance if you put the
> system tables and the log files on the same physical disk.
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:%23AKD5sEXDHA.1872@.TK2MSFTNGP12.phx.gbl...
> > Is it better to create a seperate filegroup for all user tables ? And if
> so
> > should all the system tables that belong to the Primary filegroup reside
> on
> > the same drive with the log file . I am using SQL 2000. Which system
> tables
> > get hit the most daily for any inserts,updates and deletes that may be
> > occuring on the server
> >
> > Thanks
> >
> >
>|||Hassan
If you are using SQL Server 7, it is a good idea to put
your system files in a seperate filegroup. If you get a
corrupted user table, if your system tables are in a
seperate filegroup you can still perform a transaction log
backup prior to invoking DR (If you have DR of course). If
they are in the same filegroup you may not be able to.
This is no longer an issue in 2000.
Hope this helps
John

Placement of sys.conversation_endpoints and sys.transmission_queue

Is there any thought going into moving these two tables to a file group that we can control? Putting this in Primary with the rest of my system tables is quite problematic, and hinders my ability to manage space usage on my files. Traditionally, we didn't have to consider a primary file group that could grow to large proportions, but now with these two tables it can. If a large volume of messages gets sent through and the system can't keep up, then these tables and my primary file group will grow sometimes enormously.

There are no plans to support this in the near future.

Rushi

|||

Ouch! This will be real painful, as whenever messages queue up it chews up a large amount of space. I would think that we are not too disimilar from other high transaction systems, and really don't want our primary file groups to fill up with anything other than schema definition and not require a large amount of space. This may be something important to consider, as it requires us, your customers, to plan in a completely different way than we have had to previously.

Thanks!

|||

You can submit this feedback here:

http://connect.microsoft.com/SQLServer

Rushi

|||

I agree and have raised a suggestion on connect.

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=249443

|||

The connect item has been closed

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=249443

but no comment on why it has been closed.

|||Operator error, the issue should be still active.|||Thought as much, any view on a resolution?

Placement of sys.conversation_endpoints and sys.transmission_queue

Is there any thought going into moving these two tables to a file group that we can control? Putting this in Primary with the rest of my system tables is quite problematic, and hinders my ability to manage space usage on my files. Traditionally, we didn't have to consider a primary file group that could grow to large proportions, but now with these two tables it can. If a large volume of messages gets sent through and the system can't keep up, then these tables and my primary file group will grow sometimes enormously.

There are no plans to support this in the near future.

Rushi

|||

Ouch! This will be real painful, as whenever messages queue up it chews up a large amount of space. I would think that we are not too disimilar from other high transaction systems, and really don't want our primary file groups to fill up with anything other than schema definition and not require a large amount of space. This may be something important to consider, as it requires us, your customers, to plan in a completely different way than we have had to previously.

Thanks!

|||

You can submit this feedback here:

http://connect.microsoft.com/SQLServer

Rushi

|||

I agree and have raised a suggestion on connect.

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=249443

|||

The connect item has been closed

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=249443

but no comment on why it has been closed.

|||Operator error, the issue should be still active.|||Thought as much, any view on a resolution?

PK/UQ dumb question?

If there's a primary key constraint on a field, is it redundant to have a
unique key constraint on that same field?You may still want a unique constraint on the same table, for example
primary key on Customer_ID and unique key on SocialSecurityNumber, but there
is no need for a unique key constraint on the primary key column.
"Paul Pedersen" <no-reply@.swen.com> wrote in message
news:erqfdQXXFHA.1044@.TK2MSFTNGP10.phx.gbl...
> If there's a primary key constraint on a field, is it redundant to have a
> unique key constraint on that same field?
>
>|||Hi,
SQL Server will not allow to keep Primary key and Unique constraint on the
same field.
Thanks
Hari
SQL Server MVP
"Paul Pedersen" <no-reply@.swen.com> wrote in message
news:erqfdQXXFHA.1044@.TK2MSFTNGP10.phx.gbl...
> If there's a primary key constraint on a field, is it redundant to have a
> unique key constraint on that same field?
>
>|||Thanks!
"JT" <someone@.microsoft.com> wrote in message
news:uQpn1TXXFHA.3840@.tk2msftngp13.phx.gbl...
> You may still want a unique constraint on the same table, for example
> primary key on Customer_ID and unique key on SocialSecurityNumber, but
> there
> is no need for a unique key constraint on the primary key column.
> "Paul Pedersen" <no-reply@.swen.com> wrote in message
> news:erqfdQXXFHA.1044@.TK2MSFTNGP10.phx.gbl...
>|||Thanks for your reponse. It seems like it can be done, though.
create table test (testfld int not null)
alter table test add constraint pk_test primary key (testfld)
alter table test add constraint uq_test unique (testfld)
"Hari Pra" <hari_pra_k@.hotmail.com> wrote in message
news:OyZaPWXXFHA.3488@.tk2msftngp13.phx.gbl...
> Hi,
> SQL Server will not allow to keep Primary key and Unique constraint on the
> same field.
> Thanks
> Hari
> SQL Server MVP
>
> "Paul Pedersen" <no-reply@.swen.com> wrote in message
> news:erqfdQXXFHA.1044@.TK2MSFTNGP10.phx.gbl...
>|||Yes, it is redundant because a pk by definition have to be unique and not
null. When sql server check for integrity, it will give preference to pk.
use northwind
go
create table t1 (
c1 int not null,
constraint pk_t1 primary key (c1),
constraint u_t1_c1 unique (c1)
)
go
select
*
from
INFORMATION_SCHEMA.TABLE_CONSTRAINTS
where
table_name = 't1'
go
insert into t1 values(1)
go
-- this will give an error because of the pk constraint
insert into t1 values(1)
go
drop table t1
go
AMB
"Paul Pedersen" wrote:

> If there's a primary key constraint on a field, is it redundant to have a
> unique key constraint on that same field?
>
>
>|||I accepted. If there is primary key then the Unique constraint is redundant.
Thanks
Hari
"Paul Pedersen" <no-reply@.swen.com> wrote in message
news:OBibncXXFHA.2796@.TK2MSFTNGP09.phx.gbl...
> Thanks for your reponse. It seems like it can be done, though.
>
> create table test (testfld int not null)
> alter table test add constraint pk_test primary key (testfld)
> alter table test add constraint uq_test unique (testfld)
>
>
> "Hari Pra" <hari_pra_k@.hotmail.com> wrote in message
> news:OyZaPWXXFHA.3488@.tk2msftngp13.phx.gbl...
>|||Thank you for your help.
"Hari Pra" <hari_pra_k@.hotmail.com> wrote in message
news:eyTSShXXFHA.796@.TK2MSFTNGP09.phx.gbl...
>I accepted. If there is primary key then the Unique constraint is
>redundant.
> Thanks
> Hari
> "Paul Pedersen" <no-reply@.swen.com> wrote in message
> news:OBibncXXFHA.2796@.TK2MSFTNGP09.phx.gbl...
>|||Thank you.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:7EAC399A-D99C-4578-B34B-82967C169247@.microsoft.com...
> Yes, it is redundant because a pk by definition have to be unique and not
> null. When sql server check for integrity, it will give preference to pk.
> use northwind
> go
> create table t1 (
> c1 int not null,
> constraint pk_t1 primary key (c1),
> constraint u_t1_c1 unique (c1)
> )
> go
> select
> *
> from
> INFORMATION_SCHEMA.TABLE_CONSTRAINTS
> where
> table_name = 't1'
> go
> insert into t1 values(1)
> go
> -- this will give an error because of the pk constraint
> insert into t1 values(1)
> go
> drop table t1
> go
>
> AMB
>
> "Paul Pedersen" wrote:
>

PK vs. Unique clustered indexes

Are there any differences, performance related or other wise, between having
a clustered primary key vs. a clustered unique key?
I'm am trying to ready a SQL2K server for replication. Some of the tables
in database A don't have a primary key but they do have a cluster unique
index. The index is named pk_table_A. I guess what I'd like to do, is drop
this index and recreated it as a primary key with the same columns and name.
Would this pose any type of possible problem ?
TIA,
Billy
That should be fine (assuming you don't already have a PK on the table). The PK carries with it a
unique index (you have control over whether it is to be a clustered index or not).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"BillyDees" <BillyDees@.discussions.microsoft.com> wrote in message
news:1F6AAF9E-5E99-4776-ACF4-54B7FDB0EF89@.microsoft.com...
> Are there any differences, performance related or other wise, between having
> a clustered primary key vs. a clustered unique key?
> I'm am trying to ready a SQL2K server for replication. Some of the tables
> in database A don't have a primary key but they do have a cluster unique
> index. The index is named pk_table_A. I guess what I'd like to do, is drop
> this index and recreated it as a primary key with the same columns and name.
> Would this pose any type of possible problem ?
> TIA,
> Billy
|||There is one thing to look out for and that is that unique indexes can be on
nullable columns (although they only allow one NULL value), but primary keys
can't. But if the columns in the unique index are non-nullable, it can be
replaced with a primary key, provided, as you say, that there isn't one
already on the table.
Jacco Schalkwijk
SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e$YRdIcRFHA.904@.tk2msftngp13.phx.gbl...
> That should be fine (assuming you don't already have a PK on the table).
> The PK carries with it a unique index (you have control over whether it is
> to be a clustered index or not).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "BillyDees" <BillyDees@.discussions.microsoft.com> wrote in message
> news:1F6AAF9E-5E99-4776-ACF4-54B7FDB0EF89@.microsoft.com...
>
|||Good catch, Jacco!
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid > wrote in message
news:ueX1vNcRFHA.996@.TK2MSFTNGP09.phx.gbl...
> There is one thing to look out for and that is that unique indexes can be on nullable columns
> (although they only allow one NULL value), but primary keys can't. But if the columns in the
> unique index are non-nullable, it can be replaced with a primary key, provided, as you say, that
> there isn't one already on the table.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:e$YRdIcRFHA.904@.tk2msftngp13.phx.gbl...
>
|||Thanks to all of you for your replies. You confirmed what I thought but I
needed to be sure.
Billy
"Tibor Karaszi" wrote:

> Good catch, Jacco!
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid > wrote in message
> news:ueX1vNcRFHA.996@.TK2MSFTNGP09.phx.gbl...
>
>

PK vs. Unique clustered indexes

Are there any differences, performance related or other wise, between having
a clustered primary key vs. a clustered unique key?
I'm am trying to ready a SQL2K server for replication. Some of the tables
in database A don't have a primary key but they do have a cluster unique
index. The index is named pk_table_A. I guess what I'd like to do, is drop
this index and recreated it as a primary key with the same columns and name.
Would this pose any type of possible problem ?
TIA,
BillyThat should be fine (assuming you don't already have a PK on the table). The PK carries with it a
unique index (you have control over whether it is to be a clustered index or not).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"BillyDees" <BillyDees@.discussions.microsoft.com> wrote in message
news:1F6AAF9E-5E99-4776-ACF4-54B7FDB0EF89@.microsoft.com...
> Are there any differences, performance related or other wise, between having
> a clustered primary key vs. a clustered unique key?
> I'm am trying to ready a SQL2K server for replication. Some of the tables
> in database A don't have a primary key but they do have a cluster unique
> index. The index is named pk_table_A. I guess what I'd like to do, is drop
> this index and recreated it as a primary key with the same columns and name.
> Would this pose any type of possible problem ?
> TIA,
> Billy|||There is one thing to look out for and that is that unique indexes can be on
nullable columns (although they only allow one NULL value), but primary keys
can't. But if the columns in the unique index are non-nullable, it can be
replaced with a primary key, provided, as you say, that there isn't one
already on the table.
--
Jacco Schalkwijk
SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e$YRdIcRFHA.904@.tk2msftngp13.phx.gbl...
> That should be fine (assuming you don't already have a PK on the table).
> The PK carries with it a unique index (you have control over whether it is
> to be a clustered index or not).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "BillyDees" <BillyDees@.discussions.microsoft.com> wrote in message
> news:1F6AAF9E-5E99-4776-ACF4-54B7FDB0EF89@.microsoft.com...
>> Are there any differences, performance related or other wise, between
>> having
>> a clustered primary key vs. a clustered unique key?
>> I'm am trying to ready a SQL2K server for replication. Some of the
>> tables
>> in database A don't have a primary key but they do have a cluster unique
>> index. The index is named pk_table_A. I guess what I'd like to do, is
>> drop
>> this index and recreated it as a primary key with the same columns and
>> name.
>> Would this pose any type of possible problem ?
>> TIA,
>> Billy
>|||Good catch, Jacco!
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote in message
news:ueX1vNcRFHA.996@.TK2MSFTNGP09.phx.gbl...
> There is one thing to look out for and that is that unique indexes can be on nullable columns
> (although they only allow one NULL value), but primary keys can't. But if the columns in the
> unique index are non-nullable, it can be replaced with a primary key, provided, as you say, that
> there isn't one already on the table.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:e$YRdIcRFHA.904@.tk2msftngp13.phx.gbl...
>> That should be fine (assuming you don't already have a PK on the table). The PK carries with it a
>> unique index (you have control over whether it is to be a clustered index or not).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "BillyDees" <BillyDees@.discussions.microsoft.com> wrote in message
>> news:1F6AAF9E-5E99-4776-ACF4-54B7FDB0EF89@.microsoft.com...
>> Are there any differences, performance related or other wise, between having
>> a clustered primary key vs. a clustered unique key?
>> I'm am trying to ready a SQL2K server for replication. Some of the tables
>> in database A don't have a primary key but they do have a cluster unique
>> index. The index is named pk_table_A. I guess what I'd like to do, is drop
>> this index and recreated it as a primary key with the same columns and name.
>> Would this pose any type of possible problem ?
>> TIA,
>> Billy
>>
>|||Thanks to all of you for your replies. You confirmed what I thought but I
needed to be sure.
Billy
"Tibor Karaszi" wrote:
> Good catch, Jacco!
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote in message
> news:ueX1vNcRFHA.996@.TK2MSFTNGP09.phx.gbl...
> > There is one thing to look out for and that is that unique indexes can be on nullable columns
> > (although they only allow one NULL value), but primary keys can't. But if the columns in the
> > unique index are non-nullable, it can be replaced with a primary key, provided, as you say, that
> > there isn't one already on the table.
> >
> > --
> > Jacco Schalkwijk
> > SQL Server MVP
> >
> >
> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> > news:e$YRdIcRFHA.904@.tk2msftngp13.phx.gbl...
> >> That should be fine (assuming you don't already have a PK on the table). The PK carries with it a
> >> unique index (you have control over whether it is to be a clustered index or not).
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "BillyDees" <BillyDees@.discussions.microsoft.com> wrote in message
> >> news:1F6AAF9E-5E99-4776-ACF4-54B7FDB0EF89@.microsoft.com...
> >> Are there any differences, performance related or other wise, between having
> >> a clustered primary key vs. a clustered unique key?
> >>
> >> I'm am trying to ready a SQL2K server for replication. Some of the tables
> >> in database A don't have a primary key but they do have a cluster unique
> >> index. The index is named pk_table_A. I guess what I'd like to do, is drop
> >> this index and recreated it as a primary key with the same columns and name.
> >> Would this pose any type of possible problem ?
> >>
> >> TIA,
> >> Billy
> >>
> >>
> >
> >
>
>

PK vs. Unique clustered indexes

Are there any differences, performance related or other wise, between having
a clustered primary key vs. a clustered unique key?
I'm am trying to ready a SQL2K server for replication. Some of the tables
in database A don't have a primary key but they do have a cluster unique
index. The index is named pk_table_A. I guess what I'd like to do, is drop
this index and recreated it as a primary key with the same columns and name.
Would this pose any type of possible problem ?
TIA,
BillyThat should be fine (assuming you don't already have a PK on the table). The
PK carries with it a
unique index (you have control over whether it is to be a clustered index or
not).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"BillyDees" <BillyDees@.discussions.microsoft.com> wrote in message
news:1F6AAF9E-5E99-4776-ACF4-54B7FDB0EF89@.microsoft.com...
> Are there any differences, performance related or other wise, between havi
ng
> a clustered primary key vs. a clustered unique key?
> I'm am trying to ready a SQL2K server for replication. Some of the tables
> in database A don't have a primary key but they do have a cluster unique
> index. The index is named pk_table_A. I guess what I'd like to do, is dr
op
> this index and recreated it as a primary key with the same columns and nam
e.
> Would this pose any type of possible problem ?
> TIA,
> Billy|||There is one thing to look out for and that is that unique indexes can be on
nullable columns (although they only allow one NULL value), but primary keys
can't. But if the columns in the unique index are non-nullable, it can be
replaced with a primary key, provided, as you say, that there isn't one
already on the table.
Jacco Schalkwijk
SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e$YRdIcRFHA.904@.tk2msftngp13.phx.gbl...
> That should be fine (assuming you don't already have a PK on the table).
> The PK carries with it a unique index (you have control over whether it is
> to be a clustered index or not).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "BillyDees" <BillyDees@.discussions.microsoft.com> wrote in message
> news:1F6AAF9E-5E99-4776-ACF4-54B7FDB0EF89@.microsoft.com...
>|||Good catch, Jacco!
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message
news:ueX1vNcRFHA.996@.TK2MSFTNGP09.phx.gbl...
> There is one thing to look out for and that is that unique indexes can be
on nullable columns
> (although they only allow one NULL value), but primary keys can't. But if
the columns in the
> unique index are non-nullable, it can be replaced with a primary key, prov
ided, as you say, that
> there isn't one already on the table.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:e$YRdIcRFHA.904@.tk2msftngp13.phx.gbl...
>|||Thanks to all of you for your replies. You confirmed what I thought but I
needed to be sure.
Billy
"Tibor Karaszi" wrote:

> Good catch, Jacco!
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wro
te in message
> news:ueX1vNcRFHA.996@.TK2MSFTNGP09.phx.gbl...
>
>

Pk to be initiated every time?

i want primary key of table to be inserted automatically. i've set its in desgin table as Identity = Yes; Seed=1

i want my application all other attributes except primary key which i've set atutomatically inserted.

but by doing this; it doesn't insert its value. instead it enters 0

can u plz help me in doing so?

Hi,

You are probably not doing things in the right way. It's a good idea to try ceating the table using a create table script and see if the problem persists or not:

CREATE TABLE TableName( PrimaryKeyColumnNameINT IDENTITY PRIMARY KEYNOT NULL, Column1 DataType, ...)

Happy SQLing!

Mehrdad

PK Index name

I have table tipDok with primary key, which is varchar data type.
The name of the index(primary key) is generated automatically and is:
PK__TipDOK__1EC48A19
I select: ALL tasks- Export data in enterprice manager and copy objects
option and then select this table to be copied to some other database.
The table is copied successfully and is identical, only the name of index is
now different:
PK__TipDOK__267ABA7A
Why?
I would like that also the name is identical.
Because I have program for comparing the datatbases and it gives me the
difference between this table just because of the index name.
Then I have errors when I synchronize databases, because the index can't
be dropped.
Any idea?
lp,
Simonwhat you need is a program that recognises that the primary keys are the sam
e
and only the name is different so it renames the key. DB Ghost
(http://www.dbghost.com) does this and does it for foreign keys as well.
"simon" wrote:

> I have table tipDok with primary key, which is varchar data type.
> The name of the index(primary key) is generated automatically and is:
> PK__TipDOK__1EC48A19
> I select: ALL tasks- Export data in enterprice manager and copy objects
> option and then select this table to be copied to some other database.
> The table is copied successfully and is identical, only the name of index
is
> now different:
> PK__TipDOK__267ABA7A
> Why?
> I would like that also the name is identical.
> Because I have program for comparing the datatbases and it gives me the
> difference between this table just because of the index name.
> Then I have errors when I synchronize databases, because the index can't
> be dropped.
> Any idea?
> lp,
> Simon
>
>|||In Enterprise Manager, right-click on your database, select All Tasks, then
Generate SQL Script.
Select whatever tables you want to copy, then go to the Options tab and make
sure "Select PRIMARY keys, FOREIGN keys, defaults, and check constriants" is
selected.
Use the script this generates to create a new, empty database. Then you can
use the DTS wizard to copy the data in from the old database.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"simon" <simon.zupan@.iware.si> wrote in message
news:iQ%Td.9584$F6.1864547@.news.siol.net...
> I have table tipDok with primary key, which is varchar data type.
> The name of the index(primary key) is generated automatically and is:
> PK__TipDOK__1EC48A19
> I select: ALL tasks- Export data in enterprice manager and copy objects
> option and then select this table to be copied to some other database.
> The table is copied successfully and is identical, only the name of index
is
> now different:
> PK__TipDOK__267ABA7A
> Why?
> I would like that also the name is identical.
> Because I have program for comparing the datatbases and it gives me the
> difference between this table just because of the index name.
> Then I have errors when I synchronize databases, because the index can't
> be dropped.
> Any idea?
> lp,
> Simon
>

PK Index in SQL Server

I'm a little confused on the details of how a PK index is implemented in SQL
Server.
It looks like when you say "ADD CONSTRAINT _name_ PRIMARY KEY (_col_) ON
_filegroup_" you are specifying the index the PK will use in its entirety.
What I'm accustomed to (under Informix) is to create the table, create the
index the constraint will use and then create the constraint, which will
detect the appropriate index and use it (Informix has much more robust
storage options, which are available in create index, but not add
constraint).
However, under SQL Server when I do this, I seem to get two unique indexes
on the same column(s). Also, SQL Server seems to limit indexes to a single
filegroup negating anything fancy in the first place (this is still true
with DPV's).
So, I'm guessing that the ADD CONSTRAINT clause in T-SQL will create its own
index, period. Also, except for the entry in sysconstraints, a constraint
and an index are implemented completely identically in SQL Server.
Am I correct?
Thanks,
JayHi Jay,
In SQL Server you create a primary key, adding a constraint. The way SS
implement such constraint, is creating a unique index, behind the scene, that
could be clustered or nonclustered. If you do not specify what kind of index
to use, SS will use clustered by default. It will be the same with UNIQUE
constraints.
You can not disable a primary key or unique constraint using "alter table
... nocheck constraint_name", but you can disable the index associated to the
costraint. Notice that if the index is a clustered one, then disabling it
will stop you from inserting into the table.
CREATE TABLE dbo.t(
c1 INT NOT NULL CONSTRAINT pk_t PRIMARY KEY NONCLUSTERED,
c2 INT NOT NULL CONSTRAINT uq_c2 UNIQUE NONCLUSTERED
)
GO
ALTER TABLE dbo.t
NOCHECK constraint pk_t
GO
ALTER TABLE dbo.t
NOCHECK constraint uq_c2
GO
SELECT
OBJECTPROPERTY(OBJECT_ID('pk_t'), 'CnstIsDisabled '),
OBJECTPROPERTY(OBJECT_ID('uq_c2'), 'CnstIsDisabled ')
GO
ALTER INDEX pk_t ON dbo.t DISABLE
go
ALTER INDEX uq_c2 ON dbo.t DISABLE
go
SELECT
OBJECTPROPERTY(OBJECT_ID('pk_t'), 'CnstIsDisabled '),
OBJECTPROPERTY(OBJECT_ID('uq_c2'), 'CnstIsDisabled ')
GO
INSERT INTO dbo.t VALUES(1, 1)
GO
INSERT INTO dbo.t VALUES(2, 1)
GO
INSERT INTO dbo.t VALUES(2, 1)
GO
SELECT * FROM dbo.[t]
GO
DROP TABLE dbo.[t]
GO
AMB
"Jay" wrote:
> I'm a little confused on the details of how a PK index is implemented in SQL
> Server.
> It looks like when you say "ADD CONSTRAINT _name_ PRIMARY KEY (_col_) ON
> _filegroup_" you are specifying the index the PK will use in its entirety.
> What I'm accustomed to (under Informix) is to create the table, create the
> index the constraint will use and then create the constraint, which will
> detect the appropriate index and use it (Informix has much more robust
> storage options, which are available in create index, but not add
> constraint).
> However, under SQL Server when I do this, I seem to get two unique indexes
> on the same column(s). Also, SQL Server seems to limit indexes to a single
> filegroup negating anything fancy in the first place (this is still true
> with DPV's).
> So, I'm guessing that the ADD CONSTRAINT clause in T-SQL will create its own
> index, period. Also, except for the entry in sysconstraints, a constraint
> and an index are implemented completely identically in SQL Server.
> Am I correct?
> Thanks,
> Jay
>
>|||Thank Alejandro,
So, if I understand you correctly, with the exceptions of focusing on
clustering and disabling indexes, adding a primary key constraing is the
same as adding an index, except that you get the extra stuff that comes with
a primary key.
Thanks,
Jay
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:2B48C256-79BC-45C6-92A4-13D940FD6980@.microsoft.com...
> Hi Jay,
> In SQL Server you create a primary key, adding a constraint. The way SS
> implement such constraint, is creating a unique index, behind the scene,
> that
> could be clustered or nonclustered. If you do not specify what kind of
> index
> to use, SS will use clustered by default. It will be the same with UNIQUE
> constraints.
> You can not disable a primary key or unique constraint using "alter table
> ... nocheck constraint_name", but you can disable the index associated to
> the
> costraint. Notice that if the index is a clustered one, then disabling it
> will stop you from inserting into the table.
>
> CREATE TABLE dbo.t(
> c1 INT NOT NULL CONSTRAINT pk_t PRIMARY KEY NONCLUSTERED,
> c2 INT NOT NULL CONSTRAINT uq_c2 UNIQUE NONCLUSTERED
> )
> GO
> ALTER TABLE dbo.t
> NOCHECK constraint pk_t
> GO
> ALTER TABLE dbo.t
> NOCHECK constraint uq_c2
> GO
> SELECT
> OBJECTPROPERTY(OBJECT_ID('pk_t'), 'CnstIsDisabled '),
> OBJECTPROPERTY(OBJECT_ID('uq_c2'), 'CnstIsDisabled ')
> GO
> ALTER INDEX pk_t ON dbo.t DISABLE
> go
> ALTER INDEX uq_c2 ON dbo.t DISABLE
> go
> SELECT
> OBJECTPROPERTY(OBJECT_ID('pk_t'), 'CnstIsDisabled '),
> OBJECTPROPERTY(OBJECT_ID('uq_c2'), 'CnstIsDisabled ')
> GO
> INSERT INTO dbo.t VALUES(1, 1)
> GO
> INSERT INTO dbo.t VALUES(2, 1)
> GO
> INSERT INTO dbo.t VALUES(2, 1)
> GO
> SELECT * FROM dbo.[t]
> GO
> DROP TABLE dbo.[t]
> GO
>
> AMB
>
> "Jay" wrote:
>> I'm a little confused on the details of how a PK index is implemented in
>> SQL
>> Server.
>> It looks like when you say "ADD CONSTRAINT _name_ PRIMARY KEY (_col_) ON
>> _filegroup_" you are specifying the index the PK will use in its
>> entirety.
>> What I'm accustomed to (under Informix) is to create the table, create
>> the
>> index the constraint will use and then create the constraint, which will
>> detect the appropriate index and use it (Informix has much more robust
>> storage options, which are available in create index, but not add
>> constraint).
>> However, under SQL Server when I do this, I seem to get two unique
>> indexes
>> on the same column(s). Also, SQL Server seems to limit indexes to a
>> single
>> filegroup negating anything fancy in the first place (this is still true
>> with DPV's).
>> So, I'm guessing that the ADD CONSTRAINT clause in T-SQL will create its
>> own
>> index, period. Also, except for the entry in sysconstraints, a constraint
>> and an index are implemented completely identically in SQL Server.
>> Am I correct?
>> Thanks,
>> Jay
>>|||> So, if I understand you correctly, with the exceptions of focusing on clustering and disabling
> indexes, adding a primary key constraing is the same as adding an index, except that you get the
> extra stuff that comes with a primary key.
Yes. A PK and UQ constraint is "implemented" though a unique index, which is created for you when
you defined (add) your constraint. The index has the same name as the constraint. This is also why
the constraint definition is "littered" with physical attributes like clustered/nonclustered and
filegroup information. It is just a matter of getting used to that this is the way they've done it.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jay" <spam@.nospam.org> wrote in message news:ejzlYVICIHA.3916@.TK2MSFTNGP02.phx.gbl...
> Thank Alejandro,
> So, if I understand you correctly, with the exceptions of focusing on clustering and disabling
> indexes, adding a primary key constraing is the same as adding an index, except that you get the
> extra stuff that comes with a primary key.
> Thanks,
> Jay
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
> news:2B48C256-79BC-45C6-92A4-13D940FD6980@.microsoft.com...
>> Hi Jay,
>> In SQL Server you create a primary key, adding a constraint. The way SS
>> implement such constraint, is creating a unique index, behind the scene, that
>> could be clustered or nonclustered. If you do not specify what kind of index
>> to use, SS will use clustered by default. It will be the same with UNIQUE
>> constraints.
>> You can not disable a primary key or unique constraint using "alter table
>> ... nocheck constraint_name", but you can disable the index associated to the
>> costraint. Notice that if the index is a clustered one, then disabling it
>> will stop you from inserting into the table.
>>
>> CREATE TABLE dbo.t(
>> c1 INT NOT NULL CONSTRAINT pk_t PRIMARY KEY NONCLUSTERED,
>> c2 INT NOT NULL CONSTRAINT uq_c2 UNIQUE NONCLUSTERED
>> )
>> GO
>> ALTER TABLE dbo.t
>> NOCHECK constraint pk_t
>> GO
>> ALTER TABLE dbo.t
>> NOCHECK constraint uq_c2
>> GO
>> SELECT
>> OBJECTPROPERTY(OBJECT_ID('pk_t'), 'CnstIsDisabled '),
>> OBJECTPROPERTY(OBJECT_ID('uq_c2'), 'CnstIsDisabled ')
>> GO
>> ALTER INDEX pk_t ON dbo.t DISABLE
>> go
>> ALTER INDEX uq_c2 ON dbo.t DISABLE
>> go
>> SELECT
>> OBJECTPROPERTY(OBJECT_ID('pk_t'), 'CnstIsDisabled '),
>> OBJECTPROPERTY(OBJECT_ID('uq_c2'), 'CnstIsDisabled ')
>> GO
>> INSERT INTO dbo.t VALUES(1, 1)
>> GO
>> INSERT INTO dbo.t VALUES(2, 1)
>> GO
>> INSERT INTO dbo.t VALUES(2, 1)
>> GO
>> SELECT * FROM dbo.[t]
>> GO
>> DROP TABLE dbo.[t]
>> GO
>>
>> AMB
>>
>> "Jay" wrote:
>> I'm a little confused on the details of how a PK index is implemented in SQL
>> Server.
>> It looks like when you say "ADD CONSTRAINT _name_ PRIMARY KEY (_col_) ON
>> _filegroup_" you are specifying the index the PK will use in its entirety.
>> What I'm accustomed to (under Informix) is to create the table, create the
>> index the constraint will use and then create the constraint, which will
>> detect the appropriate index and use it (Informix has much more robust
>> storage options, which are available in create index, but not add
>> constraint).
>> However, under SQL Server when I do this, I seem to get two unique indexes
>> on the same column(s). Also, SQL Server seems to limit indexes to a single
>> filegroup negating anything fancy in the first place (this is still true
>> with DPV's).
>> So, I'm guessing that the ADD CONSTRAINT clause in T-SQL will create its own
>> index, period. Also, except for the entry in sysconstraints, a constraint
>> and an index are implemented completely identically in SQL Server.
>> Am I correct?
>> Thanks,
>> Jay
>>
>|||Technically, yes.
However, the point of having constraints and indexes is because
logically, they have different purposes.
If you are adding a constraint (such as a Primary Key), you are saying
something about your data, you are determining "business rules". With a
Primary Key you are saying: this is the key that identifies the row, and
it should be unique. Then, it is up to the RDBMS how it enforces this
constraint. If you have a compound primary key, then from this point of
view, the order of the columns does not matter.
The current and all past versions of SQL Server enforce a Primary Key
and Unique Constraint with a unique index. This could be changed in the
future. Maybe one day SQL Server might enforce it using hashing or some
other technique.
If you add an index, you are optimizing your data access. When you add a
compound index, the column order matters a lot, because of the nature of
B-tree indexes. It also matters because a join between two tables with a
compound key in different order is very inefficient.
I consider it a good practice to always use constraints to determine
keys and uniqueness, and to use indexes for additional tuning. This way,
the person optimizing the data access knows which indexes can be removed
without destroying integrity, and the person (re)modelling the schema
does not have to look at indexes.
--
Gert-Jan
Jay wrote:
> Thank Alejandro,
> So, if I understand you correctly, with the exceptions of focusing on
> clustering and disabling indexes, adding a primary key constraing is the
> same as adding an index, except that you get the extra stuff that comes with
> a primary key.
> Thanks,
> Jay
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
> news:2B48C256-79BC-45C6-92A4-13D940FD6980@.microsoft.com...
> > Hi Jay,
> >
> > In SQL Server you create a primary key, adding a constraint. The way SS
> > implement such constraint, is creating a unique index, behind the scene,
> > that
> > could be clustered or nonclustered. If you do not specify what kind of
> > index
> > to use, SS will use clustered by default. It will be the same with UNIQUE
> > constraints.
> >
> > You can not disable a primary key or unique constraint using "alter table
> > ... nocheck constraint_name", but you can disable the index associated to
> > the
> > costraint. Notice that if the index is a clustered one, then disabling it
> > will stop you from inserting into the table.
> >
> >
> > CREATE TABLE dbo.t(
> > c1 INT NOT NULL CONSTRAINT pk_t PRIMARY KEY NONCLUSTERED,
> > c2 INT NOT NULL CONSTRAINT uq_c2 UNIQUE NONCLUSTERED
> > )
> > GO
> >
> > ALTER TABLE dbo.t
> > NOCHECK constraint pk_t
> > GO
> >
> > ALTER TABLE dbo.t
> > NOCHECK constraint uq_c2
> > GO
> >
> > SELECT
> > OBJECTPROPERTY(OBJECT_ID('pk_t'), 'CnstIsDisabled '),
> > OBJECTPROPERTY(OBJECT_ID('uq_c2'), 'CnstIsDisabled ')
> > GO
> >
> > ALTER INDEX pk_t ON dbo.t DISABLE
> > go
> >
> > ALTER INDEX uq_c2 ON dbo.t DISABLE
> > go
> >
> > SELECT
> > OBJECTPROPERTY(OBJECT_ID('pk_t'), 'CnstIsDisabled '),
> > OBJECTPROPERTY(OBJECT_ID('uq_c2'), 'CnstIsDisabled ')
> > GO
> >
> > INSERT INTO dbo.t VALUES(1, 1)
> > GO
> >
> > INSERT INTO dbo.t VALUES(2, 1)
> > GO
> >
> > INSERT INTO dbo.t VALUES(2, 1)
> > GO
> >
> > SELECT * FROM dbo.[t]
> > GO
> >
> > DROP TABLE dbo.[t]
> > GO
> >
> >
> > AMB
> >
> >
> > "Jay" wrote:
> >
> >> I'm a little confused on the details of how a PK index is implemented in
> >> SQL
> >> Server.
> >>
> >> It looks like when you say "ADD CONSTRAINT _name_ PRIMARY KEY (_col_) ON
> >> _filegroup_" you are specifying the index the PK will use in its
> >> entirety.
> >>
> >> What I'm accustomed to (under Informix) is to create the table, create
> >> the
> >> index the constraint will use and then create the constraint, which will
> >> detect the appropriate index and use it (Informix has much more robust
> >> storage options, which are available in create index, but not add
> >> constraint).
> >>
> >> However, under SQL Server when I do this, I seem to get two unique
> >> indexes
> >> on the same column(s). Also, SQL Server seems to limit indexes to a
> >> single
> >> filegroup negating anything fancy in the first place (this is still true
> >> with DPV's).
> >>
> >> So, I'm guessing that the ADD CONSTRAINT clause in T-SQL will create its
> >> own
> >> index, period. Also, except for the entry in sysconstraints, a constraint
> >> and an index are implemented completely identically in SQL Server.
> >>
> >> Am I correct?
> >>
> >> Thanks,
> >> Jay
> >>
> >>
> >>|||Jay,
You got very good answers from Tibor and Gert-Jan. It is important, at least
for me, to point what Gert-Jan already mentioned, that primary key is part of
the models earlier than the physical. We do not talk about indexes, during
logical and conceptual models, to enforce a constraint.
AMB
"Jay" wrote:
> Thank Alejandro,
> So, if I understand you correctly, with the exceptions of focusing on
> clustering and disabling indexes, adding a primary key constraing is the
> same as adding an index, except that you get the extra stuff that comes with
> a primary key.
> Thanks,
> Jay
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
> news:2B48C256-79BC-45C6-92A4-13D940FD6980@.microsoft.com...
> > Hi Jay,
> >
> > In SQL Server you create a primary key, adding a constraint. The way SS
> > implement such constraint, is creating a unique index, behind the scene,
> > that
> > could be clustered or nonclustered. If you do not specify what kind of
> > index
> > to use, SS will use clustered by default. It will be the same with UNIQUE
> > constraints.
> >
> > You can not disable a primary key or unique constraint using "alter table
> > ... nocheck constraint_name", but you can disable the index associated to
> > the
> > costraint. Notice that if the index is a clustered one, then disabling it
> > will stop you from inserting into the table.
> >
> >
> > CREATE TABLE dbo.t(
> > c1 INT NOT NULL CONSTRAINT pk_t PRIMARY KEY NONCLUSTERED,
> > c2 INT NOT NULL CONSTRAINT uq_c2 UNIQUE NONCLUSTERED
> > )
> > GO
> >
> > ALTER TABLE dbo.t
> > NOCHECK constraint pk_t
> > GO
> >
> > ALTER TABLE dbo.t
> > NOCHECK constraint uq_c2
> > GO
> >
> > SELECT
> > OBJECTPROPERTY(OBJECT_ID('pk_t'), 'CnstIsDisabled '),
> > OBJECTPROPERTY(OBJECT_ID('uq_c2'), 'CnstIsDisabled ')
> > GO
> >
> > ALTER INDEX pk_t ON dbo.t DISABLE
> > go
> >
> > ALTER INDEX uq_c2 ON dbo.t DISABLE
> > go
> >
> > SELECT
> > OBJECTPROPERTY(OBJECT_ID('pk_t'), 'CnstIsDisabled '),
> > OBJECTPROPERTY(OBJECT_ID('uq_c2'), 'CnstIsDisabled ')
> > GO
> >
> > INSERT INTO dbo.t VALUES(1, 1)
> > GO
> >
> > INSERT INTO dbo.t VALUES(2, 1)
> > GO
> >
> > INSERT INTO dbo.t VALUES(2, 1)
> > GO
> >
> > SELECT * FROM dbo.[t]
> > GO
> >
> > DROP TABLE dbo.[t]
> > GO
> >
> >
> > AMB
> >
> >
> > "Jay" wrote:
> >
> >> I'm a little confused on the details of how a PK index is implemented in
> >> SQL
> >> Server.
> >>
> >> It looks like when you say "ADD CONSTRAINT _name_ PRIMARY KEY (_col_) ON
> >> _filegroup_" you are specifying the index the PK will use in its
> >> entirety.
> >>
> >> What I'm accustomed to (under Informix) is to create the table, create
> >> the
> >> index the constraint will use and then create the constraint, which will
> >> detect the appropriate index and use it (Informix has much more robust
> >> storage options, which are available in create index, but not add
> >> constraint).
> >>
> >> However, under SQL Server when I do this, I seem to get two unique
> >> indexes
> >> on the same column(s). Also, SQL Server seems to limit indexes to a
> >> single
> >> filegroup negating anything fancy in the first place (this is still true
> >> with DPV's).
> >>
> >> So, I'm guessing that the ADD CONSTRAINT clause in T-SQL will create its
> >> own
> >> index, period. Also, except for the entry in sysconstraints, a constraint
> >> and an index are implemented completely identically in SQL Server.
> >>
> >> Am I correct?
> >>
> >> Thanks,
> >> Jay
> >>
> >>
> >>
>
>|||Thanks Alejandro,
But I have no issues with the logical constructs as I've been working with
relational databases for just under 20 years, it was just the physical
implementation I needed to grasp.
Jay
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:2700F07B-B429-4829-BD6A-83DDDB464703@.microsoft.com...
> Jay,
> You got very good answers from Tibor and Gert-Jan. It is important, at
> least
> for me, to point what Gert-Jan already mentioned, that primary key is part
> of
> the models earlier than the physical. We do not talk about indexes, during
> logical and conceptual models, to enforce a constraint.
>
> AMB
> "Jay" wrote:
>> Thank Alejandro,
>> So, if I understand you correctly, with the exceptions of focusing on
>> clustering and disabling indexes, adding a primary key constraing is the
>> same as adding an index, except that you get the extra stuff that comes
>> with
>> a primary key.
>> Thanks,
>> Jay
>> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in
>> message
>> news:2B48C256-79BC-45C6-92A4-13D940FD6980@.microsoft.com...
>> > Hi Jay,
>> >
>> > In SQL Server you create a primary key, adding a constraint. The way SS
>> > implement such constraint, is creating a unique index, behind the
>> > scene,
>> > that
>> > could be clustered or nonclustered. If you do not specify what kind of
>> > index
>> > to use, SS will use clustered by default. It will be the same with
>> > UNIQUE
>> > constraints.
>> >
>> > You can not disable a primary key or unique constraint using "alter
>> > table
>> > ... nocheck constraint_name", but you can disable the index associated
>> > to
>> > the
>> > costraint. Notice that if the index is a clustered one, then disabling
>> > it
>> > will stop you from inserting into the table.
>> >
>> >
>> > CREATE TABLE dbo.t(
>> > c1 INT NOT NULL CONSTRAINT pk_t PRIMARY KEY NONCLUSTERED,
>> > c2 INT NOT NULL CONSTRAINT uq_c2 UNIQUE NONCLUSTERED
>> > )
>> > GO
>> >
>> > ALTER TABLE dbo.t
>> > NOCHECK constraint pk_t
>> > GO
>> >
>> > ALTER TABLE dbo.t
>> > NOCHECK constraint uq_c2
>> > GO
>> >
>> > SELECT
>> > OBJECTPROPERTY(OBJECT_ID('pk_t'), 'CnstIsDisabled '),
>> > OBJECTPROPERTY(OBJECT_ID('uq_c2'), 'CnstIsDisabled ')
>> > GO
>> >
>> > ALTER INDEX pk_t ON dbo.t DISABLE
>> > go
>> >
>> > ALTER INDEX uq_c2 ON dbo.t DISABLE
>> > go
>> >
>> > SELECT
>> > OBJECTPROPERTY(OBJECT_ID('pk_t'), 'CnstIsDisabled '),
>> > OBJECTPROPERTY(OBJECT_ID('uq_c2'), 'CnstIsDisabled ')
>> > GO
>> >
>> > INSERT INTO dbo.t VALUES(1, 1)
>> > GO
>> >
>> > INSERT INTO dbo.t VALUES(2, 1)
>> > GO
>> >
>> > INSERT INTO dbo.t VALUES(2, 1)
>> > GO
>> >
>> > SELECT * FROM dbo.[t]
>> > GO
>> >
>> > DROP TABLE dbo.[t]
>> > GO
>> >
>> >
>> > AMB
>> >
>> >
>> > "Jay" wrote:
>> >
>> >> I'm a little confused on the details of how a PK index is implemented
>> >> in
>> >> SQL
>> >> Server.
>> >>
>> >> It looks like when you say "ADD CONSTRAINT _name_ PRIMARY KEY (_col_)
>> >> ON
>> >> _filegroup_" you are specifying the index the PK will use in its
>> >> entirety.
>> >>
>> >> What I'm accustomed to (under Informix) is to create the table, create
>> >> the
>> >> index the constraint will use and then create the constraint, which
>> >> will
>> >> detect the appropriate index and use it (Informix has much more robust
>> >> storage options, which are available in create index, but not add
>> >> constraint).
>> >>
>> >> However, under SQL Server when I do this, I seem to get two unique
>> >> indexes
>> >> on the same column(s). Also, SQL Server seems to limit indexes to a
>> >> single
>> >> filegroup negating anything fancy in the first place (this is still
>> >> true
>> >> with DPV's).
>> >>
>> >> So, I'm guessing that the ADD CONSTRAINT clause in T-SQL will create
>> >> its
>> >> own
>> >> index, period. Also, except for the entry in sysconstraints, a
>> >> constraint
>> >> and an index are implemented completely identically in SQL Server.
>> >>
>> >> Am I correct?
>> >>
>> >> Thanks,
>> >> Jay
>> >>
>> >>
>> >>
>>

PK Index Cluster/Non Cluster

I have application that I inherited. Looks to me the indexes where set up
incorrectly. Numerous tables have Non-Cluster indexes as Primary Keys that
were set up with Identity Incremented by 1.
I was told the primary keys that are increasing in numerical value should be
a cluster index.
Is there a list of rules for best practices on setting up Cluster and
Non-Indexes?
Thanks,Most of the time yes, it probably would be clustered. But just because it's
not doesnt mean its incorrect. In a reporting server, you may want clusterin
g
done on date fields, as thats what most queries are run against and that
could greatly speed stuff up.
TIA,
ChrisR
"Joe K." wrote:

> I have application that I inherited. Looks to me the indexes where set u
p
> incorrectly. Numerous tables have Non-Cluster indexes as Primary Keys tha
t
> were set up with Identity Incremented by 1.
> I was told the primary keys that are increasing in numerical value should
be
> a cluster index.
> Is there a list of rules for best practices on setting up Cluster and
> Non-Indexes?
> Thanks,|||Here's my shortlist of reasons why identities make for excellent clustered
indexes:
(a) They're narrow - because the keys of a clustered index CIX) are also
stored in the leaf nodes of all non-clustered indexes (NCIX), the narrower
the CIX keys, the less size impact they'll have on the size of the NCIX's.
Identities can be any of the integer types, sos their size can vary, but in
general, a 4 byte integer key makes for a nice compact key.
(b) Unique - SQL Server "uniquefies" non-unique values in CIXs with 8 byte
uniquefiers. Because identities are reasonably unique, they don't suffer
from this problem.
(c) Incremental - because identity columns are generally incremental in
nature, you typically don't suffer much from fragmentation. Non-incremental
keys (eg, customername) can heavily fragment a database during insert /
update / delete operations
(d) Stable - if a CIX key gets updated, any associated NCIXs (on the same
table) also have to be updated to synchronise their CIX bookmark keys (as
described in (a) )
Regards,
Greg Linwood
SQL Server MVP
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:7E115D53-5D47-410E-9900-FE935FE397D6@.microsoft.com...
> I have application that I inherited. Looks to me the indexes where set
> up
> incorrectly. Numerous tables have Non-Cluster indexes as Primary Keys
> that
> were set up with Identity Incremented by 1.
> I was told the primary keys that are increasing in numerical value should
> be
> a cluster index.
> Is there a list of rules for best practices on setting up Cluster and
> Non-Indexes?
> Thanks,

PK Index Cluster/Non Cluster

I have application that I inherited. Looks to me the indexes where set up
incorrectly. Numerous tables have Non-Cluster indexes as Primary Keys that
were set up with Identity Incremented by 1.
I was told the primary keys that are increasing in numerical value should be
a cluster index.
Is there a list of rules for best practices on setting up Cluster and
Non-Indexes?
Thanks,Most of the time yes, it probably would be clustered. But just because it's
not doesnt mean its incorrect. In a reporting server, you may want clustering
done on date fields, as thats what most queries are run against and that
could greatly speed stuff up.
TIA,
ChrisR
"Joe K." wrote:
> I have application that I inherited. Looks to me the indexes where set up
> incorrectly. Numerous tables have Non-Cluster indexes as Primary Keys that
> were set up with Identity Incremented by 1.
> I was told the primary keys that are increasing in numerical value should be
> a cluster index.
> Is there a list of rules for best practices on setting up Cluster and
> Non-Indexes?
> Thanks,|||Here's my shortlist of reasons why identities make for excellent clustered
indexes:
(a) They're narrow - because the keys of a clustered index CIX) are also
stored in the leaf nodes of all non-clustered indexes (NCIX), the narrower
the CIX keys, the less size impact they'll have on the size of the NCIX's.
Identities can be any of the integer types, sos their size can vary, but in
general, a 4 byte integer key makes for a nice compact key.
(b) Unique - SQL Server "uniquefies" non-unique values in CIXs with 8 byte
uniquefiers. Because identities are reasonably unique, they don't suffer
from this problem.
(c) Incremental - because identity columns are generally incremental in
nature, you typically don't suffer much from fragmentation. Non-incremental
keys (eg, customername) can heavily fragment a database during insert /
update / delete operations
(d) Stable - if a CIX key gets updated, any associated NCIXs (on the same
table) also have to be updated to synchronise their CIX bookmark keys (as
described in (a) )
Regards,
Greg Linwood
SQL Server MVP
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:7E115D53-5D47-410E-9900-FE935FE397D6@.microsoft.com...
> I have application that I inherited. Looks to me the indexes where set
> up
> incorrectly. Numerous tables have Non-Cluster indexes as Primary Keys
> that
> were set up with Identity Incremented by 1.
> I was told the primary keys that are increasing in numerical value should
> be
> a cluster index.
> Is there a list of rules for best practices on setting up Cluster and
> Non-Indexes?
> Thanks,

PK Index Cluster/Non Cluster

I have application that I inherited. Looks to me the indexes where set up
incorrectly. Numerous tables have Non-Cluster indexes as Primary Keys that
were set up with Identity Incremented by 1.
I was told the primary keys that are increasing in numerical value should be
a cluster index.
Is there a list of rules for best practices on setting up Cluster and
Non-Indexes?
Thanks,
Most of the time yes, it probably would be clustered. But just because it's
not doesnt mean its incorrect. In a reporting server, you may want clustering
done on date fields, as thats what most queries are run against and that
could greatly speed stuff up.
TIA,
ChrisR
"Joe K." wrote:

> I have application that I inherited. Looks to me the indexes where set up
> incorrectly. Numerous tables have Non-Cluster indexes as Primary Keys that
> were set up with Identity Incremented by 1.
> I was told the primary keys that are increasing in numerical value should be
> a cluster index.
> Is there a list of rules for best practices on setting up Cluster and
> Non-Indexes?
> Thanks,
|||Here's my shortlist of reasons why identities make for excellent clustered
indexes:
(a) They're narrow - because the keys of a clustered index CIX) are also
stored in the leaf nodes of all non-clustered indexes (NCIX), the narrower
the CIX keys, the less size impact they'll have on the size of the NCIX's.
Identities can be any of the integer types, sos their size can vary, but in
general, a 4 byte integer key makes for a nice compact key.
(b) Unique - SQL Server "uniquefies" non-unique values in CIXs with 8 byte
uniquefiers. Because identities are reasonably unique, they don't suffer
from this problem.
(c) Incremental - because identity columns are generally incremental in
nature, you typically don't suffer much from fragmentation. Non-incremental
keys (eg, customername) can heavily fragment a database during insert /
update / delete operations
(d) Stable - if a CIX key gets updated, any associated NCIXs (on the same
table) also have to be updated to synchronise their CIX bookmark keys (as
described in (a) )
Regards,
Greg Linwood
SQL Server MVP
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:7E115D53-5D47-410E-9900-FE935FE397D6@.microsoft.com...
> I have application that I inherited. Looks to me the indexes where set
> up
> incorrectly. Numerous tables have Non-Cluster indexes as Primary Keys
> that
> were set up with Identity Incremented by 1.
> I was told the primary keys that are increasing in numerical value should
> be
> a cluster index.
> Is there a list of rules for best practices on setting up Cluster and
> Non-Indexes?
> Thanks,

PK creation

Is there any way to speed up Primary key creation on a
table
I have a 60o million row table and its taking forever
I know that data in table ic clean in data in PK columns i
am ccreating is unique
alter table Profile_table add primary key (as_of_date,
PAN11_ACCOUNT_NUMBER, PRODUCT_CODE)
Thanks
SanjayIf this table doesn't already have too many foreign keys associated with it,
what I would do is create another table with a similar structure, add all
foreign keys you need to it, and then import the data over (USING DTS or
some data transfer method that doesn't perform any logging) from the
existent 60 million record table.
This is exactly what SQL server does (behind the scenes) when you try to add
a foreign key to the table, except that it logs the transfer of every single
row into the new table, and that's what takes up all the time. The way you
are doing it, is just a little bit more manual, and you are shutting off the
whole logging process, which saves you up A TON of time and should be
relatively faster.
Good Luck
Awah-
"Sanjay" <sanjayg@.hotmail.com> wrote in message
news:02fe01c37179$ea8ebb70$a101280a@.phx.gbl...
> Is there any way to speed up Primary key creation on a
> table
> I have a 60o million row table and its taking forever
> I know that data in table ic clean in data in PK columns i
> am ccreating is unique
> alter table Profile_table add primary key (as_of_date,
> PAN11_ACCOUNT_NUMBER, PRODUCT_CODE)
> Thanks
> Sanjay

PK And Index

I have a primary key that comprises 2 columns (lets say ReportDate and
Symbol).

I know that if I submit a statement like SELECT * FROM T1 WHERE
ReportDate = '20031219' AND Symbol = 'XYZ' it will use the index.

But how about the statement SELECT * FROM T1 WHERE Symbol = 'XYZ'?

Do I need to create another index on symbol alone?Jason (JayCallas@.hotmail.com) writes:
> I have a primary key that comprises 2 columns (lets say ReportDate and
> Symbol).
> I know that if I submit a statement like SELECT * FROM T1 WHERE
> ReportDate = '20031219' AND Symbol = 'XYZ' it will use the index.
> But how about the statement SELECT * FROM T1 WHERE Symbol = 'XYZ'?
> Do I need to create another index on symbol alone?

For best performance, yes.

But the query may use the existing index, if the index is non-clustered.
If SQL Server finds that XYZ is not a very common value, it may opt
scan the index to find the rows. This is faster than scanning the entire
table. If the value is common, however, the bookmark lookups will be
more expensive than scanning.

If the existing index is clustered, it can not help to speed up the
retrieval. Ah, that wasn't completely true, either. Because if the
there is a non-clustered index on the table as well, the keys of the
clustered index appears in the non-clustered index, so SQL Server can
scan that index.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||To add to Erlands response. You could check the Execution Plan when using
Query Analyzer to see how SQL Server is using your indexes.

BZ

"Jason" <JayCallas@.hotmail.com> wrote in message
news:f01a7c89.0312190912.1c1ea341@.posting.google.c om...
> I have a primary key that comprises 2 columns (lets say ReportDate and
> Symbol).
> I know that if I submit a statement like SELECT * FROM T1 WHERE
> ReportDate = '20031219' AND Symbol = 'XYZ' it will use the index.
> But how about the statement SELECT * FROM T1 WHERE Symbol = 'XYZ'?
> Do I need to create another index on symbol alone?

pk & trailing spaces

I have a situation where the primary key is:
user_id char(20) not null
user_cd char(2) not null
allows users to store trailing spaces, which in turn makes the key not unique.
For example it allows the following, one with spaces one without:
ernie 01
ernie 01
This in turn causes processing problems in the app.
How do I fix this problem? BTW, ANSI_PADDING is turned off.
Thanks,
GracieUse VARCHAR not CHAR.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:DC0DFC0D-1017-40CD-A5C3-C6BD70224E62@.microsoft.com...
> I have a situation where the primary key is:
> user_id char(20) not null
> user_cd char(2) not null
> allows users to store trailing spaces, which in turn makes the key not
unique.
> For example it allows the following, one with spaces one without:
> ernie 01
> ernie 01
> This in turn causes processing problems in the app.
> How do I fix this problem? BTW, ANSI_PADDING is turned off.
> Thanks,
> Gracie|||What if I can't?
"gracie" wrote:
> I have a situation where the primary key is:
> user_id char(20) not null
> user_cd char(2) not null
> allows users to store trailing spaces, which in turn makes the key not unique.
> For example it allows the following, one with spaces one without:
> ernie 01
> ernie 01
> This in turn causes processing problems in the app.
> How do I fix this problem? BTW, ANSI_PADDING is turned off.
> Thanks,
> Gracie|||Char is a fixed length datatype, it will always pad spaces to length of data declaration with
spaces. Did you mean varchar?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:1CDF624A-3879-4BD9-81F1-01A4043E17D7@.microsoft.com...
> What if I can't?
> "gracie" wrote:
>> I have a situation where the primary key is:
>> user_id char(20) not null
>> user_cd char(2) not null
>> allows users to store trailing spaces, which in turn makes the key not unique.
>> For example it allows the following, one with spaces one without:
>> ernie 01
>> ernie 01
>> This in turn causes processing problems in the app.
>> How do I fix this problem? BTW, ANSI_PADDING is turned off.
>> Thanks,
>> Gracie|||Is it by design that you allow users to put trailing spaces? If not make a
small change in the insert statement on the web page sql statement...
RTRIM(user_id)
Am suggesting this only if you can't change the datatype to varchar.
Thanks
GYK
"gracie" wrote:
> What if I can't?
> "gracie" wrote:
> > I have a situation where the primary key is:
> >
> > user_id char(20) not null
> > user_cd char(2) not null
> >
> > allows users to store trailing spaces, which in turn makes the key not unique.
> >
> > For example it allows the following, one with spaces one without:
> >
> > ernie 01
> > ernie 01
> >
> > This in turn causes processing problems in the app.
> >
> > How do I fix this problem? BTW, ANSI_PADDING is turned off.
> >
> > Thanks,
> > Gracie|||"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:DC0DFC0D-1017-40CD-A5C3-C6BD70224E62@.microsoft.com...
>I have a situation where the primary key is:
> user_id char(20) not null
> user_cd char(2) not null
> allows users to store trailing spaces, which in turn makes the key not
> unique.
> For example it allows the following, one with spaces one without:
> ernie 01
> ernie 01
>
I don't understand.
For char(20)
'ernie'
and
'ernie '
are stored the same, as
'ernie '
and are considered duplicates.
ANSI_PADDING has no effect on non-nullable char columns. ANSI_PADDING
controls whether trailing blanks are trimmed from varchar columns, and
whether nullable char columns are padded to width before being stored (this
is because nullable char columns are actually stored as as varchars).
But in general, I agree with what others have said: don't use for user_id,
use varchar. CHAR is ok for fixed-width coded like user_cd, but for data
which actually varies in length, varchar is better.
David|||Gracie,
I can't reproduce the behavior you describe. Regardless of the data types
or collations on the columns, ('ernie','01') and ('ernie ','01') will
be considered
duplicate keys, since ('ernie' = 'ernie ') and ('01' = '01') is true.
Trailing spaces
are ignored in string comparisons for all string data types and for all
collations,
as far as I know.
Steve Kass
Drew University
gracie wrote:
>I have a situation where the primary key is:
>user_id char(20) not null
>user_cd char(2) not null
>allows users to store trailing spaces, which in turn makes the key not unique.
>For example it allows the following, one with spaces one without:
>ernie 01
>ernie 01
>This in turn causes processing problems in the app.
>How do I fix this problem? BTW, ANSI_PADDING is turned off.
>Thanks,
>Gracie
>

pk & trailing spaces

I have a situation where the primary key is:
user_id char(20) not null
user_cd char(2) not null
allows users to store trailing spaces, which in turn makes the key not uniqu
e.
For example it allows the following, one with spaces one without:
ernie 01
ernie 01
This in turn causes processing problems in the app.
How do I fix this problem? BTW, ANSI_PADDING is turned off.
Thanks,
GracieUse VARCHAR not CHAR.
http://www.aspfaq.com/
(Reverse address to reply.)
"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:DC0DFC0D-1017-40CD-A5C3-C6BD70224E62@.microsoft.com...
> I have a situation where the primary key is:
> user_id char(20) not null
> user_cd char(2) not null
> allows users to store trailing spaces, which in turn makes the key not
unique.
> For example it allows the following, one with spaces one without:
> ernie 01
> ernie 01
> This in turn causes processing problems in the app.
> How do I fix this problem? BTW, ANSI_PADDING is turned off.
> Thanks,
> Gracie|||What if I can't?
"gracie" wrote:

> I have a situation where the primary key is:
> user_id char(20) not null
> user_cd char(2) not null
> allows users to store trailing spaces, which in turn makes the key not uni
que.
> For example it allows the following, one with spaces one without:
> ernie 01
> ernie 01
> This in turn causes processing problems in the app.
> How do I fix this problem? BTW, ANSI_PADDING is turned off.
> Thanks,
> Gracie|||Char is a fixed length datatype, it will always pad spaces to length of data
declaration with
spaces. Did you mean varchar?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:1CDF624A-3879-4BD9-81F1-01A4043E17D7@.microsoft.com...[vbcol=seagreen]
> What if I can't?
> "gracie" wrote:
>|||Is it by design that you allow users to put trailing spaces? If not make a
small change in the insert statement on the web page sql statement...
RTRIM(user_id)
Am suggesting this only if you can't change the datatype to varchar.
Thanks
GYK
"gracie" wrote:
[vbcol=seagreen]
> What if I can't?
> "gracie" wrote:
>|||"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:DC0DFC0D-1017-40CD-A5C3-C6BD70224E62@.microsoft.com...
>I have a situation where the primary key is:
> user_id char(20) not null
> user_cd char(2) not null
> allows users to store trailing spaces, which in turn makes the key not
> unique.
> For example it allows the following, one with spaces one without:
> ernie 01
> ernie 01
>
I don't understand.
For char(20)
'ernie'
and
'ernie '
are stored the same, as
'ernie '
and are considered duplicates.
ANSI_PADDING has no effect on non-nullable char columns. ANSI_PADDING
controls whether trailing blanks are trimmed from varchar columns, and
whether nullable char columns are padded to width before being stored (this
is because nullable char columns are actually stored as as varchars).
But in general, I agree with what others have said: don't use for user_id,
use varchar. CHAR is ok for fixed-width coded like user_cd, but for data
which actually varies in length, varchar is better.
David|||Gracie,
I can't reproduce the behavior you describe. Regardless of the data types
or collations on the columns, ('ernie','01') and ('ernie ','01') will
be considered
duplicate keys, since ('ernie' = 'ernie ') and ('01' = '01') is true.
Trailing spaces
are ignored in string comparisons for all string data types and for all
collations,
as far as I know.
Steve Kass
Drew University
gracie wrote:

>I have a situation where the primary key is:
>user_id char(20) not null
>user_cd char(2) not null
>allows users to store trailing spaces, which in turn makes the key not uniq
ue.
>For example it allows the following, one with spaces one without:
>ernie 01
>ernie 01
>This in turn causes processing problems in the app.
>How do I fix this problem? BTW, ANSI_PADDING is turned off.
>Thanks,
>Gracie
>

pk & trailing spaces

I have a situation where the primary key is:
user_idchar(20) not null
user_cd char(2) not null
allows users to store trailing spaces, which in turn makes the key not unique.
For example it allows the following, one with spaces one without:
ernie01
ernie01
This in turn causes processing problems in the app.
How do I fix this problem? BTW, ANSI_PADDING is turned off.
Thanks,
Gracie
Use VARCHAR not CHAR.
http://www.aspfaq.com/
(Reverse address to reply.)
"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:DC0DFC0D-1017-40CD-A5C3-C6BD70224E62@.microsoft.com...
> I have a situation where the primary key is:
> user_id char(20) not null
> user_cd char(2) not null
> allows users to store trailing spaces, which in turn makes the key not
unique.
> For example it allows the following, one with spaces one without:
> ernie 01
> ernie 01
> This in turn causes processing problems in the app.
> How do I fix this problem? BTW, ANSI_PADDING is turned off.
> Thanks,
> Gracie
|||What if I can't?
"gracie" wrote:

> I have a situation where the primary key is:
> user_idchar(20) not null
> user_cd char(2) not null
> allows users to store trailing spaces, which in turn makes the key not unique.
> For example it allows the following, one with spaces one without:
> ernie01
> ernie01
> This in turn causes processing problems in the app.
> How do I fix this problem? BTW, ANSI_PADDING is turned off.
> Thanks,
> Gracie
|||Char is a fixed length datatype, it will always pad spaces to length of data declaration with
spaces. Did you mean varchar?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:1CDF624A-3879-4BD9-81F1-01A4043E17D7@.microsoft.com...[vbcol=seagreen]
> What if I can't?
> "gracie" wrote:
|||Is it by design that you allow users to put trailing spaces? If not make a
small change in the insert statement on the web page sql statement...
RTRIM(user_id)
Am suggesting this only if you can't change the datatype to varchar.
Thanks
GYK
"gracie" wrote:
[vbcol=seagreen]
> What if I can't?
> "gracie" wrote:
|||"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:DC0DFC0D-1017-40CD-A5C3-C6BD70224E62@.microsoft.com...
>I have a situation where the primary key is:
> user_id char(20) not null
> user_cd char(2) not null
> allows users to store trailing spaces, which in turn makes the key not
> unique.
> For example it allows the following, one with spaces one without:
> ernie 01
> ernie 01
>
I don't understand.
For char(20)
'ernie'
and
'ernie '
are stored the same, as
'ernie '
and are considered duplicates.
ANSI_PADDING has no effect on non-nullable char columns. ANSI_PADDING
controls whether trailing blanks are trimmed from varchar columns, and
whether nullable char columns are padded to width before being stored (this
is because nullable char columns are actually stored as as varchars).
But in general, I agree with what others have said: don't use for user_id,
use varchar. CHAR is ok for fixed-width coded like user_cd, but for data
which actually varies in length, varchar is better.
David
|||Gracie,
I can't reproduce the behavior you describe. Regardless of the data types
or collations on the columns, ('ernie','01') and ('ernie ','01') will
be considered
duplicate keys, since ('ernie' = 'ernie ') and ('01' = '01') is true.
Trailing spaces
are ignored in string comparisons for all string data types and for all
collations,
as far as I know.
Steve Kass
Drew University
gracie wrote:

>I have a situation where the primary key is:
>user_idchar(20) not null
>user_cd char(2) not null
>allows users to store trailing spaces, which in turn makes the key not unique.
>For example it allows the following, one with spaces one without:
>ernie01
>ernie01
>This in turn causes processing problems in the app.
>How do I fix this problem? BTW, ANSI_PADDING is turned off.
>Thanks,
>Gracie
>