SQL LIKE

SQL Server Database Restore from SQL Server 2008 R2 to 2008

December 19, 2010

Recently I was working on a database which was on two different versions, SQL Server 2008 and SQL Server 2008 R2.

And in the middle I wanted to do a restore from SQL Server 2008 R2 to SQL Server 2008, and went through a bit of issues, and finally found out that there is no easy way to get out of this Issue.

I had taken a backup of the SQL Server Database from R2 and tried restoring it on SQL Server 2008, and got the below message,

“The database was backed up on a server running version 10.50.1600. That version is incompatible with this server, which is running version 10.00.2531. Either restore the database on a server that supports the backup, or use a backup that is compatible with this server. (Microsoft.SqlServer.Smo)”

When we look through the above error, that’s clear that, the basic sql server physical version is different for both SQL Server SQL Server 2008 and SQL Server 2008 R2.

SQL Server 2008 R2 is 10.5

SQL Server 2008 is 10.

So, now the issue was on how to accomplish it, the below is what had to be done, it is laborious manual way, I suppose it is only the developers that encounter such problems, so Microsoft will not for sure address it J because even if the feature is there, there would be many few people using it.

We need to generate a script of the complete database with a proper Script Server Version selected. Can be done by

Tasks –> Generate Scripts–> Script entire database and all database objects –> Save the File –> Got to Advanced –> Select proper Script for Server Version 200/2005/2008 –> Type of data to Script –> select Schema/Data/both –> Execute it on the target server.

Of course you will be getting lots of errors w.r.t the primary-foreign key relationships, need to address them all, or do a manually by using the import and export wizard w.r.t the data.

Please let me know if there are any other suitable better performing methods for accomplishing the above in comments.

Tagged as: Backup, restore

{ Comments on this entry are closed }

SQL Server Query to Find First and Last Day of Month, Week,Quarter, Year

December 18, 2010

in SQL Basics

These are a set of queries that I keep using frequently in the stored procedures that I wrote, they can basically be used for finding the date time of

Start and End of Today

SELECT DATEADD(DD,DATEDIFF(DD,0, GETDATE()),0) as [Start Of Day]
	  ,DATEADD(s,-1,DATEADD(dd, DATEDIFF(dd,0,GETDATE())+1,0)) as [End Of Day]

Start and End of Week

SELECT DATEADD(WK, DATEDIFF(WK,0,GETDATE()),0) as [Start Of Week]
	  ,DATEADD(s,-1,DATEADD(WK, DATEDIFF(WK,0,GETDATE())+1,0)) as [End Of Week]

Start and End of Month

SELECT DATEADD(M, DATEDIFF(M,0,GETDATE()),0) as [Start Of Month]
	  ,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0)) as [End Of Month]

Start and End of Next Month

SELECT DATEADD(DD,DATEDIFF(DD,0,DATEADD(dd,-(DAY(DATEADD(mm,1,GETDATE()))-1),DATEADD(mm,1,GETDATE()))),0) as [Start of Next Month]
	  ,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+2,0)) as [End Of Next Month]

Start and End of Quarter

SELECT DATEADD(QQ, DATEDIFF(QQ,0,GETDATE()),0) as [Start Of Qaurter]
	  ,DATEADD(s,-1,DATEADD(QQ, DATEDIFF(QQ,0,GETDATE())+1,0)) as [End Of Quarter]

Start and End of Year

SELECT DATEADD(YY, DATEDIFF(YY,0,GETDATE()),0) as [Start Of Year]
	  ,DATEADD(s,-1,DATEADD(YY, DATEDIFF(YY,0,GETDATE())+1,0)) as [End Of Year]

Please let me know if there are any other suitable better performing methods for accomplishing the above in comments.

Tagged as: T-SQL

{ Comments on this entry are closed }

SQL SERVER – Comma Separated Values (CSV) from Table Column

December 16, 2010

in SQL Basics

There are many instances where I need to convert a set of rows in a single column to a comma separated string, and this is how I accomplish it, going with an example, creating a single column table with some string values in it…

DECLARE @MakeStr VARCHAR(200)
CREATE TABLE #TEMPTABLE1(COL1 NVARCHAR(10))

INSERT INTO #TEMPTABLE1 VALUES('ABC')
INSERT INTO #TEMPTABLE1 VALUES('BCD')
INSERT INTO #TEMPTABLE1 VALUES('CDE')
INSERT INTO #TEMPTABLE1 VALUES('EFG')
INSERT INTO #TEMPTABLE1 VALUES('GHI')

Create Comma Seperated String Using COALESCE Method

SELECT @MakeStr = COALESCE(@MakeStr+',' ,'') + COL1
FROM #TEMPTABLE1

SELECT @MakeStr as SingleString

Create Comma Seperated String Using XML Method

SELECT SUBSTRING(
(SELECT ',' + COL1
FROM #TEMPTABLE1
ORDER BY COL1
FOR XML PATH('')),2,200) AS SingleString

And the result for the above would be

SingleString
——————–
ABC,BCD,CDE,EFG,GHI

Dropping the temporary table that we have create.

DROP TABLE #TEMPTABLE1

Please let me know if there are any other suitable better performing methods for accomplishing the above in comments.

Tagged as: T-SQL

{ Comments on this entry are closed }

SQL Server Denali Installation

December 6, 2010

in Denali

Just went through the installation of SQL Server Denali on Windows 7. And here are a few screens from that installation process.

Tagged as: Denali, Installation

{ Comments on this entry are closed }

SP_ExecuteSQL Explained

June 9, 2010

in Developer

Primarily there are two ways to execute a string or a variable in a string format as sql, they are using EXEC and SP_EXECUTESQL.

sp_executesql is always to be preferred for executing Dynamic SQL because, sp_executesql supports parameter substitution and also there are more chances of the reuse of execution plan when we use sp_executesql.

What we should remember when using sp_ExecuteSQL is that, it always executes as a separate batch irrespective if the batch/ procedure it is contained in.The transact SQL Contained in a variable which is being executed through the SP_ExecuteSQL has its Execution plan generated separately.

Here are a set of example on how a sp_Executesql can be used, and what are the basic mistakes that we commit while using sp_Executesql.

Using sp_ExecuteSQL, sp_executesql error – Data Type of Vairable

Here is a basic example of sp_ExecuteSQL, where we declare a variable and assign a valid sql to it and when executed it throws an error “Procedure expects parameter ‘@statement’ of type ‘ntext/nchar/nvarchar‘.”

This tells us that, it is compulsory to use variable of the above data types.

Use AdventureWorks
GO
DECLARE @SqlStr varchar(500)
SET @SqlStr='SELECT TOP 10 EmployeeID,NationalIDNumber,Title FROM HumanResources.Employee'
EXEC sp_executesql @SqlStr
Msg 214, Level 16, State 2, Procedure sp_executesql, Line 1
Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.

Using sp_ExecuteSQL, sp_executesql error - Parsed and Compiled

Now, lets make another mistake deliberately to see what happens if we use an invalid sql statement in the variable which is being executed through sp_ExecuteSQL, it tells us that the sql query in the variable is parsed and compiled only when executed through the sp_ExcuteSQL

Use AdventureWorks
GO
DECLARE @SqlStr nvarchar(500)
SET @SqlStr='SELECT TOP 10 EmployeeID,A_NationalIDNumber,Title FROM HumanResources.Employee'
EXEC sp_executesql @SqlStr
Msg 207, Level 16, State 1, Line 1
Invalid column name 'A_NationalIDNumber'.

Using sp_ExecuteSQL,Correct Method

Now, lets go and put correct sql in the variable and run it using sp_ExecuteSQL to see how it works.

Use AdventureWorks
GO
DECLARE @SqlStr nvarchar(500)
SET @SqlStr='SELECT TOP 10 EmployeeID,NationalIDNumber,Title FROM HumanResources.Employee'
EXEC sp_executesql @SqlStr

Using sp_ExecuteSQL, Parameter Concatenation

The below code snippet shows how a string can be formed dynamically and then be assigned to a variable which is in turn executed through the sp_ExecuteSQL stored procedure

Use AdventureWorks
GO
DECLARE @Employeeid int
DECLARE @SqlStr nvarchar(500)
SET @EmployeeID=10
SET @SqlStr='SELECT TOP 10 EmployeeID,NationalIDNumber,Title FROM HumanResources.Employee WHERE EmployeeID= ' + Cast(@Employeeid as Varchar)
EXEC sp_executesql @SqlStr

Using sp_ExecuteSQL, How to Save the result set in a table

This example, shows how a result set that is being returned via sp_ExecuteSQL be stored in a temporary table.

