Configure Database mail using T SQL script

-- using T-SQL

use msdb
declare @profile_id smallint
exec msdb.dbo.sp.sysmail_add_profile_sp
@profile_name=’DATABASEMAIL’,
@description=’DATABASEMAIL’,
@profile_id=@profile_id output;
select @profile_id
--–exec msdb.dbo.sysmail_help_profile_sp select * from msdb.dbo.sysmail_profile
declare @account_id int
execute msdb.dbo.sysmail_add_account_sp
@account_name=’samardeep’,
@email_address=’samardeep.skhera@diaspark.com’,
@display_name=’samardeep’,
@description=’Test Mail’,
@account_id=@account_id output;
select @account_id
/* to update
declare @account_id int
execute msdb.dbo.sysmail_update_account_sp
@account_id=1,
@account_name=’test’,
@email_address=’samae_99@gmail.com’,
@display_name=’ElixirCT’,
@description=’test mail’,
@replyto_address=’samae_99@gmail.com’,
@mailserver_name=’smtp.gmail.com’,
@mailserver_type=’SMTP’,
@port=25,
@username=samae_99@gmail.com,
@password=password,
@use_default_credentials=0,
@enable_ssl=0;
exec sysmail_help_account_sp
*/
–select * from sysmail_account exec sysmail_help_account_sp
–exec sysmail_help_profile_sp exec sysmail_help_account_sp
/*
exec msdb.dbo.sysmail_add_profileaccount_sp
@profile_id=1,
@account_name=’samardeep’,
@sequence_number=1
–or
@account_id=1,
@profile_name=’DATABASEMAIL’,
@sequence_number=2
*/
–exec sysmail_help_profileaccount_sp
–make the profile public
exec sysmail_add_principalprofile_sp
@profile_name=’DATABASEMAIL’,
@profile_id=1,
@is_default=0,
@principal_name=’public’
–exec sysmail_help_principalprofile_sp
exec msdb.dbo.sp_send_dbmail
@profile_name=’DATABASEMAIL’,
@recipients=’samae_99@yahoo.com’,
@subject=’hello…test mail’,
@body=’hi,
h r u’
SELECT * FROM sysmail_allitems


Enjoy

Difference beween SQLMail and Database mail

Its very small but very important to know difference between these two features of SQL Server. The difference between Database Mail and SQLMail is very confusing sometime. I will try to explain this concept below -

Database mail is newly introduced concept in SQL Server 2005 and it is replacement of SQLMail of SQL Server earlier version.

Database Mail has many improvements over SQLMail. Database Mail is based on SMTP (Simple Mail Transfer Protocol) and also very fast and reliable where as SQLMail is based on MAPI (Messaging Application Programming Interface).

Database mail depends on Service Broker so this service must be enabled for Database Mail.

Database Mail can be encrypted for additional security.

SQLMail is lesser secure as it can encrypt the message as well anybody can use SMTP to send email. Additionally, for MAPI to be enabled for SQLMail it will require Outlook to be installed.All this leads to potential security threat to database server.

So its good if you are using SQLMail, but this is right time to upgrade to Database Mail.

If you want to use the old SQLMail you can enable it with these commands.

EXEC sp_configure 'SQL Mail XPs', 1
GO
RECONFIGURE
GO

Hope this helps

Enjoy

SSAS Interview Questions

Q: What is Analysis Services? List out the features?
Microsoft SQL Server 2005 Analysis Services (SSAS) delivers online analytical processing (OLAP) and data mining functionality for business intelligence applications. Analysis Services supports OLAP by letting we design, create, and manage multidimensional structures that contain data aggregated from other data sources, such as relational databases. For data mining applications, Analysis Services lets we design, create, and visualize data mining models that are constructed from other data sources by using a wide variety of industry-standard
data mining algorithms.
Analysis Services is a middle tier server for analytical processing, OLAP, and Data mining. It manages multidimensional cubes of data and provides access to heaps of information including aggregation of data. One can create data mining models from data sources and use it for Business Intelligence also including reporting features.
Analysis service provides a combined view of the data used in OLAP or Data mining. Services here refer to OLAP, Data mining. Analysis services assists in creating, designing and managing multidimensional structures containing data from varied sources. It provides a wide array of data mining algorithms for specific trends and needs.

Some of the key features are:

• Ease of use with a lot of wizards and designers.
• Flexible data model creation and management
• Scalable architecture to handle OLAP
• Provides integration of administration tools, data sources, security, caching, and reporting etc.
• Provides extensive support for custom applications

