Pages

Saturday 27 October 2012

SQL SERVER - SEQUENCE in SQL SERVER 2012

SEQUENCE is user-defined object that has start value, increment value and end value defined in them and this sequence can be added to a column whenever required rather than defining an identity column individually for tables. It generates a sequence of numeric values according to the specification with which the sequence was created. The sequence of numeric values is generated in an ascending or descending order at a defined interval and can be configured to restart (cycle) when exhausted. Sequences are not associated with specific tables like identity columns. The relationship between sequences and tables is controlled by the application.
A sequence can be defined as any integer type. Also the following types are allowed: tinyint, smallint, int, bigint, decimal and numeric with a scale of 0. Any user-defined data type (alias type) that is based on one of the allowed types.
If no data type is provided, the bigint data type is used as the default.

There are different ways to create SEQUENCE, like:

1. SQL Server Management Studio
2. TSQL statement

Let us take a quick look to create a SEQUENCE using TSQL Statements. It can be created using CREATE SEQUENCE Syntax

---- Create a SEQUENCE object on schema "dbo" by the name of TEST_Sequence
CREATE SEQUENCE SEQ_MyTestSequence  
AS INT  
START WITH 1  
INCREMENT BY 1  
MINVALUE 0  
NO MAXVALUE  

---- Create table Customer
CREATE TABLE Customer
(
CustomerID  INT NOT NULL,
CustomerName VARCHAR(100)
)
GO
Now, Populate Customer table using SEQ_MyTestSequence to generate the CustomerId column:

INSERT INTO Customer(CustomerID, CustomerName)
VALUES
(NEXT VALUE FOR SEQ_MyTestSequence, 'JOHN'),
(NEXT VALUE FOR SEQ_MyTestSequence, 'ANTHONY'),
(NEXT VALUE FOR SEQ_MyTestSequence, 'JUANA')
Lets us look at results, which we inserted using SEQUENCE

SELECT * FROM Customer

We should note that, sequence are global to a database and not dependent on tables. Let us create a new table named Account and use the same sequence which we used for Cutomer table in past to understand.


--- Create a new table called Account

CREATE TABLE Account(
[AccountID] INT NOT NULL,
[AccountNumber] [varchar](6) NULL
) 
GO

INSERT INTO Account(AccountID,AccountNumber)
VALUES
(NEXT VALUE FOR SEQ_MyTestSequence, '000001'),
(NEXT VALUE FOR SEQ_MyTestSequence, '000002')
GO

SELECT * FROM Account

In above result, NEXT value of SEQUENCE has given a next number of SEQUENCE. Last number was 3 that was used in Customer)

Every time when we use NEXT VALUE FOR SEQ_MyTestSequence, it will give a new value. for example selecting or printing NEXT value of SEQUENCE Every time we will get a New Value

SELECT NEXT VALUE FOR SEQ_MyTestSequence 

Let us see how to check the current value of the SEQUENCE:
SELECT current_value FROM sys.sequences WHERE name = 'SEQ_MyTestSequence'

With the help of following statement we can alter a SEQUENCE:

ALTER SEQUENCE SEQ_MyTestSequence
RESTART WITH 101

In above code, we have altered a SEQUENCE and started with 101, let us check it by running the following code

SELECT current_value FROM sys.sequences WHERE name = 'SEQ_MyTestSequence'

We can change the MAX and MIN value of a SEQUENCE:

ALTER SEQUENCE SEQ_MyTestSequence
MINVALUE 101
MAXVALUE 1011
CYCLE
Above code will alter SEQUENCE and specify start as 101 and MAX as 1011, once the MAX Value is reached, SEQUENCE numbers will be restarted from MINVALUE.

Now, verify MAX value and CYCLE Clause

SELECT *,NEXT VALUE FOR SEQ_MyTestSequence as NextId
FROM Account 

We can drop a SEQUENCE with the help of following statement:
DROP SEQUENCE SEQ_MyTestSequence 

Reference: http://msdn.microsoft.com/en-us/library/ff878091.aspx

Saturday 22 September 2012

SQL SERVER - Different ways to know when was SQL Server restarted

Following are the ways to get the timestamp when SQL Server started:

Method 1: In SQL Server 2008 and above, with the DMV sys.dm_os_sys_info returns a column called sqlserver_start_time. This column contains the date and time SQL Server was last started:
SELECT sqlserver_start_time FROM sys.dm_os_sys_info 

