SQL SERVER – What is @@Rowcount, @@Error and @@Identity

@@Rowcount is used to display the number of rows affected by last SQL statement.

@@Error displays the error number for the last SQL statement executed. The value is zero,if there is no error.

@@identity returns the last inserted identity value.

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

Advertisements

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 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 2008 – What is the difference betweeen Execute and sp_executesql

sp_executesql and Execute statement are used to execute a sql stored Procedure. The execute statement doesn’t support parameter substitution in the executed string.

According to Sql server Book Online Link Here.

‘To execute a string, we recommend that you use the sp_executesql stored procedure instead of the EXECUTE statement. Because this stored procedure supports parameter substitution, sp_executesql is more versatile than EXECUTE; and because sp_executesql generates execution plans that are more likely to be reused by SQL Server, sp_executesqlis more efficient than EXECUTE.’

So the best thing to use is sp_executesql but depends according to the requirement.

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

SQL SERVER – Default sizes for databases

Master database – Stores system files – 11MB

Model database – Template for all databases – 0.75MB

Temp database – Stores temporary objects – 8MB

Msdb database – Used by SQL server agents for scheduling alerts and jobs and recording operators- 12MB

The min. value for DB size is 512KB

The default size for DB is 1MB.

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