Q: What is UDM? Its significance in SSAS?
The role of a Unified Dimensional Model (UDM) is to provide a bridge between the user and the data sources. A UDM is constructed over one or more physical data sources, and then the end user issues queries against the UDM using one of a variety of client tools, such as Microsoft Excel. At a minimum, when the UDM is constructed merely as a thin layer over the data source, the advantages to the end user are a simpler, more readily understood model of the data, isolation from heterogeneous backend data sources, and improved performance for summary type queries. In some scenarios a simple UDM like this is constructed totally automatically. With greater investment in the construction of the UDM, additional benefits accrue from the richness of metadata that the model can provide.

The UDM provides the following benefits:
• Allows the user model to be greatly enriched.
• Provides high performance queries supporting interactive analysis, even over huge data volumes.
• Allows business rules to be captured in the model to support richer analysis.

Q: What is the need for SSAS component?

• Analysis Services is the only component in SQL Server using which we can perform Analysis and Forecast operations.
• SSAS is very easy to use and interactive.
• Faster Analysis and Troubleshooting.
• Ability to create and manage Data warehouses.
• Apply efficient Security Principles.

Q: Explain the TWO-Tier Architecture of SSAS?

• SSAS uses both server and client components to supply OLAP and data mining functionality BI Applications.
• The server component is implemented as a Microsoft Windows service. Each instance of Analysis Services implemented as a separate instance of the Windows service.
• Clients communicate with Analysis Services using the standard the XMLA (XML For Analysis) , protocol for issuing commands and receiving responses, exposed as a web service.

Q: What are the components of SSAS?

• An OLAP Engine is used for enabling fast ad hoc queries by end users. A user can interactively explore data by drilling, slicing or pivoting.
• Drilling refers to the process of exploring details of the data.
• Slicing refers to the process of placing data in rows and columns.
• Pivoting refers to switching categories of data between rows and columns.
• In OLAP, we will be using what are called as Dimensional Databases.

Q: What is FASMI ?
A database is called a OLAP Database if the database satisfies the FASMI rules :

• Fast Analysis– is defined in the OLAP scenario in five seconds or less.
• Shared – Must support access to data by many users in the factors of Sensitivity and Write Backs.
• Multidimensional – The data inside the OLAP Database must be multidimensional in structure.
• Information – The OLAP database Must support large volumes of data..

Q: What languages are used in SSAS ?

• Structured Query Language (SQL)
• Multidimensional Expressions (MDX) - an industry standard query language orientated towards analysis
• Data Mining Extensions (DMX) - an industry standard query language oriented toward data mining.
• Analysis Services Scripting Language (ASSL) - used to manage Analysis Services database objects.

Q: How Cubes are implemented in SSAS ?

• Cubes are multidimensional models that store data from one or more sources.
• Cubes can also store aggregations
• SSAS Cubes are created using the Cube Wizard.
• We also build Dimensions when creating Cubes.
• Cubes can see only the DSV( logical View).

Q: What is the difference between a derived measure and a calculated measure?
The difference between a derived measure and a calculated measure is when the calculation is performed. A derived measure is calculated before aggregations are created, and the values of the derived measure are stored in the cube. A calculated measure is calculated after aggregations are created, and the values of a calculated measure aren’t stored in the cube. The primary criterion for choosing between a derived measure and a calculated measure is not efficiency, but accuracy.

Q: What is a partition?
A partition in Analysis Services is the physical location of stored cube data. Every cube has at least one partition by default. Each time we create a measure group, another partition is created. Queries run faster against a partitioned cube because Analysis Services only needs to read data from the partitions that contain the answers to the queries. Queries run even faster when partition also stores aggregations, the pre calculated totals for additive measures. Partitions are a powerful and flexible means of managing cubes, especially large cubes.

Q: While creating a new calculated member in a cube what is the use of property
called non-empty behavior?
Nonempty behavior is important property for ratio calculations. If the denominator Is empty, an MDX expression will return an error just as it would if the denominator Were equal to zero. By selecting one or more measures for the Non-Empty Behavior property, we are establishing a requirement that each selected measure first be evaluated before the calculation expression is evaluated. If each selected measure is empty, then The expression is also treated as empty and no error is returned.

Q: What is a RAGGED hierarchy?
Under normal circumstances, each level in a hierarchy in Microsoft SQL Server 2005 Analysis Services (SSAS) has the same number of members above it as any other member at the same level. In a ragged hierarchy, the logical parent member of at least one member is not in the level immediately above the member. When this occurs, the hierarchy descends to different levels for different drilldown paths. Expanding through every level for every drilldown path is then unnecessarily complicated.

Q: What are the roles of an Analysis Services Information Worker?

The role of an Analysis Services information worker is the traditional "domain expert" role in business intelligence (BI) someone who understands the data employed by a solution and is able to translate the data into business information. The role of an Analysis Services information worker often has one of the following job titles: Business Analyst (Report Consumer), Manager (Report Consumer), Technical Trainer, Help Desk/Operation, or Network Administrator.

