File 3


Friday, July 8, 2011

1) Define some facts about Temporary table and there types
ans: Temporary table name is limited t 116 characters. Local temporary table created with single # and then name of table.
And this table is to be deleted explicitly and if it is created in store procedure it will be dropped at the end of store procedure.
On the other hand if are talking abut Global variable tables start with ## and these are dropped on the session ends.
And some more facts about the Temporary tables is that these cannot be partitioned and we c
annot create key constraints to these tables and one more thing is that we cannot create user-defined data types in tempdb.

2) What are the different index configurations a table can have
ans: (1)No indexes
(2)A clustered index
(3)A clustered index and many nonclustered indexes
(4)A nonclustered index
(5)Many nonclustered indexes

3)What do you mean by KEYSET Cursor
ans: KEYSET Cursor uses the set of keys that are primary key or we can saw uniquely identify the cursor's rows.
SQL Server uses a table in tempdb to store keyset. The KEYSET cursor helps to updates non key values from being made through this cursor,
but when inserts made by other users are not visible. Updates nonkey values made by other users are visible as the owner scrolls around the cursor,
but updates key values made by other users are not visible.

3)Here are some instruction when creating a store procedure to increase speed
ans: Here are the some good tips when creating a store procedure

(1)Always use ANSI92 syntax avoid to use the old syntax.
(2)Use SQL keyword in capital letters to increase readability.

(3)Use few possible variables to increase cache memory.
(4)Try to avoid dynamic queries if we are not using dynamic query there is no recompilation of execution plan but on the other side
if we are using dynamic query every time we need recompile of plan.

(5)Use SET NOCOUNT ON this will helps us to get number of row effected without network traffic.
(6)To avoid recompilations use OPTION KEEPFIXED PLAN.

(7)In Select and Set use select to assign value to variable it is much faster than multiple set statement.
(8)Try to avoid IN. IN counts Null values so use EXISTA there. EXISTS which return only boolean value and IN return heavier result than EXISTS.

(9)In CAST and CONVERT always try to use CAST it is ASNI92 standard.Use convert in case of datetime.
(10)Avoid Distinct and Order by clause.These class needs extra space.

(11)Avoid cursor so use while loop for that and temparory tables.
(12)Avoid to use * in select statement.

(13)Avoid correlated sub queries.
(14)Avoid select * into for large tables it locks the system objects.

(15)Avoid temporary tables because it will recompile the procedure.
4)Size of Clustered NonClustered in 2005 and 2008
ans: In SQL 2005 and earlier there was a limitation of 250 indexes per table, one clustered and 249 non-clustered. In SQL 2008,
with the addition of filtered indexes, that limitation was increased to 1000, one clustered and 999 non-clustered indexes.

5)Difference between Set and Select
ans: Set is a ANSI standard for variable assignment.

Select is a Non-ANSI standard when assigning variables.
We can assign only one variable at a time

We can assign multiple variable at a time
When assigning from a query that returns more than one value, SET will fail with an error.

When assigning from a query that returns more than one value, SELECT will assign the last value returned by the query and hide the fact that the query returned

6)How many different locks in SQL SERVER
ans: (1)Intent

(2)shared
(3)Update

(4)Exclusive
(5)Schema

(6)Bulk Update
7)What is the use of OLAP
ans: OLAP(On-Line Analytical Processing) is useful because it provides fast and interactive access to aggregated data and the ability to drill down to detail.

8)What do you mean by Filtered indexes
ans:Filtered indexes are a new feature in SQL 2008 and they allow for an index to contain only some of the rows in the table. Prior to this,
an index would always have, at the leaf level, the same number of rows as the table. With filtered indexes, an index can be based on just a subset of the rows.
When creating a filtered index, a predicate is specified as part of the index creation statement. There are several limitations on this predicate. The comparison ca
nnot reference a computed column, a column that is declared as a user-defined type, a spatial column or a hierarchyid column.
A clustered index cannot be filtered as it is the actual table.

