SQL SERVER DATA TYPES INTERVIEW QUESTIONS ANSWERS FOR FRESHER AND EXPERIENCED
SQL SERVER DATA TYPES INTERVIEW QUESTIONS FOR FRESHER AS WELL AS EXPERIENCED FROM BASIC TO COMPLEX
Here we are coming with SQL server interview questions category wise and this category is belong to SQL Server data types. So if you are fresher or experienced must go through below question and prepare your self for your upcoming interview. We have one request if you have any questions related to datatype then must leave as a comment we will add it in this list, and soon we will provide full pdf.
So let's first go through SQL Server Data types
Here we are coming with SQL server interview questions category wise and this category is belong to SQL Server data types. So if you are fresher or experienced must go through below question and prepare your self for your upcoming interview. We have one request if you have any questions related to datatype then must leave as a comment we will add it in this list, and soon we will provide full pdf.
So let's first go through SQL Server Data types
| DATATYPE | DESCRIPTION |
| bigint | Integer data from -2^63 to 2^63-1 |
| binary | Fixed-length binary data with a maximum length of 8,000 bytes |
| bit | Integer data with either a 1 or 0 value (often for a true or false reading) |
| char | Fixed-length non-unicode character data with a maximum length of 8,000 characters |
| cursor | A reference to a cursor |
| datetime | Date and time data from January 1, 1753, through December 31, 9999, with an accuracy of 3.33 milliseconds (but use datetime2 instead) |
| decimal | Fixed precision and scale numeric data from -10^38 +1 through 10^38 -1 (same as 'numeric') (decimal(9,2)) = max value 9999999.99) |
| float | Floating precision number data from -1.79E + 308 through 1.79E + 308 |
| image | Variable-length binary data with a maximum length of 2^31 - 1 bytes |
| int | Integer data from -2^31 through 2^31 - 1 (-2 billion to 2 billion approx) |
| money | Monetary data values from -2^63 through 2^63 - 1 |
| nchar | Fixed-length Unicode data with a maximum length of 4,000 characters |
| ntext | Variable-length Unicode data with a maximum length of 2^30 - 1 characters (Deprecated - don't use!) |
| numeric | Fixed precision and scale numeric data from -10^38 +1 through 10^38 -1 (same as 'decimal') |
| nvarchar | Variable-length Unicode data with a maximum length of 4,000 characters |
| real | Floating precision number data from -3.40E + 38 through 3.40E + 38 |
| smalldatetime | Date and time data from January 1, 1900, through June 6, 2079, with an accuracy of one minute |
| smallint | Integer data from -2^15 through 2^15 - 1 (-32000 to 32000 approx) |
| smallmoney | Monetary data values from -214,748.3648 to +214,748.3647 |
| sql_variant | A data type that stores values of various data types, except text, ntext, timestamp, and sql_variant |
| table | A special data type used to store a result set for later processing |
| text | Variable-length data with a maximum length of 2^31 - 1 characters (Deprecated - don't use!) |
| timestamp | A database-wide unique number that gets updated every time a row gets updated |
| tinyint | Integer data from 0 to 255 |
| uniqueidentifier | A globally unique identifier |
| varbinary | Variable-length binary data with a maximum length of 8,000 bytes |
| varchar | Variable-length non-unicode data with a maximum of 8,000 characters |
| date | holds date |
| time | holds time |
| datetime2 | Date and time data from January 1, 1753, through December 31, 9999, with an accuracy of about 100 nanoseconds, plus more compactly stored |
| datetimeoffset | takes international time into account in reading |
| xml | for storing or even parsing raw xml data |
SQL SERVER DATATYPES INTERVIEW QUESTIONS
What do you understand by Data-types in sql server?
Ans: SQL Server data types defines the characteristic of the data that is stored in a column. Each column, variable and expression has related data type in SQL.
How you should choose data type for particular column when you create a table?
Ans: The data type should be chosen based on the information you wish to store. for example you would not use an integer data type for storing employee name.
What is the very useful datatype introduced in SQL Server 2016?
Ans: JSON datatype
What are the two types of character data SQL Server supports?
Ans: Regular and Unicode
What are the Regular character data types?
Ans: Char and VarChar
What are the Unicode character data types?
Ans: NChar and NVarChar
How are literal strings expressed with Regular character column?
Ans: Single quote 'text'.
How are literal strings expressed with Unicode character column?
Ans: Must Start with N'text'.
What can you define with variable length character data types?
Ans: VARCHAR(MAX)
How large is VARCHAR(MAX)?
Ans: 8000 Bytes in line.
Name any five date and time data types?
Ans: 1.) DATETIME
2.) SMALLDATETIME
3.) DATE
4.) TIME
5.) DATETIME2
6.) DATETIMEOFFSET
What does the PARSE function do?
Ans: Parse a value as a requested type and indicate a culture.
Syntax?
PARSE('date' AS datatype USING culture)
What happens when you only want to work with time in a DATETIME data type?
Ans: SQL Server stores the date as Jan 1 1900.
What do you understand by Timestamp, Difference between Datetime and Timestamp datatypes?
Ans: Datetime: Datetime is a datatype.
Timestamp: Timestamp is a data type that exposes automatically generated binary numbers, which are guaranteed to be unique within a database. timestamp is used typically as a mechanism for version-stamping table rows. The storage size is 8 bytes.
In fact, in sql server 2008 this column type was renamed (i.e. timestamp is deprecated) to rowversion. It basically means that every time a row is changed, this value is increased. This is done with a database counter, i.e. two different rows that where updated in the same transaction have the same row version.
What do you understand by rowversion datatype, have you ever used rowversion?
Ans: Rowversion is a data type that exposes automatically generated, unique binary numbers within a database. rowversion is generally used as a mechanism for version-stamping table rows. The storage size is 8 bytes. The rowversion data type is just an incrementing number and does not preserve a date or a time.
Timestamp is the synonym for the rowversion data type and is subject to the behavior of data type synonyms. In DDL statements, use rowversion instead of timestamp wherever possible.
Example :
CREATE TABLE ExampleTable2 (PriKey int PRIMARY KEY, VerCol rowversion);
OR
CREATE TABLE ExampleTable1 (PriKey int PRIMARY KEY, VerCol timestamp);
How many column a table can, with timestamp column?
Ans: A table can contain only one timstamp column.
What are the differences between Datetime and Datetime2 datatypes?
What are the differences between Datetime and DateTimeOffset datatypes?
Lets suppose today is 27th Aug 2015, What will happend when we run this following script?
Is this script through error or not?
SELECT 1 + GETDATE() + 1
Ans: it will add 2 days in current date time and according to date specified it will display 29th Aug 2015 with current time.
What is User-defined data type in SQL Server?
Ans: User-defined data types also know as Alias types are based on the system data types in Microsoft® SQL Server™ 2000. User-defined data types can be used when several tables must store the same type of data in a column and you must ensure that these columns have exactly the same data type, length, and nullability. For example, a user-defined data type called postal_code could be created based on the char data type.
Can we use User-defined data type in table variable?
Ans: No
Can you alter user-defined data types?
Ans: No, If you want to change it then first you need to drop it, and then you need to re-create with your changes.
User-defined data types are located in your databaes node under?
Ans: Programmability > Types > User-Defined Data TYpes
What is SWITCHOFFSET function?
Ans: SWITCHOFFSET function is used to convert the time from one time zone to another time zone, and it will work for date time offset fields(DatetimeOffset datatype).
Syntex: SWITCHOFFSET ( DATETIMEOFFSET, time_zone )
What is SPARSE data option introduced in SQL Server 2008, when to use it?
Ans: The sparse data option is a new SQL Server 2008 feature for tields you expect to be predominantly null. Using the sparse data option, you can instruct SQL Server to not have nulls consume space in sparsely populated fields.
SPARSE column are better at managing NULL and ZERO values in SQL Server. It does not take any space in database at all. If column is created with SPARSE clause with it and it contains ZERO or NULL it will be take lesser space then regular column (without SPARSE clause).
We can use this option when we sure that our column will contain mostly null values(or zero) etc.
Example : Create table Employee(ID INT, Name Varchar(20) NULL, Salary Money NULL,
BonusAmount Money SPARSE NULL)
What are the Advantages and Disadvantages of SPARSE column?
Ans: Advantages of SPARSE column are:
-INSERT, UPDATE, and DELETE statements can reference the sparse columns by name. SPARSE column can work as one XML column as well.
-SPARSE column can take advantage of filtered Indexes, where data are filled in the row.
-SPARSE column saves lots of database space when there are zero or null values in database.
Disadvantages of SPARSE column are:
-SPARSE column does not have IDENTITY or ROWGUIDCOL property.
-SPARSE column can not be applied on text, ntext, image, timestamp, geometry, geography or user defined datatypes.
-SPARSE column can not have default value or rule or computed column.
-Clustered index or a unique primary key index can not be applied SPARSE column. SPARSE column can not be part of clustered index key.
-Table containing SPARSE column can have maximum size of 8018 bytes instead of regular 8060 bytes.
-A table operation which involves SPARSE column takes performance hit over regular column.
Ref: http://blog.sqlauthority.com/2008/07/14/sql-server-2008-introduction-to-sparse-columns-part-2/
Does SPARSE cluse use with the Geography or Geometry data types?
Ans: No, Sparse cannot be used for every data type. It can't be used with Geography or Geometry data types as well as old LOB types(text, ntext, image).
Can a primary key be a sparsed column?
Ans: No
What is the only data type avaliable in SQL Server which can store GPS data that has been defined by the OGC()?
Ans: Geography data type
What is the function which returns the closest path between two Geography points in meters?
Ans: STDistance()
What is the difference between Varchar() and Nvarchar() datatypes?(Most Imp)
What would be the output of the following script?
What would be the output of the following script?
If you have any questions related to datatype then please share as a comment.
DECLARE @Name VARCHAR(20)
SET @Name = 'विकास अहलावत'
SELECT @Name
Ans: Output would be "????? ??????"
DECLARE @Name NVARCHAR(20)
SET @Name = 'विकास अहलावत'
SELECT @Name
Ans: Output would be "????? ??????"
How will you print or save name as 'विकास अहलावत'?
How will you print or save name as 'विकास अहलावत'?
Ans: To save Unicode character we need to take data type nvarchar and string must be with "N", otherwise you will lost the even you have Nvarchar data type you can see in the above question
DECLARE @Name NVARCHAR(20)
SET @Name = N'विकास अहलावत'
SELECT @Name
Output would be : विकास अहलावत
If you have any questions related to datatype then please share as a comment.
The question are useful for the students. The writing service are also discussing about attending the interviews.
ReplyDeleteNice 1
ReplyDeleteplease send PDF of all the questions on this Emailid samirjamdagni@gmail.com
ReplyDelete