Use of SQL Server CTEs

SQL Server 2005 introduced Common Table Expression concept.
CTE can be used as alternative to complex sub queries.
CTEs replace traditional Group By condition to remove duplicate records from db tables having or not having primary keys.

Syntax:
;WITH CTE1(Id,Name) as
(
   SELECT EMPLOYEE_ID, EMPLOYEE_NAME FROM EMP
)
SELECT Id, Name FROM CTE1

CTE can be defined with or without parameters; when CTE is defined with parameters, its definition should contain same number of parameters.

CTEs can be used in SQL JOIN conditions similar to db tables or views.
Also we can perform DML operations on the CTEs.

For more information on CTEs, please refer to the link here.

Please add your valueble comments on this post.

Community Tech Days is Back

Hi guys,
Community Tech Days is back in Hyderabad on 18th Dec , 2010.
Registration for inperson and online partiicipation is open now.

For more details, please visit http://www.communitytechdays.com/

Regards,
DP.

SQL Server Builtin functions to validate the data

Recently I came across a scenario in which I need to validate the data against the datatype.
After some searching I found the below SQL Server Builtin functions to validate the data.

ISNULL
ISNUMERIC
ISDATE
Eg:
SELECT ISNUMERIC('1234')--Returns 1 for success and 0 for failure.
SELECT ISDATE('11-30-2010')--Returns 1 for success and 0 for failure.

If you find relevant information, please update here so that I will publish with due credit.
Regards,
DP.

SQL Server Trouble shooting

Reference
1. http://blog.sqlauthority.com/2009/05/21/sql-server-fix-error-provider-named-pipes-provider-error-40-could-not-open-a-connection-to-sql-server-microsoft-sql-server-error/
2. http://blog.sqlauthority.com/2009/03/18/sql-server-find-all-servers-from-local-network-using-sqlcmd/

Useful Technical Links

For reference:
1. SQL Server 2005/2008 and XML datatype
  http://blog.sqlauthority.com/2009/02/13/sql-server-simple-example-of-reading-xml-file-using-t-sql/
  http://www.kodyaz.com/articles/how-to-import-xml-into-sql-server-2005.aspx

2. Microsoft SQL Server Community Projects & Samples
    http://sqlserversamples.codeplex.com/

3. Cloud computing reference:
    http://en.wikipedia.org/wiki/Cloud_computing

4. SQL Server Articles
   http://www.kodyaz.com/content/SQLServerArticles.aspx

Use of SQLCMD

Using SSMS, we can perform T-SQL batch operations on databases.
The same can be done using SQLCMD in appropriate situations.
1. Open command prompt and type sqlcmd -? to get the available commands information
2. sqlcmd -H 'hostname' -S 'servername' -D 'database'
3. Using sqlcmd -Q 'SQL query' and sqlcmd -q 'SQL query', batch operations can be achieved.