Method 2:
SELECT start_time FROM sys.dm_exec_requests WHERE (session_id = 1) 

Method 3:
SELECT MIN(login_time) FROM sys.sysprocesses 

Method 4: TempDb is created every time SQL Server is started, so another workaround is to look at the creation date of the TempDb database:
SELECT create_date FROM sys.databases WHERE name = 'tempdb' 
OR
SELECT crdate FROM sysdatabases WHERE name='tempdb' 

Method 5:
SELECT start_time FROM sys.traces WHERE is_default = 1 

Method 6: Dashboard Reports
We can use the SQL Server Dashboard Report in SQL 2008 and above. Right click on the server name in SSMS and select Reports -> Server Reports -> Server Dashboard and you will get a report in which we can find Server Start Time.

Method 7: Windows Event Viewer
Windows Event Viewer is also an option to look for the start up time. If we open Windows Event Viewer and filter Event Sources for the SQL Server instance you can find all of the start up times that are still in the Event Viewer log.

Method 8: SQL Server Error Log
If you open the current SQL Server error log in SSMS under Management > SQL Server Logs and scroll to the bottom of the error log you can see when the server was started. Note the text "SQL Server is starting". You will want to look for this to make sure the error log was not cycled and give you a false impression of the start time. The error log method can be used for any version of SQL Server.
SP_ReadErrorLog 0,1,'Copyright (c)' 

Sunday 16 September 2012

SQL SERVER - Dynamic sorting using CASE statement

Let us look at the different examples to sort a field dynamically based on parameter supplied by the user in SQL Server. We can implement this by using dynamic SQL where we create a query on the fly and execute it, but dynamic SQL has many issues and very few advantages. If we are willing to limit ourselves to a fixed number of columns to sort then it is possible to achieve without dynamic SQL otherwise completely dynamic sorting by using several CASE statements will be the solution. We can write these types of queries using CASE statement with ORDER BY clause.

Following examples will demonstrate to use several CASE statements in ORDER BY to sort:

-- -- First, create a new database and a table to test
GO
CREATE DATABASE MyTestDB
GO

USE MyTestDB
GO
CREATE TABLE dbo.Employees (
EmployeeID INT IDENTITY(1,1),
EmployeeName VARCHAR(15),
Department VARCHAR(15),
Salary NUMERIC(16,2)
)

INSERT INTO dbo.Employees(EmployeeName, Department, Salary)
SELECT 'Barry','IT', 80000 UNION
SELECT 'Jones','IT', 40000 UNION
SELECT 'Billy','IT', 50000 UNION
SELECT 'Ralph','IT', 50000 UNION
SELECT 'Aaron','Finance', 40000 UNION
SELECT 'Barney','Finance', 25000 UNION
SELECT 'Callum','Finance', 25000 UNION
SELECT 'Oliver','Finance', 15000 UNION
SELECT 'Anderson','HR', 25000 UNION
SELECT 'Martin','HR', 15000 UNION
SELECT 'Garcia','HR', 15000 UNION
SELECT 'Caine','HR', 10000

Now create stored procedures to get sorted records from Employees table based on given field.

-- 1 -- Decide field to sort but without dynamic sort order.
CREATE PROCEDURE dbo.GetAllEmployees_1
(
@SortField VARCHAR(50) = 'EmployeeName'
)
AS
BEGIN

SELECT EmployeeName, Department, Salary
FROM dbo.Employees
ORDER BY
CASE
WHEN @SortField = 'EmployeeName' THEN EmployeeName
ELSE @SortField
END
ASC

END
GO

-- Execute the stored procedure created above:

EXEC dbo.GetAllEmployees_1 'EmployeeName'

-- 2 -- User option to sort on any field and sort in ascending or descending order method 1.
CREATE PROCEDURE dbo.GetAllEmployees_2
(
@SortField VARCHAR(50) = 'EmployeeName',
@SortDir VARCHAR(4) = 'ASC'
)
AS
BEGIN

SELECT EmployeeName, Department, Salary
FROM dbo.Employees
ORDER BY
CASE WHEN @SortField = 'EmployeeName' AND @SortDir = 'ASC' THEN EmployeeName END
ASC,
CASE WHEN @SortField = 'EmployeeName' AND @SortDir = 'DESC' THEN EmployeeName END
DESC,
CASE WHEN @SortField = 'Department' AND @SortDir = 'ASC' THEN Department END ASC,
CASE WHEN @SortField = 'Department' AND @SortDir = 'DESC' THEN Department END
DESC,
CASE WHEN @SortField = 'Salary' AND @SortDir = 'ASC' THEN Salary END ASC,
CASE WHEN @SortField = 'Salary' AND @SortDir = 'DESC' THEN Salary END DESC

END
GO

-- Execution of the following stored procedures will show the records sorted based on given field and based on given sort order:

EXEC dbo.GetAllEmployees_2 'EmployeeName', 'ASC'
EXEC dbo.GetAllEmployees_2 'EmployeeName', 'DESC'

-- 3 -- User option to sort on any field and sort in ascending or descending order method 2
CREATE PROCEDURE dbo.GetAllEmployees_3
(
@SortField VARCHAR(50) = 'EmployeeName',
@SortDir VARCHAR(4) = 'ASC'
)
AS
BEGIN

SELECT EmployeeName, Department, Salary
FROM dbo.Employees
ORDER BY
CASE WHEN @SortField = 'EmployeeName' AND @SortDir = 'ASC' THEN EmployeeName END
ASC,
CASE WHEN @SortField = 'EmployeeName' AND @SortDir = 'DESC' THEN EmployeeName END
DESC,
CASE WHEN @SortField = 'Department' AND @SortDir = 'ASC' THEN Department END ASC,
CASE WHEN @SortField = 'Department' AND @SortDir = 'DESC' THEN Department END
DESC,
CASE WHEN @SortField = 'Salary' AND @SortDir = 'ASC' THEN Salary END ASC,
CASE WHEN @SortField = 'Salary' AND @SortDir = 'DESC' THEN Salary END DESC

END
GO

-- Execute the following code to test the above created stored procedure:

EXEC dbo.GetAllEmployees_3 'EmployeeName', 'ASC'
EXEC dbo.GetAllEmployees_3 'EmployeeName', 'DESC'

Sometimes want to display records based on priority that also could done by dynamic sorting using CASE statement. In the following example we will sort the field Department based on priority where department “IT” should be first then “Finance” and then “HR”.

-- 4 -- Order by priority.
CREATE PROCEDURE dbo.GetAllEmployees_4
AS
BEGIN

SELECT EmployeeName, Department, Salary
FROM dbo.Employees
ORDER BY
CASE
WHEN Department = 'IT' THEN 1
WHEN Department = 'Finance' THEN 2
WHEN Department = 'HR' THEN 3
END ASC

END
GO

-- Execute the following code to test:
EXEC dbo.GetAllEmployees_4

In above example, the data type must be the same for sort field. In this example the data type is of integer. This can be helpful where in some situations we want display data based on priority.

Sunday 24 June 2012

SQL SERVER - Single DROP statement to drop multiple tables

We can drop multiple tables using a single DROP statement. For example, we have three tables Table1, Table2, Table3, we want to drop all these tables, and we can do it by following statement:
DROP TABLE Table1, Table2, Table3
Following is an example to drop multiple tables using single DROP statement:

CREATE DATABASE MyTestDB
GO

USE MyTestDB
GO

CREATE TABLE [dbo].[Table1](
[ID] INT IDENTITY(1,1) PRIMARY KEY,
[Column1] [varchar](6) NULL
) 
GO

CREATE TABLE [dbo].[Table2](
[ID] INT IDENTITY(1,1) PRIMARY KEY,
[Column2] [varchar](6) NULL
) 
GO

CREATE TABLE [dbo].[Table3](
[ID] INT IDENTITY(1,1) PRIMARY KEY,
[Column3] [varchar](6) NULL
) 
GO

We have created three tables above in database MyTestDB, now we can drop all three table by:
DROP TABLE Table1, Table2, Table3

-- Clean up 
USE MASTER
GO
DROP DATABASE MyTestDB

Saturday 2 June 2012

SQL SERVER - View Query Editor Window In Full Screen Mode

We can view query window in full screen mode by enabling Full Screen mode.It will hide all tool windows and view only document windows. Click anywhere in the Query Editor window to activate the window.

To enable Full Screen mode:

Press ALT + SHIFT + ENTER to toggle Full Screen mode.
OR
Go to View menu >> Select Full Screen Option

To disable Full Screen mode:
Press "Full Screen" button located at top in the menu bar.

By this way we can view SQL Server Management Studio in full screen mode.

Sunday 20 May 2012

SQL SERVER - Display Line Numbers In Query Editor Window

We can display line numbers in query editor window . Line numbers are useful for navigating code in SQL Server Management Studio

