 |
T-Sql Interview Questions
|
T-SQL Interview Questions
1) what is T-SQL?
T-SQL stands for
Transact Structured Query Language. It is an extension of SQL functionality
supported by Microsoft SQL Server and Sybase ASE.
2) Mention what is the difference between SQL
and T-SQL?
The difference between SQL
and TSQL is that SQL is a query language to operate on sets, while TSQL is a
proprietary procedural language used by MS SQL Server. Also, T-SQL has a
different implementation of DELETE and UPDATE than SQL.
3) Mention how tsql statements can be written
and submitted to the Database engine?
Tsql statements can be
written and submitted to the Database engine in following ways,
·
By
using the SQLcmd Utility
·
By
using the SQL Server Management Studio
·
By
connecting from an application that you create
4) Mention what is “GO” in T-SQL?
“GO” is not a Transact-SQL
statement but a batch separator. It is a command identified by the sqlcmd and
osql utilities and SQL Server Management Studio Code editor. SQL Server
utilities read “GO” as a signal that they should send the current batch of TSQL
statements to an instance of SQL Server.
5) Mention what is the difference between
TRUNCATE and DELETE statement?
The difference between
TRUNCATE and DELETE statement is that,
·
TRUNCATE
is used for unconditional removal of data records from Tables. Truncate
Operations are not logged.
·
DELETE
is used for conditional removal of data records from Tables. These operations
are logged.
6) Mention how does a local variable is
defined using T-SQL?
A local variable is defined
using TSQL by using statement “DECLARE” and the name of the local variable
should begin with “@” sign as the first character of its name. For example,
integer CNT we will define local variable as,
DECLARE @CNT INT
7) Mention what does the T-SQL command
IDENT_CURRENT does?
The TSQL command IDENT_CURRENT
returns the last identity value produced for a specified table or view.
The last identity value created can be for any session and any scope.
8) Mention what does the T-SQL command
IDENT_INCR does?
TSQL command IDENT_INCR
returns the increment value mentioned during the formation of an identity
column in a table or view that has an identity column.
9) Mention if it is possible to import data
directly from T-SQL commands without using SQL Server Integration Services? If
yes, what are the commands?
Yes, it is possible to
import data directly from T-SQL commands without using SQL Server Integration
Services. These commands include,
·
BCP
·
OpenRowSet
·
Bulk
Insert
·
OPENQUERY
·
OPENDATASOURCE
·
Linked
Servers
10) Mention what is sub-query?
A sub-query is used to
return data that will be used in the main query as a condition to further
restrict the data to be retrieved.
A sub-query can be used
with the statements like Update, select, delete and insert with the operators
like =, >, <, >=,<=, etc.
11) Mention what are dynamic queries in
T-SQL?
Dynamic queries in T-SQL are those queries
designed on the fly/ at run time using variables or using CTE or other
sources. We use EXECUTE function or SP_EXECUTESQLStored
Procedure to execute such queries.
12) Mention what are ROLLUP and CUBE in
T-SQL?
Rollup and cube are the
grouping sets used along with GROUP BY clause to generate summarized
aggregations. These are mainly used for Data Audits and Report Generation.
13) Mention what are the maximum number of
rows that can be constructed by inserting rows directly in VALUE list?
The maximum number of rows
that can be constructed by inserting rows directly in VALUE list is 1000.
14) Mention what is TOP in TSQL?
TOP limits the rows
returned in a query result set to a specified number of rows or percentage of
rows in SQL Server. When TOP is used in combination with the ORDERBY clause,
the result set is limited to the first N number of ordered rows. Otherwise, it
retrieves the first N number of rows in an undefined order.
15) Mention what are the Join Types in TSQL?
Join Types in TSQL are,
·
Inner
join
·
Outer
join
·
Left
outer join
·
Right
outer join
·
Left
outer join with Exclusions
·
Right
outer join with Exclusions
·
Full
outer join
·
Full
outer joins with Exclusions
·
Cross
join
16) Mention what are the T String functions
available in TSQL?
T String functions
available in TSQL are,
·
Left
·
Right
·
Ltrim
·
Rtrim
·
Substring
·
Replace
·
Stuff
17) Mention what is the syntax used for
partition in TSQL?
In TSQL, the syntax used
for partition is,
[ database_name. ]
$PARTITION.partition_function_name(expression)
18) Mention what is the syntax for using
SQL_Variant_Property?
The syntax for using
SQL_Variant_Property is,
SQL_Variant_Property
(expression, property)
19) Mention what is OFFSET-FETCH filter in
tsql?
In tsql OFFSET-FETCH filter
is designed similar to TOP but with an extra element. It helps to define
how many rows you want to skip before specifying how many rows you want to
filter.
20) Mention what is uncommittable
transactions?
When an error occurs in a
transaction within a TRY block, and if the error is not serious it enters into
a status open and Uncommittable. In uncommittable state, the transactions
cannot perform any action that would generate a write to the transaction log.
21) Mention what is Sp_pkeys?
Sp_pkeys is part of
Catalog Stored Procedures and returns primary key information for a single
table in the current database.
Sytax for Sp_pkeys is sp_pkeys [
@table_name = ‘Tbale name.’ ]
22) Mention how to do backup entire database?
To backup entire database,
use following syntax;
BACKUP DATABASE {
database_name }
TO backup_device [ ,…n ]
[ MIRROR TO clause ]
[ WITH { DIFFERENTIAL | [
,…n ] } ];
23) Mention what are the limitations of
IDENTITY column?
The limitations of the
IDENTITY column is that column values cannot be updated once generated. Also,
it may require to specify this column as a PRIMARY KEY, as such, there is a
possibility of duplication of values within a table. Identity property is
applicable for integer based column only.
24) Mention what is the use of SET statement
in TSQL?
In TSQL, SET statement
allows you to change the current session handling of specific information like:
system language, dateformat, lock timeout, rowcount and so on.
