Tag Info

Hot answers tagged

10

This is expected behaviour at the moment the function gets evaluated on the DELETE stream. So it actually behaves like this (pseudo code) DELETE k OUTPUT Deleted.name, ROW_NUMBER() OVER (ORDER BY Deleted.object_id) as r FROM ( SELECT k.*, ROW_NUMBER() OVER (ORDER BY object_id) r FROM #o k ) k WHERE r <> 1 --OUTPUT returns rows ...


8

Does the backup process USE only the memory that Has been assigned to the sql server (instance)? The memory that you assign with MIN and MAX memory is just for Buffer Pool and for SQL Server 2012 and up, the memory manager has changed. When a backup starts it creates a series of buffers, allocated from the memory outside the buffer pool. The ...


7

A non-clustered index scan may be chosen in this scenario: the optimizer determines that it is cheaper to scan all rows rather than perform seeks/range scans the non-clustered index is "skinnier" than the clustered index the non-clustered still covers the columns needed by the query (or it covers enough of them and a lookup for the remainder is still ...


5

Well, you shouldn't ever be using DBCC SHRINKDATABASE, IMHO - if you need to shrink files at all, you should think twice, maybe even three times, and even in the odd case where it really is warranted (hint: this should be rare), you should target each file individually using DBCC SHRINKFILE. Please read every single word on this page. Anyway, you can see ...


5

Yes, this is a limitation in T-SQL. There are several scenarios where you can't build up a string dynamically, but rather have to do so beforehand. For example: RAISERROR('String' + @variable,1,0); EXEC dbo.procedure @param = 'String' + @variable; There are other cases where it is valid, but usually just for assignment, not for passing or doing anything ...


5

You can't, ish, is the short answer. You have 3 ish components in a data flow task that can modify data. OLE DB/ADO.NET Destination, OLE DB Command and a Script Component. I'm ignoring Script Component for this answer as it's the swiss army knife of components. If you want to do something badly enough, the Script Component will allow you to. Whether ...


5

A few questions. 20 million rows isn't necessarily that large. What process are you using to perform your updates and deletes right now? Is the dimension a CLUSTERED COLUMNSTORE INDEX, CLUSTERED INDEX or HEAP? Are you saying that there is movement whilst you update and delete this table or did you just see movement when you changed the table from ...


5

Just a word about nested CTEs, they get evaluated each time they are referenced. To put it another way, they do not behave like temp tables, the data is not cached in some special way. So for a CTE referenced multiple times as yours are ( ie once in the UNION, once in the RunningTotal subquery ), there can be a performance penalty. I set up a simple rig ...


5

First thing never set Max Number of Concurrent Connections on server properties page to 1, as this option can even stop administrators to login but still in any case Dedicated Administrative Connection can have access. But if you have changed Max Number of Concurrent Connections value to 1 and now unable to login 1-Restart services using SQL Server ...


4

Size is one consideration. An int can hold up to -2,147,483,648 in four bytes. A char will need 11 bytes to hold the same value. There are built-in functions to manipulate the various data types. DATEADD() and DATEDIFF() are two examples. This will not be possible with date-stored-as-text. Constantly CASTing back and forth will not make for efficient ...


4

Your colleague is correct that it is easier to simply not think about it and just store everything as a varchar. But this comes at a large cost in terms of space requirements, performance, flexibility in querying data, and most importantly, lack of data integrity. This is not just a one-time cost; it is paid repeatedly over the lifecycle of the ...


4

This happens because the msdb log file name has been changed from the default which is "MSDBLog". In my case, it was called "msdb_log". The name "MSDBLog" is hard-coded into the upgrade script so it fails if the name has been changed. To fix the problem, we need to rename the log file to 'MSDBLog'. We have to start SQL with trace flag 902 to stop it from ...


4

There are a couple of possibilities. First if the orphaned user is a windows login/group (type U or G) then no problem. -- If the login doesn't currently exist on the server CREATE LOGIN [Windows Account] FROM WINDOWS; -- Fix the user USE dbname ALTER USER [user name] WITH LOGIN = [Windows Account]; If it's a "SQL Login" (type S) and the login exists ...


3

Ok, so when you ran SELECT * FROM sys.columns WHERE object_id = object_id('tb1') for each of the tables you found that the collation was not the same as the database default. At this point you have two options. Change the collation in the query All you have to do here is use the COLLATE phrase after the column that needs it. select distinct Number, ...


3

There are several situations in which it's better to represent numbers using some kind of numeric data-type. It's a little more efficient, but that's just the beginning. You get support for built-in arithmetic using SQL operators without performing type conversions at run time. Not only do type conversions slow things down, but they can result in numerous ...


3

To answer more concisely than the existing answers: REORGANIZE produces the same amount of log with SIMPLE and FULL. It's just that SIMPLE almost immediately makes that space available again. Except if something is preventing log truncation such as an open transaction (unrelated to the session the REORGANIZE runs in).


3

An ORM requires information about the first write (SCOPE_IDENTITY or such) to complete the second write. This means 2 (with an OUTPUT clause) or 3 database (with SELECT SCOPE_IDENTITY) calls in general in a client side transaction. No amount of tinkering with isolation levels will eliminate these 2 or 3 calls. If you want more performance, then the best ...


3

You have two problems. First you need commas between the CTEs. Second you can only have one ORDER BY for a group of UNIONed statements. Just use one ORDER BY at the very end. WITH [ProgressInvoicesPeriodCost] AS ( SELECT ProgressInvoices.ProjectId , ProgressInvoices.PeriodId , SUM(ProgressInvoiceDetails.TotalThisInvoice) AS ...


3

It could also be that you applied the update to SQL Server but didn't apply it to other services (SSAS, SSRS, etc.) or to other components (like Management Tools). Run SP2 again (or SP3!) and see which components it finds eligible for an update - I bet there is at least one.


3

update a set a.CHK = 1 from tableA a join tableB b on b.IDA = a.IDA and b.IDB = a.IDB


2

Do you really need ROW_NUMBER and OUTPUT? How about a nice set-based DELETE, eg something like this: USE tempdb SET XACT_ABORT ON BEGIN TRAN SELECT * INTO #o FROM sys.objects -- Identify records to keep SELECT TOP 2 object_id INTO #keep FROM #o ORDER BY object_id -- Delete others DELETE o FROM #o o ...


2

No, the DMVs track aggregates, not individual executions, so you can't pinpoint things like that. Want to drill in, you'll need to capture queries using server-side trace*, extended events, management data warehouse or one of many 3rd party monitoring tools. The latter cost money but, IMHO, you get what you pay for. We have one I could recommend... * Note: ...


2

From SQL 2012 onwards, there is the WITH RESULT SETS option, which makes it easy to use the output from the OUTPUT clause with no sticky issues in SSIS not understanding the metadata for the resultset, eg you can use the output from an OUTPUT clause in SSIS Source components, eg OLE DB Source. I set up a simple example using a stored procedure with MERGE ...


2

If you are just doing some maintenance scripts (not stored procedures) then you can do this type of thing using SQLCMD Mode ( Query > SQLCMD Mode ), eg :setvar databaseName yourDbName EXEC msdb.dbo.sp_help_schedule @schedule_name = 'FullBackup_$(databaseName)' Variables have a batch scope and can even pass through to other scripts called with the :r ...


2

The link provided by Martin Smith to an existing SO question provided me with the answer: The main restrictions are that foreign key relationships must be based on a single key to contribute to the simplification process


2

Something has happened such that the source column doesn't exist any more and the mapping is broken. In the bottom drop down, "spaltzenzuodnungsoption..." there should be 3? options. You want the one that says delete. This will remove all the mappings so you can start over.


2

No need to reinvent the wheel, Adam Machanic has written a usefull stored procedure to replace SP_Who and SP_Who2. You can find it here for free. (http://sqlblog.com/blogs/adam_machanic/archive/2011/04/27/who-is-active-v11-00-a-month-of-activity-monitoring-part-27-of-30.aspx) This stored procedure has a column “Percent_Complete” which gives you how much ...


2

You are using SQL Authentication. This requires the use of a self signed SSL certificate which is provided by the SQL Server so that the client can encrypt the authentication packets of the connection process. My guess, no SSL breaks this process.


2

SQL Server does indeed like RAM and will take everything it is allowed to. For that reason, an upper limit of RAM should be assigned, leaving sufficient for the OS and anything else running on that server. It would seem your 2GB database would easily fit into your existing RAM, but again, it depends on what else is running on your server. In addition to ...


2

By index file, you mean you place indexes on a dedicated filegroup that maps to the file in question? Yes, it could indeed be smaller. Indexes can become fragmented over time (inserts and updates need to split pages when inserting into a range) causing free space on your pages over that defined by your fillfactor. If your fillfactor is say 80%, this is not ...



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