Identity Functions

Over several years of working with RDBMS products like MS SQL Server (since 2000) and Oracle 11g, there are times when really simple concepts are so darn confusing for whatever reason.  I imagine that it really depends on the person about what they find confusing but for me the proper use of @@IDENTITY and SCOPE_IDENTITY() and IDENT_CURRENT() always causes me pause. 

My work as a .NET developer and DBA and Data Warehouse Architect has required extensive use of this code and it seems that regardless of the type of database work you do it is very important to have a solid grip on what the differences really are.

There are many advantages to being certified in multiple version of SQL Server but what I consider one of the disadvantages of studying towards multiple certifications on different versions is having to review some of the more simple concepts over and over.  For example, an INT data type was the same in 2008 when I started studying towards my MCTS in SQL Server 2005 and it is the same today.   

Luckily studying some of the same concepts repeatedly really serves as a refresher.  This is one of those things that no matter how many times I go over it I can’t help but enjoy confirming the difference between the three pieces of code.

It really boils down to scope being the main difference.  I’ll assume we all know what an IDENTITY is.  It is just a property of a table column that is very helpful in creating key values in an easy and predictable way.  

When we insert into a table that contains a column that has a primary key that is defined as an Identity then we don’t need to tell it what value to be.  The identity property does it for us.

Now when we are coding sql stored procs or functions there are times when we need to know more information about the identity values that are used when inserting rows into tables.  That is where @@IDENTITY, SCOPE_IDENTITY() and IDENT_CURRENT() come in handy.

IDENT_CURRENT()

The one that I find easies to remember is IDENT_CURRENT() function.  If we pass it a the name of a table such as IDENT_CURRENT(‘Sales.Product’) then it will tell us the last identity value that was given to that table.

Now this is where it starts to get a bit more confusing J.

@@IDENTITY

This one returns the most recent identity value given to a database engine connection and it doesn’t care about the table or even the scope.  

SCOPE_IDENTITY()

This one returns the most recent identity value given to a database engine connection but it does care about the scope of the statement but still doesn’t care about the table.

Still confused?

Let’s look at some simple code to help make it more clear.

Ill first define a table called IdentitySample with an identity column called ID.

CREATE TABLE dbo.IdentitySample

(

Id INT NOT NULL IDENTITY(1, 1)

CONSTRAINT PK_ID PRIMARY KEY,

ProductId INT NOT NULL,

SampleDate DATE NOT NULL

);

If I insert some sample data into a sample table as such.

INSERT INTO dbo.IdentitySample(ProductId, SampleDate) VALUES

(1, '20150502'),

(1, '20150603'),

(2, '20150708');

Then view the data.

Id            ProductId            SampleDate

1              1                              20150502

2              1                              20150603

3              2                              20150708

Now if I call three of the identity columns discussed in the same session that I ran the last insert in I get the following.

SELECT

IDENT_CURRENT(‘dbo.IdentitySample’) as A,

@@IDENTITY as B ,

SCOPE_IDENTITY() as C;

I get the following:

A             B             C

3              3              3

They all have the same value which is the last identity value assigned to the dbo.IndentitySample table.

If I were to open another window and rerun the same statement I would get the following.

A             B             C

NULL     NULL     3

This is because for the scope of this newly opened connection there has been no identity values issued but the last identity issued to the dbo.IdentitySample is still 3.

Now if you were using a stored proc to insert a row and a new identity value was assigned then the SCOPE_IDENTITY() function will return value that that stored proc created.   

Now if the stored proc were to call a different stored proc (out of scope) then @@IDENTITY would return the value of the by that stored proc regardless of the table or the scope of the statement that created the identity.

Thanks for reading, 

SQL Canuck

You can read more about this at 

https://msdn.microsoft.com/en-CA/library/ms187342.aspx

Use configuration file to install SQL Server 2012

Microsoft introduced a ‘Server Core’ version of Windows Server 2008 as basically a stripped down minimal installation option for the higher editions such as Standard, Enterprise and Datacenter.  It contains the features that are necessary to provide your users with all the usual Microsoft services such as Sql Server or Active Directory.

Here is a picture of the much simplified GUI in Windows Server 2012.

There are many reasons why Microsoft has provided us with this including reduced resource consumption, better security and less management.  On the flip side, quite often IT shot callers are sometimes reluctant to adopt this new version because it can seem scary to learn something new or a fear that troubleshooting an emergency with limited GUI could be risky.

Regardless of the pros and cons, I wanted to share a way of installing SQL Server 2012 that works well with Server Core or even on a full GUI Server version in a way that makes the install process of SQL Server much faster and as easy as clicking a batch file for users who don’t have much experience installing SQL Server 2012.  This method uses a file called the configuration file and this file is referenced at the command line prompt.

Where does a person get one of these configuration files?  A configuration file is just a text file that provides instructions on how the install should look.  It contains name/value pair parameters with comments that describe each line.

The file can be generated by stepping through a normal installation using the regular installation media but an option to completing the install is to just stop at the ‘Ready to Install’ page and noting the path to the config file at the bottom of the page as seen below.