USE AdventureWorks
GO
CREATE TABLE #usp_TestInsertWithSp_exec(EmployeeID int, NationalIDNumber nvarchar(15),Title nvarchar(50))
DECLARE @Employeeid int
DECLARE @SqlStr nvarchar(500)
SET @EmployeeID=10
SET @SqlStr='SELECT TOP 10 EmployeeID,NationalIDNumber,Title FROM HumanResources.Employee WHERE EmployeeID= ' + Cast(@Employeeid as Varchar)
INSERT INTO #usp_TestInsertWithSp_exec
EXEC sp_executesql @SqlStr
SELECT * FROM #usp_TestInsertWithSp_exec
DROP TABLE #usp_TestInsertWithSp_exec

Using sp_ExecuteSQL, OUTPUT Parameters

The below code gives a basic example of How to specify output parameters when we use the sp_executesql stored procedure in SQL Server.

Use AdventureWorks
GO
DECLARE @Employeeid int
DECLARE @NationalID nvarchar(15)
DECLARE @SqlStr nvarchar(500)
SET @EmployeeID=10
SET @SqlStr='SELECT @NationalID= NationalIDNumber FROM HumanResources.Employee WHERE EmployeeID= ' + Cast(@Employeeid as Varchar)+''
EXEC sp_executesql @SqlStr, N'@NationalID nvarchar(15) OUTPUT',@NationalID OUTPUT
PRINT @NationalID

I suppose, that I have covered most of the points on SP_ExecuteSQL, if you have any comments regarding this sp_ExecuteSQL topic or any other topic on SQL Server, Please feel free to comment.

If you have liked the article and want to get the articles (or) new posts at sql like, subscribe to articles through email by entering your email address in the top right and verifying the same.

Tagged as: Advanced SQL

{ Comments on this entry are closed }

SSAS Attribute Relationships

May 11, 2010

in Business Intelligence, SSAS

In the SSAS attribute relationships, what is the difference between Rigid and Flexible Relationships?

In simple terminology, by Rigid the following terms will come into our mind, something which is strict, something which is not so flexible, something which is fixed, firm or stiff and so on. So, even in Analysis Services, it has the same meaning, we use these for dimension attribute which do not change their values over a period of time, for example, if we take Time Dimension, the month May 2010 would always remain under the year 2010 (it’s true, you can believe me on thisJ, and any doubts, check calendar…), the same thing applies to a week in a month or a day in a week, the relationships never change.

So, with the same analogy, flexible is something that has a possibility of change (This is what we have to deal with most of the times, remember the rule, “Change is only constant”), in other terms, there is a possibility of members moving between dimension, taking a standard example for more clarity, let us take Organization -> Employee Department, an employee may be in Sales Department today, he or she can move to Marketing department, or vice versa or what ever other possibility… And another good example would be with respect to customer, customer’s address has a possibility to change, so this relationship between customer name and customer address is definitely going to be a Flexible one!!!

Now, with respect to analysis services, the default value for a relationship type property is flexible, so what does it really mean for us, flexible relationships require Analysis Services to drop and re-compute any existing aggregations during incremental dimension. If we change or define relationship between two attributes to Rigid, the analysis services by default takes the relationship as fixed and does not actually go and re-compute existing relationships and in other words lets the existing relationships remain without clearing them, thereby reducing the incremental process time.

In the above, the hard lines with dark arrows are the once representing the rigid relationships and the lighter once with transparent arrows are the flexible once.

If looked from other angle, with respect to MDX Query’s on the Cube with say… 30% aggregation, if we fire a MDX and say the numbers we need are not there in the aggregations present, so analysis services in turn has to calculate the numbers that we need from the details, now, it takes in to consideration all the relationships, suppose I need a number @ semester level which is not there in my current aggregations, so if I design a relationship in the time dimension like Year points to semester, semester point to quarter , quarter to month, month to week and week to day. So, here if analysis services calculate the required data for date level, by default it has the required information for returning the data that you need by doing proper summation. So, thus we can say that, the hierarchies with rigid relationships can be queried faster than those with flexible relationships. But, the issue that we would face is, changing any dependent attribute that has a rigid relationship within any hierarchy requires full process of the entire dimension.

Note:this should be present in a checklist for cube development process, because these settings really count for processing as well as query performance.

If you have liked the article and want to get the articles (or) new posts at sql like, subscribe to articles through email by entering your email address in the top right and verifying the same. If you have any comments, then the below form is for you, go ahead!!!

Tagged as: Performance, SSAS

{ Comments on this entry are closed }

Difference Between UNION and UNION ALL

May 9, 2010

in Developer, SQL Basics

What is the difference between union and union all in sql server?

The UNION operator joins result sets from tow or more SELECT statements. The basic Syntax of using “UNION” operator is

SELECT Column List FROM Table1

UNION [ALL]

