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 […]
Continue reading ...
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 ...
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 […]
Continue reading ...
Ever since SQL 2005, I have found schemas to be indispensable because of the security they offer. In previous versions of SQL Server, in order to restrict access, you had to do it by creating a new database. In many cases, this was the main factor in creating a new database. Now we have Schemas. These schemas, allow for an added layer of decoupling between a user and the objects they can access or execute. When I create a database now, I usually create a schema that only the web can access. I name this Schema: App. So basically, anything in the app schema is available to the web. From there, any procedures withing the app schema can access any other schema that is created with the same owner (i.e. dbo). However it still stops the web from being able to access objects of other schemas. This also allows you to have security on an entire schema rather than having it at the object level. To implement this, you create a new database role, and in the securables section select “all objects of the types” » and select schemas, then select the permissons.
Continue reading ...
Synonyms were first introduced in SQL 2005 and prove to be a great feature so far. What they basically do is decouple the database you are working in from other databases or objects you reference. This helps because it allows you to move objects (tables, procedures) to other locations without having to change the existing objects that reference them. I used this recently in a project where I implemented a new system within an existing system. From a purist standpoint, the system should be on it’s own, but since there was no time to create subscriptions, we implemented it where most of the data was. While I did not like this method, I still to reference the data that should be external via synonyms. To create a synonym, you can either use the dialog, or code. The dialog is pretty intuitive. Synonyms are created within a database, so expand the database and right click on Synonyms. Once you do that, a dialog pops up: Synonym Name Akin to the table or procedure name Synonym Schema Akin to the schema Server Name Server to reference, blank for current Database name Database to reference Schema external schema to reference Object Type [Drop down values] Object Name [Drop down values] And the code to create a synonym is: [cc lang=”sql”] CREATE SYNONYM [Subs].[Employees] FOR [ServerX].[AdventureWorks].[dbo].[Employee] [/cc] Reference it in your code the same way you do a table: [cc lang=”sql”] SELECT * FROM Subs.Employees [/cc] *Intellisense doesn’t work with it as of this […]
Continue reading ...
