SQL interview Questions and Answers III

What are the advantage of User Defined function over store procedure

There are no of benefits of SQL Server User-Defined functions. Some of these are here we can use these functions in so many different places with comparison to SQL Server stored procedure. Two of user define function acts like a table (Inline and Multi-statement functions) helps developers to reduce the code and break complex logic in short code blocks. On the other hand Scalar User-Defined Function have ability so that we use this function anywhere where we need some single value result or some of operation. Combining these advantages with the ability to pass parameters into these database objects makes the SQL Server User-Defined function a very powerful tool.

What are the advantages of using Stored Procedures? Stored procedure can reduced network traffic and latency, boosting application performance. Stored procedure execution plans can be reused, staying cached in SQL Server's memory, reducing server overhead. Stored procedures help promote code reuse. Stored procedures can encapsulate logic. You can change stored procedure code without affecting clients. Stored procedures provide better security to your data.

What is a table called, if it has neither Cluster nor Non‐cluster Index? What is it used for? Un-indexed table or Heap. Microsoft Press Books and Book on Line (BOL) refers it as Heap. A heap is a table that does not have a clustered index and, therefore, the pages are not linked by pointers. The IAM pages are the only structures that link the pages in a table together. Un-indexed tables are good for fast storing of data. Many times it is better to drop all indexes from table and then do bulk of inserts and to restore those indexes after that.

What command do we use to rename a db, a table and a column?
To rename db
sp_renamedb ‘oldname’ , ‘newname’
If someone is using db it will not accept sp_renmaedb. In that case first bring db to single user using sp_dboptions. Use sp_renamedb to rename database. Use sp_dboptions to bring database to multi user mode.
E.g.
USE master;
GO
EXEC sp_dboption AdventureWorks, 'Single User', True
GO
EXEC sp_renamedb 'AdventureWorks', 'AdventureWorks_New'
GO
EXEC sp_dboption AdventureWorks, 'Single User', False
GO
To rename Table
We can change the table name using sp_rename as follows,
sp_rename ‘oldTableName’ ‘newTableName’
E.g.
SP_RENAME ‘Table_First’, ‘Table_Last’ GO
To rename Column
The script for renaming any column :
sp_rename ‘TableName.[OldcolumnName]’, ‘NewColumnName’, ‘Column’
E.g.
sp_RENAME ‘Table_First.Name’, ‘NameChange’ , ‘COLUMN’ GO

Define basic functions for master and msdb and tempdb databases in SQL Server
(1)master:-It contains system level information for a SQL Server system and also contains login accounts and all system configuration settings. master is the database that records the existence of all other databases, including the location of the database files.
(2) tempdb - This database holds all temporary tables and temporary stored procedures. It also fills any other temporary storage needs such as work tables generated by SQ
L Server. tempdb is re-created every time SQL Server is started so the system starts with a clean copy of the database.
(3)mode - The model database is used as the template for all databases created on a system. When a CREATE DATABASE statement is issued, the first part of the database is created by copying in the contents of the model database, then the remainder of the new database is filled with empty pages. Because tempdb is created every time SQL Server is started, the model database must always exist on a SQL Server system.
(4)msdb - The msdb database is used by SQL Server Agent for scheduling alerts and jobs, and recording operators.

What is Cursor: Cursor is a database object used by applications to manipulate data in a set on a row-by-row basis, instead of the typical SQL commands that operate on all the rows in the set at one time. For example, you can use cursor to include a list of all user databases and make multiple operations against each database by passing each database name as a variable.

What is Collate in SQL SERVER2000: The COLLATE clause can be applied only for the char, varchar, text, nchar, nvarchar, and ntext data types. The physical storage of character strings in Microsoft� SQL Server� 2000 is controlled by collations. A collation specifies the bit patterns to represent each character and the rules by which characters are sorted and compared with another character.

Exception Handling in SQL Server 2000: Basically, in Microsoft SQL Server 2000, there exists no structured exception handling. We need to depend on @@ERROR for any errors that occur. No TRY CATCH Block is available in 2000.

create procedure dbo.sp_emp_insert
(
@empno int,
@ename varchar(20),
@sal float,
@deptno int
)
as
begin

declare @Error int

begin transaction
insert into emp (empno,ename,sal,deptno) values (empno,@ename,@sal,@deptno)

set @Error = @@ERROR
if @Error <> 0 --if error is raised
begin
goto LogError
end
commit transaction
goto ProcEnd

LogError:
rollback transaction

declare @ErrMsg varchar(1000)
select @ErrMsg = [description] from master.dbo.sysmessages
where error = @Error
insert into error_log (LogDate,Source,ErrMsg)
values (getdate(),'sp_emp_insert',@ErrMsg)

ProcEnd:
end

GO

To execute the above program, you need to issue the following statement in query analyzer:

exec sp_emp_insert 1003,'ccc',4000,30

Exception Handling in SQL Server 2005
DropProcedure dbo.sp_emp_insert
go

createprocedure [dbo].[sp_emp_insert]
(
@empno int,
@ename varchar(20),
@sal float,
@deptno int
)
as
begin

begintry

begin transaction
insert into emp (empno,ename,sal,deptno)
values (@empno,@ename,@sal,@deptno)
commit transaction

endtry
begincatch
rollback transaction
insert into error_log (LogDate,Source,ErrMsg)
values (getdate(),'sp_emp_insert',error_message())

endcatch

end

Execute:
exec sp_emp_insert 1003,'ccc',4000,30

What is a Linked Server? Linked Servers is a concept in SQL Server by which we can add other SQL Server to a Group and query both the SQL Server dbs using T‐SQL Statements. With a linked server, you can create very clean, easy to follow, SQL statements that allow remote data to be retrieved, joined and combined with local data. Stored Procedure sp_addlinkedserver, sp_addlinkedsrvlogin will be used add new Linked Server.

What is Collation? Collation refers to a set of rules that determine how data is sorted and compared. Character data is sorted using rules that define the correct character sequence, with options for specifying case sensitivity, accent marks, kana character types and character width.

What is User Defined Functions? What kind of User-Defined Functions can be created?
User‐Defined Functions allow defining its own T‐SQL functions that can accept 0 or more parameters and return a single scalar data value or a table data type.

Different Kinds of User‐Defined Functions created are:
• Scalar User‐Defined Function
• A Scalar user‐defined function returns one of the scalar data types. Text, ntext, image and timestamp data types are not supported.

These are the type of user‐defined functions that most developers are used to in other programming languages. You pass in 0 to many parameters and you get a return value.

What is Difference between Function and Stored Procedure? 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 row-set. This can be used in JOINs with other tables. Inline UDF's can be thought of as views that take parameters and can be used in JOINs and other Row-set operations.

What is sub-query? Explain properties of sub-query? Sub‐queries are often referred to as sub‐selects, as they allow a SELECT statement to be executed arbitrarily within the body of another SQL statement. A sub‐query is executed by enclosing it in a set of parentheses. Sub‐queries are generally used to return a single row as an atomic value, though they may be used to compare values against multiple rows with the IN keyword.

A sub-query is a SELECT statement that is nested within another T‐SQL statement. A sub-query SELECT statement if executed independently of the T‐SQL statement, in which it is nested, will return a result-set. Meaning a sub-query SELECT statement can standalone and is not depended on the statement in which it is nested. A sub-query SELECT statement can return any number of values, and can be found in, the column list of a SELECT statement, a FROM, GROUP BY, HAVING, and/or ORDER BY clauses of a T‐SQL statement. A Sub-query can also be used as a parameter to a function call. Basically a sub-query can be used anywhere an expression can be used.

