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 are the DBCC commands that are commonly use for database maintenance

DBCC CHECKDB, DBCC CHECKTABLE, DBCC CHECKCATALOG, DBCC CHECKALLOC, DBCC SHOWCONTIG, DBCC SHRINKDATABASE, DBCC SHRINKFILE etc. But there are a whole load of DBCC commands which are very useful for DBAs. Check out SQL Server books online for more information.

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

SQL SERVER 2008 – How to rename a column

There are many ways where in we can  rename  a column but what I prefer is using predefined stored Procedure sp_rename .

Syntax :

exec sp_rename ‘tablename.ColumnName’, ‘NewColumnName’, ‘column’ –objecttype

EX: changing the column name “Name” to “FirstName”

create table Personal(ID int, Name varchar(50))

EXEC sp_rename ‘Personal.Name’, ‘FirstName’, ‘column’

Output:

The message displayed is a caution message that says when we alter the column name there is a possibility that it can be used in one or more stored procedures so make sure you update everything and then only change the name .

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 – What is the difference of a LEFT JOIN and an INNER JOIN statement

A LEFT JOIN will take ALL values from the first declared table and matching values from the second declared table based on the column the join has been declared on. An INNER JOIN will take only matching values from both tables

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

SQL SERVER – What is a synonym and How is it used

A synonym is used to reference a table or view by another name.  The other name can then be written in the application code pointing to test tables in the development stage and to production entities when the code is migrated.  The synonym is linked to the AUTHID that created it.

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