March 20, 2015 at 7:04 am
Hello.
I am trying to create a unique index on a table such that the combination of 2 columns is unique.
How do I go about that?
March 20, 2015 at 7:05 am
CREATE UNIQUE INDEX <index name> on <Table Name> (<Column1>, <Column2>)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 20, 2015 at 7:06 am
Thank you
March 20, 2015 at 10:03 am
You'll want to explicitly state the FILLFACTOR and the filegroup, at least, rather than accept whatever the defaults happen to be:
CREATE UNIQUE NONCLUSTERED INDEX <index name>
ON <Table Name> ( <Column1>, <Column2> )
WITH ( FILLFACTOR = 95 ) --chg fillfactor as best matches the table
ON [PRIMARY] --chg to desired filegroup name
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 20, 2015 at 1:57 pm
ScottPletcher (3/20/2015)
You'll want to explicitly state the FILLFACTOR and the filegroup, at least, rather than accept whatever the defaults happen to be:CREATE UNIQUE NONCLUSTERED INDEX <index name>
ON <Table Name> ( <Column1>, <Column2> )
WITH ( FILLFACTOR = 95 ) --chg fillfactor as best matches the table
ON [PRIMARY] --chg to desired filegroup name
FILLFACTOR? Are you serious? Do you really expect from someone who doesn't know the syntax of "create index" to figure out what is the best fillfactor for the table?
I bet you won't be able to predict the best fillfactor for the new index until you get some metrics from prod server. So why do you suggest a newbie a very advanced tuning technique?
March 20, 2015 at 2:11 pm
Alexander Suprun (3/20/2015)
ScottPletcher (3/20/2015)
You'll want to explicitly state the FILLFACTOR and the filegroup, at least, rather than accept whatever the defaults happen to be:CREATE UNIQUE NONCLUSTERED INDEX <index name>
ON <Table Name> ( <Column1>, <Column2> )
WITH ( FILLFACTOR = 95 ) --chg fillfactor as best matches the table
ON [PRIMARY] --chg to desired filegroup name
FILLFACTOR? Are you serious? Do you really expect from someone who doesn't know the syntax of "create index" to figure out what is the best fillfactor for the table?
I bet you won't be able to predict the best fillfactor for the new index until you get some metrics from prod server. So why do you suggest a newbie a very advanced tuning technique?
Anyone that actually creates an index needs to be aware that a fillfactor and filegroup should be specified. I suggested reasonable defaults for an unknown situation. Certainly if possible someone else can help specify the correct values.
But just ignoring them is far worse than understanding that, even though you don't currently know the best values, you need to consider them when creating any index.
Edit: Finally, it's possible the person would know enough about the usage of this table to properly determine the fillfactor even if he/she doesn't know the syntax of a CREATE INDEX command, which is much more specialized and obscure. I don't consider fillfactor particularly advanced; in fact, even some developers can understand it 😀
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply