SQL SERVER – Resolving SQL Server Connection Errors – SQL in Sixty Seconds #030 – Video

One of the most famous errors related to SQL Server is about connecting to SQL Server itself. Here is how it goes, most of the time developers have worked with SQL Server and knows pretty much every error which they face during development language. However, hardly they install fresh SQL Server. As the installation of the SQL Server is a rare occasion unless you are DBA who is responsible for such an instance – the error faced during installations are pretty rare as well.

I have earlier written an article about this which describes how to resolve the errors which are related to SQL Server connection. Even though the step by step directions are pretty simple there are many first time IT Professional who are not able to figure out how to resolve this error. I have quickly built a video which is covering most of the solutions related to resolving the connection error.

In the Fix SQL Server Connection Error article following workarounds are described:

  1. SQL Server Services
  2. TCP/IP Settings
  3. Firewall Settings
  4. Enable Remote Connection
  5. Browser Services
  6. Firewall exception of sqlbrowser.exe
  7. Recreating Alias

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 – Find Rows and Index Count – SQL in Sixty Seconds #029 – Video

There are a few questions I often get asked. I wonder how interesting is that in our daily life all of us have to often need the same kind of information at the same time. Here is the example of the similar questions:

  • How many user created tables are there in the database?
  • How many non clustered indexes each of the tables in the database have?
  • Is table Heap or has clustered index on it?
  • How many rows each of the tables is contained in the database?

I finally wrote down a very quick script (in less than sixty seconds when I originally wrote it) which can answer above questions. I also created a very quick video to explain the results and how to execute the script.

Here is the complete complete script which I have used in the SQL in Sixty Seconds Video. Thanks Harsh for important Tip in the comment.

SELECT [schema_name] = s.name, table_name = o.name,
MAX(i1.type_desc) ClusteredIndexorHeap,
COUNT(i.TYPE) NoOfNonClusteredIndex, p.rows
FROM sys.indexes i
RIGHT JOIN sys.objects o ON i.[object_id] = o.[object_id]
INNER JOIN sys.schemas s ON o.[schema_id] = s.[schema_id]
LEFT JOIN sys.partitions p ON p.OBJECT_ID = o.OBJECT_ID AND p.index_id IN (0,1)
LEFT JOIN sys.indexes i1 ON i.OBJECT_ID = i1.OBJECT_ID AND i1.TYPE IN (0,1)
WHERE o.TYPE IN ('U')
GROUP BY s.name, o.name, p.rows
ORDER BY schema_name, table_name

Above script have small modification as suggested by Harsh in comment.

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)

SQLAuthority News – A Conversation with an Old Friend – Sri Sridharan

Sri Sridharan SQLAuthority News   A Conversation with an Old Friend   Sri SridharanSri Sridharan is my old friend and we often talk on GTalk. The subject varies from Life in India/USA, movies, musics, and of course SQL. We have our differences when we talk about food or movie but we always agree when we talk about SQL. Yesterday while chatting with him we talked about SQLPASS and the conversation lasted for a long time. Here is the conversation between us on GTalk. I have removed a few of the personal talks and formatted into paragraphs as GTalk often shows stuff out of formatting.

Pinal: Sri, Congrats on running for the PASS BoD again. You were so close last year. What made you decide to run again this year?
Sri: Thank you Pinal for your leadership in the PASS India Community and all the things you do out there. After coming so close last year, there was no doubt in my mind that I will run again. I was truly humbled by the support I got from the community. Growing up in India for over 25 years, you are brought up in a very competitive part of the world. Right from the pressure of staying in the top of the class from kindergarten to your graduation, the relentless push from your parents about studying and getting good grades (and nothing else matters), you land up essentially living in a pressure cooker. To survive that relentless pressure, you need to have a thick skin, ability to stand up for who you really are , what you want to accomplish and in the process stay true those values.

I am striving for a greater cause, to make PASS an organization that can help people with their SQL Server careers, to make PASS relevant to its chapter members, to make PASS an organization that every SQL professional in the world wants to be connected with. Just because I did not get elected or appointed last year does not mean that these causes are not worth fighting. Giving up upon failing the first time is simply not in me. If I did that, what message would I send to those who voted for me? What message would I send to my kids?

