Tag Info

New answers tagged

1

Fisrt optimizer takes one of best plans - not the best. This is because when the optimizer works the time for calculating the best plan can be bigger than the time it saves during the execution. And yes, optimizer works based on statistics. So if your statistics is old enough optimizer will create plan suiatble for that statistics - i.e. for the moment of ...


4

The optimizer strives to get a plan that is "good enough", and this is not always the optimal one. A very common reason is a too complex query. Breaking it down to a few queries helps the optimizer choose a better plan. In some cases, too many indexes on a table can also cause this, as the optimizer might use an index that is not the best one because as ...


0

As Aaron has explained, @@ERROR needs to be checked after each ALTER PROCEDURE. There is no avoiding that, but as for using a transaction, you could replace it with a different approach, if you are open to suggestions on that. You could manipulate the SET NOEXEC setting to achieve the same result you are using a transaction for: either both procedures ...


4

@@ERROR is reset after every statement. The error from your attempt to alter the first procedure is no longer detectable via @@ERROR after you successfully altered the second procedure. Here is an even simpler repro: SELECT 1/0; GO SELECT @@ERROR; -- 8134 However if I put a successful statement in between: SELECT 1/0; GO SELECT 1/1; GO SELECT @@ERROR; -- ...


1

There are a couple of problems with this approach: The immediate problem is that you cannot continue an empty line. So the two lines (following each go ^ line) that are only the single character ^ are causing the line to end and not continue. It has nothing to do with the go. So you can just add a space before the carrot and it will build the full string. ...


4

Here is the first approach I came up with: DECLARE @ChosenLanguage INT = 48; SELECT sc.Id, Result = MAX(COALESCE( CASE WHEN lst.LanguageId = @ChosenLanguage THEN st.Text END, CASE WHEN lst.LanguageId = sk.DefaultLanguageId THEN st.Text END) ) FROM dbo.SupportCategories AS sc INNER JOIN dbo.StringKeys AS sk ON sc.StringKeyId = sk.Id LEFT OUTER ...


1

Get rid of GO altogether. It's recognized by sqlcmd only in the interactive prompt or when reading from a file using the -i switch. If some statements need to be in a separate batch, use EXEC to wrap them. Example: EXEC('CREATE VIEW SomeView')


2

INIT will simply overwrite the existing file if it already exists, rather than append. However, you can prevent this from happening if you use the RETAINDAYS option, and set it to a really long time (you will have to maintain and manage these files as they get older, of course). If all of your backup files are initially set to last 10 years, and you always ...


2

As kin mentioned above. The easist way seems to be bcp out and in. Script out your table to a file ( The create table script ) Run the following command Export Data at command line using bcp "Database.Schema.Table" out "D:\filename.dat" -N -S "SQLServer\Instance" -T Once you copy that file / move to other system create the table you need then do the ...


0

I finally managed to solve my problem. I started by adding a unique ID to every "step 1" I could, then used an OUTER APPLY to fill in the rest of the gap of everything with a lower START_TS than it. -- Add a unique ID to every Step 1 DECLARE @myVar INT = 0 UPDATE #start SET @myvar = JobID = @myVar + 1 WHERE Step_Number=1 -- Fill in all the blanks to group ...


1

Note that regardless of whether you trigger a table lock, a 1000+ record INSERT transaction will lock many pages in your indexes, and has a high risk of causing deadlocking or other problems. The good news is that your post does not seem to provide a business case for taking on a such a large transactional operation. The data source table is not going ...


1

The only correct answer here is "It Depends". There are so many factors that come into play that you will just have do many runs with different parameters try to find what are the optimal settings for your system, and you can also play with locking hints if your table has indexes that are optimal for the search condition. There are some rules of thumb.. if ...


3

