56) Why to use “No Lock” in Sql Server?
“No Lock” is used for unlocking the rows which are locked by some other transaction. Once after the rows are committed or rolled back no need to use No Lock. For example
SELECT * from Employees WITH(NOLOCK)
- master - This database will have data and catalog of all the databases of SQL Server instance.
- tempdb - tempdb database will have temporary objects like local and global temporary tables and stored procedures as well.
- model - model database is mainly used for creating new user databases.
Unique Identifier datatype mainly used for primary key columns of the tables or any other columns which need to have unique Ids. “NEWID()” function can be used for generating unique identifier for the column. Unique Identifiers are also named as GUIDs.
59) Why to use “PIVOT” in Sql Server?
Pivot table automatically count, sort and total the data in a table or spreadsheet and used to create a separate table for displaying summarized data.
60) Explain Alternate key, Candidate Key and Composite Key in Sql Server?
- Alternate Key – To identity a row uniquely we can have multiple keys one of them is called primary key and rest of them are called alternate keys.
- Candidate Key – Set of fields or columns which are uniquely identified in a row and they constitute candidate keys.
- Composite Key – One key formed by combining at least two or more columns or fields.
61) How to use “BETWEEN” operator in Sql Server?
“BETWEEN” operator is used for selecting the range of values. For example
SELECT * FROM Employees WHERE EmpSalary BETWEEN 10000 AND 20000
62) How to use “DROP” keyword in Sql Server and Give an example.
“DROP” keyword is used to drop either Index or database or table. Below are list of Sql statements using Drop keyword.
- Dropping Index
- DROP INDEX my_index
- Dropping Database
- DROP DATABASE my_database
- Dropping Table
- DROP TABLE my_table
63) List out all Null functions in Sql Server?
Below are the list of Null functions in Sql –
64) What are the differences between ISNULL() and COALESCE() in Sql Server?
ISNULL() – This function is used to replace the given value in case of NULL value. For example
SELECT ISNULL(@myvar, ‘replacetext’)
COALESCE() – This function will return the first non-null expression given in the list of expressions. Advantage of this function over “ISNULL()” is it takes more than two inputs for checking non null expression unlike ISNULL() which takes only two inputs. For example
SELECT COALESCE(@firvar, @secondvar, @thirdvar);
65) Give list of Scalar and Aggregate functions of Sql Server?
- MID() - Extracting characters from a string.
- LEN() – Get the length of string.
- ROUND() – Rounding the number.
- UCASE() - Change it to upper case.
- LCASE() - Change it to lower case etc.
- AVG() – Returns the average value.
- MAX() – Returns maximum value.
- MIN() - Returns minimum value.
- COUNT() - Returns count of rows.
- SUM() – Returns sum value etc.
66) Give list of Date functions of Sql Server?
- GETDATE() - Get current data and time.
- DATEDIFF() – Get time between two dates.
- DATEPART() – Get single part of Datetime.
- DATEADD() – Adding time interval from Date.
- CONVERT() – Display date in different formats.