SQL Server Indexes best practices

SQL Server Indexes best practices

Below are some of the SQL Server Indexes best practices or points to keep in mind while creating SQL Server Indexes.

1. Although indexes can make querying a database faster, they slow changes to the database. The more write-heavy a table is, the more careful you need to be when you add you indexes. Indexing isn't a one time event, though. As your database grows, you will need to reevaluate your indexing strategy. Indexes will need to be periodically rebuilt to ensure best performance. Changes to client applications, database design, or even your server's hardware will change your indexing strategy.

2. Keep indexes as narrow as possible. In other words, use the absolute minimum number of columns necessary to get the effect you want. The larger the composite index, the harder SQL Server will work to keep it updated and to use it in queries.

3. The first column you specify should be as unique as possible, and ideally should be the one used by most queries’ WHERE clauses.

4. Composite indexes that are also covering indexes are always useful. These indexes are built from more than one column, and all the columns necessary to satisfy a query are included in the index, which is why the index is said to cover the query.

5. Avoid using composite indexes as a table’s clustered index. Clustered indexes don’t do as well when they’re based on multiple columns. Clustered indexes physically order the table’s data rows and work best when they’re based on a single column. If you don’t have a single useful column, consider creating an identity column and using that as the basis for the clustered index.
[Source: The Definitive Guide to Scaling Out SQL Server 2005 Edition By Realtimepublishers.com]

Some other popular best practices while creating SQL Server Indexes include the following:

1. Use clustered indexes on primary keys, by default.
2. Index on search columns.
3. Use a database-level fill factor.
4. Use an index-level fill factor.
5. Index on foreign key columns.

[Source: Pro SQL Server 2012 Practices By Chris Shaw, Grant Fritchey, Carlos Bossy, Jeremy Lowell, Gail Shaw, Jesper Johansen, Mladen Prajdi, Wendy Pastrick, Kellyn Pot'Vin, Jason Strate, Herve Roggero, TJay Belt, Jonathan Gardner, Glenn Berry, Bradley Ball, Jes Borland, Ben DeBow, Louis Davidson]

More on SQL Server Indexes best practices

Index use by SQL Server depends on a number of factors, including, but not limited to: the query construction, referenced tables in the query, referenced columns, number of rows in the table, data distribution, and the uniqueness of the index column(s) data. The following are some basic guidelines to keep in mind when building you index strategy:

1. Add indexes based on your high-priority and high-execution count queries. Determine ahead of time what acceptable query execution durations might be based on your business requirements.

2. Don’t add too many indexes at the same time. Instead, add an index and test the query to see that the new index is used. If it is not used, remove it. If it is used, test to make sure there are no negative side effects to other queries. Remember that each additional index adds extra overhead to data modifications to the base table.

3. Unless you have a very good reason not to do so, always add a clustered index to each table.

4. A table without a clustered index is a heap, meaning that the data is stored in no particular order. Clustered indexes are ordered according to the clustered key and its data pages reordered during an index rebuild or reorganization. Heaps, however, are not rebuilt during an index rebuild or reorganization process, and therefore can grow out of control, taking up many more data pages than necessary.

5. Monitor query performance over time. As your data and application activity changes, so too will the performance and effectiveness of your indexes.

6. Fragmented indexes can slow down query performance, since more I/O operations are required in order to return results for a query. Keep index fragmentation to a minimum by rebuilding and/or reorganizing your indexes on a scheduled or as-needed basis.

7. Select clustered index keys that are rarely modified, highly unique, and narrow in data type width. Width is particularly important because each nonclustered index also contains within it the clustered index key. Clustered indexes are useful when applied to columns used in range queries. This includes queries that use the operators BETWEEN, >, >=, <, and <=. Clustered index keys also help reduce execution time for queries that return large result sets or depend heavily on ORDER BY and GROUP BY clauses. With all these factors in mind, remember that you can only have a single clustered index for your table, so choose carefully.

8. Nonclustered indexes are ideal for small or one-row result sets. Again, columns should be chosen based on their use in a query, specifically in the JOIN or WHERE clause. Nonclustered indexes should be made on columns containing highly unique data. Don’t forget to consider using covering queries and the INCLUDE functionality for non-key columns.

9. Use a 100% fill factor for those indexes that are located within read-only filegroups or data- bases. This reduces I/O and can improve query performance because fewer data pages are required to fulfill a query’s result set.

10. Avoid wide index keys. Always test narrower composite keys in favor of larger indexes.

11. Try to anticipate which indexes will be needed based on the queries you perform — but also don’t be afraid to make frequent use of the Database Engine Tuning Advisor tool. Using the Database Engine Tuning Advisor, SQL Server can evaluate your query or batch of queries and determine what indexes could be added (or removed) in order to help the query run faster.
[Source: SQL Server 2012 T-SQL Recipes: A Problem-Solution Approach By Jason Brimhall, David Dye, Timothy Roberts, Wayne Sheffield, Jonathan Gennick, Joseph Sack]

Few other sources for SQL Server Indexes best practices

1. MS Sql Server Indexes
2. Best practices for creating indexes
3. SQL Server Indexing best practice (SQL Server 2008)
4. Efficient SQL Server Indexing by Design
5. SQL Server Performance: Indexing Basics
6. SQL Indexing and SQL Performance Part 4: Design Considerations
7. SQL Server Indexing Best Practices: Stairway to SQL Server Indexes Level 15
8. Five Indexing Tips to Improve SQL Server Performance
9. Best Practices for Creating Indexes
10. 5 Things About Fillfactor

DATENAME Function Examples

DATENAME Function Examples

This DATENAME() function returns a character string that represents a part of a given date and time value. Lets see some of the examples of DATENAME() function.

DATENAME Example 1
Below queries find today's day. Result of both the queries is same:

SELECT DATENAME(dw, getdate()) AS 'Today Is'
SELECT DATENAME(weekday, getdate()) AS 'Today Is'

Result:
Today Is
Monday

DATENAME Example 2
Below queries find current week. Result of all the 3 queries is same:

SELECT DATENAME(week, getdate()) AS 'This is week'
SELECT DATENAME(wk, getdate()) AS 'This is week'
SELECT DATENAME(ww, getdate()) AS 'This is week'

Result:
This is week
30

DATENAME Example 3
Below queries find current month. Result of all the 3 queries is same:

SELECT DATENAME(month, getdate()) AS 'This is month'
SELECT DATENAME(mm, getdate()) AS 'This is month'
SELECT DATENAME(m, getdate()) AS 'This is month'

Result:
This is month
July

DATENAME Example 4
Below queries find current quarter. Result of all the 3 queries is same:

SELECT DATENAME(quarter, getdate()) AS 'This is quarter'
SELECT DATENAME(q, getdate()) AS 'This is quarter'
SELECT DATENAME(qq, getdate()) AS 'This is quarter'

Result:
This is quarter
3

DATENAME Example 5
Below queries find current year. Result of all the 3 queries is same:

SELECT DATENAME(year, getdate()) AS 'This is year'
SELECT DATENAME(yyyy, getdate()) AS 'This is year'
SELECT DATENAME(yy, getdate()) AS 'This is year'

Result:
This is year
2017

DATENAME Example 6
Below query finds current timezone.

Select DATENAME(tz,SYSDATETIMEOFFSET())

Result:
(No column name)
+05:30

Multiple Choice Questions - Sparse Columns

Multiple Choice Questions - Sparse Columns

1. Sparse columns make it possible to store columns more efficiently where a large percentage of the rows will be . . . . . . .

A) Non-Null
B) Null
C) Indexed
D) Sorted

2. Sparse columns require an extra . . . . . . of storage for each non-NULL, fixed-length value in the table.

A) 2 bits
B) 4 bits
C) 2 bytes
D) 4 bytes

3. SQL Server stores sparse columns in a single . . . . . . that appears to external applications and end users as a normal column.

A) bigint
B) binary
C) XML column
D) nvarchar

4. A sparse column cannot be defined with a default value.

A) True
B) False

