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:


a1

Now how could you solve this tricky task? The solution is very simple, we need to perform this through a query:
  1. UPDATE Product1 SET vProductName=vManfacturer , vManfacturer=vProductName

a2

The solution is shown below after completing the update query:

a3

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:
  1. xp_fixeddrives
a4


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:

a5

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:
  1. SELECT CAST(14.85 AS int);
a6

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.

a7

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:
  1. CREATE TABLE Customer ( ID int IDENTITY, Name varchar(100), Address varchar(200) )
By default it will start from 1.

Or
  1. 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:

a9


Now I am using Select Into Statement:
  1. 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.

a10

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