Category Archives: #SQLServer

All about Microsoft SQL Server

#0223 – Happy 2nd Birthday to my blog on BeyondRelational.com!


Today’s out-of-band post is for a very special reason – it’s been two (2) years since I started blogging on BeyondRelational.com and doing my bit to contribute back into the community as a gesture of thanks for all that the community has given me.

Thank-yous

I would take this opportunity to thank the following for their tremendous contribution and sacrifices in making this journey possible:

  • Jacob Sebastian (blog), Pinal Dave (blog) and Vinod Kumar (blog) for inspiring and introducing me to this wonderful SQL Server community
  • My dear wife, Ami and my respected parents, without whose love, blessings, sacrifices and support I wouldn’t have achieved this
  • My friends and colleagues at work who kept providing me feedback on my posts and encouraged me to write more
  • Finally, it’s you – the kind members of the community who take the time out from your busy schedules to read what I write – you are the most important contributors in this milestone

A quick snapshot of the last two years…

Here’s a quick summary of the splendid two year journey in the world of SQL Server and the community, in general. As I was preparing this list, I was surprised to see how much I did in these two years in addition to shouldering increased responsibilities at work. It just goes to show the satisfaction and drive that one gets by contributing to the community.

Ways to stay in touch with me

Ask Me a Question

You can always ask me a SQL Server Question at: http://beyondrelational.com/ask/nakul/default.aspx.

Please note that this is voluntary assistance, and therefore, I will only provide guidance. I will not do your work or assignments for you.

My Linked-In Profile:

You can take a look at my Linked In Profile here:

View Nakul Vachhrajani's profile on LinkedIn

Social Media Presence

You can follow me on the following:

Twitter: Tweet to @nakulv_sql

Google+: +Nakul

Concluding…

I would like to hear from you, especially what would you like me to do new in 2013! Keep the feedback coming – I really appreciate it!

Until we meet next time,

Be courteous. Drive responsibly.

#0222 – SQL Server – Stored procedures and temporary tables – Scope, Modification and some fun


Recently, I was asked a question on the ASK platform on this site (http://beyondrelational.com/modules/3/ask/questions/18239/table-variable-as-output-parameter-in-sql-server-stored-procedure-not-as-readonly-parameter.aspx):

How can one pass a table (as a variable or otherwise) to a stored procedure, but not as a read-only parameter?

As you already know, table valued parameters can be passed to stored procedures only as READONLY parameters. So, how can one implement a scenario wherein a table needs to be passed over to a procedure, modify the table inside it and then consume the results outside of the procedure?

The answer is available in the following posts from Madhivanan:

To summarize the solution:

  1. Create a temporary table
  2. Within the same session, execute a stored procedure – the temporary table will be available within this procedure
  3. Modify the contents of the table as required
  4. Once the procedure completes, the table continues to be available as long as the session is not changed

With this method of sharing tables between stored procedures, here’s an interesting test. In this test, we are creating another temporary table with the same name within the stored procedure and attempting to manipulate it.

USE tempdb
GO
--00. Safety check
IF OBJECT_ID('usrproc_SharingTempTablesTesting') IS NOT NULL
BEGIN
DROP PROCEDURE usrproc_SharingTempTablesTesting;
END
GO

IF OBJECT_ID('#SharingTempTables') IS NOT NULL
BEGIN
DROP TABLE #SharingTempTables;
END
GO

--01. Create temp. table
CREATE TABLE #SharingTempTables (iA INT DEFAULT 0,
iB INT DEFAULT 0,
iT AS (iA + iB)
)
GO

--02. Create stored procedure
CREATE PROCEDURE dbo.usrproc_SharingTempTablesTesting
@intA INT,
@intB INT
AS
BEGIN
SET NOCOUNT ON

--ATTENTION:
--Creating a temp. table with the same name inside of the procedure
CREATE TABLE #SharingTempTables (iA INT DEFAULT 0,
iB INT DEFAULT 0,
iT AS (iA - iB)
)