Pinal: As someone who has such strong roots in India, what can the Indian PASS Community expect from you?
Sri: First of all, I think fostering a regional leadership is something PASS must encourage as part of its global growth plan. For PASS global being able to understand all the issues in a region of the world and make sound decisions will be a tough thing to do on a continuous basis. I expect people like you, chapter leaders, regional mentors, MVPs of the region start playing a bigger role in shaping the next generation of PASS. That is something I said in my campaign and I still stand by it.

I would like to see growth in the number of chapters in India. The current count does not truly represent the full potential of that region. I was pretty thrilled to see the Bangalore SQLSaturday happen early this year. I would like to see more of SQLSaturday events, at least in the major metro cities. I know the issues in India are very different from the rest of the world. So the formula needs to be tweaked a little for it to work better in India.

Once the SQLSaturday model is vetted out, maybe there could be enough justification to have SQLRally India. PASS needs to have a premier SQL event in that region. Going to USA or Europe for that matter is incredibly hard due to VISA issues etc. So this could be a case of where PASS comes closer to where the community is.

Pinal: What portfolio would take on if you are elected to the PASS Board?
Sri: There are some very strong folks on the PASS Board today. The President discusses the portfolios with the group and makes the final call on the portfolios. I am also a fan of having a team associated with the portfolios. In that case, one person is the primary for a portfolio but secondary on a couple of other portfolios. This way people on the board have a direct vested interest in a few portfolios.

Personally, I know I would these portfolios good justice – Chapters, Global Growth and Events (SQLSat, SQLRally). I would try to see if we can get a director to focus on Volunteers.  To me that is very critical for growth in the international regions.

Pinal: This is an interesting conversation with you Sri. I know you so long time but this is indeed inspiring to many. India is a big country and we appreciate your thoughts.

Sri: Thank you very much for taking time to chat with me today. Cheers.

There are pretty strong candidates for SQLPASS Board of Elections this year. I know all of them in person and honestly it is going to be extremely difficult to not to vote for anybody. I am indeed in a crunch right now how to pick one over another. Though the choice is difficult, I encourage you to vote for them. I am extremely confident that the new board of directors will all have the same goal – Better SQL Server Community.

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

SQL SERVER – Manage Help Settings – CTRL + ALT + F1

It is a miracle that curiosity survives formal education. ~ Albert Einstein

questionofkids SQL SERVER   Manage Help Settings   CTRL + ALT + F1I have 3 years old daughter and she never misses any chance to play with the system. I have multiple computers and I always make sure that if I am working with production server, I never leave it open but when I am doing some experiment I often leave my computer open. My daughter loves the part when I have left the computer open and I am not observing her. Recently I had the same scenario, I got urgent call and I moved away from my computer and when I returned she was playing with SSMS left open my computer. Here is the screen which was visible on the screen.

updatehelp3 SQL SERVER   Manage Help Settings   CTRL + ALT + F1

For a moment, I could not figure out what was this screen and what was about to get updated. I tried to ask her what keys she pressed the reaction was “I wanted – eya eya o”. Well, what more I expect from 3 years old. She is no computer genius – she just learned to use notepad and paint on my machine.

Finally, when I saw the above screen in detail, I realize that this screen was from the help screen and something got updated. I have been using SQL Server for a long time but I never updated help on the screen. When I need to search something if I remember that I have written it earlier I will go to http://search.sqlauthority.com and will search there or will search on Google.

As this computer was already updated I fired up Virtual Machine and tried to look recreate how my daughter was reached to above screen. Here are the steps which I have to do to reach to above screen.

Go to SSMS >> Toolbar >> Help >> Manage Help Settings (or type CTRL+ALT+F1) and click it.

updatehelp1 SQL SERVER   Manage Help Settings   CTRL + ALT + F1

Above click brought up following screen.

updatehelp2 SQL SERVER   Manage Help Settings   CTRL + ALT + F1

I clicked on Check for update online brought following screen up.

updatehelp SQL SERVER   Manage Help Settings   CTRL + ALT + F1

When I clicked on Update it brought me back to original screen which my daughter was able to bring up earlier.

updatehelp3 SQL SERVER   Manage Help Settings   CTRL + ALT + F1

I found it so interesting that what took me 2-3 minutes to figure out and the screen which I have never come across in my career I learned from my curiosity like my daughter.

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

SQL SERVER – Identify Most Resource Intensive Queries – SQL in Sixty Seconds #028 – Video

