Sql Server Interview Questions and Answers

1) SQL Server query to split the Field into 2 Column. This query the developer can use when the name consists of numeric value like Ram1, Ram2 SELECT Left(Lastname, 3) As characters, Right(Lastname, 1) As numbers         FROM   [Amatya].[dbo].[tblUsers] Note – First 3 Character will be… Continue reading

Top Sql Server Interview Questions and their Answers

1)  How to create an empty table emp1 with same structure as emp?  Create table emp1 as select * from emp where 1=2; 2)  How to find the Max Salary from each department? Select D.DepName, MAX(E.Salary) as Salary from tbl_Emp E        Inner Join tbl_Department D on E.Fk_DepId… Continue reading

Tips to improve performance of Database Operations

Return Multiple ResultsetsThe database code if has request paths that go to the database more than once then, these round-trips decreases the number of requests per second your application can serve.Solution:Return multiple resultsets in a single database request, so that you can cut the total time spent communicating with the… Continue reading

Major Points when Creating Indexes for a database

Best practices to follow when creating Indexes for a database. Examine the WHERE clause and JOIN criteria columns : SQL server Query optimizers looks for indexes on the columns specified in the WHERE clause and the JOIN criteria and if the indexes are available on these columns then optimizer considers Indexes when creating… Continue reading

What is fillfactor? What is the use of it ? What happens when we ignore it? When you should use low fill factor?

When you create a clustered index, the data in the table is stored in the data pages of the database according to the order of the values in the indexed columns. When new rows of data are inserted into the table or the values in the indexed columns are changed,… Continue reading

What are statistics, under what circumstances they go out of date, how do you update them?

Statistics determine the selectivity of the indexes. If an indexed column has unique values then the selectivity of that index is more, as opposed to an index with non-unique values. Query optimizer uses these indexes in determining whether to choose an index or not while executing a query. Some situations… Continue reading