Hot answers tagged

12

This DMV only maintains statistics since the last SQL Server restart; the view gets wiped out completely and everything starts from scratch. More importantly, the rows in this view for any specific index are removed when that index is rebuilt (but not when it is reorganized). If you are performing regular index maintenance, it might be useful to look at the ...


10

Query that adds "is_primary_key" field for each column The sys.key_constraints and sys.index_columns catalog views will identify the PK columns. You can JOIN them together and then use that set as a derived table to LEFT JOIN to your main query which will allow for not filtering out columns that are not part of a PK. Also, you want to use [user_type_id] ...


10

I would suggest storing the number as a BIGINT, and simply displaying the number the way you want it. DECLARE @Num BIGINT; SET @Num = 2421402015; SELECT CONVERT(VARCHAR(11), REPLICATE('0', 11 - LEN(@Num))) + CONVERT(VARCHAR(11), @Num); As pointed out by Aaron Bertrand in his comment, it is better performance-wise to not calculate the length of the ...


8

While I agree that you're raising an error even if you have a PK violation (as the other answer suggests, you need BEGIN/END, because your IF conditional currently assumes that everything indented after it will be part of the IF logic), that doesn't really answer why you're getting -4 specifically. You don't have an explicit return value in the event that ...


8

To answer your question, why not just grow the file to 500 GB and then shrink it. By which I mean dont' generate 500GB of transactions first. --grow file... USE [master] ALTER DATABASE <DBName> MODIFY FILE (NAME=N'<logical filename>', SIZE=512000MB) GO --shrink file... USE [database name] DBCC SHRINKFILE(N'<logical filename>', [target ...


8

My problem is that my server is going over the set 12GB memory and if things like CLR/Full Text/Server exe files taking additional memory. What Denny Cherry wrote is true for SQL Server version from 2005 to 2008 r2. From SQL Server 2012 onward there was considerable change in SQL Server memory allocation. Memory for SQLCLR is allocated from max server ...


7

There's no exact way to do this. The setting you apply to max server memory only manages some of SQL Server's memory, it will still use other allocations depending on what you're doing. To limit this you can minimize your usage of things that use memory external to this (CLR, full text, etc), but you can't eliminate all of it. You can play with max server ...


7

You probably misunderstood the recovery model concept. From BOL Doc A recovery model is a database property that controls how transactions are logged, whether the transaction log requires (and allows) backing up, and what kinds of restore operations are available Recovery model controls the logging and there by how much data you can recover. Only ...


7

If you want the interestingData and timestamp from the same row (the most recent row that exceeds the threshold), and if you want to include all rows that exceed the threshold even if some rows for that ID don't meet the threshold, then: ;WITH x AS ( SELECT ID, interestingData, [timestamp], [count] = COUNT(1) OVER (PARTITION BY ID), rn = ...


6

