Tag Info

Hot answers tagged

12

The typical way to do this in SQL Server 2005 and up is to use a CTE and windowing functions. For top n per group you can simply use ROW_NUMBER() with a PARTITION clause, and filter against that in the outer query. So, for example, the top 5 most recent orders per customer could be displayed this way: DECLARE @top INT; SET @top = 5; ;WITH grp AS ( ...


9

In DBMS, like MySQL, that do not have window functions or CROSS APPLY, the way to do this would be to use standard SQL (89). The slow way would be a triangular cross join with aggregate. The faster way (but still and probably not as efficient as using cross apply or the row_number function) would be what I call the "poor man's CROSS APPLY". It would be ...


8

This is just Standard SQL join syntax with the optional parentheses removed: SELECT * FROM tableC LEFT JOIN ( TableB RIGHT JOIN TableA ON TableA.ID = TableB.ID ) ON TableB.TypeID = TableC.TypeID If you don't like the syntax generated by the SSMS view designer (which is buggy and rarely updated anyway), simply write the views by hand using ...


8

Yes, each user will get their own copy of the #temp table, even if they run at the exact same time. (However, don't use global ##temp tables, signified with two leading pound/hash signs.) But why do you need a #temp table here at all? Something like this should work (untested, as I don't have LDAP anywhere near me): CREATE PROCEDURE dbo.stp_adlookup -- ...


7

There is no difference in result but there is a bit different semantics. X [comparison] ALL(set) mean that set is empty or the comparison is TRUE for each entry in the set. X NOT IN (set) means that X does not belong to the set. While [comparison] is "not equal", both forms are equivalent. But for other comparisons it may be different.


7

Another solution: SELECT Y1.* FROM dbo.yourtable AS Y1 WHERE Y1.username = ANY ( SELECT Y2.username FROM dbo.yourtable AS Y2 WHERE Y2.col2 IS NULL INTERSECT SELECT Y3.username FROM dbo.yourtable AS Y3 WHERE Y3.col2 IS NOT NULL ); In a similar logical vein: SELECT Y.* FROM dbo.yourtable AS Y WHERE EXISTS ( SELECT * ...


6

You should be able to use conditional aggregation to get the username with both a value in col2 as well as null. I'd suggest using a HAVING clause with the conditions. The query would be similar to: select username from yourtable group by username having sum(case when col2 is not null then 1 else 0 end) = 1 and sum(case when col2 is null then 1 else 0 ...


6

No, a loop is not necessarily faster than a cursor, though some people are more comfortable with them. I went through the loop writing phase myself at one time. Also cursors come in several flavors, so choosing the right type of cursor is an important detail. Your question is probably answered by Aaron Bertrand (from 2012), since he ran several comparative ...


6

1) SET DATEFORMAT has no effect on the actual storage of the table. It is only used to format output and interpret strings. Behind the scenes, all the date formats are stored as integers in a canonical format. The actual representation of the data type depends on which type is used. For example, SMALLDATETIME is an integer that stores the number of seconds ...


5

I typically use a combination of CTEs and windowing functions. You could achieve this answer using something like the following: ;WITH GiveMeCounts AS ( SELECT CustomerID ,OrderDate ,TotalAmt ,ROW_NUMBER() OVER ( PARTITION BY CustomerID ORDER BY --You can change the following field or sort order to ...


5

This can't help with the determinism issue, but CROSS APPLY is a good tool for big expressions that need to be reused: SELECT TOP 10 team_name, CxA.Score_Points FROM teams t CROSS APPLY (SELECT Score_Points = <big Expression, refer to t>) CxA WHERE CxA.Score_Points >= 100 ORDER BY CxA.Score_Points DESC CROSS APPLY expressions get calculated for ...


4

This one works too. SQL Fiddle demo I obtain C1 as the total rows for each username, C2 as the total null rows for each user name and I compare these values later. SELECT username, col2 FROM ( SELECT *, (SELECT Count(*) FROM T Where username = T1.username) C1, (SELECT Count(*) FROM T Where username = T1.username and col2 is null) C2 FROM T T1 ) T2 WHERE ...


4

I am unsure if its necessary to add the TABLOCK table hint to an empty temporary table, defined with a clustered index in order to get minimal logging. No. Local temporary tables (#temp) are private to the creating session, so a table lock hint is not required. A table lock hint would be required for a global temporary table (##temp) or a regular table ...


4

How about simplifying greatly: UPDATE d SET [CreatedDate] = [Source].[t_CreatedDate], [DbDate]= [Source].[t_DbDate], [ModifiedDate] = [Source].[t_ModifiedDate], [SubGUID] = [Source].[t_SubGUID], [eType] = [Source].[t_eType] FROM dbo.NO_table AS d INNER JOIN @track AS [Source] ON d.JobID = [Source].t_JobID WHERE d.[ModifiedDate] < ...


4

Just another way to do it: ; WITH cte AS ( SELECT username, col2, cnt_all = COUNT(*) OVER (PARTITION BY username), not_null = COUNT(col2) OVER (PARTITION BY username) FROM yourtable AS a ) SELECT username, col2 FROM cte WHERE cnt_all > not_null AND not_null > 0 ;


4

If you do 'NewID' as [field/@id] you will get a field element with an id attribute. On the next line you add B.ID as [field] ot get the value of ID as the node value to the field node you created on the line before. <field id="NewID">1</field> After that you want a new field node and to create that you can use a node without a name that has no ...


4

You should be just fine, we have countless SPs here that get run 1000s of times a day with temp tables that are named the same and don't have any issues. Here's a visual example. I've created 2 tables on my SQL2014 instance. One was created from SPID 53, the other from SPID 57. Here's what it looks like in Object Explorer: As you can see, though ...


3

You need to perform the update in dynamic SQL too (or just create the table with all columns in the first place). The error is happening because the update with the new column is being parsed before the dynamic SQL has run. As an aside, you are probably only getting this error if you try to execute the stored procedure and choose 'Display estimated ...


3

I would use the sub-query to select those usernames like: select username from dbo.yourtable group by username having sum(distinct case when col2 is not null then 1 else 2 end) = 3;


3

It looks like you want to keep the rows where the combination of SSN, FName and LName are the most common. You can do that with an updateable CTE that counts the number of occurrences for each combination using count() over() and then enumerates the counts using rank(). declare @T table ( SSN int, FName char(1), LName char(1) ); insert into @T ...


3

You need to create a multiplication first - every combination of Property + RoomType. In SQL Server we use a CROSS JOIN for that. Then you need to outer join to the junction table, and conditionally display Yes/No based on whether the junction table had a matching row. This should provide the output you want: SELECT p.Property, RoomTypeID = t.Id, -- ...


2

According to your sample query, you must have two separate NonClustered Indexes on Session_id column for both tables. Further it must contain columns that are being used by SELECT clause to avoid bookmark lookups. Fill factor for indexes on uniqueidentifier: If only key column (like in your case) is uniqueidentifier then you can fill all the page (Fill ...


2

While the syntax of your trigger is not quite correct, this is not the source of the error. The main problem... Msg 8197, Level 16, State 4, Procedure ComputeGrade The object 'dbo.StudentMarks' does not exist or is invalid for this operation. ...is exactly what the error message states. Either you have spelled the name of the table wrong, you are in ...


2

A few things: Remove the set implicit_transactions off Actually, this is probably for the best as you don't want them on. This setting has nothing to do with a "top level transaction". When implicit_transactions are ON, INSERT and DELETE (and others) will auto-start a transaction. You do not need a BEGIN TRAN / COMMIT around a table variable. Not only does ...


2

@Jacob - I would say we've all had an unpleasant experience at some point in our lives, so don't beat yourself up. It's admirable that you're seeking guidance and learning from it. So hats off to you. Some key things to target might be the impacted rowcounts. If you're inserting into @idsToDelete, ideally you have a recordset around 1000 rows or ...


2

You can find out the referencing entity dependencies for your function or any other database entities by using DMV dm_sql_referencing_entities. An example statement is as below. Select referencing_schema_name, referencing_entity_name, referencing_id, referencing_class_desc, is_caller_dependent from sys.dm_sql_referencing_entities ('dbo.ufn_MyFunction', ...


2

You would need to use Dynamic SQL to pass a dynamic file path to the Bulk Insert. DECLARE @FileName NVARCHAR(4000); SET @FileName = '/path/to.csv'; DECLARE @sql NVARCHAR(4000) = 'BULK INSERT #CSV FROM ''' + @FileName + ''' WITH ( FIELDTERMINATOR ='','', ROWTERMINATOR =''\n'' )'; EXEC(@sql);


2

Looks like somebody is really trying to hack into your SQL Server with brute force attacks. I recommend taking a look at this Whitepaper on SQL Server security, that will help you a lot. Since most attacks happen at the same time, make sure you don't have a security scanner on your network that runs its checks at that time. Products such as Nessus can ...


2

Although not a direct answer to the question, I'm not entirely convinced that the examples that were shown in the blog post you linked to were really a good example of SET DATEFORMAT. One of the key reasons you should use DATEFORMAT, is when a date can be interpreted in multiple ways. If you work for a global business where the date formats vary between ...


2

Here is a working FOR XML EXPLICIT example. They are a bit harder to code, but I tend to build them up, section by section so they're not so bad: USE tempdb GO SET NOCOUNT ON GO IF OBJECT_ID('dbo.Borrowers') IS NOT NULL DROP TABLE dbo.Borrowers CREATE TABLE dbo.Borrowers ( ID INT IDENTITY PRIMARY KEY, FirstName ...



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