Alter Procedure VS Drop Create

I had to go back through this article and break it out in terms of Pro’s and Con’s. Pro’s of using Alter Procedure Permissions stay intact ObjectId’s of procedures stay the same Altering a procedure is transactional, dropping is not The create date of the procedure remains as original, and modified date gets updated Pro’s of using Drop / Create for a Procedure Objects can be rebuilt in a new location using source control (does not need to already exist) You don’t need to worry if the procedure already exists to determine how to write it I’ve mostly used drop create in places where I’ve worked, but I’ve just been convinced to use Alter (where applicable) instead. The script below will show the original procedure’s create date and modified date: [cc lang=”sql”] SELECT CREATED, LAST_ALTERED FROM INFORMATION_SCHEMA.ROUTINES [/cc] As for the permissions issue, two things come up. The first is that I don’t set my permissions at the object level. I set them actually at the schema level (for 2005+). This comes in handy because whatever schema you create an object under, automatically inherits those permissions. However, if you have a DBA that sets more granular permissions on the objects, then drop/create will get you in trouble, because those permissions will forever be lost. Another issue with drop/create, is that in a highly transactional system, if you execute the drop statement and a call comes in prior to the create, you will receive errors. Alter will not do this however […] Continue reading ...

Using @@Identity

The @@Identity variable is an intrinsic variable which contains the last value inserted into a primary key via the auto increment method. What this means is that if you create a table that has an identity column as a primary key, once that identity column is inserted anywhere within that connection then the @@identity variable will hold that value. You may be wondering what the catch is with the italic bold statement above stating “anywhere within that connection”. Well, it is a catch because this is what can make the @@identity variable so dangerous. Here’s the scenario. Say you perform an insert into the customers table. [cc lang=”sql”] INSERT INTO Customers ( FirstName, LastName ) SELECT FName, LName FROM dbo.NewCustomers WHERE CutomerID = 1 SELECT @@identity [/cc] According to this example, the @@identity will surely contain the value of the primary key which is the auto increment for the Customers table right?? Hmm.. maybe.. and most likely… but maybe not. Here’s why. If the Customers table fires a trigger to insert into say, an audit table, and that audit table has an auto increment primary key, then you might find that auto increment returned into the @@identity variable. This is because the trigger is executed in the context of the same connection as the original insert. And remember, @@identity contains the most recent identity column from anywhere within the connection. So what do you do? Use the Scope_Identity() function. It will only return the identity column inserted from the last […]

Using DBCC DBReindex

Increasingly, you can count on most DBCC commands as being deprecated in future versions of SQL. This is one of the ones that will be removed later. It still servers a purpose. However it can be replaced using the ALTER INDEX command. The great thing about the DBCC DBReindex command is that it will completely rebuild a totally fragmented or corrupted index. This is likely to happen on a busy production system. The most basic syntax is: [cc lang=”sql”] DBCC DBREINDEX (‘Person.Address’) [/cc] This will rebuild all indexes on the Person.Address table with the default fill factor. Optionally, you can also re-index only one index on the table and specify a specific fill factor. To do that, simply add the following two parameters: [cc lang=”sql”] DBCC DBREINDEX (‘Person.Address’, ‘PK_Address_AddressID’, 90) [/cc] This will only re-index one index, in this case the primary key, with a fill factor of 90%. Meaning 90% of the data pages will be full, leaving 10% for splits. Something to note with DBCC DBReindex is that it will take your operation offline. Meaning it will block any operations that occur against the table during the duration of the rebuild. There is a way around this. It is to use the ALTER INDEX command. Here is the alternative. [cc lang=”sql”] ALTER INDEX ALL ON Person.Address REBUILD WITH (ONLINE = ON, FILLFACTOR = 90) [/cc] This command will rebuild all the indexes on the Person.Address table with a fill factor of 90%. This will all happen online, meaning […] Continue reading ...

Overcoming RSI, Carpal Tunnel, or Any Other chronic illness

If I were to read this post about 4 weeks ago, I would have completely dismissed it. The truth is, I’ve become even more keenly aware of how powerful the mind is in controlling the body. I’m writing this specifically for people that suffer from RSI or Carpal tunnel, however what I’m about to write helps with any chronic illness, as it has helped me. I’m going to jump to it. Many if not most chronic illnesses are caused by our own minds. Don’t get me wrong, I’m not saying the pain is not real. Absolutely it is real. However the fear to use an “ailing” part of your body is the very issue that is causing the pain. It is psychosomatic and/or psychogenic. How do I know this? Two ways. I would have mild RSI that would occur when I would work somewhere over 45 hours a week. When this would occur, I would start worrying, “Oh crap, here it comes again”. Once I did that, the merry-go-round would start. Once it began, I would soak my hands in hot water, or give my hands deep tissue massages. It would temporarily stop the pain.  So, being tired of it, I did a search for solutions.  Luckily, I stumbled onto another person who suffered from RSI who no longer does.  That’s where I first learned about John Sarno’s book. John Sarno is a doctor that specializes in a chronic illness he calls TMS or  Tension Myositis Syndrome.  (Yes I know […]