5. A sparse column cannot be a part of a . . . . . . . .

A) clustered index
B) nonclustered index
C) XML index
D) unique primary key index

6. Sparse columns are good candidates for inclusion in . . . . . . . indexes.

A) XML
B) clustered
C) filtered
D) nonclustered

7. Sparse columns must allow NULL values and cannot include the following data types:

A) Geography
B) Varchar
C) Bigint
D) Text
E) Timestamp

8. A column cannot be changed from sparse to nonsparse or nonsparse to sparse.

A) True
B) False

9. The sparse columns feature allows you to have far more columns that you ever could before.

A) True
B) False

10. A table can only have one . . . . . defined, and when a table has a . . . . . . defined, SELECT * no longer returns individual sparse columns. Instead it returns an XML fragment containing all the non-null values for the sparse columns.

A) sql_variant
B) COLUMN_SET
C) ALL_SPARSE_COLUMNS
D) SYSTEM_VIEW

Answers

1) b, 2) d, 3) c, 4) a, 5) a,d, 6) c, 7) a,d,e, 8) b, 9) a, 10) b

How to Check Indexes on a Table

How to Check Indexes on a Table

Lets create a table named Dummy and insert few rows in it.

CREATE TABLE Dummy (
    ID int,
    LastName varchar(255),
    FirstName varchar(255),
    City varchar(255)
);

insert into Dummy values (1, 'l_1', 'f_1','c_1');
insert into Dummy values (2, 'l_2', 'f_2','c_2');
insert into Dummy values (3, 'l_3', 'f_3','c_3');
insert into Dummy values (4, 'l_4', 'f_4','c_4');
insert into Dummy values (5, 'l_5', 'f_5','c_5');
insert into Dummy values (6, 'l_6', 'f_6','c_6');

Select * from Dummy

ID	LastName	FirstName	City
1	l_1	f_1	c_1
2	l_2	f_2	c_2
3	l_3	f_3	c_3
4	l_4	f_4	c_4
5	l_5	f_5	c_5
6	l_6	f_6	c_6

We can see below 4 different queries to know about indexes on a table. Lets run all 4 below queries before creating an index on our table 'Dummy'.

1)
First query to know the indexes on a particular table

select * from sys.indexes
where object_id in (select object_id 
                    from sys.objects 
                    where name = 'Dummy')

Result:


2)
Second query to know indexes on a table

EXEC sys.sp_helpindex @objname = N'Dummy'

Result:
The object 'Dummy' does not have any indexes, or you do not have permissions.

3)
Third query to know all the tables without an index.

SELECT name
FROM sys.tables 
WHERE OBJECTPROPERTY(object_id,'IsIndexed') = 0

Result:

Name
Dummy

4)
Fourth query to get indexes for User Created Tables.

SELECT
            so.name AS TableName
            , si.name AS IndexName
            , si.type_desc AS IndexType
FROM
            sys.indexes si
            JOIN sys.objects so ON si.[object_id] = so.[object_id]
WHERE
            so.type = 'U'    --Only get indexes for User Created Tables
            AND si.name IS NOT NULL
            AND so.name = 'Dummy'
ORDER BY
            so.name, si.type

Result:

TableName	IndexName	IndexType
-	-	-

Now lets create an Index on 'LastName' column of table 'Dummy':

CREATE INDEX idx_lastname
ON Dummy (LastName);

Lets run all the 4 queries again after creating an index on a table.

1)
First query to know the indexes on a particular table

select * from sys.indexes
where object_id in (select object_id 
                    from sys.objects 
                    where name = 'Dummy')

Result:


2)
Second query to know indexes on a table

EXEC sys.sp_helpindex @objname = N'Dummy'

Result:

index_name	index_description	index_keys
idx_lastname	nonclustered located on PRIMARY	LastName

3)
Third query to know all the tables without an index.

SELECT Name
FROM sys.tables 
WHERE OBJECTPROPERTY(object_id,'IsIndexed') = 0

Result:

Name
-

4)
Fourth query to get indexes for User Created Tables.

SELECT
            so.name AS TableName
            , si.name AS IndexName
            , si.type_desc AS IndexType
FROM
            sys.indexes si
            JOIN sys.objects so ON si.[object_id] = so.[object_id]
WHERE
            so.type = 'U'    --Only get indexes for User Created Tables
            AND si.name IS NOT NULL
            AND so.name = 'Dummy'
ORDER BY
            so.name, si.type

Result:

TableName	IndexName	IndexType
Dummy	idx_lastname	NONCLUSTERED

Also See:
Return indexes for a particular table
Query to check index on a table

Subqueries in WHERE Clause - Examples

Subqueries in WHERE Clause - Examples

Below are examples of subqueries in the WHERE clause of an SQL Server statement.

CREATE TABLE teacher
(
Id int,
Name varchar(255),
Salary int,
Dept varchar(255)
);

insert into teacher values (1, 'A', 100, 'D1') 
insert into teacher values (2, 'B', 50, 'D5')
insert into teacher values (3, 'C', 500, 'D3')
insert into teacher values (4, 'D', 200, 'D7')
insert into teacher values (5, 'E', 400, 'D1')

CREATE TABLE subjects
(
Subject_Id varchar(50),
Title varchar(255),
Time varchar(255),
Instructor_Id int
);

insert into subjects values ('S1', 'Maths', 'Summer', 1)
insert into subjects values ('S1', 'English', 'Winter', 2)
insert into subjects values ('S1', 'Science', 'Summer', 3)
insert into subjects values ('S1', 'Language', 'Spring', 5)

Select * from teacher;
Select * from subjects;

teacher Table

  

    
Id
    
Name
 
    
Salary
    
Dept
  
  

    
1
    
A
    
100
    
D1
  
  

    
2
    
B
    
50
    
D5
  
  

    
3
    
C
    
500
    
D3
  
    

    
4
    
D
    
200
    
D7
  
    

    
5
    
E
    
400
    
D1
  


subjects Table

  

    
Subject_Id
    
Title
 
    
Time
    
Instructor_Id
  
  

    
S1
    
Maths
    
Summer
    
1
  
  

    
S1
    
English
    
Winter
    
2
  
  

    
S1
    
Science
    
Summer
    
3
  
    

    
S1
    
Language
    
Spring
    
5
  

Example 1 of Subqueries in WHERE Clause

SELECT
  Id,
  Name, Salary
FROM
  teacher
WHERE
  Id =
  (
    SELECT Instructor_Id
    FROM Subjects
    WHERE Title = 'Science'
  );


  

    
Id
    
Name
 
    
Salary
  
  

    
3
    
C
    
500
  
  

The subquery finds the Instructor_Id where Title is Science. This Instructor_Id is used by outer query to find the row from teacher table.

Example 2 of Subqueries in WHERE Clause (using NOT IN)

SELECT
  Id,
  Name, Salary
FROM
  teacher
WHERE
  Id NOT IN
  (
    SELECT Instructor_Id
    FROM Subjects
    WHERE Title = 'Science'
  );


  

    
Id
    
Name
 
    
Salary
  
  

    
1
    
A
    
100
  
  

    
2
    
B
    
50
  
  

    
4
    
D
    
200
  
    

    
5
    
E
    
400
  
 

The subquery finds the Instructor_Id where Title is Science. Outer query finds rows from the teacher table where Id is Not 3 (3 is the Instructor_Id returned by subquery).

Example 3 of Subqueries in WHERE Clause (using NOT EXISTS)

SELECT
  Id,
  Name, Salary
FROM
  teacher
WHERE NOT EXISTS
  (
    SELECT Instructor_Id
    FROM Subjects
    WHERE Title = 'Arabic'
  );


  

    
Id
    
Name
 
    
Salary
  
  

    
1
    
A
    
100
  
  

    
2
    
B
    
50
  
  

    
3
    
C
    
500
  
    

    
4
    
D
    
200
  
  

    
5
    
E
    
400
  

NOT EXISTS works like EXISTS, except the WHERE clause in which it is used is satisfied if no rows are returned by the subquery. Same way, no rows are returned by the subquery, satisfying the WHERE clause, in the above example, so the outer query returns all the rows from the teacher table.

In below example, subquery returns a row, therefore outer query does not return any rows because the WHERE clause is not satisfied.

Example 3A of Subqueries in WHERE Clause (using NOT EXISTS)

SELECT
  Id,
  Name, Salary
FROM
  teacher
WHERE NOT EXISTS
  (
    SELECT Instructor_Id
    FROM Subjects
    WHERE Title = 'Language'
  );


  

    
Id
    
Name
 
    
Salary

Example 4 of Subqueries in WHERE Clause (using EXISTS)

SELECT
  Id,
  Name, Salary
FROM
  teacher
WHERE EXISTS
  (
    SELECT Instructor_Id
    FROM Subjects
    WHERE Title = 'Arabic'
  );
  

  

    
Id
    
Name
 
    
Salary

EXISTS specifies a subquery to test for the existence of rows. Example 3 and 3A uses NOT EXISTS and this example 4 and 4A use EXISTS. As with Example 3 and 3A, this example 4 and 4A also shows no records in the result or all records in the result based on whether WHERE clause is satisfied by the subquery or not.

Example 4A of Subqueries in WHERE Clause (using EXISTS)

SELECT
  Id,
  Name, Salary
FROM
  teacher
WHERE EXISTS
  (
    SELECT Instructor_Id
    FROM Subjects
    WHERE Title = 'Language'
  );


  

    
Id
    
Name
 
    
Salary
  
  

    
1
    
A
    
100
  
  

    
2
    
B
    
50
  
  

    
3
    
C
    
500
  
    

    
4
    
D
    
200
  
  

    
5
    
E
    
400
  

Example 5 of Subqueries in WHERE Clause (using ALL)

SELECT
  Id,
  Name, Salary
FROM
  teacher
WHERE
  Id > ALL
  (
    SELECT Instructor_Id
    FROM Subjects
    WHERE Title in ('Maths', 'Science')
  );


  

    
Id
    
Name
 
    
Salary
  
    

    
4
    
D
    
200
  
  

    
5
    
E
    
400
  

>ALL means greater than every value. In other words, it means greater than the maximum value. Subquery returns two Instructor_Ids i.e. 1 and 3. Outer query fetches all the rows with Instructor_Ids greater than 1 and 3 (or you can say greater than 3).

Example 6 of Subqueries in WHERE Clause (using ANY)

SELECT
  Id,
  Name, Salary
FROM
  teacher
WHERE
  Id > ANY
  (
    SELECT Instructor_Id
    FROM Subjects
    WHERE Title in ('Maths', 'Science')
  );


  

    
Id
    
Name
 
    
Salary
  
  

    
2
    
B
    
50
  
  

    
3
    
C
    
500
  
    

    
4
    
D
    
200
  
  

    
5
    
E
    
400
  

>ANY means greater than at least one value, that is, greater than the minimum, hence the above result.

Example 7 of Subqueries in WHERE Clause (using SOME)

SELECT
  Id,
  Name, Salary
FROM
  teacher
WHERE
  Id > SOME
  (
    SELECT Instructor_Id
    FROM Subjects
    WHERE Title in ('Maths', 'Science')
  );


  

    
Id
    
Name
 
    
Salary
  
  

    
2
    
B
    
50
  
  

    
3
    
C
    
500
  
    

    
4
    
D
    
200
  
  

    
5
    
E
    
400
  

SOME and ANY are equivalent.

Multiple Choice Questions - Contained Databases

Multiple Choice Questions - Contained Databases

1) A contained database is one that is isolated from other databases but may or may not be isolated from the instance of SQL Server that it is running on.

A) True
B) False

2) In SQL Server 2012, all databases are still, by default, . . . . . . . . . . .

A) partially contained
B) contained
C) non-contained
D) None of above

3) With partially contained databases not all the users need to be . . . . . . . . . Partially contained databases can have a combination of . . . . . . . . . . and . . . . . . . . . within the database.

A) contained users, contained users, non-contained users
B) contained users, system users, non-contained users
C) system users, contained users, non-contained users
D) contained users, contained users, remote users

4) Advantages of contained databases: (choose all that apply)

A) Portability of the database.
B) Good candidates for AlwaysOn
C) No need to explicitly specify the database in the connection string.
D) Reducing administrative overhead
E) Database mirroring

5) A fully contained databases has no external dependencies like logins or references to other databases, making it easier to move between servers without breaking any functionality.

A) True
B) False

6) Two key steps which are required to create a partially contained database in SQL Server 2012 are:

A) The first step is to delete non-contained databases . The next step is to configure the database for containment.
B) The first step is to disable non-contained databases. The next step is to configure the database for containment.
C) The first step is to enable root configuration. The next step is to configure the database for containment.
D) The first step is to enable contained database authentication. The next step is to configure the database for containment.

7) Partially contained databases do not store the . . . . . . . So you still need to replicate the . . . . . . . . . to all the secondary replicas.

A) jobs
B) user information
C) system views
D) authentication information

8) SQL Server provides a new data management view (DMV) called . . . . . . . . . . that you can query on to discover potential threats to database portability.

A) sys.sql_modules
B) sys.dm_db_uncontained_entities
C) sys.dm_db_resource_stats
D) sys.dm_db_persisted_sku_features

9) A contained database cannot use . . . . . . . . . . .

A) replication
B) change tracking
C) change data capture
D) all of above

10) In the contained model, a database user is created with a password and cannot be mapped to a windows user.

A) True
B) False

Answers

1) b, 2) c, 3) a, 4) a,b,d,e, 5) a, 6) d, 7) a, 8) b, 9) d, 10) b

Difference between GRANT, DENY & REVOKE

Difference between GRANT, DENY & REVOKE

Grant is used to specifically allow access to an object.

Deny is used to specifically prevent access to an object.

Revoke is used to remove specifically granted or denied access to an object. [The Handbook for Reluctant Database Administrators by Josef Finsel]

Difference between GRANT, DENY & REVOKE can also be stated in below words
When permission is granted, the user or role is given permission to perform an acton, such as creating a table.

The DENY statement denies permission on an object and prevents the principal from gaining GRANT permission based on membership in a group or role.

The REVOKE statement removes a permission that was previously granted or denied.

Permissions denied at a higher scope in a security model (server at highest level, followed by database and schema) override grants on that permission at a lower scope. If INSERT permission is denied on tables at the database level, and INSERT on a specific table in that database is granted at the schema level, the result is that INSERT is denied on all tables. In this example a database-level DENY overrides any GRANT at the lower schema level. For more on this please refer [Microsoft SQL Server 2014 Unleashed by Ray Rankins, Paul Bertucci, Chris Gallelli, Alex T. Silverstein].

Simply difference between REVOKE & DENY can also be stated as
REVOKE removes a previously assigned permission. DENY prevents the permission from being assigned. [Mastering SQL Server 2000 Security by Mike Young, Curtis W. Young]

Examples of GRANT, DENY & REVOKE

1.
Granting SELECT on employee table to user1 and user2.

USE DB1;
GRANT SELECT ON employee
TO user1, user2;

2.
Granting CREATE TABLE to user1 and user2.

USE DB1;
GRANT CREATE TABLE
TO user1, user2;

3.
Denying CREATE TABLE to user1 and user2.

USE DB1;
DENY CREATE TABLE
TO user1;

4.
Revoking SELECT on table_1 from user2.

USE DB1;
REVOKE SELECT ON table_1
FROM user2;

Multiple Choice Questions on SQL Server

Multiple Choice Questions on SQL Server

1. Multiple Choice Questions on SQL Data Types [Link]
2. Multiple Choice Questions on SQL Insert and Update [Link]
3. Multiple Choice Questions on SQL Where Clause [Link]
4. Multiple Choice Questions on SQL Inbuilt Functions [Link]
5. Multiple Choice Questions on SQL Union [Link]
6. Multiple Choice Questions on SQL Joins [Link]
7. Multiple Choice Questions on SQL Delete [Link]
8. Multiple Choice Questions on SQL Truncate [Link]
9. Multiple Choice Questions on SQL Keys [Link]
10. Multiple Choice Questions on SQL Indexes [Link]
11. Multiple Choice Questions on SQL Views [Link]
12. Multiple Choice Questions on SQL Delete - Set 2 [Link]
13. Multiple Choice Questions on SQL Tables [Link]
14. Multiple Choice Questions on SQL Subqueries [Link]
15. Multiple Choice Questions on SQL Normalization [Link]
16. Multiple Choice Questions on SQL Indexes - Set 2 [Link]
17. Multiple Choice Questions on SQL Temp Tables [Link]
18. Multiple Choice Questions on SQL Data Types - Set 2 [Link]
19. Multiple Choice Questions on SQL Expressions [Link]
20. Multiple Choice Questions on SQL NULL [Link]
21. Multiple Choice Questions on SQL GROUP BY / HAVING [Link]
22. Multiple Choice Questions on SQL Server Permissions [Link]
23. Multiple Choice Questions on SQL Joins - Set 2 [Link]
24. Multiple Choice Questions on SQL Inbuilt Functions - Set 2 [Link]
25. Multiple Choice Questions on SQL Common Table Expressions [Link]
26. Multiple Choice Questions on SQL Exists & IN [Link]
27. Multiple Choice Questions on SQL Server Date / time [Link]
28. Multiple Choice Questions on SQL Truncate - Set 2 [Link]
29. Multiple Choice Questions on SQL Server Cast / Convert [Link]
30. Multiple Choice Questions on SQL Server - Table Columns [Link]
31. Multiple Choice Questions on SQL Server - Locks [Link]
32. Multiple Choice Questions on SQL Server - Drop Indexes [Link]
33. Multiple Choice Questions on SQL Server - ORDER BY [Link]
34. Multiple Choice Questions on SQL Server - GRANT [Link]
35. Multiple Choice Questions on SQL Server - Like [Link]
36. Multiple Choice Questions on SQL Joins - Set 3 [Link]
37. Multiple Choice Questions on SQL Insert [Link]
38. Multiple Choice Questions on SQL Merge [Link]
39. Multiple Choice Questions on SQL Server Local Variables [Link]
40. Multiple Choice Questions on SQL Joins - Set 4 [Link]
41. Multiple Choice Questions on SQL Server - Merge Join [Link]
42. Multiple Choice Questions on SQL Server - Window Functions [Link]
43. Multiple Choice Questions on SQL Server - Backup & Restore [Link]
44. Multiple Choice Questions on SQL Server - Indexed Views [Link]
45. Multiple Choice Questions on SQL Server - Hash Join [Link]
46. Multiple Choice Questions on SQL Server - SQL Subqueries - Set 2 [Link]
47. Multiple Choice Questions on SQL Server - Files & Filegroups [Link]
48. Multiple Choice Questions on SQL Server - Join Hints [Link]
49. Multiple Choice Questions on SQL Server - Stored Procedures [Link]
50. Multiple Choice Questions on SQL Server - Columnstore indexes [Link]
51. Multiple Choice Questions on SQL Server - Stored Procedures - Set 2 [Link]
52. Multiple Choice Questions on SQL Server - SQL "Where" With Wildcards [Link]
53. Multiple Choice Questions on SQL Server - Joins & Nulls [Link]
54. Multiple Choice Questions on SQL Server - Row Level Security [Link]
55. Multiple Choice Questions on SQL Server - Sequence Objects [Link]
56. Multiple Choice Questions on SQL Server - XML Data Type & Columns [Link]
57. Multiple Choice Questions on SQL Server - Identity Column [Link]
58. Multiple Choice Questions on SQL Joins - Set 5 [Link]
59. Multiple Choice Questions on SQL Joins - Set 6 [Link]
60. Multiple Choice Questions on SQL Server - Union All - [Link]
61. Multiple Choice Questions on SQL Server - Always Encrypted - [Link]
62. Multiple Choice Questions on SQL Server - Dynamic Data Masking - [Link]
63. Multiple Choice Questions on SQL Joins - Set 7 [Link]
64. Multiple Choice Questions on SQL Server - Contained Databases [Link]
65. Multiple Choice Questions on SQL Server - Sparse Columns [Link]

SQL Server - Interview Questions for Testers

SQL Server - Interview Questions for Testers

1.

What are the two authentication modes in SQL Server?

Two authentication modes in SQL Server are:

Windows Mode
Mixed Mode

2.

What are the differences between local and global temporary tables?

These local and global temporary tables are physical tables created within the Temporary Tables folder in tempdb database.

Local temporary table name is prefixed with hash ("#") sign.
Local temporary tables are visible only in the current session, i.e; are only available to the current connection for the user. These tables are deleted or destroyed after the user disconnects from the instance of SQL Server.

Global temporary table name is prefixed with a double hash ("##") sign.
Global temporary tables are visible to all sessions and all users. Global temporary tables are deleted or dropped when all users referencing the table disconnect from the instance of SQL Server.
Global temporary tables are dropped automatically when the last session using the temporary table has completed.

Temporary tables are automatically dropped when they go out of scope, unless they are explicitly dropped using DROP TABLE.

Example of Local temporary table

CREATE TABLE #tmp_local (id int, name varchar(50))

INSERT INTO #tmp_local VALUES (1, 'AAA');

SELECT * FROM #tmp_local

Example of Global temporary table

CREATE TABLE ##tmp_global (id int, name varchar(50))

INSERT INTO ##tmp_global VALUES (1, 'AAA');

SELECT * FROM ##tmp_global

Also see:
Microsoft SQL Server Interview Questions By Chandan Sinha

3.

What is sub query and its properties?

A sub-query is a query which can be nested inside a main query like Select, Update, Insert or Delete statements. This can be used when expression is allowed.

Properties of sub query can be defined as
A sub query should not have order by clause.
A sub query should be placed in the right hand side of the comparison operator of the main query.
A sub query should be enclosed in parenthesis because it needs to be executed first before the main query.
More than one sub query can be included.

Types of sub query
There are three types of sub query –
Single row sub query which returns only one row.
Multiple row sub query which returns multiple rows.
Multiple column sub query which returns multiple columns to the main query. With that sub query result, Main query will be executed.
[Source]

4.

What is COALESCE in SQL Server?

COALESCE returns first non-null expression within the arguments. This function is used to return a non-null from more than one column in the arguments.

Example 1 of COALESCE

Select * from Students
Select ID, COALESCE(First_Name, Middle_Name, Last_Name) from Students

Result of running the above two queries:

ID	First_Name	Middle_Name	Last_Name
1	John	NULL	NULL
2	NULL	William	NULL
3	NULL	NULL	Smith
4	Peter	Mark	Garcia
5	NULL	NULL	Campbell

ID	(No column name)
1	John
2	William
3	Smith
4	Peter
5	Campbell

Example 2 of COALESCE

Select COALESCE(1, 2, 3, 4, 5, NULL)

(No Column Name)
1

Select COALESCE(Null, Null, Null, 4, 5, NULL)

(No Column Name)
4

5.

How to get the version of SQL Server?

Select SERVERPROPERTY('productversion')

6.

What is Normalization?

Goal of Normalization
When you normalize a database, you have four goals: arranging data into logical groupings such that each group describes a small part of the whole; minimizing the amount of duplicate data stored in a database; organizing the data such that, when you modify it, you make the change in only one place; and building a database in which you can access and manipulate the data quickly and efficiently without compromising the integrity of the data in storage.

Data normalization helps you design new databases to meet these goals or to test databases to see whether they meet the goals. Sometimes database designers refer to these goals in terms such as data integrity, referential integrity, or keyed data access. Ideally, you normalize data before you create database tables. However, you can also use these techniques to test an existing database. [Source]

A database is in first normal form if it satisfies the following conditions:

Contains only atomic values that is there must be only one field for each item of data you want to include.

Below table is not in first normal form:

Furniture_ID	Furniture_Type	Price
1	Chair, Sofa	300
2	Table	100
3	Chair	50
4	Table, Chair	200
5	Sofa	150

In order to normalize the above table to first normal form, above table is divided into two tables as below:

Furniture_ID	Price
1	300
2	100
3	50
4	200
5	150

Furniture_ID	Furniture_Type
2	Table
3	Chair
5	Sofa

A database is in second normal form if it satisfies the following conditions:
- is in 1st normal form
- all the keys are dependent on the whole of the key

Furniture_ID	Furniture_Store_ID	Furniture_Store_Location
1	1	India
2	2	US
3	1	India
4	3	UK

This above table has a composite primary key [Furniture_ID, Furniture_Store_ID]. The non-key attribute is [Furniture_Store_Location]. In this case, [Furniture_Store_Location] only depends on [Furniture_Store_ID], which is only part of the primary key. Therefore, this table does not satisfy second normal form. So it is divided into below two tables to normalize it into second normal form.

Furniture_ID	Furniture_Store_ID
1	1
2	2
3	1
4	3

Furniture_Store_ID	Furniture_Store_Location
1	India
2	US
3	UK

A database is in third normal form if it satisfies the following conditions:
-if it is in second normal form
-It has no non-key dependencies (means there are no fields that are dependent on other fields that are not part of the key)

Booking_ID	Location_ID	Location	Price
1	2	Singapore	987
2	1	Hong Kong	654
3	2	Singapore	345
4	3	Chile	657
5	1	Hong Kong	700

In the table above, [Booking_ID] determines [Location_ID], and [Location_ID] determines [Location]. Therefore, [Booking_ID] determines [Location] via [Location_ID] and we have transitive functional dependency, and this structure does not satisfy third normal form. So the above table is normalized to third normal form and divided into two below tables.

Booking_ID	Location_ID	Price
1	2	987
2	1	654
3	2	345
4	3	657
5	1	700

Location_ID	Location
1	Hong Kong
2	Singapore
3	Chile

7.

How is ACID property related to Database?

Atomicity - a transaction to transfer funds from one account to another involves making a withdrawal operation from the first account and a deposit operation on the second. If the deposit operation failed, you don’t want the withdrawal operation to happen either.

Consistency - a database tracking a checking account may only allow unique check numbers to exist for each transaction.

Isolation - a teller looking up a balance must be isolated from a concurrent transaction involving a withdrawal from the same account. Only when the withdrawal transaction commits successfully and the teller looks at the balance again will the new balance be reported.

Durability - A system crash or any other failure must not be allowed to lose the results of a transaction or the contents of the database. Durability is often achieved through separate transaction logs that can "re-create" all transactions from some picked point in time (like a backup). [Source]

8.

What is a Stored Procedure?

A stored procedure groups one or more T-SQL statements into a logical unit, stored as an object in a SQL Server database. After stored procedure is created, its definition is stored in the sys.sql_module system catalog view. [SQL Server 2005 T-SQL Recipes: A Problem-Solution Approach by Joseph Sack]

Example 1 of Stored Procedure:

CREATE PROCEDURE AddTwoNumbers
   @num1 int=0,
   @num2 int=0,
   @result int OUTPUT
AS
BEGIN
   SET NOCOUNT ON;
   SELECT @result=@num1 + @num2
END
GO

DECLARE @i INT
EXECUTE AddTwoNumbers 3,2,@i OUTPUT
SELECT @i

Result of running the above stored procedure:

(No column name)
5

Example 2 of Stored Procedure:

Select * from A

ID	Name
1	A
2	A
3	A

Two procedures are there: A_details and A_insert_row:

Create Procedure A_details
As
Begin
    Select * from A
End

--Run the procedure on SQL Server:

Execute   A_details

Create Procedure A_insert_row
(@ID int, @Name nchar(10))
As
Begin
    Insert Into A
    Values (@ID, @Name)
End

Execute A_insert_row 4,'B'

ID	Name
1	A
2	A
3	A
4	B

More on Stored Procedures:
Stored Procedures
Reasons for using stored procedures

9.

How stored procedures help reduce the Network Traffic?

You can reduce the amount of extraneous information sent back by a stored procedure (for example, the message N rows affected) by placing the following statement at the top of your stored procedure:

SET NO COUNT ON

When you use the Set NOCOUNT ON statement, you limit the amount of extraneous information sent back to the calling process, thus reducing network traffic and increasing performance. The following example, created in the pubs database, shows how to use the statement in a stored procedure:

Create procedure usp_nocount
as
SET NOCOUNT ON
Select * from authors

[Source: Microsoft SQL Server DBA Survival Guide]

10.

What is an Index?

Indexes are created on columns in tables or views. The index provides a fast way to look up data based on the values within those columns. For example, if you create an index on the primary key and then search for a row of data based on one of the primary key values, SQL Server first finds that value in the index, and then uses the index to quickly locate the entire row of data. Without the index, a table scan would have to be performed in order to locate the row, which can have a significant effect on performance.



Clustered Indexes
A clustered index stores the actual data rows at the leaf level of the index. Returning to the example above, that would mean that the entire row of data associated with the primary key value of 123 would be stored in that leaf node. An important characteristic of the clustered index is that the indexed values are sorted in either ascending or descending order. As a result, there can be only one clustered index on a table or view.

Nonclustered Indexes
Unlike a clustered indexed, the leaf nodes of a non-clustered index contain only the values from the indexed columns and row locators that point to the actual data rows, rather than contain the data rows themselves. This means that the query engine must take an additional step in order to locate the actual data.

Also See:
SQL Server Index Basics

11.

What are different types of Joins?

Suppose we have two tables A & B:

Table A

A
1
2
3
4

Table B

B
3
4
5
6

INNER JOIN
It returns all rows from multiple tables where the join condition is met.

Syntax of SQL Server INNER JOINS

SELECT columns
FROM table1 
INNER JOIN table2
ON table1.column = table2.column;

Using Inner Join on tables A & B result will be:

A	B
3	3
4	4

LEFT OUTER JOIN
It returns all rows from the LEFT table specified in the ON condition and only those rows from the other table where the join condition is met.

Syntax of SQL Server LEFT OUTER JOIN

SELECT columns
FROM table1
LEFT [OUTER] JOIN table2
ON table1.column = table2.column;

Using Left Outer Join on tables A & B result will be:

A	B
1	Null
2	Null
3	3
4	4

RIGHT OUTER JOIN
It returns all rows from the RIGHT table specified in the ON condition and only those rows from the other table where the join condition is met.

Syntax of SQL Server RIGHT OUTER JOIN

SELECT columns
FROM table1
RIGHT [OUTER] JOIN table2
ON table1.column = table2.column;

Using Right Outer Join on tables A & B result will be:

A	B
3	3
4	4
Null	5
Null	6

FULL OUTER JOIN
It returns all rows from the LEFT table and RIGHT table with nulls in place where the join condition is not met.

Syntax of SQL Server FULL OUTER JOIN

SELECT columns
FROM table1
FULL [OUTER] JOIN table2
ON table1.column = table2.column;

Using Full Outer Join on tables A & B result will be:

A	B
1	Null
2	Null
3	3
4	4
Null	5
Null	6

12.

What is the difference between a Function and a Stored Procedure?

Functions must always return value to the caller. On the other hand stored procedures do not have this requirement. You commonly use functions within another expression, whereas you often use stored procedures independently. [Microsoft SQL Server 2008 For Dummies By Mike Chapple]

Also See:
Difference between a Function and a Stored Procedure

13.

What are Primary Keys and Foreign Keys?

A primary key constraint is a column or a set of columns that uniquely identify a row in the table.

A foreign key constraint consists of a column or a set of columns that participates in a relationship with a primary key table. The primary key is on the one side of the relationship, whereas the foreign key is on the many side of the relationship. A table can have only one primary key, but it can have multiple foreign keys. Each foreign key relates to a different primary key in a separate table. [Sams Teach Yourself SQL Server 2005 Express in 24 Hours By Alison Balter]

