• Home
  • SQL Server
    • Articles
    • T-SQL Puzzles
    • Output Puzzles
    • Interview Questions
    • Performance Tuning
    • SQL SERVER On Linux
    • Resources
  • SSRS
    • SSRS Articles
    • Interview Questions
  • SSAS
    • SSAS Articles
    • DAX
  • SQL Puzzles
  • Interview Questions
    • SQL Interview Questions
    • Data Interview Questions
  • Python Interview Puzzles
  • New Features(SQL SERVER)
    • SQL SERVER 2017
    • SQL SERVER 2016
    • SQL SERVER On Linux
  • Social
    • Expert Exchange
      • Top Expert in SQL
      • Yearly Award
      • Certifications
      • Achievement List
      • Top Expert of the Week
    • HackerRank (SQL)
    • StackOverflow
    • About Me
      • Contact Me
      • Blog Rules

Improving my SQL BI Skills

Improving my SQL BI Skills

Daily Archives: June 3, 2015

SQL SERVER Indexes – ALL (Covers almost everything) [Continued – Page 4]

03 Wednesday Jun 2015

Posted by Pawan Kumar Khowal in Download SQL Interview Q's, SQL Concepts, SQL Server Interview Questions

≈ Leave a comment

Tags

2.NonClustered Index, Advanced SQL tutorial pdf, change fill factor while creating NonClustered Index, clustered and non clustered index in sql, Clustered and Nonclustered Indexes Described, Clustered Index, clustered index in sql, Clustered Index vs. Non-Clustered Index in SQL Server, Clustered versus Non Clustered Index, Clustered vs. Nonclustered Index Structures in SQL Server, Covering Index, Covering Index in SQL, Difference Between Clustered and Non-Clustered Indexes, Difference between clustered and nonclustered index, difference between clustered and nonclustered index in sql, difference between clustered and nonclustered index in sql server with example, Differences between Clustered Index Vs NonClustered Index, Differences between Clustered Index Vs Primary Key, Download SQL Questions, Download SQL Server Interview Question in PDF, Download SQL SERVER Interview questions, Download SQL Server Interview questions and answers, download sql server interview questions and answers pdf, download sql server interview questions by Pawan Khowal, download sql server interview questions by Pawan Kumar, download sql server interview questions by Pawan Kumar Khowal, Download T-SQL Interview Questions, Fill factor in Indexes, Filtered Index, Free Download SQL SERVER Interview questions, Indexes in detail, Learn Indexes in detail, NonClustered Index with Included columns, SQL, SQL Common Interview Questions, SQL Common Interview Questions and answers, SQL FAQ, SQL FAQs, SQL Interview Q & A, SQL Interview Questions, SQL Queries asked in interviews, SQL Questions, SQL Server - General Interview Questions and Answers, SQL Server developer Interview questions and answers, SQL Server developer Interview questions with answers, SQL SERVER Indexes, SQL SERVER Interview questions, SQL SERVER Interview questions & Answers, SQL Server Interview questions and answers, SQL Server Interview Questions and Answers - Free PDF, sql server interview questions and answers for net developers, SQL SERVER Tips, SQL Tips & Tricks, SQL Tips and Tricks, SQL Tricks, T-SQL Server Interview Questions, Types of Indexes, Types of Indexes in SQL


SQL SERVER Indexes – ALL (Covers almost everything) [Continued – Page 4]

https://msbiskills.com/

Download – SQL SERVER Indexes Interview Notes – Pawan Kumar Khowal

Differences between Clustered Index Vs NonClustered Index

Clustered Index NonClustered Index
Here data is physically sorted based on the Key columns Here data is not physically sorted based on the key columns
At leaf level you will get entire data means data for all columns. Even if you create clustered index on a single column all the columns will be available at leaf level. Here at leaf level you will get Key value and row locator. For a clustered table, the row locator is the clustered index key. For a heap, a row locator is a RID pointer to the row.
We can have only 1 clustered index per table We can have 999 NonClustered index per table.
Example for Clustered Index is given below-


--

CREATE TABLE testPrimClus4
(
	ID INT
)
GO