Once you cancel the installation, the INI file is generated.  Move the newly created config file to the location where you want to start the installation.

The only way to install SQL Server by using the config file is at the windows CMD prompt.

At the CMD prompt type in the following command referencing the config file you just generated.

Setup.exe /ConfigurationFile=MyConfigurationFile.INI

Obviously there are many flags and switches and options that a person can include in this command to change things like the Silent Mode or to accept the licence term etc.  You can edit the config file as needed and once you start the install then it will complete unattended if you include the QUIET =’True’. 

You can see where this would save a DBA responsible for installing SQL Server on many hosts a lot of time.

You can learn more on this topic at Technet.

https://technet.microsoft.com/en-us/library/dd239405(v=sql.110).aspx

Thanks for reading,

SQL Canuck

Uninstall a named instance in SQL Server 2012

Over the last several years as I have been studying towards my Microsoft SQL Server certifications in many different versions,  I have had to install demo versions of the database engine to work on the examples that the study materials provide.  The demo versions are usually good for a limited amount of time such as 6 months.  I will generally install a named instance that is specific to whatever study book I’m working through.   When the time comes that the demo instance has expired,  I have found it necessary to uninstall a specific named instance but leave other instances undisturbed.

This is a quick step by step guide on uninstalling a named instance while leaving the default instance alone.

Step 1. Open the Control Panel and click on Uninstall a program.

Step 2.  Locate Microsoft 2012 from the list of installed programs.  Right click on it and select Uninstall.

Step 3. Click on Remove.

Step 4.  After the uninstall wizard completed the Setup Support Rules dialog, click on OK.

Step 5.  This is the most critical step.  From the “Instance to remove features from:” drop down select the desired instance name.  Then click Next.

Step 6.  Select the features you wish to uninstall from the instance.  In this case, we wish to preserve the shared features but uninstall the database engine for the desired instance.  Click Next.

 Step 7.  Once Removal Rules has completed, 

click Next.

Step 8.  Review the summary on the Ready to Remove screen.  Click Remove.

Step 9.  The removal will show its progress then once complete you click Close on the Complete screen.

Keep in mind this only removes the database engine for the named instance you specify.  It does not remove any shared features that other instances use.  If you have a named instance that is a time sensitive demo version then it is best to uninstall these once expired so they don’t interrupt any other instances you have.

Thanks for reading,
MSSQL Canuck

Edit sql agent job if you aren't the owner or sysadmin

In a previous job I had as a Database Administrator III, our team was responsible for creating and maintaining SQL agent jobs that ran various database maintenance tasks and we would often use the standard maintenance plan wizard that comes with SSMS.

The wizard would provide you with an easy way to create simple common database maintenance jobs to keep your database healthy and happy.  Such tasks were things like the database check integrity task or the reorganize index task.

A common issue we would come across while maintaining these jobs had to do with the ability to edit the job if you weren’t the owner.  It was standard for us to create the job and use a common dba account so that all the dbas could edit the job but from time to time a dba would create the job using their own personal AD account and not the standard dba.  This didn’t cause too much inconvenience for us because we, as DBAs, also had the sysadmin privs so we could edit the job anyway.  But this got me thinking.  Was there a way to allow someone who wasn’t an owner of a job the ability to modify the job without granting them too much privilege like giving them sysadmin?

I came across a great article by Boris Hristov that explains a simple but effective method for doing so.

Any OOP programmer is familiar with the concept of overriding a method.  This is basically the same idea but overriding a system stored proc with a custom stored proc with one line of code that allows to achieve our goal.

Currently, if you try to modify a sql agent job a stored procedure called sp_update_job is executed.  This example is from MSDN.

USE msdb ;

GO

EXEC dbo.sp_update_job

    @job_name = N'NightlyBackups',

    @new_name = N'NightlyBackups -- Disabled',

    @description = N'Nightly backups disabled during server migration.',

    @enabled = 0 ;

GO

However, only members of sysadmin are able to edit jobs that they do not own.

If you create a copy of the dbo.sp_update_job as dbo.sp_update_job_override but add an ‘Grant Execute On’ at the end you can override it.

CREATE PROC [dbo].[sp_update_job_override]       @job_id                       UNIQUEIDENTIFIER = NULL,       @job_name                     sysname          = NULL,       @new_name                     sysname          = NULL,       @enabled                      TINYINT          = NULL,       @description                  NVARCHAR(512)    = NULL,       @start_step_id                INT              = NULL,       @category_name                sysname          = NULL,       @owner_login_name             sysname          = NULL,       @notify_level_eventlog        INT              = NULL,       @notify_level_email           INT              = NULL,       @notify_level_netsend         INT              = NULL,       @notify_level_page            INT              = NULL,       @notify_email_operator_name   sysname          = NULL,       @notify_netsend_operator_name sysname          = NULL,       @notify_page_operator_name    sysname          = NULL,       @delete_level                 INT              = NULL,       @automatic_post               BIT              = 1 WITH EXECUTE AS OWNER AS BEGIN    EXEC dbo.sp_update_job       @job_id       ,@job_name       ,@new_name       ,@enabled       ,@description       ,@start_step_id       ,@category_name       ,@owner_login_name       ,@notify_level_eventlog       ,@notify_level_email       ,@notify_level_netsend       ,@notify_level_page       ,@notify_email_operator_name       ,@notify_netsend_operator_name       ,@notify_page_operator_name       ,@delete_level       ,@automatic_post END GO GRANT EXECUTE ON [dbo].[sp_update_job_override] TO [SQLAgentOperatorRole] GO