Also See:
Difference between Primary Keys and Foreign Keys

14.

What is the difference between Clustered and a Non-clustered Index?

Clustered Index
Only one per table
Faster to read than non clustered as data is physically stored in index order

Non Clustered Index
Can be used many times per table
Quicker for insert and update operations than a clustered index

Both types of index will improve performance when select data with fields that use the index but will slow down update and insert operations. Because of the slower insert and update clustered indexes should be set on a field that is normally incremental ie Id or Timestamp.

SQL Server will normally only use an index if its selectivity is above 95%.

Also See:
Difference between Clustered and a Non-clustered Index
Difference between Clustered and a Non-clustered Index

15.

What’s the difference between a Primary Key and a Unique Key?

Primary Key:
There can only be one primary key in a table.
In some DBMS it cannot be NULL - e.g. MySQL adds NOT NULL.
Primary Key is a unique key identifier of the record.

Unique Key:
Can be more than one unique key in one table.
Unique key can have null values.
It can be a candidate key.
Unique key can be null and may not be unique.

Also See:
Difference between a Primary Key and a Unique Key
Difference between a Primary Key and a Unique Key

16.

What is difference between DELETE and TRUNCATE Commands?

Truncate	Delete
After Truncate, Rollback is not possible.	We can Rollback after Delete.
Its DDL	Its DML
Can't use WHERE clause with truncate	Can use WHERE clause with Delete
Syntax: Truncate Table Table_Name	Syntax: Delete From Table_Name

Also see:
Difference between Truncate / Delete
Difference between Truncate / Drop

17.

What is the difference between a HAVING clause and a WHERE clause?

HAVING is used to check conditions after the aggregation takes place.
WHERE is used before the aggregation takes place.

This code:

select City, CNT=Count(1)
From Address
Where State = 'MA'
Group By City

Gives you a count of all the cities in MA.

This code:

select City, CNT=Count(1)
From Address
Where State = 'MA'
Group By City
Having Count(1)>5

Gives you the count of all the cities in MA that occur 6 or more times.
[Source]

Also See:
Difference between a HAVING clause and a WHERE clause

18.

What is the difference between UNION and UNION ALL?

UNION removes duplicate records (where all columns in the results are the same), UNION ALL does not. There is a performance hit when using UNION vs UNION ALL, since the database server must do additional work to remove the duplicate rows, but usually you do not want the duplicates (especially when developing reports). [Source]

Also See:
Difference between UNION and UNION ALL

19.

What is an Identity?

When you make a column an identity column, SQL Server automatically assigns a sequenced number to this column with every row you insert. The number that SQL Server starts counting from is called the seed value, and the amount that the value increases or decreases by with each row is called the increment. The default is for a seed of 1 and an increment of 1. Once you have set a seed (the starting point) and the increment, your values only go up (or down if you set the increment to a negative number). There is no automatic mechanism to go back and fill in the numbers for any rows you may have deleted. If you want to fill in blank spaces like that, you need to use SET IDENTITY_INSERT ON, which allows you to turn off the identity process for inserts from the current connection. [Beginning Microsoft SQL Server 2012 Programming By Paul Atkinson, Robert Vieira]

Also See:
Identity

20.

What is CHECK Constraint?

A check constraint limits the values allowed in a column.

Example

CREATE TABLE MyOrders 
(OrderID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
 Amount DECIMAL(18,2),
 Tax DECIMAL(5,2) CHECK (Tax >= 0.0 AND Tax <= 200.0)
)

Now when you try inserting data:

INSERT INTO MyOrders(Amount, Tax)
VALUES (150.0, 2.75)  

INSERT INTO MyOrders(Amount, Tax)
VALUES (250.0, 15.75) 

INSERT INTO MyOrders(Amount, Tax)
VALUES (300.0, 210.0)

The first two insert statements will just work fine but the third statement will give below error:

Msg 547, Level 16, State 0, Line 1 The INSERT statement conflicted with the CHECK constraint "CK__Orders__Tax__2BFE89A6". The conflict occurred in database "new_DB", table "dbo.Orders", column 'Tax'. The statement has been terminated.

Also See:
Check Constraint

21.

How to delete duplicate rows?

Below are two ways to delete duplicate records from a table in SQL Server:

Suppose we have a table P as shown below:

Select * from P

ID	Name
-100	AA
-1100	BB
-2100	CC
-100	AA

This query deletes all the duplicate rows:

DELETE
FROM P
WHERE ID IN
(
SELECT MIN(ID)
FROM P
GROUP BY ID, Name
having count(*)>1)

Result after running the above query:

Select * from P

ID	Name
-1100	BB
-2100	CC

Lets again use our table P and see second method of deleting duplicate rows from a table:

Select * from P

ID	Name
-100	AA
-1100	BB
-2100	CC
-100	AA

This query deletes all duplicate rows but one. For example if you have two same rows in a table it deletes one and keeps the other.

WITH cte AS (
  SELECT ID, Name, 
     row_number() OVER(PARTITION BY ID, Name ORDER BY ID) AS [rn]
  FROM P
)
DELETE cte WHERE [rn] > 1

Results of running the above query:

Select * from P

ID	Name
-100	AA
-1100	BB
-2100	CC

Also See:
Deleting duplicate records

22.

What is NOT NULL Constraint?

NOT NULL constraint is a type of a check constraint because it checks that a column value is set to something and not specifically set to NULL.

Also See:
NOT NULL Constraint

23.

What are user-defined functions? What are the types of user-defined functions that can be created?

Like functions in programming languages, SQL Server user-defined functions are routines that accept parameters, perform an action, such as a complex calculation, and return the result of that action as a value. The return value can either be a single scalar value or a result set.

Scalar Function
User-defined scalar functions return a single data value of the type defined in the RETURNS clause.

Example

CREATE FUNCTION MyReverse1(@string varchar(100)) 
RETURNS varchar(100)  
AS  
BEGIN  
    DECLARE @custName varchar(100) =''
 SET @custName = Reverse (@string)
    RETURN @custName
END  

Call the above function as

Select dbo.MyReverse1('SQL');

Result:

(No column name)
LQS

Table-Valued Functions
User-defined table-valued functions return a table data type.

Example

CREATE FUNCTION SalaryMoreThan(@Sal INT)  
RETURNS TABLE  
AS  
RETURN  
    SELECT Id, Name, Salary 
    FROM teacher  
    WHERE Salary > @Sal   

SELECT Id, Name, Salary FROM SalaryMoreThan(200)

System Functions
SQL Server provides many system functions that you can use to perform a variety of operations. They cannot be modified.

Also See:
User-defined functions

24.

Which TCP/IP port does the SQL Server run on? How can it be changed?

SQL Server runs on port 1433. It can be changed from the Network Utility TCP/IP properties.

25.

What is a Table Called, if it has neither Cluster nor Non-cluster Index? What is it used for?

Its heap and it is used for inserting and updating records.

26.

What is the difference between CHAR and VARCHAR Datatypes?

The primary difference between the varchar and char types is data padding. If you have a column called FirstName that is a varchar(10) data type and you store the value of "SAM" in the column, only 3 bytes are physically stored, plus a little overhead. If you store the same value in a char(10) data type, all 10 bytes would be used. SQL inserts trailing spaces to fill the 10 characters.

Example depicting the difference between CHAR and VARCHAR

DECLARE @Var_Char Char(10) = 'SAM',
@Var_Varchar VarChar(10) = 'SAM'

SELECT DATALENGTH(@Var_Char) Char_Space_Used,
DATALENGTH(@Var_Varchar) VarChar_Space_Used

Char_Space_Used	VarChar_Space_Used
10	3

Another good source of difference between CHAR and VARCHAR.

Also See:
Professional Microsoft SQL Server 2014 Administration by Adam Jorgensen, Bradley Ball, Steven Wort, Ross LoForte, Brian Knight