CREATE UNIQUE CLUSTERED INDEX Ix_Clx ON testPrimClus4(ID)

--

Example for NonClustered Index is given below-


--

CREATE TABLE testPrimClus4
(
	ID INT
)
GO

CREATE UNIQUE NONCLUSTERED INDEX Ix_NonCl ON testPrimClus4(ID)

--

Differences between Clustered Index Vs Primary Key

Clustered Index Primary Key
Clustered index will create only Index on the table. It will not create constraint on the table. Primary key internally creates 2 objects. They are Index and Primary Key constraint. Both Index and Primary Key constraint can be clustered or non-clustered depending on what you have written in primary key definition. If you don’t specify anything then Unique clustered index will be created and a Primary key constraint (clustered) will be created.
Here if you create non unique clustered index you can insert multiple null values. If you create unique clustered index you can insert single null value. We cannot insert null values if we have Primary key on the table. You will get below error if you try. Msg 515, Level 16, State 2, Line 6 Cannot insert the value NULL into column ‘ID’, table ‘Pawan.dbo.testPrimClus1’; column does not allow nulls. INSERT fails. The statement has been terminated.
We can also add Clustered index after table creation using create index command. We don’t need to alter table in this case. E.g.


--

CREATE TABLE testPrimClus4
(
	ID INT
)
GO

CREATE UNIQUE CLUSTERED INDEX Ix_Clx ON testPrimClus4(ID)

--

We can add primary key after that creating table using below alter command. Please note that we need to first drop existing constraints. Also primary key column should be non-null. E.g.


--

CREATE TABLE testPrimClus113
(
	ID INT NOT NULL
)
GO

ALTER TABLE testPrimClus113 ADD PRIMARY KEY (ID)

--

Share this

  • LinkedIn
  • Facebook
  • Twitter
  • WhatsApp
  • Email

SQL SERVER Indexes – ALL (Covers almost everything) [Continued – Page 3]

03 Wednesday Jun 2015

Posted by Pawan Kumar Khowal in Download SQL Interview Q's, SQL Concepts, SQL SERVER, SQL Server Interview Questions

≈ Leave a comment

Tags

2.NonClustered Index, Advanced SQL tutorial pdf, change fill factor while creating NonClustered Index, clustered and non clustered index in sql, Clustered and Nonclustered Indexes Described, Clustered Index, clustered index in sql, Clustered Index vs. Non-Clustered Index in SQL Server, Clustered versus Non Clustered Index, Clustered vs. Nonclustered Index Structures in SQL Server, Covering Index, Covering Index in SQL, Difference Between Clustered and Non-Clustered Indexes, Difference between clustered and nonclustered index, difference between clustered and nonclustered index in sql, difference between clustered and nonclustered index in sql server with example, Download SQL Questions, Download SQL Server Interview Question in PDF, Download SQL SERVER Interview questions, Download SQL Server Interview questions and answers, download sql server interview questions and answers pdf, download sql server interview questions by Pawan Khowal, download sql server interview questions by Pawan Kumar, download sql server interview questions by Pawan Kumar Khowal, Download T-SQL Interview Questions, Fill factor in Indexes, Filtered Index, Free Download SQL SERVER Interview questions, Indexes in detail, Learn Indexes in detail, NonClustered Index with Included columns, SQL, SQL Common Interview Questions, SQL Common Interview Questions and answers, SQL FAQ, SQL FAQs, SQL Interview Q & A, SQL Interview Questions, SQL Queries asked in interviews, SQL Questions, SQL Server - General Interview Questions and Answers, SQL Server developer Interview questions and answers, SQL Server developer Interview questions with answers, SQL SERVER Indexes, SQL SERVER Interview questions, SQL SERVER Interview questions & Answers, SQL Server Interview questions and answers, SQL Server Interview Questions and Answers - Free PDF, sql server interview questions and answers for net developers, SQL SERVER Tips, SQL Tips & Tricks, SQL Tips and Tricks, SQL Tricks, T-SQL Server Interview Questions, Types of Indexes, Types of Indexes in SQL


