Hi Friends,

With respect to my last post, the decks & demo scripts of the event is uploaded on www.WeTogether.in

Regards
Amit

Hi Friends,

I recently concluded a community session for SQLServerGeeks.com – AB’s SQL Server day. SQLServerGeeks.com officially kicked off its series of community sessions on SQL Server branded as AB’s SQL Server day. The Delhi NCR chapter was delivered on 14th April 2010 at Microsoft Gurgaon. Sarabpreet Singh and I presented 1 session each on Back/Restore & SQL Server Spatial data respectively. The sessions were very well received and responded by the audience. The event was attended by 40 people approximately. Thanks to Microsoft, Rishu Mehra, Sarabpreet Singh, Amit Bansal, PeoplewareIndia, UGSS, GITCA & INETA for all their support in community activities.

All the pics can be seen on www.peoplewareindia.com/events.htm

Stay tuned. More events coming up !!!

Regards

Amit Bansal
www.amitbansal.net

Hi Friends,

SQLServerGeeks.com & PeoplewareIndia.com cordially invite you to AB’s SQL Server day on 24^th April 2010 (Saturday) at Microsoft Gurgaon from 10.30 am to 2 pm. Register at http://www.eDominer.com/CL/Registration1.aspx

Amit Bansal & Sarabpreet Singh will present power-packed sessions. This is a community event and is absolutely free. Session details are as follows:

10.30 am to 10.45 am – Introduction

Topic 1: Backup & Restore

Speaker: Sarabpreet Singh

Time: 10.45 am to 12 noon

Abstract: Guidelines for good backup Strategy

Role of recovery models in Backup \ Restore

Precautions while switching Recovery Models

How to avoid common pitfalls in Backup\ Restore

Tweaking to get maximum Performance in Backup\ Restore

12 noon to 12.30 pm – Refreshments & Networking

Topic 2: SQL Server Spatial stuff

Speaker: Amit Bansal

Time: 12.30 pm to 1.45 pm

Abstract: You might have attended many sessions (online webcasts & physical events) that show you how to insert & retrieve spatial data to and from a database in SQL Server 2008. You might have seen standard demos and code snippets that insert data into Geometry/Geography data type in a table and then easily retrieve it using T-SQL. But do you really know what spatial data is all about? Do you understand what is Geo-Spatial stuff is all about? Fathom this - Coordinate Systems, Cartesian coordinate systems, World Geodetic System, Spatial Reference System, Geographic Coordinate System, Spherical Coordinate System, etc. Already confused? Well, Spatial support is just not about 2 data types, it’s a whole world of complex data and Amit Bansal will make this difficult concept easy for you to understand and implement. Amit will present a well-balanced session with delivery of strong concepts followed by crisp demos. Get ready to develop your own Geo Spatial application !

1.45 pm to 2 pm – Closing note

Date: 24^th April 2010, Saturday

Time: 10.30 am to 2 pm

Venue: Microsoft, CyberGreen towers, DLF Phase II, 9^th Floor, Gurgaon

Contact: Rishu Mehra: 9818562858

Registration link: http://www.eDominer.com/CL/Registration1.aspx

Note: Registration is a must.

We look forward to your participation. Please forward this email to your friends & colleagues to make them aware of the event. Do spread the word by blogging about it and posting it in relevant forums. Your efforts in spreading the word will be highly appreciated.

Thanks & Regards

SQLServerGeeks.com

PeoplewareIndia.com

Hi Friends,

Just a quick recap. You can insert multiple records in a single statement starting from SQL Server 2008.

Try this:

——————

use tempdb
go

create table amit1 (name varchar(20), age int);
go
create table amit2 (name varchar(20), age int);
go

– Insert multiple records

INSERT INTO amit1 VALUES(’Amitabh’, 5), (’Abhishek’, 6);
go

SELECT * FROM amit1
go

——————

Now try this: does this work?

INSERT INTO amit2 VALUES ((SELECT Name FROM amit1), (SELECT Age FROM amit1))
go

what about this?

INSERT INTO amit2 VALUES ((SELECT TOP(1) Name FROM amit1), (SELECT TOP(1) Age FROM amit1))
go

Have fun with Row constructors

Regards

Amit
www.amitbansal.net
www.peoplewareindia.com

Hi Friends,

DateTime2 is the new data type in SQL Server 2008. You can say, its the next version of DateTime data type which has been there for years. So what’s the difference?

1. DateTime2 provides precision up to 7 digits. (nanosecond precision)

Try this:

select cast(’2010-04-14 12:00:00.347′ as DATETIME) as [old_datetime]

select cast(’2010-04-14 12:00:00.347′ as DATETIME2) as [new_datetime2]

Observe the results:

This also means that the following conversion will fail for DateTime data type but will succeed for DateTime2.

select cast(’2010-04-14 12:00:00.3473′ as DATETIME) as [old_datetime]

select cast(’2010-04-14 12:00:00.3473′ as DATETIME2) as [new_datetime2]

2. DateTime2 can store more date ranges.

Try this:

CREATE TABLE #DateTest
(Independence DATETime)

INSERT INTO #DateTest VALUES (’01/01/1500′)

Got an error right?

Try this now…

CREATE TABLE #DateTest2
(Independence DATETime2)

INSERT INTO #DateTest2 VALUES (’01/01/1500′)

Works

DateTime2 can store values from 1st January 0001 to 31st December 9999. But DateTIme could only store values from 01/01/1753 to 31/12/9999.

This means DateTime2 is ANSI compliant….

There are more intricacies that I can talk about, but sometime later…

Hope you enjoyed this.

Best Regards

