What is the difference between a Local and a Global temporary table?
A local temporary table exists only for the duration of a connection or, if defined inside a compound statement, for the duration of the compound statement.
A global temporary table remains in the database permanently, but the rows exist only within a given connection. When connection is closed, the data in the global temporary table disappears. However, the table definition remains with the database for access when database is opened next time.
Q - Can a stored procedure call itself or recursive stored procedure? How much level SP nesting is possible?
Ans- Yes. T-SQL supports recursion, you can wrirte stored procedure that call themselves.Stored procedure can be nested up to 32 levels.
Q - What is CHECK Constraint?
Ans- A CHECK constraint is used to limit the values that can be placed in a column. The check constraints are used to enforce domain integrity.
A local temporary table exists only for the duration of a connection or, if defined inside a compound statement, for the duration of the compound statement.
A global temporary table remains in the database permanently, but the rows exist only within a given connection. When connection is closed, the data in the global temporary table disappears. However, the table definition remains with the database for access when database is opened next time.
Q - Can a stored procedure call itself or recursive stored procedure? How much level SP nesting is possible?
Ans- Yes. T-SQL supports recursion, you can wrirte stored procedure that call themselves.Stored procedure can be nested up to 32 levels.
Q - What is CHECK Constraint?
Ans- A CHECK constraint is used to limit the values that can be placed in a column. The check constraints are used to enforce domain integrity.
What is trigger?
Triggers allows us to execute a batch of SQL code when either an insert, update or delete command is executed against a specific table.
Triggers are special types of stored procedures that are defined to execute automatically in place of or after data modifications. They can be executed automatically on the insert, delete and update operation.
What is Clustered & Non-Clustered Index?
Clustered Index: Clustered index physically rearrange the data that users inserts in your tables. It is nothing but a dictionary type data where actual data remains.
Non-Clustered Index: It Non-Clustered Index contains pointers to the data that is stored in the data page. It is a kind of index backside of the book where you see only the reference of a kind of data.
How to join two tables in Sql Server?
you can write following sql statement
select category.*, categoryparent.categoryparent from category, categoryparent where category.categoryparentid = categoryparent.autoid
I am assuming here that category.categoryparentid (foreign key) is the value of categoryparent.autoid (primary key).
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'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.
What's the difference between DELETE TABLE and TRUNCATE TABLE commands?
DELETE TABLE is a logged operation, so the deletion of each row gets logged in the transaction log, which makes it slow. TRUNCATE TABLE also deletes all the rows in a table, but it won't log the deletion of each row, instead it logs the deallocation of the data pages of the table, which makes it faster. Of course.
What is a transaction and what are ACID properties?
A transaction is a logical unit of work in which, all the steps must be performed or none. ACID stands for Atomicity, Consistency, Isolation, Durability. These are the properties of a transaction. For more information and explanation of these properties, see SQL Server books online or any RDBMS fundamentals text book.
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.
Difference between Primary key Constraint and Unique key Constraint in SQL Server.
Unique Key Constraint:
The column values should retain uniqueness.
It allows null values in the column.
It will create non-clustered index by default.
Any number of unique constraints can be added to a table.
Primary Key Constraint:
Primary key will create column data uniqueness in the table.
It Wont allow Null values.
By default Primary key will create clustered index.
Only one Primary key can be created for a table.
Multiple columns can be consolidated to form a single primary key.
Source: [b]http://interviews.dotnetthread.com/ [/b]
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
To know more about cursor, see
http://www.mssqlcity.com/Articles/General/UseCursor.htm
What is #temp table and @table variable in SQL Server?
#temp Table (Temporary Table)
temp table is a temporary table that is generally created to store session specific data. Its kind of normal table but it is created and populated on disk, in the system database tempdb — with a session-specific identifier packed onto the name, to differentiate between similarly-named #temp tables created from other sessions.
The data in this #temp table (in fact, the table itself) is visible only to the current scope. 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.
Syntax:
-- 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 myOriginalTable WHERE AutoID <= 50 -- to select the data from Temp variable SELECT * FROM @myTable
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
For more details see http://www.sqljunkies.ddj.com/Article/296D1B56-8BDD-4236-808F-E62CC1908C4E.scuk
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
---------------------------------
No comments:
Post a Comment