The problem is that you are missing a way to tie the outer table to the data inside of the STUFF. You can do this by adding a WHERE clause to the subquery: SELECT x.SurveyQuestionID, PossibleAnswers = STUFF(( SELECT ', ' + Answer FROM SurveyQuestionPossibleAnswer sqpa INNER JOIN SurveyQuestion sq ...


0

An alternative way that may be faster is to use the STIntersects and BufferWithTolerance methods to check if one point is within a certain distance of another. SELECT DISTINCT DS1.[ID] FROM DataSource DS1 INNER JOIN DataSource DS2 ON DS1.[ID] = DS2.[ID] WHERE DS2.STIntersects(DS1.BufferWithTolerance(200, 0.9, 0)) = 1 Do note though that ...


1

I got this same error a few weeks ago, despite the fact the connection was fine; it was actually due to an authentication issue. If you don't have domain connectivity between the two machines, you can use certificates to create credentials that can be used for mirroring endpoints. To do this, you create a certificate on the database on each end, create an ...


2

Start by making a column like CASE WHEN MinutesSinceLastRequest < 20 THEN 0 ELSE 1 END AS IsOver20 Then wrap that in a rolling sum - this will change every time there's a 1 for IsOver20: SUM(IsOver20) OVER (ORDER BY [TimeStamp] ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS GroupNum So now you can group by this easily. Include PARTITION BY UserId ...


6

When a view is created it is bound to the metadata it needs in order to execute. Note: https://msdn.microsoft.com/en-us/library/ms187821.aspx That says that sp_refreshview: "Updates the metadata for the specified non-schema-bound view. Persistent metadata for a view can become outdated because of changes to the underlying objects upon which the view ...


8

A couple of feasible options, in order of my preference: Option 1 Create a new, empty database locally - maybe make the log file large enough, at least temporarily, to accommodate the entire set of data you are moving without growth Use SELECT INTO, the Import/Export "Wizard", or Red Gate SQL Data Compare to copy this table to the new database (note that ...


0

Use this T/SQL : DELETE FROM dbo.YourTable WHERE ( ID NOT IN ( SELECT MAX(ID) FROM dbo.YourTable GROUP BY road -- <List of columns that define -- duplicate's condition -- for exemple road> ) )


-1

Nice work thanks to Nigel Rivett for sharing his work... perfect http://www.nigelrivett.net/AuditTrailTrigger.html /* This trigger audit trails all changes made to a table. It will place in the table Audit all inserted, deleted, changed columns in the table on which it is placed. It will put out an error message if there is no primary key on the table You ...


3

You need to have a distinction between varchar and nvarchar. There is a big difference. If I try to create varbinary from your "should work out to" value: SELECT CONVERT(VARBINARY(64), '201407240005688309') I get this value, which looks kind of like the value you're converting, but not quite: 0x323031343037323430303035363838333039 So now if I change ...


0

I would use dynamic sql: DECLARE @fldname varchar(max) = 'foo' Declare @sql NVARCHAR(MAX) = ' UPDATE TABLE SET ' + @FldName + ' = bar WHERE ' + @FldName + ' <> bar ' EXEC(@sql) In this example, this sets @sql to: UPDATE TABLE SET foo = bar WHERE foo <> bar And executes the script. Clearly, you would want to modify for your exact ...


0

So I ended up solving this with a C# application. I thought I would share my code in case anyone else needed something similar. It's using Entity Framework to do the data access. using (var ticketsContext = new CMS.Data.Tickets.TicketsContext("name=CMS")) { using (var analysisContext = new TicketTitleAnalysis.Analysis()) ...


1

You have different delimiters but I'm sure you have a small set of them. In your sample data the two delimiters are "space" and "comma-space". You can amend one of the linked solutions to work with multiple matching conditions. This will deliver unpivoted values. The data will then look something like 1 All 1 of 1 R489804 1 R489805 ... 2 SP 2 fr 2 ...


2

Here is a sample of how I would do this: select a.id , substring(a.value,T.N,7) from ( values (1,'All of R489804 R489805 R489806 R489807 R489808'), (2,'SP fr R308999 '), (3,'R308777, R104621, R708931 ') ) a(id,value) cross apply (select N from ...


2

Your sub-select's WHERE clause references a column alias "Name" from the extern alias. This column alias does not exist at the time the sub-select is evaluated, however. Column aliases only come into existence after the "FROM" part of the query completes. Here's a post on this. See the section "Logical Processing Order of the SELECT statement". To ...


-1

Sounds like you need to use CASE WHEN expression, which is well documented. So case when x = y then select this else select that etc.


4

No there isn't anything similar to what you are asking built into the product. Standard SQL has the concept of a NATURAL JOIN that joins on common column names but SQL Server does not implement this and there would be no guarantee that any such names would correspond with the PK anyway. It would be possible in theory for you to write some sort of script ...


0

Assuming your full backups are always done WITH INIT: SELECT TOP (1) @disk = f.physical_device_name FROM msdb.dbo.backupmediafamily AS f INNER JOIN msdb.dbo.backupset AS s ON f.media_set_id = s.media_set_id WHERE s.database_name = @db AND s.[type] = N'D' ORDER BY backup_finish_date;


6

You cannot redeclare variables, nor can you test to see if they have been declared (at least not that I have ever been able to find). BUT, you shouldn't need to do this anyway. If you know the variable names ahead of time, just declare them all at the beginning of the process. Then, use them throughout the script. For example: ...


0

From reading your question I think you are building the SQL code in your application and then sending it to the server as one long script with multiple statements. If this is the case you can separate your statements with GO to create batches. The following is valid SQL: DECLARE @a INT; SET @a = 1; SELECT @a; GO DECLARE @a INT; SET @a = 1; SELECT @a; ...


0

didn't know your table name so I created my own name. change the table name and it should work. This gives the difference in minutes. select A.rec_number,A.tank, CONVERT(varchar(5), DATEADD(minute, datediff (minute,A.start_ts,B.end_ts), 0), 114) as differenceTime from ( select a.rec_number,a.tank, start_ts from dbo.TEST a inner join( select ...


1

Ugh, 2000. Assuming all objects are owned by dbo, you can generate a script, e.g.: SELECT N'GRANT INSERT, UPDATE, DELETE, SELECT ON dbo.' + QUOTENAME(name) + N' TO [user];' FROM sysobjects WHERE type IN (N'U', N'V'); SELECT N'GRANT EXECUTE ON dbo.' + QUOTENAME(name) + N' TO [user];' FROM sysobjects WHERE type = N'P'; Now you can copy and paste the ...


7

The idea that an application should be written with just standard SQL comes from application developers who think that changing a DBMS is a worthy design goal. I -- and many other DBAs with me -- don't agree. While it holds true for basic features, your application is going to perform best if it uses the features that your particular DBMS provides. This ...


2

SQL Server is atrocious when it comes to the performance of scalar functions as well as the reporting of its impact. (a very useful article with details as to why: T-SQL User-Defined Functions: the good, the bad, and the ugly (part 1)) You are correct that the Query plan (actual) does not reflect the true performance of the two different approaches, this is ...


8

Why does SQL server run ths inline SVF query slower - both in CPU and elapsed time? Scalar valued functions are executed in a different context than the main query and setting that up for each call takes time. By centralising some simple logic it appears I impede performance through code reuse. Yes, for scalar valued functions that is true. ...


2

What you need to do is use the LEAD windowing function to look at one row down, two rows, three rows, etc for however long your pattern is. Example: LEAD(gradeissued,1) over (partition by studentid order by whenissued) as one_row. Put that in a CTE or subquery and then in the next query do a where criteria on your pattern (one_row = 'D' and two_row = 'C' and ...


5

One way of doing it would be to use LEAD. WITH T AS ( SELECT StudentID, GradeIssued AS g1, LEAD(GradeIssued, 1) OVER (PARTITION BY StudentID ORDER BY WhenIssued) AS g2, LEAD(GradeIssued, 2) OVER (PARTITION BY StudentID ORDER BY WhenIssued) AS g3, LEAD(GradeIssued, 3) OVER (PARTITION BY StudentID ORDER BY WhenIssued) AS g4 ...


3

Using the newly added LAG() and LEAD() functions makes it look easy: WITH a AS ( SELECT PersonID, FloorNum, EnterTime, PrevFloorNum = LAG(FloorNum) OVER (PARTITION BY PersonID ORDER BY EnterTime) FROM RoomAssignments ) SELECT PersonID, FloorNum, FromTime = EnterTime, ToTime = ...


6

Native tooling approach Grab a newer copy of Management Studio (2012 SP2 and 2014 are both free, fully functional, and can co-exist with your 2008 R2 tools). Then you can do this. Put your cursor right before the first leading ABC. Hold Shift+Alt, then hit the down arrow three times. Type '. It's subtle, but you should see a faint blue vertical line ...


1

If you want to change the current\default database to a different one try: alter login <loginname> with default_database = <dbname>; Now, create a user for above login created use <dbname>; create user <username> from login <loginname>; And now you can assign roles to the above create user for the login as below: use ...


3

USE msdb; GO CREATE USER shims FROM LOGIN shims; GO ALTER ROLE SqlAgentUserRole ADD MEMBER shims; GO Also, for future reference, any time you know how to do something in the UI but not in a script, this is what the Script option on most dialogs is for - it will show you what script SSMS would have executed:


2

SET SINGLE_USER means only one user can connect to the database at a time. If someone or some service (SSIS, SSRS, SQL Agent, etc) connects before you than you will have to wait for them to disconnect


2

Would you like to try this? DECLARE @cmd1 nvarchar(2000) SET @cmd1 = 'IF ''?'' NOT IN(''Name of your database'')' + 'BEGIN ' + 'drop DATABASE [?] ' + 'END' EXEC sp_MSForEachdb @command1 = @cmd1 GO it worked here. PAY ATTENTION TO NOT DROP MASTER AND ETC. Put then on that list.


3

How about something like below : change your output in SSMS to text and once you are happy run the generated sql statements in another query window : use master go select 'Alter database '+ quotename(name) + char(10) +'set single user with rollback immediate' +char(10)+ 'go'+ char(10)+ 'Drop database '+quotename(name) + char(10)+ 'go' from ...


2

After setting the db to singleuser you have to switch back to master. I added a use master statement but I don't want to test this.... EXEC sp_MSforeachdb ' USE [?] IF (DB_ID(''?'') > 4 AND DB_NAME()!=''ABC'') BEGIN ALTER DATABASE ''?'' SET SINGLE_USER WITH ROLLBACK IMMEDIATE **use master;** DROP DATABASE ''?'' END



Top 50 recent answers are included