SQL Server: Database Developer (T-SQL) Interview Questions and Answers (Day-16)

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

What are the useful DMVs for TempDB?

• dm_db_file_space_usage: Stores metadata about disk space usage statistics by “user object”, “internal objects” and “version store”
• dm_db_session_space_usage: Stores metadata about disk space usage, page allocation and deallocation statistics at user session level.
• dm_db_task_space_usage: Stores metadata about disk space usage, page allocation and deallocation statistics at session task level.
• dm_tran_active_snapshot_database_transactions: its returns a virtual table for all active transaction running in tempdb

What kind of different objects SQL Server stores in the TempDB?

User Objects:

• Table variables
• Table valued functions
• Local and Global temporary variables
• Local and Global Temporary Indexes
• Temp Stored procedure
• User defined table
• User defined Index
• Global table
• System Index

Internal Objects:

• Cursor’s worktable
• Temporary Large object (LOB) storage
• DISTINCT, ORDER BY, GROUP BY and UNION
• Rebuilding Index with SORT_IN_TEMPDB options
• HASH JOIN
• Multiple active result set (MARS)

Database Engine spool:

• Lazy Spool
• Eager Spool
• Index Spool
• Table Spool
• Row Count Spool
• Window Spool

Version Store:

• DML operation that use row versioning
• Index rebuild that use row versioning

How to find minimum, maximum and average row size in each table in SQL server?

Using DBCC SHOWCONTIG, we can find minimum, maximum and average row size of table.

Query to get result for all the table:

1	DBCC SHOWCONTIG WITH TABLERESULTS

Query to get result for a specific table:

1	DBCC SHOWCONTIG([dbo].[TableName]) with TABLERESULTS

What are the new features introduced in SQL Server 2014?

• Buffer pool extensions to SSD
• In-memory OLTP
• IN-memory DW
• Delayed Durability
• SQL Server datafile in window Azure
• Clustered Columnstore Index
• Better Statistics per partition
• Parallel SELECT INTO
• Rebuild Blob index ONLINE
• Backup Encryption

What are the new features introduced in SQL Server 2012?

New Features related to database administration:

• Always on features which is actually High availability and Disaster recovery solution
• Always on SQL Failover Clustering (FCI) across multi domain
• Always on Availability group
• Contained database
• Column store Index
• User defined server role
• Distributed replay
• Statistical Semantic Search
• Added few DMVs like sys.dm_os_volumn_stats, sys.dm_os_windows_info, sys.dm_server_memory_dumps, sys.dm_server_services and sys.dm_server_registry
• 15000 Partitioned table can be created now from previously 1000 limit.

New features related T-SQL:

• Sequence Object
• File table
• Logical Function CHOOSE and IIF
• Conversion function PARSE, TRY_CONVERT and TRY_PARSE
• Date and Time function DATEFORMATS, DATETIME2FORMATS, DATETIMEFORMATS, DATETIMEOFFSETFORMATS, EOMONTHS, SMALLDATETIMEFORMATS and TIMEFROMPARTS
• String functions CONCAT and FORMAT
• Analytic Function CUME_DIST, FIRST_VALUE, LAST_VALUE, LAG, LEAD, PERCENTILE_CONT, PERCENTILE_DISC and PERCENT_RANK
• OFFSET and FETCH key work introduced for SELECT statement
• ONLINE rebuild option is available now for LOB column type too

How to get Only Hour, Minute and Second from the datetime column?

What is SET ANSI WARNING on?

When on it displays the warning if there Is any null value for operations such as group by, order by max etc. and also if there is an arithmetic over flow. When off its displays no warning and also truncates the data to fit in the size of the column.

What’s wrong in the following query?

SELECT subject_code, AVG (marks) FROM students WHERE AVG(marks) > 88 GROUP BY subject_code;

The WHERE clause cannot be used to restrict groups. The HAVING clause should be used.

What’s wrong in the following query?

SELECT subject_code, count(name) FROM students;

It doesn’t have a GROUP BY clause. The subject_code should be in the GROUP BY clause.

What is the difference between SUBSTR and INSTR 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.

What is the use of FOR Clause?

FOR clause is mainly used for XML and browser options. This clause is mainly used to display the query results in XML format or in browser.

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 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.

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 to select unique records from a table?

Select unique records from a table by using DISTINCT keyword.

Select DISTINCT ColumnName from TableName;

How do you implement one-to-one, one-to-many and many-to-many relationships while designing tables?

• One-to-One relationship can be implemented as a single table and rarely as two tables with primary and foreign key relationships.
• One-to-Many relationships are implemented by splitting the data into two tables with primary key and foreign key relationships.
• Many-to-Many relationships are implemented using a junction table with the keys from both the tables forming the composite primary key of the junction table.
• It will be a good idea to read up a database designing fundamentals text book.

What is the STUFF Function and How Does it Differ from the REPLACE Function?

STUFF function is used to overwrite existing characters using this syntax: STUFF (string_expression, start, length, replacement_characters), where string_expression is the string that will have characters substituted, start is the starting position, length is the number of characters in the string that are substituted, and replacement_characters are the new characters interjected into the string.

REPLACE function is used to replace existing characters of all occurrences. Using the syntax REPLACE (string_expression, search_string, replacement_string), every incidence of search_string found in the string_expression will be replaced with replacement_string.

How to Enable/Disable Indexes?

How do you solve performance problems with XML operations?

There are no ‘correct’ answers to generic performance questions. A proper solution can be suggested only after examining the specific use case and environment.  However, a few recommendations are as follows:

If you are dealing with UNTYPED XML, try to convert them to TYPED XML. When using TYPED XML values, SQL Server has got better understanding of the structure of the XML document, data types of elements and attributes etc.  This allows SQL Server to process the XML document more efficiently.

XML Indexes can be used to speed up queries that read information from XML data type columns.