:::: MENU ::::

Posts Categorized / T-SQL Interview Questions

  • Nov 05 / 2008
  • 0
dbDigger, Performance Tunning and Optimization, T-SQL Interview Questions

ANSI Warnings Enabled (ANSI_WARNINGS) in SQL Server T-SQL

ANSI Warnings option is used to determine the behavior of certain exception conditions. When set to TRUE in SQL Server Management Studio or ON when using the ALTER DATABASE command, errors or warnings are issued when conditions such as divide-by-zero occur or null values appear in aggregate functions.
When set to FALSE, no warning is raised, and instead a NULL value is returned. It is important to note that connection-level settings that are set by using the SET statement override the default database setting.

  • Nov 05 / 2008
  • 0
dbDigger, T-SQL Interview Questions

ANSI Padding Enabled (ANSI_PADDING) in SQL Server T-SQL

ANSI Padding option is used to control the padding of strings for comparison and insert operations.

When set to TRUE in SQL Server Management Studio or ON when using the ALTER DATABASE command, all strings are padded to the same length before conversion or insertion into a varchar or nvarchar data type. Trailing blanks in character values inserted into varchar or nvarchar columns and trailing zeros in binary values inserted into varbinary columns are not trimmed.

When set to FALSE, trailing blanks for varchar or nvarchar, and zeros for varbinary are trimmed.

It is important to note that connection-level settings that are set by using the SET statement override the default database setting. Generally, it is recommended you keep this option set to TRUE.

  • Nov 05 / 2008
  • 0
dbDigger, T-SQL Interview Questions

ANSI NULL Default (ANSI_NULL_DEFAULT) in SQL Server T-SQL

You can use ANSI NULL Default option to determine the default value of a column, alias data type, or CLR user-defined type for which the nullability is not explicitly defined. When set to TRUE in SQL Server Management Studio or ON when using the ALTER DATABASE command, the default value is NULL, and when set to FALSE, the default value is NOT NULL. It is important to note that Connection-level settings override the default database-level setting. Similarly, columns that are defined with explicit constraints follow constraint rules regardless of this setting.

  • Nov 05 / 2008
  • 0
dbDigger, T-SQL Interview Questions

ANSI NULL Enabled (ANSI_NULLS) in SQL Server T-SQL

You can use ANSI NULL Enabled to determine how NULL values are compared.

When set to TRUE in SQL Server Management Studio or ON when using the ALTER DATABASE command, all comparisons to a null value evaluate to UNKNOWN.

When set to FALSE, comparisons of non-UNICODE values to a null value evaluate to TRUE if both values are NULL.

It is important to note that Connection-level settings override the default database setting.

  • Oct 20 / 2008
  • 0
Aggregates, dbDigger, T-SQL Interview Questions

Using Group By Clause with aggregate functions

In T-SQL Group by clause is used to summarize data for a given parameter. Real power of Group By clause may be exploited when using with Aggregate functions. Before further example and explanation it would be better to mention list of aggregate functions in T-SQL.
Aggregate functions used in T-SQL are as follows

  • Average
  • BINARY_CHECKSUM
  • CHECKSUM
  • CHECKSUM_AGG
  • COUNT
  • COUNT_BIG
  • DISTINCT
  • GROUPING
  • ROLLUP or CUBE function
  • MAX
  • MIN
  • SUM
  • STDEV
  • STDEVP
  • VAR
  • VARP

For use of explanation above functions you may visit link on MSQLTips.com
In case when aggregate functions are used with Group By clause, aggregate function is applied to each group produced in result. To understand the concept with a very use full example please follow the link to see this use for removing duplicate rows from a table.

  • Jul 29 / 2008
  • 0
Cursors, dbDigger, T-SQL Interview Questions

Server-side cursors

Question: Of course, we all know that server-side cursors are slow on SQL Server and should be avoided when possible. However, there are a few cases where they can’t be avoided, for instance because an external process has to be invoked for each row, or because you run into one of the very rare problems where a cursor actually runs faster than a set-based solution.
So, suppose you find yourself in a situation where you do need a cursor, and you also need to change the data retrieved by the cursor – how can you assure maximum performance?

 

Answer: Do not use FOR UPDATE in the cursor declaration, and use WHERE keycolumn = @keyvalue in the update statement

 

Explanation: Though not documented by Microosoft, extensive testing has shown that reading a STATIC cursor with no FOR UPDATE option and using the primary key to update the row just read is faster than specifying a FOR UPDATE option (either with or without a column list) and using the WHERE CURRENT OF clause in the UPDATE statement.

Note: The Question of day is taken from SQLServercentral.com. I want to compile selected QOD.

Consult us to explore the Databases. Contact us