SQL Server interview question Part 3

1. Ques: What is Filtered Index ?
Answer:
Filtered Index used to index a portion of rows in a table.
It is new feature in SQL SERVER 2008.
it applies filter on INDEX which improves query performance, reduce index maintenance costs, and reduce index storage costs as compared with full-table indexes.
It created only as Nonclustered Index.
It cannot be created on full-text Indexes.


2. Ques: What are the new Features of SQL Server 2012 ?
Answer:
Column Store Indexes
Business Intelligence
Sequence Objects
Windows Server Core Support
Pagination
Error Handling
New T-SQL Functions
Enhanced Execute Keyword with WITH RESULT SETS
User-Defined Server Roles
Metadata Discovery Enhancements

3. Ques: What is Sequence object in SQL Server ?
Answer:

• Sequence is a user-defined object that creates a sequence of a number(in ascending or descending order) .
• It is new Feature of SQL Server 2012 is sequence object.
• Unlike identity columns, a sequence object  is created independent of any table.
• It is act like Identity column but not a table specific.it sequence is independent of any table.

syntax to create Sequence object is as

CREATE SEQUENCE Sequence_Name  AS INT
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 10000
CACHE 20 CYCLE;

START WITH: Starting number in the sequence.
INCREMENT BY: The incrementing value of the sequence
MINVALUE: The minimum value the sequence can produce.
MAXVALUE: The maximum value the sequence can produce.
CYCLE: If the MAXVALUE is set with the CYCLE, when the MAXVALUE is reached the sequence will cycle to the MINVALUE and start again.


4. Ques: What are new functions introduce in SQL Server 2012
Answer:

Conversion functions

• PARSE (Transact-SQL)
• TRY_PARSE (Transact-SQL)
• TRY_CONVERT (Transact-SQL)

Date and time functions

• DATEFROMPARTS (Transact-SQL)
• DATETIMEFROMPARTS (Transact-SQL)
• DATETIME2FROMPARTS (Transact-SQL)
• SMALLDATETIMEFROMPARTS (Transact-SQL)
• DATETIMEOFFSETFROMPARTS (Transact-SQL)
• TIMEFROMPARTS (Transact-SQL)
• EOMONTH (Transact-SQL)

Logical functions

• CHOOSE (Transact-SQL)
• IIF (Transact-SQL)

String functions

• CONCAT (Transact-SQL)
• FORMAT (Transact-SQL)

Analytic Functions

• First_Value Function
• Last_Value Function

5. Ques: What is a Cursor?
Answer:
A cursor is a database object used to manipulate data in a set on a row-by-row basis.
A cursor perform steps in the following order:
Declare cursor
Open cursor
Fetch row from the cursor
Process fetched row
Close cursor
Deallocate cursor


6. Ques: What are Constraints in Sql Server. How many types of Constraints in Sql Server.
Answer:
Constraints are some rules that enforce on the data to be enter into the database table

Primary Constraint:It is uniquely identifies each row in a table.
Foreign Constraint: It is a field in a table that is a Primary key in another table.
Check Constraint: It checks for a specific condition before inserting data into a table.
Default Constraint:It Specifies a default value when a value is not specified for this column
Unique Constraint :It ensures that each row for a column must have a unique value.
Not Null Constraint:A Not null constraint restrict the insertion of null values into a column.

7. Ques: What is sparse columns in sql server ? what are the Advantages and Disadvantages of a sparse column ?
Answer:
Sparse Columns are a new feature introduced in SQL Server 2008.A SPARSE column is an ordinary type of column that has optimized storage for NULL values. It also reduces the space requirements for null values at the cost of more overhead to retrieve non-null values. In other words, a SPARSE column is better at managing NULL and ZERO values in SQL Server.

ex:
CREATE TABLE tblStore
(
DoucumentID int PRIMARY KEY,
title varchar(200) NOT NULL,
production_specification varchar(20) SPARSE NULL,
production_location smallint SPARSE NULL,
marketing_group varchar(20) SPARSE NULL);
GO

Advantages of a SPARSE column

• A SPARSE column saves database space when there is zero or null values in the database.
• INSERT, UPDATE, and DELETE statements can reference the SPARSE columns by name.
• We can get more benefit of Filtered indexes on a SPARSE column.
• We can use SPARSE columns with change tracking and change data capture.

Disadvantages of a SPARSE column

• A SPARSE column must be nullable and cannot have the ROWGUIDCOL or IDENTITY properties.
• A SPARSE column cannot be data types like text, ntext, image, timestamp, user-defined data type, geometry, or geography.
• It cannot have a default value and bounded-to rule.
• A SPARSE column cannot be part of a clustered index or a unique primary key index and partition key of a clustered index or heap.
• Merge replication does not support SPARSE columns.
• The SPARSE property of a column is not preserved when the table is copied.

8. Ques: What is Temporary Tables in sql Server? How many types of Temporary Tables ?
Answer:
Temporary Tables are created in the tempdb and are automatically deleted when they are no longer in use.

There are two type of Temporary Tables.
Local temp table: Local temporary tables are only available to the current connection to the database for the current user and are dropped when the connection is closed. they are automatically deleted when the user disconnects from instances. Local temporary table name is stared with hash ("#") sign.

Global temp table: Global temporary tables are available to any connection once created, and are dropped when the last connection using it is closed. Global Temporary tables name starts with a double hash ("##"). Once this table has been created by a connection, like a permanent table it is then available to any user by any connection. It can only be deleted once all connections have been closed.