Q: What are the different ways of creating Aggregations?
We can create aggregations for faster MDX statements using Aggregation Wizard or thru UBO – Usage Based Optimizations. Always, prefer UBO method in realtime performance troubleshooting.

Q: What is WriteBack? What are the pre-conditions?
The Enable/Disable Writeback dialog box enables or disables writeback for a measure group in a cube. Enabling writeback on a measure group defines a writeback partition and creates a writeback table for that measure group. Disabling writeback on a measure group removes the writeback partition but does not delete the writeback table, to avoid unanticipated data loss.

Q: What is processing?
Processing is a critical and resource intensive operation in the data warehouse lifecycle and needs to be carefully optimized and executed. Analysis Services 2005 offers a high performance and scalable processing architecture with a comprehensive set of controls for database administrators.
We can process an OLAP database, individual cube, Dimension or a specific Partition in a cube.

Q: What MDX functions do you most commonly use?
This is a great question because you only know this answer by experience. If you ask me this question, the answer practically rushes out of me. “CrossJoin, Descendants, and NonEmpty, in addition to Sum, Count, and Aggregate. My personal favorite is CrossJoin because it allows me identify non-contiguous slices of the cube and aggregate even though those cube cells don’t roll up to a natural ancestor.” Indeed, CrossJoin has easily been my bread and butter.

Q: Where do you put calculated members?
The reflexive answer is “in the Measures dimension” but this is the obvious answer. So I always follow up with another question. “If you want to create a calculated member that intersects all measures, where do you put it?” A high percentage of candidates can’t answer this question, and the answer is “In a dimension other than Measures.” If they can answer it, I immediately ask them why. The answer is “Because a member in a dimension cannot intersect its own relatives in that dimension.”

Q: How do I find the bottom 10 customers with the lowest sales in 2003 that were not null?

A: Simply using bottomcount will return customers with null sales. You will have to combine it with NONEMPTY or FILTER.

SELECT { [Measures].[Internet Sales Amount] } ON COLUMNS ,
BOTTOMCOUNT(
NONEMPTY(DESCENDANTS( [Customer].[Customer Geography].[All Customers]
, [Customer].[Customer Geography].[Customer] )
, ( [Measures].[Internet Sales Amount] ) )
, 10
, ( [Measures].[Internet Sales Amount] )
) ON ROWS
FROM [Adventure Works]
WHERE ( [Date].[Calendar].[Calendar Year].&[2003] ) ;

Q: How in MDX query can I get top 3 sales years based on order quantity?

By default Analysis Services returns members in an order specified during attribute design. Attribute properties that define ordering are "OrderBy" and "OrderByAttribute". Lets say we want to see order counts for each year. In Adventure Works MDX query would be:

SELECT {[Measures].[Reseller Order Quantity]} ON 0
, [Date].[Calendar].[Calendar Year].Members ON 1
FROM [Adventure Works];

Same query using TopCount:
SELECT
{[Measures].[Reseller Order Quantity]} ON 0,
TopCount([Date].[Calendar].[Calendar Year].Members,3, [Measures].[Reseller Order Quantity]) ON 1
FROM [Adventure Works];

Q: How do you extract first tuple from the set?

Use could usefunction Set.Item(0)
Example:

SELECT {{[Date].[Calendar].[Calendar Year].Members
}.Item(0)}
ON 0
FROM [Adventure Works]

Q: How can I setup default dimension member in Calculation script?

You can use ALTER CUBE statement. Syntax:
ALTER CUBE CurrentCube | YourCubeName UPDATE DIMENSION , DEFAULT_MEMBER='';

Dynamically generate stored procedures for Insert, update and delete on a table

Hi friends.....
This stored procedure script is very useful for generating dynamic stored procedures for insert, update and delete operations on a table.

After creating the table execute this SP with the table name as the parameter and it will automatically generate stored procedures for insert, update and delete operations on that table.

In this way u can save your precious programming time for other important work..

Happy scripting..

Copy and paste this stored procedure in your SQL server query browser and execute it..
once created you can call this with the table name as a paramater

/*-- =======================================================================================
-- Author : XYZ
-- Create date : dd/mm/yy
-- Description : Generate the Insert / Update/ Delete Stored procedure script of any table
-- by passing the table name
Exec [dbo].[sp_et_create_sps_for_table] 'et_application'

-- ========================================================================================= */
CREATE PROCEDURE [dbo].[sp_et_create_sps_for_table]
@tblName Varchar(50)
AS
BEGIN

