Sql Server Interview Questions and Answers (1758) - Page 38

To get a list of all sql server collations we can execute "Fn_helpcollations() " system function. This will return name and description of various collations.

e.g.

select * from fn_helpcollations()

Result(Partial)

name description
Albanian_BIN Albanian, binary sort
Albanian_BIN2 Albanian, binary code point comparison sort
Albanian_CI_AI Albanian, case-insensitive, accent-insensitive, kanatype-insensitive, width-insensitive
Albanian_CI_AI_WS Albanian, case-insensitive, accent-insensitive, kanatype-insensitive, width-sensitive
Albanian_CI_AI_KS Albanian, case-insensitive, accent-insensitive, kanatype-sensitive, width-insensitive
Albanian_CI_AI_KS_WS Albanian, case-insensitive, accent-insensitive, kanatype-sensitive, width-sensitive

1)Function must return a value.Stored procedure may or not return values.

2) Functions will allow only Select statement, it will not allow us to use DML statements.
Stored Procedures can have select statements as well as DML statements such as insert, update, delete etc

3)Functions will allow only input parameters, doesn’t support output parameters.But Stored Procedures can have both input and output parameters.

4) Transactions are not allowed within functions.We can use transactions within Stored procefures.

5) Stored procedures can’t be called from function.Stored Procedures can call functions.

6) UDF can be used in join clause as a result set.Procedures can’t be used in Join clause

1) Varchar can store maximum 8000 Non-Unicode characters .Varchar(Max) can store maximum of 2 147 483 647 Non-Unicode characters i.e. maximum storage capacity is: 2GB.

2)We can create index on Varchar column data type.Index can’t be created on a Varchar(Max) data type columns.

3)Varchar uses the normal data pages to store the data i.e. it stores the value ‘in a row’.In case of VARCHAR(MAX), Sql server will try to store the value ‘in a row’ but if it could not then it will store the value ‘out of row’. i.e. It uses the normal data pages until the content actually fills 8k of data.When overflow happens, data is stored as old TEXT Data Type and a pointer is replacing the old content.

1)Varchar takes 1 byte per character.NVarchar takes 2 bytes per Unicode/Non-Unicode character.

2)Varchar can store maximum 8000 Non-Unicode characters.NVarchar can store maximum 4000 Unicode/Non-Unicode characters.

3)Varchar takes no. of bytes equal to the no. of Characters entered plus two bytes extra for defining offset.NVarchar takes no. of bytes equal to twice the no. of Characters entered plus two bytes extra for defining offset.

For as long as there has been T-SQL code, there has been prevalent use of SELECT
*. This is the most straightforward way to determine what a table looks like in ad hoc and troubleshooting scenarios.
In many cases, not all the columns in the table or view are required by the application.
Using SELECT * in this case can cause wasteful scans or lookups in order to
return all of the columns, when the query may have been satisfied by a covering index at a much lower resource cost. Not to mention there’s the additional overhead of sending all the unneeded columns over the network just to have the application ignore them in the first place—or worse, incorporate them into the code, never to be used.

Server has some inconsistent rules about how long a string can be, depending on how the value is defined. Consider the following examples:

DECLARE @x CHAR = 'foo';

SELECT a = @x, b = CAST('foo' AS CHAR), c = CONVERT(CHAR, 'foo');

One would expect in all three cases to see 'foo' returned, but in fact the first column in the query returns only the letter 'f'. This is because when a CHAR-based variable is
declared without a length, the length becomes 1 (and this follows the ANSI standard).On the other hand, when we use CAST or CONVERT to specify that a string should be a
CHAR-based type, the length becomes 30. This behavior can also come into play when we create tables.If we create a stored procedure that accepts a parameter with the exact same type (VARCHAR with no length), there’s no error message, and the string is silently truncated and SQL Server quite happily puts the leading character into the column:

CREATE PROCEDURE dbo.x_insert

@y VARCHAR

AS

BEGIN

SET NOCOUNT ON;

INSERT dbo.x(y) SELECT @y;

END

GO

EXEC dbo.x_insert @y = 'foo';

SELECT Result = y FROM dbo.x;



Results:

Result

-

f

This means that it can take quite a bit of manual effort, or maybe even luck, to discover that the strings we are passing into the stored procedure aren’t remaining
intact when they get written to the table. This problem goes away if we always declare a length for the CHAR-based columns.

SEQUENCE is a user-defined, schema-bound object that generates a sequence of
numeric values. The values generated by the sequence can be ascending or descending,
starting from any defined value. The value numbers can be cyclic, repeated until
reaching the upper or lower value.It has been introduced since SQL Server 2012 (code name DENALI).Unlike an IDENTITY column, a SEQUENCE object isn’t linked to any table, so any required relationships have to be managed from the application.

A SEQUENCE object can be used instead of the IDENTITY column in the following scenarios:
1) The application requires the value before an INSERT statement is executed.
2)The application requires the values to be shared between two or more tables.
3) The application has to restart the sequence after a certain value has been
reached.
4) The application requires sequence values to be sorted by another column

There are some restrictions that one have to be aware of when using a SEQUENCE:

1) Unlike an IDENTITY column, the sequence values aren’t protected from UPDATE,
and the column that received the value can be modified.

