Tag Info

Hot answers tagged

11

As I read the question, the basic recursive algorithm required is: Return the row with the earliest date in the set Set that date as "current" Find the row with the earliest date more than 90 days after the current date Repeat from step 2 until no more rows are found This is relatively easy to implement with a recursive common table expression. For ...


8

There are two key differences between EXCEPT and NOT IN. EXCEPT EXCEPT filters the DISTINCT values from the left-hand table that do not appear in the right-hand table. It's essentially the same as doing a NOT EXISTS with a DISTINCT clause. It also expects the two tables (or subset of columns from the tables) to have the same number of columns in the ...


7

DateTime has a precision of has a 0.00333(1/300th) seconds, so sql server rounds value to nearest 1/300th. If you are using SQL Server 2008 above, use DateTime2 which can have a precision of millisecond, even down to 100 nanoseconds and can hold the accurate value. SELECT CAST('2014-11-28 15:06:02.165' AS DATETIME2(3));


6

Ok, there are a few things to explain first. (1) The Connect to Server prompt within the Login properties is only asking for authentication. You cannot specify a database name within the Server Name field of that window in order to connect to it. For the server name you specify either server or server\instance name. You would connect to a specific database ...


5

SQL Server has an upper bound on creating efficient query plans given a moderately complex query involving a lot of joins - there isn't a single upper bound or magic formula to determine when a query is complex enough to cause a problem; it is very case-by-case and involves baselining from some known expectation (people sometimes think a certain query ...


5

To check for a specific effective permission on a securable, use HAS_PERMS_BY_NAME. For example, to show if the current security context has SELECT permission on the Production.Product table in the AdventureWorks sample database: SELECT HAS_PERMS_BY_NAME ( N'Production.Product', N'OBJECT', N'SELECT' ); Related: To ...


3

Use script instead to check status of currently running job. SELECT job.name, job.job_id, job.originating_server, activity.run_requested_date, DATEDIFF(MINUTE, activity.run_requested_date, GETDATE()) as Elapsed, case when activity.last_executed_step_id is null then 'Step 1 executing' else ...


3

--======================================================= -- delete the duplicate records from table @t -- keeping a single unit of each -- marcelo miorelli 24-nov-2014 --======================================================= --======================================================= --create a table variable and insert records in it -- just for this ...


3

Based on your question, I would index the Timestamp column with the clustered index. And to make the index unique, just make sure to include the identity column in the index definition: ... PRIMARY KEY CLUSTERED ([Timestamp], [Id]) If query performance for queries on Exchange_Id is still an issue after that, you can also add a non-clustered index that ...


2

(This is very similar to a question recently posted on S.O.: Time trigger database updating) There is no direct way of doing this, at least not in SQL Server. In that similar question linked above I did detail a way of getting very close to this concept in SQL Server, and someone mentioned Service Broker, but these are still indirect and have not really ...


2

select ROW_NUMBER() OVER (PARTITION BY num, com ORDER BY identity_field) as 'RowNumber', t1.* into ##TableA from TableA t1 --delete t1 select t1.* from TableA t1 join ##TableA t2 on t2.identity_field=t1.identity_field where t2.RowNumber > 1 but first make backup of your table


2

If I can understand correctly what you are looking to do, you want to SELECT all records from TableA that only appear once, but don't already appear in TableB. You can do this with a GROUP BY and HAVING to identify those that only appear once in TableA, then do an EXCEPT to filter out those that already appear in TableB. SELECT int, com FROM TableA ...


2

some 7 seconds for 500k to return, and also a lot of time to render the grid you're likely displaying the results in. You are waiting 7 seconds because that's how much it takes for SQL to push 500k rows to your client. Look at client statistics in your SSMS, see Database Engine Query Editor: Include Client Statistics: Includes a Client Statistics ...


2

The spatial index, as with other objects, have their own script <object> as menus and need to be done separately. This can be done to the clipboard and pasted to the query window generated by the script table as command. I can't confirm at the moment, but I suspect that the primary key and other constraints come out in the script table as options ...


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

