For those that don’t know. A rules engine is a way to harness powerful decision-making based on source data. These decisions are defined in rules. Rules will generally consist of a query that outputs a list of successes or failures, depending on what needs to be reported on. In my experience, I have only found two major methods for building a rules-based engine in SQL Server. They both have their pros and cons. The trick is finding the middle ground which is defined as a third method between the two different extremes that will fit your requirement. With that said we will discuss the two extremes — and the middle ground. The Three Kinds of SQL Rules Engines Extreme 1 – The Dynamic SQL Rules Engine The first method involves dynamic SQL. Using dynamic SQL, the sky is the limit in regards to possibilities. The downfall however is maintainability. Dynamic SQL is very hard to maintain and even harder to troubleshoot and debug. My experience dealing with dynamic SQL is that it often starts out small, but once you’ve gone the dynamic route, it gets bigger and bigger and harder to maintain. Therefore I try to avoid this method altogether. If you’re into it, you’ll definitely find more job security, I’m not into that game personally. Extreme 2 – The Hard-coded Rules Engine The next method is to write each rule as a hardcoded procedure. When the rule executes, it returns back the list of results that fail (or pass) […]
Continue reading ...
Select Distinct a way to tell SQL Server to not return duplicate rows. Adding the distinct keyword makes it an expensive query because it essentially performs a group by for all of the returned columns. That being the case, it should only optimally be used on queries that return a small number of rows, or small number of columns. When using Select Distinct, you should keep in mind that this is generally a workaround to either improper joining of tables, or an improper where clause that is not limiting the result set to only returning one instance of a record. In order to utilize distinct, all you need to do, is place it before the select list. [cc lang=”sql”] SELECT DISTINCT * FROM yourtable [/cc] This will surely only return a unique instance of every record in the result.
Continue reading ...
A SPID in SQL Server is a Server Process ID. These process ID’s are essentially sessions in SQL Server. Everytime an application connects to SQL Server, a new connection (or SPID) is created. This connection has a defined scope and memory space and cannot interact with other SPIDs. The term SPID is synonymous with Connection, or Session. Viewing SPIDs In order to view all the connections in SQL Server execute the following query. [cc lang=”sql”] SELECT * FROM sys.dm_exec_sessions [/cc] From here we see a session_id shown in the left hand column. This is also known as the SPID. To find the SPID for your current execution window run this. [cc lang=”sql”] SELECT @@SPID [/cc] Connection Settings Each SPID can have it’s own connection settings. Connection settings can be defined by the connection string or the default values for the SQL Server instance, so if two SPIDs come in through the same connection string, then they will also have the same connection settings. This is important because execution plans that are generated for queries are dependent on the connection settings. If two different SPIDs come in with different connection settings, then they will not use the same execution plan defined for a SQL statement. In order to view the connections settings execute the SQL Statement shown above. Transaction Isolation SPIDs can have their own transaction isolation levels defined globally. When set within a connection, all proceeding executions maintain the same transaction isolation. For more on isolation levels, view this Temp […]
Continue reading ...
In Oracle there is a function (trunc) used to remove the time portion of a date. In order to do this with SQL Server, you need to use the convert function. Convert takes 3 parameters, the datatype to convert to, the value to convert, and an optional parameter for the formatting style. It is this third parameter we will use in order to customize the format of the date. [cc lang=”sql”] — Month first SELECT CONVERT(varchar(12),GETDATE(), 101) — 06/29/2009 SELECT CONVERT(varchar(12),GETDATE(), 110) — 06-29-2009 SELECT CONVERT(varchar(12),GETDATE(), 100) — Jun 29 2009 SELECT CONVERT(varchar(12),GETDATE(), 107) — Jun 29, 2009 — Year first SELECT CONVERT(varchar(12),GETDATE(), 102) — 2009.06.29 SELECT CONVERT(varchar(12),GETDATE(), 111) — 2009/06/29 SELECT CONVERT(varchar(12),GETDATE(), 112) — 20090629 — Day first SELECT CONVERT(varchar(12),GETDATE(), 103) — 29/06/2009 SELECT CONVERT(varchar(12),GETDATE(), 105) — 29-06-2009 SELECT CONVERT(varchar(12),GETDATE(), 104) — 29.06.2009 SELECT CONVERT(varchar(12),GETDATE(), 106) — 29 Jun 2009 — Time only SELECT CONVERT(varchar(12),GETDATE(), 108) — 07:26:16 SELECT CONVERT(varchar(12),GETDATE(), 114) — 07:27:11:203 — Date Only No Time (SQL 2008) [thank you John] SELECT Cast(GetDate() AS date); — 08/12/2011 [/cc]
Continue reading ...
If you’re here then you’ve probably run into the situation where you’ve automatically created a temp table in your script, and every time you execute the script you have to drop the temp table manually. Yes, this can be a pain. I’ve done this myself many times. So here’s the easy solution. We need to check if the temp table exists within the TempDB database and if it does, we need to drop it. [cc lang=”sql”] IF OBJECT_ID(N’tempdb..#Temp’) IS NOT NULL BEGIN DROP TABLE #Temp END [/cc] To replicate this, let’s run the following command in the same window multiple times: [cc lang=”sql”] — (Replace the product table below with your table) SELECT * INTO #Temp FROM SalesLT.Product [/cc] We get the error message: Now we simple add our snippet of code, and we are able to execute without having to manually drop anymore. [cc lang=”sql”] IF OBJECT_ID(‘tempdb..#Temp’) IS NOT NULL BEGIN DROP TABLE #Temp END — (Replace the product table below with your table) SELECT * INTO #Temp FROM SalesLT.Product [/cc]
Continue reading ...
