Hot answers tagged

11

As long as all the variables involved are of datatypes compatible with sql_variant (they are in this case - basically no LOB datatypes, CLR types, or user defined datatypes) then you can use SELECT CASE WHEN COALESCE( CAST(@StartDate as sql_variant), @EndDate, @StateCode, @CountyCode, @ProducerName, @TaxID, @Farm) IS NULL ...


10

I find the simple syntax the more readable and less confusing. Why use functions (that can throw these weird errors) when you can check whether all variables (one by one) are NULL?: CASE WHEN (@a IS NULL AND @b iS NULL .... AND @x iS NULL) THEN 'yes' ELSE 'no' END Alternative conditions - although less readable in my opinion would be: ...


10

One thing to consider is that a Primary Key and a Clustered Index are not the same thing. A Primary Key is a constraint and deals with the rules by which the data lives (i.e. data integrity); it has nothing to do with efficiency / performance. A Primary Key requires that the key column(s) be unique (in combination) and NOT NULL (individually). A PK is ...


9

The simplest answer to your question involves telling SQL Server what to look for by placing brackets around grouped requirements. So, for instance, if we only want to check Field2 when @parameter1 IS NULL, we could do this: WHERE (@parameter1 IS NULL AND Field2 = @parameter2) OR (Field1 = @parameter1) SQL Server will evaluate Field2 = @parameter2 ...


9

One way would be to pivot WITH CTE AS ( SELECT [Document No_], 'SL' AS Source FROM [Sales Line] UNION ALL SELECT [Document No_], 'SLA' AS Source FROM [Sales Line Archive] ) SELECT P.[Document No_], P.SL, P.SLA FROM CTE PIVOT (COUNT(Source) FOR Source IN ([SL], [SLA])) P WHERE P.[Document No_] IN (1,2,3); I'd hope that the predicate on [...


9

Do I need to create a store procedure first that way the inputs are recognized? You are missing @ symbols at the beginning of each parameter. Find out more about writing functions here Once you know the basics this article by Jeremiah Peschka explains the benefits of inline functions and much more. Basically if your table valued function is not ...


8

One way would be to add an ID column in your users table, with a default value based on a sequence object, then add a computed column to create the key using a date conversion to string. To create the sequence, something like: CREATE SEQUENCE [dbo].[userid_sq] AS [int] START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 500 CYCLE I wouldn't limit ...


7

For a non-unique clustered index on comany_id alone, SQL Server will automatically add a 4 byte integer uniqueifier to all duplicate (i.e. second and subsequent for a key value) clustered index keys to make it unique. This is not exposed to the user though. The advantage of adding your own unique identifier as a secondary key column is that you can then ...


7

As you don't care about the order of the concatenated items it would be quite easy to knock up a custom CLR aggregate to do this and it will likely out perform the XML method, there is an example of one in this article. There is a quick and easy change you can make to your existing code though. Instead of SELECT DISTINCT email, STUFF((...


6

That STUFF FOR XML PATH string concatenation technique sure is cute, but it does not scale very well and across millions of rows it is probably not a very good idea. For larger tables, you may have to write some good old-fashioned procedural SQL with a loop, something like this: -- Create the working table ... IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP ...


6

What would the syntax be to write an update statement to update the null values for fields power1, power2, power3 with the values already listed in the table? I take this to mean that each company's nulls should be populated based on the values taken from the one populated entry that, as you say elsewhere in your post, each company has. So, in pseudo-code, ...


6

If I understood you, this should do the job: SELECT ... WHERE (Field2 = @Parameter2 AND @Parameter1 IS NULL) OR (Field1 = @Parameter1 AND @Parameter2 IS NULL) However, I'd rather not do such things because of potential performance issues. Execution plan for this query depends to a great extent on parameter values. For instance, passing NULL as ...


5

UPDATE dbo.[User] SET username_column = N'ACCOUNTS' + LOWER(SUBSTRING(username_column, 9, 4000)) WHERE username_column LIKE 'ACCOUNTS\%';


4