What are the properties and different Types of Sub-Queries?
Properties of Sub‐Query
• A sub‐query must be enclosed in the parenthesis.
• A sub‐query must be put in the right hand of the comparison operator, and
• A sub‐query cannot contain an ORDER‐BY clause.
• A query can contain more than one sub‐query.
Types of Sub‐query
• Single‐row sub‐query, where the sub‐query returns only one row.
• Multiple‐row sub‐query, where the sub‐query returns multiple rows,. and
• Multiple column sub‐query, where the sub‐query returns multiple columns

What are primary keys and foreign keys?

Primary keys are the unique identifiers for each row. They must contain unique values and cannot be null. Due to their importance in relational databases, Primary keys are the most fundamental of all keys and constraints. A table can have only one Primary key.
Foreign keys are both a method of ensuring data integrity and a manifestation of the relationship between tables.

Candidate key: If any unique data column(s) in table is called candidate key. Maybe one or more unique data column
(s).We can select any one unique (candidate key column) as a primary key.

Alternate key: If one table contains more than one candidate keys, remaining candidate keys column(s) which is not selected as a primary key is called Alternate Key.

Composite key: One primary key Contains more than one columns is called Composite key.

What is UNIQUE KEY constraint?

A UNIQUE constraint enforces the uniqueness of the values in a set of columns, so no duplicate values are entered. The unique key constraints are used to enforce entity integrity as the primary key constraints.

How to get @@ERROR and @@ROWCOUNT at the same time?
If @@Rowcount is checked after Error checking statement then it will have 0 as the value of @@Recordcount as it would have been reset. And if @@Recordcount is checked before the error‐checking statement then @@Error would get reset. To get @@error and @@rowcount at the same time do both in same statement and store them in local variable. SELECT @RC = @@ROWCOUNT, @ER = @@ERROR
Inline Table-Value User-Defined Function
An Inline Table‐Value user‐defined function returns a table data type and is an exceptional alternative to a view as the user‐defined function can pass parameters into a T‐SQL select command and in essence provide us with a parameterized, non‐updateable view of the underlying tables.

Multi-statement Table-Value User-Defined Function

A Multi‐Statement Table‐Value user‐defined function returns a table and is also an exceptional alternative to a view as the function can support multiple T‐SQL statements to build the final result where the view is limited to a single SELECT statement. Also, the ability to pass parameters into a TSQL select command or a group of them gives us the capability to in essence create a parameterized, non‐updateable view of the data in the underlying tables. Within the create function command you must define the table structure that is being returned. After creating this type of user‐defined function, It can be used in the FROM clause of a T‐SQL command unlike the behavior found when using a stored procedure which can also return record sets.

What is Identity? Identity (or AutoNumber) is a column that automatically generates numeric values. A start and increment value can be set, but most DBA leave these at 1. A GUID column also generates numbers; the value of this cannot be controlled. Identity/GUID columns do not need to be indexed.

What is Data-Warehousing? Subject‐oriented, meaning that the data in the database is organized so that all the data elements relating to the same real‐world event or object are linked together;
Time‐variant, meaning that the changes to the data in the database are tracked and recorded so that reports can be produced showing changes over time;
Non‐volatile, meaning that data in the database is never over‐written or deleted, once committed, the data is static, read‐only, but retained for future reporting.
Integrated, meaning that the database contains data from most or all of an organization's operational applications, and that this data is made consistent.

What are the difference between clustered and a non-clustered index?

A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages.

A non clustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf node of a non clustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.

What are the different index configurations a table can have?
A table can have one of the following index configurations:
o indexes
A clustered index
A clustered index and many non-clustered indexes
A non-clustered index
Many non-clustered indexes

What are different types of Collation Sensitivity?
Case sensitivity ‐ A and a, B and b, etc.
Accent sensitivity ‐ a and á, o and ó, etc.
Kana Sensitivity ‐ When Japanese kana characters Hiragana and Katakana are treated differently, it is called Kana sensitive.
Width sensitivity ‐ A single‐byte character (half‐width) and the same character represented as a double‐byte character (full‐width) are treated differently than it is width sensitive.