SQL SERVER – CREATE INDEX myIndex ON myTable(myColumn)What type of Index will get created after executing the above statement

Non-clustered index. Important thing to note: By default a clustered index gets created on the primary key, unless specified otherwise.

Reference : Dilip Kumar Jena ( https://sqlexplore.wordpress.com )

Advertisements

SQL SERVER – What is the difference between group by and order by

Group by controls the presentation of the rows, order by controls the presentation of the columns  for the results of the SELECT statement.

EX 1 : Group By helps us display any aggregate of any column based on a field what has repeated names.

use AdventureWorksDW2008R2

go

select Title,SUM(BaseRate) as Rate  from dbo.DimEmployee group by Title

Output:

EX 2 : Order By clause helps us display any table based on the values present on that particular column.

use AdventureWorksDW2008R2

go

select FirstName,Title  from dbo.DimEmployee order by FirstName

the difference is how we choose and what is the reuirement.

Reference : Dilip Kumar Jena ( https://sqlexplore.wordpress.com )

SQL SEREVER – What is a View and An Indexed View

 A view  is a virtual tables that is made up of one or more tables. It can be used for security purpose, hiding complex queries.

An Indexed view is a view that has unique clustered index created on it. This takes physical storage i.e it stores data. It will be used in OLAP, decision support where inserts/updates are low. It can be used in the following scenarios

1) Joins and aggregation of big tables.

2) Repeated joins of the same tables on the same keys.

Reference : Dilip Kumar Jena ( https://sqlexplore.wordpress.com )

SQL SERVER – How many tables can SQL SERVER have and how many columns per table.

Sql server can have 2 billion tables per tables and 1024 columns per table.

Reference : Dilip Kumar Jena ( https://sqlexplore.wordpress.com )

SQL SERVER – What is the difference between static and dynamic sql

Static sql is compiled and optimized prior to its execution; dynamic is compiled and optimized during execution.

Reference : Dilip Kumar Jena ( https://sqlexplore.wordpress.com )

SQL SERVER – What is an alias and how does it differ from a synonym

An alias is an alternative to a synonym, designed for a distributed environment to avoid having to use the location qualifier of a table or view.  The alias is not dropped when the table is dropped.

Reference : Dilip Kumar Jena ( https://sqlexplore.wordpress.com )

SQL SERVER 2008 – There is a trigger defined for INSERT operations on a table, in an OLTP system. The trigger is written to instantiate a COM object and pass the newly insterted rows to it for some custom processing. What do you think of this implementation? Can this be implemented better

Instantiating COM objects is a time consuming process and since you are doing it from within a trigger, it slows down the data insertion process. Same is the case with sending emails from triggers. This scenario can be better implemented by logging all the necessary data into a separate table, and have a job which periodically checks this table and does the needful.
Reference : Dilip Kumar Jena ( https://sqlexplore.wordpress.com )