Go to menu Tools >> select Options >> Click Text Editor >> Click All Languages >> Click General. Select Line numbers in Display section.

To view line numbers in only some programming languages, select Line Numbers in the appropriate folder.

It should be noted that, document will not be printed with line numbers by turning on line numbering. For line numbers to print, we must select the Include Line Numbers check box in the Print command on the File menu.

Reference: Enabling Full Screen Mode

Saturday 12 May 2012

SQL SERVER - Add New Column In Existing Table With Default Value

Many times, we need to add new column in our existing database table. Many of us prefer to use a table designer to add new column, I would like to use SQL query to do this operation. If we add new column through designer it will not update the default value for that column and we need to update that column. With the help of following query, we can add new column to our existing table with default value we want to assign to that column without executing update statement.

ALTER TABLE [dbo].[Account]
ADD Active BIT DEFAULT 1 WITH VALUES;

Following is the complete example to test the above SQL statement.
USE [Master]
GO
CREATE DATABASE MyTestDB
GO

USE MyTestDB
GO
CREATE TABLE [dbo].[Account](
 [AccountID] INT IDENTITY(1,1) PRIMARY KEY,
 [AccountNumber] [varchar](6) NULL,
 [BranchName] [varchar](6) NULL,
 [Balance] [numeric](8, 2) NULL
) 
GO

INSERT INTO dbo.Account(AccountNumber,BranchName,Balance)
SELECT '000001','Br001',1202.00
UNION SELECT '000002','Br002',904.00

SELECT * FROM dbo.Account

ALTER TABLE [dbo].[Account]
ADD Active BIT DEFAULT 1 WITH VALUES;

SELECT * FROM dbo.Account

DROP TABLE [dbo].[Account]

USE MASTER
GO
DROP DATABASE MyTestDB

In above example, it will add a new column ‘Active’ with default value 1 for all the rows in table ‘[dbo].[Account]'. The above example shows that we can add new column in existing table with default value in SQL SERVER using ALTER TABLE statement.

Saturday 5 May 2012

SQL SERVER - View full content of a text or varchar(MAX) column

Recently I came across with a problem where I had to debug the dynamic SQL stored in large dynamic SQL varchar datatype variable and could not select in the result grid or print the whole SQL script because of SQL server limitation of displaying maximum number of text when dealing with large dynamic SQL variables.

Therefore, I started to search the web and found one tricky solution. Here I want to share that solution for such kind of problem.

DECLARE @str AS VARCHAR(MAX)
SELECT @str = REPLICATE(CAST('A' AS VARCHAR(MAX)), 65535)

SELECT [processing-instruction(X)] = @str 
FOR XML PATH(''),TYPE

This will convert the dynamic SQL to XML, instead of printing the dynamic SQL to the message tab. XML
The column name [processing-instruction(x)] sends special XML instruction allowing the text to be converted, along with any special characters. XML also keeps the formatting and can store up to 2 GB of data. It should be noted that whatever value you put in parenthesis would be incorporated in the XML tags, in this case “X”. We can strip of the XML tags from the top and bottom of the query text and go ahead with debugging.

Reference: Allow large text to be displayed in as a link

Friday 27 April 2012

SQL SERVER - Find Currently Running Queries

Following is the SQL script to list out which queries are currently running on SQL server database.

SELECT 
 OBJECT_NAME(t.ObjectID) as ObjectName,
 SUBSTRING(t.text,(statement_start_offset/2)+1,
  (( CASE statement_end_offset
   WHEN -1 THEN DATALENGTH(t.text)
   ELSE statement_end_offset
  END - statement_start_offset)/2) + 1
 ) AS StatementText,
 db_name(database_id) as DatabaseName,
 req.blocking_session_id as blocking_session_id,
 req.session_id,
 req.start_time,
 req.[status],
 req.command,
 req.open_transaction_count,
 req.percent_complete,
 req.cpu_time,
 req.total_elapsed_time
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(req.[sql_handle]) AS t

GO

The above query helps to identify queries that are running for long time so that an appropriate action can be taken.

more about sys.dm_exec_requests
more about sys.dm_exec_sql_text

Sunday 22 April 2012

SQL Server - Convert Comma Separated String to Table Column


In previous post I mentioned different ways to get comma separated values (CSV) from a table column.
Following are the different ways to convert comma separated string to column:

-- -- Using while loop: 
DECLARE @strCSVString VARCHAR(4000)
SELECT @strCSVString = '1,2,3,4,5'
DECLARE @Result TABLE(ColumnValue NVARCHAR(100))

