.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
david stephan
Gaurav Pal
Post New Web Links

Explicitly defining Clustered Index and NOT NULL constraint on Primary Key column

Posted By:      Posted Date: December 04, 2010    Points: 0   Category :Sql Server

In the following table definition, is it necessary to explicitly define clustered index and NOT NULL constraint on Primary Key column, since defining Primary Key alone will do the job.

create table t1 (
col1 char(4) Not Null Primary Key Clustered,
col2 varchar(20)

View Complete Post

More Related Resource Links

Create unique constraint on a column which has null values

Hi All, I have a table suppose 'Temp' having one of the column as 'ColA' which has some null values as well as non null values. Now i have a requirement to create a unique constraint on it. We have tried but couldnt do it.Apart from having a trigger on insert and update statements is there any other alternate. Can any one please help me on this. Thanks & Regards, Srikanth  

Search for Clustered Index in database on varchar, nvarchar column



I have very big database, i am running a Re-index job and it's keep failing throwing everyday error stating "Online index operation cannot be performed on clustered index as index contains column of data type text, nvarchar.

is there any way i can search and change all such datatype of culstered into non-clustered indexes or any better solution i have.

Primary, Clustered, IDENTITY key index fragmentation


We have a table with an IDENTITY primary key and a clustered index on this key.  The SQL to create the table is shown below.  After inserting 100 records or less (records aren't deleted or updated, typically), the index is highly fragmented.  This eventually becomes a crucial bottleneck because the table is joined to another table with millions of records to produce a view.

It's easy enough to rebuild the index, but it would be optimal to prevent the fragmentation in the first place.


1) What causes the fragmentation? Is it the fact that the table contains a nvarchar(max) field?  Is it the clustered index?

2) Are there ways to prevent fragmentation in the first place, and if so, how?  I have tried setting PAD_INDEX to ON, and using FILLFACTORS of 30, 50, 80, 90, and 100, all to no avail.  I have also tried converting the nvarchar(max) to a nchar(400).

Thank you.

SQL Server Version: 2008

SQL to create the table, index:

