Tag Info

Hot answers tagged

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


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


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


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


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


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


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


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


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


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


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


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


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


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


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


2

You never set the SequenceId to anything. It is not possible to set the value of a column during an insert using an expression that references the value of an IDENTITY column on that same table that will be set upon that same insert operation. If you think about it this makes sense because the row doesn't exist until you insert it, so there is no IDENTITY ...


1

SequenceID is invalid,since you are not specifying from where to get the value.I suggest,create one more temp variable and add ,some thing like below declare @seqid int select @seqid =next value for from your sequence then in place of sequence ,keep this variable


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


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


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


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


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


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


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



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