SQL SERVER Indexes – ALL (Covers almost everything) [Continued – Page 3]

https://msbiskills.com/

Download – SQL SERVER Indexes Interview Notes – Pawan Kumar Khowal

Filtered Index

1 If you add a where clause to a NonClustered index it is called Filtered Index.
2 When you create a NonClustered index, you can add a WHERE clause to reduce the number of rows that are stored at the leaf level.
3 If we have fewer rows in an index then less I/O will be used and it will improve query performance.
4 Also the size of the index will be smaller since we have fewer rows in the index. Hence less space will be used by the Index.
5 Filtered indexes are excellent for performance if you have a value that is used in a where clause very frequently and that value is only a small amount of the total values for that table.
6 The example of Filtered index is given below.

--

CREATE TABLE testFilterIndexes
(
	 ID INT
	,Name VARCHAR(50)
	,OrderStatus BIT --can be 0,1 OR NULL
)
GO

CREATE NONCLUSTERED INDEX Ix_Filx ON testFilterIndexes(OrderStatus)
WHERE OrderStatus IN ( 0 , 1 )

SELECT OrderStatus FROM testFilterIndexes
WHERE OrderStatus = 0 OR OrderStatus = 1

Output
---------

--

FilteredIndex

7 You cannot use OR in filtered indexes. Please check out the example below.

--

CREATE NONCLUSTERED INDEX Ix_Filx1 ON testFilterIndexes(OrderStatus)
WHERE OrderStatus =  0 OR  OrderStatus =  1

Output
--------

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'OR'.

--

8 Please check out some sample Filtered Indexes below. We can use AND, IN, NULL, NOT NULL.

--

CREATE NONCLUSTERED INDEX Ix_Filx1 ON testFilterIndexes(OrderStatus)
WHERE OrderStatus =  0 AND OrderStatus =  1

CREATE NONCLUSTERED INDEX Ix_Filx2 ON testFilterIndexes(OrderStatus)
WHERE OrderStatus IN ( 0 , 1 )

CREATE NONCLUSTERED INDEX Ix_Filx3 ON testFilterIndexes(OrderStatus)
WHERE OrderStatus IS NOT NULL

CREATE NONCLUSTERED INDEX Ix_Filx4 ON testFilterIndexes(OrderStatus)
WHERE OrderStatus IS NULL

---

9 You cannot use BETWEEN, NOT IN, or a CASE statement with Filtered Indexes.
10 The query optimizer won’t use filtered indexes if you’re using local variables or parameterized SQL. Use the way we have used our dynamic parameterized queries given below.

--

SELECT OrderStatus FROM testFilterIndexes
WHERE OrderStatus = 0

DECLARE @SQL NVARCHAR(MAX), @OrderStatus INT 

SET @OrderStatus = 0 

SET @SQL = N' SELECT OrderStatus FROM testFilterIndexes WHERE OrderStatus =' + CAST(@OrderStatus AS VARCHAR(10)) 

EXECUTE sp_executesql @SQL

--

Final Say-

Indexes are very easy to add to your database to improve performance. However, too much of an indexes can be bad as we have to pay their maintenance cost. When designing a database, or troubleshooting poor performing query, consider all your indexes carefully and test them thoroughly.

Share this

  • LinkedIn
  • Facebook
  • Twitter
  • WhatsApp
  • Email

SQL SERVER Indexes – ALL (Covers almost everything) [Continued – Page 2]

03 Wednesday Jun 2015

Posted by Pawan Kumar Khowal in Download SQL Interview Q's, SQL Concepts, SQL Server Interview Questions

≈ 1 Comment

Tags