DECLARE @str VARCHAR(20)
DECLARE @idx Int
IF ISNULL(@strCSVString,'') <> ''
BEGIN
    SET @idx = CHARINDEX(',' , @strCSVString)
    WHILE @idx > 0
    BEGIN
          SET @str = SUBSTRING(@strCSVString, 1, @idx - 1)
          SET @strCSVString = SUBSTRING(@strCSVString, @idx + 1, LEN(@strCSVString) - @idx)
          INSERT INTO @Result values (@str)
          SET @idx = CHARINDEX(',' , @strCSVString)
    END
    SET @str = @strCSVString
    INSERT INTO @Result values (@str)
END
SELECT * FROM @Result 
GO

-- -- Using Common Table Expression (CTE): 
DECLARE @strCSVString VARCHAR(4000)
SELECT @strCSVString = '1,2,3,4,5'
DECLARE @Result TABLE(ColumnValue NVARCHAR(100))

; WITH CTE(Start, [Stop]) AS
(
  SELECT  1, CHARINDEX(',' , @strCSVString )
  UNION ALL
  SELECT  [Stop] + 1, CHARINDEX(',' ,@strCSVString  , [Stop] + 1)
  FROM CTE
  WHERE [Stop] > 0
)
INSERT INTO @Result
SELECT  SUBSTRING(@strCSVString , Start, CASE WHEN stop > 0 THEN [Stop]-Start ELSE 4000 END) AS stringValue
FROM CTE
        
SELECT * FROM @Result 
GO

-- -- Using XML: 
DECLARE @strCSVString VARCHAR(4000)
SELECT @strCSVString = '1,2,3,4,5'
DECLARE @Result TABLE(ColumnValue NVARCHAR(100))

DECLARE @xmlData XML 
SELECT @xmlData = CAST('<Col>'+ REPLACE(@strCSVString,',','</Col><Col>')+ '</Col>' AS XML)

INSERT INTO @Result            
SELECT d.value('.', 'int') AS Val
FROM @xmlData.nodes('/Col') AS x(d)
SELECT * FROM @Result 
GO

We can use the above code by creating a function and can change the delimiter string from comma to any valid character  by having a parameter in function.
For example, let's create a funtion, dbo.Split() that converts comma delimited string in to a table column.

CREATE FUNCTION [dbo].[Split]
(
@strCSVString nvarchar(2000),
@Delimiter nvarchar(5)
)  
RETURNS @Result TABLE (ColumnValue NVARCHAR(100)) 
AS  
BEGIN
  ;WITH CTE(Start, [Stop]) AS
(
 SELECT  1, CHARINDEX(@Delimiter , @strCSVString )
 UNION ALL
 SELECT  [Stop] + 1, CHARINDEX(@Delimiter, @strCSVString, [Stop] + 1)
 FROM CTE
 WHERE [Stop] > 0
)
INSERT INTO @Result
SELECT  SUBSTRING(@strCSVString , Start, CASE WHEN stop > 0 THEN [Stop] - Start ELSE 4000 END) AS StringValue
FROM CTE
 RETURN
END
GO

-----------------
SELECT * FROM  dbo.[Split]('1,2,3,4,5' , ',')
SELECT * FROM  dbo.[Split]('1|2|3|4|5' , '|')
-----------------
Output:

The all above queries will give the same result as:



SQL SERVER - Questions with answers - 1



Question:

What is PRIMARY KEY?

Answer:
The PRIMARY KEY is a field or column that uniquely identifies each record of the table, each table can have only one key declared as a primary key. We can have PRIMARY KEY constraint on more than one column, so duplicate values allowed in one of the columns, but the combination of values from all the columns in the primary key must be unique. By default, primary key creates clustered index.

Question:
What is FOREIGN KEY?

Answer:
A FOREIGN KEY is a column or combination of columns used to establish and enforce a link between the data in two tables. It ensures that the values that can be entered in a particular column (foreign key) exist in a specified table (primary key). A link is created between two tables by adding the column or columns that hold one table's primary key values to the other table, this column becomes a foreign key in the second table. Therefore, we can say that a FOREIGN KEY in one table points to a PRIMARY KEY in another table.

Question:
What are COMPOSITE KEY, CANDIDATE KEY and ALTERNATE KEY?

Answer:
A key formed by combining at least two or more columns is called a composite key. When multiple fields used as a primary key, they are called a COMPOSITE KEY. One primary key contains more than one column.