sixtysecs SQL SERVER   Identify Most Resource Intensive Queries   SQL in Sixty Seconds #028   VideoDuring performance tuning conversation the very first question people often ask is what are the queries offending the server or in another word let us identify the queries which are the most resource intensive. The resources are often described as either Memory, CPU or IO. When we talk about the queries the same is applicable for them as well. The query which is doing lots of reads or writes are for sure resource intensive as well query which are taking maximum CPU time.

Performance tuning is a very deep subject and we all have our own preference regarding what should be the first step to tuning and what should be looked with the salt of grain. Though there is no denying that a query which uses more resources than what it should be using for sure require tuning. There are many ways to do identify query using intense resources (e.g. Extended events etc) but in this one we will go by simple DMV.

There is a small gotcha we all have to remember about usage of DMV is that it only brings back results from existing cache. So if you have a query which is very resource intensive but is not cached or if you have explicitly removed the query from the cache it will be not part of the result returned by this DMV. It is quite possible that a query is aged and removed from the cache if your cache is not huge. If your cache is large you may want to be careful in running this query during business hours as this query itself can be resource intensive.

Get Script to identify resource intensive query from Here

Related Tips in SQL in Sixty Seconds:

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

SQL SERVER – Copy Column Headers from Resultset – SQL in Sixty Seconds #027 – Video

sixtysecs SQL SERVER   Copy Column Headers from Resultset   SQL in Sixty Seconds #027   VideoSQL Server Management Studio returns results in Grid View, Text View and to the file. When we copy results from Grid View to Excel there is a common complaint that the column  header displayed in resultset is not copied to the Excel. I often spend time in performance tuning databases and I run many DMV’s in SSMS to get a quick view of the server. In my case it is almost certain that I need all the time column headers when I copy my data to excel or any other place.

SQL Server Management Studio have two different ways to do this.

Method 1: Ad-hoc

When result is rendered you can right click on the resultset and click on Copy Header. This will copy the headers along with the resultset. Additionally, you can use the shortcut key CTRL+SHIFT+C for coping column headers along with the resultset.

Method 2: Option Setting at SSMS level

This is SSMS level settings and I kept this option always selected as I often need the column headers when I select the resultset.

Go Tools >> Options >> Query Results >> SQL Server >> Results to Grid >> Check the Box “Include column header when copying or saving the results.”

Both of the methods are discussed in following SQL in Sixty Seconds Video.

Here is the code used in the video.

Related Tips in SQL in Sixty Seconds:

If we like your idea we promise to share with you educational material.

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

SQL SERVER – Effect of Collation on Resultset – SQL in Sixty Seconds #026 – Video

sixtysecs SQL SERVER   Effect of Collation on Resultset   SQL in Sixty Seconds #026   VideoCollation is a very important concept but often ignored. I have often seen developers either not understanding this or ignored it – this is plain wrong. In simple word we can say Collation is the language or interpreting done by SQL Server. Well, in today’s SQL in Sixty Seconds we are going to observe how collation affects the resultset. Today’s blog post is inspired from my earlier blog post SQL SERVER – Effect of Case Sensitive Collation on Resultset. I strongly encourage you to read this earlier blog post for sample code as well additional explanation related to the concept shared in today’s SQL in Sixty Seconds.

Here is the code used in the video.

USE TempDB
GO
-- Sample Data Building
CREATE TABLE ColTable
(Col1 VARCHAR(15) COLLATE Latin1_General_CI_AS,
Col2 VARCHAR(14) COLLATE Latin1_General_CS_AS) ;
INSERT ColTable(Col1, Col2)
VALUES ('Apple','Apple'),
(
'apple','apple'),
(
'pineapple','pineapple'),
(
'Pineapple','Pineapple');
GO
-- Retrieve Data
SELECT *
FROM ColTable
GO
-- Retrieve Data
SELECT *
FROM ColTable
ORDER BY Col1
GO
-- Retrieve Data
SELECT *
FROM ColTable
ORDER BY Col2
GO
-- Clean up
DROP TABLE ColTable
GO

Related Tips in SQL in Sixty Seconds:

If we like your idea we promise to share with you educational material.

Click to Download Scripts

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

SQL SERVER – Get Date and Time From Current DateTime – SQL in Sixty Seconds #025 – Video

sqlinsixtyseconds SQL SERVER   Get Date and Time From Current DateTime   SQL in Sixty Seconds #025   Video

