1) What is T-SQL?
T-SQL stands for Transact Structured Query Language. T-SQL is the extension of SQL functionality supported by Microsoft SQL Server and Sybase ASE.
2) What are the features of T-SQL?
T-SQL makes enhancement in SQL server and provides the following features:
- TRUNCATE TABLE with PARTATION.
- DROP IF EXISTS.
- Advanced Analytics Extensions.
- Querying Temporal Tables
- JSON support.
- FORMATMESSAGE
3) What is difference between T-SQL and SQL?
- T-SQL gives different implementation of DELETE and UPDATE than SQL.
- T-SQL is best if it use Microsoft SQL servers.
- T-SQL supports local variable, procedural programming but, SQL does not.
- T-SQL is proprietary control under Microsoft while SQL is an open format.
4) What are the different data types in T-SQL?
T-SQL has different categories of data types:
Integer types (bigint, int, smallint, tinyint, bit,decimal,numeric,money,smallmoney).
Floating types (Float, Real).
Character Strings (char, varchar, Varchar(max), text)
Unicode Charcter Strings (nchar, nvarchar, Nvarchar(max), ntext)
Date and Time types (datetime, smalldatetime, date, datetimeoffset, datetime2, time).
Binary Strings (binary, varbinary, varbinary(max), image).
Others (sql_variant, timestamp, uniqueidentifier, xml, cursor, table, hierarchyid).
5) How can you execute a T-SQL query from command prompt?
6) What do you understand about Index in TSQL?
Index is special lookup used to retrieve data from database very fast. CREATE INDEX statement is used to create an index. Index is not visible to user. Creating and deleting of INDEX dose not effects on data.
Syntax:
CREATE INDEX index_name ON table_name;
7) How local variable is defined in T-SQL?
Local variable is defined by using “DECLARE†statement and the name of local variable must start with "@" character in T-SQL.
Syntax:
DECLARE @variable_name data_type;
8) What is "GO" in T-SQL?
GO is not a T-SQL statement, it signs the end of batch of T-SQL statement. It is a command recognized by the
sqlcmd and
osql utilities and SQL Server Management Studio Code Editor.
9) What is dynamic SQL statement?
A dynamic SQL statement is created at execution time. Dynamic SQL statement is useful when you need to decide at run time what field to be chosen with different criteria of queries.
Syntax:
DECLARE @WhereClause NVARCHAR(2000)
SET @WhereClause = ' Prouct = ''Computer'''
SELECT * FROM SalesHistory WHERE @WhereClause
Example:
DECLARE @Product VARCHAR(20)
SET @Product = 'Computer'
SELECT * FROM SalesHistory WHERE Product = @Product
10) What is the difference between TRUNCATE and DELETE statement?
| DELETE |
TRUNCATE |
| DELETE statement is used to remove data record with condition clause. |
TRUNCATE statement is used to remove all data records from table without any condition. |
| DELETE is slower. |
TRUNCATE is faster. |
| DELETE is DML statement. |
TRANCATE is DDL statement. |
11) What is NULL and UNKNOWN in T-SQL?
NULL is a value which is unknown. A null value is different from zero or an empty value. The comparison between two null values returns unknown because the value of each null is unknown.
12) What is @@ERROR in T-SQL?
@@ERROR returns the error number of last T-SQL statement executed.
It returns 0 if the last executed statement has no error.
13) What are the Join Types in TSQL?
TSQL has the following join types:
- 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
14) What is transaction Auto commit?
Auto commit is a default transaction management mode. Every statement is committed or rolled back when complete. When SQL statement successfully completed it is committed otherwise it is rolled back.
15) What is sub-query in TSQL?
Sub Query is a query that is nested inside another query. A sub query returns data which is further used in main query as a condition to generate final result. The sub query executed before the parent query.
Syntax:
FROM table1 [, table2 ]
WHERE column_name OPERATOR
(SELECT column_name [, column_name ]
FROM table1 [, table2 ] [WHERE])
