Interview Questions of SQLServer

Do you know what are User Defined functions.

Scalar, Inline Table-Valued and Multi-statement Table-valued.

A Scalar user-defined function returns one of the scalar data types. Text, ntext, image and timestamp data types are not supported. These are the type of user-defined functions that most developers are used to in other programming languages. You pass in 0 to many parameters and you get a return value.

An Inline Table-Value user-defined function returns a table data type and is an exceptional alternative to a view as the user-defined function can pass parameters into a T-SQL select command and in essence provide us with a parameterized, non-updateable view of the underlying tables.

A Multi-Statement Table-Value user-defined function returns a table and is also an exceptional alternative to a view as the function can support multiple T-SQL statements to build the final result where the view is limited to a single SELECT statement.
 

Can I return a table from a user defined function?
Yes

If I return a table from user defined function, can I join that table with another table?
Yes

What are differences between a function and SP
Return Value, and not exec
Not mandatory to return value, executable

What are views
Virtual Tables

What are advantages of a View?
More Performance

Why would I need to go for a view?
Instead of writing query in sp, you just create a view and call that obj in sp.. good performance

Can I create a view with Order by clause
No, because View is also a virtual table, so you can order the result set of View

Diff between Delete and Truncate table
TRUNCATE is a DDL command whereas DELETE is a DML command.

TRUNCATE is much faster than DELETE.
Reason:When you type DELETE.all the data get copied into the Rollback Tablespace first.then delete operation get performed.Thatswhy when you type ROLLBACK after deletinga table you can get back the data(The system get it for you from the Rollback Tablespace).All this process take time.But when you type TRUNCATE it removes data directly without copying it into the Rollback Tablespace.Thatswhy TRUNCATE is faster.Once you Truncate you cann't get back the data.
You cann't rollback in TRUNCATE but in DELETE you can rollback.TRUNCATE removes the record permanently.
In case of TRUNCATE Trigger doesn't get fired.But in DML commands like DELETE .Trigger get fired.
You cann't use conditions(WHERE clause) in TRUNCATE.But in DELETE you can write conditions using WHERE clause.

What is a temporary table
table with a pound sign (#). This tells SQL Server that this table is a local temporary table. This table is only visible to this session of SQL Server. When I close this session, the table will be automatically dropped. You can treat this table just like any other table with a few exceptions. The only real major one is that you can't have foreign key constraints on a temporary table
If stored procedure A creates a temporary table and calls stored procedure B, then B will be able to use the temporary table that A created
If I create a temp table the memory allocated in Tempdb

what is the diff between temp table and Variable table
    Table variables don't need to be dropped when you are done with them.
If I want to creaate a where condition dynamically and then assign it to Select statement how do I do it and how can I execute it

If you have less than 100 rows generally use a table variable.  Otherwise use  a temporary table.  This is because SQL Server won't create statistics on table variables.
If you need to create indexes on it then you must use a temporary table.
When using temporary tables always create them and create any indexes and then use them.  This will help reduce recompilations.  The impact of this is reduced starting in SQL Server 2005 but it's still a good idea.
 

what are differrent contraints

Domain Integerity Constraints:
Check: Limits the range of values or domain, checks the values which a col can have.
Referntial Integrity Constraints:
Foreign Key: Referntial integrity b/w 2 tables, one table col values based on other table col values.
Entity Integrity Constraints:
Default Constraint: defines the default value for a column.
Null: allows null value.
NOT null:Donot allow null value.
Primary Key: Ensure no duplicate data and not allow nulls.
Unique Key: donot allow duplicate values, but allows one null per col.Can have multiple unique constraints on a table

What is rank keyword in SQL Server.
Rank() Applies to monotonically increasing number for each value in the set
Rank() and Row_Number differ only if there are ties. (rank same for two rows.. next rows has incremental)
In Dense_Rank() Method the rank is increasing number for each value in the set.
NTILE(n)- Which splits the records into partition based on the Column in the Over() and assigns the Rank for the partition.

What is OLAP?
Online Analytical Processing (OLAP) allows us to access aggregated and organized data from business data sources, such as data warehouses, in a multidimensional structure called a cube. The arrangement of data into cubes avoids a limitation of relational databases which are not well suited for near instantaneous analysis of large amounts of data.

What is OLTP?
Online Transaction Processing (OLTP) relational databases are optimal for managing changing data. When several users are performing transactions at the same time, OLTP databases are designed to let transactional applications write only the data needed to handle a single transaction as quickly as possible.

What is SQL Server Agent?
SQL Server Agent is a Microsoft Windows service that executes scheduled administrative tasks called jobs.
we have roles created for using SQL Server agents.
·         SQLAgentUserRole
·         SQLAgentReaderRole
·         SQLAgentOperatorRole

Replication is a set of technologies for copying and distributing data and database objects from one database to another and then synchronizing between databases to maintain consistency.

Database Mirroring – Database Mirroring is moving the transactions of database from one SQL Server database to another SQL server database on a different SQL Server.

CheckPoint
When we done operation on SQL SERVER that is not commited directly to the database example:we are deleting bulk records from server but in between power shut down or some thing happened then that transaction is not committed from our side if we create check point on that delete statement after deleting some records it will save as check point next no need to delete those records again if check point is not there means we have to start form scratch.
All operation must be logged in to Transaction Log files after that they should be done on to the main database.CheckPoint are the point which alert Sql Server to save all the data to main database if no Check point is there then log files get full we can use Checkpoint command to commit all data in the SQL SERVER.
When we stop the SQL Server it will take long time because Checkpoint is also fired.