INSERT INTO #SharingTempTables (iA, iB) VALUES (@intA, @intB)

INSERT INTO #SharingTempTables (iA, iB)
SELECT 10*@intA, 20 * @intB FROM #SharingTempTables

--Selecting data from the temp. table
SELECT'Inside',* FROM #SharingTempTables
END
GO

Let’s now insert some test data into the stored procedure and execute the stored procedure (the scripts have been split into two parts for sake of better understanding. The entire script needs to be executed within the same session):

--Continues from the script above...
USE tempdb
GO

--03. Insert some test data
INSERT INTO #SharingTempTables (iA, iB)
VALUES (1,1), (1,2), (1,3), (1,4)
GO

--04. Execute the stored procedure
EXEC dbo.usrproc_SharingTempTablesTesting @intA = 2, @intB = 1
GO

--05. Select data from the temp table
SELECT 'Outside',* FROM #SharingTempTables
GO

image

Something did not go as expected. Had we used permanent tables instead of temporary tables, we would surely have ended up in an error because we cannot have two tables with the same name within the same database and schema. But, we did not encounter any error.

What’s even more interesting is that the results returned from inside of the stored procedure indicate that the data supplied via the table outside of the procedure has not been used.

Summarizing the behaviour until now:

  1. No error is encountered if a temp. table with a name duplicate to another pre-existing temp. table is used
  2. In such cases where temp. tables with the same name exist, the inner temp. table operates on a data set different from the one supplied from outside of the procedure

So, what’s going on?

We will make a small change to the procedure and the calling script as shown below – we will try to fetch the OBJECT_ID() of the temporary table alongwith the result sets.

--06. Create stored procedure
ALTER PROCEDURE dbo.usrproc_SharingTempTablesTesting
    @intA INT,
    @intB INT
AS
BEGIN
  SET NOCOUNT ON
--ATTENTION:
--Creating a temp. table with the same name inside of the procedure
CREATE TABLE #SharingTempTables (iA INT DEFAULT 0,
iB INT DEFAULT 0,
iT AS (iA - iB)
)

INSERT INTO #SharingTempTables (iA, iB) VALUES (@intA, @intB)

INSERT INTO #SharingTempTables (iA, iB)
SELECT 10*@intA, 20 * @intB FROM #SharingTempTables

--Selecting data from the temp. table
SELECT'Inside', OBJECT_ID('#SharingTempTables'),* FROM #SharingTempTables
END
GO

--07. Execute the stored procedure
EXEC dbo.usrproc_SharingTempTablesTesting @intA = 2, @intB = 1
GO

--08. Select data from the temp table
SELECT 'Outside',OBJECT_ID('#SharingTempTables'),* FROM #SharingTempTables
GO

image

As can be seen from the results, the inner temp. table is actually a separate object, and not the same as the one used outside of the stored procedure.

This is because the temp. table with duplicate name defined inside of the procedure is limited to the procedure in scope. If a table with duplicate name was not defined, the table defined outside of the procedure would still be available inside of the procedure.

Do you have any other interesting observations regarding temp. tables? If so, do share them here – I am sure the entire community would be eager to hear about them!

Until we meet next time,

Be courteous. Drive responsibly.

#0221 – SQL Server – SSMS – Database Properties – Number of Users value


Recently we were working on adjusting the configuration on a database which had just been moved to another server during a routine technology upgrade of our development servers (Reviewing and configuring your SQL Server instance). No users were yet allowed in the system and hence, one of the team members was surprised to note a “Number of Users” count higher than 1 in the database properties window of the SQL Server Management Studio (Getting Started with SSMS).

0221A

The team member obviously thought that the value of “Number of Users” indicated the number of distinct, currently open connections to the database. The confusion that the team member went through is understandable, and that’s what inspired me to write this quick post today.

The “Number of Users” value is actually the number of database users associated with the database – it is not, I repeat: not, the number of concurrent users connected to the database at a given point in time.

The number of users associated with a database for AdventureWorks2008R2 sample database is 4:

0221B