This is 25th video of series SQL in Sixty Seconds we started a few months ago. Even though this is 25th video it seems like we have just started this few days ago. The best part of this SQL in Sixty Seconds is that one can learn something new in less than sixty seconds. There are many concepts which are not new for many but just we all have 60 seconds to refresh our memories. In this video I have touched a very simple question which I receive very frequently on this blog.

Q1) How to get current date time?
Q2) How to get Only Date from datetime?
Q3) How to get Only Time from datetime?

I have created a sixty second video on this subject and hopefully this will help many beginners in the SQL Server field. This sixty second video describes the same. Here is a similar script which I have used in the video.

SELECT GETDATE()
GO
-- SQL Server 2000/2005
SELECT
CONVERT(VARCHAR(8),GETDATE(),108) AS HourMinuteSecond,
CONVERT(VARCHAR(10),GETDATE(),101) AS DateOnly;
GO
-- SQL Server 2008 Onwards
SELECT CONVERT(TIME,GETDATE()) AS HourMinuteSeconds;
SELECT CONVERT(DATE,GETDATE()) AS DateOnly;
GO

Related Tips in SQL in Sixty Seconds:

I encourage you to submit your ideas for SQL in Sixty Seconds. We will try to accommodate as many as we can.

If we like your idea we promise to share with you educational material.

Image Credit: Movie Gone in 60 Seconds

Click to Download Scripts

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

SQL SERVER – Three Methods to Insert Multiple Rows into Single Table – SQL in Sixty Seconds #024 – Video

One of the biggest ask I have always received from developers is that if there is any way to insert multiple rows into a single table in a single statement. Currently when developers have to insert any value into the table they have to write multiple insert statements. First of all this is not only boring it is also very much time consuming as well. Additionally, one has to repeat the same syntax so many times that the word boring becomes an understatement.

In the following quick video we have demonstrated three different methods to insert multiple values into a single table.

-- Insert Multiple Values into SQL Server
CREATE TABLE #SQLAuthority (ID INT, Value VARCHAR(100));

Method 1: Traditional Method of INSERT…VALUE

-- Method 1 - Traditional Insert
INSERT INTO #SQLAuthority (ID, Value)
VALUES (1, 'First');
INSERT INTO #SQLAuthority (ID, Value)
VALUES (2, 'Second');
INSERT INTO #SQLAuthority (ID, Value)
VALUES (3, 'Third');

Clean up
-- Clean up
TRUNCATE TABLE #SQLAuthority;

Method 2: INSERT…SELECT

-- Method 2 - Select Union Insert
INSERT INTO #SQLAuthority (ID, Value)
SELECT 1, 'First'
UNION ALL
SELECT 2, 'Second'
UNION ALL
SELECT 3, 'Third';

Clean up
-- Clean up
TRUNCATE TABLE #SQLAuthority;

Method 3: SQL Server 2008+ Row Construction

-- Method 3 - SQL Server 2008+ Row Construction
INSERT INTO #SQLAuthority (ID, Value)
VALUES (1, 'First'), (2, 'Second'), (3, 'Third');

Clean up
-- Clean up
DROP TABLE #SQLAuthority;

Related Tips in SQL in Sixty Seconds:

I encourage you to submit your ideas for SQL in Sixty Seconds. We will try to accommodate as many as we can.

If we like your idea we promise to share with you educational material.

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

SQL SERVER – Color Coding SQL Server Management Studio Status Bar – SQL in Sixty Seconds #023 – Video

sixty SQL SERVER   Color Coding SQL Server Management Studio Status Bar   SQL in Sixty Seconds #023   Video

I often see developers executing the unplanned code on production server when they actually want to execute on the development server. Developers and DBAs get confused because when they use SQL Server Management Studio (SSMS) they forget to pay attention to the server they are connecting. It is very easy to fix this problem. You can select different color for a different server. Once you have different color for different server in the status bar, it will be easier for developer easily notice the server against which they are about to execute the script.

Personally when I work on SQL Server development, here is the color code, which I follow. I keep Green for my development server, blue for my staging server and red for my production server. Honestly color coding does not signify much but different color for different server is the key here.

More Tips on SSMS in SQL in Sixty Seconds:

I encourage you to submit your ideas for SQL in Sixty Seconds. We will try to accommodate as many as we can.

If we like your idea we promise to share with you educational material.

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