SQL SERVER – RESEED Identity Column in Database Table – Rest Table Identity Value – SQL in Sixty Seconds #051

This is the 51th episode of SQL in Sixty Seconds Video and we will see in this episode how to RESEED identity of the table column. Identity column is every increasing (or decreasing) value based on the interval specified in its property. In today’s SQL in Sixty Seconds video we will see that how we can reseed the identity value to any other value. In the video I demonstrate that we can set the value to any value which is greater than the current column value however, you can also set the identity value to any value lower than the current column as well.

In the real world, developers sometimes delete few rows from the table and due to any reasons they want to reseed the identity value to a lower value, this feature can be very much useful. However, there is one question which keeps on ringing in my mind that is why would anyone need to reseed identity. Anyway here is the SQL in Sixty Seconds Video which explains how we can reseed identity value.

Let us see the same concept in following SQL in Sixty Seconds Video:

Here is the script used in this episode:

USE tempdb
GO
-- Create Table
CREATE TABLE TestTable (ID INT IDENTITY(1,1), Col1 VARCHAR(100));
-- Insert Table
INSERT INTO TestTable (Col1)
SELECT 'First'
UNION ALL
SELECT 'Second'
UNION ALL
SELECT 'Third'
UNION ALL
SELECT 'Fourth'
UNION ALL
SELECT 'Fifth';
-- Select data
SELECT *
FROM TestTable
GO
-- Returns current Identity Value
DBCC CHECKIDENT ('TestTable', NORESEED);
-- Resets the current Identity value to maximum value
DBCC CHECKIDENT ('TestTable', RESEED, 11);
-- Insert Table
INSERT INTO TestTable (Col1)
SELECT 'First'
UNION ALL
SELECT 'Second'
UNION ALL
SELECT 'Third'
UNION ALL
SELECT 'Fourth'
UNION ALL
SELECT 'Fifth';
-- Select Table
SELECT *
FROM TestTable
ORDER BY ID;
-- Drop Table
DROP TABLE TestTable;

Related Tips in SQL in Sixty Seconds:

What would you like to see in the next SQL in Sixty Seconds video?

Click to Download Scripts

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Interview Questions and Answers Sample Chapter Free Download – SQL in Sixty Seconds #050

This journey of SQL in Sixty Seconds we started almost a year ago and today we are at very interesting milestone where I am recording 50th episode. Thought I wanted to keep the length of each video to sixty seconds, sometimes it went up by a few seconds. Due to this we are also at very interesting milestone as well – today’s 50th episode also accumulates the play time for entire playlist to 60 minutes (complete 1 hour).

There are two different milestones to celebrate today.

  1. This is the 50th Episode of SQL in Sixty Seconds
  2. Total play time for SQL in Sixty Seconds is One hour complete

This journey of SQL in Sixty Seconds we started almost a year ago and today we are at very interesting milestone where I am recording 50th episode. Thought I wanted to keep the length of each video to sixty seconds, sometimes it went up by a few seconds. Due to this we are also at very interesting milestone as well – today’s 50th episode also accumulates the play time for entire playlist to 60 minutes (complete 1 hour).

You can watch the entire SQL in Sixty Seconds series over here

On SQLAuthority.com one of the most viewed and popular articles are SQL Server Interview Questions and Answers. It has been consistently asked and referred again and again. Earlier I also had a book released on this subject which is also very popular in the industry. Here in today’s SQL in Sixty second I explain how you can download the sample chapters of the popular SQL Server Interview Questions and Answers book in PDF for FREE.

Let us see the same concept in following SQL in Sixty Seconds Video:

Related Tips in SQL in Sixty Seconds:

What would you like to see in the next SQL in Sixty Seconds video?

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Tricky Question – What is the Default Size of the Database

I love tricky questions – they are fun and educating. Yesterday I was presenting in one of the largest organization in India on SQL Server Performance Tuning Subject. During the conversation, one of the user suggested that every single time they are creating new database it is created with the big MDF file. They were wondering how come they have always very large file when they create new database. It was indeed a fun question to be asked.

In reply to the same question – I asked following question to the audience.

What is the default size of the SQL Database?

I got many different answers for the question. Here are few of the answers.

  • 3 MB
  • 4MB
  • Many other numbers….
  • As big as integer
  • 8060 bytes

…and so on and so forth. However, the real answer is as follows:

The default size of the user database is based on system database model.

When we create a new user database it is modeled after model database and created with same settings and properties. If your model database has large mdf or ldf file the same can be automatically replicated when a new user database is created. This was the actual case in the scenario mentioned earlier. On a separate note, with the default installation of SQL Server the mdf file of model database is 3 MB and ldf is about 1 MB.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Monday Morning Puzzle – Query Returns Results Sometimes but Not Always

