TSQL Interview Questions – Part 1
These are the following common question that I faced in various TSQL interviews.
… I’ll be updating this post with more questions.
1. Difference between SQL Server 2000 & SQL Server 2005 features, or new features of 2005 vs 2000.
– Ranking functions (ROW_NUMBER, RANK, DENSE_RANK, NTILE), [YouTube]
– Exception handling (TRY-CATCH block)
– CTE (Common Table Expressions)
– PIVOT, UNPOVIT
– CUBE, ROLUP & GROUPING SET
– SYNOMYMS
2. What tools do you use for performance tuning?
Query Analyzer, Profiler, Index Wizard, Performance Monitor
Link: http://www.sqlteam.com/article/sql-server-2000-performance-tuning-tools
3. What is SQL Profiler? What it does? What template do you use?
More Info: http://www.extremeexperts.com/SQL/Articles/TraceTemplate.aspx
http://msdn.microsoft.com/en-us/library/ms190176.aspx
4. How can you execute an SQL query from command prompt?
OSQL & SQLCMD
More Info: http://msdn.microsoft.com/en-us/library/ms162773.aspx
http://blog.sqlauthority.com/2009/01/05/sql-server-sqlcmd-vs-osql-basic-comparison/
http://www.databasejournal.com/features/mssql/article.php/3654176/SQL-Server-2005-Command-Line-Tool-SQLCMD–Part-I.htm
5. Difference between DELETE & TRUNCATE statement? Which statement can be Rollbacked?
– With DELETE we can provide conditional WHERE clause to remove/delete specific rows, which is not possible with TRUNCATE.
– TRUNCATE is faster than DELETE as Delete keeps log of each row it deletes in transaction logs, but truncate keeps log of only de-allocated pages in transaction logs.
– Both statements can be rolled backed if provided in a transaction (BEGIN TRANS). If not then none of them can be rollbacked.
– DELETE is DML just like INSERT, UPDATE, but TRANCATE is DDL, just like CREATE, ALTER, DROP
More differences on Delete & Truncate: http://wp.me/p12rgl-7 | YouTube.
6. What are extended stored procedures? Can you create your own extended stored-proc?
More Info: http://msdn.microsoft.com/en-us/library/ms175200.aspx
http://msdn.microsoft.com/en-us/library/ms164627.aspx
7. How can you execute a DOS command from SQL or through SQL query by using xp_cmdshell?
exec xp_cmdshell 'dir c:\*.exe'
More Info: http://msdn.microsoft.com/en-us/library/aa260689%28SQL.80%29.aspx
http://msdn.microsoft.com/en-us/library/ms175046.aspx
8. How will you insert result set of the above proc in a table?
insert into exec xp_cmdshell 'dir c:\*.exe'
9. What are Cursors and their types? What type do you use most and which one is fast?
FORWARD-ONLY, FAST-FORWARD or READ-ONLY cursors.
Fastest to slowest: Dynamic, Static, and Keyset.
More Info: http://msdn.microsoft.com/en-us/library/ms180169.aspx
Check how to use Cursors optimally: https://sqlwithmanoj.com/2011/02/07/avoid-cursors-or-use-them-optimally/
10. Why you should not use a cursor? What are its alternatives?
Alternatives: while loops with temp tables, derived tables, correlated sub-queries, CASE stmt
More Info: http://www.sql-server-performance.com/tips/cursors_p1.aspx
http://sqlserverpedia.com/wiki/Cursor_Performance_Issues
http://searchsqlserver.techtarget.com/feature/Part-3-Cursor-disadvantages
11. Difference between LEFT JOIN with WHERE clause & LEFT JOIN with no WHERE clause.
OUTER LEFT/RIGHT JOIN with WHERE clause can act like an INNER JOIN if not used wisely or logically.
JOINs and its types: https://sqlwithmanoj.com/2009/03/12/sql-server-joins-and-types/
12. How will you migrate an SSIS package from Development to Production environment?
Do not include db connections and file paths in your workflow, instead create configuration files. This will help in deploying the pkg created in DEV server to Testing and finally to the PROD environment.
More Info: http://msdn.microsoft.com/en-us/library/cc966389.aspx
http://www.wpconfig.com/2010/03/26/ssis-package-configurations/
13. Multiple ways to execute a dynamic query.
EXEC sp_executesql, EXECUTE()
More Info: https://sqlwithmanoj.com/2011/03/22/execute-or-exec-vs-sp_executesql/
14. Difference between COALESCE() & ISNULL()
More Info: https://sqlwithmanoj.com/2010/12/23/isnull-vs-coalesce/
15. Difference between Temporary Table and Table Variable.
Check here: http://wp.me/p12rgl-r | YouTube
16. What should be the ideal combination with IN & UNION (ALL) in terms of performance?
a. SELECT *FROM
WHERE
IN (SELECT… UNION SELECT…)
OR
b. SELECT * FROM
WHERE
IN (SELECT… UNION ALL SELECT…)
17. What is an IDENTITY column and its usage in INSERT statements?
IDENTITY column can be used with a tables column to make it auto incremental, or a surrogate key.
Check my all blog posts related to IDENTITY: https://sqlwithmanoj.com/tag/identity/
18. Can you create a Primary key without clustered index?
Yes, you can create a Primary Key with a Non Clustered Index. But by default creation of PK automatically creates a clustered index upon the table, check here: https://sqlwithmanoj.com/2015/05/24/sql-myth-primary-key-pk-always-creates-clustered-index/
19. There are two tables one Master and another Feed table, both with 2 columns: ID & Price. Feed table gets truncated and re-populated every day.
Master Table Feed Table ID, Price ID, Price 1 100 1 200 3 200 2 250 5 300 4 500 6 400 6 750 7 500 7 800
Create a job with an optimal script that will update the Master table by the Feed table.
Use MERGE statement to INSERT any new record form the Feed table, update any existing record in Master table and DELETE existing records in Master table that is not present in Feed table.
20. What are CUBE & ROLLUP sets?
CUBE & ROLLUP are the grouping sets used with GROUP BY clause and are very helpful in creating reports.
More Info: https://sqlwithmanoj.com/tag/cube/
21. What new indexes are introduced in SQL Server 2005 in comparison to 2000?
– Spatial
– XML
More Info: http://msdn.microsoft.com/en-us/library/ms175049.aspx
22. What are XML indexes, what is their use?
More Info: http://msdn.microsoft.com/en-us/library/ms345121%28SQL.90%29.aspx
23. How many types of functions (UDF) are there in SQL Server? What are inline functions?
– Scalar functions
– Inline Table-valued functions
– Multi-statement Table-valued functions
More Info: https://sqlwithmanoj.com/2010/12/11/udf-user-defined-functions/
http://msdn.microsoft.com/en-us/library/ms189593.aspx
24. How will you handle exceptions in SQL Server programming?
By using TRY-CATCH constructs, putting our SQL statements/scripts inside the TRY block and error handling in the CATCH block, https://sqlwithmanoj.com/2010/06/16/try-catch-exception-handling/.
25. How would you send an e-mail form SQL Server?
Configure Database mail here: https://sqlwithmanoj.com/2010/09/29/database-mail-setup-sql-server-2005/.
… more questions on next post [Part-2].
SQL DBA – Querying SQL Server Metadata, INFORMATION_SCHEMA, sysObjects, System Catalog, etc
While writing complex code & business logic in my scrits & stored-procdedures most of the time I wonder…
– What table does a particular column belongs to?
– What all Stored Procedures effect a particular table?
– How can I see what particular constraint does my tables have?
– What all Foreign Keys defined in table’s columns are linked to?
These and many more questions can be answered by querying the SQL Server System Catalog and metadata that SQL Server manages very beautifully.
The SQL Server system catalogs is a set of views that show metadata that describes the objects in an instance of SQL Server. Metadata is data that describes the attributes of objects in a system. SQL Server-based applications can access the information in the system catalogs by using some of the following:
-> Information Schema, views to quickly retrieve metadata
-> Catalog Views, recommened by MS.
–// Information Schema Views: They present the catalog information in a format that is independent of any catalog table implementation and therefore are not affected by changes in the underlying catalog tables.
-- INFORMATION_SCHEMA.TABLES select * from INFORMATION_SCHEMA.TABLES
TABLE_CATALOG TABLE_SCHEMA TABLE_NAME TABLE_TYPE
AdventureWorks Production ProductProductPhoto BASE TABLE
AdventureWorks Sales StoreContact BASE TABLE
AdventureWorks Person Address BASE TABLE
AdventureWorks Production ProductReview BASE TABLE
AdventureWorks Production TransactionHistory BASE TABLE
AdventureWorks Person AddressType BASE TABLE
select distinct TABLE_SCHEMA from INFORMATION_SCHEMA.TABLES
TABLE_SCHEMA
dbo
HumanResources
Person
Production
Purchasing
Sales
select distinct TABLE_TYPE from INFORMATION_SCHEMA.TABLES
TABLE_TYPE
BASE TABLE
VIEW
-- INFORMATION_SCHEMA.COLUMNS select * from INFORMATION_SCHEMA.COLUMNS select TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH, NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX, NUMERIC_SCALE from INFORMATION_SCHEMA.COLUMNS select * from INFORMATION_SCHEMA.COLUMNS where COLUMN_NAME = 'ContactID' -- INFORMATION_SCHEMA.VIEWS select * from INFORMATION_SCHEMA.VIEWS select * from INFORMATION_SCHEMA.VIEW_TABLE_USAGE select * from INFORMATION_SCHEMA.VIEW_COLUMN_USAGE -- INFORMATION_SCHEMA.ROUTINES select * from INFORMATION_SCHEMA.ROUTINES select distinct ROUTINE_SCHEMA from INFORMATION_SCHEMA.ROUTINES
ROUTINE_SCHEMA
dbo
HumanResources
select distinct ROUTINE_TYPE from INFORMATION_SCHEMA.ROUTINES
ROUTINE_TYPE
FUNCTION
PROCEDURE
select ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPE, DATA_TYPE, ROUTINE_BODY, ROUTINE_DEFINITION, IS_DETERMINISTIC, SQL_DATA_ACCESS, CREATED, LAST_ALTERED from INFORMATION_SCHEMA.ROUTINES select * from INFORMATION_SCHEMA.ROUTINES where ROUTINE_DEFINITION like '%ContactID%' select * from INFORMATION_SCHEMA.ROUTINE_COLUMNS
–// Catalog Views: provide access to metadata that is stored in every database on the server.
-- The following SQL statement will pull all Stored-Procedures & Functions in the Database.
select c.id, object_name(c.id) as obj_name_c, o.name as obj_name_o, o.xtype, c.text, o.crdate, o.refdate
from sys.syscomments c
join sys.sysobjects o
on o.id = c.id
where xtype in ('P', 'FN')
-- This following SQL statement will pull all Objects information with in the Database.
select c.id, object_name(c.id) as obj_name_c, -- o.name
case xtype
when 'C' then 'Check Constraint'
when 'D' then 'Default Constraint'
when 'F' then 'Foreign Key Constraint'
when 'L' then 'Log'
when 'P' then 'Stored Procedure'
when 'PK' then 'Primary Key Constraint'
when 'RF' then 'Replication Filter Procedure'
when 'S' then 'System Table'
when 'TR' then 'Trigger'
when 'U' then 'User Table'
when 'UQ' then 'Unique Constraint'
when 'V' then 'View'
when 'X' then 'Extended Procedure'
when 'FN' then 'User Defined Function'
else 'N/A'
end as obj_type,
c.text, o.crdate, o.refdate
from sys.syscomments c
join sys.sysobjects o
on o.id = c.id
-- Pulls Foreign Key and its links
select fk.name as fk_name, fk.object_id, object_name(fk.parent_object_id) as table_name,
col_name(fc.parent_object_id, fc.parent_column_id) as constraint_col_name,
object_name(fk.referenced_object_id) as referenced_table,
COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS referenced_col_name,
fk.delete_referential_action_desc
from sys.foreign_keys fk
join sys.foreign_key_columns fc
on fk.object_id = fc.constraint_object_id
Note: All examples are executed on SQL Server 2005 AdventureWorks DB.
More info from MSDN BOL: http://msdn.microsoft.com/en-us/library/ms189082(v=SQL.90).aspx
MSDN FAQs: http://msdn.microsoft.com/en-us/library/ms345522.aspx
MS SQL Server (TSQL) Interview Questions
Here is a collection of MS SQL Server (TSQL) Server Interview Questions. Some I faced in interviews and some common questions:
1. TSQL Interview Questions – Part 1
2. TSQL Interview Questions – Part 2
3. TSQL Interview Questions – Part 3
4. TSQL Interview Questions – Part 4
5. TSQL Interview Questions – Part 5
Comments, suggestions & more question appreciated.
OUTPUT clause and MERGE statement in SQL Server
Just responded to a post in MSDN forum, link: http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/c06e1db4-7fd6-43c4-8569-5335d555dac8
Accroding to MS-BOL, OUTPUT clause returns information from, or expressions based on, each row affected by an INSERT, UPDATE, DELETE, or MERGE statement. These results can be returned to the processing application for use in such things as confirmation messages, archiving, and other such application requirements. The results can also be inserted into a table or table variable. Additionally, you can capture the results of an OUTPUT clause in a nested INSERT, UPDATE, DELETE, or MERGE statement, and insert those results into a target table or view.
Lets go with a self descriptive example:
–> OUTPUT with INSERT
create table manoj (sn int, ename varchar(50)) insert into manoj OUTPUT INSERTED.* values (1,'manoj'), (2,'hema'), (3,'kanchan'), (4,'pankaj')
This gives me following output instead of normal message (N row(s) affected):
sn ename
1 manoj
2 hema
3 kanchan
4 pankaj
select * from manoj
This gives me the same output as above:
sn ename
1 manoj
2 hema
3 kanchan
4 pankaj
–> OUTPUT with DELETE
delete from manoj OUTPUT DELETED.* where sn = 4
This gives me following output:
sn ename
4 pankaj
select * from manoj
Now the result set is changed to:
sn ename
1 manoj
2 hema
3 kanchan
–> OUTPUT with UPDATE
update manoj set ename = 'pankaj' OUTPUT DELETED.*, INSERTED.* from manoj where sn = 2
This gives me following output:
sn ename sn ename
2 hema 2 pankaj
select * from manoj
Now the result set is changed to:
sn ename
1 manoj
2 pankaj
3 kanchan
–> OUTPUT with MERGE
According to MS-BOL, MERGE performs insert, update, or delete operations on a target table based on the results of a join with a source table. For example, you can synchronize two tables by inserting, updating, or deleting rows in one table based on differences found in the other table.
create table manoj2 (sn int, ename varchar(50)) insert into manoj2 values (1,'manoj'), (2,'hema'), (3,'kanchan'), (4,'pankaj'), (5,'saurabh') select * from manoj2
This gives me following output instead of normal message (N row(s) affected):
sn ename
1 manoj
2 hema
3 kanchan
4 pankaj
MERGE manoj AS TARGET USING (SELECT sn, ename FROM manoj2) AS SOURCE ON (TARGET.sn = SOURCE.sn) WHEN MATCHED THEN UPDATE SET TARGET.ename = SOURCE.ename WHEN NOT MATCHED BY TARGET THEN INSERT (sn, ename) VALUES (sn, ename) OUTPUT $action, DELETED.*, INSERTED.*;
This gives me following output:
$action sn ename sn ename
INSERT NULL NULL 4 pankaj
INSERT NULL NULL 5 saurabh
UPDATE 1 manoj 1 manoj
UPDATE 2 pankaj 2 hema
UPDATE 3 kanchan 3 kanchan
select * from manoj
Now the result set is changed to:
sn ename
1 manoj
2 hema
3 kanchan
4 pankaj
5 saurabh
–> Final cleanup
drop table manoj drop table manoj2
Plz note: An UPDATE, INSERT, or DELETE statement that has an OUTPUT clause will return rows to the client even if the statement encounters errors and is rolled back. The result should not be used if any error occurs when you run the statement.
MS BOL:-
– On MERGE: http://technet.microsoft.com/en-us/library/bb510625.aspx
– On OUTPUT: http://technet.microsoft.com/en-us/library/ms177564.aspx
SQL Server 2012, codename Denali – New Features
Microsoft released out the first Community Technology Preview (CTP) of the next generation of its SQL Server Enterprise Database product codenamed Denali.
I found very interesting information on MS BOL and want to brief it here as we developers are interested in new features, like:
A. New Server Installation:
1. SQL Server Multi-Subnet Clustering (One can now configure a SQL Server failover cluster using clustered nodes on different subnets, link: http://msdn.microsoft.com/en-us/library/ff878716(v=SQL.110).aspx)
2. Prerequisite Installation during SQL Server Setup (You must download the following and install them before you start SQL Server Setup, link: http://msdn.microsoft.com/en-us/library/ms143219(v=SQL.110).aspx)
2.1. Windows PowerShell
2.2. MS.NET Framework
2.3. No-Reboot package for .NET Framework 4.0
3. No support for SQL Server Itanium editions
B. New (Database Engine):
1. Availability Enhancements – “HADR” is a high-availability and disaster recovery solution introduced in SQL Server Code-Named “Denali” to enable you to maximize availability for one or more of your user databases. Deploying “HADR” involves configuring one or more availability groups. Each availability group establishes the context within which a selected set of user databases can fail over as a single unit. (http://msdn.microsoft.com/en-us/library/ff877884(v=SQL.110).aspx)
2. Manageability Enhancements – New Improved & enhanced tools & monitiring features.
2.1. SSMS, enhanced functionality for Transact-SQL debugging and IntelliSense (http://msdn.microsoft.com/en-us/library/cc645579(v=SQL.110).aspx)
2.2. Powershell, is no longer installed by SQL Server Setup, but is a pre-requisite for installing SQL Server Code-Named “Denali”.
2.3. Contained Databases, Moving databases from one instance of the SQL Server Database Engine to another instance of the Database Engine is simplified by introducing contained databases. Users in a contained database are no longer associated with logins on the instance of SQL Server. Many other dependencies on the instance are also removed. (http://msdn.microsoft.com/en-us/library/ff929071(v=SQL.110).aspx)
2.4. Startup Options, Database Engine startup options are now configured by using a new Startup Parameters tab of SQL Server Configuration Manager. (http://msdn.microsoft.com/en-us/library/ms345416(v=SQL.110).aspx)
3. Programmability Enhancements – includes property-scoped full-text search and customizable proximity search, ad-hoc query paging, circular arc segment support for spatial types, support for sequence objects, and numerous improvements and additions to Transact-SQL.
3.1. Full-Text Search, You can configure a full-text index to support property-scoped searching on properties, such as Author and Title, which are emitted by IFilters. (http://msdn.microsoft.com/en-us/library/ee677637(v=SQL.110).aspx)
You can customize a proximity search by using the new custom NEAR option of the CONTAINS predicate or CONTAINSTABLE function. Custom NEAR enables you to optionally specify the maximum number of non-search terms that separate the first and last search terms in a match. Custom NEAR also enables you to optionally specify that words and phrases are matched only if they occur in the order in which you specify them. (http://msdn.microsoft.com/en-us/library/ms142568(v=SQL.110).aspx)
3.2. Metadata Discovery, The SET FMTONLY option for determining the format of a response without actually running the query is replaced with sp_describe_first_result_set, sp_describe_undeclared_parameters, sys.dm_exec_describe_first_result_set, and sys.dm_exec_describe_first_result_set_for_object. (http://msdn.microsoft.com/en-us/library/ms173839(v=SQL.110).aspx)
3.3. EXECUTE Statement, can now specify the metadata returned from the statement by using the WITH RESULT SETS argument. (http://msdn.microsoft.com/en-us/library/ms188332(v=SQL.110).aspx)
3.4. UTF-16 Supplementary Characters (SC) Collations
3.5. Ad-hoc Query Paging Implementation, You can specify a range of rows returned by a SELECT statement based on row offset and row count values that you provide. This is useful when you want to control the number of rows sent to a client application for a given query. (http://msdn.microsoft.com/en-us/library/ms188385(v=SQL.110).aspx)
3.6. Circular Arc Segment Support for Spatial Types
3.7. Sequence Objects, is a user-defined schema-bound object that generates a sequence of numeric values according to the specification with which the sequence was created. It operates similar to an identity column, but sequence numbers are not restricted to use in a single table. (http://msdn.microsoft.com/en-us/library/ff878058(v=SQL.110).aspx)
4. Scalability and Performance Enhancements (http://msdn.microsoft.com/en-us/library/cc645580(v=SQL.110).aspx)
5. Security Enhancements, include new SEARCH PROPERTY LIST permissions, new user-defined server roles, and new ways of managing server and database roles.
5.1. New Permissions, New GRANT, REVOKE, and DENY permissions to a SEARCH PROPERTY LIST are available. New GRANT, REVOKE, and DENY permissions to CREATE SERVER ROLE and ALTER ANY SERVER ROLE.
5.2. New Role Management, User-defined server roles are now available. To manage user-defined server roles and to add and remove members from all server roles, use CREATE SERVER ROLE, ALTER SERVER ROLE, and DROP SERVER ROLE.
5.3. Hashing Algorithms, The HASHBYTES function now supports the SHA2_256, and SHA2_512 algorithms.
5.4. Database Engine Access through Contained Databases
6. ColumnStore Index, new technology for storing, retrieving and managing data by using a columnar data format, called a ColumnStore, [video].
C. New (Integration Services): (http://msdn.microsoft.com/en-us/library/bb522534(v=SQL.110).aspx)
1. Deployment and Administration Enhancements, Build your project, packages, and parameters to a project deployment file (.ispac extension) and deploy it directly to the SQL Server database engine. New dialog boxes in SQL Server Management Studio and a comprehensive set of Transact-SQL views and stored procedures help you manage, execute, and monitor your packages in real time.
2. Object Impact and Data Lineage Analysis
3. Usability Enhancements
4. Reduced Memory Usage by the Merge and Merge Join Transformations, Merge and Merge Join transformations are more robust and reliable now.
5. New Data Correction Component, that enables you to more easily and accurately improve the quality of data.
Current Visitors
StatCounter …since April 2012
Leisure blog: Creek & Trails
- NMDC Hyderabad Marathon – My first 42k FM, cramps, training and fuelling
- Singapore (Part 2) – 6 days itinerary, sightseeing & attractions
- Singapore (Part 1) – Tickets, Visa, Hotel, Forex Card/Cash, Metro/Bus cards
- I got full refund of my flight tickets during COVID lockdown (AirIndia via MakeMyTrip)
- YouTube – Your Google Ads account was cancelled due to no spend
- YouTube latest update on its YPP (YouTube Partner Program) which may affect your channel
- Starting your own blog !!!
- How to file ITR (Income Tax Return) online AY 2017-18 (for simple salaried)
- Scam – Become a kin/hier and earn a fortune – via LinkedIn and Email
- Places to visit in and around Vizag (aka Visakhapatnam)
SQL with Manoj 