SQL Server Theoretical Interview Question

Theoretical Question:

1. Is CDC asynchronous?

Ans: Yes

2. Is it possible to use having function without group by? Give example

Ans: Yes
Query:

declare @input as table

(
       column_name varchar(100)
)

insert into @input values('AAA')
insert into @input values('BBB')
insert into @input values('CCC')

select
       max(column_name)
from
       @input
having
       max(column_name) >= 'AAA'

Output:

3. What are the constraints in SQL Server? Give me the name of it.
Ans: Constraints let you define the way the Database Engine automatically enforces the integrity of a database. Constraints define rules regarding the values allowed in columns and are the standard mechanism for enforcing integrity. The query optimizer also uses constraint definitions to build high-performance query execution plans.
a)   NOT NULL
b)   CHECK
c)   UNIQUE
d)   PRIMARY KEY
   a.   FOREIGN KEY
   b.   NO ACTION
   c.    CASCADE
   d.   SET NULL
   e.   SET DEFAULT

4. What’s difference between Function and Stored Procedure?
Ans:
·  Procedure can return zero or n values whereas function can return one value which is mandatory.
·  Procedures can have input/output parameters for it whereas functions can have only input parameters.
·  Procedure allows select as well as DML statement in it whereas function allows only select statement in it.
·  Functions can be called from procedure whereas procedures cannot be called from function.
·  Exception can be handled by try-catch block in a procedure whereas try-catch block cannot be used in a function.
·  We can go for transaction management in procedure whereas we can't go in function.
·  Procedures cannot be utilized in a select statement whereas function can be embedded in a select statement.
·  UDF can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section where as Stored procedures cannot be.
·  UDFs that return tables can be treated as another rowset. This can be used in JOINs with other tables.
·  Inline UDF's can be though of as views that take parameters and can be used in JOINs and other Rowset operations.

5. Types of parameters in Procedure
Ans:
·  Input and
·  Output.

6. What’s difference between Primary Key and Foreign Key?
Ans: A primary key is a column which uniquely identifies the records in a table. In a broad sense, a primary key is the mixture of a unique key and an index: A collumn with a primary key is indexed to deliver a faster query, and doesn't allow duplicate values to ensure specific data.

A foreign key is a column (the child column) in a table which has a corresponding relationship and a dependency on another column (the parent collumn) that is usually in a different table. Parent columns can have multiple child columns, but a child collumn can only have one parent column.

7. What are the rank functions in SQL Server?
Ans: Ranking functions return a ranking value for each row in a partition. Depending on the function that is used, some rows might receive the same value as other rows. Ranking functions are nondeterministic.
Transact-SQL provides the following ranking functions:
·  RANK
·  DENSE_RANK
·  NTILE
·  ROW_NUMBER

8. What is CTE and use of it?
Ans: A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.

A CTE can be used to:
·  Create a recursive query. For more information, see Recursive Queries Using Common Table Expressions.
·  Substitute for a view when the general use of a view is not required; that is, you do not have to store the definition in metadata.
·  Enable grouping by a column that is derived from a scalar subselect, or a function that is either not deterministic or has external access.
·  Reference the resulting table multiple times in the same statement.

9. What are the types of trigger and syntax for them?
Ans: A trigger is a special kind of stored procedure that automatically executes when an event occurs in the database server.
·  DML triggers execute when a user tries to modify data through a data manipulation language (DML) event. DML events are INSERT, UPDATE, or DELETE statements on a table or view. These triggers fire when any valid event is fired, regardless of whether or not any table rows are affected.
·  DDL triggers execute in response to a variety of data definition language (DDL) events. These events primarily correspond to Transact-SQL CREATE, ALTER, and DROP statements, and certain system stored procedures that perform DDL-like operations.
·  Logon triggers fire in response to the LOGON event that is raised when a user sessions is being established.

10. What is difference between declare variable and temporary table. Which is best and give scenario for that?
Ans:
·  Temporary Tables are real tables so you can do things like CREATE INDEXes, etc. If you have large amounts of data for which accessing by index will be faster then temporary tables are a good option.
·  Table variables can have indexes by using PRIMARY KEY or UNIQUE constraints. (If you want a non-unique index just includes the primary key column as the last column in the unique constraint. If you don't have a unique column, you can use an identity column.) SQL 2014 has non-unique indexes too.
·  Table variables don't participate in transactions, logging or locking. This means they're faster as they don't require the overhead, but conversely you don't get those features. So for instance if you want to ROLLBACK midway through a procedure then table variables populated during that transaction will still be populated!
·  Temp tables might result in stored procedures being recompiled, perhaps often. Table variables will not.
·  You can create a temp table using SELECT INTO, which can be quicker to write (good for ad-hoc querying) and may allow you to deal with changing datatypes over time, since you don't need to define your temp table structure upfront.
·  You can pass table variables back from functions, enabling you to encapsulate and reuse logic much easier (eg make a function to split a string into a table of values on some arbitrary delimiter).
·  Using Table Variables within user-defined functions enables those functions to be used more widely (see CREATE FUNCTION documentation for details). If you're writing a function you should use table variables over temp tables unless there's a compelling need otherwise.
·  Both table variables and temp tables are stored in tempdb. This means you should be aware of issues such as COLLATION problems if your database collation is different to your server collation; temp tables and table variables will by default inherit the collation of the server, causing problems if you want to compare data in them with data in your database.
·  Global Temp Tables (##tmp) are another type of temp table available to all sessions and users.

11. What’s different type of indexes? Which is best of it? Give the scenario for that.
Ans:
Clustered: A clustered index sorts and stores the data rows of the table or view in order based on the clustered index key. The clustered index is implemented as a B-tree index structure that supports fast retrieval of the rows, based on their clustered index key values.

Nonclustered: A nonclustered index can be defined on a table or view with a clustered index or on a heap. Each index row in the nonclustered index contains the nonclustered key value and a row locator. This locator points to the data row in the clustered index or heap having the key value. The rows in the index are stored in the order of the index key values, but the data rows are not guaranteed to be in any particular order unless a clustered index is created on the table.

Unique: A unique index ensures that the index key contains no duplicate values and therefore every row in the table or view is in some way unique.

Both clustered and nonclustered indexes can be unique.

Index with included columns: A nonclustered index that is extended to include nonkey columns in addition to the key columns.

Full-text: A special type of token-based functional index that is built and maintained by the Microsoft Full-Text Engine for SQL Server. It provides efficient support for sophisticated word searches in character string data.

Spatial: A spatial index provides the ability to perform certain operations more efficiently on spatial objects (spatial data) in a column of the geometry data type. The spatial index reduces the number of objects on which relatively costly spatial operations need to be applied.

Filtered: An optimized nonclustered index, especially suited to cover queries that select from a well-defined subset of data. It uses a filter predicate to index a portion of rows in the table. A well-designed filtered index can improve query performance, reduce index maintenance costs, and reduce index storage costs compared with full-table indexes.

XML: A shredded, and persisted, representation of the XML binary large objects (BLOBs) in the xml data type column.

12. What’s dynamic SQL query and how to execute it?
Ans: Dynamic SQL is query which is build up on fly. sp_executesql executes a Transact-SQL statement or batch that can be reused many times, or one that has been built dynamically. The Transact-SQL statement or batch can contain embedded parameters.

13. What’s index seek and index scan?
Ans: Index Scan is nothing but scanning on the data pages from the first page to the last page. If there is an index on a table, and if the query is touching a larger amount of data, which means the query is retrieving more than 50 percent or 90 percent of the data, and then optimizer would just scan all the data pages to retrieve the data rows. If there is no index, then you might see a Table Scan (Index Scan) in the execution plan.

Index seeks are generally preferred for the highly selective queries. What that means is that the query is just requesting a fewer number of rows or just retrieving the other 10 (some documents says 15 percent) of the rows of the table.

14. How to generate comma separated values for columns for a given table.
Ans: There are 2 ways
·  COALESCE
·  FOR XML PATH

15. There is a table employee which have one record for employee. I want to repeat same record 5 times. How to achieve that?
Ans: We can do this using CROSS APPLY.