Declare @dbName Varchar(50)
Declare @insertSPName Varchar(50), @updateSPName Varchar(50), @deleteSPName Varchar(50) ;
Declare @tablColumnParameters Varchar(1000), @tableColumns Varchar(1000),@tableColumnVariables Varchar(1000);
Declare @tableCols Varchar(1000), @tablinsertParameters Varchar(1000);
Declare @space Varchar(50) ;
Declare @colName Varchar(100) ;
Declare @colVariable Varchar(100) ;
Declare @colParameter Varchar(100) ;
Declare @strSpText Varchar(8000);
Declare @updCols Varchar(2000);
Declare @delParamCols Varchar(2000);
Declare @whereCols Varchar(2000);
Set @tblName = SubString(@tblName,CharIndex('.',@tblName)+1, Len(@tblName))
Set @insertSPName = '[dbo].[sp_' + lower(@tblName) +'_insert]' ;
Set @updateSPName = '[dbo].[sp_' + lower(@tblName) +'_update]' ;
Set @deleteSPName = '[dbo].[sp_' + lower(@tblName) +'_delete]' ;
Set @space = REPLICATE(' ', 4) ;
Set @tablColumnParameters = '' ;
Set @tableColumns = '' ;
Set @tableColumnVariables = '' ;
Set @strSPText = '' ;
Set @tableCols = '' ;
Set @updCols = '' ;
Set @delParamCols = '' ;
Set @whereCols = '' ;
SET NOCOUNT ON
-- Get all columns & data types for a table

-- SELECT distinct
-- sysobjects.name as 'Table',
-- syscolumns.colid ,
-- '[' + syscolumns.name + ']' as 'ColumnName',
-- '@'+syscolumns.name as 'ColumnVariable',
-- systypes.name +
-- Case When systypes.xusertype in (165,167,175,231,239 ) Then '(' + Convert(varchar(10),Case When syscolumns.length=-1 Then 4000 else syscolumns.length end) +')' Else '' end as 'DataType' ,
-- '@'+syscolumns.name + ' ' + systypes.name +
-- Case When systypes.xusertype in (165,167,175,231,239 ) Then '(' + Convert(varchar(10),Case When syscolumns.length=-1 Then 4000 else syscolumns.length end) +')' Else '' end as 'ColumnParameter'
-- Into #tmp_Structure
-- From sysobjects , syscolumns , systypes
-- Where sysobjects.id = syscolumns.id
-- and syscolumns.xusertype = systypes.xusertype
-- and sysobjects.xtype = 'u'
-- and sysobjects.name = @tblName
-- and syscolumns.name!=(SELECT
-- c.name AS ColumnName
-- FROM sys.columns AS c
-- INNER JOIN
-- sys.tables AS t
-- ON t.[object_id] = c.[object_id]
-- where c.is_identity = 1
-- and t.name=@tblName)
-- Order by syscolumns.colid


-- SELECT distinct
-- sysobjects.name as 'Table',
-- syscolumns.colid ,
-- '[' + syscolumns.name + ']' as 'ColumnName',
-- '@'+syscolumns.name as 'ColumnVariable',
-- systypes.name +
-- Case When systypes.xusertype in (165,167,175,231,239 ) Then '(' + Convert(varchar(10),Case When syscolumns.length=-1 Then 4000 else syscolumns.length end) +')' Else '' end as 'DataType' ,
-- '@'+syscolumns.name + ' ' + systypes.name +
-- Case When systypes.xusertype in (165,167,175,231,239 ) Then '(' + Convert(varchar(10),Case When syscolumns.length=-1 Then 4000 else syscolumns.length end) +')' Else '' end as 'ColumnParameter'
-- Into #tmp_Structure
-- From sysobjects , syscolumns , systypes
-- Where sysobjects.id = syscolumns.id
-- and syscolumns.xusertype = systypes.xusertype
-- and sysobjects.xtype = 'u'
-- and sysobjects.name = @tblName
-- Order by syscolumns.colid




-- Get all Primary KEY columns & data types for a table
SELECT t.name as 'Table',
c.colid ,
'[' + c.name + ']' as 'ColumnName',
'@'+c.name as 'ColumnVariable',
systypes.name +
Case When systypes.xusertype in (165,167,175,231,239 ) Then '(' + Convert(varchar(10),c.length) +')' Else '' end as 'DataType' ,
'@'+c.name + ' ' + systypes.name +
Case When systypes.xusertype in (165,167,175,231,239 ) Then '(' + Convert(varchar(10),c.length) +')' Else '' end as 'ColumnParameter'
Into #tmp_PK_Structure
FROM sysindexes i, sysobjects t, sysindexkeys k, syscolumns c, systypes
WHERE i.id = t.id AND
i.indid = k.indid AND i.id = k.ID And
c.id = t.id AND c.colid = k.colid AND
i.indid BETWEEN 1 And 254 AND
c.xusertype = systypes.xusertype AND
(i.status & 2048) = 2048 AND t.id = OBJECT_ID(@tblName)