A CANDIDATE KEY is one that can identify each row of a table uniquely. A candidate key becomes the primary key of the table. If the table has more than one candidate key, one of them will become the primary key, and the rest are called ALTERNATE KEYs.

Question:
What is the difference between a PRIMARY KEY and a UNIQUE key?

Answer:
Primary keys do not allow NULL values, but unique keys allow one NULL value.
A primary key creates a clustered index on the column and unique keys create a non-clustered index, by default.

We can define only one primary key per table.
We can have more than one UNIQUE constraint per table.

Primary and unique keys enforce uniqueness of the column on which they are defined.
You can use primary key constraints to enforce uniqueness as well as referential integrity.
Primary key defined on a table helps to relate that table to other tables. These relationships help to normalize a database.

Question:
What is NORMALIZATION?

Answer:
Normalization is the process of systematically organizing the data in a database. Normalization process consists of eliminating redundant data by storing the same data in more than one table, and ensuring data dependencies by only storing related data in a table.
Normalization helps to reduce redundancy of data and to make sure that all tables are properly dependent.
The characteristic of a normalized database is the number of small tables join together and produce the required details.
To normalizing the logical database design using formal methods to split the data into multiple and number of related tables.
Reasonable normalization also improves the performance query. When useful indexes are available, and efficient joins between tables are used boost up performance.
Normalization is part of successful database design, without normalization, database systems can be inaccurate, inefficient, and slow they might not produce the data you expect.

Question:
What is DENORMALIZATION?

Answer:
Denormalization is the process of deliberately adding redundant data or grouping data to optimize the read performance of a database. Some times more normalization in a database also reduce the performance of  the process, such as in OLTP applications.  In some cases, denormalization helps cover up the inefficiencies inherent in relational database software. A relational normalized database imposes a heavy access load over physical storage of data even if it is well tuned for high performance. It is the opposite of normalization process.
Denormalization helps to speed up database access by moving from higher to lower form of normalization.

Question:
What is ACID Properties?

Answer:
ACID is an acronym for Atomicity, Consistency, Isolation, and Durability.

ACID property ensures the quality of the transaction.

Atomicity: If the transaction is completed its execution then at the end, either all of the transaction is successful, or all of the transaction fails. If a partial data is written to disk, the atomic property is violated. It is an all-or-none proposition.

Consistency: This property ensures that a transaction never leaves the database in a partially finished state.

Isolation: This isolation level must implies that the transaction must either visible to the data in its previous state or the transaction must wait until the changes from other transactions are committed. It keeps transactions separated from each other until they are finished.

Durability: This property ensures that once the user has been notified of success, the transaction will persist, and not be undone. The database will keep track of pending changes in such a way that the server can recover from an abnormal termination. This means it will survive system failure, and the database system has checked the integrity constraints and will not need to abort the transaction. In other words, when a client application receives notification that a transaction has completed its work successfully, it is guaranteed that the data is changed permanently.

Question:
What is the difference between DBMS and RDBMS

Answer:
DBMS (Database Management System) has to be persistent, i.e. it should be accessible when the program created the data ceases to exist or even the application that created the data restarted. A DBMS also has to provide some uniform methods independent of a specific application for accessing the information that is stored.
DBMS is used for easier business applications

RDBMS (Relational Data Base Management) adds the additional condition that the system provides a tabular structure for the data, with enforced relationships between the tables in the database. This excludes the databases that do not provide a tabular structure or don't enforce relationships between tables.
RDBMS is used for complex applications.
RDBMS that enforces the rules of foreign key concept


Question:
What are the types of constraints?

Answer:
-> PRIMARY KEY
A primary key defines the column or combination of columns that allow a row to be uniquely  identified. Each table can have only one key declared as a primary key.

Example:
You implement a primary key on the CustomerID column of the Customers table as follows:

CREATE TABLE Customers (
                CustomerID                        INT IDENTITY(1,1) PRIMARY KEY,
                CustomerName                 VARCHAR(50) NOT NULL,
                CreatedDate                         DATETIME DEFAULT (GETDATE()),
)
GO

-> FOREIGN KEY
Foreign key constraints can be used to implement a concept called referential integrity. It ensures that the values that can be entered in a particular column exist in a specified table (i.e. PRIMARY KEY table). Values cannot be inserted in this column that do not exist in the specified table. A FOREIGN KEY in one table points to a PRIMARY KEY in another table.

