Tag Info

Hot answers tagged

24

This pattern column = @argument OR (@argument IS NULL AND column IS NULL) can be replaced with EXISTS (SELECT column INTERSECT SELECT @argument) This will let you match a NULL with a NULL and will allow the engine to use an index on column efficiently. For an excellent in-depth analysis of this technique, I refer you to Paul White's blog article: ...


6

Try: HAVING 100*((sum(COMMISSION/100)) / (SUM(AMOUNT/100))) > 4 However you will still probably find that integer math is preventing you from getting the result you want. So this might be better: HAVING 100*((1.0*sum(COMMISSION/100)) / (1.0*SUM(AMOUNT/100))) > 4


3

You just need to specify the first SiteInfo node using [1] update [dbo].[Sites] SET [SiteInfo].modify('insert <Anothernode>ABC123</Anothernode> into (/SiteInfo[1])') where Siteid = 1


3

You are looking at the wrong output. The output you are looking at is the results pane. If you look in the messages pane you will see that you are in fact printing the first row twice. In both cases it's because of your second fetch FETCH NEXT FROM idCursor You have to tell it the variables you want to put the data into. Change it to look like the ...


2

What I've been using is Adam Machanic's Who_IS_ACTIVE to track the stats for slow running queries. The prod uses the DMV's and will track the stats as mentioned. One of the great things about sp_whoisactive is that it’s very quick to generate a table to collect the data you need, then run the procedure in a loop with a wait statement to regularly collect ...


2

It looks like you have at least two different regional date formats there, possibly USA and Europe? You could just split the updates by languages... something like this, which uses the language setting to control isdate and convert. set language english; update test_dates set ToDateTime = convert(datetime,ContainedData) where AID='123' and PID='2' and ...


2

How about this: UPDATE [dbo].[MyTable] SET [ToDateTime] = [ContainedData] WHERE aid = 123 and pid = 2 and isdate([ContainedData] ) = 1


2

Decimal and whole numbers are encoded very differently in varbinary. Decimals need more space. Try: SELECT CONVERT(VARBINARY(32), 1), CONVERT(VARBINARY(32), 1.0); As for your ultimate goal, storing whole numbers as varbinary to save space, I think you've answered that question yourself - not worth it.


2

There are two possible explanations: Your code looks like this: DECLARE c CURSOR FOR ... OPEN c; FETCH c INTO @var; -- fetches 1st row WHILE @@FETCH_STATUS = 0 BEGIN FETCH c INTO @var; -- fetches 2nd row -- do something END If that's not the case, then since you don't have an ORDER BY, SQL Server is free to return the results in whatever order it ...


2

You forget group by account_no. So finaly: select account_no, 100*((sum(COMMISSION/100)) / (SUM(AMOUNT/100))) from accounts etc group by account_no HAVING 100*((1.0*sum(COMMISSION/100)) / (1.0*SUM(AMOUNT/100))) > 4


1

A quick-and-dirty way to get this information is to run something like this query while the query you wish to observe is still running. select st.text, r.wait_type, r.wait_time, r.wait_resource, qs.creation_time, qs.* from sys.dm_exec_query_stats qs join sys.dm_exec_requests r on r.sql_handle = qs.sql_handle cross apply ...


1

I love Aaron Bertrand's answer. Although I don't understand it completely, it looks really elegant. In the past I've ran into problems with permissions when using sys.objects. Combined with the need for me to troubleshoot the code, I've come up with a variation on Aaron's code, and added it below. This is my procedure: CREATE PROCEDURE ...


1

This totally depends how healthy the server is SQL1,2008R2. You will need to analyse first the usage of Databases, SSIS or SSRS and other parameters hosted on the server. Also, what resources you have in terms of RAM, CPU, Disk(Raid levels) etc. available on the server along with how they are being getting used. It would be a good start if you separate ...


1

You will almost always be susceptible to vendor updates when it comes to schema changes and their app, especially when it comes to ERP applications such as SAP Business One or it's bigger brother. The issue is how will their update scripts react to your table being added/modified? Usually it's not a big deal for me but sometimes it could cause massive ...



Only top voted, non community-wiki answers of a minimum length are eligible