@AaronBertrand is right. You should consider a different method of refreshing your staging environment. If for no other reason that any sensitive data that you have in production is now available to a new group of users who may not be as carefully restricted as in production. However I realize that changing your process can take time (if management will ...


2

Your best bet is to create three separate jobs with the same schedule to kick the jobs off at the same time. Depending on what the jobs are doing you should be careful to monitor blocking and deadlocking.


2

Here how can you create HTML body part of your mail. Step 1: DECLARE @Body NVARCHAR(MAX), @TableHead VARCHAR(1000), @TableTail VARCHAR(1000) SET @TableTail = '</table></body></html>' ; SET @TableHead = '<html><head>' + '<style>' + 'td {border: solid black;border-width: ...


2

Is the default setting for Sql Server comparable to "innodb_flush_log_at_trx_commit = 1" or to "innodb_flush_log_at_trx_commit = 2"? No. Assuming similar hardware, SQL Server should be just as slow as MySQL, since both would have to flush the commit for every row. Therefore there must be something else at play, very likely in the (not posted) C# code ...


1

Option 1 I would probably take advantage of using a Central Management Server to run the password change script across all the different servers at once. This should provide an easy method for verifying the output from each server while keeping it easy to manage by executing once for all target servers. Option 2 If I was doing this in a tighter change ...


1

I would change my comment as answer. Value Buffer pool target is not in amount of 8 KB pages but the value is in Kbytes . So as per output the target buffer pool memory is 32751616 Kbytes which would be 31 G. Now for reason why Bpool target is showing value 32 G in DBCC MEMORYSTATUS and memory manager is showing 12 G as Target committed is because your ...


1

In SSMS 2008, I believe you need to delete the file C:\Users\%username%\AppData\Roaming\Microsoft\SQL Server Management Studio\%Version%\SqlStudio.bin This will, however, clear ALL of the logins and saved servers. You may be able to edit the file using an editor, but I believe it would just be easier to start from scratch. If you are using SSMS 2012 or ...


1

Stop worrying about timer/queue waits like CLR_SEMAPHORE. These waits always accumulate wait time, but this is not "bad" wait time in 99.999% of all cases. You should be filtering them out of your analysis and focusing on the ones that matter and that you can do anything about anyway. Use Paul Randal's NOT IN list as a guide: Wait statistics, or please ...


1

There are a few causes of this, the top ones are: storage subsystem, bad t-sql, missing/bad indexes. Paul Randall talks about this on his blog and at the sqlperformance blog. I like the scripts from Glenn Berry to find missing indexes and also SP_BlitzIndex. How do you know if you can trust the recommendations? Well I examine the existing indexes first ...


1

SQL Server supports piecemeal restores. From that link: Piecemeal restore works with all recovery models, but is more flexible for the full and bulk-logged models than for the simple model. So you should be OK with the FULL recovery model. I'd suggest you partition your tables. The log tables especially should be amenable to partitioning by write ...


1

You must pass the value from the first SP to the second via a variable local to the master SP. The bare bones of the code will look something like this: create master_sp .. declare @LocalVariable int; exec first_sp @sp_parameter = @LocalVariable OUTPUT; exec second_sp @sp_input = @LocalVariable;


1

I found the solution in case anyone else comes across this. The issue lied in the connection string property of the data source. For some reason (maybe standard, I am simply unaware of why) the connection string properties were empty after importing them to the new server. After setting them, they were able to hit the stored procedures and all reports ran ...


1

Dirty pages are written directly to the data files. There is no need to write them to the log because their modifications have been logged already. A dirty page never hits disk until all log records that record its modification have been written already. After the log has been hardened SQL Server is at leisure to write dirty data pages whenever it wants (or ...


1

Batching large data sets into smaller data sets, e.g. 100,000 rows or 1,000,000 rows will make the load run better than One Big Insert. But the same is true from SSIS, of course, as it batches inserts. The difference in time that you show in your example is fairly small. That does not give much hope for great speed improvements, but it does encourage you ...


1

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



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