Archive

Archive for the ‘SQL Server2008’ Category

Generate insert statements for existing data

March 30, 2010 3 comments

Once in a while I get this query: Do you know any tool to generate scripts for data in an existing table, of course for SQL Server?

Well, There are quite a few options.

If your company is rich enough to sponsor a tool then you could go for Redgate SQLToolBelt. This has quite a nice set of tools to generate scripts from existing data or new data and to compare scripts etc. 

You could also try out the free tool :  http://www.ssmstoolspack.com But this works only with SQL server Management Studio 2005 SP2 or above. 

If you are using SQL Server 2008 you could use the built in option SSMS in 2008. This is a quick run through to generate Insert statements for all of the data in your table, using no scripts or add-ins to SQL Management Studio 2008:

  1. DATABASE NAME: Right Click
  2. TASKS: GENERATE SCRIPTS
  3. Under Table/View Options: Set SCRIPT DATA = TRUE

You will then get the create table statement and all of the INSERT statements for the data straight out of SSMS.

You can also use this simple stored procedure given generously by Vyas.  The procedure that does the script generation is here.

I am using it for quite sometime now. Works perfect for my requirement!

Using INT or GUID for Primary Keys? Or COMB GUID?

January 21, 2010 3 comments

Today I read an interesting article by Jimmy Nilsson (the author of .Net Enterprise Design) about using auto-generated primary keys in SQL Server. Especially for Surrogate Keys.

Of course, most of our decisions would be to use INT (or BIGINT) as data type and set IDENTITY to true. There is another solution: using GUID. [NEWID in SQL Server].

When a comparison is done between using INT and GUID as primary key, we can list out few important differences:

Pros of using GUID

  1. GUID will be unique through out the database; while INT is not
  2. @@IDENTITY can be a problem while doing INSERT through TRIGGERS. And using @@IDENTITY to get the identity for the recently added row in the table brings the created identity for the current connection.[This could be solved using SCOPE_IDENTITY (see here for an excellent explanation on this!)]
  3. GUID can be used to create primary keys before inserting the value to the database
  4. Integers are not big enough for most of the scenarios; even though you could start from smallest negative value. Anyway BIGINT can be a solution here.
  5. Using INT could be a real night mare when doing manual merge of tables.

Cons of using GUID

  1. 1. In theory GUID presents a risk of getting duplicate GUIDs; but in practice it is not. In the recent windows version (from windows 2000) the algorithm to generate GUIDs doesn’t use the MAC address of the network cars. Instead it is just a random value.
  2. GUID is 4 times larger in size.
  3. There are portability problems, not all the database have GUID as a data type.
  4. Huge overhead; 4 times larger than INT.
    Evolution of COMB GUID

So apparently overhead is the main problem here. When Jimmy Nilsson did real  inserts using GUID as primary key he found out that time taken for inserts with GUID is 30 times greater than inserts with INT. That happened for 2 reasons

1. The generated GUIDs lacked the order; they were random. As you see in the below table only the 4 is common.

C87FC84A-EE47-47EE-842C-29E969AC5131
2A734AE4-E0EF-4D77-9F84-51A8365AC5A0
70E2E8DE-500E-4630-B3CB-166131D35C21
15ED815C-921C-4011-8667-7158982951EA
56B3BF2D-BDB0-4AFE-A26B-C8F59C4B5103

2. SQL insert statement makes the indexes to be reordered and this takes a lot of time. The new ordering for indexes depended on the last bytes

Therefore Jimmy Nilsson arrived at a new algorithm and his new GUID is called as COMB GUIDs.

Jeffery Palermo in his book ASP.NET MVC in Action uses this COMB GUID for his sample code to explain NHibernate. I am using this COMB GUID for my pet project.

Other Resources:

  1. GUIDs as PRIMARY KEYs and/or the clustering key
  2. NHibernate POID Generators revealed : COMB GUID does not cause database fragmentation
  3. INT vs Unique-Identifier for ID field in database
  4. GUIDs are one of the leading causes of index fragmentation
  5. Top tips for effective database managements

MySQL to SQLServer : Free Migration Assistant

January 13, 2010 Leave a comment

Microsoft announced the first CTP of a SQL Server Migration Assistant (SSMA) for MySQL v1.0 which automates aspects of the migration process to Microsoft SQL Server 2005 & 2008 including SQL Azure Database.

SQL Server Migration Assistant for MySQL is available for free download and preview at SSMA 2008 for MySQL v1.0 CTP1 and will be generally available in the summer of 2010. Customers and partners can also provide feedback via ssmateam@microsoft.com.  (via)

Dynamic Queries, Stored Procedures and SQL Injections

October 28, 2009 Leave a comment

As every one knows that Ad hoc dynamic queries are prone to SQL Injection attacks, I am not going to touch that. But there is still some confusion hanging over usage of dynamic sql with in a stored procedure. This is what I thought of blogging about.

Point 1: Using dynamic SQL with in stored procedure are prone to SQL Injection attack.

