What maximum size of row is allowed in SQL Server ?
8060 bytes
What is referential integrity in SQL Server ?
We can enforce such referential integrity through foreign key constraints.
For an example,…
CREATE TABLE Table_Referenced
(
id INT
CONSTRAINT pk_id PRIMARY KEY(id)--Create Primary Key here
);
GO
CREATE TABLE Table_Referencing
(
RID Int
CONSTRAINT fk_RID FOREIGN KEY(RID)REFERENCES Table_Referenced(id)--Creates foreign key names 'RID' which is referring primary key of Table_Referenced.id
);
SET NOCOUNT ON and SET NOCOUNT OFF
This Statement turns off the messages that SQL Server sends back to the client after each T-SQL statement is executed. This is performed for all SELECT, INSERT, UPDATE, and DELETE statements
How to pass table name as stored procedure parameter in SQL Server 2005 (Write stored procedure) ?
Create PROCEDURE Pass_TableName_As_Param --create procedure
@TableName varchar(50) -- Parameter of table name
AS
BEGIN
SET NOCOUNT ON;
SET @TableName= RTRIM(@TableName) --Trim the parameter of table name
DECLARE @cmd AS NVARCHAR(max)
SET @cmd = N'SELECT * FROM'+ @table_name
END
Maximum number of parameters you can pass in a Store procedure in SQL Server 2005?
2100 in sqlserver 2005
1024 in sqlserver 2000
What is the name of the Store procedure to get the listing of current users?
sp_who
Difference between clustered index and non clustered index
The leaf level of a clustered index is the actual data and the data is resorted in case of clustered index. Whereas in case of non-clustered index the leaf level is actually a pointer to the data in rows so we can have as many non-clustered indexes as we can on the db. The Telephone Directory is a fine example of Clustered Index as data and index are at the same page, whereas index in the back side of the book is a fine example of non-clustered index and non-clustered index is a fast B-tree structure as index just points to the data page. Also only one clustered index is possible per table and 249 non-clustered index per table.
What is trigger?
Triggers are special types of stored procedures allows us to execute a batch of SQL code when either an insert, update or delete command is executed against a specific table.
There are four types of triggers.
1. Insert 2. Delete 3. Update 4. Instead of
What is constraints?
SQL Server users constraints to enforce limitations on the data that can be entered into a particular column in table. There are following types of constraints.
Unique, Default, Check, Primary Key, Foreign Key, Not Null.
What is the difference between Truncate and Delete?
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. Delete Command require Log file updation for each row of deleting process. But the Truncate command not. Truncate will fail if the concerned table has foreign key constraints while delete doesnt.
What's the difference between a primary key and a unique key?
Both primary key and unique enforce uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, where are unique creates a nonclustered index by default. Another major difference is that, primary key doesn't allow NULLs, but unique key allows one NULL only.
ACID
Atomicity, Consistency, Isolation, Durability
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.
Difference Between Implict Transaction And Explict Transaction
Implicit Transaction is the auto commit. There is no beginning or ending of the transaction.
Explicit Transaction has the beginning, ending and rollback of transactions with the command
Begin Transaction
Commit Transaction and
Rollback Transation
In the explicit transaction, if an error occurs in between we can rollback to the begining of the transaction which cannot be done in implicit transaction.
How to change Database name in SQL Server?
Use following code
Supported in SQL Server 2000 and 2005
exec sp_renamedb "test", "test1"
Supported in SQL Server 2005 and later version
ALTER Database "test1" Modify Name="test"
What is #temp table and @table variable in SQL Server?
#temp - is created and populated on disk, in the system database tempdb. Generally, the table gets cleared up automatically when the current procedure goes out of scope, however, we should manually clean up the data when we are done with it.
-- create temporary table
CREATE TABLE #myTempTable (
AutoID int,
MyName char(50) )
-- populate temporary table
INSERT INTO #myTempTable (AutoID, MyName )
SELECT AutoID, MyName
FROM myOriginalTable
WHERE AutoID <= 50000
-- Drop temporary table
drop table #myTempTable
@table variable
table variable is similar to temporary table except with more flexibility. It is not physically stored in the hard disk, it is stored in the memory. We should choose this when we need to store less 100 records.
Syntax:
DECLARE @myTable TABLE (
AutoID int,
myName char(50) )
INSERT INTO @myTable (AutoID, myName )
SELECT YakID, YakName
FROM myTable
WHERE AutoID <= 50
We don't need to drop the @temp variable as this is created inside the memory and automatically disposed when scope finishes.
How to return XML in SQL Server?
We can use FOR XML statement at the end of the query to return xml data from the SQL Server.
select * from mytable for xml auto
There are three mode of returning XML and they are auto, raw and explicit
What is OPENXML in SQL Server?
OPENXML can parse the xml data in SQL server very efficiently in SQL Server. OpenXML primarily gives the ability to insert XML data to the relational database, however we can query the data too using OpenXML. We need to specify the path of the xml element using xpath.
Syntax:
DECLARE @index int
DECLARE @xmlString varchar(8000)
SET @xmlString ='<Persons>
<Person id="1">
<Name>Mohan</Name>
<PhoneNo>34343</PhoneNo>
</Person>
<Person id="2">
<Name>Sita</Name>
<PhoneNo>23432</PhoneNo>
</Person>
</Persons>'
EXEC sp_xml_preparedocument @index OUTPUT, @xmlString
SELECT *
FROM OPENXML (@index, 'Persons/Person')
WITH (id Varchar(10), Name varchar(100) 'Name' , PhoneNo Varchar(50) 'PhoneNo')
EXEC sp_xml_removedocument @index
The above code snippet will give following result.
---------------------------------
1 Mohan 34343
2 Sita 23432
---------------------------------
What is the use of COALESCE in SQL Server?
Coalesce returns the first non-null expression among its arguments.
Lets say we have to return a non-null from more than one column, then we can use COALESCE function.
SELECT COALESCE(hourly_wage, salary, commission) AS 'Total Salary' FROM wages
In this case,
If hourly_wage is not null and other two columns are null then hourly_wage will be returned.
If hourly_wage, commission are null and salary is not null then salary will be returned.
If commission is non-null and other two columns are null then commission will be returned.
What is cursor in SQL Server?
A cursor is a set of rows together with a pointer that identifies a current row.
In other word, Cursor is a database object used by applications to manipulate data in a set on a row-by-row basis, its like recordset in the ASP and visual basic.
Typical syntax of cursor is
DECLARE @fName varchar(50), @lName varchar(50)
DECLARE cursorName CURSOR -- Declare cursor
LOCAL SCROLL STATIC
FOR
Select firstName, lastName FROM myTable
OPEN cursorName -- open the cursor
FETCH NEXT FROM cursorName
INTO @fName, @lName
PRINT @fName + ' ' + @lName -- print the name
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM cursorName
INTO @fName, @lName
PRINT @fName + ' ' + @lName -- print the name
END
CLOSE cursorName -- close the cursor
DEALLOCATE cursorName -- Deallocate the cursor
DESCRIBE is used to see table structure
How you can get the last identity value inserted in any table ?
@@IDENTITY
What is the maximum size of an SQL Server 2000 Database?
1,048,516 TB
how many database can be created on MSSQL Server 2000
32767
What is the use of @@TRANCOUNT in SQL Server?
Returns the number of active transactions for the current connection.
What is the maximum limit for Primary Key?
10 fields in MS Acces
900 Bytes in SQL Server
Where do you think the users names and passwords will be stored in sql server?
They get stored in master db in the sysxlogins table.
What is Log Shipping?
log shipping to feed transaction logs from one database to another
What Is DTS?
DTS is a set of tools you can use to import, export, and transform heterogeneous data between one or more data sources, such as Microsoft SQL Server, Microsoft Excel, or Microsoft Access. Connectivity is provided through OLE DB, an open-standard for data access. ODBC (Open Database Connectivity) data sources are supported through the OLE DB Provider for ODBC.
What is a tuple?
A tuple is an instance of data within a relational database.
What are three SQL keywords used to change or set someone’s permissions?
Grant, Deny and Revoke
How to get GUID in sql server?
select newid().
Can we create a Foreign Key with out Primary Key?
Yes. If the table has Unique Key then it is posible to create a Foreign key constraint
Difference between varchar and char:
varchar are variable length strings with a maximum length specified. If a string is less than the maximum length, then it is stored verbatim without any extra characters.
char are fixed length strings with a set length specified. If a string is less than the set length, then it is padded with extra characters so that it's length is the set length.
What is the use of Keyword WITH ENCRYPTION keyword in SQL Server?
WITH ENCRYPTION keyword is used to encrypt the text of the Stored Procedure
Microsoft SQL Server's default protocol
Named Pipes
What are the disadvantages/limitation of the cursor?
Cursor requires a network roundtrip each time it fetches a record, thus consume network resources.
While data processing, it issues locks on part of the table, or on the whole table.
The maximum number of columns a table can have in SQL Server?
1024
38) How to Get SQL Server Version ?
@@VERSION
SELECT @@VERSION
39) What is the Max length of varchar variable in SQL Server?
8000
40) What is the Max Length of nVarchar in SQL Server?
4000
41) What is 'Self-referencing' tables in SQL Server ?
A FOREIGN KEY constraint can reference columns within the same table (a table itself).
42) DEFAULT definitions cannot be created on columns defined with the following DATA TYPE ?
Timestamp
43) What is the Max length of varchar variable in SQL Server?
8000
44) Number of PRIMARY KEY/FOREIGN KEY Constraints in a Table in SQL Server 2005/2008 ?
PRIMARY KEY :1, FOREIGN KEY :UNLIMITED (Recommended is 253)
45) How Can we Write the Transaction Block in SQL Server?
BEGIN TRANSACTION
Statement 1
Statement 2
..................
...............
IF(@ERROR>0)
ROLLBACK TRANSACTION
ELSE
COMMIT TRANSACTION
46) What is the page size in SQL Server?
8KB
47) How do you determine the maximum nested-level of Stored Procedure ?
The current nested level can be determine by : @@NESTLEVEL, The maximum nested level is 32 .
48) Which Function is used to count more than two billion rows in a table?
COUNT_BIG function - is identical to the COUNT function, but returns a BIGINT data type, whereas COUNT returns an INT
49) SQL Server allows for only 10 levels of nesting in CASE expressions.
50) Is it possible to create Cluster Index on Unique Key Column?
YES
51)How to Get nth Record in a Table?
First Get the n records fron the table using
Select Top n FROM UserTable
Now Reverse the Order using identity Column like:
Select Top n FROM UserTable Order By 1 DESC
Now we need nth record that can be get as
SELECT TOP 1 * FROM (Select Top n FROM UserTable Order By 1 DESC)AC
For Example i need to get 5th record From userTable then quey will be like this:
SELECT TOP 1 * FROM (SELECT TOP 5 * FROM UserTable Order By 1 DESC)AC
52) Can we use Truncate command on a table which is referenced by FOREIGN KEY?
No
53) What is a table called, if it has no Cluster Index?
Heap
54) How we can get the List of System Tables in DataBase?
select * from Sys.Objects where Type='s'
55) How we can generate Random number in SQL Server?
Select NEWID()
56) How We can Get List of Store Procedures?
select * from Sys.Objects where Type='p'
57) How we can Get List of Triggers?
select * from Sys.Objects where Type='tr'
58) Query to Select n Rendom Records From a Table in the Database?
SELECT TOP 3 * FROM Tbl_REGISTRATION ORDER By NEWID()
59) Query to Get List of Views?
select * from information_schema.views
61) What is the name of store procedure to send Email using SQL Server 2005/2008?
sp_send_dbmail in msdb database can be used to send email using SQL Server 2005/2008