The amount of email I receive sometime it is impossible for me to answer every email. Nonetheless I try to answer pretty much every email I receive. However, quite often I receive such questions in email that I have no answer to them because either emails are not complete or they are out of my domain expertise. In recent times I received one email which had only one or two lines but indeed attracted my attention to it. The question was bit vague but it indeed made me think. The answer was not straightforward so I had to keep on writing the answer as I remember it. However, after writing the answer I do not feel satisfied. Let me put this question in front of you and see if we all can come up with a comprehensive answer.

Question: I am beginner with SQL Server. I have one query, it sometime returns a result and sometime it does not return me the result. Where should I start looking for a solution and what kind of information I should send to you so you can help me with solving. I have no clue, please guide me.

Well, if you read the question, it is indeed incomplete and it does not contain much of the information at all. I decided to help him and here is the answer, which I started to compose.

Answer: As there are not much information in the original question, I am not confident what will solve your problem. However, here are the few things which you can try to look at and see if that solves your problem.

  • Check parameter which is passed to the query. Is the parameter changing at various executions?
  • Check connection string – is there some kind of logic around it?
  • Do you have a non-deterministic component in your query logic? (In other words – does your result is based on current date time or any other time based function?)
  • Are you facing time out while running your query?
  • Is there any error in error log?
  • What is the business logic in your query?
  • Do you have all the valid permissions to all the objects used in the query? Are permissions changing or query accessing a different object in various executions?
  • (Add your suggestions here)

Meanwhile, have you ever faced this situation? If yes, do share your experience in the comment area. I will send a copy of my book SQL Server Interview Questions and Answers to one of the most interesting comment. The winner will be announced by next Monday. 

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – A Puzzle – Swap Value of Column Without Case Statement

For the last few weeks, I have been doing Friday Puzzles and I am really loving it. Yesterday I received a very interesting question by Navneet Chaurasia on Facebook Page. He was asked this question in one of the interview questions for job. Please read the original thread for a complete idea of the conversation. I am presenting the same question here.

Puzzle

Let us assume there is a single column in the table called Gender. The challenge is to write a single update statement which will flip or swap the value in the column. For example if the value in the gender column is ‘male’ swap it with ‘female’ and if the value is ‘female’ swap it with ‘male’.

Here is the quick setup script for the puzzle.

USE tempdb
GO
CREATE TABLE SimpleTable (ID INT, Gender VARCHAR(10))
GO
INSERT INTO SimpleTable (ID, Gender)
SELECT 1, 'female'
UNION ALL
SELECT 2, 'male'
UNION ALL
SELECT 3, 'male'
GO
SELECT *
FROM SimpleTable
GO

The above query will return following result set.

The puzzle was to write a single update column which will generate following result set.

There are multiple answers to this simple puzzle. Let me show you three different ways. I am assuming that the column will have either value ‘male’ or ‘female’ only.

Method 1: Using CASE Statement

I believe this is going to be the most popular solution as we are all familiar with CASE Statement.

UPDATE SimpleTable
SET Gender = CASE Gender WHEN 'male' THEN 'female' ELSE 'male' END
GO
SELECT *
FROM SimpleTable
GO

Method 2: Using REPLACE  Function

I totally understand it is the not cleanest solution but it will for sure work in giving situation.

UPDATE SimpleTable
SET Gender = REPLACE(('fe'+Gender),'fefe','')
GO
SELECT *
FROM SimpleTable
GO

Method 3: Using IIF in SQL Server 2012

If you are using SQL Server 2012 you can use IIF and get the same effect as CASE statement.

UPDATE SimpleTable
SET Gender = IIF(Gender = 'male', 'female', 'male')
GO
SELECT *
FROM SimpleTable
GO

You can read my article series on SQL Server 2012 various functions over here.

Let us clean up.

DROP TABLE SimpleTable
GO

Question to you:

I came up with three simple tricks where there is a single UPDATE statement which swaps the values in the column. Do you know any other simple trick? If yes, please post here in the comments. I will pick two random winners from all the valid answers. Winners will get 1) Print Copy of SQL Server Interview Questions and Answers 2) Free Learning Code for Online Video Courses

I will announce the winners on coming Monday.

Reference:  Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – #TechEdIn – Presenting Tomorrow on Speed Up! – Parallel Processes and Unparalleled Performance at TechEd India 2012

Performance tuning is always a very hot topic when it is about SQL Server. SQL Server Performance Tuning is a very challenging subject that requires expertise in Database Administration and Database Development. I always have enjoyed talking about SQL Server Performance tuning subject. However, in India, it’s actually the very first time someone is presenting on this interesting subject, so this time I had the biggest challenge to present this session.