So if you grant the user the SQLAGENTOPERATORROLE they will be able to make this call and it will complete successfully.

USE msdb ;

GO

EXEC dbo.sp_update_job_override

    @job_name = N'NightlyBackups',

    @new_name = N'NightlyBackups -- Disabled',

    @description = N'Nightly backups disabled during server migration.',

    @enabled = 0 ;

GO

Thanks to Boris and feel free to share this concept.

Thanks for reading,
MSSQL Canuck

Dimensional Modeling Concepts

Dimensional Modeling Concepts

I have been working for my current employer for nearly 10 years now.  I started out as a report developer but always intended on climbing the ladder as quickly as I could to get promoted into a more senior technical role. 

The opportunity to work as a Senior Data Warehouse architect came to me but first I had to pass a technical test in order to be considered.  Long story short, I passed the test and got the job.

Recently, a new report developer mentioned to me that they wanted to eventually do the same thing.  My biggest piece of advice was to start studying on data warehouse concepts now so when the opportunity comes up they are ready.

An important concept to comprehend to be a data warehouse architect is to understand the concepts of dimensional modelling.

Any student of any reputable computer science or CIS program learns all about the relational modelling but very few seem to graduate from school and know much about dimensional modelling.

It is the purpose of this blog entry to touch on the very broad main concepts of dimensional modelling.

In a nut shell, the main purpose of dimensional modelling is provide users with a database model that is optimal for reading and summarizing data in order to come to sound decisions.

This is achieved by creating a database model that resembles a star shape with a fact table in the middle and more or more dimension tables that surround the fact table.  This is called a ‘Star Schema’.

Once a business purpose is defined for the star schema, then the grain of the fact table is established.

For example, a grain for a business purpose could be the cost of each individual items that a surgeon uses to perform a given surgery.  From this grain, the total cost of each surgery could easily be calculated as could the cost of all the surgeries that a given surgeon performs or even the total cost of all the surgeries that all the surgeons perform for a given medical procedure.  Now you can start to see where a star schema can become very powerful.

The main benefits to modelling the data in this way is that reading the data from a star schema is much faster than a relational model and often when the data is structured in this way it becomes much easier for the consumer to understand the nature of the structure compared to complex relational models .

The star schema for a cost of surgery situation could resemble something like this.

This provides a very simple to understand schema that is optimal to read from because of the simplicity of the relationships and joins.    This isn’t as efficient as 3^rd normal form relational modeling in some aspects but those aspects aren’t considered as important in a OLAP environment where reading from a data structure is considered more vital than simply storing data or entering data as in a OLTP environment.

Thanks for reading,
MSSQL Canuck

My thoughts on Industry Tech Certifications

Recently, at a work team meeting, the subject of tech related certifications came up.  I have been interested in attaining certifications for several years.  I have always found them to be beneficial in many ways.   The main thing for me has been that it provides me with a purpose and an objective.  This makes self-studying much more natural for me.  I feel like it is way too easy in life to just let the years slip by while going to work and doing your job while other people stay focused and attain a long list of industry tech certifications.  When a time for a new job or a promotion comes up you can really be doing yourself a disservice if you haven’t made the effort to keep your skill set current.  What better way to do that than to work on tech certifications?

I am surprised when I hear people hate on certification calling them a money grab by the vendor or think they are useless.  I have absolutely gained jobs and promotions over other competitors because I had a relevant certification.

I am currently working towards a Microsoft SQL Server 2012 certification and the author of the book I’m reading summarized a few reasons why certifications are worthwhile.

Here are a few reasons that struck a chord with me.

1.        Proves you are committed to your profession.

2.       Provides you with confidence.

3.       Hopefully helps you earn more as your career progresses.

4.       Fills out the breadth of the knowledge you possess for a given tool or skill.

5.       Hopefully helps you stick out from the crowd with hiring managers.

So far I possess 4 industry tech certs but the other day I made a list of all the tests I had to pass to get them.

Oracle
     Oracle Database 11g:
            Oracle Database 11g: SQL Fundamentals 1Z0-051
            Oracle Database 11g: Administration 1Z0-052

Microsoft
      Microsoft SQL Server 2008
            Microsoft SQL Server 2008 - Implementation and Maintenance 70-432
            Microsoft SQL Server 2008 - PRO: Designing, optimizing and maintaining a database                         administrative solution using Microsoft SQL Server 2008 70-450

       Microsoft SQL Server 2005
            Microsoft SQL Server 2005 - Implementation and Maintenance 70-431

I am committed to to continuing to work toward further certifications as it is consistent with my personal growth and educational objectives.  I am  currently working on MCSA SQL Server 2012.

Thanks for reading,

SQL Canuck