Example:
Create Orders table and add foreign key constraint to Customers table CustomerID column.
CREATE TABLE Orders(
                OrderID                INT IDENTITY(1,1) PRIMARY KEY,
                CustomerID        INT NOT NULL REFERENCES Customers(CustomerID),
                OrderDate          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
)

-> CHECK CONSTRAINTS
A CHECK constraint defines a condition for one or more columns in a table at the time of INSERT and UPDATE operations.
Condition can be defined by any expression that returns TRUE or FALSE value. If the condition returns value TRUE then
The operation continues, but if the condition returns FALSE value, the operation is rolled back.

Example:
CREATE TABLE Employees (
                EmployeeID                       INT IDENTITY(1,1) NOT NULL,
                EmployeeName               VARCHAR(50) NOT NULL,
                ZipCode                               VARCHAR(5) NOT NULL CHECK (ZipCode LIKE '[0-9][0-9][0-9][0-9][0-9]')
)
GO

Values rejected by the CHECK constraints that evaluate to FALSE. Because NULL values evaluate to UNKNOWN, their presence in expressions may override a constraint. For example, suppose there is a constraint on an INT column "Unit" specifying that "Unit" can contain only the value 20 (Unit=20). If we try to insert the value NULL into "Unit" then the database engine inserts NULL and does not return an error for this.

-> DEFAULT CONSTRAINT
DEFAULT condition can be defined for columns to avoid repetitive data entry. If a column has a DEFAULT
Constraint then the default value will be supplied if you don't provide a specific value for the column in the INSERT statement.  We can provide DEFAULT definition as any expression that evaluates to a single scalar value with a data type that is compatible with the data type of the column in which the DEFAULT definition is defined.
This constraint is applied only in INSERT operations.
This expression can be a constant value, any scalar function in the system, the result of a scalar user defined function, any scalar expression created from any combination constant value or system scalar function or user defined function, including mathematical expressions.

Example:
CREATE TABLE Customers (
                CustomerID                        INT IDENTITY(1,1) NOT NULL,
                CustomerName                VARCHAR(50) NOT NULL,
                CreatedDate      DATETIME DEFAULT (GETDATE()),
)
GO

-> RULES
Rules provide the same functionality as check constraints, except that rules are created as a separate object. Because rules are not associated with a specific table or column when you create them, they cannot reference columns or tables in their definition. Instead, you use variables as placeholders. We define check constraints within the table definition and cannot reuse them. Rules provide the same features and complex comparisons via AND, OR, and NOT as check constraints and allow pattern matching. The following examples show the previous two check constraints implemented as rules:

CREATE RULE EmailValidatorRule
AS
@EmailValue like ‘%@%.[a-z][a-z][a-z]‘ OR @EmailValue like ‘%@%.[a-z][a-z].[a-z][a-z]‘;

After defining a rule, you then bind it to columns or user-defined data types by using the sp_bindrule system stored procedure as EXEC sp_bindrule 'EmailValidatorRule', 'dbo.mytesttable.Email'.

-> UNIQUE CONSTRAINT
We can create a PRIMARY KEY to enforce uniqueness in a field or group of fields, but we can have only one PRIMARY KEY per table.
Therefore, if we require enforcing uniqueness in other columns, you can create a UNIQUE constraint. Unique keys allow one NULL value.

Example:
CREATE TABLE Employees (
                EmployeeID                     INT NOT NULL UNIQUE NONCLUSTERED,
                EmployeeName               VARCHAR(50) NOT NULL
)
GO


Question:
What is a Subquery?

Answer:
A subquery or Inner query or Nested query is a nested select statement where the inner select statement is evaluated first. It is a query in a query. We can use the results of the inner select statement as the input for another select statement. For example, you can use the results of a subquery as a search condition that uses the IN ( ) function or EXISTS operator. A subquery is usually added in the WHERE Clause of the sql statement.

Example:
USE NORTHWIND
GO
SELECT * FROM Orders
WHERE OrderID IN(SELECT OrderID FROM [Order Details] WHERE OrderId = 90425)

Question:
What is a Correlated Subquery?

Answer:
It is a subquery in which the inner query is evaluated once for every value returned by the outer query. Generally a query which has a subquery will execute the subquery once and replace the resulting value or values into the WHERE clause of the outer query.
Correlated subquery (also known as a synchronized subquery or a repeating subquery) is a subquery that uses values from the outer query in its WHERE clause. The subquery is evaluated once for each row processed by the outer query. In queries that include a correlated
subquery, the subquery depends on the outer query for its values.

For example, using the Northwind database, if you want to find out the price of all the books ordered, you can use a correlated subquery. Price information about each book is in the [Order Details] table, and the
Orders table has all the books ordered.
Example:
USE NORTHWIND
GO
SELECT
                o.OrderID,
                o.OrderDate,
                (SELECT MAX (d.UnitPrice) FROM [Order Details] AS d WHERE d.OrderID = o.OrderID) AS MaximumUnitPrice
FROM Orders AS o

Question:
What is Stored Procedures?

Answer:
A stored procedure is a set of named Transact-SQL statement that is stored in the database in compiled form so that it can be shared by a number of programs. The main difference between a stored procedure and a set of statements is that a stored procedure can be reused just by calling its name. Therefore, if you want to re-run the code, you do not have to execute the whole set of statements that compose the stored procedure one by one.

Question:
What are advantages of Stored Procedures?

Answer:
- Stored Procedures allow modular programming
- Stored Procedures are precompiled statements
- Stored Procedures provide faster code execution and optimize network traffic.
- Stored Procedures provide security mechanism by needing to execute any of the statements directly.
- Stored Procedures can be set to execute automatically when SQL Server starts
- Stored Procedures can use parameters

Question:
Can we call a function and stored procedure from a select statement?

Answer:
It is possible to call a function from a select statement. It is not possible to call a stored procedure from a select statement.

Question:
What is INDEX in SQL Server?

Answer:
Indexes are created on columns in tables or views. The index provides a fast way to access data based on the values within those columns. Indexes are constructed as B-tree, which enables SQL Server to search very large volumes of data without affecting the performance from one query to the next. Indexes are useful only if it can help find data quickly regardless of the volume of data stored.


Question:
What are the benefits of Indexes?

Answer:
The database engine can use indexes to boost performance in a number of different queries
-- Searching For Records: When searching for rows with specific key values. These are queries with a
WHERE clause to restrict the query to specific values for every key column. (Exact-match queries)
-- Sorting Records: to produce an ordered output when using the ORDER BY clause.
-- Range queries: when solving queries that search for a range of values in a column.
-- Filtering for values in the foreign key to solve a join operation: When using a JOIN predicate to search for rows in a table based on keys from a second table.
-- Hash and merge join operations: In some cases, having an index can speed up the execution of a
JOIN algorithm, because the data is exactly in the order that the JOIN algorithm uses.
-- Covering a query: To avoid a full-table scan, when a narrow index contains all the required data.
-- Avoiding duplicates: To check for the existence of suitable indexes in an INSERT or UPDATE
operation in an attempt to avoid duplicates.

Question:
Is it possible to create Non-Clustered index on a primary key column?

Answer:
Yes. It is possible to create a non-clustered Index on a primary key column. We can use syntax given below to create a Non-Clustered Index on primary key column while creating table itself.
CREATE TABLE Customers
(
          CustomerID INT IDENTITY(1,1) PRIMARY KEY NONCLUSTERED
)

Question:
Is it possible to create index on more than one column?

Answer:
Yes, it is possible to create index on more than one column. We can include maximum 16 columns as key columns while creating index. Sum of bytes should not be more than 900 bytes.

Question:
How many non-clustered indexes can be created in SQL Server?

Answer:
There can be only 249 non-clustered indexes per table possible in SQL Server 2005 and 999 Non Clustered Indexes per table in SQL Server 2008

Question:
Is it possible to force a query to use a specific Index?


Answer:
Yes, it is possible to force a query to use a specific index by using index hints.

Example:
SELECT FristName, LastName
FROM Employees WITH(INDEX (indx_firstname, indx_lastname))

Question:
What is the difference between clustered and non-clustered INDEX?

Answer:
Clustered INDEX is unique for any given table and the leaf level of a clustered index is the actual data, and the data is re-sorted in case of clustered index. We can have only one clustered index per table.

In case of non-clustered INDEX the leaf level is actually a pointer to the data in rows so we can have as many non-clustered indexes as we can on the database.

Consider the Telephone Directory as an example of Clustered Index, in which data and index are at the same page, whereas index in the backside of the book is a good example of non-clustered index.

Only one clustered index is possible per table and non-clustered index may be more than one per table (There can be only 249 non-clustered indexes per table possible in SQL Server 2005 and 999 Non Clustered Indexes per table in SQL Server 2008).