SELECT Column List FROM Table2

ALL Clause in the query is optional

A couple of things that need to be kept in mind while using the “SQL UNION” operator are

The tables should have same data types for the columns
The Order in which we get the columns should be same
If we want to enforce Sort order for the final result set, we should have a “Order By” Clause on the last “SQL SELECT Statement” that we are going to have in our query.

One main difference between using the “ UNION ALL” clause and not using it is, when we use the “UNION” without the “ALL” Clause we get distinct records in the overall result set, but when we use the “UNION ALL” we get the complete set of results with out the removal of any duplicates.

So, based on this we can say that, UNION for SQL Server is just concatenation whereas UNION ALL has one more operation of identifying and removing the duplicates.

Now, let’s take an example to see what is happening???

Let’s go ahead and create two tables for our learning purpose, Table1 with a single int column and also table2 with single int column and insert some records with duplicates in them

Table 1

CREATE TABLE Table1(Col1 int)

INSERT INTO Table1 Values (1)

INSERT INTO Table1 Values (1)

INSERT INTO Table1 Values (2)

INSERT INTO Table1 Values (3)

INSERT INTO Table1 Values (3)

INSERT INTO Table1 Values (4)

Table 2

CREATE TABLE Table2(Col1 int)

 INSERT INTO Table2 Values (4)

INSERT INTO Table2 Values (5)

INSERT INTO Table2 Values (5)

INSERT INTO Table2 Values (6)

INSERT INTO Table2 Values (7)

INSERT INTO Table2 Values (8)

So, now let’s go and write the UNION queries on these tables and see what the result set is and also what is the Execution Plan for these queries to get better understanding of these operators.

SELECT Col1 FROM Table1

UNION

SELECT Col1 FROM Table2

SELECT Col1 FROM Table1

UNION ALL

SELECT Col1 FROM Table2

If you look at the operator that is used to unite the two sets for a UNION ALL, you’ll see it’s a Concatenation operator, combining two sets of data without any further thought of whether rows have previously been seen.

For a UNION, You can see a Distinct Sort as it makes sure it doesn’t return the same row twice.

So, now coming to the point on which one is better, from the above we can say that if duplicates are not an issue then definitely we should go for UNION ALL, but we need to really look at what our query needs to return and make sure users queries are answered correctly.

So, I think here I have pointed out nearly all the difference between union and union all in sql server.

If you have liked the article and want to get the articles (or) new posts at sql like, subscribe to articles through email by entering your email address in the top right and verifying the same. If you have any comments, then the below form is for you, go ahead!!!

Tagged as: SQL Queries, Syntax

{ Comments on this entry are closed }

Concatenate Numbers using SQL

April 29, 2010

in Developer, SQL Basics

How do we concatenate numbers in sql?

Let us take an example for this, say; we create a table with two integer columns.

USE TempDB

GO

CREATE TABLE concatenate_integers ( col1 int, col2 int)

GO

Now let us put a couple of records over here on which we can run our analysis on.

INSERT INTO concatenate_integers

Values(1,2)

INSERT INTO concatenate_integers

Values(11,12)

INSERT INTO concatenate_integers

Values(21,31)

GO

For concatenation in SQL Server, we use the “+” operator.

So, as a test let’s see what’s going to happen if we are going to write a query

select Col1,Col2,Col1+Col2 as Conc from concatenate_integers

Col1 Col2 Conc

1 2 3

11 12 23

21 31 52

Oh, we got a sum!!!

So, for integer type columns the “+” operator returns sum.

But that’s not what we are looking for what we want as result is 12, 1112, 2131…

So, first we need to cast these columns as strings before we concatenate the numbers.

Here it goes…

select Col1,Col2,Cast(Col1 as varchar)+Cast(Col2 as varchar) as Conc from concatenate_integers

Col1 Col2 Conc

1 2 12

11 12 1112

21 31 2131

Learning: The + sign sums integer type columns but concatenates character type columns.

You can also try the same with the below code.

DECLARE @A INT

DECLARE @B INT

DECLARE @C VARCHAR

DECLARE @D VARCHAR

SET @A=1

SET @B=2

SET @C = 'A'

SET @D = 'B'

SELECT @A+@B

-- RESULT IS 3

SELECT @C+@D

-- RESULT IS AB

SELECT CAST(@A AS VARCHAR) +CAST(@B AS VARCHAR)

-- RESULT IS 12

Tagged as: SQL Queries

{ Comments on this entry are closed }

Types of Keys in Database

April 17, 2010

in General

Super Key