9)What are Sparse Columns in Sql Server2008
ans:sparse column is a tool that helps to reduce amount of physical storage used in a database.
These are ordinary columns that have an optimized storage for all null values.SPARSE column are better at managing NULL and ZERO values in SQL Server.
It does not take any space in database at all.

10)Difference between Triggers and Storedprocedures
Triggers are basically used to implement business rules.Triggers is also similar to stored procedures.
The difference is that it can be activated when data is added or edited or deleted from a table in a database.
Triggers are special kind of stored procedures that get executed automatically when an INSERT,UPDATE or DELETE operation takes place on a table.

11)Diffrence between temp table and table variable
(1)Temp Tables are created in the SQL Server TEMPDB database and therefore require more IO resources and locking.
Table Variables and Derived Tables are created in memory.

(2)Temp Tables will generally perform better for large amounts of data that can be worked on using parallelism whereas
Table Variables are best used for small amounts of data (I use a rule of thumb of 100 or less rows) where parallelism would not provide
a significant performance improvement.
(3)You cannot use a stored procedure to insert data into a Table Variable or Derived Table.
For example, the following will work: INSERT INTO #MyTempTable EXEC dbo.GetPolicies_sp whereas the following will generate an error:
INSERT INTO @MyTableVariable EXEC dbo.GetPolicies_sp.

(4)Derived Tables can only be created from a SELECT statement but can be used within an Insert, Update, or Delete statement.
(5) In order of scope endurance, Temp Tables extend the furthest in scope, followed by Table Variables, and finally Derived Tables.

12)Diffrence between varchar and nvarchar
An nvarchar column can store any Unicode data. A varchar column is restricted to an 8-bit codepage. Some people think that varchar should be used
because it takes up less space. I believe this is not the correct answer. Codepage incompatabilities are a pain, and Unicode is the cure for codepage problems.
With cheap disk and memory nowadays, there is really no reason to waste time mucking around with code pages anymore.

13)What is SQL injection
SQL injection is a security vulnerability that occurs in the database layer of an application. The vulnerability is present when user input is
either incorrectly filtered for string literal escape characters embedded in SQL statements or user input is not strongly typed and thereby unexpectedly executed.
It is in fact an instance of a more general class of vulnerabilities that can occur whenever one programming or scripting language is embedded inside another.

14)What are Checkpoint in SQL Server
When we done operation on SQL SERVER that is not commited directly to the database.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.

15)What is the difference between UNION ALL Statement and UNION
The main difference between UNION ALL statement and UNION is UNION All statement is much faster than UNION,the reason behind this is that
because UNION ALL statement does not look for duplicate rows, but on the other hand UNION statement does look for duplicate rows, whether or not they exist.

16)Write some disadvantage of Cursor
Cursor plays there row quite nicely but although there are some disadvantage of Cursor . Because we know cursor doing roundtrip it will make
network line busy and also make time consuming methods. First of all select query gernate output and after that cursor goes one by one so roundtrip
happen.Another disadvange of cursor are ther are too costly because they require lot of resources and temporary storage so network is quite busy.

17)What is different in Rules and Constraints
Rules and Constraints are similar in functionality but there is a An little diffrence between them.Rules are used for backward compatibility .
One the most exclusive diffrence is that we an bind rules to a datatypes whereas constraints are bound only to columns.So we can create our own
datatype with the help of Rules and get the input according to that.

18)What is the difference between a HAVING CLAUSE and a WHERE CLAUSE
Having Clause is basically used only with the GROUP BY function in a query. WHERE Clause is applied to each row before they are part of the
GROUP BY function in a query.

19)What are the advantage of User Defined function over store procedure
There are no of benefits of SQL Server User-Defined functions.Some of these are here we can use these functions in so many different places with
comparison to SQL Server stored procedure.Two of user define function acts like a table (Inline and Multi-statement functions) helps developers to
reduce the code and break complex logic in short code blocks.On the other hand Scalar User-Defined Function have ability so that we use this function
anywhere where we
need some single value result or some of opertion.Combining these advantages with the ability to pass parameters into these database objects makes
the SQL Server User-Defined function a very powerful tool.

20)

No comments :