CREATE TABLE [dbo].[MyStrings](
    [StringId] [int] IDENTITY(-2147483647,1) NOT NULL,
    [SecondaryKey] [binary](20) NOT NULL,
    [Text] [nvarchar](max) NOT NULL,

Clustered Index & Primary key


I need to understand what is the best practice for me for a Simple Order Entry System.

My Table has two important columns.. Order Number & DATE (When order was placed)

My Order Number is ALWAYS unique so i made it Primary key, there are many others tables which join to this table using Order Number.

Now I use DATE field to return sales within a Date Range...say jan - march orders.

My question is should I change the Primary Key Default Clustered Index on OrderNumber to Primary Key NON CLUSTERED INDEX and then Change DATE to a CLUSTERED INDEX? i want fast performance..please keep in mind orders tables connects to many orders tables and they are joined together by using "Order Number" Column.

Thanks in advance

how to create index for a column


Hi Friends,

                       I need to create the index for a column and accessing that in sqlserver 2005. Please any one help me.


What is the meaning of Ascending\descending sort order of a column within an index

When I add a new index I can also specify the "sort order" as ascending or descending. What does it mean? does it affect performance? does it mean that the binary search tree will be set in a certain way?

Primary Key Constraint Violation on Update

When UPDATING a single column (NOT the primary key column) of a transactionally replicated table, within MS SQL Server Management Studio, the replication monitor gives  Violation of PRIMARY KEY constraint 'PK_my_table. Cannot insert duplicate key in object 'my_table'. Statement Delivery is via the standard SCALL of dbo.sp_MSupd_my_table. The problem correlates with Statement Delivery of DELETE operations. That is, the problem exists if I specify "Do not replicate Delete Statements" and goes away if I specify standard behaviour for delete i.e a call on dbo.sp_MSdel_My_Table dbo.sp_MSupd_my_table does not appear to invoke deletion in any way and Im not updating the primary key value Any ideas Henrietta

Does inserting a non clustered index reduce IO?

I ran a report on my prod server (Performance - Top queries by total IO) and found the following query the 2nd highest.   select * from Table1 where Col1 is null order by Col2, Col3, Col4 Table has 100,000 rows. 1 pk and about 30 columns After running it against engine tuning advisor it advised, if i created a non clustered index on Col1, Col2, Col3 and Col4 it would increase the performance by 77%. But when i run the command just by it self it runs and completes instantaneously. What am i missing? Should i create the index to reduce the IO's as the Advisor suggests?    

Clustered vs NC index storage differences?

Does clustered index takes more disk space than NC for the same filed(s) for large tables? Also is CI takes longer (time) to build than NC if you have large tables? Any microsoft articles supporting these aruguments.

Display Null if Column is empty

is it possible to display Nullif my sql data is null while binding it with gridview in asp.net<asp:GridView ID="GridView2" runat="server" AutoGenerateColumns="False" BackColor="White" BorderColor="#999999" BorderStyle="None" BorderWidth="1px" CellPadding="3" GridLines="Vertical" Height="185px" Width="244px"> <RowStyle BackColor="#EEEEEE" ForeColor="Black" /> <FooterStyle BackColor="#CCCCCC" ForeColor="Black" /> <PagerStyle BackColor="#999999" ForeColor="Black" HorizontalAlign="Center" /> <SelectedRowStyle BackColor="#008A8C" Font-Bold="True" ForeColor="White" /> <HeaderStyle BackColor="#000084" Font-Bold="True" ForeColor="White" /> <AlternatingRowStyle BackColor="Gainsboro" /> </asp:GridView> string command="select * from user1"; DataSet ds1 = new DataSet(); ds1 = ob.getall(command); GridView1.DataSource = ds1; GridView1.DataBind(); for example like this

SSIS 2005 imports column as null

Hi, I am using SSIS 2005 to import excel files to sql server. I have a large excel file with many columns. I have one column -qty that not all rows have data for it-empty. For such rows a another column-value that i need to export is imported as NULL. If column QTY contains a value than column Value is imported fine but if column QTY is blank Value is NULL even if it does have a value. I have played with TypeGuessRows but it doesn't help. Any ideas? Thanks

"String Concatenation": Not appear result Or 'Null v alue' if any column contain Null null value

I write This statement to display FullName of Person SELECT CardID,( FirstName + ':' + FatherName + ':' + GrandFatherName + ':' + FamilyName) as name from PersonalData but I found he dispalay null value if any part of any columns Concatenation contain null value what the exception to make them "solution"

Update nullable column in db to null?

Can anyone reliably get the EDS to save a nullable column to the databse as a "null" when bound to any of the controls such as "FormView"? I have tried using several different UpdateParameters (Session, ControlParamater, Parameter,  etc). I have tried setting "ConvertEmptyStringToNull" to true and leaving the property off entirely. Nothing works. On my "Inserts" it works fine. (I have made sure the column is set to nullable = true in the Entity Designer.....)

How to create nonclustered index which uses function on column?

Hi, Is there any way I can perform following. The ID column is of varchar type.   CREATE NONCLUSTERED INDEX idxID ON Table_1(CAST(ID AS BIGINT) );   Regards, Jigs

Violation of PRIMARY KEY constraint 'PK_droughtDataTable_1'.

 Hello, I have a droughtDataTableAdapter which has the sqlINSERT INTO [droughtDataTable] ([StateName], [Moist], [Drought], [region], [district], [ID]) VALUES (@StateName, @Moist, @Drought, @region, @district, @ID) In the code I have the codeDataSet1TableAdapters.droughtDataTableAdapter k1 = new DataSet1TableAdapters.droughtDataTableAdapter(); for (int a = 0; a <= 254; a++) { k1.FillDrought( k[a][0], double.Parse(k[a][9]), double.Parse(k[a][12]), 1, k[a][1],a); }   However I got an error Violation of PRIMARY KEY constraint 'PK_droughtDataTable_1'. Cannot insert duplicate key in object 'dbo.droughtDataTable'. The question is that I don't have 'droughtDataTable_1', where is from for "_1"? The definition of the table droughtDataTable isStateName nchar(10) Moist float Drought float region smallint district varchar(50) ID int (Primary Key)   Thanks for help.

gridview column index

how can i change column index of girdview ,i have to show button at right hand side

Violation of PRIMARY KEY constraint 'PK_User_Details'. Cannot insert duplicate key in object 'dbo

hi,i'm using sql server 2005 as backend for web development .In one of my database table User_Details, 'user_id' is the primary key ,and it is set to autoincrementing as follows.object count = objUser.countUid(); string count1 = Convert.ToString(count);            if (count1 == "0")            {                string uid5 = "U1";           objUser.addUser(string uid5);}else{               object uid = objUser.selectUid();                string usid = uid.ToString();                string uid2 = usid.Substring(1);                int uid3 = int.Parse(uid2);                uid3 += 1;                string uid4 = usid.Substring(0, 1);          
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend