SQL interview Questions and Answers IV

What is difference between DELETE & TRUNCATE commands?

Delete command removes the rows from a table based on the condition that we provide with a WHERE clause. Truncate will actually remove all the rows from a table and there will be no data in the table after we run the truncate command.

TRUNCATE

• TRUNCATE is faster and uses fewer system and transaction log resources than DELETE.

• TRUNCATE removes the data by de-allocating the data pages used to store the table’s data, and only the page de-allocations are recorded in the transaction log.

• TRUNCATE removes all rows from a table, but the table structure, its columns, constraints, indexes and so on, remains. The counter used by an identity for new rows is reset to the seed for the column.

• You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint. Because TRUNCATE TABLE is not logged, it cannot activate a trigger.

• TRUNCATE cannot be rolled back.

• TRUNCATE is DDL Command.

• TRUNCATE Resets identity of the table

DELETE

• DELETE removes rows one at a time and records an entry in the transaction log for each deleted row.

• If you want to retain the identity counter, use DELETE instead. If you want to remove table definition and its data, use the DROP TABLE statement.

• DELETE Can be used with or without a WHERE clause

• DELETE Activates Triggers.

• DELETE can be rolled back.

• DELETE is DML Command.

• DELETE does not reset identity of the table.

Can a stored procedure call itself or recursive stored procedure? How much level SP nesting is possible?

Yes. Because Transact‐SQL supports recursion, you can write stored procedures that call themselves. Recursion can be defined as a method of problem solving wherein the solution is arrived at by repetitively applying it to subsets of the problem. A common application of recursive logic is to perform numeric computations that lend themselves to repetitive evaluation by the same processing steps. Stored procedures are nested when one stored procedure calls another or executes managed code by referencing a CLR routine, type, or aggregate. You can nest stored procedures and managed code references up to 32 levels.

Name 3 ways to get an accurate count of the number of records in a table?

• SELECT * FROM table1

• SELECT COUNT(*) FROM table1

• SELECT rows FROM sysindexes WHERE id = OBJECT_ID(table1) AND indid Difference between stored procedure and function?

1>Procedure can return zero or n values whereas function can return one value which is mandatory.

2>Procedures can have input, output parameters for it whereas functions can have only input parameters.

3>Procedure allow select as well as DML statement in it whereas function allow only select statement in it.

4>Functions can be called from procedure whereas procedures cannot be called from function.

5>Exception can be handled by try-catch block in a procedure whereas try-catch block cannot be used in a function.

6>we can go for transaction management in procedure whereas we can’t go in function.

7>Procedures cannot be utilized in a select statement whereas function can be embedded in a select statement.

8>Error handling is restricted. RAISERROR and @@ERROR are invalid from inside User Defined Functions.

CREATE FUNCTION dbo.Function1



    (



    /*



    @parameter1 datatype = default value,



    @parameter2 datatype



    */



    )



 RETURNS /* datatype */



 AS



    BEGIN



     /* sql statement ... */



    RETURN /* value */



    END

Aggregate Functions

SQL aggregate functions return a single value, calculated from values in a column.

• AVG() – Returns the average value

• COUNT() – Returns the number of rows

• FIRST() – Returns the first value

• LAST() – Returns the last value

• MAX() – Returns the largest value

• MIN() – Returns the smallest value

• SUM() – Returns the sum

Table : “Orders”

Table : “Orders”

1. Average value of the “OrderPrice” fields.

SELECT AVG(OrderPrice) AS OrderAverage FROM Orders

2. The COUNT(column_name) function returns the number of values (NULL values will not be counted) of the specified column:

SELECT COUNT(Customer) AS CustomerNilsen FROM Orders WHERE Customer=’Nilsen’

3. The FIRST() function returns the first value of the selected column

SELECT FIRST(OrderPrice) AS FirstOrderPrice FROM Orders

Workaround if FIRST() function is not supported:

SELECT OrderPrice FROM Orders ORDER BY O_Id LIMIT 1

The LAST() function returns the last value of the selected column.

SELECT LAST(OrderPrice) AS LastOrderPrice FROM Orders

Workaround if LAST() function is not supported:

SELECT OrderPrice FROM Orders ORDER BY O_Id DESC LIMIT 1

5. The MAX() function returns the largest value of the selected column.

SELECT MAX(OrderPrice) AS LargestOrderPrice FROM Orders

6. The MIN() function returns the smallest value of the selected column.

SELECT MIN(OrderPrice) AS SmallestOrderPrice FROM Orders

7. The SUM() function returns the total sum of a numeric column.

SELECT SUM(OrderPrice) AS OrderTotal FROM Orders

GROUP BY Statement

The GROUP BY statement is used in conjunction (Join together) with the aggregate functions to group the result-set by one or more columns. – find the total sum (total order) of each customer.

SELECT Customer,SUM(OrderPrice) FROM Orders GROUP BY Customer

GROUP BY More Than One Column

SELECT Customer,OrderDate,SUM(OrderPrice) FROM Orders GROUP BY customer,OrderDate

HAVING Clause

The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions. – find if any of the customers have a total order of less than 2000.

SELECT Customer,SUM(OrderPrice) FROM Orders GROUP BY Customer HAVING SUM(OrderPrice)<2000

Scalar functions

SQL scalar functions return a single value, based on the input value.

• UCASE() – Converts a field to upper case

• LCASE() – Converts a field to lower case

• MID() – Extract characters from a text field

• LEN() – Returns the length of a text field

• ROUND() – Rounds a numeric field to the number of decimals specified

• NOW() – Returns the current system date and time

• FORMAT() – Formats how a field is to be displayed

ROUND (): SELECT ROUND(UnitPrice,0) as UnitPrice FROM Products

The ROUND () function is used to round a numeric field to the number of decimals specified.

Convert 12.20 to 12.

NOW (): SELECT ProductName, UnitPrice, Now() as PerDate FROM Products

The NOW() function returns the current system date and time.

Result: Carlsberg || 10.45 || 10/7/2008 11:25:02 AM

FORMAT (): used to format how a field is to be displayed.

SELECT ProductName, UnitPrice, FORMAT(Now(),’YYYY-MM-DD’) as PerDate FROM Products

TOP Clause: used to specify the number of records to return.

SELECT TOP 2 * FROM Products

Result: Return first two Rows in the Table

TOP PERCENT: Select only 50% of the records in the table above.

SELECT TOP 50 PERCENT * FROM Persons

Result: Return 50% Rows in the Table. If a table contains 100 rows, it will return 50 rows.

LIKE Operator: The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.

SELECT * FROM Persons WHERE City LIKE ‘s%’ Result: Returns rows where the City Name starts with ‘S’

SELECT * FROM Persons WHERE City LIKE ‘%s’ Result: Returns rows where the City Name ends with ‘S’

SELECT * FROM Persons WHERE City LIKE ‘%s%’ Result: Returns rows where the City Name contains ‘S’

SELECT * FROM Persons WHERE City NOT LIKE ‘%s%’ Result: Returns rows where the City Name Not contains ‘S’

Note: The “%” sign can be used to define wildcards (missing letters in the pattern) both before and after the pattern.

IN Operator: specify multiple values in a WHERE clause.

SELECT * FROM Persons WHERE LastName IN (‘Hansen’,’Pettersen’)

BETWEEN Operator: It selects a range of data between two values. The values can be numbers, text, or dates.

SELECT * FROM Persons WHERE LastName BETWEEN ‘Hansen’ AND ‘Petersen’

SELECT * FROM Persons WHERE Amount BETWEEN 1000 AND 5000

Note: Won’t consider about last parameter passed(Take Record from 1000 up to 4999)

SELECT * FROM Persons WHERE LastName NOT BETWEEN ‘Hansen’ AND ‘Petersen’

AND & OR Operators:

The AND operator displays a record if both the first condition and the second condition is true.

The OR operator displays a record if either the first condition or the second condition is true.

SELECT * FROM Persons WHERE FirstName=’Tove’ AND LastName=’Svendson’

SELECT * FROM Persons WHERE FirstName=’Tove’ OR FirstName=’Ola’

Combining AND & OR

You can also combine AND and OR (use parenthesis to form complex expressions).

Now we want to select only the persons with the last name equal to “Svendson” AND the first name equal to “Tove” OR to “Ola”:

SELECT * FROM Persons WHERE LastName=’Svendson’ AND (FirstName=’Tove’ OR FirstName=’Ola’)

ORDER BY Keyword: The ORDER BY keyword is used to sort the result-set by a specified column. The ORDER BY keyword sort the records in ascending order by default. If you want to sort the records in a descending order, you can use the DESC keyword.

SELECT * FROM Persons ORDER BY LastName DESC

INSERT INTO Statement: used to insert a new row in a table.

INSERT INTO Persons VALUES (4,’Nilsen’, ‘Johan’, ‘Bakken 2’, ‘Stavanger’)

UPDATE Statement: used to update existing records in a table.

Note: Notice the WHERE clause in the UPDATE syntax. The WHERE clause specifies which record or records that should be updated. If you omit the WHERE clause, all records will be updated!

UPDATE Persons SET Address=’Nissestien 67′, City=’Sandnes’ WHERE LastName=’Tjessem’ AND FirstName=’Jakob’

DELETE Statement: used to delete rows in a table.

Note: Notice the WHERE clause in the DELETE syntax. The WHERE clause specifies which record or records that should be deleted. If you omit the WHERE clause, all records will be deleted!

DELETE FROM Persons WHERE LastName=’Tjessem’ AND FirstName=’Jakob’

Delete All Rows: It is possible to delete all rows in a table without deleting the table. This means that the table structure, attributes, and indexes will be intact:

Note: Be very careful when deleting records. You cannot undo this statement!

DELETE FROM table_name

JOIN: The JOIN keyword is used in an SQL statement to query data from two or more tables, based on a relationship between certain columns in these tables. Tables in a database are often related to each other with keys.

Different SQL JOINs:

• JOIN: Return rows when there is at least one match in both tables

• LEFT JOIN: Return all rows from the left table, even if there are no matches in the right table

• RIGHT JOIN: Return all rows from the right table, even if there are no matches in the left table

• FULL JOIN: Return rows when there is a match in one of the tables

INNER JOIN: return rows when there is at least one match in both tables.

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons INNER JOIN Orders ON Persons.P_Id=Orders.P_Id ORDER BY Persons.LastName

LEFT JOIN: The LEFT JOIN keyword returns all the rows from the left table (Persons), even if there are no matches in the right table (Orders).

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons LEFT JOIN Orders

ON Persons.P_Id=Orders.P_Id ORDER BY Persons.LastName

RIGHT JOIN: returns all the rows from the right table (Orders), even if there are no matches in the left table (Persons).

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons RIGHT JOIN Orders ON Persons.P_Id=Orders.P_Id ORDER BY Persons.LastName

FULL JOIN: list all the persons and their orders, and all the orders with their persons.

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons FULL JOIN Orders ON Persons.P_Id=Orders.P_Id ORDER BY Persons.LastName

UNION: The UNION operator is used to combine the result-set of two or more SELECT statements.

Notice that each SELECT statement within the UNION must have the same number of columns. The columns must also have similar data types. Also, the columns in each SELECT statement must be in the same order.

SELECT E_Name FROM Employees_Norway UNION SELECT E_Name FROM Employees_USA

The UNION operator selects only distinct values by default. To allow duplicate values, use UNION ALL.

SELECT E_Name FROM Employees_Norway UNION ALL SELECT E_Name FROM Employees_USA

SELECT INTO: The SELECT INTO statement selects data from one table and inserts it into a different table. The SELECT INTO statement is most often used to create backup copies of tables.

SELECT * INTO Persons_Backup FROM Persons

Note: We can also use the IN clause to copy the table into another database:

SELECT * INTO Persons_Backup IN ‘Backup.mdb’ FROM Persons

Note: We can also copy only a few fields into the new table:

SELECT LastName, FirstName INTO Persons_Backup FROM Persons

SELECT INTO – Joined Tables: Selecting data from more than one table is also possible.

The following example creates a “Persons_Order_Backup” table contains data from the two tables “Persons” and “Orders”:

SELECT Persons. LastName, Orders. OrderNo INTO Persons_Order_Backup FROM Persons INNER JOIN Orders ON Persons.P_Id=Orders.P_Id

DROP INDEX, DROP TABLE, and DROP DATABASE

The DROP INDEX statement is used to delete an index in a table:

DROP INDEX table_name.index_name

The DROP TABLE statement is used to delete a table.

DROP TABLE table_name

The DROP DATABASE statement is used to delete a database:

DROP DATABASE database_name

TRUNCATE TABLE: only want to delete the data inside the table, and not the table itself?

TRUNCATE TABLE table_name

ALTER TABLE: is used to add, delete, or modify columns in an existing table.

• ALTER TABLE Persons ADD DateOfBirth date

• ALTER TABLE Persons ALTER COLUMN DateOfBirth year

• ALTER TABLE Persons DROP COLUMN DateOfBirth

Backup And Restore:

BACKUP DATABASE [AdventureWorks] TO DISK=N’\\nas\Backup\L40\SQL2005\AdventureWorks_backup_200702120215.bak’

— Full File Backup

BACKUP DATABASE AdventureWorks

FILE=’AdventureWorks_Data’

TO DISK = N’C:\Backup\AdventureWorks.bak’

— Differential File Backup

BACKUP DATABASE AdventureWorks

FILE=’AdventureWorks_Data’

TO DISK = N’C:\Backup\AdventureWorks.bak’

WITH DIFFERENTIAL

RESTORE FILELISTONLY FROM DISK = ‘D:BackUpYourBaackUpFile.bak’

Or

RESTORE DATABASE YourDB

FROM DISK = ‘D:BackUpYourBaackUpFile.bak’

WITH MOVE ‘YourMDFLogicalName’ TO ‘D:DataYourMDFFile.mdf’,

MOVE ‘YourLDFLogicalName’ TO ‘D:DataYourLDFFile.mdf’

/*If there is no error in statement before database will be in multiuser

mode.If error occurs please execute following command it will convert

database in multi user.*/

ALTER DATABASE YourDB SET MULTI_USER

GO

SQL Server comes with the following data types for storing a date or a date/time value in the database:

• DATE – format YYYY-MM-DD

• DATETIME – format: YYYY-MM-DD HH:MM:SS

• SMALLDATETIME – format: YYYY-MM-DD HH:MM:SS

• TIMESTAMP – format: a unique number

Note: The date types are chosen for a column when you create a new table in your database!

SQL NULL Values

If a column in a table is optional, we can insert a new record or update an existing record without adding a value to this column. This means that the field will be saved with a NULL value.

NULL values are treated differently from other values.

NULL is used as a placeholder for unknown or inapplicable values.

Note: It is not possible to compare NULL and 0; they are not equivalent.

SELECT LastName,FirstName,Address FROM Persons WHERE Address IS NULL

SELECT LastName,FirstName,Address FROM Persons WHERE Address IS NOT NULL

What is BIT datatype and what’s the information that can be stored inside a bit column? Bit datatype is used to store Boolean information like 1 or 0 (true or false). Until SQL Server 6.5 bit datatype could hold either a 1 or 0 and there was no support for NULL. But from SQL Server 7.0 onwards, bit datatype can represent a third state, which is NULL

What is De-normalization and when would you go for it? As the name indicates, de-normalization is the reverse process of normalization. It’s the controlled introduction of redundancy in to the database design. It helps improve the query performance as the number of joins could be reduced.

Define Candidate key, Alternate key, and composite key?

A candidate key is one that can identify each row of a table uniquely. Generally 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.

A key formed by combining at least two or more columns is called composite key.

Explain different isolation levels? An isolation level determines the degree of isolation of data between concurrent transactions. The default SQL Server isolation level is Read Committed. Here are the other isolation levels (in the ascending order of isolation): Read Uncommitted, Read Committed, and Repeatable Read, serializable. See SQL Server books online for an explanation of the isolation levels. Be sure to read about SET TRANSACTION ISOLATION LEVEL, which lets you customize the isolation level at the connection level.

What’s the maximum size of a row? 8060 bytes. Don’t be surprised with questions like ‘what is the maximum number of columns per table’. Check out SQL Server books online for the page titled: “Maximum Capacity Specifications”.

What’s the maximum number of columns you can create in a table?

• 1024 for fixed-length columns in both all-pages-locked (APL) and data-only- locked (DOL) tables

• 254 for variable-length columns in an APL table

• 1024 for variable-length columns in an DOL table

The maximum size of a column depends on:

• Whether the table includes any variable- or fixed-length columns.

• The logical page size of the database. For example, in a database with 2K logical pages, the maximum size of a column in an APL table can be as large as a single row, about 1962 bytes, less the row format overheads. Similarly, for a 4K page, the maximum size of a column in a APL table can be as large as 4010 bytes, less the row format overheads. See Table 0-1 for more information.

• If you attempt to create a table with a fixed-length column that is greater than the limits of the logical page size, create table issues an error message.

How do you implement one-to-one, one-to-many and many-to-many relationships while designing tables?

One-to-One relationship can be implemented as a single table and rarely as two tables with primary and foreign key relationships.

One-to-Many relationships are implemented by splitting the data into two tables with primary key and foreign key relationships.

Many-to-Many relationships are implemented using a junction table with the keys from both the tables forming the composite primary key of the junction table.

What are constraints? Explain different types of constraints? Constraints enable the RDBMS enforce the integrity of the database automatically, without needing you to create triggers, rule or defaults. Types of constraints: NOT NULL, CHECK, UNIQUE, PRIMARY KEY, FOREIGN KEY

What is an index? What are the types of indexes? How many clustered indexes can be created on a table? I create a separate index on each column of a table. What are the advantages and disadvantages of this approach? Indexes in SQL Server are similar to the indexes in books. They help SQL Server retrieve the data quicker. Indexes are of two type- Clustered indexes and non-clustered indexes. When you create a clustered index on a table, all the rows in the table are stored in the order of the clustered index key. So, there can be only one clustered index per table. Non-clustered indexes have their own storage separate from the table data storage. Non-clustered indexes are stored as B-tree structures (so do clustered indexes), with the leaf level nodes having the index key and its row locater. The row located could be the RID or the Clustered index key, depending up on the absence or presence of clustered index on the table.

If you create an index on each column of a table, it improves the query performance, as the query optimizer can choose from all the existing indexes to come up with an efficient execution plan. At the same time, data modification operations (such as INSERT, UPDATE, DELETE) will become slow, as every time data changes in the table, all the indexes need to be updated. Another disadvantage is that, indexes need disk space, the more indexes you have, more disk space is used.

What is a deadlock and what is a live lock? How will you go about resolving deadlocks? Deadlock is a situation when two processes, each having a lock on one piece of data, attempt to acquire a lock on the other’s piece. Each process would wait indefinitely for the other to release the lock, unless one of the user processes is terminated. SQL Server detects deadlocks and terminates one user’s process.

A livelock is one, where a request for an exclusive lock is repeatedly denied because a series of overlapping shared locks keeps interfering. SQL Server detects the situation after four denials and refuses further shared locks. A livelock also occurs when read transactions monopolize a table or page, forcing a write transaction to wait indefinitely.

Check out SET DEADLOCK_PRIORITY and “Minimizing Deadlocks” in SQL Server books online. Also check out the article Q169960 from Microsoft knowledge base.

What is database replication? What are the different types of replication you can set up in SQL Server? Replication is the process of copying/moving data between databases on the same or different servers. SQL Server supports the following types of replication scenarios:

• Snapshot replication

• Transactional replication (with immediate updating subscribers, with queued updating subscribers)

• Merge replication

See SQL Server books online for in-depth coverage on replication. Be prepared to explain how different replication agents function, what are the main system tables used in replication etc.

What is the wildcard character in SQL? Let’s say you want to query database with LIKE for all employees whose name starts with La. The wildcard character is %, the proper query with LIKE would involve ‘La%’.

What connections does Microsoft SQL Server support? Windows Authentication (via Active Directory) and SQL Server authentication (via Microsoft SQL Server username and passwords).

Which one is trusted and which one is un-trusted? Windows Authentication is trusted because the username and password are checked with the Active Directory; the SQL Server authentication is un-trusted, since SQL Server is the only verifier participating in the transaction.

Nth Maximum: Table: employee

1 harry 3500.0000

2 jack 2500.0000

3 john 2500.0000

4 xavier 5500.0000

5 steven 7500.0000

6 susana 2400.0000

• Select * From Employee E1 Where (N-1) = (Select Count (Distinct (E2.Salary)) From Employee E2 Where E2.Salary > E1.Salary)

• select * from Employee a where N=(select distinct(b.empid)from Employee b where a.empid=b.empid)

Second Maximum:

Select min(Salary) from (select top 2 Salary from employee order by Salary desc) M

//Here M is alias name

Table Size: sp_spaceused emp

Optional parameters to my stored procedures

CREATE PROCEDURE xxx

@param1 VARCHAR (32) = NULL,

@param2 INT = NULL

AS

BEGIN

SET NOCOUNT ON

SELECT Param1 = COALESCE

(

@param1,

‘@param1 was empty’

)

SELECT Param2 = COALESCE

(

RTRIM(@param2),

‘@param2 was empty’

)

END

EXEC dbo.foo @param1=’bar’, @param2=4

EXEC dbo.foo @param1=’bar’

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.

SQL interview Questions and Answers II

What are the null values in SQL SERVER: Before understand the null values we have some overview about what the value is. Value is the actual data stored in a particular field of particular record. But what is done when there are no values in the field. That value is something like .Nulls present missing information. We can also call null propagation.

Write a Role of SQL Server 2005 in XML Web Services: SQL Server 2005 creates a standard method for getting the database engine using SOAP via HTTP. By this method, we can send SOAP/HTTP requests to SQL Server for executing T-SQL batch statements, stored procedures, extended stored procedures, and scalar-valued user-defined functions may be with or without parameters.

What are the different types of Locks
There are three main types of locks that SQL Server
• Shared locks are used for operations that do not allow changing or updating data, such as a SELECT statement.
• Update locks are used when SQL Server intends to modify a page, and later promotes the update page lock to an exclusive page lock before actually making the changes.
• Exclusive locks are used for the data modification operations, such as UPDATE, INSERT, or DELETE.

What is Write ahead log in SQL Server 2000: Before understanding it we must have an idea about the transaction log files. These files are the files which hold the data for change in database. Now we explain when we are doing some SQL Server 2000 query or any SQL query like SQL insert query, delete SQL query, update SQL query and change the data in SQL server database it cannot change the database directly to table .SQL server extracts the data that is modified by SQL server 2000 query or by SQL query and places it in memory. Once data is stores in memory user can make changes to that a log file is garneted this log file is garneted in every five minutes of transaction is done. After this SQL server writes changes to database with the help of transaction log files. This is called Write-ahead log.

What does u mean by Extents and types of Extents: An Extent is a collection of 8 sequential pages to hold database from becoming fragmented. Fragment means these pages relates to same table of database these also holds in indexing. To avoid for fragmentation SQL Server assign space to table in extents. So that the SQL Server keep up to date data in extents. Because these pages are continuously one after another. There are usually two types of extends:-Uniform and Mixed. Uniform means when extent its own by a single object means all collection of 8 ages hold by a single extends is called uniform. Mixed mean when more than one object is comes in extents is known as mixed extents.

