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:
Question 3: What is .MDF, .LDF and .NDF file in SQL Server?
Answer: SQL Server databases have three types of files:
- 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)
- 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)
- 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.