Use "With" clause to improve performance of your Query

WITH studentsM AS (SELECT * FROM StudentsMaster)SELECT * FROM studentsMif you are looking first time "With" clause in SQL then you will think that here studentsM is a temporary table and in next line "SELECT * FROM studentsM" we are retriving values from temporary table.BUT ITS NOT CORRECTIn real "With" clause is very useful to improve performance of your query.How: in our first statement its not crating Temporary table. It just giving name to select query and in second statement we are querying...

Physical Database Files and Filegroups

SQL Server 2000 databases have three types of files:Primary data filesThe primary data file is the starting point of the database and points to the other files in the database. Every database has one primary data file. The recommended file name extension for primary data files is .mdf.Secondary data filesSecondary data files comprise all of the data files other than the primary data file. Some databases may not have any secondary data files, while others have multiple secondary data files. The recommended...

Trigger FOR DELETE Operation

when we want to create a trigger for delete operation on any table we can do it as shown belowand by useing "FROM deleted" as we did in our example ("SELECT stor_id FROM deleted") we can access the ID of record which just deleted and trigger got fired.CREATE TABLE storesmaster( stor_id char(4) NOT NULL, stor_name varchar(40) NULL, stor_address varchar(40) NULL, city varchar(20) NULL, ) GOinsert storesmaster values('1','B','567...

How to find nth highest salary from Employee table in SQL Server?

SELECT TOP 1 salary FROM (SELECT DISTINCT TOP n salary FROM employee ORDER BY salary DESC) a ORDER BY sal...

What is a Subquery ?

A Subquery is a normal T-SQL query that is nested inside another query. They are created using parentheses when you have a SELECT statement that serve as the basis for the either part of the data or the condition in another query.Subqueries are generally used to fill one of couple of needs -1. Break a query up into a series of a logical steps.2. Provide a listing to be the target of a WHERE clause together with [IN|ESISTS|ANY|ALL].3. TO provide a lookup driven by each individual record in a parent...

What is the DEADLOCK ?

A deadlock is a situation in which two transactions conflict with each other and the only resolution is to cancel one transacti...

How to find out column names and their datatypes in a given table using a query in SQL Server?

Using the table tablename, write the query as follows -Select * from information_schema.columns where table_name = tablen...