What is different in Rules and Constraints: Rules and Constraints are similar in functionality but there is a An little difference between them. Rules are used for backward compatibility. One the most exclusive difference is that we can bind rules to a datatype whereas constraints are bound only to columns. So we can create our own datatype with the help of Rules and get the input according to that.

What is defaults in SQL Server and types of Defaults: Defaults are used when a field of columns is almost common for all the rows for example in employee table all living in Delhi that value of this field is common for all the row in the table if we set this field as default the value that is not fill by us automatically fills the value in the field it’s also work as intellisense means when user inputting d it will automatically fill the Delhi. There are two types of defaults object and definitions. Object default:-These defaults are applicable on particular columns. These are usually denied at the time of table designing. When u set the object default field in column state this column in automatically field when u left this filed blank. Definition default:-When we bind the datatype with default let we named this as Dotnet .Then every time we create column and named its datatype as Dotnet it will behave the same that we set for Dotnet datatype.

Transaction: Transactions group a set of tasks into a single execution unit. Each transaction begins with a specific task and ends when all the tasks in the group successfully complete. If any of the tasks fails the transaction fails. Therefore a transaction has only two results: success or failure. Incomplete steps result in the failure of the transaction. Users can group two or more Transact-SQL statements into a single transaction using the following statements: * Begin Transaction * Rollback Transaction * Commit Transaction If anything goes wrong with any of the grouped statements all changes need to be aborted. The process of reversing changes is called rollback in SQL Server terminology. If everything is in order with all statements within a single transaction all changes are recorded together in the database.

CREATE PROCEDURE DeleteEmployee ( @EmployeeID int )
AS

BEGIN TRY
BEGIN TRANSACTION — Start the transaction
— Delete the Employee’s phone numbers
DELETE FROM EmployeePhoneNumbers WHERE EmployeeID = @EmployeeID
— Delete the Employee record
DELETE FROM Employees WHERE EmployeeID = @EmployeeID
— If we reach here, success!
COMMIT
END TRY
BEGIN CATCH
— Whoops, there was an error
IF @@TRANCOUNT > 0
ROLLBACK
— Raise an error with the details of the exception
DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int
SELECT @ErrMsg = ERROR_MESSAGE(), @ErrSeverity = ERROR_SEVERITY()
RAISERROR(@ErrMsg, @ErrSeverity, 1)
END CATCH

What is COMMIT and ROLLBACK statement in SQL: Commit statement helps in termination of the current transaction and does all the changes that occur in transaction persistent and this also commits all the changes to the database. COMMIT we can also use in store procedure. ROLLBACK do the same thing just terminate the current transaction but one another thing is that the changes made to database are ROLLBACK to the database.

What is difference between OSQL and Query Analyzer: Both are the same but there is little difference OSQL is command line tool which is execute query and display the result same a query analyzer but query analyzer is graphical and OSQL is a command line tool. OSQL have not ability like query analyzer to analyze queries and show statics on speed of execution and other useful thing about OSQL is that its helps in scheduling.

What are Data Integrity and its categories: Enforcing data integrity ensures the quality of the data in the database. For example, if an employee is entered with an employee_id value of 123, the database should not allow another employee to have an ID with the same value. If you have an employee_rating column intended to have values ranging from 1 to 5, the database should not accept a value of 6. If the table has a dept_id column that stores the department number for the employee, the database should allow only values that are valid for the department numbers in the company.

Two important steps in planning tables are to identify valid values for a column and to decide how to enforce the integrity of the data in the column. Data integrity falls into these categories:
1) Entity integrity 2) Domain integrity 3) Referential integrity 4) User-defined integrity
Entity Integrity: Entity integrity defines a row as a unique entity for a particular table. Entity integrity enforces the integrity of the identifier column(s) or the primary key of a table (through indexes, UNIQUE constraints, PRIMARY KEY constraints, or IDENTITY properties).

Domain Integrity: Domain integrity is the validity of entries for a given column. You can enforce domain integrity by restricting the type (through data types), the format (through CHECK constraints and rules), or the range of possible values (through FOREIGN KEY constraints, CHECK constraints, DEFAULT definitions, NOT NULL definitions, and rules).

Referential Integrity: Referential integrity preserves the defined relationships between tables when records are entered or deleted. In Microsoft� SQL Server� 2000, referential integrity is based on relationships between foreign keys and primary keys or between foreign keys and unique keys (through FOREIGN KEY and CHECK constraints). Referential integrity ensures that key values are consistent across tables. Such consistency requires that there be no references to nonexistent values and that if a key value changes, all references to it change consistently throughout the database. When you enforce referential integrity, SQL Server prevents users from: � Adding records to a related table if there is no associated record in the primary table. � Changing values in a primary table that result in orphaned records in a related table. � Deleting records from a primary table if there are matching related records. For example, with the sales and titles tables in the pubs database, referential integrity is based on the relationship between the foreign key (title_id) in the sales table and the primary key (title_id) in the titles table.

User-Defined: Integrity User-defined integrity allows you to define specific business rules that do not fall into one of the other integrity categories. All of the integrity categories support user-defined integrity (all column- and table-level constraints in CREATE TABLE, stored procedures, and triggers).
SQL Server runs on which TCP IP port and from where can you change the default port: SQL Server runs on port 1433 but we can also change it for better security and from the network Utility TCP/IP properties –>Port number, both on client and the server.

What is the difference between a HAVING CLAUSE and a WHERE CLAUSE

Having Clause is basically used only with the GROUP BY function in a query. WHERE Clause is applied to each row before they are part of the GROUP BY function in a query.

When do you use SQL Profiler: SQL Profiler utility allows us to basically track Connections to the SQL Server and also determine activities, such as which SQL Scripts are running, failed jobs etc?
ACID (an acronym for Atomicity Consistency Isolation Durability) is a concept that Database Professionals generally look for when evaluating databases and application architectures. For a reliable database all this four attributes should be achieved.
Atomicity is an all-or-none proposition.
Consistency guarantees that a transaction never leaves your database in a half-finished state.
Isolation keeps transactions separated from each other until they’re finished.
Durability guarantees that the database will keep track of pending changes in such a way that the server can recover from an abnormal termination.
Above four rules are very important for any developers dealing with databases.

