Welcome to Appsloveworld Technologies , In the session, we learn about what a unique index is and the difference between a unique index and a unique constraint.
Before continuing with the post, I strongly recommend reading read the previous post of this series.
- How does database indexing work with real time example?
- Clustered and Non clustered index in sql server with real example
what’s a unique index?
A unique index is used to enforce the uniqueness of key values in the index. Let’s understand this with an example.
I have the sample SQL script here, which creates table TblUsers.And if you look at the script, the ID column is marked as a primary key column.
And by default, a primary key constraint creates a unique clustered index on that column if another clustered index doesn’t already exist in the table. Let’s look at this in action.
CREATE TABLE [dbo].[TblUsers](
[Id] [int] Primary Key,
[Name] [nvarchar](100) NULL,
[Email] [nvarchar](100) NOT NULL,
[Gender] [int] NOT NULL,
[RewardsPoint] [decimal](18, 2) NOT NULL
Let’s execute the SQL script to create a table, Command completed successfully.
So the table should have been created since we have marked the Id column as the primary key column.
A unique clustered index should have been created on that column for in the table. And obviously, to find that out, we can make use of the system stored procedure,sp_helpindex, passing in the name of the table, which will list all the indexes that are available for this table.
Execute sp_helpindex TblUsers
So let’s execute this. As you can see on the below image we have a unique clustered index on the ID column.
So within the object explorer, refresh the tables folder and you should see the table expand that, expand the index folder and you should see an index that we have just created.
And look at the name it says it’s just a clustered index. It doesn’t tell us whether it’s a unique or non-unique index.
To identify that, you can click on the index, select properties and you should see a unique checkbox checked, which proves that this is a unique clustered index.
How did we get this unique clustered index?
Because we marked the Id column as the primary column. It created this unique clustered index behind the scenes. Since we marked the Id column as a primary key, there should be a primary key as well with that column.
So when I expand the Keys Folder, look at the primary key in the Keys folder. Look at the primary key and look at the index.
They’re actually the same thing, PK__TblUsers and random number.
So in reality, the primary key constraint actually uses a unique index behind the scenes, to enforce, the primary key constraint.
Let’s prove that. Let’s try to drop this index.
And obviously to drop the index we can use the drop index statement drop index. Any time you drop an index you’ll need to specify the table name as well.
Let’s execute the below drop script and see what’s going to happen.
drop index TblUsers.PK__TblUsers__3214EC0799AA7AF9
An explicit drop index is not allowed on this index because it’s being used for primary key constraint enforcement.
So this message proves that the primary key uses a unique index behind the scenes to enforce the constraint.
if you’re not able to drop the index using drop index statement, you can actually do that using the object explorer.
So let’s try to delete this index from the object explorer. Right-click on the index and select delete.
Now refresh the folder, look at what’s happening , the primary key constraint is also gone.
So now if I try to insert duplicates into the table, it will accept the duplicates values.
INSERT [dbo].[TblUsers] ([Id], [Name], [Email], [Gender], [RewardsPoint]) VALUES (1, 'Test 2', 'email@example.com', 0, CAST(15.00 AS Decimal(18, 2)))
INSERT [dbo].[TblUsers] ([Id], [Name], [Email], [Gender], [RewardsPoint]) VALUES (1, 'Test 3', 'firstname.lastname@example.org', 1, CAST(31685.00 AS Decimal(18, 2)))
INSERT [dbo].[TblUsers] ([Id], [Name], [Email], [Gender], [RewardsPoint]) VALUES (1, 'aakankshi6', 'email@example.com', 3, CAST(109.00 AS Decimal(18, 2)))
select * from [dbo].[TblUsers]
this proves that behind the scenes, the primary key constraint actually uses a unique index to enforce that constraint.
So the uniqueness of an index is not a separate index by itself. It is just a property of an index. So both a clustered and a non clustered indexes can be unique.
If I want to create a unique non clustered index, for example, let’s say I want to ensure that in tblusers, no two users can have the same name and email.
Let’s say I want to enforce that, I can actually create a unique non clustered index in that case.
Create Unique NonClustered Index UIX_TblUsers_Name_Email
On TblUsers(Name, Email)
So I’m creating a unique non clustered index. And usually, for unique indexes, it’s better if you prefix that with UIX. So just by looking at the name, you can say that this is a unique index on TblUsers on the name and email columns.
Differences between a unique constraint and a unique index
If you want to enforce uniqueness across one or more columns, we will actually use unique key constraint.but now we are talking about using a unique index.
So what’s the difference between them? Actually, there are no major differences between a unique constraint and a unique index.
- when we add a unique constraint, a unique index gets created behind the pictures.
Let’s look at that in action. let’s say I have the table TblCustomers, we just got a Country column on that.
Now I want to ensure that, all countries are unique within the table. How do I do that? I can add a unique constraint. So let’s add a unique constraint. so obviously to add a unique constraint, we have to alter the table.
ALTER TABLE TblCustomers
ADD CONSTRAINT UQ_TblCustomers_Country
UNIQUE NONCLUSTERED (Country)
Let’s execute the command, and refresh the table folder expand the “keys” folder, and then expand the indexes folder.
Now look at this, we have index created and if you look at the type of the index, it’s a non clustered unique index.
If you want to create a clustered index, you can specify that as well
ALTER TABLE TblCustomers
ADD CONSTRAINT UQ_TblCustomers_Country
UNIQUE CLUSTERED (Country)
so whether you add a unique constraint or whether you create an index, there are two ways to create a unique index by adding a unique constraint or using create index statement and directly creating a unique index.
So eventually to actually be creating a unique constraint over a unique index. Now we know that behind the scenes, a unique index gets created. Now you can do that through a constraint or directly using a create index statement.
when do we choose one option over the other?
Now to make our intentions clear, create a unique constraint when data integrity is the objective. this makes the objective of that index very clear.
In either case, this data is validated in the same manner and the query optimizer doesn’t really make any difference between a unique index that you have created by using a constraint or manually created that creates index statement.
Some useful points to remember
- we have already learned that by default, a primary key constraint creates a unique clustered index.
- we have also seen a unique constraint creates a unique non clustered index by default. these are only defaults and they can be changed if we wish to.
The post Sql Server- Difference between unique and non unique index appeared first on Software Development | Programming Tutorials.
Read More Articles
- Linq to SQL Group by and Sum in Select | Linq To SQL Group By and Sum
- How send an HTTP POST request to a server from Excel using VBA?
- What is Difference between where and having clauses in SQL server
- How to Use EXCEPT Operator with an example in Sql
- How to use merge statement in the SQL server
- How to write Re-runnable SQL server query
- How to create Cursor in sql server with example
- How to generate random data in sql server for performance testing
- How to write subquery in select statement in Sql
- How to Log record changes in SQL server in an audit table
- Get Month and YTD Level Share ( both Value , Volume) .Share formula - Comp / Industry
- Insert PictureBox Image into Sql Server database
- SQL Server Management Studio Display Database Diagrams (ER) Permissions
- Elastic Search SQL Server
- Does VB.Net DateTime match up to SQL DateTime?
- How I can swap two values in same column with dynamic SQL?
- Modeling an N-level tree in SQL Server with cascading deletes
- Query to pull second smallest id
- Inserting UTF-32 characters
- System.IndexOutOfRangeException when trying to return 'select count (Id)'
- How to sum multiple rows in SQL which has different values in the adjacent column
- When to use Stored Procedures instead of using any ORM with programming logic?
- How to add PostDeploy script during Schema Compare generate script
- SSIS - 'Cannot insert the value null' even though Identity Column is set
- Convert OUTER APPLY to LEFT JOIN
- How to copy indexes from one table to another in SQL Server
- How can I use the group by Clause in a subquery
- Upgrading SQL Server 2000 to 2005 or 2008 - DTS to SSIS
- what are the possible problems with CLR integration in Sqlserver
- Foreign key to table A and B, where A already have a foreign key to B
- Entity Framework 6 in Mono 3.0.10 connecting SqlServer throws a Provider Incompatible Exception
- Unable to connect to SQL Server 2014 remotely
- DATEADD with part days
- SQL Server query XML xpath empty namespace
- How to alter a column's attribute using sql script
- Passing two strings into a stored procedure and splitting them via specific character to perform a query
- How to Select continuous date in sql
- Grouping ID while counting specific attribute values
- Average of difference between two dates using Hibernate JPA in SQL Server
- GROUP BY and ORDER BY date as month
- Create a SqlGeography polygon-circle from a center and radius
- Selecting the sum of multiple rows as a value in larger query
- How to attach database from winforms using C#
- Sql server bulk load into a table
- sorting by DATE string MSSql
- System.OverflowException when running dtsserver.getrunningpackages()
- SQL data error: The conversion of a varchar data type to a datetime data type resulted in an out-of-range value
- Compile all values across multiple delimited strings into a table
- Nested SELECT clause in SQL Compact 3.5
- Computed Column Specification in SQL Server - Time Difference in hours or minutes
- Unable to access database on different subnet from IIS application
- How to store PathGeometry in (SQL) database?
- Why doesn't this Update fail? is this intended behavior?
- Difference between values in the same column between max and min dates?
- PKI-based x.509 certificate authentication with SQL Server?
- Checking Constraints SQL
- Counting if data exists in a row
- What is the fastest way to get 1,000,000 lines of fixed width text into a SQL Server database?
- Unknown number of child and grandchild elements in an XQuery
- Superset - SQL Server Analysis Services Compatibility
- Stubborn Ampersand with Invoke-Webrequest SQL & Powershell
- DBMS-independent queries
- Dynamic SQL with variable number of table joins
- nearest future date from given date and frequency
- Using RTRIM in JOIN condition
- len of varbinary 2
- num_rows() function is not working for MSSQL in codeigniter
- SSRS graph expression - custom week filter/expression
- Insert data into SQL Server using VB .NET
- UWP service for accessing SQL Server
- How to test if a SQL Server 2008 database can serve 200 concurent users
- Create Excel file from C# SQL
- Convert varchar to datetime on SQL Server with custom format
- UPDATE Statement for latitude and longitude based on address in the same table
- Skipping perfectly identical records in SQL Server
- Determine which table was updated in database level trigger
- What is the best way to delete a joined association to a stored file in a database?
- Dynamically change the ServerDB name in SSIS connection manager
- Can fulltext search work if the document type's value is empty in SqlServer?
- SQL Pivot function to obtain top notes field
- Reading last inserted UID from database using SQL Command
- How to connect to SQL Server 2000 database from Visual Studio 2012
- Running Total (pre 2012) for more than 1 group
- SQL Database design for huge datasets
- How to get count of two-way combinations from two columns?
- Add data from table to other Table SQLSERVER
- MyBatis Callable with SQL temp variable
- Create dummy rows to fill missing values into a SQL Server table
- SSIS Package error- SSIS Error Code DTS_E_PROCESSINPUTFAILED
- Database design for website (CMS) consideration & suggestion for database driven menus
- Sql where clause to return everything in case filter is null
- SQL query ignores "not between"
- Classic ASP cannot execute stored procedure
- PDOException: SQLSTATE[IMSSP]: Tried to bind parameter number 65536. SQL Server supports a maximum of 2100 parameters
- Read error with spark.read against SQL Server table (via JDBC Connection)
- Sql query to find subtotal of distinct column and grand total from a single table?
- How to see Partitions created in SSMS on a table & use them in SSAS to process the Model
- SQL Server Integration Services Merge Join not working for multiple rows
- Update var binary to existing SQL columns (Add to existing image)?
- SQL Server : update rows based other rows in the same table
- sql server 2008 Deadlock in two parallel transactions
- What is the proper way to dynamically create and call a stored procedure in Delphi using FireDac?
- Why is SQLServerDriver gone from Slick 2.0.0-M3 to Slick 2.0.0?
- Generate random tokens (unique id's) for users in a table in SQL server
- How to prevent Hibernate formula annotation of adding table name before reserved words?
- SQL Server Insert JSON data with other external parameters
- To find records which have same
- Can I create a dummy INSTEAD OF UPDATE, INSERT trigger so I can use MERGE with INSTEAD OF DELETE?
- Unicode T-SQL like not returning expected all records
- Change rows to columns in SQL
- Find without using lead lag and CTE missing value from a list
- SQL Server: If I stop a single long running update before it is finished, will it roll back automatically?
- How to join linked server tables with openquery
- Update SQL server xml column using XQUERY?
- SSIS Package Creation via MVS 2015 ODBC IDENTITY_INSERT
- sql - first record if the record is one of a duplicate set
- Increase search performance in sql server?
- COUNT(NULL) and the IN clause
- SQL Grouping in Pivot table
- stored procedure return .00 decimal
- Query: How to find erroneous components against a required items list
- Getting individual count of insert and update using merge statement - C# and SQL Server
- How to convert fields into rows through SQL in MS Access 2007 or MS SQL Server 2005
- How to give a unique constraint to a combination of columns in SQL irrespective of Order
- SQL Server: User-Define Function w/Table Parameters Filtering
- How to Split the address string, House number and Street Name using SQL?
- What is disadvantage of using CTE?
- How it is possible to create clustered index on a view
- How to reverse the order of a column
- How to Remove Titles and Suffixes from "Last Name" column?
- Comparing two databases
- Add concatenated column to temp table when I don't have ADD permission
- SQL: break up one row into many (normalization)
- SQL Server: String vs Binary?
- How to pass two values to a single parameter in SQL Server stored procedure?
- Data localization in SQL Server with GUIDs or...?
- How to sum two columns of different tables in sql
- SQL Server : how to return several row into one variable
- SQL 2014 - SSIS or Stored procedures to copy data from SQL Server to SQL Server with same table structure
- Why is the toolbox empty on VS 2010 shell used by SQL Server data tools?
- How do I install the SQL ASCMDLETS module?
- can a multi column fk reference multiple tables?
- Stream aggregation in the execution plan with distinct clause
- How to configure vsdiffmerge tool as external diff/merge tool for VersionSql in SQL Server Management Studio?
- How do you add a NOT NULL FOREIGN KEY column to an existing (populated) table in MS SQL?
- Autoincrement version per Foreign Key SQL Server
- Set Column Value based on Other Column in SQL
- Not Equal does not work in SQL Query
- how to pivot a simple joined data set?
- SQL Delete with group by clause
- Fastest way to find distinct matching records
- SQL Server Management Studio - Adding/Moving Columns require drop and re-create?
- My select command doesn't work
- SQL Server stored procedure performance impact
- Accessing Existing Data in a SQL Server Table with Django
- using sql server 2005 synonyms for table causes dynamic query generation errors
- sql server - check to see if cast is possible
- SSIS How to load a single member with multiple rows.
- Get remote server version MS SQL