Amit Bansal
www.amitbansal.net
www.peoplewareindia.com

Hi Friends,

There are many occasions when our SSIS packages are run for hours – typical long running packages. The flow involves one Data Flow Task (DFT) calling another DFT. Have you come across scenarios when a long running DFT completes successfully but the next DFT fails due to some reason and you have to run the package all over again after you have fixed the issue. Yes, I am talking about a mechanism where you provide restart points within your package and checkpoints exactly do that.

You can declare a checkpoint on any task as long as the package is configured to use checkpoints. When you run the package with checkpoints, SSIS tracks which tasks and containers have run successfully and updates the checkpoint file accordingly. Next time when you run the package again, it starts from where it failed, coool Thus, Checkpoints can help you avoid repeating time-consuming tasks.

For example, suppose that your package includes a Data Flow task followed by a Script task and then by an Execute SQL task. The Data Flow task extracts over one million rows of data from the data source, which takes a considerable amount of time to load. By default, if the package fails on the Script task, you must repeat the Data Flow task when you restart the package. However, if you configure checkpoints on the package, you only have to run those tasks that did not run successfully on the last execution, and you will not need to run the Data Flow task again.

There are 3 steps to implement checkpoints:

First, configure the package.

CheckpointFileName. This property specifies the path and file name of the
checkpoint file.

CheckpointUsage: This property specifies whether the package uses a
checkpoint file when it runs. (IfExists is the most common option) which means the package should use a checkpoint file only if one exists.

SaveCheckpoints: This property specifies whether Integration Services saves
checkpoints when the package runs. Set the property to True to save checkpoints.

Second,

To configure the checkpoints, set the FailPackageOnFailure property to True for
each container or task that you want to configure as a checkpoint.

Third,

Don’t forget to configure appropriate security on the folder where you will store the checkpoint file.

That’s it. Happy checkpointing

Hi Friends,

1. Which type of Index (clustered or non-clustered) is created by default with CREATE INDEX statement?

2. Which type of Index (clustered or non-clustered) is created by default when you create a Primary Key constraint?

3. Which type of Index (clustered or non-clustered) is created by default when you create a Unique constraint?

Yes, these are basics, but we all sometimes tend to wonder when suddenly asked about this SO here is a quick re-cap. Answers are below:

1. Non-clustered

2. Clustered

3. Non-clustered

Writing this at 1.26 am. Was feeling so sleepy so did not have the energy to write anything better. Goodnight friends.

Hi Friends,

What is the background process on the disk when you create a new database? The data and log files that have to be initialized for your database are initialized by overwriting any data left on the disk from previously delete files. What does this mean?

Suppose you have a 10 GB database. And you delete the database. The database certainly gets deleted and the operation cannot be undone. The space is also released to the OS. But the data still resides on the disk unless it is overwritten by some other file. Yes, you can see that OS has 10 GB free space because that space is available to be used.

Now, suppose after deleting that 10 GB database, you immediately create another database of 5 GB, SQL Server will initialize the data files for the new database by filling the file with zeros. And this may take some time. This is called zero initialization.

What if you want to create the files instantly? You can enable instant initialization. When you enable instant initialization, the database is created instantly but the files are not filled with zeros, instead the zeroing out operation happens later when you start writing data to the files for the first time.

Instant file initialization is only available if the SQL Server (MSSQLSERVER) service account has been granted SE_MANAGE_VOLUME_NAME. Members of the Windows Administrator group have this right and can grant it to other users by adding them to the Perform Volume Maintenance Tasks security policy.

So this is a kind of availability feature where you can make the data files quickly available to client applications.

Hi Friends, this is just a quick tip / knowhow. Following is the scenario:

There is a large table (approx 50 GB) and you want to move this large table from one filegroup to another, for performance and manageability reasons. While the the move operation is in process, you want your users to continue accessing the table? How do you do it?

You can use online operations in SQL Server. The trick here is to re-create the clustered index for that table on the other file group with ONLINE=ON.

FYI, there is no RECREATE DDL statement. You use the normal CREATE INDEX statement but you specify two clauses: DROP_EXISITING = ON, ONLINE=ON.

Since you are recreating the clustered index (clustered index being the data itself) on the new file group, complete data gets moved. DROP_EXISTING is to ensure that the index gets dropped on the original file group, ONLINE=ON ensure that users can continue accessing the table while moving operation is in process.

Online operations is one of the many high availability features in SQL Server, only available in Enterprise, Developer & Evaluations editions. Read Books online to know more.

Hi Friends,

I was spending some time with Database Snapshots today and came across a few limitations which are as follows:

1. The source database on which snapshot is base on, cannot be dropped, detached or re-created. - Backups will run fine

Source database here refers to the database for which you are creating a snapshot.

2. The file structure of the source database cannot be altered. In case, you want to do that, you have to first delete all the snapshots.

3. Database snapshots cannot be created for a portion of the source database. For example, you might want to create a snapshot only for the orders table: you can’t do that.

4. DB snapshots cannot reside on another instance – it has to be on the same instance as the source database.

5. Suppose you have created a snapshot on a separate volume. And the volume runs out of disk space – the snapshot will be marked suspect

6. Yes, snapshots are read only and cannot be created for system databases. Its only meant for user databases.

7. DB snapshots cannot be backed up, restored, attached or detached, doesn’t have a log file either.

8. Suppose you have multiple snapshots on a single source databases. And you want to revert the source db back to one of the snapshots. How do you do it? You have to first delete all the other snapshots and then revert to the one you want.

Use the following syntax to revert:

RESTORE DATABASE <database_name> FROM DATABASE_SNAPSHOT = <database_snapshot_name>

Hope this helps.