27.

What is the difference between VARCHAR and NVARCHAR datatypes?

The difference is that nvarchar data types can be used for unicode data that can store data in any language whereas varchar data types cannot store data an all languages. So if you need your application to be truly international it is best to use nvarchar data types. [Source: Practical Sql: Microsoft Sql Server T-SQL for Beginners by Mark O'Donovan]

The main difference between the VARCHAR and the NVARCHAR data types is that each NVARCHAR character is stored in 2 bytes, while each VARCHAR character uses 1 byte of storeage space. The maximum number of characters in a column of NVARCHAR data type is 4000. [Source: Microsoft SQL Server 2012 A Begineers Guide by PETKOVIC]

28.

Why can there be only one Clustered Index and not more than one?

The leaf level of a clustered index is the table itself. The clustered index sits on the top of the table. As a result, the table is physically sorted according to the clustered key. For this reason, a table can have only one clustered index. [Building Client-Server Applications with Visual FoxPro and SQL Server 7. 0 by Chuck Urwiler, Gary DeWitt, Leslie Koorhan, Mike Levy]

The reason why there can be only one clustered index on a table is that the clustered index governs the physical placement of the data, and the data cannot be in two places at once. There can only be one sequence in which the data can be physically placed. [Microsoft SQL Server 2005 Performance Optimization and Tuning Handbook by Ken England, Gavin JT Powell]

29.

How to copy data from one table to Another Table?

If both tables are truly the same schema:

INSERT INTO newTable
SELECT * FROM oldTable

Otherwise, you'll have to specify the column names (the column list for newTable is optional if you are specifying a value for all columns and selecting columns in the same order as newTable's schema):

INSERT INTO newTable (col1, col2, col3)
SELECT column1, column2, column3
FROM oldTable

[Source]

30.

How to find the second highest salary from a table?

CREATE TABLE EMP (
    EMP_ID int,
    Salary int
    );

Insert into EMP values (1, 200);
Insert into EMP values (2, 100);
Insert into EMP values (3, 50);
Insert into EMP values (4, 600);
Insert into EMP values (5, 300);

Select * from EMP

EMP_ID	Salary
1	200
2	100
3	50
4	600
5	300

Example 1 of Finding the 2nd highest salary

--Second highest Salary
SELECT max(Salary)
FROM EMP
WHERE Salary < (SELECT max(Salary)
                FROM EMP);

Result:

(No column name)
300

Example 2 of Finding the 2nd highest salary

--Nth highest salary
SELECT TOP 1 Salary 
FROM ( SELECT TOP 2 Salary 
       FROM 
       EMP ORDER BY Salary DESC) AS EMP ORDER BY Salary ASC

Result:

Salary
300

Difference between isNull & COALESCE

Difference between isNull & COALESCE

Here are 3 points of difference between isNULL & Coalesce. Also below you can have a look at the table to get the points of difference between isNULL & Coalesce at a glance.

1. The type of the COALESCE expression is the type of the input expression with the highest precedence, whereas the type of the ISNULL expression is determined by the first input. [Training Kit (Exam 70-461): Querying Microsoft SQL Server 2012 by Itzik Ben-Gan, Dejan Sarka, Ron Talmage]

The type returned from ISNULL changes depending on the order of the input parameters, while COALESCE returns the data type of the highest precedence regardless of the argument order. [SQL Server T-SQL Recipes by David Dye, Jason Brimhall, Timothy Roberts, Wayne Sheffield, Joseph Sack, Jonathan Gennick]

Declare @my_new_query NVARCHAR(MAX) = '
Declare
@x AS decimal(3,2) = 2,
@y AS int = 3;
 SELECT ISNULL(@x,@y) as ISNULL_with_first_input_as_decimal,
        ISNULL(@y,@x) as ISNULL_with_first_input_as_int,
  COALESCE(@x,@y) as COALESCE1,
  COALESCE(@y,@x) as COALESCE2;';

EXEC sp_executesql @my_new_query;

SELECT column_ordinal, is_nullable, system_type_name
from master.sys.dm_exec_describe_first_result_set(@my_new_query, NULL, 0) a ;

ISNULL_with_first_input_as_decimal	ISNULL_with_first_input_as_int	COALESCE1	COALESCE2
2.00	3	2.00	3.00

column_ordinal	is_nullable	system_type_name
1	1	decimal(3,2)
2	1	int
3	1	decimal(12,2)
4	1	decimal(12,2)

From within decimal and int, decimal has higher precedence.

2. Validations for ISNULL and COALESCE are also different. For example, a NULL value for ISNULL is converted to int whereas for COALESCE, you must provide a data type.

SELECT 'ISNULL',name, system_type_name
FROM sys.dm_exec_describe_first_result_set ('SELECT ISNULL(NULL, NULL)', NULL,0)

SELECT 'COALESCE', name, system_type_name
FROM sys.dm_exec_describe_first_result_set (N'SELECT COALESCE(CAST(NULL as Decimal), NULL);', NULL,0)

(No column name)	name	system_type_name
ISNULL	NULL	int

(No column name)	name	system_type_name
COALESCE	NULL	decimal(18,0)

[Via]

dm_exec_describe_first_result_set is dynamic management function takes a Transact-SQL statement as a parameter and describes the metadata of the first result set for the statement.

3. ISNULL takes 2 parameters only but COALESCE takes a variable number of parameters.

You can also look at the below table which clearly depicts the difference between isNull & COALESCE

[Via: Exam Ref 70-761 Querying Data with Transact-SQL by Itzik Ben-Gan]

SQL Server - COLLATE Examples

SQL Server - COLLATE Examples

Collate is used to display and store an international character set, based on the requirements. You can specify collation during a SELECT statement, while creating a database, while creating a table, in the WHERE clause etc. Below are few self explanatory examples related to SQL Server Collate. These are very simple examples of Collate in order to give you an idea as to how Collate works.

Lets create a table, insert few values in it and use it in some of our examples.

CREATE TABLE test
(mychar varchar(15) NOT NULL);  
GO  

INSERT test(mychar) VALUES ('ca');  
INSERT test(mychar) VALUES ('ch');
INSERT test(mychar) VALUES ('ci');
GO  

Select * from test

mychar
ca
ch
ci

Example 1 of Collate

Using Collate in the SELECT Statement

Applying typical collation

SELECT mychar FROM test  
ORDER BY mychar 
COLLATE Latin1_General_CS_AS_KS_WS ASC;  

mychar
ca
ch
ci

Example 2 of Collate

Applying Spanish collation

SELECT mychar FROM test  
ORDER BY mychar 
COLLATE Traditional_Spanish_ci_ai ASC;  

mychar
ca
ci
ch

Example 3 of Collate

Using Collate in the CREATE TABLE Clause

CREATE TABLE test1
  (mychar varchar(15)
   COLLATE Traditional_Spanish_ci_ai NOT NULL  
  );  
GO  

INSERT test1(mychar) VALUES ('ca');  
INSERT test1(mychar) VALUES ('ch');
INSERT test1(mychar) VALUES ('ci');
GO  

Select * from test1
ORDER BY mychar ASC

mychar
ca
ci
ch

If you try to do it without Collate in the Create Table Clause then the results will be different:

CREATE TABLE test2
  (mychar varchar(15) NOT NULL  
  );  
GO  

INSERT test2(mychar) VALUES ('ca');  
INSERT test2(mychar) VALUES ('ch');
INSERT test2(mychar) VALUES ('ci');
GO  

Select * from test2
ORDER BY mychar ASC

mychar
ca
ch
ci

You can use Collate with Alter Table Clause also.

Example 4 of Collate

Using Collate in the WHERE Clause

CREATE TABLE test3
  (mychar varchar(15)
   
  );  
GO  

INSERT test3(mychar) VALUES ('case');  
INSERT test3(mychar) VALUES ('Case');
INSERT test3(mychar) VALUES ('CASE');
GO  

Select * from test3

mychar
case
Case
CASE

