After reading above three posts, I am very confident that you all will be ready for the next set of puzzles now.
First execute the script which I have written here. Now guess what will be the next value as requested in the query.
GO -- Create sequence CREATE SEQUENCE dbo.SequenceID AS DECIMAL(3,0) START WITH 1
INCREMENT BY -1
NO CACHE; GO SELECT next value FOR dbo.SequenceID; -- Guess the number SELECT next value FOR dbo.SequenceID; -- Clean up DROP SEQUENCE dbo.SequenceID; GO
Please note that Starting value is 1, Increment value is the negative value of -1 and Minimum value is 3.
Now let us first assume how this will work out.
In our example of the sequence starting value is equal to 1 and decrement value is -1, this means the value should decrement from 1 to 0. However, the minimum value is 1. This means the value cannot further decrement at all. What will happen here? The natural assumption is that it should throw an error.
How many of you are assuming about query will throw anERROR? Well, you are WRONG! Do not blame yourself, it is my fault as I have told you only half of the story.
Now if you have voted for error, let us continue running above code in SQL Server Management Studio. The above script will give the following output:
Isn’t it interesting that instead of error out it is giving us result value 3. To understand the answer about the same, carefully observe the original syntax of creating SEQUENCE – there is a keyword CYCLE. This keyword cycles the values between the minimum and maximum value and when one of the range is exhausted it cycles the values from the other end of the cycle. As we have negative incremental value when query reaches to the minimum value or lower end it will cycle it from the maximum value. Here the maximum value is 3 so the next logical value is 3.
If your business requirement is such that if sequence reaches the maximum or minimum value, it should throw an error, you should not use the keyword cycle, and it will behave as discussed.
I hope, you are enjoying the puzzles as much as I am enjoying it. If you have any interesting puzzle to share, please do share with me and I will share this on blog with due credit to you.
Some kinds of confusion never go away. Here is one of the ancient confusing things in SQL. The precision of the SmallDateTime is one concept that confuses a lot of people, proven by the many messages I receive everyday relating to this subject.
Let me start with the question: What is the precision of the SMALLDATETIME datatypes?
What is your answer? Write it down on your notepad.
Since the increase of social media conversations, I noticed that the amount of the comments I receive on this blog is a bit staggering. I receive lots of questions on facebook, twitter or Google+. One of the very interesting questions yesterday was asked on Facebook by Raghavendra. I am re-organizing his script and asking all of the questions he has asked me. Let us see if we could help him with his question:
CREATE TABLE #temp (name VARCHAR(100),registered smalldatetime) GO DECLARE @test smalldatetime SET @test=GETDATE() INSERT INTO #temp VALUES ('Value1',@test) INSERT INTO #temp VALUES ('Value2',@test) GO SELECT * FROM #temp ORDER BY registered DESC GO DROP TABLE #temp GO
Now when the above script is ran, we will get the following result:
Well, the expectation of the query was to have the following result. The row which was inserted last was expected to return as first row in result set as the ORDER BY descending.
Side note: Because the requirement is to get the latest data, we can’t use any column other than smalldatetime column in order by. If we use name column in the order by, we will get an incorrect result as it can be any name.
My Initial Reaction
My initial reaction was as follows:
1) DataType DateTime2: If file precision of the column is expected from the column which store date and time, it should not be smalldatetime. The precision of the column smalldatetime is One Minute (Read Here) for finer precision use DateTime or DateTime2 data type.
Here is the code which includes above suggestion:
CREATE TABLE #temp (name VARCHAR(100), registered datetime2) GO DECLARE @test datetime2 SET @test=GETDATE() INSERT INTO #temp VALUES ('Value1',@test) INSERT INTO #temp VALUES ('Value2',@test) GO SELECT * FROM #temp ORDER BY registered DESC GO DROP TABLE #temp GO
2) Tie Breaker Identity: There are always possibilities that two rows were inserted at the same time. In that case, you may need a tie breaker. If you have an increasing identity column, you can use that as a tie breaker as well.
CREATE TABLE #temp (ID INT IDENTITY(1,1), name VARCHAR(100),registered datetime2) GO DECLARE @test datetime2 SET @test=GETDATE() INSERT INTO #temp VALUES ('Value1',@test) INSERT INTO #temp VALUES ('Value2',@test) GO SELECT * FROM #temp ORDER BY ID DESC GO DROP TABLE #temp GO
Those two were the quick suggestions I provided. It is not necessary that you should use both advices. It is possible that one can use only DATETIME datatype or Identity column can have datatype of BIGINT or have another tie breaker.
CREATE TABLE #temp (name VARCHAR(100),registered smalldatetime) GO DECLARE @test smalldatetime SET @test=GETDATE() INSERT INTO #temp VALUES ('Value1',@test) INSERT INTO #temp VALUES ('Value2',@test) GO SELECT name, registered, ROW_NUMBER() OVER(ORDER BY registered DESC) AS "Row Number" FROM #temp ORDER BY 3 DESC GO DROP TABLE #temp GO
However, I believe it is not the solution and can be further misleading if used in a production server. Here is the example of why it is not a good solution:
CREATE TABLE #temp (name VARCHAR(100) NOT NULL,registered smalldatetime) GO DECLARE @test smalldatetime SET @test=GETDATE() INSERT INTO #temp VALUES ('Value1',@test) INSERT INTO #temp VALUES ('Value2',@test) GO -- Before Index SELECT name, registered, ROW_NUMBER() OVER(ORDER BY registered DESC) AS "Row Number" FROM #temp ORDER BY 3 DESC GO -- Create Index ALTER TABLE #temp ADD CONSTRAINT [PK_#temp] PRIMARY KEY CLUSTERED (name DESC) GO -- After Index SELECT name, registered, ROW_NUMBER() OVER(ORDER BY registered DESC) AS "Row Number" FROM #temp ORDER BY 3 DESC GO DROP TABLE #temp GO
Now let us examine the resultset. You will notice that an index which is created on the base table which is (indeed) schema change the table but can affect the resultset. As you can see, an index can change the resultset, so this method is not yet perfect to get the latest inserted resultset.
No Schema Change Requirement
After giving these two suggestions, I was waiting for the feedback of the asker. However, the requirement of the asker was there can’t be any schema change because the application was used by many other applications. I validated again, and of course, the requirement is no schema change at all. No addition of the column of change of datatypes of any other columns. There is no further help as well.
This is indeed an interesting question. I personally can’t think of any solution which I could provide him given the requirement of no schema change. Can you think of any other solution to this?
Need of Database Designer
This question once again brings up another ancient question: “Do we need a database designer?” I often come across databases which are facing major performance problems or have redundant data. Normalization is often ignored when a database is built fast under a very tight deadline. Often I come across a database which has table with unnecessary columns and performance problems. While working as Developer Lead in my earlier jobs, I have seen developers adding columns to tables without anybody’s consent and retrieving them as SELECT *. There is a lot to discuss on this subject in detail, but for now, let’s discuss the question first. Do you have any suggestions for the above question?
Microsoft has just released SQL Server 2012 Upgrade Technical Guide. This guide is very comprehensive and covers the subject of upgrade in-depth. This is indeed a helpful detailed white paper. Even writing a summary of this white paper would take over 100 pages. This further proves that SQL Server 2012 is quite an important release from Microsoft. This white paper discusses how to upgrade from SQL Server 2008/R2 to SQL Server 2012. I love how it starts with the most interesting and basic discussion of upgrade strategies: 1) In-place upgrades, 2) Side by side upgrade, 3) One-server, and 4) Two-server. This whitepaper is not just pure theory but is also an excellent source for some tips and tricks. Here is an example of a good tip from the paper:
“If you want to upgrade just one database from a legacy instance of SQL Server and not upgrade the other databases on the server, use the side-by-side upgrade method instead of the in-place method.”
There are so many trivia, tips and tricks that make creating the list seems humanly impossible given a short period of time. My friend Vinod Kumar, an SQL Server expert, wrote a very interesting article on SQL Server 2012 Upgrade before. In that article, Vinod addressed the most interesting and practical questions related to upgrades. He started with the fundamentals of how to start backup before upgrade and ended with fail-safe strategies after the upgrade is over. He covered end-to-end concepts in his blog posts in simple words in extremely precise statements.
A successful upgrade uses a cycle of: planning, document process, testing, refine process, testing, planning upgrade window, execution, verifying of upgrade and opening for business. If you are at Vinod’s blog post, I suggest you go all the way down and collect the gold mine of most important links. I have bookmarked the blog by blogging about it and I suggest that you bookmark it as well with the way you prefer.
SQL Server 2012 Upgrade Technical Guide is a detailed resource that’s also available online for free. Each chapter was carefully crafted and explained in detail. Here is a quick list of the chapters included in the whitepaper. Before downloading the guide, beware of its size of 9 MB and 454 pages.
Here’s the list of chapters:
Chapter 1: Upgrade Planning and Deployment
Chapter 2: Management Tools
Chapter 3: Relational Databases
Chapter 4: High Availability
Chapter 5: Database Security
Chapter 6: Full-Text Search
Chapter 7: Service Broker
Chapter 8: SQL Server Express
Chapter 9: SQL Server Data Tools
Chapter 10: Transact-SQL Queries
Chapter 11: Spatial Data
Chapter 12: XML and XQuery
Chapter 13: CLR
Chapter 14: SQL Server Management Objects
Chapter 15: Business Intelligence Tools
Chapter 16: Analysis Services
Chapter 17: Integration Services
Chapter 18: Reporting Services
Chapter 19: Data Mining
Chapter 20: Other Microsoft Applications and Platforms
Appendix 1: Version and Edition Upgrade Paths
Appendix 2: SQL Server 2012: Upgrade Planning Checklist
Data is powerful. Data drives businesses. Data supports decision making and fuels progress. But managing data—making data work for us—isn’t inherently easy. And if there is one thing that is certain, it’s change—meaning that data systems created yesterday will need to be adapted to fit ever evolving needs. Since we can’t design our databases, data warehouses, and BI systems with every possible contingency in mind, we need data integration software that is smart enough to simplify the process and allow us to build more flexible solutions that can adapt to change and can let us focus on the value of our data—and not on the complexity of managing it.
I encourage you to watch this brief YouTube video to learn how easy it is to create your first data integration application with expressor Studio.
It has been more than 3 months since we have started experimenting with a new concept in SQL in Sixty Seconds. Every Wednesday, we putt a fresh new interesting concept out via video. Rick Morelan, Vinod Kumar and myself – the three of us decided to do something new and something exciting. We decided to create a short video which will consider the attention span of the viewer, keep them focused and help them learn something new through our teaching.
We all liked the idea of SQL in Sixty Seconds. As the name suggests, you will not watch content for more than a minute. We did our best to keep the video down to single minute, well, in most of the cases. There are cases when we could not accommodate the content in sixty seconds so the video had to roll for over few more seconds. In the end, we try to come up with an extremely short video which is able to teach some learning on SQL Server. So far we have 10 videos out for the SQL in Sixty Seconds series.
SQL in Sixty Seconds
Here are the details to our SQL in Sixty Seconds series. It has a total of 10 videos and a total cumulative time of around 15 minutes for all the videos.
We seriously need your help by means of rating our videos. All you have to do is to answer these three simple questions:
1. Which is your most favorite SQL in Sixty Seconds Episode? and Why? 2. Which is your least favorite SQL in Sixty Seconds Episode? and Why? 3. What next you would like to see in SQL in Sixty Seconds?
Just leave your answers in comment area.
Selection of Winner
On May 1st, we will go over the complete list. Rick, Vinod and I have also picked our most favorite episode and least favorite episode. You may call it a co-incidence or an accident, but our choice was exactly the same. Yes, three of us have selected the same video for best and least favorite videos. We will select the winner who has exactly the same selection as our selection. In case of a tie, we will look at the third question and as well as your answers to the “Why” follow-up question asked in the first and second questions.
We have decided to have more than one prize. This time, we’re giving away three prizes for the winners:
SQL Server Cheatsheet has been extremely popular download from my blog. There are plenty of request for me to update it with SQL Server 2012 features. I have finally upgraded the cheatsheet with SQL Server 2012 features. The new cheatsheet has following updates
SQL Server 2012 Datetime Functions
SQL Server Ranking Functions
SQL Server 2012 Analytic functions
It has been excellent experience to upgrade the cheatsheet. However, as the new cheatsheet contains way more information than earlier version of the cheatsheet, the size of the page is also increased. The page size is now no more A4 size, it has been increased to A3 size. When printed on A3 page it looks very clean and easy to read. If you print that on A4, you will find the pages are bit small.
Cheatsheet is free for anybody and everybody to download and print it, as long as you do not modify it. However, it is now allowed to store, host or upload anywhere without prior permission.
Previous cheatsheet was very popular and I have received many good comments. I am eagerly waiting for your feedback about this new updated cheatsheet.
It seems like a few year ago, there was a big “boom” in social media websites. All of a sudden there were so many sites to choose from. MySpace or Orkut? Blogging websites for your business or a LinkedIn account? The nature of the internet is to always be changing, but I believe that out of this huge growth of websites, a few have come to stay. Facebook is obviously the leader in social media networking, especially for your personal life. Blogging is great, but it can be more of a way to get your ideas out there, rather than a place for people to connect to you professionally. If you want to have a professional “face” on the internet, LinkedIn is the way to go.
LinkedIn is best explained as “professional Facebook.” This is simplifying things a little bit too much, but it is certainly a website where you link up with professional contacts, so that others can see where you have worked, who you have worked with, and what projects you have done. This is a much better place for professional contacts to find you than someplace like Facebook, where all they will see is your face and maybe picture of you at a birthday party or something like that!
Because so much of my SQL Server life is conducted on the internet, especially on my blog, I felt that it would be a good idea to have a well-maintained LinkedIn web page as well, so that if anyone is curious about me and my credentials they can quickly and easily find me and see that I am for real, and not someone pretending to know a lot about SQL Server.
My linked in profile is www.linkedin.com/in/pinaldave. I keep all my professional information here, and I update it as often as possible. Feel free to come find me, especially if you would like to “link up” and share professional information. The technology world is becoming more and more interconnected, and more and more international. I feel that it is very important to stay linked up virtually, because so many of us are so far apart physically.
I try to keep very connected with my LinkedIn profile. I let anyone connect with me, and I read updates from the professional world very often. I keep this profile updated, but do not post things about my personal life or anything that I might put on Twitter, for example. I also include my e-mail address here, if you would like to contact me professionally. This is the best place for me to conduct business.
Some people might not know it, but YouTube is actually more than a place to watch funny cat videos and people singing their favorite pop songs – it’s actually a social media site. When you are a member of YouTube you can follow people who regularly post videos, post video responses of your own, and even gain a following for your own videos. I myself was not aware of YouTube’s potential until recently, when I started to make SQL Server in Sixty Seconds videos.
YouTube is very different than other types of social media, and a big factor is that anyone can look at videos without being a member. Unlike other social media sites, like Twitter and Facebook, you have to have an account in order to participate. But on YouTube you are even more anonymous. To make and post videos you need an account, but anyone who comes to the site can look at what you’ve made without signing in or leaving any trace of having seen your material. This makes YouTube very anonymous and hard to track.
However, we should not overlook the power of video on the internet. Over the past few months I have been making SQL Server in Sixty Second videos and have come to love it. It is very exciting to be able to talk about a subject that mostly I write about, and for many people video is far more accessible and easy to understand. I have really enjoyed diving into something new, and would love to have more people check out these videos and give me feedback. You can find me at www.youtube.com/user/pinaldave.
I am very excited with all the possibilities on YouTube and it might just be the technology evangelist in me, but I would love for other people to discover how fun and exciting this site can be, too. Don’t think of it as just a place to find funny videos and waste a few minutes of your time, think of it as a place to learn and interact with interesting people. Come watch a few of my videos, while you’re there. Remember, everything is free and there are no contracts to sign, but I hope that you get as excited as I am and join up. We need more people creating good content on this site!
Frequent readers of my blog might know that I am trying to get more involved in all social media sites, both professionally and personally. Readers might also know that I have often struggled with finding the purpose of some social media sites – Twitter especially. One of the great uses of social media is to stay connected and updated with followers. Twitter’s 140 character limit means that Twitter is a great place to get quick updates from the world, but not a lot of deep information. In fact, I have the feeling that Twitter’s form might actually limit its usefulness – especially for complex subjects like SQL Server. However, #sqlhelp has tag has for sure overcome that belief. You can instantly talk about SQL and get help with your SQL problems on twitter.
I believe in keeping up with the changing times, and it didn’t feel right to give up on Twitter. So I have determined a good way to use Twitter and set rules for myself. The problem I was facing that if I followed everyone who interested me and let anyone follow me, I was completely overwhelmed by the amount of information Twitter could give me every day. It didn’t seem like 140 characters should be able to take up so much of my time, but it took hours to sort through all the updates to find things that were of interest to me and to SQL Server.
First, I was forced to unfollow anyone who made too many updates every day. This was not an easy decision, but just for my own sake I had to limit the amount of information I could take in every day. I still let anyone follow me who wants to, because I didn’t want to limit my readership, and I hope that they do not feel the way I did – that there are too many updates!
Next, I made sure that the information I put on Twitter is useful and to the point. I try to announce new blog posts on Twitter at least once a day, and I also try to find five posts from other people every day that are worth re-Tweeting. This forces me to stay active in the community. But it is not all business on Twitter. It is also a place for me to post updates about my family and home life, for anyone who is interested. In simple words, I talk every thing and anything on twitter.
If you’d like to follow me, my Twitter handle is www.twitter.com/pinaldave. It is a good place to start if you’d like to keep updated with my blog and find out who I follow and who my influences are. Twitter is perfect for getting little “tastes” of things you’re interested in. If you are interested in my blog, SQL Server, or both, I hope that my Twitter updates will be interesting and helpful.
TechEd India 2012 was held in Bangalore last March 21 to 23, 2012. Just like every year, this event is bigger, grander and inspiring.
Pinal Dave at TechEd India 2012
Every single year, TechEd is a special affair for my entire family. Four months before the start of TechEd, I usually start to build the mental image of the event. I start to think about various things. For the most part, what excites me most is presenting a session and meeting friends. Seriously, I start thinking about presenting my session 4 months earlier than the event! I work on my presentation day and night. I want to make sure that what I present is accurate and that I have experienced it firsthand. My wife and my daughter also contribute to my efforts. For us, TechEd is a family event, and the two of them feel equally responsible as well. They give up their family time so I can bring out the best content for the Community.
Pinal, Shaivi and Nupur at TechEd India 2012
Guinea Pigs (My Experiment Victims)
I do not rehearse my session, ever. However, I test my demo almost every single day till the last moment that I have to present it already. I sometimes go over the demo more than 2-3 times a day even though the event is more than a month away. I have two “guinea pigs”: 1) Nupur Dave and 2) Vinod Kumar. When I am at home, I present my demos to my wife Nupur. At times I feel that people often backup their demo, but in my case, I have backup demo presenters. In the office during lunch time, I present the demos to Vinod. I am sure he can walk my demos easily with eyes closed.
Pinal and Vinod at TechEd India 2012
I’ve been determined to present my sessions in a real and practical manner. I prefer to present the subject that I myself would be eager to attend to and sit through if I were an audience. Just keeping that principle in mind, I have created two sessions this year.
SQL Server Misconception and Resolution
Pinal and Vinod at TechEd India 2012
We believe all kinds of stuff – that the earth is flat, or that the forbidden fruit is apple, or that the big bang theory explains the origin of the universe, and so many other things. Just like these, we have plenty of misconceptions in SQL Server as well.
I have had this dream of co-presenting a session with Vinod Kumar for the past 3 years. I have been asking him every year if we could present a session together, but we never got it to work out, until this year came. Fortunately, we got a chance to stand on the same stage and present a single subject.
This session was inspired from the “Laurel and Hardy” show so we performed a role-playing of those famous characters. We had an excellent time at the stage and, for sure, the audience had a wonderful time, too. We had an extremely large audience for this session and had a great time interacting with them.
Speed Up! – Parallel Processes and Unparalleled Performance
Pinal Dave at TechEd India 2012
I wanted to approach this session at level 400 and I was very determined to do so. The biggest challenge I had was that this was a total of 60 minutes of session and the audience profile was very generic. I had to present at level 100 as well at 400. I worked hard to tune up these demos. I wanted to make sure that my messages would land perfectly to the minds of the attendees, and when they walk out of the session, they could use the knowledge I shared on their servers. After the session, I felt an extreme satisfaction as I received lots of positive feedback at the event. At one point, so many people rushed towards me that I was a bit scared that the stage might break and someone would get injured. Fortunately, nothing like that happened and I was able to shake hands with everybody.
Pinal Dave at TechEd India 2012
Crowd rushing to Pinal at TechEd India 2012
This is one of the primary reasons many of us visit the annual TechEd event. I had a fantastic time meeting SQL Server enthusiasts. Well, it was a terrific time meeting old friends, user group members, MVPs and SQL Enthusiasts. I have taken many photographs with lots of people, but I have received a very few back. If you are reading this blog and have a photo of us at the event, would you please send it to me so I could keep it in my memory lane?
SQL Track Speaker: Jacob and Pinal at TechEd India 2012
SQL Community: Pinal, Tejas, Nakul, Jacob, Balmukund, Manas, Sudeepta, Sahal at TechEd India 2012
Star Speakers: Amit and Balmukund at TechEd India 2012
TechED Rockstars: Nakul, Tejas and Pinal at TechEd India 2012
I guess TechEd is a mix of family affair and culture for me!