Tag Info

Hot answers tagged

9

But the execution plan for both is same as shown below: The plans are different. One is an inner join, the other is an outer join. The results may be the same in your simple test, but the semantics are different. In more complex queries, the difference may cause more obviously different execution plans, and come with a performance impact. There are ...


7

Quite convoluted but should work and return the 2 rows with values below the @repid, the row with @repid and the 2 rows with values above the @repid (assuming that id is a unique key). If there are less than 2 values below or above, the query will get more from the other side. In total a maximum of 5 rows will be returned. Note: the ORDER BY is needed. ...


7

Using OPTION (LOOP JOIN) isn`t suitable since it costs almost 15% more than MERGE JOIN The cost percentages displayed in showplan output are always optimizer model estimates, even in a post-execution (actual) plan. These costs likely do not reflect actual runtime performance on your particular hardware. The only way to be sure is to test the ...


6

I recently did this in my organization wherein we wanted to handle a table with billion + rows. All the credit for the idea goes to Aaron Bertrand and is from his blog post Trick Shots : Schema Switch-A-Roo Test below process on a small table and get your self comfortable before doing it in PROD. create 2 schemas fake and shadow with authorization dbo. ...


6

The following is basically your algorithm but implemented as a single statement: WITH ranked_and_counted AS ( SELECT ID, r = ROW_NUMBER() OVER (ORDER BY ID ASC), c = COUNT(*) OVER () FROM dbo.tblLookups ), ranked_and_counted_and_r0 AS ( SELECT *, r0 = MAX(CASE ID WHEN @ID THEN r END) OVER () FROM ranked_and_counted ) ...


4

How does the query processor execute COMPUTE BY? It is a hard-coded sequence of operations at the top of the execution tree. There is a simple stream accumulation (hence the ordering requirement), some logic to detect the start of a new group, a little computation and data value copying, and direct calls to construct the alternate TDS result sets per ...


4

There's a simple, neat way to work around the percentage sign problem. Use the message text as parameter: RAISERROR ('%s', 16, 1, 'Message with %')


4

Try: WHERE FileName >= N'ATT000' AND filename < N'ATT001' And make sure you have included columns to avoid lookups.


4

The problem mentioned on MSDN has nothing to do with sys.sql_modules or OBJECT_DEFINITION(); they've misinterpreted the problem. What actually happened is they were thwarted by an output limitation in Management Studio, which by default will only show 255 characters and at most 8192 in any output tuple in Results to Text. So first, make sure you change this ...


3

I think you're trying to solve for the wrong problem. As I suggested above, I think you're getting different results simply because you currently have different plans (one for the literal and one for the variable). The plan you're currently getting that causes an error is attempting the cast before the rows are filtered out, but both methods could cause an ...


3

No, you can't fetch scalar values from a cursor row into a table variable. You would have to declare the variables, fetch into them, and then insert: FETCH idCursor INTO @id, @data; INSERT @currentId SELECT @id, @data; However, perhaps it's the case that you don't need a cursor at all. Why are you processing one row at a time? Why not populate the @table ...


3

Here is the way I see it. Pros for #1 Because you are using a separate table your production table stays in use until you are done. No locks on it (beyond those needed to read the data). This also goes with what @AaronBertrand said: you can do it piecemeal, test etc You can change column order at need Pros for #2 It is an all or nothing operation. ...


3

In SQL Server, a stored procedure's argument can be either a string literal or a variable. It certainly cannot be a string expression, like in your attempt. To resolve your issue, you could declare a variable, assign your expression to it and then pass the variable to the sp_send_dbmail stored procedure: DECLARE @myfilename nvarchar(255); SET @myfilename = ...


3

Declare a variable first - but use format 112 for this. Like this: DECLARE @query_attachment_filename VARCHAR(100) = 'pca-test-' + CONVERT(VARCHAR(10), GETDATE(), 112) + '.csv'; EXEC msdb.dbo.sp_send_dbmail @profile_name = 'support', ... @attach_query_result_as_file = 1, @query_attachment_filename = @query_attachment_filename, @query_result_header =0, ...


3

No, how the results are displayed (if at all) is the job of the client application.


3

Think of your database like a bucket. The ALLOCATED space is how big the bucket is, i.e. how much data it can hold. The USED space is how much water/data is actually inside the bucket. When the water gets to the top of the bucket, you need to make the bucket bigger by allocating more space.


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


3

You can use a filtered index to enforce uniqueness on id and is_primary when is_primary is 1. An example here: create table Computer( id char(20) not null , NIC_name varchar(100) not null , is_primary bit not null , constraint pk_computer primary key ( id asc, NIC_name asc ) ); create unique index ix_one_primary on Computer(id, ...


2

atThis is pretty much when bluefeet gave you as an example in the comment. DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX) select @cols = STUFF((SELECT distinct ',' + QUOTENAME([Status]) from [EVAULTTEST].[dbo].EvidenceItems FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'') set ...


2

The return value of -1 makes sense when you consider what ExecuteNonQuery() does. It returns the number of rows affected or -1 in case of set nocount on or non-updating queries. As per documentation, For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. ... For all other types of statements, the ...


2

You are doing the entire delete in one transaction that will possibly bloat your transaction log and it will crawl like a sloth. There is a much better way of doing a delete - break it into chunks or you can do deletes using Careful Batching. Also, its always a good habit to refer objects with their schema.


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

I would use the Object Explorer in SQL Server Management Studio and go from the top, one procedure at a time. Right click on the procedure. Select Modify. Edit the procedure code. List item Press F5 to execute the modification of the procedure. Close the tab. Go to 1. Makes it easy to divide the work between you and your friend that does the same going ...


2

In simple cases, it will be the same. However, I have seen very complex queries with several joins have significantly different plans. A recent one I was working on started with a table that has close to 6 million rows joined to about 20 different tables. Only the first join to this table was an inner join, all others were left outer joins. The filter in the ...


1

SELECT MAX(Description) as MAX_Description,a.Structure ,a.Description FROM (select ,CASE WHEN Type = 'Test' THEN Structure ELSE '' as Structure CASE WHEN Type = 'Test' THEN Description ELSE '' END as Description from <Table_Name> ) a GROUP BY a.Structure ,a.Description


1

My colleague ended up finding an article about what he was referring to: http://www.nigelrivett.net/SQLAdmin/AlterTableProblems.html. After reading this and realizing our year end reporting was coming up, we decided to not make the alterations to the column types and will revisit this in the next couple months. I think after reading the article, I may just ...


1

Couple of ways : -- test data CREATE TABLE dbo.Details ([STATE] varchar(10), [Total] int, [BadEggs] int, [GoodEggs] int, [Unknown] int) ; INSERT INTO dbo.Details VALUES ('Kansas', 5, 2, 2, 1), ('Texas', 10, 5, 4, 1), ('Texas', 2, 0, 2, 0), ('Georgia', 20, 5, 5, 10), ('Maine', 25, 15, 5, 5), ('Florida', 5, 1, 2, 2), ...


1

SELECT ISNULL([State],'Total'), SUM(Total), SUM(GoodEggs), SUM(BadEggs) FROM Detail GROUP BY GROUPING SETS (([State]), ()); Although you should probably just do GROUP BY [State] and provide the total row and right column in your application.


1

;WITH cteA AS(SELECT Name,GroupID, DENSE_RANK () OVER(ORDER BY Name) AS New_GroupID FROM #T) UPDATE cteA SET GroupID = New_GroupID Now , depending of your system , this could take a few seconds or more. You can split the update , to do in chunks. Something like A-G , then from G to M ... and you can add , in the DENSE_RANK something ...


1

Be careful with the drop and recreate option: this can leave sys.depends in an odd state and cause problems for cached plans where the ordering or type of columns is changing. You will also need to take steps to maintain any object level permissions, as these will be lost in the DROP and not automatically recreated with the subsequent CREATE. ALTER TABLE ...



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