This is a classic case of a pivot operation in SQL. The method working in, probably, most (if not all) SQL products, including SQL Server, is conditional aggregation: SELECT UserID, Year, M1 = SUM(CASE Mth WHEN 1 THEN Value END), M2 = SUM(CASE Mth WHEN 2 THEN Value END), M3 = SUM(CASE Mth WHEN 3 THEN Value END), M4 = SUM(CASE Mth WHEN 4 ...


4

COALESCE must return a valid datatype. I think your problem is related to the way it works to determine datatype to return. A quick workaround can be: IF (@StartDate IS NULL AND @StateCode IS NULL AND ....) BEGIN SELECT 'Yes' END ELSE SELECT 'No'


4

I'd try this but I have no idea if it will be more efficient. You need the DISTINCT to remove duplicates, so the UNION ALL might be more appropriate, no need for two distinct operations: SELECT DISTINCT JobRunId = @JobRunID, d.* FROM dbo.TestUpload CROSS APPLY ( SELECT ThingAName AS Name, ThingAType AS [Type], ...


4

Use cross apply to unpivot columns into rows SELECT --DISTINCT most probably JobRunId, ut.Name, ut.[Type], ut.Granularity FROM dbo.TestUpload CROSS APPLY ( SELECT ThingBName AS Name, ThingBType AS [Type], ThingBGranularity AS Granularity UNION ALL SELECT ThingAName AS Name, ThingAType AS [Type], ThingAGranularity AS ...


3

You are doing a cartesian product here: ... FROM dbo.[DataSource], @ids i you need to know which address_id corresponds to which user, and for that you must have a clause. Something like this will do it: INSERT INTO dbo.[Users] ( first_name, surname , address_id ) SELECT first_name, surname, ...


3

If I understand the Question correctly, you need to use the XML_SCHEMA_NAMESPACE built-in function to extract the Schema Collection, and you can find the list of those in the sys.xml_schema_collections system catalog view. You can use something along the lines of: SELECT CONVERT(NVARCHAR(MAX), XML_SCHEMA_NAMESPACE(sch.[name], xsc.[name])) AS [XSD] FROM ...


3

The context of this question is strikingly similar to this question: LOB_DATA, slow table scans, and some I/O questions. But the direction is a bit different. In order to answer whether or not there would be much benefit in moving the XML column into its own table requires answering the following questions first (and I will explain why in a moment): How ...


3

The database engine is pretty smart about this and does not read LOB pages unless it has to as far as I can tell, but I created a test-rig to double-check. Test Rig USE master GO SET NOCOUNT ON GO CREATE DATABASE bigXMLTest GO ALTER DATABASE bigXMLTest SET RECOVERY SIMPLE GO ALTER DATABASE bigXMLTest MODIFY FILE ( NAME = N'bigXMLTest', SIZE = 20GB , ...


2

is there a statement to check if a specific index's auto stats is disabled? You can use sys.sp_autostats or sys.stats to get this information. sys.sp_autostats can also be used to enable or disable automatic updates for a particular statistics object. For example, using the AdventureWorks sample database: EXECUTE sys.sp_autostats @tblname = N'[...


2

Ms Sql recursive CTE syntax has no RECURSIVE keyword See https://technet.microsoft.com/en-us/library/ms186243(v=sql.105).aspx Ie. CTE of the form WITH cte_name ( column_name [,...n] ) AS ( –- Anchor member UNION ALL –- Recursive member ) is recursive when Recursive member references cte_name. For example WITH node_rec AS ( SELECT TOP(10) 1 AS ...


2

Probably you need tree nodes in depth-first order with tag as ( select * from (values (1,'.Net',cast(null as int)), (2,'EF',1), (3,'NHib',1), (4,'CF',2), (5,'Java',null), (6,'JRE',3)) t([Id],[Name],[ParentTagId]) ), cte (ParentID, ID, Name, lev, shift)AS( SELECT ParentTagID, ID, Name, ...


2

s is not valid at that point you only have inserted if you want the fields then try OUTPUT inserted.ID, inserted.[Field1], inserted.[Field2] instead of line 2 in your query. You can find more here: OUTPUT Clause (Transact-SQL)


2

You can use a common table expression (cte). ;with cte as ( select row_number() over(partition by bbid order by #one.id desc) as rn ,id from #one ) select t1.id ,t3.bbid ,t3.indate ,t3.st ,t1.val from #One as t1 join #Two as t3 on t1.bbid = t3.bbid join cte on cte.id = t1.id and cte.rn = 1 order by t1.id desc


2

update dbo.B set b.[ID] = a.orig_id FROM dbo.A a join dbo.B b on b.[NUMBER] = a.number and b.[ID] <> a.orig_id the <> is so it can avoid taking a lock if the are equal


2

Yes. You can write your own snippets code in a .snippet file and then import it into SSMS. To import the folder containing the .snippet file: Open SSMS Click on Tools ->Code Snippets Manager Click on Add and select the folder where your custom snippets are written. I believe this answers a part of your question. You can further follow the below links to ...


1

select * from ( select t1.id , t3.bbid , t3.indate , t3.st , t1.val , row_number() over (partition by bbid order by id desc) as rn FROM #One t1 INNER JOIN #Two t3 ON t1.bbid = t3.bbid ) tt where tt.rn = 1 or just put this in your query as t2 Select MAX(ID) As "Maxid" FROM #One GROUP BY bbid


1

Replace and Lower should do the job here. UPDATE dbo.User SET username = 'ACCOUNTS\' + LOWER(REPLACE(username, 'ACCOUNTS\', ''))



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