Use "With" clause to improve performance of your Query

WITH studentsM AS (
SELECT * FROM StudentsMaster
)
SELECT * FROM studentsM

if 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 CORRECT

In 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 from that Name (above select query) means you need not to create a temporary table you just write any large sql query and give it name using with and later (in next of after next statement) we can query on it... Very Goood no..? It will improve your query Speed very much..

Physical Database Files and Filegroups

SQL Server 2000 databases have three types of files:

• Primary data files

  The 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 files

  Secondary 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 file name extension for secondary data files is .ndf.

• Log files

  Log files hold all of the log information used to recover the database. There must be at least one log file for each database, although there can be more than one. The recommended file name extension for log files is .ldf.

SQL Server 2000 does not enforce the .mdf, .ndf, and .ldf file name extensions, but these extensions are recommended to help identify the use of the file.

In SQL Server 2000, the locations of all the files in a database are recorded in both the master database and the primary file for the database. Most of the time the database engine uses the file location information from the master database. For some operations, however, the database engine uses the file location information from the primary file to initialize the file location entries in the master database:

• When attaching a database using the sp_attach_db system stored procedure.
  
  
• When upgrading from SQL Server version 7.0 to SQL Server 2000.
  
  
• When restoring the master database.

SQL Server 2000 files have two names:

• logical_file_name is a name used to refer to the file in all Transact-SQL statements.

  The logical file name must conform to the rules for SQL Server identifiers and must be unique to the database.

• os_file_name is the name of the physical file.

  It must follow the rules for Microsoft Windows NT® or Microsoft Windows Me, and Microsoft Windows 98 file names.

Trigger FOR DELETE Operation

when we want to create a trigger for delete operation on any table we can do it as shown below

and 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,

)

GO

insert storesmaster values('1','B','567 Ave.','Tustin')

insert storesmaster values('2','N','577 St.', 'Los Gatos')

insert storesmaster values('3','T','679 St.', 'Portland')

insert storesmaster values('4','F','89 St.', 'Fremont')

GO

CREATE TABLE discounts(

discounttype varchar(40) NOT NULL,

stor_id char(4) NULL ,

lowqty smallint NULL,

highqty smallint NULL,

discount dec(4,2) NOT NULL

)

GO

insert discounts values('Initial Customer', NULL, NULL, NULL, 10.5)

insert discounts values('Volume Discount', NULL, 100, 1000, 6.7)

insert discounts values('Customer Discount', '8042', NULL, NULL, 5.0)

GO

CREATE TRIGGER myTrigger ON stores

FOR DELETE

AS

DECLARE @intRowCount int

SELECT @intRowCount = @@RowCount

IF @intRowCount > 0

BEGIN

DELETE sales

WHERE stor_id IN (SELECT stor_id FROM deleted)

DELETE discounts

WHERE stor_id IN (SELECT stor_id FROM deleted)

END

GO

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 salary

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 query.
Eg.
SELECT SelectList FROM SomeTable WHERE SomeColumn = (SELECT SingleColumn FROM SomeTable WHERE Condition that results in only one row returned)

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 transaction.

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 = tablename