The problem is that the query in question runs very slow when compared to the query run with one or two, rather than all three of its conditions. Now the query. Select Count(*) From SearchTable Where [Date] >= '8/1/2009' AND [Zip] In (Select ZipCode from dbo.ZipCodesForRadius('30348', 150)) AND FreeText([D...
I've been hearing a lot lately that I ought to take a look at the execution plan of my SQL to make a judgment on how well it will perform. However, I'm not really sure where to begin with this feature or what exactly it means. I'm looking for either a good explanation of what the execution plan does, what its limitations are, and how I...
I have a table containing a number of timestamps per day, they represents start and stop events. ID TimeStamp ---------------------- 1 2008-01-01 07:00:00 1 2008-01-01 08:15:00 1 2008-01-01 10:00:00 1 2008-01-01 11:00:00 1 2008-01-02 10:30:00 1 2008-01-02 12:00:00 I would like to calcuate the total running time per day, like th...
I have some code in an after insert trigger that may potentially fail. Such a failure isn't crucial and should not rollback the transaction. How can I trap the error inside the trigger and have the rest of the transaction execute normally? The example below shows what I mean. The trigger intentionally creates an error condition with th...
I would like to be able to match a single field based upon multiple patterns contained in another table, kind of like and IN clause for LIKE comparisons. The following query obviously doesn't work, but it expresses what I am trying to accomplish. SELECT * FROM TableA WHERE TableA.RegCode Like ( SELECT '%' + TableB.PartialRegCode ...
I am saddled with an ERP database which lacks any foreign keys and therefore lacks referential integrity. I am writing a script to check most of the major tables in our database for ophaned records. For example, in this case I'm working with our Sales tables. SLCDPM - Customer Master Table SOMAST - Sales Order Master SOITEM - Sales...
Given this table: How can I get the datediff in days between each status_date for each group of ID_Number? In other words I need to find the number of elapsed days for each status that the ID_Number has been given. Some things to know: All ID_Number will have a received_date which should be the earliest date for each ID_Number (bu...
I have a WPF app, where one of the fields has a numeric input box for length of a phone call, called ActivityDuration. Previously this has been saved as an Integer value that respresents minutes. However, the client now wishes to record meetings using the same table, but meetings can last for 4-5 hours so entering 240 minutes doesn't se...
I am trying to get the first part of a Guid Field with the TSQL substring function as follows SELECT SUBSTRING(Guid, 1, 8) AS Gu FROM MyTable but all i get is this error. Argument data type uniqueidentifier is invalid for argument 1 of substring function. So what is going on here? Should i treat the Guid as pure string first or...? ...
Within a ASP.NET/C# class I am trying to run a query where I compare dates: select * from table1 CreatedDate >='DATEADD(d,-500,GETDATE())'; Basically I am trying to select rows from the the last 500 days. The problem is I am getting the following error: Syntax error converting datetime from character string. An example of the Creat...
I have two tables: CREATE TABLE #HOURS (DAY INTEGER, HOUR INTEGER) CREATE TABLE #PERSONS (DAY INTEGER, HOUR INTEGER, Name NVARCHAR(50)) GO INSERT #HOURS VALUES (1, 5) INSERT #HOURS VALUES (1, 6) INSERT #HOURS VALUES (1, 8) INSERT #HOURS VALUES (1, 10) INSERT #HOURS VALUES (1, 14) INSERT #HOURS VALUES (1, 15) INSERT #HOURS VALUES (1...
Is the following SQL susceptible to SQL injection via the @SearchWord parameter? I want to use parameters with the FormsOf function, but the only guide to doing so I've found is in this Stack Overflow question: http://stackoverflow.com/questions/1362220/how-to-pass-parameter-to-formsof-function-in-sql-server However the solution seems ...
I would like to know the fastest way to compare two datetimes omiting the year. (In one of my querys it takes 55 secs becouse of this comparision) As of now it have tried (with no difference in times): where datepart(d,date1)=datepart(d,date2) and datepart(m,date1)=datepart(m,date2) where CAST(datepart(d,date1) as varchar)+ CA...
I've been investigating making performance improvements on a series of procedures, and recently a colleague mentioned that he had achieved significant performance improvements when utilising an INNER JOIN in place of EXISTS. As part of the investigation as to why this might be I thought I would ask the question here. So: Can an INNER...
I need to fetch top first row out of each duplicate set of records from table given below. I need to use this query in view please no temp table as I have already done it by adding identity column and min function and group by. I need solution without temp table or table variable This is just sample data. Original has 1000s of records...
Hello! When I run the following snippet try { using (SqlConnection conn = new SqlConnection("I'm shy")) { conn.Open(); using (SqlCommand cmd = conn.CreateCommand()) { cmd.CommandText = "PRINT 'A';PRINT 'B';PRINT 'C';RAISERROR('SQL_Error', 18, 1)"; cmd.ExecuteNonQuery(); } } } catch (SqlException ex) { MessageBox.Show...
Hi, I am maintaining some code that has a trigger on a table to increment a column. That column is then used by a 3rd party application A. Lets say that the table is called test with two columns num1 and num2. The trigger runs on each insert of num1 in test. Following is the trigger: USE [db1] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTI...
I'm storing data in a varbinary(max) column and, for client performance reasons, chunking writes through the ".WRITE()" function using SQL Server 2005. This works great but, due to the side effects, I want to avoid the varbinary column dynamically sizing during each append. What I'd like to do is optimize this by pre-allocating the varb...
I'm getting an error running the following Transact-SQL command: CREATE UNIQUE NONCLUSTERED INDEX IX_TopicShortName ON DimMeasureTopic(TopicShortName) The error is: Msg 1505, Level 16, State 1, Line 1 The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.DimMeasureTopic' an...
I am creating a stored procedure that produces reports based on data in a SQL database and stores these reports in a separate database. The data being reported is the total time over a date range that a motor was running. I want to be able to detect if the timeframe I am reporting overlaps previous reports based on date times. For ins...