2) There aren’t any unique constraints on a sequence value.

3) In a table, the sequence value may have gaps, because of a rollback or a SQL
Server service restart.

The WITH RESULT SETS clause is related to the EXECUTE statement. This clause will
allow you to redefine the name and the data type for each column that’s in the result
set of the EXECUTE command.We can specify the new option with the EXECUTE statement when executing a stored procedure or a dynamic batch, like so:

EXECUTE WITH ;

E.g.

EXEC('SELECT 43112609 AS val;')

WITH RESULT SETS

(

  (

    val VARCHAR(10)

  )

 );

Henceforth, we can make out that, irrespective of the column name(s) returned in the result set, we can change the column names and it’s data Types as long as the data Type conversion is compatible with the original result set(i.e. the data types defined in the table schema). Else the database engine will report error.

a.Data conversion will become simpler in SSIS .

b.Changing the data type without changing the schema. Suppose a dotnet application is expecting a Boolean and the underlying schema was designed as of type int for that column.Ideally we do a conversion at runtime as Case When <condition> Then 1 Else 0. Instead of that, we can directly change the data type to bit.

c.Another example can be say the dotnet application is expecting a int but the column type is float.

d.Another usage may be say the schema has been changed and the DAL layer is not aware of this. May be the same stored procedure is called from multiple places. In such a scenario, we can just change the column names at runtime in the With Result Set so that the table schema as well as the DAL logic will be un touched.

We cannot return selected columns. The number of columns has to be same as that of the result set. For example, if we write something as under

EXEC Usp_FetchRecords 



WITH RESULT SETS



(

       (      [Emp Id] int,              



              [Phone Number] varchar(50)



       )  



) 

The engine will report the below error

Msg 11537, Level 16, State 1, Procedure Usp_FetchRecords, Line 5 EXECUTE statement failed because its WITH RESULT SETS clause specified 2 column(s) for result set number 1, but the statement sent 3 column(s) at run time.


The Usp_FetchRecords is as under

CREATE PROCEDURE [dbo].[Usp_ModifiedFetchRecords]

AS

BEGIN 



       Select 

			Id

			,Name

			,PhoneNumber

		From dbo.tbl_Test;



		Select

			Id

			,Name

		From dbo.tbl_Test

		Where PhoneNumber % 2 = 0

END

SQL Server Denali improves error handling with the new THROW (T-SQL) statement.
THROW outside of a TRY…CATCH block acts similar to the RAISERROR() function, with a
few notable exceptions:
- The message_id parameter doesn’t need to be defined in sys.messages.
- The message_id must be INT (not BIGINT) and greater than or equal to 50000.
- THROW doesn’t accept any parameter substitution for customizing the error message
inline.
- The severity level is always 16.

THROW inside a TRY…CATCH will be able to rethrow the error message that occurred in
the TRY block. The next example shows a division by 0 into a TRY block. When the error
occurs, the execution goes inside the CATCH block, where the error can be handled by
a rollback and/or a notification. Then, the THROW command displays the error:

BEGIN TRY

select 1/0

END TRY

BEGIN CATCH

PRINT N'Message from inside CATCH.';

-- rollback

-- notification

-- throwing the same errors back to the caller

THROW;

END CATCH;

go

1)Checking for @@Error must be done immediately after execution of a statement.

2)As @@ Error values changes with every execution of statement in the code we need to use a local variable to store the @@error value and use it whenever needed.

3)Along with the custom error, the system defined error also appears

This keyword is use to skip the number of rows before retrieving the rows for the projection. What the statement implies is that, suppose we have 100 records and we want to skip the first 10 records. So we need the records from 11 to 100. In this case if we issue something as

Select *

From  <SomeTable>

Order by  <SomeColumn>

Offset 10 Rows

It will generate the expected record set.

MERGE is a new feature that provides an efficient way to perform multiple DML operations. In previous versions of SQL Server, we had to write separate statements to INSERT, UPDATE, or DELETE data based on certain conditions, but now, using MERGE statement we can include the logic of such data modifications in one statement that even checks when the data is matched then just update it and when unmatched then insert it. One of the most important advantages of MERGE statement is all the data is read and processed only once.

SQL Server now supports the use of TRY...CATCH for handling errors. TRY...CATCH lets us build error handling at the level we need, by setting a region where if any error occurs, it will break out of the region and head to an error handler. The basic structure is as follows:

BEGIN TRY
<code>
END TRY
BEGIN CATCH
<code>
End CATCH

So, If any error occurs in the try block, execution is diverted to the catch block, and the error can be dealt.

A sparse column is another tool used to reduce the amount of physical storage used in a database. They are the ordinary columns that have an optimized storage for null values. Sparse columns reduce the space requirements for null values at the cost of more overhead to retrieve non null values.

CTE is an abbreviation Common Table Expression. A Common Table Expression (CTE) is an expression that can be thought of as a temporary result set which is defined within the execution of a single SQL statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query.

The TOP operator is used to specify the number of rows to be returned by a query. The TOP operator has new addition in SQL SERVER 2008 that it accepts variables as well as literal values and can be used with INSERT, UPDATE, and DELETES statements.