A combination of one or more columns in a table which can be used to identify a record in a table uniquely, a table can have any number of super keys.

Candidate Key

A Column (or) Combination of columns which can help uniquely identify a record in a table without the need of any external data is called a Candidate Key. Depending on the need and situation a Table may have one or more candidate keys and one of them can be used as a Primary Key of the table.

A candidate key is a sub set of a Super Keys.

For Example, In an Employee Table, we may have columns like Employee ID, Employee Name, and Employee SSN. We can consider either Employee ID or Employee SSN as Candidate Key’s

Compound Key

A Combination of more than one column identifying records of a table uniquely, all the columns that take part in the combination process are Simple Key’s.

We can represent this buy taking a table which has the combined information of Department to Employees, Employee ID + Department ID – here both of them define a record in this table, but both can make sense separately as well.

Primary Key

A Column in a table (which is a Simple Key) which is a Candidate Key (Uniquely identify a records in a table) and has the Constraint NOT NULL attached to it is known as a Primary Key.

Generally, but not always and need not be, Primary Key goes with a Clustered Index.

Composite Primary Key (Composite Key)

When we have a Primary Key of a table defined using more than one columns then it is known as a Composite Key, each columns data can be duplicated, but combined values cannot be. The columns which are participating in a composite primary key are not simple keys.

For Example, we can have a situation where there is a need to define the key using first Name + last Name.

Alternate Key

We cannot define the Alternate Key Seperately from a Candidate Key, for a table, if there are two Candidate Key’s and one is chosen as a Primary Key the other Candidate Key is known as the Alternate Key of that table.

In Example, we can consider the Employee SSN as Alternate Key as we have taken Employee ID as our Primary Key

Unique Key

A column (or) combination of columns which can be used to uniquely identify a record in a table, it can have one NULL Value.

Primary Key can be considered a special case of unique key with a Not Null Constraint.

Generally, but not always and need not be, Unique Key goes with a Non Clustered Index.

Foreign Key

A column of one table points to the Primary Key column of another table to implement referential data integrity.

For Example, we can have a department id column in the employee table which is pointing to department id column in a department table where it a primary key.

Tagged as: RDBMS Concepts

{ Comments on this entry are closed }

SSAS Allowed Browsing Folders

February 20, 2010

in SSAS

This article is in response to a comment in my previous article “SSAS Database Backup”, the user said that he wants to take the backup of the analysis services database to a custom location and he could not exactly figure it out. When you are in taking the backup of a SSAS Database, and when yu are in the SSAS Database Backup dialog box, then you will see a message

“The server property, AllowedBrowsingFolders, determines which folders appear in this dialog box. To add or remove folders from this list, open the Analysis Server property box, and then edit the AllowedBrowsingFolders.”

Let’s follow the message and get our task done…

Open management studio and connect to the desired instance of analysis services/ desired server.
Select the particular instance you want to check the properties for and right click and select the properties option
Now you have a new window where you see a list of properties, with fields for Name, Value, Current Value, Default Value and if a restart of the analysis services is required if the particular property is changed.
At the bottom you have a couple of check boxes one being “Show Advanced (All) properties”, check this box.
Now at the beginning of the list of the properties, you will find “AllowedBrowsingFolders”, Now select the value field, press end, and first add a pipe symbol to tell the analysis services that you are adding a new folder in the list of folders which can be browsed(“|”), and add the full folder path, and press “OK”.

SSAS Properties Allowed Browsing Folders

That’s it when we go to the backup dialog box, we can see the newly added folder in the list of folders!!!

Tagged as: SSAS

{ Comments on this entry are closed }

← Previous Entries

Next Entries →

SQL Server Database Restore from SQL Server 2008 R2 to 2008

SQL Server Query to Find First and Last Day of Month, Week,Quarter, Year

SQL SERVER – Comma Separated Values (CSV) from Table Column

SQL Server Denali Installation

SP_ExecuteSQL Explained

Using sp_ExecuteSQL, sp_executesql error – Data Type of Vairable

Using sp_ExecuteSQL, sp_executesql error - Parsed and Compiled

Using sp_ExecuteSQL,Correct Method

Using sp_ExecuteSQL, Parameter Concatenation

Using sp_ExecuteSQL, How to Save the result set in a table

Using sp_ExecuteSQL, OUTPUT Parameters

SSAS Attribute Relationships

Difference Between UNION and UNION ALL

Concatenate Numbers using SQL

Types of Keys in Database

SSAS Allowed Browsing Folders

Subscribe

Topics

Recent Posts

Archives