2.NonClustered Index, Advanced SQL tutorial pdf, change fill factor while creating NonClustered Index, clustered and non clustered index in sql, Clustered and Nonclustered Indexes Described, Clustered Index, clustered index in sql, Clustered Index vs. Non-Clustered Index in SQL Server, Clustered versus Non Clustered Index, Clustered vs. Nonclustered Index Structures in SQL Server, Covering Index, Covering Index in SQL, Difference Between Clustered and Non-Clustered Indexes, Difference between clustered and nonclustered index, difference between clustered and nonclustered index in sql, difference between clustered and nonclustered index in sql server with example, Download SQL Questions, Download SQL Server Interview Question in PDF, Download SQL SERVER Interview questions, Download SQL Server Interview questions and answers, download sql server interview questions and answers pdf, download sql server interview questions by Pawan Khowal, download sql server interview questions by Pawan Kumar, download sql server interview questions by Pawan Kumar Khowal, Download T-SQL Interview Questions, Fill factor in Indexes, Free Download SQL SERVER Interview questions, Indexes in detail, Learn Indexes in detail, NonClustered Index with Included columns, SQL, SQL Common Interview Questions, SQL Common Interview Questions and answers, SQL FAQ, SQL FAQs, SQL Interview Q & A, SQL Interview Questions, SQL Queries asked in interviews, SQL Questions, SQL Server - General Interview Questions and Answers, SQL Server developer Interview questions and answers, SQL Server developer Interview questions with answers, SQL SERVER Indexes, SQL SERVER Interview questions, SQL SERVER Interview questions & Answers, SQL Server Interview questions and answers, SQL Server Interview Questions and Answers - Free PDF, sql server interview questions and answers for net developers, SQL SERVER Tips, SQL Tips & Tricks, SQL Tips and Tricks, SQL Tricks, T-SQL Server Interview Questions, Types of Indexes, Types of Indexes in SQL


SQL SERVER Indexes – ALL (Covers almost everything) [Continued – Page 2]

https://msbiskills.com/

Download – SQL SERVER Indexes Interview Notes – Pawan Kumar Khowal

Other Index Types

  1. NonClustered Index with Included columns
  2. Covering Index

NonClustered Index with Included columns

1 A NonClustered index can be extended by including nonkey columns in addition to the index key columns. The nonkey columns are stored at the leaf level of the index b+ Tree.
2 The general syntax of a Non Clustered Index with Included column is given below-CREATE INDEX [Index_Name] ON table_name (KeyColumns) INCLUDE (NonKeyColumns)
3 KeyColumns – These columns are used for row restriction and processing E.g they were used in WHERE, JOIN, ORDER BY, GROUP BY etc.
4 NonKeyColumns – These columns are used in SELECT and Aggregation. For e.g. AVG(col) after selection/restriction.
5 So always choose KeyColumns and NonKeyColumns based on the query requirements only
6 Please note that you cannot add a column in Key column section and a NonKey column section.  It is either a key column or a non-key, included column.
7 A nonkey column will only be stored at the leaf level, however. There are some benefits of using non-key columns Columns can be accessed without extra lookups (RID lookup / Clustered Key lookup). It will reduce IO operation and improve performance of queries.
8 Included columns do not count against the 900 byte index key limit enforced by SQL Server.
9 Data types not allowed in key columns are allowed in nonkey columns. All data types but text, ntext, and image are allowed.
10 Included columns do not count against the 900 byte index key limit enforced by SQL Server. Please check out the above example where we have a column in Include column with size greater than 900 byte.
11 Example of NonClustered Index with Included column is given below-

--

--Create table
CREATE TABLE testNonClusIncludedColsIndexes
(
	 ID INT
	,Name VARCHAR(50)
	,OrderStatus BIT --can be 0,1 OR NULL
)
GO

--Insert some rows
INSERT INTO testNonClusIncludedColsIndexes VALUES(1,'Pawan',1),(2,'Isha',0)

--Now create a normal nonclustered index
CREATE NONCLUSTERED INDEX Ix_NonCls ON testNonClusIncludedColsIndexes(Name)

--Execute the below query
SELECT Name,OrderStatus FROM testNonClusIncludedColsIndexes
WHERE Name = 'Pawan'

--Now here you can see that even you have NonClusteredIndex still table scan is used. Now let’s drop the above index and create NonClusteredIndex with Included column.

DROP INDEX Ix_NonCls ON testNonClusIncludedColsIndexes

CREATE NONCLUSTERED INDEX Ix_NonClsIncluded ON testNonClusIncludedColsIndexes(Name) INCLUDE(OrderStatus)

