What is the use of DBCC commands

DBCC stands for database consistency checker. We use these commands to check the consistency of the databases, i.e., maintenance, validation task and status checks
.E.g. DBCC CHECKDB - Ensures that tables in the db and the indexes are correctly linked.
DBCC CHECKALLOC - To check that all pages in a db are correctly allocated.
DBCC CHECKFILEGROUP - Checks all tables file group for any damage.

What is cursors

Cursor is a database object used by applications to manipulate data in a set on a row-by-row basis, instead of the typical SQL commands that operate on all the rows in the set at one time.
In order to work with a cursor we need to perform some steps in the following order:
1- Declare cursor
2- Open cursor
3- Fetch row from the cursor
4- Process fetched row
5- Close cursor
6- Deallocate cursor

What is the difference between clustered and a non-clustered index

A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages.
A nonclustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf node of a nonclustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.

What is @@ERROR

The @@ERROR automatic variable returns the error code of the last Transact-SQL statement. If there was no error, @@ERROR returns zero. Because @@ERROR is reset after each Transact-SQL statement, it must be saved to a variable if it is needed to process it further after checking it.

What is Raiseerror

Stored procedures report errors to client applications via the RAISERROR command. RAISERROR doesn’t change the flow of a procedure; it merely displays an error message, sets the @@ERROR automatic variable, and optionally writes the message to the SQL Server error log and the NT application event log.

What is Transaction

Mark the starting point of an explicit, local transaction. The concept of the transaction is to consider multiple tasks as a single unit. The transaction when completes, it either performs all of the tasks within it or none.
Thus a transaction is either implemented completely or is not implemented at all and has no intermediate state.
Syntax :
BEGIN TRAN[SACTION] [transaction [WITH MARK ['description'] ]
BEGIN TRAN[SACTION] [@transaction_var [WITH MARK ['description'] ]
Description ::
transaction :- A name for the transaction <= 32 characters.
@transaction_var :- A user-defined variable containing a transaction name.
WITH MARK :- Note the transaction in the log.
Transaction Type
1- Begin Tran
2- Commit Tran
3- Rollback Tran
Example ::
DECLARE @MyTranName VARCHAR(20);
SET @MyTranName = 'deleteTran';
BEGIN TRANSACTION @MyTranName
DELETE FROM emp WHERE empcode = 123;
COMMIT TRANSACTION @MyTranName
Note :: @@TRANCOUNT = Count Number of transaction is open.

count of the number of records in a table

1. SELECT COUNT(*) FROM tableName
2. SELECT rows FROM sysindexes WHERE id = OBJECT_ID(tableName) AND indid < 2

Advantages of Stored Procedure.

• Stored procedure can reduced network traffic and latency, boosting application performance.
• Stored procedure execution plans can be reused, staying cached in SQL Server’s memory, reducing server overhead.
• Stored procedures help promote code reuse.
• Stored procedures can encapsulate logic. You can change stored procedure code without affecting clients.
• Stored procedures provide better security to your data.

What is the use of COALESCE ?

If you have NULLable columns, you must use COALESCE or ISNULL function to convert the NULL to some comparable value, before you can compare a NULL and non-NULL value. T-SQL's COALESCE() function lets you specify a number of expressions and COALESCE() will return the first non-null value from the group.
Use the following form where expression is any data type and can equal most any valid expression
COALESCE (expression [,…n])
The function doesn't limit the number of arguments, but they must all be of the same data type; the function returns the same data type as expression.
When all arguments are NULL, COALESCE() returns NULL.
SELECT LName, FName, COALESCE (Reg, Country) AS Area FROM Employees
ORDER BY LName, FName
NOTE:: If the Reg value is null, COALESCE() returns the corresponding Country value instead.

case sensitive searches in SQL Server

Q: How to perform case sensitive searches in SQL Server?
A: Method 1: Converting data to binary type before comparison .
SELECT 1 FROM dbo.Customers
WHERE CAST(CustID AS varbinary(8)) = CAST(@CustID AS varbinary(8))
AND CAST(CustPassword AS varbinary(15)) = CAST(@CustPassword AS varbinary(15))

Method 2: Using the COLLATE clause to dictate the case sensitiveness of the query.
IF EXISTS (SELECT 1 FROM dbo.Customers WHERE CustID = @CustID COLLATE SQL_Latin1_General_CP1_CS_AS AND CustPassword = @CustPassword COLLATE SQL_Latin1_General_CP1_CS_AS )

Method 3: Using BINARY_CHECKSUM function.
SELECT 1 FROM dbo.Customers
WHERE BINARY_CHECKSUM(CustID) = BINARY_CHECKSUM(@CustID) and BINARY_CHECKSUM(CustPassword) = BINARY_CHECKSUM(@CustPassword)

Insertion Queries

Q: Insert Value in one table from another table ?
A: INSERT INTO insertion_tableName SELECT * FROM Existing_tableName
Note:: Be careful with column Name .
Q: Give me an Example of INSERT multiple value with using UNION ?
A: Example :
INSERT INTO USER_DETAIL(UID, FNAME, LNAME, EMAILID)
SELECT 1,'Navneeth','Naik','navneeth@kdmnn.com'
UNION
SELECT 2,'Amol','Kulkarni','amol@kdmnn.com'
UNION
SELECT 3,'Anil','Bahirat','anil@kdmnn.com'
UNION
SELECT 4,'Murthy','Belluri','murthy@kdmnn.com'

How many types to create New Table

Q: Throught script to create table?
A: CREATE TABLE Tablename
(
ColumnName1 DATATYPE,
ColumnName2 DATATYPE,
..........
)
Q: Create Table from Existing Table with data ?
A: SELECT * INTO New_ TableName FROM Exists_TableName

Q: Create Table from Existing Table without data ?
A: SELECT * INTO New_TableName FROM Existing_Tablename where 1=2