Frequently enough, we get these two kind of questions:

  1. How to turn off parallelism as it is reducing performance?
  2. How to turn on parallelism as I want more performance?

The reality is that not everyone knows what exactly is needed by their system. In this session, I have attempted to answer this very question. I’ve decided to provide a balanced view but stay away from theory, which leads us to say “It depends”. The session will have a clear message about this towards its end.

Deck Details

  • Slides: 45+
  • Demos: 7+ Bonus
  • Quiz: 5
  • Images: 10+
  • Session delivery time: 52 Mins + 8 Mins of Q & A

I have presented this session a couple of times to my friends and so far have received good feedback. Oftentimes, when people hear that I am going to present 45 slides, they all say it is too much to cover. However, when I am done with the session the usual reaction is that I truly gave justice to those slides.

Action Item

Here are a few of the action items for all of those who are going to attend this session:

If you want to attend the session, just come early. There’s a good chance that you may not get a seat because right before me, there is a session from SQL Guru Vinod Kumar. He performs a powerful delivery of million concepts in just a little time.

Quiz. I will be asking few questions during the session as well as before the session starts. If you get the correct answer, I will give unique learning material for you. You may not want to miss this learning opportunity at any cosst.

Session Details

Title: Speed Up! – Parallel Processes and Unparalleled Performance (Add to Calendar)

Abstract: “More CPU, More Performance” – A  very common understanding is that usage of multiple CPUs can improve the performance of the query. To get a maximum performance out of any query, one has to master various aspects of the parallel processes. In this deep-dive session, we will explore this complex subject with a very simple interactive demo. Attendees will walk away with proper understanding of CX_PACKET wait types, MAXDOP, parallelism threshold and various other concepts.

Date and Time: March 23, 2012, 12:15 to 13:15

Location: Hotel Lalit Ashok – Kumara Krupa High Grounds, Bengaluru – 560001, Karnataka, India.

Add to Calendar

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – #TechEdIn – Presenting Tomorrow on SQL Server Misconception and Resolution with Vinod Kumar at TechEd India 2012

I am excited AND nervous at the same time. I am going to present a very interesting topic tomorrow at an SQL Server track in India. This will be my fourth time presenting at TechEd India. So far, I have received so much feedback about this one session. It seems like every single person out there has their own wishes and requests. I am sure that it is going to very challenging experience to satisfy everyone who attends the event through my presentation.

Surprise Element

Here is the good news: I am going to co-present this session with Vinod Kumar, my long time friend and co-worker. We have known each other for almost four years now, but this is the very first time that we are going to present together on the big stage of TechEd.  When there are more than two presenters, the usual trick is to practice the session multiple times and know exactly what each other is going to present and talk about. However, there’s a catch – we decided to make it different this time and have shared nothing to each other regarding what exactly we are going to present. This makes everything extremely interesting as each of us will be as clueless as the audience when other person is going to talk.

Action Item

Here are a few of the action items for all of those who are going to attend this session. Vinod and I will be present at the venue 15 minutes before the session. Do come in early and talk with us. We would be glad to talk with you and see if either of us can accommodate your suggestion in our session. If we do, we will give a surprise gift for you.

As discussed, this session is going to be a unique two-presenter session. You will have chance to take a side with one speaker and stump the other speaker. Come early to decide which speaker you want to cheer during the session.

Quiz and Goodies

By now, you must have figured out that this session is going to be an extremely interactive session. We need your support through your active participation. We will have some really brain-twisting quiz line up just for you. You will have to take part and win surprises from us! Trust me. If you get it right, we will give you something which can help you learn more!

We will have a quiz on Twitter as well. We will ask a question in person and you will be able to participate on Twitter.

10 – Demos

As I said, both of us do not know what each other is going to present, but there are few things which we know very well. We have 10 demos and 6 slides. I think this is going to be an exciting demo marathon. Trust me, you will love it and the taste of this session will be in your mouth till the next TechEd.

Session Details

Title: SQL Server Misconceptions and Resolution – A Practical Perspective (Add to Calendar)

Abstract:

“The earth is flat”! – An ancient common misconception, which has been proven incorrect as we progressed in modern times. In this session, we will see various database misconceptions prevailing and their resolutions with the aid of the demos. In this unique session, the audience will be a part of the conversation and resolution.

Date and Time: March 21, 2012, 15:15 to 16:15

Location:
 Hotel Lalit Ashok – Kumara Krupa High Grounds, Bengaluru – 560001, Karnataka, India.

Add to Calendar

Reference: Pinal Dave (http://blog.sqlauthority.com)