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.
Showing posts with label key. Show all posts
Showing posts with label key. Show all posts
Friday, March 30, 2012
Friday, March 9, 2012
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:
>
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 messagenews: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 messagenews: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...
>
>
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
> >>
> >>
> >
> >
>
>
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...
>
>
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
>
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
>
Wednesday, March 7, 2012
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
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).
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
>
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
>
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
>
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
>
PK
I have seen some look-up tables have no primary key because there is a null
or " " value in them. Since every table should have a Primary Key, can
anyone educate me how can I handle null or " " value, in the code level? I
am avoiding using Surrogate key.
or " " value in them. Since every table should have a Primary Key, can
anyone educate me how can I handle null or " " value, in the code level? I
am avoiding using Surrogate key.
Thanks,
J
P.S. Null or " " value is "Unknown".The primary key can not be null,or it can not be a primary key.
Add anther Column to this table to create a primary of this column.|||J wrote:
> I have seen some look-up tables have no primary key because there is a null
> or " " value in them. Since every table should have a Primary Key, can
> anyone educate me how can I handle null or " " value, in the code level? I
> am avoiding using Surrogate key.
>
> Thanks,
> J
>
> P.S. Null or " " value is "Unknown".
Nothing technically wrong with ' ' as a key if that makes sense to your
business. If you have nulls then eliminate them either by decomposition
or by encoding the unknown value as something else. There is no excuse
not to have a candidate key.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||J, I suspect that if you have NULL in the colum or a column of what
should be the natural key to a table that you have bad data or a
non-normalized table.
Fixing bad data is usually easier than fixing bad design.
Adding a surrogate key will do nothing to protect the integrity of the
business data in the columns in question.
Good luck
-- Mark D Powell --
Saturday, February 25, 2012
pivot transform help
I need to transform the following layout by hopefully using the pivot transform, but am confused about the editor ......I have a compound primary key that I want to keep intact but then values in the row need to be broken out into their own row.
I need to go from this...
PKcol1 PKcol2 PKcol3 col4 col5 col6 col7
A 2007 1 Y N N N
A 2007 2 Y Y N N
A 2007 3 N N N Y
into this....
A 2007 1 col4 Y
A 2007 1 col5 N
A 2007 1 col6 N
A 2007 1 col7 N
A 2007 2 col4 Y
A 2007 2 col5 Y
A 2007 2 col6 N
A 2007 2 col7 N
A 2007 3 col4 N
A 2007 3 col5 N
A 2007 3 col6 N
A 2007 3 col7 Y
Can I do this using the pivot transform? Any suggestions?
The easiest way to accomplish this will be to transform the compound key into a single column key using the derived column transformation. You would then perform the pivot , then merge the dataflow back to include the compound key.
EDIT: It looks like you want the UNpivot transformation (that which takes you to a more normalized state). see http://technet.microsoft.com/en-us/library/ms141723.aspx
|||
Indeed the unpivot is exactly what I needed. Data flow is now as follows....
OLE DB selects the data
connect to Unpivot
Inside Unpivot editor, my PKcol1-PKcol3 above you check the pass-through box on the right. The other columns (col4-7) you check the other box. Then name the destination column, and put in values if you don't want the "Y" & "N".
Pivot Task Error - Duplicate pivot key
I am using the pivot task to to a pivot of YTD-Values and after that I use derived columns to calculate month values and do a unpivot then.
All worked fine, but now I get this error message:
[ytd_pivot [123]] Error: Duplicate pivot key value "6".
The settings in the advanced editor seem to be correct (no duplicate pivot key value) and I am extracting the data from the source sorted by month.
Could it be a problem that I use all pivot columns (month 1 to 12) in the derived colum transformation and they aren′t available at this moment while data extracting is still going on?
any hints?
Cheers
Markus
The pivot transform takes values like:
cust# Product Qty
-- -
1 Ham 4
1 Chips 2
1 Flan 1
2 Chips 3
2 Beer 19
and produces rows like:
cust# HamQty ChipsQty FlanQty BeerQty
-- - - - -
1 4 2 1 null
2 null 3 null 19
so what to do with input data like this?
cust# Product Qty
-- -
1 Ham 4
1 Chips 2
1 Chips 5
Which value should go into the ChipsQty column 2 or 5?
Most application would want 7, and so we suggest that the pivot be preceded by an aggregate transform to ensure that there is only 1 row for each distinct value of the pivot key. If not, you will see the error you report.
hope this helps
Monday, February 20, 2012
Pivot Key Values
Is it possible to use a expression as a pivot key value?
I need to take data from one table and pivot it into another but what pivots to where is conditional, so I'm wondering if it's possible to say something like
"where Column1 = 'A' and Column2 = 'B' " in the pivot key value.
I'm trying to get it to work but can't, just keep getting the 'No pivot Key found' error.
Thanks
bobbins,
How many conditions are there? You could just use a conditional split to grab the records with the correct values per type and use a seperate chain for each, doing a union all to get them back together after the chain.
--
Looking at it more closely you are probably trying to unpivot (un-normalize). You could possibly use an expression to do the trick. The expression is located on the data flow task and is referenced in a manner similar to Unpivot.Unpivot Input.ColumnName.PivotKeyValue . (NOTE: both pivot and unpivot expose the pivotkeyvalue as data flow property expressions) Although, to be perfectly honest, I'm not sure if you will have access to the record information going through the pipe at the moment or not, so that might not work either...
|||You could pivot the values with a script. Using the script allows you a lot more flexibility in defining the pivoting rules.|||Thanks for the replies, I am trying to de-normalize the data, here is a better explanation of what I'm trying to do:
My source data is an Ingres db on Unix:
I want to put the data in a de-normalized table so it looks like this:
So my data mapping rules are:
Where Code = A and Type = 1 then map to ValueA1
Where Code = B and Type = 2 then map to ValueB2
Where Code = C and Type = 3 then map to ValueC3
I would like to do this as the data comes through the pipe instead of creating a staging table at either the source end or destination and then just bumping the data straight in from the staging table. I have limited experience with SSIS and want to learn but I'm struggling to work out what to do in the time I've been given to do this, hence my question about what you can actually put in the Pivot Key Values. A conditional split will split the values out but how do I put them all back together again as one row per MemberID to go into the destination? Or is there another way to do this?
Thanks again
|||Take a look at this post. http://agilebi.com/cs/blogs/jwelch/archive/2007/05/18/dynamically-pivoting-columns-to-rows.aspx
You'll have to alter the script to not use the Split function, and apply your mapping rules, but it should provide a good starting point. If you are still having problems, post back here and we'll help.
Subscribe to:
Posts (Atom)