This is typically a hot topic and I’m going to try and tackle it with my rudimentary math. Ultimately given time and the frequency of generation, there is no such thing as a completely unique random number. There will always be some chance that a random number can be regenerated even though the chances do go way down when given a larger set of bytes. Using Numbers Let’s first look at probably the most random way to generate a random number. Using a function introduced in SQL 2008 called CRYPT_GEN_RANDOM(). This function takes a parameter of byte length, and returns a random hex based number within that range of bytes. We can easily convert this varbinary data to an integer based value. This function is based upon a low level windows API and is cryptographically secure. [cc lang=”sql”] SELECT CAST(CRYPT_GEN_RANDOM(8) AS bigint) [/cc] Using the method above returning an 8 byte random number and casting it as a bigint, your chances of repeating a duplicate are (roughly) between 1 and 15 billion. To break that out in terms of time, if you generated a random number every second you would without a doubt hit a duplicate random number between 31 and 47 years. However there’s no guarantee that it might not happen way before or way after however the chances go down the farther you move from the mean. Even though this may sound like a long time, the randomness that a bigint provides may not be enough for your […]
Continue reading ...
This is an invaluable trick that can be sometimes be used instead of the BETWEEN operator. A common place this can be used is in an historical table that contains EffectiveFrom & EffectiveTo dates. In these kinds of tables, many historical versions of a record can be stored. Each record in this scenario will contain a Start & End Date which signifies the time span when a record is active. Because two records cannot be active at the same time, we can be sure that the Start & End dates will never overlap. While this describes one particular scenario for this example, there are many other situations this method can be used. Let’s look at the typical query we would use to find the record which is active for an employee at a specific point in time. [cc lang=”sql”] SELECT * FROM [HumanResources].[EmployeeDepartmentHistory] WHERE EmployeeID = 274 AND ‘2001-04-28’ BETWEEN StartDate AND EndDate [/cc] This query will return to us the single record that is active as of ‘2001-04-28’. However if you think about the constraints our table contains, we actually only need to query one column (the StartDate) in order to isolate this record. This is because the StartDate will not overlap any other StartDate & EndDate for this employee. Here’s an example: [cc lang=”sql”] SELECT TOP 1 * FROM [HumanResources].[EmployeeDepartmentHistory] WHERE EmployeeID = 274 AND StartDate >= ‘2001-04-28’ ORDER BY StartDate [/cc] Because only one record can be active for an employee at a single point in time, […]
Continue reading ...
If you’ve ever wondered how to make a procedure available in any database, it’s actually pretty simple. If you create a procedure in the master database with the prefix of “sp_”, it will be callable from any database. I personally think this feature is great for utilities, however I would not use this for dependent objects. In other words, I would not advise creating a procedure (or function that is referenced by other procedures) within the master database. The biggest reason is that when restoring databases to other servers, you typically do not restore the master database. So it can be easily left out or forgotten. If that happens you may have a mission critical troubleshooting situation on your hands. However sometimes there may not be a way around it. But you can look into utilizing synonyms as an alternative. Another interesting observation regarding placing procedures in the master database with the sp_ prefix is that the procedure is actually executed from the context of the master database. In other words, if you create a procedure to enumerate all the other procedures within your database and place it in master, once you execute the procedure from a database other than master, it will still enumerate the procedures in the master database. Here’s an example: After creating this procedure and calling it from AdventureWorks, it still enumerates all the procedures within the master database. To get around this, we can use the undocumented procedure which will mark the procedure as a […]
Continue reading ...
To explain what an indexed view is, let’s first look at what constitutes a view. A view may sound like a fancy elaborate thing, however all it is, is a saved SELECT statement, nothing else. It is not explicitly compiled, nor does it contain any data. When you select from a view, it goes to the underlying tables and retrieves the data at the time it is called. An indexed view on the other hand, is a normal view that takes a copy of the underlying data it points to, and stores it locally. This way, the joins and aggregations that are processed at run-time in a normal view, are already materialized, so when queried, it’s as fast as querying a normal table. Therefore, another name for indexed view is “materialized view”. This is what it’s called in Oracle. Creating To make a normal view an indexed view, you need to do two things. First you need to enable SCHEMABINDING for the view. Schemabinding essentially locks the underlying DDL schemas for the tables that the view references. This prevents any DDL changes from being made to the referenced tables. If you want to make a change to the tables, you need to drop the view first. Let’s create this new view using the AdventureWorks Database: [cc lang=”sql”] CREATE VIEW Sales.OrderTotals WITH SCHEMABINDING AS SELECT SalesOrderID = soh.SalesOrderID, OrderTotal = SUM(sod.UnitPrice), OrderCount = COUNT_BIG(*) FROM Sales.SalesOrderHeader soh JOIN Sales.SalesOrderDetail sod ON sod.SalesOrderID = soh.SalesOrderID GROUP BY soh.SalesOrderID [/cc] Okay, we’re half […]
Continue reading ...
Auditing tables can be a complicated procedure if you require a new table for every table being audited. Fortunately the new XML datatype can help to automate this procedure of auditing multiple tables into a single table. The following trigger template can be used to audit a table into a single table. First let’s create the table needed to hold the audited values: [cc lang=”sql”] CREATE TABLE [Log].[AuditTableHistory]( [HistoryID] [int] IDENTITY(1,1) NOT NULL, [TableSchema] [varchar](10) NULL, [TableName] [varchar](100) NULL, [AuditValue] [xml] NULL, [DateCreated] [datetime] NULL, CONSTRAINT [PK_AUDITTABLEHISTORY] PRIMARY KEY CLUSTERED ( [HistoryID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY] ) ON [PRIMARY] — Now let’s create the trigger CREATE TRIGGER [Audit].[Trg_Schema_Table] ON [Schema].[Table] AFTER UPDATE,DELETE AS BEGIN — SET NOCOUNT ON added to prevent extra result sets from — interfering with SELECT statements. SET NOCOUNT ON; DECLARE @AuditValue xml SET @AuditValue = ( SELECT TOP 1 * FROM DELETED FOR XML AUTO ) INSERT INTO Log.AuditTableHistory ( AuditValue, TableSchema, TableName, DateCreated ) SELECT AuditValue = @AuditValue, TableSchema = OBJECT_SCHEMA_NAME(parent_obj), TableName = OBJECT_NAME(parent_obj), DateCreated = GETDATE() FROM sysobjects so WHERE so.id = @@PROCID END GO [/cc] We can apply this trigger to any table we want just by changing the “ON” statement. It will work for any table. The only difficulty comes in when we change the schema for a particular table. When this happens, we need to know the date/time the table schema changed so […]
Continue reading ...