Other disadvantages of using dynamic SQL includes:

  1. Not readable and there for un maintainable code.
  2. Execution path is not saved there fore every time a stored procedure is run execution path is calculated again and again.
    But there are cases when we might need to use dynamic queries inside a stored procedure. What have to be done in this case?
    To demonstrate the sql injection attacks and to give a sample how to avoid this, I created a table named test with just one column [name].

    Table Definition

    USE [ASPNETDEV]
    GO
    /****** Object:  Table [dbo].[test1]    Script Date: 10/28/2009 15:14:07 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [dbo].[test](
        [name] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
    ) ON [PRIMARY]

    Insert the below values in to the table.

    insert into test values (‘muthu’);

    insert into test values (‘muthu1’);

Case 1: Procedure using static query

create procedure testsi
(@name nvarchar(10))
as
select * from test where [name]=@name

when we execute the above procedure with normal parameters (‘muthu’) it brings just 1 row.

exec testsi ‘muthu’

Now I give a value that introduces SQL injection as below

exec testsi ‘muthu”;drop table test;–”select * from test;’

When you look in to the value passed to the parameter @name ; you can very well see the SQL injection in the form of ‘’;drop table test;—. As you see, this  just closes the single quote and drops the table test. Well this is sql injection.

But to our surprise executing this does not drop the table and promptly brings in one row.

muthu

Because what we passed is just a value for the column [name] and obviously we don’t have any row in the table [test] with the column [name] having value ‘muthu”;drop table test;–”select * from test;’

Case 2: Procedure with dynamic Query 

create procedure testsid
(@name nvarchar(1000))
as
declare @sql as nvarchar(1000)
set @sql=’select * from test where [name]=”’ + @name + ””
print @sql
execute (@sql)

The line given in bold is the place where we use dynamic query.

Now lets execute this procedure using our SQL Injection value.

exec testsid ‘muthu”;drop table test;–”select * from test;’

Opps! Now the table is lost.

when you see the “Messages” tab in the Management Studio to your surprise it will be as follows:

select * from test where [name]=’muthu’;drop table test;–‘select * from test;’

(1 row(s) affected)

When you separate the statement using semicolon you will get 2 statements as follows; never mind the third one is commented.

  1. select * from test where [name]=’muthu’;
  2. drop table test;
  3. –‘select * from test;’

So this is SQL injection and this doesn’t just disappear if you use stored procedure.

Case 3: Procedure with dynamic query and avoiding SQL injection

However Microsoft has introduced a new way to run dynamic queries from the stored procedure using sp_executesql.

From MSDN:

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_executesql is more efficient than EXECUTE.

 

Please refer to the SQL server 2008 books online to get more information about this sp_executesql.

alter procedure testside
(@name nvarchar(1000))
as
declare @sql as nvarchar(1000)
declare @ParamDefinition nvarchar(500)
set @ParamDefinition = N’@name nvarchar(1000)’
set @sql=’select * from test where [name]=@name’

exec sp_executesql @sql, @ParamDefinition,@name

print @sql

In the above procedure we create a dynamic parameterized  query and we pass the query, the parameter definition and the value for the parameter to sp_excutesql procedure.

exec testside ‘muthu”;drop table test;–”select * from test;’

Even though we run the procedure with SQL Injection values to our surprise the table test does not get dropped.

But remember just using sp_executesql will not avoid sql injection attacks. It must be used sensibly. For example an example as follows is still susceptible to sql injection.

How not to use sp_executesql?

create procedure testsides
(@name nvarchar(1000))
as
declare @sql as nvarchar(1000)
declare @ParamDefinition nvarchar(500)
set @ParamDefinition = N’@name nvarchar(1000)’
set @sql=’select * from test where [name]=”’ + @name + ””
exec sp_executesql @sql, @ParamDefinition,@name

print @sql

Even though the procedure uses “sp_executesql” it is still prone to SQL injection because it does not use parameterized query.

But there are times when one cannot use parameterized queries, in this case there is no way but to use dynamic query. But in this case one must take extra-ordinary steps to validate the data. This article may help further understanding.

Update:

A live example for sql injection attack: Barackobama.com!

SQL Query : Comma separated values

September 17, 2009 Leave a comment

Level: Intermediate

This might be a very simple task for experienced programmers, but I want to point it out here in case if any one wonders how to do this.

Have you ever written a query that returns comma separated values from a column? Before I wrote this query I was thinking it must very hard to achieve this. But in truth it is not. Here is the table that has 3 columns namely Id, Name, and Dept.

I need to write a query that gives all the values in the name column separated by comma.Here is how to do it!

Id Name Dept
1 name1 dept1
2 name2 dept1
3 name3 dept1
4 name4 dept4
5 name5 dept4

declare @mname varchar(1000)

set @mname=”

select @mname= @mname + ltrim(rtrim(name)) + ‘,’ from table2

print @mname

Output: name1,name2,name3,name4,name5,

Happy Querying!

SQL Server 2008 : Prevent Saving changes

June 7, 2009 Leave a comment

Today I was working on a pet project and got this weird problem. I have worked with SQL Server 2005 but this is my first experience with SQL Server 2008. On the first very attempt with SQL Server I got this error from Visual Studio Management Studio.

SQLError-1

My change involves changing the column type from Nullable to Non-Nullable. So naturally SQL Server will have to drop the table and recreate the table. I understand.

But it is obvious that this message comes from Management Studio not from SQL Server. There must be some setting in Management Studio that does not allow me to do this change. I searched in the Tools  Options menu and aha I have struck gold.

SQLError2

Just uncheck “Prevent Saving changes that require table re-creation”. That’s it.
By the way, any good web3.0 project ideas for a asp.net pet project? Share with me