Tag Info

New answers tagged

1

For SQL Server, using a similar example with a clustered index on the Id, the plans both use a "Clustered Index Seek" which is a broad explanation for what may happen internally. select ObjectId from oav.Object where ObjectId in (3105680,3105681,3105682,3105683,3105684,3105685,3105686,3105687,3105688,3105689) select ObjectId from oav.Object where ...


-2

In this scenario system table has always been useful. Please use this query. Select * From SYS.Comments Where Text = 'Stored Procedure Name' Although this table as multiple rows for a single object_id but putting a concat function on the table helps to get a single ROW for a single object_id.


0

Got the answer. To remove "FROM" away from INNER JOIN thx


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


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


1

The answer from @Mister Magoo is great. I used SQL Server Management Studio to do the same task. I followed the instructions here from MSDN. Pasting below as well for convenience: To create a filtered index In Object Explorer, click the plus sign to expand the database that contains the table on which you want to create a filtered index. Click the plus ...


0

Basically, use common table expressions to isolate and number rows in 2 groups with one helper column NULL in each, and then join the two groups on row numbers. WITH cte1 AS ( SELECT [id], [data], [from] , ROW_NUMBER() OVER(ORDER BY [id], [data], [from]) AS [row] FROM #temp3 WHERE [prevfrom] IS NULL), cte2 AS ( SELECT [to], ...


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


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


0

This can be done. But as far as i know, it will break the replication.You'll have to enable the option and re-initialize the subscriber with a new snapshot. Also, if you have non-clustered indexes with INCLUDED, you'll have to modify the definitions (.IDX files) manually to add INCLUDED to them. You can enable the option where you can replicate the non ...


0

If the string is being thrown directly out to a HTML stream then you could do Select 'Your submitting date is 2<sup>nd</sup> January 2016'; but that really isn't recommended practise - you should avoid making presentation layer decisions like that within your data layer. If you tightly couple your back-end to your presentation layer in ways ...


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


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


2

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


0

Jon, One possible option with the statement you posted would be to put an explicit begin transaction before the first delete statement and a commit statement right before the set of the @LargestKeyProcessed variable. This would commit the delete before you move to next loop iteration and should help with the log file as it's breaking the batch up into small ...


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.


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


0

Ok, after yor comment it is clear. I will do the following: Create a new DB called SERVICE, MAINTANANCE, etc. Inside it create a stored procedures for different steps - drop old DB, create new DB, populate new DB. You can make all that in one procedure also. Create one procedure which will execute others - step by step (if you not implemented all logic in ...


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


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.


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


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


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


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


1

You are saying: EXEC @SQLString; This is like saying: EXEC N'SELECT 1;'; Which is probably something that looks like it will work on first glance, but go ahead and try it, it fails. You want: DECLARE @SQL NVARCHAR(MAX); SET @SQL = N'SELECT ...;'; EXEC sys.sp_executesql @sql; I wrote a pretty detailed article on dynamic pivot here: ...


1

When talking about memory storage, the Hotel analogy also works well. But in this kind of hotel only one person is allowed in a room at a time; a man or a woman. This represents bit data. Ones and zeros. The max capacity of the hotel is 512 people. Before your hotel opens you decide to allocate only half of your rooms for guests. The allocated space is ...


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


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


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


1

My dilemma is about utility and advantages of using CROSS APPLY and CTE. Are there any or its just exotic? For small datasets the optimizer is probably not bothering with extensive analysis. However, if one were to look at competing plans for large data sets (say millions of Orders or Items from your example), then CROSS APPLY, especially if Items are ...


1

Something like this would work: declare @cmd varchar(500) select @cmd = 'osql -U -P -S -Q"select * from myTable" -o"c:\output.txt" -w500' exec master..xp_cmdshell @cmd You could also do it by creating a C# based stored procedure and execute it via the CLR.


0

"result to text" is for client application e.g. in SSMS when you press CTRL+T, you tell the client application (SSMS) to show / render the results of the query in TEXT format. Similarly, CTRL+D in SSMS will show the results of the query in GRID format.


3

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


1

CREATE TABLE TABLE_ITEMS ( serial_num int null, item_group int null ) GO INSERT INTO TABLE_ITEMS VALUES (0,1), (1,1), (2,1),(3,1) ,(4,1) ,(0,2) ,(1,2), (2,2), (3,2), (4,2), (5,2), (0,3) ,(1,3) ,(2,3) GO SELECT Item_group, ROW_NUMBER() OVER (PARTITION BY item_group order by item_group) AS AddNum INTO #NewSerials from TABLE_ITEMS WHERE ...


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.


0

You may like to first split the words and then apply your logic to separate the names. You can visit the following link for a custom split function: T SQL Table Valued Function to Split a Column on commas


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


1

The answer to both of your questions is really the same. You are using a script generation tool. It does what it's told to do. Typically the second command is only needed if you are doing a WITH NOCHECK on the first one. However it's included because the settings for the script generation component of SSMS told it to include it. Here is a post I did on ...


1

ANSI_NULL_DEFAULT { ON | OFF } : When you dont specify ANSI_NULL_DEFAULT in CREATE TABLE or ALTER TABLE statements, this database option will determine the default value, NULL or NOT NULL for a column or CLR user-defined type. When SET to ON, the DEFAULT value is NULL. When SET to OFF, the DEFAULT value is NOT NULL. Connection-level settings that are ...


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


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


0

Instead of creating a trigger I would suggest you to create a new column in the purchase table which will be a computed column. Before creating it, create a scalar function which will accept productname or productid and returns int. Inside it, write a query to find the total from sales for that particular row (by passing productname or id) and subtract it ...


0

I don't think that a trigger is the right place for your application business logic. A stored procedure to update the corresponding rows in the stock table would be much more sensible in my opinion. If you want to prevent users from modifying the sales and purchase tables directly without updating the stock table, don't grant permissions on those objects ...


0

Deleting data is easy from the syntax point of view, but that's not all there is. DELETE FROM myTable WHERE someDate < deletionDate might give you a few nasty suprises. Constraints might block you from doing a delete. A textbook example is a foreign key constraint between customer and order tables. Deleting a customer is not allowed as long as there are ...


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



Top 50 recent answers are included