SELECT *
FROM test3
WHERE mychar = 'Case'

mychar
case
Case
CASE

SELECT *
FROM test3
WHERE mychar COLLATE Latin1_General_CS_AS = 'Case'

mychar
Case

Example 5 of Collate

Casting the Collation

Here nvarchar is being converted into varchar

SELECT SQL_VARIANT_PROPERTY(N'abc','BaseType') BaseType

BaseType
nvarchar

SELECT SQL_VARIANT_PROPERTY(
(SELECT CAST(N'abc' AS varchar(5)) 
COLLATE French_CS_AS),'BaseType') BaseType1

BaseType1
varchar

Example 6 of Collate

Using CONVERT with COLLATE

SELECT
CONVERT(varchar(50), N'æøåáäĺćçčéđńőöřůýţžš')
COLLATE Cyrillic_General_CI_AI

(No column name)
?oaaalcccednooruytzs

[Source of Example 6]

SQL Introduction

SQL stands for Structured Query Language. and it is generally referred to as SEQUEL. SQL is simple language to learn. SQL is a Nonprocedural language, as compared to the procedural or third generation languages (3GLs) such as COBOL and C. SQL was developed by IBM in the 1970s.

The American National Standards Institute (ANSI) published its first SQL standard in 1986 and a second widely adopted standard in 1989. ANSI released updates in 1992, known as SQL92 and SQL2, and again in 1999, termed both SQL99 and SQL3. Each time, ANSI added new features and incorporated new commands and capabilities into the language.

SQL is a simple, yet powerful, language used to create, access, and manipulate data and structure in the database.

SQL Statements categories: DDL - Data Definition Language.

DDL is used to define, alter, or drop database objects and their privileges. DDL statements will implicitly perform a commit.

DDL Statements:

Create	It is used to create objects(tables, views) in the database.
Alter	It is used to alter the structure of the database objects.
Drop	delete database objects (It will invalidate the dependent objects ,it also drops indexes, triggers and referential integrity constraints ).
Truncate	remove all records from a table, including all spaces allocated for the records are removed (It is fast as compared to Delete and does not generate undo information as Delete does. It performs an implicit commit as it is a DDL. It resets the high water mark.)
Grant	assigning privileges

DML - Data Manipulation Language.

DML is used to access, create, modify or delete data in the structures of the database.

DML Statements:

Select	Select data from the database
Insert	It is used to insert data into a table
Update	It is used to update existing data within a table
Delete	It removes rows from the table.

DCL - Data Control Language

Following are the examples of Data control Statements.

DCL Statements:

Commit	It will end the current transaction making the changes permanent and visible to all users..
Savepoint	It will identify a point(named SAVEPOINT) in a transaction to which you can later roll back
Rollback	It will undo all the changes made by the current transaction.
Set- Transaction	It is used to define the properties of a transaction.

Multiple Choice Questions - SQL Joins - Set 7

Multiple Choice Questions - SQL Joins - Set 7

1. Which of the below point(s) are true with respect to joins:

A) Null values never join.
B) Columns used in the joins mush have compatible data types.
C) Join column is usually a primary key or foreign key.
D) Cross join without a WHERE clause returns a Cartesian product.
E) A & C
F) All of above

2.

Orders table
Order_no, Cust, Prodt, Qty, Amt, Discount

Customers table
Custnbr, Company, Custrep, Creditlim

Print all the orders showing order number, amount, company name and credit limit of customers.

A)

Select Order_no, Amt, Company, Creditlim 
from Customers outer join Orders 
on customers.custnbr = orders.cust;

B)

Select Order_no, Amt, Company, Creditlim 
from Customers left outer join Orders 
on customers.custnbr = orders.cust;

C)

Select Order_no, Amt, Company, Creditlim 
from Customers inner join Orders 
on customers.custnbr = orders.cust;

D)

Select Order_no, Amt, Company, Creditlim 
from Customers right outer join Orders 
on customers.custnbr = orders.cust;

3. . . . . . matches the rows using a non-equal condition, that is, using a comparison operator other than equality like <,>,<=,>=,<>.

A) Multiple condition join
B) Theta join
C) Semi join
D) Self join

4.

Orders table
order_number, customer, prod, qty, cost, disc

Customers table
customer_number, company, cust_rep

Sales_Persons table
repnbr, name, rep_office, quota, sales

Display all the orders over $95000 along with the name of the salesperson who took the order and the name of the customer who placed it.

A)

Select order_number, cost, company, name, 
From orders, customers, Sales_Persons
Where customer = customer_number and 
cust_rep = repnbr and cost <= 95000;

B)

Select order_number, cost, company, name, 
From orders, customers, Sales_Persons
Where customer = customer_number or 
cust_rep = repnbr and cost >= 95000;

C)

Select order_number, cost, company, name, 
From orders, customers, Sales_Persons
Where customer = customer_number and 
cust_rep = repnbr or cost >= 95000;

D)

Select order_number, cost, company, name, 
From orders, customers, Sales_Persons
Where customer = customer_number and 
cust_rep = repnbr and cost >= 95000;

5.
Select * from Employees1

Empno	Ename	Title	Mgr
1	n1	t1	3
2	n2	t2	3
3	n3	t3	6
4	n4	t4	3
5	n5	t5	6
6	n6	t6	3

We want the result as shown below:

employee	manager
n1	n3
n2	n3
n3	n6
n4	n3
n5	n6
n6	n3

What query or queries can be used to get the above result.

A)

Select emp.ename as employee, manager.ename as manager
from Employees1 as emp 
join 
Employees1 as manager 
on emp.Mgr = manager.Empno

B)

Select emp.ename as employee, manager.ename as manager
from Employees1 as emp 
inner join 
Employees2 as manager 
on emp.Mgr = manager.Empno

C)

Select emp.ename as employee, manager.ename as manager
from Employees1 as emp 
inner join 
Employees1 as manager 
on emp.Mgr = emp.Empno

D)

Select emp.ename as employee, manager.ename as manager
from Employees1 as emp 
inner join 
Employees1 as manager 
on emp.Mgr = manager.Empno

6. In case of inner joins the order of the data sources is very important.

A) True
B) False

7. In case of outer joins, if the condition is in the . . . . . . . , SQL Server includes all rows from the outer table and then uses the condition to include rows from the second table. If the condition is in the . . . . . . . . , the join is performed and then the where clause is applied to the joined rows.

A) FROM clause, WHERE clause
B) JOIN clause, WHERE clause
C) SELECT clause, FROM clause
D) JOIN clause, SELECT clause

8.

Customers table
Custnbr, Company, Custrep, Creditlim

Orders table
Order_no, Cust, Prodt, Qty, Amt, Discount

Find all the customers with orders more than 500 or credit limits greater than or equal to 500.

A)

Select distinct Custnbr
from Customers Right JOIN Orders 
on Custnbr = Cust
where (Creditlim >= 500 OR Amt > 500)

B)

Select distinct Custnbr
from Customers LEFT JOIN Orders 
on Custnbr = Cust
where (Creditlim > 500 OR Amt >= 500)

C)

Select Custnbr
from Customers LEFT JOIN Orders 
on Custnbr = Cust
where (Creditlim > 500 OR Amt > 500)

D)

Select distinct Custnbr
from Customers LEFT JOIN Orders 
on Custnbr = Cust
where (Creditlim >= 500 OR Amt > 500)

9. Every outer join operation can be simulated using the . . . . . . operator plus the NOT EXISTS function.

A) IN ( )
B) LIKE
C) UNION
D) EXISTS

10. It is not necessary that only primary and foreign keys should be used for joins. The join can match a row in one table with a row in another table using any column as long as the columns share compatible data types and the data match.

A) Ture
B) False

11. SQL Server join type "nested loop" usually works well in case of . . . . . . .

A) small tables
B) medium sized tables
C) large tables
D) all of above

Answers

1) f, 2) c, 3) b, 4) d, 5) a,d, 6) b, 7) b, 8) d, 9) c, 10) a, 11) a