--if exists(select * from #tmp_PK_Structure)
--BEGIN
--print 'y'
SELECT distinct
sysobjects.name as 'Table',
syscolumns.colid ,
'[' + syscolumns.name + ']' as 'ColumnName',
'@'+syscolumns.name as 'ColumnVariable',
systypes.name +
Case When systypes.xusertype in (165,167,175,231,239 ) Then '(' + Convert(varchar(10),Case When syscolumns.length=-1 Then 4000 else syscolumns.length end) +')' Else '' end as 'DataType' ,
'@'+syscolumns.name + ' ' + systypes.name +
Case When systypes.xusertype in (165,167,175,231,239 ) Then '(' + Convert(varchar(10),Case When syscolumns.length=-1 Then 4000 else syscolumns.length end) +')' Else '' end as 'ColumnParameter'
Into #tmp_Structure
From sysobjects , syscolumns , systypes
Where sysobjects.id = syscolumns.id
and syscolumns.xusertype = systypes.xusertype
and sysobjects.xtype = 'u'
and sysobjects.name = @tblName
and syscolumns.name!=(SELECT
c.name AS ColumnName
FROM sys.columns AS c
INNER JOIN
sys.tables AS t
ON t.[object_id] = c.[object_id]
where c.is_identity = 1
and t.name=@tblName)
Order by syscolumns.colid

--END
--ELSe
--BEGIN
--print 'n'
SELECT distinct
sysobjects.name as 'Table',
syscolumns.colid ,
'[' + syscolumns.name + ']' as 'ColumnName',
'@'+syscolumns.name as 'ColumnVariable',
systypes.name +
Case When systypes.xusertype in (165,167,175,231,239 ) Then '(' + Convert(varchar(10),Case When syscolumns.length=-1 Then 4000 else syscolumns.length end) +')' Else '' end as 'DataType' ,
'@'+syscolumns.name + ' ' + systypes.name +
Case When systypes.xusertype in (165,167,175,231,239 ) Then '(' + Convert(varchar(10),Case When syscolumns.length=-1 Then 4000 else syscolumns.length end) +')' Else '' end as 'ColumnParameter'
Into #tmp_Structure1
From sysobjects , syscolumns , systypes
Where sysobjects.id = syscolumns.id
and syscolumns.xusertype = systypes.xusertype
and sysobjects.xtype = 'u'
and sysobjects.name = @tblName
Order by syscolumns.colid
--END

/* Read the table structure and populate variables*/
Declare SpText_Cursor Cursor For
Select ColumnName, ColumnVariable, ColumnParameter
From #tmp_Structure

Open SpText_Cursor

Fetch Next From SpText_Cursor Into @colName, @colVariable, @colParameter
While @@FETCH_STATUS = 0
Begin
Set @tableColumns = @tableColumns + @colName + CHAR(13) + @space + @space + ',' ;
Set @tablColumnParameters = @tablColumnParameters + @colParameter + CHAR(13) + @space + ',' ;
Set @tableColumnVariables = @tableColumnVariables + @colVariable + CHAR(13) + @space + @space + ',' ;
Set @tableCols = @tableCols + @colName + ',' ;
Set @updCols = @updCols + @colName + ' = ' + @colVariable + CHAR(13) + @space + @space + ',' ;
Fetch Next From SpText_Cursor Into @colName, @colVariable, @colParameter
End

Close SpText_Cursor
Deallocate SpText_Cursor

/* for update parameter*/

if exists(select * from #tmp_PK_Structure)
BEGIN
Set @tablinsertParameters=''
Declare SpText_Cursor1 Cursor For
Select ColumnParameter
From #tmp_Structure1

Open SpText_Cursor1

Fetch Next From SpText_Cursor1 Into @colParameter
While @@FETCH_STATUS = 0
Begin

Set @tablinsertParameters = @tablinsertParameters + @colParameter + CHAR(13) + @space + ',' ;
Fetch Next From SpText_Cursor1 Into @colParameter
End

Close SpText_Cursor1
Deallocate SpText_Cursor1
END
/*end for update parameter*/

/* Read the Primary Keys from the table and populate variables*/
Declare SpPKText_Cursor Cursor For
Select ColumnName, ColumnVariable, ColumnParameter
From #tmp_PK_Structure

Open SpPKText_Cursor

Fetch Next From SpPKText_Cursor Into @colName, @colVariable, @colParameter
While @@FETCH_STATUS = 0
Begin
Set @delParamCols = @delParamCols + @colParameter + CHAR(13) + @space + ',' ;
Set @whereCols = @whereCols + @colName + ' = ' + @colVariable + ' AND ' ;
Fetch Next From SpPKText_Cursor Into @colName, @colVariable, @colParameter
End

Close SpPKText_Cursor
Deallocate SpPKText_Cursor


If (LEN(@tablinsertParameters)>0)
Set @tablinsertParameters = LEFT(@tablinsertParameters,LEN(@tablinsertParameters)-1) ;

-- Stored procedure scripts starts here
If (LEN(@tablColumnParameters)>0)
Begin
Set @tablColumnParameters = LEFT(@tablColumnParameters,LEN(@tablColumnParameters)-1) ;
Set @tableColumnVariables = LEFT(@tableColumnVariables,LEN(@tableColumnVariables)-1) ;
Set @tableColumns = LEFT(@tableColumns,LEN(@tableColumns)-1) ;
Set @tableCols = LEFT(@tableCols,LEN(@tableCols)-1) ;
Set @updCols = LEFT(@updCols,LEN(@updCols)-1) ;

If (LEN(@whereCols)>0)
Begin
Set @whereCols = 'WHERE ' + LEFT(@whereCols,LEN(@whereCols)-4) ;
Set @delParamCols = LEFT(@delParamCols,LEN(@delParamCols)-1) ;
End

/* Create INSERT stored procedure for the table if it does not exist */
IF Not EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(@insertSPName) AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
Begin
Set @strSPText = @strSPText + CHAR(13) + ''
Set @strSPText = @strSPText + CHAR(13) + '/*-- ============================================='
Set @strSPText = @strSPText + CHAR(13) + '-- Author : dbo'
Set @strSPText = @strSPText + CHAR(13) + '-- Create date : ' + Convert(varchar(20),Getdate())
Set @strSPText = @strSPText + CHAR(13) + '-- Description : Insert Procedure for ' + @tblName
Set @strSPText = @strSPText + CHAR(13) + '-- Exec ' + @insertSPName + ' ' + @tableCols
Set @strSPText = @strSPText + CHAR(13) + '-- ============================================= */'
Set @strSPText = @strSPText + CHAR(13) + 'CREATE PROCEDURE ' + @insertSPName

if exists(select * from #tmp_PK_Structure)
Set @strSPText = @strSPText + CHAR(13) + @space + ' ' + @tablColumnParameters
else
Set @strSPText = @strSPText + CHAR(13) + @space + ' ' + @tablinsertParameters

Set @strSPText = @strSPText + CHAR(13) + 'AS'
Set @strSPText = @strSPText + CHAR(13) + 'BEGIN'
Set @strSPText = @strSPText + CHAR(13) + ''
Set @strSPText = @strSPText + CHAR(13) + @space + 'INSERT INTO [dbo].['+@tblName +']'
Set @strSPText = @strSPText + CHAR(13) + @space + '( '
Set @strSPText = @strSPText + CHAR(13) + @space + @space + ' ' + @tableColumns
Set @strSPText = @strSPText + CHAR(13) + @space + ')'
Set @strSPText = @strSPText + CHAR(13) + @space + 'VALUES'
Set @strSPText = @strSPText + CHAR(13) + @space + '('
Set @strSPText = @strSPText + CHAR(13) + @space + @space + ' ' + @tableColumnVariables
Set @strSPText = @strSPText + CHAR(13) + @space + ')'
Set @strSPText = @strSPText + CHAR(13) + 'END'
Set @strSPText = @strSPText + CHAR(13) + ''
Set @strSPText = @strSPText + CHAR(13) + ''
Set @strSPText = @strSPText + CHAR(13) + ''
--Print @strSPText ;
--print
Exec(@strSPText);

if (@@ERROR=0)
Print 'Procedure ' + @insertSPName + ' Created Successfully '
End
Else
Begin
Print 'Sorry!! ' + @insertSPName + ' Already exists in the database. '
End
/* Create UPDATE stored procedure for the table if it does not exist */
IF Not EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(@updateSPName) AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
Begin
Set @strSPText = ''
Set @strSPText = @strSPText + CHAR(13) + '/*-- ============================================='
Set @strSPText = @strSPText + CHAR(13) + '-- Author : dbo'
Set @strSPText = @strSPText + CHAR(13) + '-- Create date : ' + Convert(varchar(20),Getdate())
Set @strSPText = @strSPText + CHAR(13) + '-- Description : Update Procedure for ' + @tblName
Set @strSPText = @strSPText + CHAR(13) + '-- Exec ' + @updateSPName + ' ' + @tableCols
Set @strSPText = @strSPText + CHAR(13) + '-- ============================================= */'
Set @strSPText = @strSPText + CHAR(13) + 'CREATE PROCEDURE ' + @updateSPName


if exists(select * from #tmp_PK_Structure)
Set @strSPText = @strSPText + CHAR(13) + @space + ' ' + @tablinsertParameters
else
Set @strSPText = @strSPText + CHAR(13) + @space + ' ' + @tablColumnParameters
--Set @strSPText = @strSPText + CHAR(13) + @space + ' ' + @tablColumnParameters

Set @strSPText = @strSPText + CHAR(13) + 'AS'
Set @strSPText = @strSPText + CHAR(13) + 'BEGIN'
Set @strSPText = @strSPText + CHAR(13) + ''
Set @strSPText = @strSPText + CHAR(13) + @space + 'UPDATE [dbo].['+@tblName +']'
Set @strSPText = @strSPText + CHAR(13) + @space + 'SET '
Set @strSPText = @strSPText + CHAR(13) + @space + @space + ' ' + @updCols
Set @strSPText = @strSPText + CHAR(13) + @space + @whereCols
Set @strSPText = @strSPText + CHAR(13) + ''
Set @strSPText = @strSPText + CHAR(13) + 'END'
Set @strSPText = @strSPText + CHAR(13) + ''
Set @strSPText = @strSPText + CHAR(13) + ''
--Print @strSPText ;
Exec(@strSPText);

if (@@ERROR=0)
Print 'Procedure ' + @updateSPName + ' Created Successfully '
End
Else
Begin
Print 'Sorry!! ' + @updateSPName + ' Already exists in the database. '
End
/* Create DELETE stored procedure for the table if it does not exist */
IF Not EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(@deleteSPName) AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
Begin
Set @strSPText = ''
Set @strSPText = @strSPText + CHAR(13) + '/*-- ============================================='
Set @strSPText = @strSPText + CHAR(13) + '-- Author : dbo'
Set @strSPText = @strSPText + CHAR(13) + '-- Create date : ' + Convert(varchar(20),Getdate())
Set @strSPText = @strSPText + CHAR(13) + '-- Description : Delete Procedure for ' + @tblName
Set @strSPText = @strSPText + CHAR(13) + '-- Exec ' + @deleteSPName + ' ' + @delParamCols
Set @strSPText = @strSPText + CHAR(13) + '-- ============================================= */'
Set @strSPText = @strSPText + CHAR(13) + 'CREATE PROCEDURE ' + @deleteSPName
Set @strSPText = @strSPText + CHAR(13) + @space + ' ' + @delParamCols
Set @strSPText = @strSPText + CHAR(13) + 'AS'
Set @strSPText = @strSPText + CHAR(13) + 'BEGIN'
Set @strSPText = @strSPText + CHAR(13) + ''
Set @strSPText = @strSPText + CHAR(13) + @space + 'DELETE FROM [dbo].['+@tblName +']'
Set @strSPText = @strSPText + CHAR(13) + @space + @whereCols
Set @strSPText = @strSPText + CHAR(13) + ''
Set @strSPText = @strSPText + CHAR(13) + 'END'
Set @strSPText = @strSPText + CHAR(13) + ''
Set @strSPText = @strSPText + CHAR(13) + ''
--Print @strSPText ;
Exec(@strSPText);

if (@@ERROR=0)
Print 'Procedure ' + @deleteSPName + ' Created Successfully '
End
Else
Begin
Print 'Sorry!! ' + @deleteSPName + ' Already exists in the database. '
End
End
Drop table #tmp_Structure
Drop table #tmp_Structure1
Drop table #tmp_PK_Structure
END

SQL Server 2008 Interview questions

General Questions asked about SQL Server

What is RDBMS?
What is Normalization?
What is De-normalization where is it used?
What are different normalization forms?
What is Stored Procedure?
What is Trigger?
What are different types of triggers?
What is View?
What is an Indexed View?
What is “Rollup” clause?

NEW Questions added..
How to decide the sequence of fields in a compound index?
Is it possible to declare a clustured index on non-primary field?
What are the commands to compare two result sets in 2005?
What are the pre-requisits to create an indexed view?
What are the advantages and disadvantages of NO LOCK option in select statement?
Can we insert/update rows in system tables?
CTE for deleting duplicate rows from a table
What are 3 different ways to execute a dymnamic SQL?
How to sychronize data with foxpro DB from SQL 2005?
How to read excel data directly into SQL server 2005?
ANS:
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\Source\Addresses.xls',
'SELECT * FROM [Sheet1$]')


What are magic tables or what are inserted and deleted tables?
How to update multiple tables from a view?
What is the use of instead of trigger?
How to shrink DB files?
How to truncate transaction log?
What are DBCC commands?
How to reset value of identity column in a table?
How to create a linked server in SQL Server?
How to import data from MySQL db to SQL Server?
What are full text indexes?
What are advantages and disadvantages of Dynamic Query in a stored procedure?
How to get value of last inserted identity column?
What is the difference between a table type variable and temp table, which is better?
Can we update data in the parent table using view?



What is the need of re-indexing?
What is BCP?
What is Index?
What is a Linked Server?
What is Cursor?
What is Collation?
What is Difference between Function and Stored Procedure?
What is sub-query? Explain properties of sub-query?
What are different types of Join?
What are primary keys and foreign keys?
What is the difference between primary and unique key?
What is User Defined Functions? What kind of User‐Defined Functions can be created?
What is difference between DELETE & TRUNCATE commands?
What is difference between UNION & UNION ALL commands?
What is the difference between a HAVING CLAUSE and a WHERE CLAUSE?
What is SQL Profiler?
What is SQL Server Agent?
What is an execution plan? When would you use it? How to you view the execution plan?




Questions specific to SQL Server 2008

What is Policy Management?
What are Sparse Columns?
What is MERGE Statement?
What is Filtered Index?
Which are new data types introduced in SQL SERVER 2008?
What are synonyms?
What is EXCEPT clause?
How to handle errors in SQL Server 2008?
What is data compression?

Get details of identity columns in all the tables in the database,

To select value of identity columns in all tables in the selected database.
Below is the query which selects the description of identity columns in all tables in the selected database.

SELECT OBJECT_NAME(OBJECT_ID) AS
TABLENAME, NAME AS COLUMNNAME,
SEED_VALUE, INCREMENT_VALUE,
LAST_VALUE, IS_NOT_FOR_REPLICATION
FROM SYS.IDENTITY_COLUMNS ORDER BY 1

SQL Server Query Execution Plan Analysis

When it comes time to analyze the performance of a specific query, one of the best methods is to view the query execution plan. A query execution plan outlines how the SQL Server query optimizer actually ran (or will run) a specific query. This information if very valuable when it comes time to find out why a specific query is running slow.
There are several different ways to view a query's execution plan.

They include:
From within Query Analyzer is an option called "Show Execution Plan" (located on the Query drop-down menu). If you turn this option on, then whenever you run a query in Query Analyzer, you will get a query execution plan (in graphical format) displayed in a separate window.
If you want to see an execution plan, but you don't want to run the query, you can choose the option "Display Estimated Execution Plan" (located on the Query drop-down menu). When you select this option, immediately an execution plan (in graphical format) will appear. The difference between these two (if any) is accountable to the fact that when a query is really run (not simulated, as in this option), current operations of the server are also considered. In most cases, plans created by either method will produce similar results.
When you create a SQL Server Profiler trace, one of the events you can collect is called: MISC: Execution Plan. This information (in text form) shows the execution plan used by the query optimizer to execute the query.

From within Query Analyzer, you can run the command SET SHOWPLAN_TEXT ON. Once you run this command, any query you execute in this Query Analyzer sessions will not be run, but a text-based version of the query plan will be displayed. If the query you are running uses temp tables, then you will have to run the command, SET STATISTICS PROFILE ON before running the query.
Of these options, I prefer using the "Show Execution Plan", which produces a graphical output and considers current server operations.
*****
If you see any of the following in an execution plan, you should consider them warning signs and investigate them for potential performance problems. Each of them are less than ideal from a performance perspective.
Index or table scans: May indicate a need for better or additional indexes.
Bookmark Lookups: Consider changing the current clustered index, consider using a covering index, limit the number of columns in the SELECT statement.
Filter: Remove any functions in the WHERE clause, don't include wiews in your Transact-SQL code, may need additional indexes.
Sort: Does the data really need to be sorted? Can an index be used to avoid sorting? Can sorting be done at the client more efficiently?
It is not always possible to avoid these, but the more you can avoid them, the faster query performance will be.

*****
If you have a stored procedure, or other batch Transact-SQL code that uses temp tables, you cannot use the "Display Estimated Execution Plan" option in the Query Analyzer or Management Studio to evaluate it. Instead, you must actually run the stored procedure or batch code. This is because when a query is run using the "Display Estimated Execution Plan" option, it is not really run, and temp tables are not created. Since they are not created, any references to them in the code will fail, which prevents an estimated execution plan from being created.
On the other hand, if you use a table variable instead of a temp table, you can use the "Display Estimated Execution Plan" option

*****
If you have a very complex query you are analyzing in Query Analyzer or Management Studio as a graphical query execution plan, the resulting plan can be very difficult to view and analyze. You may find it easier to break down the query into its logical components, analyzing each component separately.

*****
The results of a graphical query execution plan are not always easy to read and interpret. Keep the following in mind when viewing a graphical execution plan:
In very complex query plans, the plan is divided into many parts, with each part listed one on top of the other on the screen. Each part represents a separate process or step that the query optimizer has to perform in order to get to the final results.
Each of the execution plan steps is often broken down into smaller sub-steps. Unfortunately, they are displayed on the screen from right to left. This means you must scroll to the far right of the graphical query plan to see where each step starts.
Each of the sub-steps and steps is connected by an arrow, showing the path (order) taken of the query when it was executed.
Eventually, all of the parts come together at the top left side of the screen.
If you move your cursor above any of the steps or sub-steps, a pop-up windows is displayed, providing more detailed information about this particular step or sub-step.
If you move your cursor over any of the arrows connecting the steps and sub-steps, you see a pop-up window showing how many records are being moved from one step or sub-step to another step or sub-step.