--Execute the below query and check the execution plan.

--Now execute and check the execution plan

SELECT Name,OrderStatus FROM testNonClusIncludedColsIndexes
WHERE Name = 'Pawan'

--

Output below in the screen shot

--

NCIWithIncludedColumn

Covering Index

1 In covering index all columns returned in a query are in the available in the index, so no additional reads are required to get the data.
2 A covering index will reduce the IO operations and improve performance of queries.
3 Please find the example of covering index below.

--

CREATE TABLE testCoveringIndexes
(
	 ID INT
	,Name VARCHAR(50)
	,OrderStatus BIT -- can be 0,1 OR NULL
)
GO

--Insert some rows
INSERT INTO testCoveringIndexes VALUES(1,'Pawan',1),(2,'Isha',0),(2,'Isha',0),(2,'Nisha',1),(2,'Isha',0)

CREATE NONCLUSTERED INDEX Ix_Covering ON testCoveringIndexes(Name,OrderStatus)

SELECT Name,OrderStatus FROM testCoveringIndexes
WHERE Name = 'Isha' AND OrderStatus = 0

Output
---------

--

CoveringIndex

4 Now let’s check whether can create index size greater than 900 bytes. NOTE – yes we can but a warning will come.

--

CREATE TABLE Pawan
(
	 ID INT
	,Name VARCHAR(100)
	,Addre VARCHAR(1000)
)
GO

CREATE UNIQUE NONCLUSTERED INDEX Ix_CoveringInx ON Pawan(Name,Addre)

Output
---------
Warning! The maximum key length is 900 bytes. The index 'Ix_CoveringInx' has maximum length of 1100 bytes. For some combination of large values, the insert/update operation will fail.

--

Share this

  • LinkedIn
  • Facebook
  • Twitter
  • WhatsApp
  • Email

SQL SERVER Indexes – ALL (Covers almost everything)

03 Wednesday Jun 2015

Posted by Pawan Kumar Khowal in Download SQL Interview Q's, SQL Concepts, SQL SERVER, SQL Server Interview Questions

≈ Leave a comment

Tags

2.NonClustered Index, Advanced SQL tutorial pdf, change fill factor while creating NonClustered Index, clustered and non clustered index in sql, Clustered and Nonclustered Indexes Described, Clustered Index, clustered index in sql, Clustered Index vs. Non-Clustered Index in SQL Server, Clustered versus Non Clustered Index, Clustered vs. Nonclustered Index Structures in SQL Server, Difference Between Clustered and Non-Clustered Indexes, Difference between clustered and nonclustered index, difference between clustered and nonclustered index in sql, difference between clustered and nonclustered index in sql server with example, Download SQL Questions, Download SQL Server Interview Question in PDF, Download SQL SERVER Interview questions, Download SQL Server Interview questions and answers, download sql server interview questions and answers pdf, download sql server interview questions by Pawan Khowal, download sql server interview questions by Pawan Kumar, download sql server interview questions by Pawan Kumar Khowal, Download T-SQL Interview Questions, Fill factor in Indexes, Free Download SQL SERVER Interview questions, Indexes in detail, Learn Indexes in detail, SQL, SQL Common Interview Questions, SQL Common Interview Questions and answers, SQL FAQ, SQL FAQs, SQL Interview Q & A, SQL Interview Questions, SQL Queries asked in interviews, SQL Questions, SQL Server - General Interview Questions and Answers, SQL Server developer Interview questions and answers, SQL Server developer Interview questions with answers, SQL SERVER Indexes, SQL SERVER Interview questions, SQL SERVER Interview questions & Answers, SQL Server Interview questions and answers, SQL Server Interview Questions and Answers - Free PDF, sql server interview questions and answers for net developers, SQL SERVER Tips, SQL Tips & Tricks, SQL Tips and Tricks, SQL Tricks, T-SQL Server Interview Questions, Types of Indexes, Types of Indexes in SQL


SQL SERVER Indexes – ALL (Covers almost everything)

https://msbiskills.com/

