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.