SQL SERVER – Frequently Asked Interview Questions And Answers - II

What are the Different Data types  in SQL Server ?Different types of Data types :                                  Exact & Approximate Numerics :                            TinyInt, SmallInt, Int, BigInt, Numeric, Decimal, Float,Real,Bit

Character & Unicode Character Strings :                      Char, Varchar, text, nchar, nVarchar, ntext                     

Binary Strings :                                                           Binary, varbinary, Image                                                

Date and time :                                         SmallDatetime, Datetime                                            

Monetary :                                                            SmallMoney, Money                                               

Other Data Types :                                               Cursor, unique identifier, Timestamp, Rowversion, Sysname, table, sql_variant, xml,User Defined Data type

How many bytes will TinyInt, SmallInt, Int and BigInt  Occupy?                                                                                 TinyInt, SmallInt, Int and BigInt will Occupy 1,2,4,8 bytes  respectively.

What is the Difference between Numeric and Decimal?    In general,There is no difference between Numeric and Decimal. we can use both of them interchangeably to store integer and floating-point numbers scaled from 1 to 38 places, inclusive of both sides of a decimal. Generally we will use this data type to control the accuracy of our calculations in terms of the number of decimal digits.

What is the Difference between Float and Real?            The only difference between float and real data types are their minimum and maximum values and their storage values. Float ranges from -1.79E + 308 to 1.79 + 308, while real ranges from     -3.40E + 38 to 3.40E + 38. The storage size of Float is 8 bytes while that of Real it is 4 bytes. The real data type is the same as float(24) — a floating data type with 24 digits to the right of the decimal point.

What is a Bit data type?                                                   Bit is used to store the logical information like True/False or  Yes/No. These Information will be stored as an Integer data type that can take a value of 1, 0, or NULL. Bit Occupies 1 byte for every 8 bit columns.

Difference Between Char/Varchar/nvarchar datatype? Char[(n)]     - Fixed-length, Non-Unicode character data with length of n bytes. n must be a value from 1 through 8,000. Storage size is n bytes.
Varchar[(n)] - Variable-length, Non-Unicode character data with length of n bytes. n must be a value from 1 through 8,000. Storage size is the actual length in bytes of the data entered, not n bytes. The data entered can be 0 characters in length.                                                             nvarchar(n)  - Variable-length, Unicode character data of n characters. n must be a value from 1 through 4,000. Storage size, in bytes, is two times the number of characters entered. The data entered can be 0 characters in length.                                                                     

What is the Difference between text and Image Data type?
Individual text, ntext, and image values can be a maximum of 2GB, which is too long to store in a single data row.All that is stored in the row is not the actual data but it is a pointer to the database pages that contain the Actual data. Coming to the difference between text and Image, Text data type is used for character data for storing more than 8000 characters. Image is used for binary large objects (BLOBs) such as digital images.

Name the Datatype which cannot be assigned to a table column.                                                              Cursor data type is the only system data type that cannot be assigned to a table column.

What is a Cursor? Is it a Data type or database object? 
Cursor data type is the only system data type that cannot be assigned to a table column in a CREATE TABLE statement. It can be used only with variables and stored procedure output parameters that contain a reference to a cursor. That means,  It is either declared with the FOR clause that will bind it to a SELECT statement or it will act as an alias for another cursor, with the SET statement.

What is UniqueIdentifier Data type?                            The UniqueIdentifier data type stores 16-byte binary values that operate as globally unique identifiers (GUIDs). A GUID is a unique binary number; no other computer in the world will generate a duplicate of that GUID value. The main use for a GUID is for assigning an identifier that must be unique in a network that has many computers at many sites

What is a Table Data type?
The Table data type is an alternative way to create a temporary table. The variable declaration requires the column definition for the table and it creates a temporary table in tempdb.Table datatype is used to store a result set for later processing. We cannot use this datatype for a column in a CREATE TABLE statement. You should use DECLARE @local_variable to declare variables of type table. Table variables should be used instead of temporary tables, whenever possible, because table variables are cleaned up automatically at the end of the function, stored procedure, or batch in which they are defined and these table variables require less locking and logging resources.

What is the difference between Timestamp and Rowversion?                                                                    Timestamp and Rowversion are two different names for the exact same data type. Timestamp is the synonym for the Rowversion data type. Microsoft deprecated timestamp data type and replaced the same with Rowversion In SQL 2005.In general These types has nothing at all to do with dates or times.Rowversion Is a data type that exposes automatically generated, unique binary numbers within a database and generally used as a mechanism for version-stamping table rows.The storage size is 8 bytes.

What is the difference between UniqueIdentifier  and Identity?

TIMESTAMP	UNIQUEIDENTIFIER
Size of TIMESTAMP value is 8 bytes	size of UNIQUEIDENTIFIER is 16 bytes
not based on system date or time	based on the computer's MAC addresses and system date time.
purpose to TIMESTAMP is to track the operation in tables on the database level	purpose is to have a unique value assigned to a row. It remains unique in any system in the world.
It should not be a primary key	it may be a primary key

What is SQL_variant Data type?                               Sql_variant data type is used to store values of various SQL Server supported data types, except text, ntext, timestamp, and sql_variant. The maximum length of sql_variant data type is 8016 bytes. You can store in one column of type sql_variant, the rows of different data types. For example in the Address column of sql_variant data type, we can store int, bigint, char and varchar values.

What is User defined data type?                                 User defined Data type can be used to create custom data types that are based on system data types using the system stored procedure sp_addtype.

What is Sysname datatype?                                               sysname data type is used for table columns, variables, and stored procedure parameters that store object names.Sysname is functionally the same as nvarchar(128) except that, sysname is NOT NULL.

What are the Different Types of Database Objects?Table, View, Procedure, User Defined Function, Trigger, Rule, Check Constraint, Default Constraint, PRIMARY KEY  Constraint, FOREIGN KEY Constraint, UNIQUE Constraint, Synonym, System Base Table