If you are like me, then you will find yourself using the COUNT function many times throughout the day. I’ll go into my many uses of the count function later, but for now let’s look at the syntax. There are basically two ways to use COUNT. One is to perform a count of ALL records, and the other is to perform a count of distinct records. [cc lang=”sql”] SELECT COUNT(DISTINCT ProductID) FROM Production.TransactionHistory SELECT COUNT(*) FROM Production.TransactionHistory [/cc] This is likely the most common use that I use it for. Often I will want to find the cardinality of a specific field within a table. What I mean by that, is to determine how unique the column is when compared to all the rows in the table. That’s what the query above does. Let’s look at the results. From this we see that the ProductID field is duplicated quite a bit in the TransactionHistory table.
Continue reading ...
This is a handy method can be used to detect if a change has happened to any set of data. This can include an entire table or even just two rows. [cc lang=”sql”] SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM Sales.SalesOrderDetail [/cc] From our execution, we see that a number is returned. The number itself doesn’t have a particular meaning. All it is, is a hash. A hash is a number or sequence that is generated that represents the data you are hashing. The idea is that, if you change the underlying data you are hashing, then the hash will change. In this case, we are using the hash to say, “This number represents all the data contained ithin this table.” If the data changes, then the hash will change also. So let’s change a minute piece of data and see what happens. We’ll update the unitprice within one row by a penny. [cc lang=”sql”] SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM Sales.SalesOrderDetail UPDATE Sales.SalesOrderDetail SET UnitPrice = UnitPrice + .01 WHERE SalesOrderID = 43659 SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM Sales.SalesOrderDetail [/cc] Here’s what we see: So what use is this for? Well, there are plenty of uses. The most common use comes when trying to persist data. What this means, is that say we have lookup tables that we want to cache either on the web tier, or even on the client’s machines. What we can do, is send the lookup tables, along with a hash key that represents the state of the data. Then, we can have the […]
Continue reading ...
Alter index is a powerful new command that replaces both the DBCC INDEXDEFRAG and the DBCC DBREINDEX, and at the same time enhances them both. The following example encapsulates the most common settings used when rebuilding an index. This example is the same as using the DBCC DBREINDEX on an entire table. The settings below are simply detailed so you can uncomment or set them yourself. They are all set to the default: [cc lang=”sql”] ALTER INDEX ALL — All indexes on Table ON Sales.SalesOrderDetail REBUILD — REBUILD OR REORGANIZE WITH ( –FILLFACTOR = 80, — Leaf page pad % PAD_INDEX = OFF, — Pads Intermediate pages SORT_IN_TEMPDB = OFF, STATISTICS_NORECOMPUTE = OFF, — Auto update stats on ONLINE = OFF, — ON for concurrency ALLOW_ROW_LOCKS = ON, — Allow row locking ALLOW_PAGE_LOCKS = ON, — Allow page locking MAXDOP = 0, DATA_COMPRESSION = NONE — NONE | ROW | PAGE ) [/cc] Let’s look at what all this means. Setting Values Description [Indexes] ALL | (Specify an index name) All – Applies the settings to all the indexes on the table. Or alternatively specify an index name to isolate one index. [Alter Type] REBUILD | REORGANIZE REBUILD completely rebuilds the indexes, while REORGANIZE simply defgraments them. This is akin to DBCC DBREINDEX VS DBCC INDEXDEFRAG. Rebuild is more thorough. FILLFACTOR 0 – 100 Specifies the percentage of free space to be left for pages at the leaf level. Higher the number helps with quicker seeks, but you will need […]
Continue reading ...
As easy as this syntax is, I had to keep looking it up for about a year. I suppose that’s why you are here. Well, here it is: [cc lang=”sql”] ALTER SCHEMA newschema TRANSFER oldschema.Table [/cc] This will transfer the table defined under “oldschema” and transfer it to “newschema”.
Continue reading ...
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 ...