Can you explain the role of each service: SQL SERVER – is for running the databases SQL AGENT – is for automation such as Jobs, DB Maintenance, Backups DTC – Is for linking and connecting to other SQL Servers.

Difference between Triggers and Stored procedures
A STORED PROCEDURE is a set of structured query language statements that you assign a name and store it in to the database in a compiled form so that it can share between numbers of programs.
Some advantages of Store Procedure.-they allow faster execution. -they can reduce network traffic.
Triggers are basically used to implement business rules. A trigger is also similar to stored procedures. The difference is that it can be activated when data is added or edited or deleted from a table in a database. Triggers are special kind of stored procedures that get executed automatically when an INSERT, UPDATE or DELETE operation takes place on a table.

Creates a DML, DDL, or logon trigger.

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.
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.
Note: These triggers fire when any valid event is fired, regardless of whether or not any table rows are affected.

CREATE TRIGGER tr_InetLog_INSERT
ON InetLog
FOR INSERT
AS

IF EXISTS (SELECT * FROM inserted WHERE Target = ‘AboutUs.htm’)
BEGIN
UPDATE LogSummary
SET LogSum_Count = (SELECT COUNT(*) FROM InetLog WHERE Target = ‘AboutUs.htm’)
WHERE LogSum_Category = ‘About Us’
END

IF EXISTS (SELECT * FROM inserted WHERE Target = ‘Services.htm’)
BEGIN
UPDATE LogSummary
SET LogSum_Count = (SELECT COUNT(*) FROM InetLog WHERE Target = ‘Services.htm’)
WHERE LogSum_Category = ‘Services’
END
Go

Types:
1. After Trigger
2. Multiple After Triggers
3. Instead Of Triggers
4. Mixing Triggers Type

What is Normalization: The logical design of the database, including the tables and the relationships between them, is the core of an optimized relational database. A good logical database design can lay the foundation for optimal database and application performance. A poor logical database design can impair the performance of the entire system. Normalizing a logical database design involves using formal methods to separate the data into multiple, related tables.

A greater n umber of narrow tables (with fewer columns) is characteristic of a normalized database. A few wide tables (with more columns) is characteristic of an non-normalized database. Reasonable normalization often improves performance. When useful indexes are available, the Microsoft� SQL Server� 2000 query optimizer is efficient at selecting rapid, efficient joins between tables. Some of the benefits of normalization include: �Faster sorting and index creation. � A larger number of clustered indexes. For more information, Narrower and more compact indexes. �Fewer indexes per table, which improves the performance of INSERT, UPDATE, and DELETE statements. �Fewer null values and less opportunity for inconsistency, which increase database compactness. As normalization increases, so do the number and complexity of joins required to retrieve data. Too many complex relational joins between too many tables can hinder performance.

Reasonable normalization often includes few regularly executed queries that use joins involving more than four tables. Sometimes the logical database design is already fixed and total redesign is not feasible. Even then, however, it might be possible to normalize a large table selectively into several smaller tables. If the database is accessed through stored procedures, this schema change could take place without affecting applications. If not, it might be possible to create a view that hides the schema change from the applications.

What are different normalization forms?

1NF: Eliminate Repeating Groups: Make a separate table for each set of related attributes, and give each table a primary key. Each field contains at most one value from its attribute domain.
2NF: Eliminate Redundant Data: If an attribute depends on only part of a multi‐valued key, remove it to a separate table.
3NF: Eliminate Columns Not Dependent On Key: If attributes do not contribute to a description of the key, remove them to a separate table. All attributes must be directly dependent on the primary key.
BCNF: Boyce‐Codd Normal Form: If there are non‐trivial dependencies between candidate key attributes, separate them out into distinct tables.
4NF: Isolate Independent Multiple Relationships: No table may contain two or more 1:n or n:m relationships that are not directly related.
5NF: Isolate Semantically Related Multiple Relationships: There may be practical constrains on information that justify separating logically related many‐to‐many relationships.
ONF: Optimal Normal Form: A model limited to only simple (elemental) facts, as expressed in Object Role Model notation.
DKNF: Domain‐Key Normal Form: A model free from all modification anomalies is said to be in DKNF.
Remember, these normalization guidelines are cumulative. For a database to be in 3NF, it must first fulfill all the criteria of a 2NF and 1NF database.

Can you explain what View is in SQL: View is just a virtual table nothing else which is based or we can say develop with SQL SELECT query? So we can say that it’s a real database table (it has columns and rows just like a regular table), but one difference is that real tables store data, but views cant. View data is generated dynamically when the view is referenced. And view can also reference one or more existing database tables or other views. We can say that it is filter of database.

How to get which Process is Blocked in SQL SERVER: There are two ways to get this sp_who and sp_who2 . You cannot get any detail about the sp_who2 but its provide more information than the sp_who . And other option from which we can find which process is blocked by other process is by using Enterprise Manager or Management Studio, these two commands work much faster and more efficiently than these GUI-based front-ends.

Can you tell me the difference between DELETE and TRUNCATE commands

Delete command removes the rows from a table based on the condition that we provide with a WHERE clause. Truncate will actually remove all the rows from a table and there will be no data in the table after we run the truncate command.

What are the Global Temporary Tables: We can create global temporary tables but these are not using much in sql and the name of this table start with two pound signs? For example, ##interviewqsn is a global temporary table. As the name suggest these table is Global temporary tables and visible to all SQL Server connections. When we create any one of these all users can see it.

SQL interview Questions and Answers

MS-SQL Server

Microsoft SQL server is a relational database management system. SQL Server offers a high level of security, reliability and scalability depending on the business needs. The server offers a wide data storage, full text query search, buffer management, logging and transaction, fast data retrieval etc. it offers a variety of replication (making copies) services to avoid losing data. It offers SQL Server Reporting Services for data gathered from the database.

