Difference Between Truncate and Delete:
TRUNCATE is a DDL command whereas DELETE is a DML command.
2>TRUNCATE is much faster than DELETE.
Reason:When you type DELETE.all the data get copied into the Rollback Tablespace first.then
delete operation get performed.Thats why when you type ROLLBACK after deleting a table ,you
can get back the data(The system get it for you from the Rollback Tablespace).All this
process take time.But when you type TRUNCATE,it removes data directly without copying it
into the Rollback Tablespace.Thatswhy TRUNCATE is faster.Once you Truncate you cann't get
back the data.
3>You cann't rollback in TRUNCATE but in DELETE you can rollback.TRUNCATE removes the
record permanently.
4>In case of TRUNCATE ,Trigger doesn't get fired.But in DML commands like DELETE .Trigger
get fired.
5>You cann't use conditions(WHERE clause) in TRUNCATE.But in DELETE you can write
conditions using WHERE clause
There is one more difference that TRUNCATE command resets the High Water Mark for the table .
but DELETE does not. So after TRUNCATE the operations on table are much faster.
That is because
DELETE has only
ghosted the rows.
The table is just as big as when it had 12M rows, even though it has
none. To count the rows (0), it takes just as much time it took to count
12M rows. In time the ghost cleanup process will garbage collect these
ghosted records and deallocate pages that contained
only ghosts, and your SELECTs will speed up. But right now if you check
Skipped Ghosted Records/sec in perfmon is probably skyrocketing during
SELECT COUNT(*).
TRUNCATE is a logged operation. It has to be otherwise it's not ACID-compliant. However, differences between
TRUNCATE and
DELETE:
- Log space usage:
TRUNCATE only logs pages/extents* freed, whereas DELETE logs individual rows.
- Lock usage:
TRUNCATE will generally use less locks, since it takes a table lock and page locks, as opposed to DELETE which uses row locks**.
IDENTITY sequences: TRUNCATE resets the identity sequence on a table, if present.
DELETE is a logged operation, and it doesn't free the space used. You
probably have a large transaction log taking up space and table scans
running on the 'empty' table space. I'd guess you need to clear the
transaction log and shrink your database.
This StackOverflow article should get you started.
What is the difference between a clustered and a nonclustered index?
A clustered index affects the way the rows of data in a table are
stored on disk. When a clustered index is used, rows are stored in
sequential order according to the index column value; for this reason, a
table can contain only one clustered index, which is usually used on
the primary index value.
A nonclustered index does not affect the way data is physically
stored; it creates a new object for the index and stores the column(s)
designated for indexing with a pointer back to the row containing the
indexed values.
You can think of a clustered index as a dictionary in alphabetical order, and a nonclustered index as a book’s index.
What are DBCC commands?
Basically, the Database Consistency Checker (DBCC) provides a set of
commands (many of which are undocumented) to maintain databases —
maintenance, validation, and status checks. The syntax is DBCC followed
by the command name. Here are three examples:
DBCC CHECKALLOC — Check disk allocation consistency.
DBCC OPENTRAN — Display information about recent transactions.
DBCC HELP — Display Help for DBCC commands.
What is the difference between truncate and delete?
Truncate is a quick way to empty a table. It removes everything
without logging each row. Truncate will fail if there are foreign key
relationships on the table. Conversely, the delete command removes rows
from a table, while logging each deletion and triggering any delete
triggers that may be present.
Some more interview Questions:
http://stackoverflow.com/questions/2119859/questions-every-good-database-sql-developer-should-be-able-to-answer
http://www.techrepublic.com/blog/programming-and-development/sql-server-database-developer-interview-questions-and-answers/5745
What is a view? What is the WITH CHECK OPTION clause for a view?
A view is a virtual table that consists of fields from one or
more real tables. Views are often used to join multiple tables or to
control access to the underlying tables.
The WITH CHECK OPTION for a view prevents data modifications (to the
data) that do not confirm to the WHERE clause of the view definition.
This allows data to be updated via the view, but only if it belongs in
the view.
What is a query execution plan?
SQL Server has an optimizer that usually does a great job of
optimizing code for the most effective execution. A query execution plan
is the breakdown of how the optimizer will run (or ran) a query. There
are several ways to view a query execution plan. This includes using the
Show Execution Plan option within Query Analyzer; Display Estimated
Execution Plan on the query dropdown menu; or use the SET SHOWPLAN_TEXT
ON command before running a query and capturing the execution plan event
in a SQL Server Profiler trace.
Pre compiled sp meaning:
The sql content of the stored proc won't be parsed if you run it. The sql parsing can be very time consuming operation.
In Microsoft SQL Server, stored procedures are compiled into a query plan the first time they are run.
At subsequent runs, they are sometimes recompiled from source, but not always. That is why they are called "pre-compiled".
There is a misconception that stored
procedures are pre-compiled. They are not pre-compiled, but compiled
only during the first run. For every subsequent runs, it is for sure
pre-compiled.
If you create any SP, you will find that there is no cache entry for the execution of that SP.
After running the SP for the first time, the entry for the cache is made in the system.
If we see the following script, we can notice the different of cache when SP was created and SP was executed.
/* Exeercise to verify if stored procedure pre-compiled */
USE AdventureWorks
GO
-- Clean Cache
DBCC FREEPROCCACHE
GO
IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[CompSP]') AND type IN (N'P', N'PC'))
DROP PROCEDURE [dbo].[CompSP]
GO
-- Create New Stored Procedure
CREATE PROCEDURE CompSP
AS
SELECT *
FROM HumanResources.Department
GO
-- Check the Query Plan for SQL Batch
-- You will find that there is no ObjectName with CompSP
SELECT cp.objtype AS PlanType,
OBJECT_NAME(st.objectid,st.dbid) AS ObjectName,
cp.refcounts AS ReferenceCounts,
cp.usecounts AS UseCounts,
st.TEXT AS SQLBatch,
qp.query_plan AS QueryPlan
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st;
GO
/* Execute Stored Procedure */
EXEC CompSP
GO
-- Check the Query Plan for SQL Batch
-- You will find that there is one entry with name ObjectName with name CompSP
SELECT cp.objtype AS PlanType,
OBJECT_NAME(st.objectid,st.dbid) AS ObjectName,
cp.refcounts AS ReferenceCounts,
cp.usecounts AS UseCounts,
st.TEXT AS SQLBatch,
qp.query_plan AS QueryPlan
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st;
GO
http://weblogs.asp.net/gunnarpeipman/archive/2010/10/21/sql-server-how-to-insert-million-numbers-to-table-fast.aspx
What is Query Plan:
A
query plan (or
query execution plan) is an ordered set of steps used to access or modify information in a
SQL relational database management system. This is a specific case of the
relational model concept of access plans.
Since
SQL is
declarative, there are typically a large number of alternative ways to execute a given query, with widely varying performance. When a
query is submitted to the database, the
query optimizer
evaluates some of the different, correct possible plans for executing
the query and returns what it considers the best alternative. Because
query optimizers are imperfect, database users and administrators
sometimes need to manually examine and tune the plans produced by the
optimizer to get better performance.
What are temp tables? What is the difference between global and local temp tables?
Temporary tables are temporary storage structures.
Local temporary tables(#) are available to the current connection for the user, so they disappear when the user disconnects.
Global temporary tables may be created with double hashes (##). These
are available to all users via all connections, and they are deleted
only when all connections are closed.
Once created, these tables are used just like permanent tables; they
should be deleted when you are finished with them. Within SQL Server,
temporary tables are stored in the Temporary Tables folder of the tempdb
database.
temp tables life is till the query window is open if that is close than it will remove from memory.this is because every time
if you open a new window you will get a new spid .you can check it on profiler.
temp tables gets store in temp database.
create local temprory tables:
create table #testtemp(srno int identity(1,1) not null,name varchar(100))
insert into #testtemp
select 'ram'
union
select 'ram'
select * from #testtemp
create global temprory tables:
create table ##testtemp(srno int identity(1,1) not null,name varchar(100))
insert into ##testtemp
select 'ram'
union
select 'ram'
select * from ##testtemp
to check temp tables:
IF OBJECT_ID('tempdb..#testtemp') IS NOT NULL
BEGIN
PRINT '#temp exists!'
END
ELSE
BEGIN
PRINT '#temp does not exist!'
END
What is uniqueidentifier key ?
The
uniqueidentifier data type stores 16-byte binary values
that operate as globally unique identifiers (GUIDs). A GUID is a unique
binary number; no other computer in the world will generate a duplicate
of that GUID value. The main use for a GUID is for assigning an
identifier that must be unique in a network that has many computers at
many sites.
A GUID value for a
uniqueidentifier column is usually obtained:
- In a Transact-SQL statement, batch, or script by calling the NEWID function.
- In application code by calling an application API function or method that returns a GUID.
The Transact-SQL NEWID function and the application API functions and methods generate new
uniqueidentifier values
from the identification number of their network card plus a unique
number from the CPU clock. Each network card has a unique identification
number. The
uniqueidentifier returned by NEWID is generated using the network card on the server. The
uniqueidentifier returned by application API functions and methods is generated using the network card on the client.
Its a way to auto-generate key values is to specify your column as a type
of uniqueidentifier and DEFAULT using NEWID() or NEWSEQUENTIALID().
Unlike IDENTITY, a DEFAULT constraint must be used to assign a GUID
value to the column.
How do NEWID() and NEWSEQUENTIALID() differ? NEWID()
randomly
generates a guaranteed unique value based on the identification number
of the server's network card plus a unique number from the CPU clock. In
contrast, NEWSEQUENTIALID() generates these values in sequential order
as opposed to randomly.
CREATE TABLE [dbo].[TblUniqueIdentifiercheck](
[srno] [uniqueidentifier] NOT NULL DEFAULT NEWID(),
[name] [varchar](50) NULL,
[value] [nvarchar](50) NULL
)
insert into dbo.TblUniqueIdentifiercheck
select NEWID(),'asd','asd'
insert into dbo.TblUniqueIdentifiercheck(name,value)
select 'asd','asd'
Difference between identity column and uniqueidentifier:
The tables used uniqueidentifier datatype and newid() for default takes a lot of space as compare to identity coloumn used tables.
if you used
uniqueidentifier datatype with NEWSEQUENTIALID() in default value it will take less space but this is also greater than identity used tables.
identity column takes less space as the data grows in these is increase in sequential order.
SQL Server keeps the last generated identity value in memory which can
be retrieved right after an INSERT using SCOPE_IDENTITY(), @@IDENTITY,
or CHECK_IDENT (depending on the scope you require). There is nothing
similar to capture the last generated GUID value. If you use a GUID,
you'll have to create your own mechanism to capture the last inserted
value (i.e. retrieve the GUID prior to insertion or use the SQL Server
2005 OUTPUT clause).
Check out below urls for more details:
What Is trigger:
A trigger is a pl/sql block structure which is fired when a DML
statements like Insert, Delete, Update is executed on a database table. A
trigger is triggered automatically when an associated DML statement is
executed.
SQL Server 2000 has many types of triggers:
1. After Trigger
2.Instead Of Triggers
Types of triggers:
Instead of trigger:
when you want to fire another statement then you can used instead of
trigger.
eg:if you want your table hidden from users than you can used a view and once
the view is created create a instead of triggre
on that view for insert,update or delete .
you will use this view for insert ,update and delete but the operatoion will
perform on your table.INSTEAD OF INSERT triggers can be defined on a view or
table to replace the standard action of the INSERT statement. Usually, the
INSTEAD OF INSERT trigger is defined on a view to insert data into one or more
base tables.
Instead of delete example:
CREATE TABLE [dbo].[test](
[srno] [int] IDENTITY(1,1) NOT NULL,
[name] [varchar](50) NULL,
[flag] [bit] NULL
) ON [PRIMARY]
insert into test
select 'raj',True
union
select 'raj1',True
CREATE TRIGGER [dbo].[InstweadOf_Trigger]
ON [dbo].[test]
instead of delete
AS
BEGIN
update test set flag='false' where srno=(select srno from deleted)
END
delete from test where srno=1
go
delete dbo.mytesting where test_id = 1
select * from dbo.mytesting
select * from dbo.myaudit
Now Try After Trigger with same statement
CREATE TRIGGER After_Trigger
ON dbo.test
after delete
AS
BEGIN
update test set flag='false' where srno=(select srno from deleted)
END
Go
Instead of insert example:
CREATE VIEW [dbo].[Instead_OfTrigger_insert_Example]
AS
SELECT srno, catagory, isexists
FROM dbo.catagory
CREATE TRIGGER INstead_of_Insert_TRg
ON dbo.Instead_OfTrigger_insert_Example
instead of insert
AS
BEGIN
/*wecan use instead of trigger in views when wewant to hide our main table it
will not
insert into view but in catagory table
*/
declare @catagory varchar(100),@isexists bit
select @catagory=catagory,@isexists=isexists from inserted
if(@catagory='Man')
insert into dbo.catagory(catagory,isexists)
values( @catagory,@isexists)
END
GO
it is same as the for trigger
There is no difference, they do the same thing.
CREATE TRIGGER trgTable on dbo.Table FOR INSERT,UPDATE,DELETE Is the same as
CREATE TRIGGER trgTable on dbo.Table AFTER INSERT,UPDATE,DELETE.
it execute after sql statement execution is done.
Diffrence between triggers and Stored procedure:
1. When you create a trigger you have to identify event and action of
your trigger but when you create s.p you don't identify event and action
2.Trigger is run automatically if the event is occured but s.p don't run automatically but you have to run it manually
3. Within a trigger you can call specific s.p but within a sp you cannot call atrigger
4.Trigger execute implicitly whereas store procedure execute via procedure call from another block.
5.We can call a stored procedure from front end (.asp files, .aspx
files, .ascx files etc.) but we can't call a trigger from these files.
6. Stored procedure can take the input parameters, but we can't pass the parameters as an input to a trigger.
7.we can call sp from a trigger but cannot call trigger from sp.
Diffrence between temp table and table variables:
1:We cannot use truncate statement in table variables but can fire in temp table.
2:For small amount data we can use table variables as its faster than but for large data (more than 1000 records) manupulation use temp table.
3:Table variables scope is within sp and sql statement, it will delete automatically once the statement is executed but this is not possible in temp table it remains in the memory (temp db) till user disconnected the session.
4:we cannot apply non cluster index on table variables but can apply on temp table.
Eg:
Table variable
declare @temp table (srno int not null identity(1,1),name varchar(100))
insert into @temp
select 'sadsadd'
select * from @temp
Temp table:
create table #temp(srno int not null identity(1,1),name varchar(100))
insert into #temp
select 'test'
Single command for Insert and delete at a time:
DECLARE @MyTable table (col1 int, col2 varchar(5))
DECLARE @MyBackupTable table (col1 int, col2 varchar(5))
INSERT INTO @MyTable VALUES (1,'A')
INSERT INTO @MyTable VALUES (2,'B')
INSERT INTO @MyTable VALUES (3,'C')
INSERT INTO @MyTable VALUES (4,'D')
--single command that does the delete and inserts
DELETE @MyTable
OUTPUT DELETED.Col1, DELETED.COl2
INTO @MyBackupTable
--show both tables final values
select * from @MyTable
select * from @MyBackupTable
Create SSIS package In sql Server:
http://stackoverflow.com/questions/5152671/how-to-load-an-xml-file-into-a-database-using-an-ssis-package
http://www.mssqltips.com/sqlservertip/1775/different-ways-to-execute-a-sql-server-ssis-package/
What are the new keywords(query hints) Used in Sql server 2008 for optimization.
There are diffrent query hints are used in sql server 2008 for the optimization purpose which are given below:
{ HASH | ORDER } GROUP
| { CONCAT | HASH | MERGE } UNION
| { LOOP | MERGE | HASH } JOIN
| FAST number_rows
| FORCE ORDER
| MAXDOP number_of_processors
| OPTIMIZE FOR ( @variable_name { UNKNOWN | = literal_constant } [ , ...n ] )
| OPTIMIZE FOR UNKNOWN
| PARAMETERIZATION { SIMPLE | FORCED }
| RECOMPILE
| ROBUST PLAN
| KEEP PLAN
| KEEPFIXED PLAN
| EXPAND VIEWS
| MAXRECURSION number
| USE PLAN N'xml_plan'| TABLE HINT ( exposed_object_name [ , <table_hint> [ [, ]...n ] ] )<table_hint> ::=
[ NOEXPAND ] {
INDEX (index_value [ ,...n ] ) | INDEX = (index_value)
| FASTFIRSTROW
| FORCESEEK
| HOLDLOCK
| NOLOCK
| NOWAIT
| PAGLOCK
| READCOMMITTED
| READCOMMITTEDLOCK
| READPAST
| READUNCOMMITTED
| REPEATABLEREAD
| ROWLOCK
| SERIALIZABLE
| TABLOCK
| TABLOCKX
| UPDLOCK
| XLOCK
The starting point of all these query hints are option(query hint).
They all are used with 'select,update,delete ,insert and merge ' statement.They used after where caluse in query.
Query hints cannot be specified in an INSERT statement except when a SELECT clause is used inside the statement.
Query hints can be specified only in the top-level query, not in subqueries. When a table hint is specified as a query hint, the hint can be specified in the top-level query or in a subquery; however, the value specified for exposed_object_name in the TABLE HINT clause must match exactly the exposed name in the query or subquer
Example:
@p1=1, @p2=9998,
Select * from t where col > @p1 or col2 > @p2 order by col1
option (OPTIMIZE FOR (@p1 UNKNOWN, @p2 UNKNOWN))
similarly we can used other query hints in sql server 2008 .This is not avialable in sql server 2005.
Write left outer join in LINQ:
Ans:
from p in context.Periods
join f in context.Facts
on p.id equals f.periodid into
fg
from fgi in fg.Where(f => f.otherid == 17).DefaultIfEmpty()
where p.companyid == 100
select f.value
How To optimize data access and Query?
1:Use query hints as given above in select ,update and delete query .
Other techniques are given in below article:
http://www.codeproject.com/Articles/34372/Top-10-steps-to-optimize-data-access-in-SQL-Server
Q:Diffrence Between Index Scan and Index Seek.
Ans:
Index Scan:
Since a scan touches every row in the table whether or not it qualifies, the cost is proportional to the total number of rows in the table. Thus, a scan is an efficient strategy if the table is small or if most of the rows qualify for the predicate.
Index scan is same as table scan.They both are same.
Index Seek:
Since a seek only touches rows that qualify and pages that contain these qualifying rows, the cost is proportional to the number of qualifying rows and pages rather than to the total number of rows in the table.
Index Scan is nothing but scanning on the data pages from the first page to the last page. If there is an index on a table, and if the query is touching a larger amount of data, which means the query is retrieving more than 50 percent or 90 percent of the data, and then optimizer would just scan all the data pages to retrieve the data rows. If there is no index, then you might see a Table Scan (Index Scan) in the execution plan.
Index seeks are generally preferred for the highly selective queries. What that means is that the query is just requesting a fewer number of rows or just retrieving the other 10 (some documents says 15 percent) of the rows of the table.
In general query optimizer tries to use an Index Seek which means that optimizer has found a useful index to retrieve recordset. But if it is not able to do so either because there is no index or no useful indexes on the table then SQL Server has to scan all the records that satisfy query condition.
Q: How to Delete duplicate rows from a table
Ans:
If table contains unique column then use below query:
delete from tablename where srno not in(select max(srno) from sametable group by colname(duplicatecolname) )
If it does not contain any unique column then use below query:
Step1 :
select distinct * into newtablename from oldtablename
Step 2:
truncate table oldtablename
Step 3:
insert into oldtablename
select * from newtablename
Apart from these 2 solution we can also use the partition key with row_number() to get rid off duplicate columns.
Q:What is Replication.
Ans:
Replication is way of keeping data synchronized in multiple databases. SQL server replication
has two important aspects publisher and subscriber.
Publisher
Database server that makes data available for replication is known as Publisher.
SubscriberDatabase Servers that get data from the publishers is called as Subscribers.
There are three types of replication supported by SQL SERVER:-
Snapshot Replication.
Snapshot Replication takes snapshot of one database and moves it to the other database. After
initial load data can be refreshed periodically. The only disadvantage of this type of replication is
that all data has to be copied each time the table is refreshed.
Transactional Replication
In transactional replication, data is copied first time as in snapshot replication, but later only the
transactions are synchronized rather than replicating the whole database. You can either specify
to run continuously or on periodic basis.
Merge Replication.
Merge replication combines data from multiple sources into a single central database. Again as
usual, the initial load is like snapshot but later it allows change of data both on subscriber and
publisher, later when they come on-line it detects and combines them and updates accordingly
For More Information use this link
http://www.codeproject.com/Articles/215093/Replication-in-MS-SQL-Server
Q:Diffrence Between Function and SP?
ANS:
1:You cannot call sp from function except some special sp like extended sps,But you can call function from sp.
2:you cannot use transaction inside function but you can do that in sp .
3:You cannot do insert ,update,delete inside function but you can do that in sp.
4:You cannot create a temporary table inside a function but you can do that in sp.
5:You can only create table variable inside sp and function both.
6:Function can return tables but not sp.
7:Function always returns value but sp may or may not return value.
Q:What are the ACID Properties .
Q:What are is te diffrence between where clause and having clause?
Q:Can we update view with 2 tables?
Ans:yes
Q:Can we delete data from view when it has multiple tables?
Ans:No we will get error in that case.
Q:can we use order by and group by clause in view?
Ans:Yes
Q:Can we use transaction inside of trigger?
Ans: Yes we can .But if you do not use begin transaction then it will throw unexpected result on rollback like below:
Msg 50000, Level 16, State 2, Procedure trg_RestrictDEL, Line 10
Rows from Views Named: view_emp_data, CANNOT be DELETED!
Msg 3609, Level 16, State 1, Line 1
The transaction ended in the trigger. The batch has been aborted.
But if you have used begin transaction then it will throw proper error on rollback
CREATE TRIGGER trg_RestrictDEL ON view_emp_data
INSTEAD OF DELETE
AS
BEGIN
IF @@rowcount > 0
BEGIN
BEGIN TRAN
RAISERROR('Rows from Views Named: view_emp_data, CANNOT be DELETED!', 16, 2 )
ROLLBACK TRANSACTION
BEGIN TRANSACTION
END
END
GO
Msg 50000, Level 16, State 2, Procedure trg_RestrictDEL, Line 10
Rows from Views Named: view_emp_data, CANNOT be DELETED!
Q:Search Using Fulltext Index?
Ans:
There are four principal T-SQL functions which allow one to interact
with your Full-Text indices: CONTAINS, FREETEXT, CONTAINSTABLE and
FREETEXTTABLE. The first two return a boolean value, meaning you could
use them directly in a WHERE clause. The latter two return a two-column
table—KEY and RANK, allowing one to manage ranked searches.
In any case, the big thing to remember is that CONTAINS is far more
specific and much more configurable than FREETEXT. The former searches
using the parameters it is told, whereas the latter will split words,
find inflectional forms and perform other magics. Obviously, FREETEXT
is the more expensive of the two, and oftentimes it can be overkill.
SELECT Description FROM Production.ProductDescription WHERE
FREETEXT(Description, @SearchWord);
SELECT FT_TBL.Description
,KEY_TBL.RANK
FROM Production.ProductDescription AS FT_TBL
INNER JOIN FREETEXTTABLE(Production.ProductDescription,
Description,
'high level of performance') AS KEY_TBL
ON FT_TBL.ProductDescriptionID = KEY_TBL.[KEY]
ORDER BY RANK DESC
Using Contains with multiple Columns:
SELECT Name, Color FROM Production.Product
WHERE CONTAINS((Name, Color), 'Red');
With Single Column:
SELECT Name, Color FROM Production.Product
WHERE CONTAINS(Name, 'Red');
select * from tbltest where contains((Productname ,discription),'"r*"')
With Or Statement:
SELECT Name FROM Production.Product
WHERE CONTAINS(Name, ' Mountain OR Road ')
With * like in table:
SELECT Name FROM Production.Product
WHERE CONTAINS(Name, ' "Chain*" ');
http://technet.microsoft.com/en-us/library/ms187787.aspx
http://www.sitepoint.com/sql-server-full-text-search-protips-part-2-contains-vs-freetext/
Q:Set nocount on what is use of this.
Ans:
set nocount on
When SET NOCOUNT is ON, the count is not returned. When SET NOCOUNT is OFF, the count is returned.
The @@ROWCOUNT function is updated even when SET NOCOUNT is ON.
SET NOCOUNT ON prevents the sending of DONE_IN_PROC
messages to the client for each statement in a stored procedure. For
stored procedures that contain several statements that do not return
much actual data, or for procedures that contain Transact-SQL loops,
setting SET NOCOUNT to ON can provide a significant performance boost,
because network traffic is greatly reduced.
The setting specified by SET NOCOUNT is in effect at execute or run time and not at parse time.
Apart from this one more important thing you can do
Q: What is the use of @@rowcount
Ans:
select @@rowcount
Returns the number of rows affected by the last statement. If the number of rows is more than 2 billion, use ROWCOUNT_BIG.
UPDATE HumanResources.Employee
SET JobTitle = N'Executive'
WHERE NationalIDNumber = 123456789
IF @@ROWCOUNT = 0
PRINT 'Warning: No rows were updated';
Q:What is Referential Integrity?
Ans:
Referential integrity is a database concept that ensures that
relationships between tables remain consistent. When one table has a
foreign key to another table, the concept of referential integrity
states that you may not add a record to the table that contains the
foreign key unless there is a corresponding record in the linked table.
It also includes the techniques known as cascading update and cascading
delete, which ensure that changes made to the linked table are reflected
in the primary table.
Consider the situation where we have two tables: Employees and Managers.
The Employees table has a foreign key attribute entitled ManagedBy
which points to the record for that employee’s manager in the Managers
table. Referential integrity enforces the following three rules:
- We may not add a record to the Employees table unless the ManagedBy attribute points to a valid record in the Managers table.
- If the primary key for a record in the Managers table changes, all
corresponding records in the Employees table must be modified using a
cascading update.
- If a record in the Managers table is deleted, all corresponding
records in the Employees table must be deleted using a cascading delete.
Q:how to get 3 thrd largest salary without using subquery in where clause.
Ans:
select top 3 * from
(
select top 10 ROW_NUMBER() over(order by salary desc) as srno1,
salary from tbltest order by salary desc
) as a where a.srno1>7
Q:If we have multiple AFTER Triggers on table how can we define the
sequence of the triggers?
If a table has multiple AFTER triggers, then you can specify which trigger should be executed
first and which trigger should be executed last using the stored procedure sp_settriggerorder.