I trust the above quick post helps clear out the confusion around the “Number of Users” value in the database properties window of SSMS.

Until we meet next time,

Be courteous. Drive responsibly.

#0220-SQL Server-Enable single-click URL navigation


There are many productivity improving and usability tips and tricks related to the SQL Server Management Studio (SSMS). Ever since I started my blog (about 2 years ago), I have been writing mostly about these tips, and the list never ends! In today’s post, I will show how to configure the SSMS to recognize URLs within code comments.


One may place URLs in code comments for many reasons. Those that are applicable for me are:



  • Referencing a specification
  • Referencing a code defect/enhancement: The URL would point to the related TFS defect
  • A link to Sharepoint or any collaboration tool that development teams may be using
  • A link/citation to a website/blog that has been used as reference

For most languages & file extensions that the SSMS can handle, using Ctrl+click on any hyperlink opens the built-in web-browser and launches the associated page within SSMS. [One difference between SSMS for SQL 2012 and SQL 2008 is that the SSMS for SQL 2008 does not show the hyperlink in blue/underline].


Image showing single-click URL navigation, where using Ctrl+click over a hyperlink will launch the link in a browser.


The SSMS for SQL Server 2012 and below (tested for SQL Server 2008 and SQL 2008 R2), provide a text editor option – “Enable single-click URL navigation” that allows the user to control this behaviour. To access this option, go to Tools –> Options –> Text Editor –> All languages –> General (one can do this for specific languages too).


Options->Text Editor)” src=”http://media.beyondrelational.com/images.ashx?id=2e1b88231cf24e9c91c41ca0192eb778&w=-1&h=-1″ width=468 height=275>


Un-checking this option will cause SSMS to stop recognizing hyperlinks within code comments:


Options causes SSMS to stop recognizing hyperlinks.’ src=”http://media.beyondrelational.com/images.ashx?id=600b25791c0948408aec09821a2834b9&w=-1&h=-1″ width=510 height=148>


The benefit of keeping “Enable single-click URL navigation” checked is that when developing and reviewing T-SQL code, it becomes very easy to reference and reach out to related documentation. I trust you found this feature useful.


Until we meet next time,


Be courteous. Drive responsibly.

#0219-SQL Server-SSMS for 2012-”Restore File Associations” Tools Option


Recently, I deployed Microsoft SQL Server 2012 on to one of my test workstations which already had SQL Server 2008 R2 installed on it. Later, I uninstalled SQL Server 2008 R2 from the workstation and suddenly, I noticed something strange – the SQL Server related script and solution files which were supposed to open up in SSMS failed to do so – the icon was set to a default “unknown program” too.


To re-associate these files with the SSMS, there are two possible options:


Option 1: Use the Control Panel to associate a program to a given file type



  1. Launch the Control Panel
  2. Go to Programs –> Default Programs
  3. Select “Associate a file type or protocol with a specific program”
  4. Scroll through the window and select the required file extension
  5. Choose “Change Program” and select the required program (in this case, SSMS) to associate it with the file type at hand

(*This method is valid for SQL Server 2012 and below).


Option 2: Use the SSMS to restore broken file associations related to Visual Studio shell


The SSMS is nothing but a Visual Studio shell. Hence, the SSMS for SQL Server 2012 extends the core benefits of Visual Studio 2010 shell. One such feature/benefit is the ability to Restore File Associations from the Tools menu.



  1. Within the SSMS for SQL Server 2012, go to Tools –> Options
  2. Under the General page in the Environment node, click on “Restore File Associations”
  3. image
  4. The following confirmatory message will be received, indicating that the file associations have been restored as expected
  5. image

(*This method is applicable from SQL 2012 only. SSMS for SQL Server 2008 R2 and below does not have this feature.)


(Please NOTE: For me, this did not work as expected when using the SSMS for SQL Server 2012 RTM. However, when I deployed SQL Server 2012 SP1, things worked as expected. Do share your findings in the comments below as applicable.)


Until we meet next time,


Be courteous. Drive responsibly.