SQL Server Interview Questions - Part 1

Question 1. How to Swap the values of two columns in SQL Server ?

Answer: Suppose that we have a table with records in database as below:

Now how could you solve this tricky task? The solution is very simple, we need to perform this through a query:

UPDATE Product1 SET vProductName=vManfacturer , vManfacturer=vProductName

The solution is shown below after completing the update query: 



Note: Data type of both the columns should be same and also length should be enough to hold the swapped column data otherwise data will be truncated.

 

Question 2: How do we find out total free space in Server?

Answer: To find out Server available disk space follow the below query:

Query:

xp_fixeddrives

 

 

Question 3: What is .MDF, .LDF and .NDF file in SQL Server?

Answer: SQL Server databases have three types of files:

1. Primary data files: It is the starting point of the database and points to the other files in the database. Every database has one primary data file.
   
   Extension: .mdf. (master data file)
   
   
2. Secondary data files: It comprise of the data files other than the primary data file. Some databases may not have any secondary data files, while others have multiple secondary data files.
   
   Extension: .ndf.( next data file)
   
   
3. Log files: It holds all of the log information used to recover the database. There must be at least one log file for each database, although there can be more than one.
   
   Extension: .ldf.( log data file)

SQL Server does not enforce the .mdf, .ndf, and .ldf file name extensions, but these extensions are recommended to help identify the use of the file.

Example: Click on database properties then select files option:
  

Question 4: Why do we use Cast function?

Answer: Cast function is used to convert Datatype of expression into other Datatypes.

Syntax: CAST( Expression AS type into (New Datatype ) )  

Example 1:

SELECT CAST(14.85 AS int);

In the following output the Cast Function is converting 14.85 datatype value into new datatype provided format as of Integer value output.

Example 2. In the following pic I used Getdate function with date with above output. Now I used Cast function that will change the getdate output into Varchar Output.



Question 5: How to insert values to identity column in SQL Server?

Answer: Identity field is usually used as a Primary Key. When you insert a new record into your table, it will automatically assign an incremented value from the previous entry.

Syntax:  IDENTITY [(seed, Increment)] Note: Seed is the Initial Value)  

Example:

CREATE TABLE Customer ( ID int IDENTITY, Name varchar(100), Address varchar(200) )

By default it will start from 1.

Or

CREATE TABLE new_employees ( id_num int IDENTITY(100,15), fname varchar (20), lname varchar(30))

Note: In this case the Seed will start with 100 and Increment value given is 15.

We can perform this through Table design: Select Database Table, then right click on Design option: Select the Column Name, in Column Properties click Yes and provide seed and increment in it.

Question 6: How to use SELECT INTO statement?

Answer: We use SELECT INTO statement to copy an existing table data into a new table creation.

Syntax:  SELECT ( * or elements) INTO Newtable_Name FROM tables WHERE conditions;   
  
Example: I have a already created table with name CustomerOrders under database (Trainee) as seen below:




Now I am using Select Into Statement:

SELECT * INTO India FROM CustomerOrders WHERE Amount=20.12

This will create a New Table with the name India with all data of CustomerOrders row where Amount=20.12 :
You can see dbo.India is created with output shown below.

Note: The new_table must not already exist. If it does already exist, the SELECT INTO statement will raise an error.