Download – SQL SERVER Indexes(Almost Everything) Interview Notes – Pawan Kumar Khowal

Index – Important Points

1 An index is a structure stored on the disk. This structure is called B+ Trees.
2 An index can only be created on a table or view.
3 They speeds retrieval of rows from the table or view.
4 An index contains keys built from one or more columns in the table or view.
5 These helps SQL Server to find the row or rows associated with the key values quickly and efficiently.
6 B+ Trees are used to store indexes in SQL SERVER.
7 B+ Tree structure always contains at least 2 levels. Root and leaf level. Number of intermediate levels can vary.
8 Every index will take some space in your database.
9 Index will be modified when DML (Insert, update and delete) operation(s) are performed. This means reordering B+ Tree occurs every time the index changes (Updates, Inserts, and Deletes).
10 Index size can be maximum of 900 bytes.

Update from Expert – Kind of. It’s really that the KEY of an index can be 900 bytes OR 16 columns – whichever comes first. But, even that’s not always true. If a table has an XML column then the PK can have a maximum of 15 columns AND the PK *must* be the CL key.

11 If the table is too small then don’t create indexes on it. In this case we will be wasting our resources because table scan will be faster than index scan.

Update from Expert – Every table should have indexes – even small tables. Indexes can be used to enforce uniqueness and allow for point queries (which do not require index scans). Having said that, I suppose the only case would be a table that has only one page. But, you don’t really care about performance for that table anyway (as far as access). What you might care about there is locking. Indexes can be beneficial to allow SQL Server to lock only the necessary row(s).

12 You can use ALT + F1 to check out what all indexes are created on a table.
13 Indexes are a lot of “trial and error” thing depending on database design, SQL queries, and database size.
14 Sample B+ Tree is given below

Types of Indexes

  1. Clustered Index
  2. NonClustered Index

Clustered Index

1 Clustered index sort and store the data rows based on their key values. These are the columns included in the index definition.
2 We can have only one clustered index per table. We cannot have more than one clustered index per table.
3 Because data rows can be sorted in only one order.
4 At leaf level you will get entire data. Means even if you create clustered index on a single column , at leaf level you will get all the columns means actual data.
5 Clustered Index will be modified when DML (Insert, update and delete) operation(s) are performed. This means reordering B+ Tree occurs every time the index changes (Updates, Inserts, and Deletes). Here physical ordering of the data changes after each DML operation.
6 The CL index leaf level will have ALL columns of the table in it.
7 If a table has Clustered Index it is called Clustered Table.
8 If a table does not contain Clustered Index, then it is stored in Heap. Heap is an unordered structure.
9 Clustered index can be unique or non-unique.
10 If you create primary key, SQL Internally creates unique clustered index and primary key constraint. Primary key is basically a constraint used to enforce uniqueness in a table. The primary key columns cannot hold NULL values. Please check out the example below.

Update – It’s really that the PK defaults to being enforced by a unique clustered index. None of the columns that make up the primary key can allow nulls (or, be nullable). However, if you have a unique constraint then ALL of the columns that make up the unique constraint can allow nulls but no more than one row can be NULL for all columns. With a PK NONE of the columns can allow NULL.

11 If you just create a unique clustered index, one null value will be allowed in the table.
12 Note – Do not use GUID as Clustered Index. It will create fragmentation issues.
13 Don’t create Clustered Index blindly. It should be created based on the business need. Try to make Clustered key’s size small and if possible integer type. Other simple rule is column should be somewhat ordered and frequently queried column. For e.g. EmplD in Employee table. Also please note that these are just simple tips, there might be scenarios where you to choose different data type or multi column clustered key. So all in all I can say choose wisely.
14 We can create Clustered Index with multiple columns also. These types of indexes are called Composite Index.
15 The example of clustered index is given below.

--

--OPTION 1 -- This is the first way by which we can create clustered index (Primary Key Way)

CREATE TABLE testClusteredIndex
(
	 ID INT PRIMARY KEY
	,Name VARCHAR(50)
	,OrderStatus BIT --can be 0,1 OR NULL
)
GO

SELECT ID,Name,OrderStatus FROM testClusteredIndex
WHERE ID = 1

