OpenText Most Frequently Asked Latest SQL Server Interview Questions Answers
What is the difference between SUBSTR and CHARINDEX in the SQL Server?
The SUBSTR function is used to return specific portion of string in a given string. But, INSTR function gives character position in a given specified string.
SUBSTR(“Smiley”,3)
1
SUBSTR(“Smiley”,3)
Gives result as Smi
CHARINDEX(“Smiley”,’i’,1)
1
CHARINDEX(“Smiley”,’i’,1)
Gives 3 as result as I appears in 3rd position of the string
What will be the maximum number of index per table?
For SQL Server 2008 100 Index can be used as maximum number per table. 1 Clustered Index and 999 Non-clustered indexes per table can be used in SQL Server.
1000 Index can be used as maximum number per table. 1 Clustered Index and 999 Non-clustered indexes per table can be used in SQL Server.
1 Clustered Index and 999 Non-clustered indexes per table can be used in SQL Server.
What is the difference between COMMIT and ROLLBACK?
Every statement between BEGIN and COMMIT becomes persistent to database when the COMMIT is executed. Every statement between BEGIN and ROOLBACK are reverted to the state when the ROLLBACK was executed.
What is the difference between varchar and nvarchar types?
Varchar and nvarchar are same but the only difference is that nvarhcar can be used to store Unicode characters for multiple languages and it also takes more space when compared with varchar.
What is the use of @@SPID?
A @@SPID returns the session ID of the current user process.
What is TABLESAMPLE?
TABLESAMPLE is used to extract sample of rows randomly that are all necessary for the application. The sample rows taken are based on the percentage of rows.
Which command is used for user defined error messages?
RAISEERROR is the command used to generate and initiates error processing for a given session. Those user defined messages are stored in sys.messages table.
What do mean by XML Datatype?
XML data type is used to store XML documents in the SQL Server database. Columns and variables are created and store XML instances in the database.
What is CDC?
CDC is abbreviated as Change Data Capture which is used to capture the data that has been changed recently. This feature is present in SQL Server 2008.
What is the command used to Recompile the stored procedure at run time?
Stored Procedure can be executed with the help of keyword called RECOMPILE.
Example
Exe <SPName> WITH RECOMPILE
Exe <SPName> WITH RECOMPILE
Or we can include WITHRECOMPILE in the stored procedure itself.
How to delete duplicate rows in SQL Server?
Duplicate rows can be deleted using CTE and ROW NUMER feature of SQL Server.
Where are SQL Server user names and passwords stored in SQL Server?
User Names and Passwords are stored in sys.server_principals and sys.sql_logins. But passwords are not stored in normal text.
What is the difference between GETDATE and SYSDATETIME?
Both are same but GETDATE can give time till milliseconds and SYSDATETIME can give precision till nanoseconds. SYSDATE TIME is more accurate than GETDATE.
How data can be copied from one table to another table?
INSERT INTO SELECT
This command is used to insert data into a table which is already created.
SELECT INTO
This command is used to create a new table and its structure and data can be copied from existing table.
What is SQL injection?
SQL injection is an attack by malicious users in which malicious code can be inserted into strings that can be passed to an instance of SQL server for parsing and execution. All statements have to checked for vulnerabilities as it executes all syntactically valid queries that it receives.
Even parameters can be manipulated by the skilled and experienced attackers.
How can we get count of the number of records in a table?
Following are the queries can be used to get the count of records in a table –
Select * from <tablename> Select count(*) from <tablename> Select rows from sysindexes where id=OBJECT_ID(tablename) and indid<2
Select * from <tablename> Select count(*) from <tablename> Select rows from sysindexes where id=OBJECT_ID(tablename) and indid<2
What is an IDENTITY column in insert statements?
IDENTITY column is used in table columns to make that column as Auto incremental number or a surrogate key.
What is Bulkcopy in SQL?
Bulkcopy is a tool used to copy large amount of data from Tables. This tool is used to load large amount of data in SQL Server.
What will be query used to get the list of triggers in a database?
Query to get the list of triggers in database-
Select * from sys.objects where type=’tr’
Select * from sys.objects where type=’tr’.
What are the types of Triggers?
There are four types of triggers and they are:
Insert
Delete
Update
Instead of
What is Collation?
Collation is defined to specify the sort order in a table. There are three types of sort order –
Case sensitive
Case Insensitive
Binary
What is the difference between UNION and UNION ALL?
• UNION: To select related information from two tables UNION command is used. It is similar to JOIN command.
• UNION All: The UNION ALL command is equal to the UNION command, except that UNION ALL selects all values. It will not remove duplicate rows, instead it will retrieve all rows from all tables.
What is the difference between SUBSTR and CHARINDEX in the SQL Server?
The SUBSTR function is used to return specific portion of string in a given string. But, INSTR function gives character position in a given specified string.
SUBSTR(“Smiley”,3)
1
SUBSTR(“Smiley”,3)
Gives result as Smi
CHARINDEX(“Smiley”,’i’,1)
1
CHARINDEX(“Smiley”,’i’,1)
Gives 3 as result as I appears in 3rd position of the string
What will be the maximum number of index per table?
For SQL Server 2008 100 Index can be used as maximum number per table. 1 Clustered Index and 999 Non-clustered indexes per table can be used in SQL Server.
1000 Index can be used as maximum number per table. 1 Clustered Index and 999 Non-clustered indexes per table can be used in SQL Server.
1 Clustered Index and 999 Non-clustered indexes per table can be used in SQL Server.
What is the difference between COMMIT and ROLLBACK?
Every statement between BEGIN and COMMIT becomes persistent to database when the COMMIT is executed. Every statement between BEGIN and ROOLBACK are reverted to the state when the ROLLBACK was executed.
 |
