SQL SERVER – Delay Command in SQL Server – SQL in Sixty Seconds #055

Have you ever needed WAIT or DELAY function in SQL Server?  Well, I personally have never needed it but I see lots of people asking for the same. It seems the need of the function is when developers are working with asynchronous applications or programs. When they are working with an application where user have to wait for a while for another application to complete the processing.

If you are programming language developer, it is very easy for you to make the application wait for command however, in SQL I personally have rarely used this feature.  However, I have seen lots of developers asking for this feature in SQL Server, hence I have decided to build this quick video on the same subject.

We can use WAITFOR DELAY ‘timepart‘ to create a SQL Statement to wait.

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)

About these ads

SQL SERVER – Need Your Feedback – Next Action Items in SQL in Sixty Seconds

SQL in Sixty Seconds series has been going on for over a year and we have over 50 videos. Here is one idea. I am planning to do a series on one topic in SQL in Sixty Seconds. I would like to gather your feedback about what should I create this series on. Here is a poll where you can select the option you think will interest you the most.

Additionally, it will be really nice if you leave comments with your opinion about what I should cover in SQL in Sixty Seconds video. I will pick the 3 best comments and will share USD 15 Amazon Gift card with them for their valuable time.

I have heard it is easy to get more participation if we put cute kitten’s photo. I have attempted that in this blog post and I hope to get more responses.

Meanwhile, here are few of the last SQL in Sixty Seconds Video.

RESEED Identity Column in Database Table

Puzzle SET ANSI_NULLS and Resultset

NuoDB in Sixty Seconds

Maximize Database Performance with DB Optimizer

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

SQL SERVER – Maximize Database Performance with DB Optimizer – SQL in Sixty Seconds #054

Performance tuning is an interesting concept and everybody evaluates it differently. Every developer and DBA have different opinion about how one can do performance tuning. I personally believe performance tuning is a three step process

  1. Understanding the Query
  2. Identifying the Bottleneck
  3. Implementing the Fix

While, we are working with large database application and it suddenly starts to slow down. We are all under stress about how we can get back the database back to normal speed. Most of the time we do not have enough time to do deep analysis of what is going wrong as well what will fix the problem. Our primary goal at that time is to just fix the database problem as fast as we can. However, here is one very important thing which we need to keep in our mind is that when we do quick fix, it should not create any further issue with other parts of the system.

When time is essence and we want to do deep analysis of our system to give us the best solution we often tend to make mistakes. Sometimes we make mistakes as we do not have proper time to analysis the entire system. Here is what I do when I face such a situation – I take the help of DB Optimizer. It is a fantastic tool and does superlative performance tuning of the system.

Everytime when I talk about performance tuning tool, the initial reaction of the people is that they do not want to try this as they believe it requires lots of the learning of the tool before they use it. It is absolutely not true with the case of the DB optimizer. It is a very easy to use and self intuitive tool. Once can get going with the product, in no time. Here is a quick video I have build where I demonstrate how we can identify what index is missing for query and how we can quickly create the index.

Entire three steps of the query tuning are completed in less than 60 seconds. If you are into performance tuning and query optimization you should download DB Optimizer and give it a go.

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

You can Download DB Optimizer and reproduce the same Sixty Seconds experience.

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 – NuoDB in Sixty Seconds – SQL in Sixty Seconds #053

Earlier this week, I have done five part blog series on NuoDB and it was very well received by audienceNuoDB is an elastically scalable SQL database that can run on local hostdatacenter and cloud-based resources. t is an operational NewSQL database built on a patented emergent architecture with full support for SQL and ACID guarantees. In this blog post, I will explore how one can download and install NuoDB database.

In this video I explain how one can install NuoDB in very few seconds and set up the entire environment in additional few seconds. One can get going with installation of NuoDB and sample database in total of less than 60 seconds.

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

You can Download NuoDB and reproduce the same Sixty Seconds experience.

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 – SQL in Sixty Seconds – Last Three Episodes – Need Your Opinion

I have been blogging for almost 7 years and building video content for around 2 years. After spending so much time on blogging and creating video, I have got the quite a good idea what people would like to read and what people like to watch. However, there is one thing, which I am constantly struggling after almost a year and I would like to get your opinion about it. Though, this may look very simple to you but it is very crucial to me and I would like to know your opinion about it.

I have been building video almost every week for my SQL in Sixty Seconds series and it has been quite popular. So far on my YouTube Channel there are over 2600 subscribers and over 250K views. Here is my problem – there are about 50+ videos on SQL in Sixty Seconds Series but the not every video is popular. There are a few videos which are extremely popular and there are videos which are absolutely struggling to get even single view. I have yet not figured out what people would love to watch on this channel. I noticed lots of people watching various videos but hardly anyone leaving comments or suggestions. At the end of the blog posts associated with the SQL in Sixty Seconds, I always ask which video people would love to watch, but I get a very low response over there too.

What I wonder is that why such a low engagement of viewers/readers on the video blog posts where as the channel is success and lots of people are watching the video? What do you think I should change in my video to increase the engagement?

Here are my last three videos from SQL in Sixty Seconds channel and I would like to know your feedback.

Remove Cached Login from SSMS Connect Dialog – SQL in Sixty Seconds #049

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

Puzzle SET ANSI_NULLS and Resultset – SQL in Sixty Seconds #052

 The feedback which I will like the most will for sure get a special surprise gift for me.

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

SQL SERVER – Puzzle SET ANSI_NULLS and Resultset – SQL in Sixty Seconds #052

Earlier I have posted a puzzle where I was receiving different results when I executed two different queries. I encourage all of you to read the original puzzle here, the puzzle had received many fantastic responses and I have later blogged about the solution of the puzzle over here.

Now I have decided to extend the same puzzle and take it to the next level. In earlier puzzle I had value of the ANSI_NULLS was set to ON. Now in this puzzle let us set the value of the ANSI_NULLS to OFF. When the value of ANSI_NULLS was off at that time, the result of the both the queries is almost identical. The key reason behind this behavior is ANSI_NULLS setting.

However, the puzzle is to answer that why the different queries are producing almost same results when there is a different setting of ANSI_NULL.

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

Here is the script used in this episode:

-- Original Puzzle
http://bit.ly/sql-puzzle-ansi
-- Script
SET ANSI_NULLS ON;
-- Query1
SELECT 'SQLAuthority' AS Statement11
WHERE 'Authority' IN ('S','Q', 'L', 'Authority', NULL);
-- Query 2
SELECT 'SQLAuthority' AS Statement12
WHERE 'Authority' NOT IN ('S','Q', 'L', NULL);
-- Puzzle Solution
http://bit.ly/sql-puzzle-ansi-sol
------------------------------------------------------
-- New puzzle
SET ANSI_NULLS OFF;
-- Query3
SELECT 'SQLAuthority' AS Statement11
WHERE 'Authority' IN ('S','Q', 'L', 'Authority', NULL);
-- Query 4
SELECT 'SQLAuthority' AS Statement12
WHERE 'Authority' NOT IN ('S','Q', 'L', NULL);

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 – 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)