Output

---------------
Screen shot below

--

CL1


--OPTION 2 -- Explicitly creating CLustered Index using Create Index command

CREATE TABLE testClusteredIndex
(
ID INT
,Name VARCHAR(50)
,OrderStatus BIT --can be 0,1 OR NULL
)
GO

CREATE UNIQUE CLUSTERED INDEX Ix_Cls ON testClusteredIndex(ID)

SELECT ID,Name,OrderStatus FROM testClusteredIndex
WHERE ID = 1

Output

---------------

Screen shot below

---------------

--

Clustered Index

Clustered Index

16 We can change fill factor while creating Clustered Index. See example below.

--

CREATE UNIQUE CLUSTERED INDEX Ix_Inx ON Pawan(ID)
WITH (FILLFACTOR=80)

--

NonClustered Index

1 A NonClustered index contains index key values and each key value entry has a row locator.
2 The structure of the row locator depends on whether the data pages are stored in a heap or a clustered table.
3 For a clustered table, the row locator is the clustered index key.
4 For a heap, a row locator is a RID pointer to the row.
5 NonClustered indexes can be unique or non-unique.
6 Indexes are internally updated after each DML operation (Insert, Update & Delete).
7 Data is not physically sorted order here.
8 We can have maximum 999 non clustered index per table.
9 A NonClustered index does not affect the ordering and storing of the data.
10 We can create duplicate NonClustered Indexes obviously with different name but we should not because indexes will be updated after every DML operation and we have pay for their maintenance also. Extra Indexes will lead to CPU and disk overhead, so craft indexes carefully and test them thoroughly.
11 Use NonClustered indexes on foreign Keys that are used in joins.
12 The example of NonClustered index is given below.

--

CREATE TABLE testNonClusIndexes
(
	 ID INT
	,Name VARCHAR(50)
	,OrderStatus BIT --can be 0,1 OR NULL
)
GO

INSERT INTO testNonClusIndexes VALUES(1,'Pawan',1),(2,'Isha',0)

CREATE NONCLUSTERED INDEX Ix_NonCls ON testNonClusIndexes(Name)

SELECT Name FROM testNonClusIndexes
WHERE Name = 'Pawan'

Output
-----------

--

NCL1

13 We can change fill factor while creating NonClustered Index. See example below

--

CREATE UNIQUE NONCLUSTERED INDEX Ix_NonInx_WithFillFactor ON Pawan(ID) WITH (FILLFACTOR = 90)

--

14 Even If you create a NonClustered primary key, SQL does not allow a single NULL value. E.g. below

--

CREATE TABLE Pawan
(
	ID INT PRIMARY KEY NONCLUSTERED
)
GO

INSERT INTO Pawan(ID) VALUES(NULL)

Output

------

Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'ID', table 'AdventureWorks2012.dbo.Pawan'; column does not allow nulls. INSERT fails.
The statement has been terminated.

--

Share this

  • LinkedIn
  • Facebook
  • Twitter
  • WhatsApp
  • Email

Blog Stats

  • 1,074,533 hits

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,131 other subscribers

Pawan Khowal

502 SQL Puzzles with answers

Achievement - 500 PuzzlesJuly 18, 2018
The big day is here. Finally presented 500+ puzzles for SQL community.

200 SQL Server Puzzle with Answers

The Big DayAugust 19, 2016
The big day is here. Completed 200 SQL Puzzles today

Archives

June 2015
M T W T F S S
1234567
891011121314
15161718192021
22232425262728
2930  
« May   Jul »

Top Articles

  • pawankkmr.wordpress.com/2…
  • pawankkmr.wordpress.com/2…
  • pawankkmr.wordpress.com/2…
  • pawankkmr.wordpress.com/2…
  • pawankkmr.wordpress.com/2…