Database:
A database is similar to a data file in that it is a storage place for data. Like a data file, a database does not present information directly to a user; the user runs an application that accesses data from the database and presents it to the user in an understandable format. Database systems are more powerful than data files in that data is more highly organized. In a well-designed database, there are no duplicate pieces of data that the user or application must update at the same time.

Related pieces of data are grouped together in a single structure or record, and relationships can be defined between these structures and records. When working with data files, an application must be coded to work with the specific structure of each data file. In contrast, a database contains a catalog that applications use to determine how data is organized. Generic database applications can use the catalog to present users with data from different databases dynamically, without being tied to a specific data format.

A database typically has two main parts: first, the files holding the physical database and second, the database management system (DBMS) software that applications use to access data. The DBMS is responsible for enforcing the database structure, including: – maintaining relationships between data in the database. Ensuring that data is stored correctly and that the rules defining data relationships are not violated. – recovering all data to a point of known consistency in case of system failures.

Relational Database: Although there are different ways to organize data in a database, relational databases are one of the most effective. Relational database systems are an application of mathematical set theory to the problem of effectively organizing data. In a relational database, data is collected into tables (called relations in relational theory).

A table represents some class of objects that are important to an organization. For example, a company may have a database with a table for employees, another table for customers, and another for stores. Each table is built of columns and rows (called attributes and tuples in relational theory). Each column represents some attribute of the object represented by the table. For example, an Employee table would typically have columns for attributes such as first name, last name, employee ID, department, pay grade, and job title.

Each row represents an instance of the object represented by the table. For example, one row in the Employee table represents the employee who has employee ID 12345. When organizing data into tables, you can usually find many different ways to define tables. Relational database theory defines a process called normalization, which ensures that the set of tables you define will organize your data effectively.

To Create DB: CREATE DATABASE DatabaseName

What is SQL what its uses and its component

Structured Query Language (SQL) is foundation for all relational database systems. Most of the large-scale databases use the SQL to define all user and administrator interactions. QL is Non-Procedural language. It allow the user to concentrate on specifying what data is required rather than concentrating on the how to get it.
The DML component of SQL comprises four basic statements:
* SELECT to get rows from tables
* UPDATE to update the rows of tables
* DELETE to remove rows from tables
* INSERT to add new rows to tables

What is the difference between SQL and T-SQL? Why would a company use one over the other?
ANSI SQL is the standard Structured Query Language. Most database vendors support SQL. T-SQL is Microsoft’s “flavor” of SQL; it is ANSI SQL with Microsoft’s extensions. A company could choose to use T-SQL over SQL if they have a database application that communicates with a MS SQL Server database only. Therefore a developer could use the “extras” for improved performance, ease of SQL coding, etc. A company could chose SQL because a front-end may have to communicate with several relational database management systems (RDBMs) therefore a programmer could rely on the fact that most database understand ANSI SQL.

Provide all the built in string function of SQL SERVER:

ASCII, NCHAR, SOUNDEX, CHAR, PATINDEX, SPACE, CHARINDEX, REPLACE, STR, DIFFERENCE, QUOTENAME, STUFF, LEFT, REPLICATE, SUBSTRING, LEN, REVERSE, UNICODE, LOWER, RIGHT, UPPER, LTRIM, RTRIM.

How to get number of Maximum connection can be establish to SQL
Select @@MAX_Connections.

Different index configurations table in SQL:

In SQL table can have following index configurations just like: – No indexes, A clustered index, A clustered index and many non-clustered indexes, A non-clustered index, Many non-clustered indexes.

How many objects SQL Server contains: Here is the list of some of the more important database objects database, The transaction log, Assemblies, Tables, Reports, Filegroups, Full-text catalogs, Diagrams, User-defined data types, Views, Roles, Stored procedures, Users, User Defined Functions.

Difference between Extents and Page: Extents: Multiple objects will use the same extent. SQL Server will allow a max of eight objects to use a shared extent.

Why we use SET ROWCOUNT in SQL: This syntax is used in SQL Server to stop processing the query after the specified number of rows is returned.

Why we use Unicode in SQL server: Unicode data is stored using the nchar, nvarchar, and ntext data types in SQL Server. Use these data types for columns that store characters from more than one character set. The SQL Server Unicode data types are based on the National Character data types in the SQL-92 standard.

What is a join and List different types of joins: Joins are used in queries to explain how different tables are related. Joins also let you select data from a table depending upon data from another table.

Types of joins:

• INNER JOIN,
• OUTER JOIN,
• CROSS JOIN.
OUTER JOIN is further classified as
• LEFT OUTER JOINS,
• RIGHT OUTER JOINS and
• FULL OUTER JOINS.

How does .NET and SQL SERVER thread is work

There are two types of threading pre-emptive and Non-preemptive but SQL Server support Non-preemptive and .NET thread model is different. Because SQL have to handle thread in different way for SQLCLR this different thread are known as Tasking of Threads. In this thread there is a switch between SQLCLR and SQL SERVER threads .SQL SERVER uses blocking points for transition to happen between SQLCLR and SQL SERVER threads.

Difference between temp table and table variable
• Temp Tables are created in the SQL Server TEMPDB database and therefore require more IO resources and locking. Table Variables and Derived Tables are created in memory.
• Temp Tables will generally perform better for large amounts of data that can be worked on using parallelism whereas Table Variables are best used for small amounts of data (I use a rule of thumb of 100 or less rows) where parallelism would not provide a significant performance improvement.
• You cannot use a stored procedure to insert data into a Table Variable or Derived Table. For example, the following will work: INSERT INTO #MyTempTable EXEC dbo.GetPolicies_sp whereas the following will generate an error: INSERT INTO @MyTableVariable EXEC dbo.GetPolicies_sp.
• Derived Tables can only be created from a SELECT statement but can be used within an Insert, Update, or Delete statement.
• In order of scope endurance, Temp Tables extend the furthest in scope, followed by Table Variables, and finally Derived Tables.

How many types of local table in SQL define with syntax

