Basic - Fresher Level

Q. What is SQL Server?
A. SQL Server is the flagship database from Microsoft. It is the one of the top database used by top enterprises for business needs.

Q. What authentication modes are available in SQL Server?
A. There are two authentication modes in SQL Server:

• Windows mode
• Mixed Mode

Windows mode is used when authentication is ment to be using local system user. In mixed mode, windows and SQL server authentication, both are allowed.

Q. What is a primary key?
A. A Primary key is a column (or collection of columns) or a set of columns that uniquely identifies each row in the table. Null values are not allowed in primery key.

Q. What is foreign key?
A. Foreign key is used to reference the primary key of other table. In this way foreign key is used to define relationship between two tables. 

Q. What are temporary tables in SQL Server?
A. Temporary table are following types:

• Local temp table
• Global temp table

Local temporary tables are the tables stored in tempdb. They use prefix # in table name, eg:  #table_name. These are temporary tables that are available only to the session that created them. These tables are automatically destroyed at the termination of the procedure or session. These temp tables can be created with the same name in multiple windows.

Global temporary table are same like local table, but they are available in multiple sessions. They use prefix ## in table name, eg:  ##table_name.

Q. How table variable is different from temp or regular table?
A. Table variable shares some common characteristics with temp table, like stored in tempdb. Following are the points which differentiate it with temp or regular table:

• Table variable must start from @ and it should be declared like a variable, eg: DECLEAR @table_name.
• Table variables cannot have Non-Clustered Indexes.
• You cannot create constraints in table variables.
• You cannot create default values on table variable columns.

A table variable’s lifespan is the duration it is executed with creating. So say you created a table variable in step one, and try to insert value in step two, then it will give you error. You have to perform both queries in one step, i.e. in one run or execution. So in case of following script, if you run all of them on one run, it will work, but if you will run it in steps, like you selected declare and insert in one step, and then again only selected the select query and run, then second query will not run.

-- Declare a table variable
DECLARE @Heros TABLE 
   ( 
   HeroId int NOT NULL, 
   ModelName varchar(20), 
   DateCreated datetime 
   ) 

-- Inserting data.
INSERT INTO @Heros (HeroId, ModelName, DateCreated) 
VALUES (1,'Naagraj', GETDATE()) 

SELECT HeroId, ModelName, DateCreated FROM @Heros

Q. What is difference between CTE and table variable?
A. CTE stands for common table expression. It is used in complex sub queries to hold temporary data. It starts with semi-colon. CTE is available for only that script scope, in which it’s create.

-- First query, 
;With CTE1(Name, Age, OrderNumber)
AS
(
SELECT Per.Name, Per.Age, Odr.OrderNumber from Person Per
INNER JOIN Orders Odr ON Per.ID = Odr.PID
)	
SELECT * FROM CTE1 --Using CTE 
WHERE CTE1.Age > 22

--Second query, this will not work
SELECT * FROM CTE1

Q. What is difference between stored procedure and function?
A. Following are the difference between stored procedure and function:

• Functions must return a value or table, while stored procedure are not bound to that condition.
• Functions only work with input parameters, while there could be no parameter in stored proc.
• Try and catch statements are not used in functions.
• Functions compile every time.

Q. In what sequence SQL statement are processed?
A. SQL statement are processes in following sequence:

• FROM [Table]
• WHERE [Condition]
• GROUP BY [Fields]
• HAVING [Condition]
• SELECT [Fields]
• ORDER BY [Fields]
• TOP [Number]