Sql server important questions and answers for interview

http://www.careerbuzz.in/sql-server-2008-interview-questions-and-answers/


some sql server important questions and answers.

tempdb Database

http://msdn.microsoft.com/en-us/library/ms190768.aspx

or

tempdb Database

SQL Server 2012

Other Versions

4 out of 4 rated this helpful - Rate this topic

The tempdb system database is a global resource that is available to all users connected to the instance of SQL Server and is used to hold the following:

• Temporary user objects that are explicitly created, such as: global or local temporary tables, temporary stored procedures, table variables, or cursors.
• Internal objects that are created by the SQL Server Database Engine, for example, work tables to store intermediate results for spools or sorting.
• Row versions that are generated by data modification transactions in a database that uses read-committed using row versioning isolation or snapshot isolation transactions.
• Row versions that are generated by data modification transactions for features, such as: online index operations, Multiple Active Result Sets (MARS), and AFTER triggers.

Operations within tempdb are minimally logged. This enables transactions to be rolled back. tempdb is re-created every time SQL Server is started so that the system always starts with a clean copy of the database. Temporary tables and stored procedures are dropped automatically on disconnect, and no connections are active when the system is shut down. Therefore, there is never anything in tempdb to be saved from one session of SQL Server to another. Backup and restore operations are not allowed on tempdb.

Physical Properties of tempdb

---

The following table lists the initial configuration values of the tempdb data and log files. The sizes of these files may vary slightly for different editions of SQL Server.

File	Logical name	Physical name	File growth
Primary data	tempdev	tempdb.mdf	Autogrow by 10 percent until the disk is full
Log	templog	templog.ldf	Autogrow by 10 percent to a maximum of 2 terabytes

The size of tempdb can affect the performance of a system. For example, if the tempdb size is too small, the system processing could be too occupied with autogrowing the database to support your workload requirement every time that you start SQL Server. You can avoid this overhead by increasing the size of tempdb.

Performance Improvements in tempdb

---

In SQL Server, tempdb performance is improved in the following ways:

• Temporary tables and table variables may be cached. Caching allows operations that drop and create the temporary objects to execute very quickly and reduces page allocation contention.
• Allocation page latching protocol is improved. This reduces the number of UP (update) latches that are used.
• Logging overhead for tempdb is reduced. This reduces disk I/O bandwidth consumption on the tempdb log file.
• The algorithm for allocating mixed pages in tempdb is improved.

Moving the tempdb Data and Log Files

To move the tempdb data and log files, see Move System Databases.

Database Options

The following table lists the default value for each database option in the tempdb database and whether the option can be modified. To view the current settings for these options, use the sys.databases catalog view.

Database option	Default value	Can be modified
ALLOW_SNAPSHOT_ISOLATION	OFF	Yes
ANSI_NULL_DEFAULT	OFF	Yes
ANSI_NULLS	OFF	Yes
ANSI_PADDING	OFF	Yes
ANSI_WARNINGS	OFF	Yes
ARITHABORT	OFF	Yes
AUTO_CLOSE	OFF	No
AUTO_CREATE_STATISTICS	ON	Yes
AUTO_SHRINK	OFF	No
AUTO_UPDATE_STATISTICS	ON	Yes
AUTO_UPDATE_STATISTICS_ASYNC	OFF	Yes
CHANGE_TRACKING	OFF	No
CONCAT_NULL_YIELDS_NULL	OFF	Yes
CURSOR_CLOSE_ON_COMMIT	OFF	Yes
CURSOR_DEFAULT	GLOBAL	Yes
Database Availability Options	ONLINE MULTI_USER READ_WRITE	No No No
DATE_CORRELATION_OPTIMIZATION	OFF	Yes
DB_CHAINING	ON	No
ENCRYPTION	OFF	No
NUMERIC_ROUNDABORT	OFF	Yes
PAGE_VERIFY	CHECKSUM for new installations of SQL Server. NONE for upgrades of SQL Server.	Yes
PARAMETERIZATION	SIMPLE	Yes
QUOTED_IDENTIFIER	OFF	Yes
READ_COMMITTED_SNAPSHOT	OFF	No
RECOVERY	SIMPLE	No
RECURSIVE_TRIGGERS	OFF	Yes
Service Broker Options	ENABLE_BROKER	Yes
TRUSTWORTHY	OFF	No

For a description of these database options, see ALTER DATABASE SET Options (Transact-SQL).

Restrictions

---

The following operations cannot be performed on the tempdb database:

• Adding filegroups.
• Backing up or restoring the database.
• Changing collation. The default collation is the server collation.
• Changing the database owner. tempdb is owned by dbo.
• Creating a database snapshot.
• Dropping the database.
• Dropping the guest user from the database.
• Enabling change data capture.
• Participating in database mirroring.
• Removing the primary filegroup, primary data file, or log file.
• Renaming the database or primary filegroup.
• Running DBCC CHECKALLOC.
• Running DBCC CHECKCATALOG.
• Setting the database to OFFLINE.
• Setting the database or primary filegroup to READ_ONLY.

Permissions

---

Any user can create temporary objects in tempdb. Users can only access their own objects, unless they receive additional permissions. It is possible to revoke the connect permission to tempdb to prevent a user from using tempdb, but this is not recommended as some routine operations require the use of tempdb.

Related Content

---

SORT_IN_TEMPDB Option For Indexes

System Databases

sys.databases (Transact-SQL)

sys.master_files (Transact-SQL)

Move Database Files

Difference Between Sql Server VARCHAR and NVARCHAR Data Type

u can use this link http://beginsql.wordpress.com/2011/12/23/difference-between-varchar-and-nvarchar/

or

Below table lists out the major difference between the VARCHAR and NVARCHAR Data Type in Sql Server:

	Varchar[(n)]	NVarchar[(n)]
Basic Definition	Non-Unicode Variable Length character data type. Example: DECLARE @FirstName AS VARCHAR(50) =‘BASAVARAJ’SELECT @FirstName	UNicode Variable Length character data type. It can store both non-Unicode and Unicode (i.e. Japanese, Korean etc) characters. Example: DECLARE @FirstName AS NVARCHAR(50)=‘BASAVARAJ’SELECT @FirstName
No. of Bytes required for each character	It takes 1 byte per character Example: DECLARE @FirstName AS VARCHAR(50) =‘BASAVARAJ’SELECT @FirstName ASFirstName,DATALENGTH(@FirstName) ASLengthResult:FirstName Length BASAVARAJ 9	It takes 2 bytes per Unicode/Non-Unicode character. Example: DECLARE @FirstName AS NVARCHAR(50)=‘BASAVARAJ’SELECT @FirstName ASFirstName,DATALENGTH(@FirstName) ASLength Result:FirstName Length BASAVARAJ 18
Optional Parameter nrange	Optional Parameter n value can be from 1 to 8000.Can store maximum 8000 Non-Unicode characters.	Optional Parameter n value can be from 1 to 4000.Can store maximum 4000 Unicode/Non-Unicode characters
If Optional Parameter n is not specified in the variable declaration or column definition	If Optional parameter value nis not specified in the variable declaration or column definition then it is considered as 1. Example: DECLARE @firstName VARCHAR=‘BASAVARAJ’SELECT @firstName FirstName,DATALENGTH(@firstName)Length Result:FirstName Length B 1	If Optional parameter value n is not specified in the variable declaration or column definition then it is considered as 1. Example: DECLARE @firstName NVARCHAR=‘BASAVARAJ’SELECT @firstName FirstName,DATALENGTH(@firstName)Length Result:FirstName Length B 2
If Optional Parameter n is not specified in while using CAST/CONVERT functions	When this optional parameter n is not specified while using the CAST/CONVERT functions, then it is considered as 30.Example:DECLARE @firstName VARCHAR(35)=‘BASAVARAJ PRABHU BIRADAR INDIA ASIA’SELECT CAST(@firstName AS VARCHAR)FirstName,DATALENGTH(CAST(@firstNameAS VARCHAR)) Length Result:FirstName Length BASAVARAJ PRABHU BIRADAR INDIA 30	When this optional parameter n is not specified while using the CAST CONVERT functions, then it is considered as 30.Example:DECLARE @firstName NVARCHAR(35)=‘BASAVARAJ PRABHU BIRADAR INDIA ASIA’SELECT CAST(@firstName AS NVARCHAR)FirstName,DATALENGTH(CAST(@firstNameAS NVARCHAR)) Length Result:FirstName Length BASAVARAJ PRABHU BIRADAR INDIA 60
Which one to use?	If we know that data to be stored in the column or variable doesn’t have any Unicode characters.	If we know that the data to be stored in the column or variable can have Unicode characters.
Storage Size	Takes no. of bytes equal to the no. of Characters entered plus two bytes extra for defining offset.	Takes no. of bytes equal to twice the no. of Characters entered plus two bytes extra for defining offset.

As both of these are variable length datatypes, so irrespective of the length (i.e. optional parameter n value) defined in the variable declaration/column definition it will always take the no. of bytes required for the actual charcters stored. The value of n defines maximum no. of characters that can be stored.

Read the article Difference between Sql Server Char and Varchar Data Type to understand in detail the difference between the fixed and variable length data type.