To know more about SQLCMD, please click link here.
Hope this post helps you,
Reference: DP (http://dptechnicalblog.blogspot.com/)

SQL PowerShell

SSMS has the object explorer using which we we can navigate to all components of database server.
The same can be achieved using T-SQL by using sqlps.exe i.e. SQL PowerShell

Syntax:
1.From run , type sqlps.exe, a pop opens
2. type dir to explore the available directory information
3. cd SQL to get the host information
4. cd 'db server', to explore the available components
5. cd databases
6. cd 'desired database name'

Reference: DP (http://dptechnicalblog.blogspot.com/)

SQL SERVER: ALTER column datatype

Though it appears simple, often we search google or  refer to msdn for simple SQL queries.

CREATE tableA(col1 Int,col2 Varchar(100),col3 Varchar(50))
GO

To alter the datatype for col3 in tableA, we need to do the below.

ALTER tableA
ALTER col3 Varbinary(MAX) NULL
GO

SQL SERVER: Problem with SELECT *

Its common practise to user SELECT * when we want to query data from a db table or underlying view or inserting data into another db table as below.

--Assumption: tableA , tableB exists
INSERT INTO tableB
SELECT * FROM tableA.
GO

The above statement works very well as long as both the db tables have the same structure.
But if the db structure for any of the above db changes, then the above query would fail.

Solution: Always use explicit column names in the SQL queries

INSERT INTO tableB(col1,col2,col3)
SELECT col1,col2,col3 FROM tableA
GO

Reference: Durga Palepu (http://blogwithdp.blogspot.com/)

Adding new column to SQL SERVER DB table

Here is the process to add new column to existing db table  in SQL SERVER.

Create sampleStudTable(Id int,Name Varchar(100))
GO

To add additional column to this db table;
ALTER TABLE sampleStudTable
ADD Discipline Varchar(100) NULL

Reference: http://blogwithdp.blogspot.com/

Happy Dussera

I wish you all Happy and Prosperous Dussera festival.

Regards,
DP

Community TechDays - Bangalore

Microsoft Community TechDays are back once again!!

This time the event happens in Bangalore and its absolutely free of cost!!

To get latest updates on Microsoft technologies, jump into the join the sessions.
You can attend the sessions in person or online from your home at your convinience.

For more details about the event and registration, please click link here.

SQL Server GO Keyword

Often we face problem when executing multiple SQL statements i.e. if any one among the SQL statements fail then all transactions may fail.
To overcome this problem in SQL Server, we can use GO keyword which we will seperate execution of one statement from the other SQL statement.

Syntax:
GO [Count]
Where Count Is a positive integer. The batch preceding GO executes the specified number of times.
Example:
Create Employee(Id int,Name Varchar(50))

INSERT INTO Employee(Id ,Name)
VALUES (100,'Emp1')

INSERT INTO Employee(Id ,Name)
VALUES (100,'Emp2)

Execution above statements fails as there is syntax error.

Using the GO keyword.
INSERT INTO Employee(Id ,Name)
VALUES (100,'Emp1')
GO
INSERT INTO Employee(Id ,Name)
VALUES (100,'Emp2)
GO
Now the 1st statement will be executed successfully.
To know more about the GO keyword click link here.

Reference: http://blogwithdp.blogspot.com/

MS SQL Tips

MSSQLTips is a very useful resource for SQL Server DBAs, Developers.
To get all the SQL tips published by this site in septembers, click link here.

Reference: http://www.mssqltips.com/

“BI From A To Z” Workshop's Detailed Agenda

160 Slides and more than 15 demos will fill the day. The detailed agenda is the following.

• The need for a Datawarehouse
  • OLTP vs DSS/OLAP
  • Inmon & Kimball
• Basic Concepts
  • Star & Snowflake Schema
  • Fact, Dimensions & Factless/Bridge Tables
• Designing & Building the Datawarehouse
  • Engineering the solution
  • Slowly Changing Dimensions
• Loading the Datawarehouse
  • Integration Services & T-SQL
• Data Analysis
  • Creating Analysis Services Cubes
  • Introduction to MDX
• Reporting
  • Using Reporting Services in BI
• Managing the solution
  • Deployment & Monitoring
• Conclusion
• Next Steps

For complete details click the link here.

Microsoft SQL Server 2008 Service Pack2 Download

Microsoft SQL Server 2008 Service Pack 2 (SP2) is now available for download.
You can download Microsoft SQL Server 2008 Service Pack2 preferred version from link here.

New features:
1. 15K partitioning improvement
2. Reporting Services in SharePoint integrated mode.
3. SQL Server 2008 R2 Application and Multi-Server Management Compatibility with SQL Server 2008.
4. SQL Server 2008 Instance Management
5. Data-tier Application (DAC) support

Reference: MSDN

How to insert explicit values into Identity column

Problem: How to insert explicit values into Identity column in SQL SERVER.
Solution: Using SET IDENTITY_INSERT ON, we can insert explicit values into Identity column.

USE AdventureWorks2008R2;
GO
-- Create Student table.
CREATE TABLE dbo.Student(
   ID INT IDENTITY NOT NULL PRIMARY KEY,
   Name VARCHAR(40) NOT NULL
)
GO
-- Inserting values into products table.
INSERT INTO dbo.Student(Name) VALUES ('Raju')
INSERT INTO dbo.Student(Name) VALUES ('Subbu')
INSERT INTO dbo.Student(Name) VALUES ('Pandu')
INSERT INTO dbo.Student(Name) VALUES ('Prasad')
GO

-- Create a gap in the identity values.
DELETE dbo.Student
WHERE Name = 'Pandu'
GO

SELECT *
FROM dbo.Student
GO

-- Try to insert an explicit ID value of 3;
-- should return a warning.
INSERT INTO dbo.Student(ID, Name) VALUES (3, 'Satya')
GO
-- SET IDENTITY_INSERT to ON.
SET IDENTITY_INSERT dbo.Student ON
GO

-- Try to insert an explicit ID value of 3.
INSERT INTO dbo.Student(ID, Name) VALUES (3, 'Satya')
GO

SELECT *
FROM dbo.Student
GO
-- Drop products table.
DROP TABLE dbo.Student
GO

Reference: MSDN

Businsess Intelligence Information

Business Intelligence is one of most sought after technology in software industry and popular as BI.
Microsoft has its share of technologies in this BI field viz SSRS, SSIS and SSAS.

SSIS is used for ETL (Extract, Transform and Load) operations. SQL Tips provides lot of information on this technology, click here of more information.

Create a db table from another db table

Often we come across the scenario to backup some db tables as security precaution.
Solution:

Use below query to accomplish the same:
SELECT * INTO TableB FROM TableB
go
The only disadvantage with this approach is primary key will not be restored in backup table.
For more information please click here.

Virtual Techdays

Virtual Techdays by Microsoft is a platform for updating our technical skills.
Click here to know more about it.
To visit the previous recorded sessions, click here.

SQLSERVER Cascading

Problem: Recently I came across a scenario in which I need to cleanup data in a table which is having foreign key references to other db tables.
Solution: Using SQLSERVER Cascading rule you can update / delete the child records references. Click here to know more about this.

SQL SERVER Linked Servers

Problem: Recently in my project, I came across a scenario in which I need to connect databases in different servers.
Solution: After some analysis, I found that Linked servers concept can fulfill the requirement.

Please suggest opinion and if any alternative solutions are there.

Hyperion Essbase.

Essbase is a multidimensional database management system (MDBMS) that provides a multidimensional database platform upon which to build analytic applications. Essbase, whose name derives from "Extended Spread Sheet dataBASE"

For more details refer to http://en.wikipedia.org/wiki/Essbase.