Tag Info

Hot answers tagged

18

This is a bit messy to get the final result because you have multiple SCHEMA_VER for each date. Before I demonstrate how to do this with dynamic SQL, I'll first show how to do it with static code to get the logic correct. In order to get the final result you can utilize both pivot and unpivot. But first, I'd change your original query to use the following: ...


13

That is just documented behavior. I don't think anyone messed with the settings. See data type precedence on MSDN. As noted in the comments the empty string gets converted to 0 in any numeric type and to 1900-01-01 00:00:00.000 when converted to a date. EDIT: I think your real problem is that your design is so that you have to join on fields of a ...


9

Should be something like this: DELETE A FROM table_example1 AS A INNER JOIN table_example2 AS B ON A.COLUMN1 =B.COLUMN1 AND A.COLUMN2 = B.COLUMN2 WHERE COLUMN_DATETIME > @Period; Alternatively: DELETE FROM A FROM dbo.table_example1 AS A WHERE EXISTS ( SELECT * FROM dbo.table_example2 AS B WHERE B.COLUMN1 = ...


7

An alternative solution that avoids the IN and the grouping in Aaron's answer: DECLARE @SelectedLanguageId integer = 48; SELECT SC.Id, SC.StringKeyId, Result = CASE -- No localization available WHEN LST.StringTranslationId IS NULL THEN SK.Name ELSE ( -- ...


6

This will not be fast (this took over a minute to produce 0 results in my local copy of AdventureWorks). DECLARE @SpecificWord NVARCHAR(32); SET @SpecificWord = N'donut'; DECLARE @sql NVARCHAR(MAX); SET @sql = N'SELECT NULL,NULL WHERE 1 = 0'; SET @SpecificWord = N'%' + @SpecificWord + N'%'; SELECT @sql += N' UNION ALL SELECT ''' + QUOTENAME(s.name) + ...


6

The NTILE function breaks an input set down into N equal sized groups. To determine how many rows belong in each group, SQL Server must first determine the total number of rows in the input set. If the NTILE function includes a PARTITION BY clause, SQL Server must compute the number of rows in each partition separately. Once we know the number of rows ...


6

It means the number of columns you select in the top query must be the same as the number of columns in the second. If you don't have the same number, you can work around it. Below I can add a NULL to the second query because it's missing a third column. SELECT col1,col2,col3 FROM t1 UNION ALL SELECT col1,col2, NULL FROM t2


5

If you have a table where the relationship between the numbers and letters is stored, it's rather trivial. If you haven't you can simply add it - every time you run the query - as a CTE or derived table: SELECT COALESCE(test.a, test.b, test.c, test.d) AS test, map.letter FROM test LEFT JOIN ( VALUES (1, 'e'), (7, ...


5

If you just want to Truncate and reload data, then faffing around with indexes is not necessarily useful. If you're inserting data in Clustered Index order i.e. in CONCEPT_CD ASC order, then there's no real advantage to dropping the Clustered Index. It'll be far more pain rebuilding it at the end on 3 billion rows than it would inserting the data in your ...


4

No, absolutely impossible. Each sqlcmd invocation is a different session and transaction cannot span sessions. Your use of a transaction on the script that invokes the sqlcmd is completely useless, it has nothing to do with the scripts being run.


4

Adding explanation and a fiddle: http://sqlfiddle.com/#!6/c92b2/5. The query bellow: 1. uses a subquery to select the min and max versions by date (min and max are applied to integers to guarantee that for instance 6 < 16) 2. Then Selects the year (to group later), Date (to order) and the min - max versions SELECT LEFT(UPG_DATE, 4) AS Year , ...


3

You can convert your string to XML and use a cast as xs:int ? to check if the value is an integer or not. declare @T table(C varchar(100)); insert into @T(C) values ('Widget (1234)'), ('Different Widget (123)'), ('Many Widgets (2x) (4567)'); select T.C, T2.X.value('(for $n in X return $n/text()[1] cast as xs:int ?)[1]', 'int') as Value from @T as T ...


3

If the data you need is always at the end of the string, would something like this work? declare @test varchar(200); set @test= 'Many Widgets (2x) (4567)'; select substring(@test, (len(@test) - charindex('(',reverse(@test))) + 2, charindex('(',reverse(@test)) - 2 ); We can reverse the string, find the ...


3

You should really consider fixing this data before it gets into your tables, because working around bad data is cumbersome. DECLARE @x TABLE(col VARCHAR(15)); INSERT @x(col) VALUES ('00000000014545p'),('00000000012645n'), ('00000000012345p'),('000000004512345'); ;WITH x AS ( SELECT col, switch = CASE WHEN RIGHT(col,1) NOT LIKE '[0-9]' THEN 1 END FROM ...


3

This sounds like dynamic SQL where you're constructing a query from other subqueries... The thing is that subqueries have slightly different rules. A subquery can't contain a CTE, nor can it be ordered without a TOP/OFFSET/FOR XML. So what you need to do is have a CTE section at the start of your main query, and add your CTE to that. You don't have an ...


3

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


3

OPTIMIZE FOR is used for making a good plan for specific query. A classical example is a report to skewed data that is run very often with same parameters. In such a scenario, it could be useful to optimize the query for the most common parameter. This is a trade-off, as other queries with different parameter are going to get worse a plan. If you are ...


2

Rather than calculate the value in code, consider a computed column instead. ALTER TABLE dbo.Customers DROP COLUMN Id; ALTER TABLE dbo.Customers ADD Id AS (CASE CustomerType WHEN 1 THEN N'P' ELSE N'x' END + CAST((SequenceId + 1000000) AS nvarchar(10))); Below is an example of the insert proc using this computed column: CREATE PROCEDURE ...


2

After finding out exactly what the data was I had to modify the code given from Aaron Bertrand to handle "over punch dibol ascii" DECLARE @x TABLE(col VARCHAR(15)); INSERT @x(col) VALUES ('00000000014545p'),('00000000012645v'), ('00000000012345w'),('00000000012845x'), ('000000004123450'),('000000004512345'); WITH x AS ( SELECT col, switch = ...


2

When running the execution plan, I noticed that each one of the 5 or so queries that are parsing the filter data are costing about 12% which is over 60% of the query just to determine the data we are going to be filtering by. The query costs are based on estimates even in the actual execution plans. They do not tell you how efficient the query ...


1

With this solution you can easily know which jobs are on the queue to run with a simple select to a table. Also, if the server goes down for some reason, cleaning the jobs that are waiting to run is as easy as a truncate on a table. And still if you want them to run you needen't do anything and they'll run orderly. Create a table (Say, ...


1

In more research and diving into the msdn site documentation I have found and am now using. sp_add_schedule, sp_attach_schedule and sp_detach_schedule, sp_delete_schedule so when the job sees that others are running ATM I am doing this: exec msdb.dbo.sp_add_schedule [MSDN link][1] @schedule_name = @TempScheduleName, @enabled = 1, @freq_interval = ...


1

Yep, indeed you can! So you are hoping to have one job complete and the last step of that job would be to kick off another job? Here is the command to kick off the next job: sp_start_job E.g. The first job has a schedule and final step of the job is to run: exec msdb.dbo.sp_start_job N'My Second Job'; "My Second Job" doesn't have a schedule, relying on ...


1

In this case, if you cant serialize this in the application code you can use try/catch or an applock begin tran if APPLOCK_TEST ('public','idx_create','exclusive') = 1 begin EXEC sp_getapplock @Resource = 'idx_create', @lockmode = 'Exclusive', @DbPrincipal='public' print 'I can do it' -- EXEC ...


1

You can use this stored procedure to the trick You need to provide the table name you wish to query note that if you'll pass to procedure the @exec parameter = 1 it will execute the select query SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[SP_SELECT_NON_NULL_COLUMNS] ( @tablename varchar (100)=null, @exec int =0) ...


1

I have been troubleshooting an issue within a database that has some serious blocking issues. I am just wondering if there is a way to see what variable is associated with the query. I would suggest, you should use either of below for troubleshooting blocking problems : You can use beta_lockinfo by Erland Sommarskog beta_lockinfo is a stored ...


1

If you want to be able to view the exact backup history for longer time than those four weeks that you keep the backup files on disk, make sure that you set the cleanup task not to delete the backup history from the msdb database, you can then run the backup report from SSMS (right click database, select reports, standard reports, backup and restore events) ...


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



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