Can I host the DLL on a different server so it will get run there? How would this work? If you want to run code on a remote server, you would still need code on the local server to be able to make that remote call. Although, you could set up an instance of Express Edition to host the SQLCLR code (it would need to be a Stored Procedure(s) and/or ...


6

Instead, try WHERE InsertedOn>=CAST(GETDATE() AS date) AND InsertedOn<DATEADD(day, 1, CAST(GETDATE() AS date)) This expression is sargable which is what you want for optimum performance. Like @Mikael indicates, you would do well to design one of your indexes so that InsertedOn is the first column, and that all the other columns used in the ...


6

You've addressed a boatload of items in your question. Thanks for being so thorough! Just a couple of things I notice off hand: How various hardware / load factors influence what should be done. Are you running a 24x7 instance? What is the load around the clock? I notice you have backup compression disabled; is that by design for the test, or ...


6

Recently I have faced with the same issue. I have googled for a while and found that this is a problem in the Microsoft products. I wrote the article according to this error message, so you can find more information there. So, to solve this issue and restore your database to point-in-time use T-SQL commands: Restore your last full backup RESTORE DATABASE ...


5

The way I understand your question is that you have an existing table with a column that has up until now been populated with manual values, and now you want to (1) make this column an IDENTITY column, and (2) make sure that the IDENTITY starts from the most recent value in the existing rows. First off, some test data to play with: CREATE TABLE ...


5

To solve this issue, you need to know how full and bulk-logged recovery model work. So, let' start from Full recovery model: In the full recovery model, full database backup does not truncate committed transactions until you make a transaction log backup. It allows creating full, differential and transaction log backups thus making point-in-time recovery ...


5

To demonstrate that what you're saying in your question is not true: DECLARE @x TABLE(y VARCHAR(11)); INSERT @x(y) VALUES('02421402015'); SELECT y FROM @x; Results: y ----------- 02421402015 You need to tell us more details about what is going on so we can understand your problem better. But most importantly, you need to consider separating storage ...


5

Here's a stab at an algorithm. It's not perfect, and depending on how much time you want to spend refining it, there are probably some further small gains to be made. Let's assume you have a table of tasks to be performed by four queues. You know the amount of work associated with performing each task, and you want all four queues to get an almost equal ...


5

The number one reason is operator error. The 'whops, I dropped the wrong table' moment. Next are catastrophic hardware failures (lost, unrecoverable, disk). Non-disk failures are actually less problematic because the data can usually be retrieved by attaching the (still working) disks to a different host. Disk I/O errors (corruption of media at rest) used ...


5

For SQL Server 2014 and later my recommendation is rather radical: switch to a clustered columnstore index. 1000 records/min is well within the range of columnstore bulk load capabilities, on even modest hardware. See Clustered Columnstore Index: Data Load Optimizations – Minimal Logging and SQL Server clustered columnstore Tuple Mover. The query performance ...


4

Not that I know of. The OUTPUT.. INTO clause requires an existing table. On a related note Using SELECT.. INTO as opposed to INSERT INTO.. SELECT or OUTPUT.. INTO can have negative side-effects depending the situation. Some examples why I think you should create the table first: If you're creating a table inside of a transaction, SQL Server may place a ...


4

The behaviors you're curious about are explained in the documentation: When an existing identity column is selected into a new table, the new column inherits the IDENTITY property, unless one of the following conditions is true: ...- The identity column is part of an expression. ...If any one of these conditions is true, the column is created NOT ...


4

According to the error message, your database contains Enterprise Edition features in the table Country. First of all, check the following on the original (old) server: SELECT SERVERPROPERTY('Edition'); That should tell you if you're running on Standard, Enterprise or Developer Edition. Developer Edition supports all of the Enterprise features (with ...


4

Defensive programing requires you to write code that handles as many known states as possible, thereby reducing the possibility of bugs. Checking XACT_STATE() to determine if a rollback can be executed is simply a good practice. Blindly attempting a rollback means you may inadvertently cause an error inside your TRY...CATCH. One way a rollback might fail ...


4

I think you need: SELECT f.Id AS FileId, f.Name FROM Filenodes AS f WHERE EXISTS ( SELECT * FROM PersonFilenodes AS pf WHERE pf.PersonId = @personId AND ( pf.FileId = f.Id OR pf.FileId.IsDescendantOf(f.id) = 1 ) ) ; The inner query is a correlated subquery that takes two parameters: ...


4

I think it is important to make transaction log backups, but everything depends on the individual case. For example: Your database grows slowly and changes occur very rarely, so, in this case, you can use simple recovery model, and backup plan like you described. You know that simple recovery model allows full and differential database backups ...


4

I would say that there isn't necessarily a Best Practice, this is more of a matter of personal preference or your businesses operating procedures. I've had separate folders per database, then sub folders for FULL, DIFF, LOG at some jobs, others everything goes to a single folder and the backup name is some combination of database name, backup type, and ...


4

You can specify the first and/or last trigger to fire using sys.sp_settriggerorder. If you have two or three triggers, this allows you to completely control the execution order of all triggers. If you have more than three, you can make one first, another fire last, but the order of execution for the triggers in the middle will be non-deterministic. In your ...


4

Ques.1: First remember that primary keys are not always clustered keys which is what I think you really want. However the answer is pretty much the same either way. You need to look at sys.indexes to see the is_primary column. This will tell you that the index is they primary key. Then you need to look at sys.index_columns to see the actual columns in ...


4

You don't have a BEGIN/END for the IF statement so it is executing the RAISEERROR regardless of the ERROR_NUMBER. I've added the block around the SELECT and the RAISEERROR and added a RETURN (0); in an ELSE block. ALTER PROCEDURE [dbo].[pSetUserItemLike] @UserID int ,@ItemID int AS BEGIN SET NOCOUNT ON; -- for exception handling DECLARE ...


3

I was not able to reproduce your error (although I only tested with one table in the mix). An OLE DB Destination has the ability to disable trigger and other check constraints when it loads but the OLE DB Command does not provide a similar facility. Set up I created a simplistic table --- a key, a value and an empty field that will be populated, via ...



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