Tag Info

New answers tagged

3

(Edited to include earlier comments for clarity of the answer.) A better plan for database maintenance is to implement Ola Hallengren's Maintenance Plan. This is free and can be downloaded from https://ola.hallengren.com You can configure the settings to suit your needs. Brent Ozar suggests some minor parameter tweaks at: ...


1

Clearly, Ola Hallengren's solution mentioned by @RLK in his comment is the way to go. Having said that, I just wanted to show you a simple way to create the ALTER INDEX statements without the need for a cursor. DECLARE @cmd VARCHAR(MAX); SET @cmd = ''; SELECT @cmd = @cmd + CASE WHEN @cmd = '' THEN '' ELSE CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + ...


4

This is a sort of bin-packing problem, so you'll most likely need to choose from one of the available approximate solutions, rather than attempting an exhaustive search. One very straightforward idea is to pack projects into groups choosing the project with the largest number of employees each time. As soon as the current project no longer fits into the ...


8

APPLY TOP or ROW_NUMBER()? What could there possibly be more to say on that matter? A short recap of the differences and to really keep it short I will only show the plans for option 2 and I have added the index on Production.TransactionHistory. create index IX_TransactionHistoryX on Production.TransactionHistory(ProductID, TransactionDate) The ...


8

Let's start with the basic scenario. If I want to get some number of rows out of a table, I have two main options: ranking functions; or TOP. First, let's consider the whole set from Production.TransactionHistory for a particular ProductID: SELECT h.TransactionID, h.ProductID, h.TransactionDate FROM Production.TransactionHistory h WHERE h.ProductID = 800; ...


1

May this help you. SELECT GroupNumber ,p.ProjectId, p.ProjectName, e.EmployeeId, e.EmployeeName FROM #Projects p INNER JOIN #Employee_Projects ep ON ep.ProjectId = p.ProjectId INNER JOIN #Employees e ON e.EmployeeId = ep.EmployeeId INNER JOIN ( SELECT MAX(RowNumber) GroupNumber, ProjectId FROM ( SELECT ROW_NUMBER() OVER (PARTITION ...


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


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


1

from a broad sense you'll be just fine doing it this way. Stored procedures have limited scope, so even though (example) 3 users execute the same stored procedure and the temp tables will not co-mingle, they won't even see each other. As long as you don't need to share the results with a different session or a user running a different process Temp table ...


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


1

DECLARE @rc INT = 1; WHILE @rc > 0 BEGIN BEGIN TRANSACTION; INSERT dbo.target(cols) SELECT TOP (5000) cols FROM dbo.source AS s WHERE NOT EXISTS ( SELECT 1 FROM dbo.target AS t WHERE t.key = s.key ) ORDER BY clustering_key; SET @rc = @@ROWCOUNT; COMMIT TRANSACTION; END Also see: Break large delete ...


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


1

Take the query you already have and capture the output in a temp table #T. select cast(T.creationDate as date) as ForDate, datepart(hour, T.creationDate) as OnHour, count(distinct T.userId) as Totals into #T from dbo.T group by cast(T.creationDate as date), datepart(hour, T.creationDate) Get the distinct ForDate values from #T and ...


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


1

Apparently, creation of CDATA nodes is possible only in the FOR XML EXPLICIT mode. Moreover, Microsoft implementation of XML does not respect them, in accordance with W3C recommendations. Here is a link: How to get [CDATA] with FOR XML PATH? So, if you truly need this, you'll have to write an ugly monster, and don't even think about the TYPE option - it ...


1

I recommend using a Calendar table or Date Dimension (whichever name you prefer). Here is an answer with using a quick CTE. /* Date Range CTE */ -- Updated based on @AaronBertrand's articles linked in the comments -- Basically ends up being the same query as the last half of @AaronBertrand's post declare @FromDate date; declare @ThruDate date; set ...


1

Well, you should have a Numbers table or a Calendar table. I'll start with a Numbers table: CREATE TABLE dbo.Numbers(Number INT PRIMARY KEY CLUSTERED); INSERT dbo.Numbers WITH (TABLOCKX) (Number) SELECT TOP (1000000) Number = ROW_NUMBER() OVER (ORDER BY s1.[object_id]) FROM sys.all_objects AS s1 CROSS JOIN sys.all_objects AS s2; Then: DECLARE ...


10

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


7

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


18

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


1

Expanding on James' answer Try this code on SqlFiddle. Also check out @SqlKiwi Paul White's post regarding Delete vs Truncate create table tracker_live ( client_nbr int not null, tracker_date date not null, submit_date date not null, live float not null, retainer float not null, bonus float not null , constraint pk_tracker_live primary key ...


0

I believe something like this will do what you want: DECLARE @Test DATE=GETUTCDATE() SELECT @Test -- compute the DATE for the last day of the previous month DECLARE @LastDayOfLastMonth DATE=DATEADD(day,-DATEPART(Day,@Test),@Test) -- dump out the DATE for the last day of the previous month SELECT LastDayOfLastMonth=@LastDayOfLastMonth -- dump out the first ...


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


-3

Actually when we use the where clase then sql creates seperate pages for each where clause. then its make join based on AND , OR and then produce the output. So, here switching the where clause is not a matter.


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


1

I'm wondering if these views could be re-written using CTEs, to somehow 'pre-fetch' the modified/pre-fixed schemas using the functions, and then query the tables with SELECT INTO to populate data into the view. No, this isn't possible with a view. You could do something broadly along these lines with a multi-statement function (MSUDF), but: This would ...


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


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


1

I am not able to reproduce the issue with the code you have given. Am getting only one warning message in the line UPDATE #tmpImportData SET group_id = CAST(group_id_text AS INT) like invalid column name group_id(its because the column is added to the temp table dynamically) Can you recheck whether you are getting the issue with code you have given in ...


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


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


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


1

You can solve the determinism issue using a CTE or a derived table, as follows: SELECT TOP 10 team_name, score_points FROM (SELECT team_name, score_points=<big expression> FROM teams) T WHERE score_points >= 100 ORDER BY score_points DESC Should you still encounter performance problems, probably there could be something to revise in ...


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.


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


-1

I tried with this one... select a.username from (select username ,col2 from yourtable where col2 is null) a,(select username ,col2 from yourtable where col2 is not null) b where a.username=b.username;


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;


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 ;


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


0

If the tables were in a user defined schema, say data and not the default dbo then you can GRANT select on the schema GRANT SELECT ON SCHEMA::Data TO MyRoleOrUser I regard this as best practice. It also means any new tables inherit SELECT permisisons from the schema and don't need explicit grants SQL Server: hierarchy of permissions for schema? Decision ...


0

Run this query: select 'Grant select on ['+t.name+'] to [MyRole];' from sys.tables as t; Copy the output to a new SSMS tab, edit as required and submit for processing.


0

There are a couple of alternatives. You could set the Select permission to the Schema that owns the tables. (dbo?) grant select on schema :: dbo to myUser --or myDBRole or use query to generate the 100 grant select statements and execute. SELECT 'grant select on ' + TABLE_NAME + ' to myUser' --or myDBRole FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE ...


0

Use GRANT statement with tables delimited by comma like below. Grant select on tbl_1, tbl2, tbl3, tbl4, ...., tb1100 to [MyRole];


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


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


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


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


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


0

You can use PIVOT with dynamic sql to solve your problem. Below is a simple implementation to solve the issue. The query will support any number of months you may have. Make sure the length of @pivcols is large enough to accommodate the maximum anticipated string length for all the current and future months (povot columns) concatenated. --Table structure-- ...



Top 50 recent answers are included