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