Archives

  • October 2020 (29)
  • September 2018 (2)
  • August 2018 (6)
  • July 2018 (25)
  • June 2018 (22)
  • May 2018 (24)
  • April 2018 (33)
  • March 2018 (35)
  • February 2018 (53)
  • January 2018 (48)
  • December 2017 (32)
  • November 2017 (2)
  • October 2017 (20)
  • August 2017 (8)
  • June 2017 (2)
  • March 2017 (1)
  • February 2017 (18)
  • January 2017 (2)
  • December 2016 (5)
  • November 2016 (23)
  • October 2016 (2)
  • September 2016 (14)
  • August 2016 (6)
  • July 2016 (22)
  • June 2016 (27)
  • May 2016 (15)
  • April 2016 (7)
  • March 2016 (5)
  • February 2016 (7)
  • December 2015 (4)
  • October 2015 (23)
  • September 2015 (31)
  • August 2015 (14)
  • July 2015 (16)
  • June 2015 (29)
  • May 2015 (25)
  • April 2015 (44)
  • March 2015 (47)
  • November 2012 (1)
  • July 2012 (8)
  • September 2010 (26)
  • August 2010 (125)
  • July 2010 (2)

Article Categories

  • Analysis Services (6)
    • DAX (6)
  • Data (2)
    • Data warehousing (2)
  • Integration Services (2)
  • Magazines (3)
  • Python (29)
  • Reporting Services (4)
  • SQL SERVER (820)
    • Download SQL Interview Q's (212)
    • SQL Concepts (323)
    • SQL Performance Tuning (155)
    • SQL Puzzles (331)
    • SQL SERVER 2017 Linux (6)
    • SQL Server Interview Questions (308)
    • SQL SERVER Puzzles (332)
    • T SQL Puzzles (547)
    • Tricky SQL Queries (439)
  • UI (30)
    • ASP.NET (5)
    • C# (13)
    • CSS (9)
    • OOPS (3)
  • Uncategorized (5)

Recent Posts

  • Python | The Print and Divide Puzzle October 30, 2020
  • Python | Count consecutive 1’s from a list of 0’s and 1’s October 30, 2020
  • Python | How to convert a number into a list of its digits October 26, 2020
  • Python | Validate an IP Address-IPV6(Internet Protocol version 6) October 26, 2020
  • Python | Print the first non-recurring element in a list October 26, 2020
  • Python | Print the most recurring element in a list October 26, 2020
  • Python | Find the cumulative sum of elements in a list October 26, 2020
  • Python | Check a character is present in a string or not October 26, 2020
  • Python | Check whether a string is palindrome or not October 26, 2020
  • Python | Find the missing number in the array of Ints October 26, 2020
  • Python | How would you delete duplicates in a list October 26, 2020
  • Python | Check whether an array is Monotonic or not October 26, 2020
  • Python | Check whether a number is prime or not October 26, 2020
  • Python | Print list of prime numbers up to a number October 26, 2020
  • Python | Print elements from odd positions in a list October 26, 2020
  • Python | Print positions of a string present in another string October 26, 2020
  • Python | How to sort an array in ascending order October 26, 2020
  • Python | How to reverse an array October 26, 2020
  • Python | Find un-common words from two strings October 26, 2020
  • Python | How to convert a string to a list October 26, 2020
  • Python | Find unique words from a string October 26, 2020
  • Python | Calculate average word length from a string October 26, 2020
  • Python | Find common words from two strings October 26, 2020
  • Python | Find the number of times a substring present in a string October 26, 2020
  • Python | Find maximum value from a list October 26, 2020
  • Python | How to find GCF of two numbers October 26, 2020
  • Python | How to find LCM of two numbers October 26, 2020
  • Python | How to convert a list to a string October 26, 2020
  • Python | Replace NONE by its previous NON None value October 26, 2020
  • Microsoft SQL Server 2019 | Features added to SQL Server on Linux September 26, 2018

Create a website or blog at WordPress.com

  • Follow Following
    • Improving my SQL BI Skills
    • Join 231 other followers
    • Already have a WordPress.com account? Log in now.
    • Improving my SQL BI Skills
    • Customize
    • Follow Following
    • Sign up
    • Log in
    • Report this content
    • View site in Reader
    • Manage subscriptions
    • Collapse this bar
 

Loading Comments...
 

You must be logged in to post a comment.