| OpenText Most Frequently Asked Latest SQL Server Interview Questions Answers |
What is the difference between varchar and nvarchar types?
Varchar and nvarchar are same but the only difference is that nvarhcar can be used to store Unicode characters for multiple languages and it also takes more space when compared with varchar.
What is the use of @@SPID?
A @@SPID returns the session ID of the current user process.
What is TABLESAMPLE?
TABLESAMPLE is used to extract sample of rows randomly that are all necessary for the application. The sample rows taken are based on the percentage of rows.
Which command is used for user defined error messages?
RAISEERROR is the command used to generate and initiates error processing for a given session. Those user defined messages are stored in sys.messages table.
What do mean by XML Datatype?
XML data type is used to store XML documents in the SQL Server database. Columns and variables are created and store XML instances in the database.
What is CDC?
CDC is abbreviated as Change Data Capture which is used to capture the data that has been changed recently. This feature is present in SQL Server 2008.
What is the command used to Recompile the stored procedure at run time?
Stored Procedure can be executed with the help of keyword called RECOMPILE.
Example
Exe <SPName> WITH RECOMPILE
Exe <SPName> WITH RECOMPILE
Or we can include WITHRECOMPILE in the stored procedure itself.
How to delete duplicate rows in SQL Server?
Duplicate rows can be deleted using CTE and ROW NUMER feature of SQL Server.
Where are SQL Server user names and passwords stored in SQL Server?
User Names and Passwords are stored in sys.server_principals and sys.sql_logins. But passwords are not stored in normal text.
What is the difference between GETDATE and SYSDATETIME?
Both are same but GETDATE can give time till milliseconds and SYSDATETIME can give precision till nanoseconds. SYSDATE TIME is more accurate than GETDATE.
How data can be copied from one table to another table?
INSERT INTO SELECT
This command is used to insert data into a table which is already created.
SELECT INTO
This command is used to create a new table and its structure and data can be copied from existing table.
SQL injection is an attack by malicious users in which malicious code can be inserted into strings that can be passed to an instance of SQL server for parsing and execution. All statements have to checked for vulnerabilities as it executes all syntactically valid queries that it receives.
Even parameters can be manipulated by the skilled and experienced attackers.
How can we get count of the number of records in a table?
Following are the queries can be used to get the count of records in a table –
Select * from <tablename> Select count(*) from <tablename> Select rows from sysindexes where id=OBJECT_ID(tablename) and indid<2
Select * from <tablename> Select count(*) from <tablename> Select rows from sysindexes where id=OBJECT_ID(tablename) and indid<2
What is an IDENTITY column in insert statements?
IDENTITY column is used in table columns to make that column as Auto incremental number or a surrogate key.
What is Bulkcopy in SQL?
Bulkcopy is a tool used to copy large amount of data from Tables. This tool is used to load large amount of data in SQL Server.
What will be query used to get the list of triggers in a database?
Query to get the list of triggers in database-
Select * from sys.objects where type=’tr’
Select * from sys.objects where type=’tr’.
What are the types of Triggers?
There are four types of triggers and they are:
Insert
Delete
Update
Instead of
What is Collation?
Collation is defined to specify the sort order in a table. There are three types of sort order –
Case sensitive
Case Insensitive
Binary
What is the difference between UNION and UNION ALL?
• UNION: To select related information from two tables UNION command is used. It is similar to JOIN command.
• UNION All: The UNION ALL command is equal to the UNION command, except that UNION ALL selects all values. It will not remove duplicate rows, instead it will retrieve all rows from all tables.