We have a table with a CHARACTER data type field. We apply a “>” row comparison between this field and another CHARACTER field in another table. What will be the results for records with field value of NULL

This is something  so many asked me because I also faced the same situation before, so having a NULL in a row when compared will give an UNKNOWN result.

The result will not be correct hence creates an ambiguity.

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

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 )

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 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 )

 

SQL SERVER – What is subselect and Is it different from a nested select

Subselect is a select which works in conjunction with another select. A nested select is a kind of subselect where the inner select passes to the where criteria for the outer select.

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

SQL SERVER 2008 – Difference between a subselect to a join

Any subselect can be rewritten as a join, but not vice versa.

Joins are usually more efficient as join rows can be returned immediately, subselects require a temporary work area for inner selects results while processing the outer select.

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

 

SQL SERVER 2008 – What is an advantage to using a stored procedure as opposed to passing an SQL query from an application

A stored procedure is pre-loaded in memory for faster execution. It allows the DBMS control of permissions for security purposes. It also eliminates the need to recompile components when minor changes occur to the database.

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