There are 2 types of temporary tables, local and global. Local temporary tables are created using a single pound (#) sign and are visible to a single connection and automatically dropped when that connection ends. Global temporary tables are created using a double pound (##) sign and are visible across multiple connections and users and are automatically dropped when all SQL sessions stop referencing the global temporary table.

CREATE TABLE #MyTempTable ( PolicyId INT IDENTITY(1,1) PRIMARY KEY NOT NULL,LastName VARCHAR(50) NOT NULL )

What is SQL tuning: SQL tuning is the process of getting that the SQL statements that an application that will issue that’s run in the fastest possible time.
What is SQL injection: SQL injection is a security vulnerability that occurs in the database layer of an application. The vulnerability is present when user input is either incorrectly filtered for string literal escape characters embedded in SQL statements or user input is not strongly typed and thereby unexpectedly executed. It is in fact an instance of a more general class of vulnerabilities that can occur whenever one programming or scripting language is embedded inside another.

What is SET operator in SQL SERVER: SET operators mainly used to combine same type of data from two or more tables. And another thing is that columns and their data type should be same as all the queries have. The column names from the first query will appear in the result.
UNION – It produce rows of first query + rows of 2nd query minus duplicate rows
UNION ALL – It produce rows from both the queries including duplicate rows.

select empid from emp union all select empid from emp_att group by empid
MINUS – Rows that are unique for the 1st query will be retrieved
INTERSECT – common rows from both the queries will be retrieved.
Join is used to select columns from two or more tables.

Can you define ROLLUP in SQL SERVER 2005: ROLLUP work with the “Group By” clause its main functioning comes into existence when we use Group by. We can get sub-total of row by using the Rollup function. When result is return by Group By class first row display the grand total or we can say that the main total.

Syntax: – select firstcolumn,secondcolumn,sum(thirdcolumn) from tablename group by firstcolumn,secondcolumn with rollup order by firstcolumn.

How many records can take clustered index in SQL: A clustered index is a special type of index that reorders the way the records in the table are physically stored. Therefore the table can have only one clustered index.

What is Cascade and Restrict when we use DROP table in SQL SERVER
When we are using Drop table in SQL the syntax is simple. Drop table table_name (CASCADE / RESTRICT) we use cascade to drop table although it have some dependencies just like triggers, storedprocedure, primarykey, foreignkey it will delete first. But if we use restrict a error message is shown on using of DROP if the table have relation Trigger, storedprocedure.
Some Tips when Optimizing SQL Server 2005 Query
1. Turn on the execution plan, and statistics
2. Use Clustered Indexes
3. Use Indexed Views
4. Use Covering Indexes
5. Keep your clustered index small.
6. Avoid cursors
7. Archive old data
8. Partition your data correctly
9. Remove user-defined inline scalar functions
10. Use APPLY
11. Use computed columns
12. Use the correct transaction isolation level

What is SQL Cache Dependency in ASP.NET 2.0: SQL cache dependencies are new technique in ASP.NET 2.0 which can automatically invalidate a cached data object just like a Dataset. When the related data is modified in the database. So for instance if you have a dataset which is tied up to a database tables any changes in the database table will invalidate the cached data object which can be a dataset or a data source. To enable this we need a syntax that is as follows: – aspnet_regSQL -ed -E -d Northwind.

How many types of local tables in SQL SERVER: There are 2 types of temporary tables, local and global. Local temporary tables are created using a single pound (#) sign and are visible to a single connection and automatically dropped when that connection ends. Global temporary tables are created using a double pound (##) sign and are visible across multiple connections and users and are automatically dropped when all SQL sessions stop referencing the global temporary table.

Where .NET CLR and SQL SERVER run: All .net application and SQL Server runs in same process or we can say that on same address because there is no issue of speed because if these two process are run in different process then there may be a speed issue created one process goes fast and other slow may create the problem.

What are Checkpoint in SQL Server: When we done operation on SQL SERVER that is not committed directly to the database. All operation must be logged in to Transaction Log files after that they should be done on to the main database. Checkpoint are the point which alert SQL Server to save all the data to main database if no Check point is there then log files get full we can use Checkpoint command to commit all data in the SQL SERVER. When we stop the SQL Server it will take long time because Checkpoint is also fired.

Explain integration between SQL Server 2005 and Visual Studio 2005: This integration provides wider range of development with the help of CLR for database server. Because CLR helps developers to get flexibility for developing database applications and also provides language interoperability just like Visual C++, Visual Basic .Net and Visual C# .Net. The CLR helps developers to get the arrays, classes and exception handling available through programming languages such as Visual C++ or Visual C# which is use in stored procedures, functions and triggers for creating database application dynamically and also provide more efficient reuse of code and faster execution of complex tasks. We particularly liked the error-checking powers of the CLR environment, which reduces run-time errors.

What is the difference between SQL and PL-SQL: We can get modify, Retrieve by single command or statement in SQL but PL/SQL process all SQL statements one at a time. With PL/SQL, an entire block of statements process in a single command line.SQL is structured query language, various queries are used to handle the database in a simplified manner. While PL/SQL is procedural language contains various types of variable, functions and procedures and other major difference is SQL as the name suggest it is just structured query language whereas PLSQL is a combination of Programming language & SQL.

What is the difference between UNION ALL Statement and UNION

The main difference between UNION ALL statement and UNION is UNION All statement is much faster than UNION, the reason behind this is that because UNION ALL statement does not look for duplicate rows, but on the other hand UNION statement does look for duplicate rows, whether or not they exist.

Write some disadvantage of Cursor: Cursor plays there row quite nicely but although there are some disadvantage of Cursor. Because we know cursor doing roundtrip it will make network line busy and also make time consuming methods. First of all select query grenade output and after that cursor goes one by one so roundtrip happen. Another disadvantage of cursor is there are too costly because they require lot of resources and temporary storage so network is quite busy.

What is Log Shipping and its purpose: In Log Shipping the transactional log file from one server is automatically updated in backup database on the other server and in the case when one server fails the other server will have the same DB and we can use this as the DDR(disaster recovery) plan.