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.

What are triggers? How to invoke a trigger on demand?

               Triggers are special kind of stored procedures that get executed automatically when an INSERT, UPDATE or DELETE operation takes place on a table. . It can be used to maintain referential integrity. A trigger can call stored procedure.

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 are different types of joins and whats the difference between them ?

INNER JOIN
Inner join shows matches only when they exist in both tables.Example in the below SQL
there are two tables Customers and Orders and the inner join in made on Customers

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.

what is stored procedure in Sql server | what are the advantages of using stored procedures in sql server

A stored procedure is a group of sql statements that has been created and stored in the database. Stored procedure will accept input parameters so that a single procedure can be used over the network by several clients using different input data. Stored procedure will reduce network traffic and increase the performance. If we modify stored procedure all the clients will get the updated stored procedure

Different Types of Indexes in SQL Server | Difference between Clustered Indexes and Non-Clustered Indexes in SQL Server

An index can be created in a table to increase the performance of application and we can get the data more quickly and efficiently. Let’s see an example to illustrate this point suppose now we are reading book in that I need to check the information for dbmanagement to get this information I need to search each page of the book because I don’t know in which page that word

Cursors in SQL Server

Cursors can be considers as named result sets which allow a user to move through each record one by one. SQL Server 2000 provides different types of cursors to support different type of scrolling options.  

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.