Difference between Truncate And Drop

TRUNCATE TABLE:

TRUNCATE will reset any identity columns to the default seed value. This means if you have a table with an identity column and you have 264 rows with a seed value of 1, your last record will have the value 264 (assuming you started with value 1) in its identity columns. After TRUNCATEing your table, when you insert a new record into the empty table, the identity column will have a value of 1. DELETE will not do this.

Where SQL Server Actually Store Data

SQL server by default stores the data in the files with extensions .MDF and .LDF under the path 
"C:\Program Files\Microsoft SQL Server\MSSQL\Data\"

What are the Global Temporary Tables

We can create global temporary tables but these are not using much in sql an the name of these table start with two pound signs. For example, ##interviewqsn is a global temporary table.As the name suggest these table is Global temporary tables and visible to all SQL Server connections. When we create any one of these all users can see it.

How can you raise custom errors from stored procedure ?

The RAISERROR statement is used to produce an ad hoc error message or to retrieve a
custom message that is stored in the sysmessages table. You can use this statement with
the error handling code presented in the previous section to implement custom error

How can you increase SQL performance ?

Following are tips which will increase your SQl performance :-


√ Every index increases the time in takes to perform INSERTS, UPDATES and
DELETES, so the number of indexes should not be very much. Try to use
maximum 4-5 indexes on one table, not more. If you have read-only table,
then the number of indexes may be increased.

What is normalization? What are different type of normalization?

It is set of rules that has been established to aid in the design of tables that are meant to
be connected through relationships. This set of rules is known as Normalization.

how to get second max salary in sql

select min(sal) as sal from emp where sal   in 
(select distinct top 2 sal from emp with (nolock) order by sal desc ) 


---------------------------------

how to get the column names and corresponding datatypes in particular table using SQL Server

SELECT column_name 'Column Name',

data_type 'Data Type',

character_maximum_length 'Maximum Length'

FROM information_schema.columns

SQL Union Operator | SQL Union ALL Operator | Difference between Union and Union ALL Operators in SQL |

Union Operators are used to combine the result of two or more select queries into single result set.

SQL UNION Operator:

SQL Union Operator is used to combine the result of two or more select statement queries into single result set. The Union Operator is used to select only distinct values from two tables.

SQL SERVER Ranking Functions - RANK, DENSE_RANK, NTILE, ROW_NUMBER SQL query to delete duplicate rows SQL Server CTE(Common Table Expression) and Recursive Queries CTE Recursive query for data hierarchy(Parent Child hierarchy) Different methods of SQL queries to insert data in tables SQL query to display all columns with datatypes for a given Table name SQL query to check two tables have identical data SQL query to search a string in database Schema SQL query to display total number of rows for each table in database SQL query to delete duplicate rows

Create a table EmpDtl1 with some duplicate rows as shown below to to understand different methods of delete duplicate rows

create table EmpDup(empid int,name varchar(20))

How To Delete a null record

The code below will show you how exactly you delete a row with a NULL value. You can not use =NULL but you have to use IS NULL

CREATE TABLE #TestDeleteNull (id INT identity, SomeDate DATETIME)
INSERT #TestDeleteNull VALUES(GETDATE())

Delete Duplicate rows from the table.

Delete Duplicate rows from the table.
Suppose there is a table called "EmployeeTable" which have some duplicate records.
There is a three way to delete the duplicate rows.First way to delete duplicate rows :

How To Copy Data and Structure of One Table To Another New Tables

select * into newtable from originaltable where 1=1

Write a SQL Query to find first day of month?

SELECT DATENAME(dw, DATEADD(dd, - DATEPART(dd, GETDATE()) + 1, GETDATE())) AS FirstDay

Write a query to convert all the letters in a word to upper case

SELECT UPPER('test')

Write a query to round up the values of a number. For example even if the user enters 7.1 it should be rounded up to 8.

Using SQL Data Definition Language (DDL) to Create Data Tables and Other Database Objects

Using the CREATE TABLE Statement to Create Tables

Tables are the primary structures used to hold data in a relational database. In a typical multi-user environment, the database administrator (dba) creates the tables that serve as the data stores for the organization's data. Users normally create their own temporary tables used to store data extracted from the main organizational tables.

SQL Server Integration Services

   Maintain DTS packages in SQL Server 2005

What if you've migrated to SQL Server 2005 and want to hold off upgrading your DTS packages to SSIS? In this tip, you'll see how to edit, maintain and develop DTS packages in SQL Server.

Development

   Basics for working with DATETIME and SMALLDATETIME in SQL Server 2005

Understanding and working with date/time data types in SQL Server can be complicated. Learn the basics of working with DATETIME and SMALLDATETIME in SQL Server 2005, along with an overview of TIMESTAMP, a data type often confused with these two primary date/time data types.

security

   Granting permissions in SQL Server 2005

New security features in SQL Server 2005 make it easier to manage and grant SQL Server permissions on a more granular basis. Get an an overview of user-schema separation and granular server permissions, as well as a new function to help you identify which permissions are available to particular users.

Backup and recovery

   Selecting a SQL Server recovery model

Your SQL Server offers three recovery models for each database: full recovery, simple recovery and bulk-logged recovery. These determine how much data loss is acceptable in case of a failure, along with what types of backup and restore functions are allowed. Find out which option will suit you best when you are attempting to select a SQL Server recovery model.