Tag Info

Hot answers tagged

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 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

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


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

Sorry to disappoint, but your boss is right on target. Some of us BI folks know about DB optimization, at least a little. =) As with any major architectural change you need to test and adapt appropriately to your unique environment, workload, servers etc. Due to heavy blocking, we can't purge data while the database is online. Table partitioning is ...


2

Double check that the paths you are trying to restore to exist on the D drive. SQL Server will not create the directory path if it doesn't exist. If you haven't already, run the restore command with the verify only option MSDN. If that is successful here are a few additional things you can try to isolate why it is failing If you have the space on the ...


1

For the portion of the question asking about compression, Dave is correct on the trade off. I would investigate adding more capacity to your system before you head down the road of compression. For the partition portion the answer is going to be it depends. Partitioning takes a lot of planning and analysis of your current queries before you can measure ...


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 ...



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