670

Contents Caveat This answer discusses "classic" table variables introduced in SQL Server 2000. SQL Server 2014 in memory OLTP introduces Memory-Optimized Table Types. Table variable instances of those are different in many respects to the ones discussed below! (more details). Storage Location No difference. Both are stored in tempdb. I've seen it ...


324

A Shorter Answer: You probably either have a long running transaction running (Index maintenance? Big batch delete or update?) or you are in the "default" (more below on what is meant by default) recovery mode of Full and have not taken a log backup (or aren't taking them frequently enough). If it is a recovery model issue, the simple answer could be ...


316

I experienced this problem as well with SQL Server 2017 Developer and it appears to be just bad planning on the part of the SQL Server installation package people. The problem is that Visual Studio 2017 installs the Microsoft Visual C++ 2017 Redistributable (x86) and (x64) and the SQL Server installation tries to install the Microsoft Visual C++ 2015 ...


216

I once had a table and it was shiny and beautiful. It held all the financial transactions for an organization. And then we started loading data into it. In the current month, they can state and restate values as often as they want. In the final 10 days of a month, they'd restate numbers -> run ETL processing -> review reports several times a day. Once the ...


201

This is pretty broad, but I'll give you as general an answer as I can. CTEs... Are unindexable (but can use existing indexes on referenced objects) Cannot have constraints Are essentially disposable VIEWs Persist only until the next query is run Can be recursive Do not have dedicated stats (rely on stats on the underlying objects) #Temp Tables... Are ...


189

The question is not 'when should the PK be NC', but instead you should ask 'what is the proper key for the clustered index'? And the answer really depends on how do you query the data. The clustered index has an advantage over all other indexes: since it always includes all columns, is always covering. Therefore queries that can leverage the clustered index ...


156

You don't have to stop the SQL Server service to move database files, but you do have to take the specific database offline. This is because you can't move files while they're being accessed and taking the database offline stops the files from being used by the SQL Server application. The process to move them is fairly simple. Detach/Attach was already ...


152

It really depends on whether the developer has any support responsibilities. If they are on the hook for third line support then they will probably need to look at the production database to do this. Generally it's a bad idea to do anything on a production server unless it's really necessary to do it there. For most development purposes, mirrors or ...


145

During parsing, SQL Server calls sqllang!DecodeCompOp to determine the type of comparison operator present: This occurs well before anything in the optimizer gets involved. From Comparison Operators (Transact-SQL) Tracing the code using a debugger and public symbols*, sqllang!DecodeCompOp returns a value in register eax** as follows: ╔════╦══════╗...


135

No. Developers should not have access to production database systems for the following reasons: Availability and Performance: Having read-only rights to a database is not harmless. A poorly written query can: Lock tables, blocking other critical processes. Trash your data cache, forcing other processes to re-read data from disk. Tax your storage layer, ...


133

Brent here (the guy you're referring to in the question). The reason I tell you not to add tbl to the front of your table names is the same reason I'd say not to add child to the front of your child's name. You don't call them childJohn and childJane. Not only does it not add any value, they may not be a child later in life - and your objects may later ...


130

No. TRUNCATE and DROP are almost identical in behavior and speed, so doing a TRUNCATE right before a DROP is simply unnecessary. Note: I wrote this answer from a SQL Server perspective and assumed it would apply equally to Sybase. It appears that this is not entirely the case. Note: When I first posted this answer, there were several other highly rated ...


129

This is likely caused by the restore script adding the WITH NORECOVERY parameter, to make the database ready for a transaction log apply after the restore. The database is now waiting for the latest transaction log file. You can either: Apply the latest transaction log, using RESTORE LOG database_name FROM backup_device WITH RECOVERY; ... or Restore the ...


125

I just overlaid the Microsoft.VisualStudio.Shell.Interop.8.0.dll in C:\Program Files (x86)\Microsoft SQL Server Management Studio 18\Common7\IDE\PublicAssemblies with the copy from ..\PrivateAssemblies\Interop and the IDE opens. (Thanks to Mitch for discovering the offending DLL name.) This looks like a popular Q & A today, so I'm glad to know this ...


118

This is a very subjective argument, but here is my take: the tbl prefix is useless. How many scenarios are you looking at code and you can't tell if something's a table or something else? What value does tbl add except that when you look at a list of tables in Object Explorer, you have to do more work to find the one(s) you're looking for? Some people ...


118

This will list all "most recent" restores for each database on your server: WITH LastRestores AS ( SELECT DatabaseName = [d].[name] , [d].[create_date] , [d].[compatibility_level] , [d].[collation_name] , r.*, RowNum = ROW_NUMBER() OVER (PARTITION BY d.Name ORDER BY r.[restore_date] DESC) FROM master.sys.databases d LEFT OUTER JOIN ...


116

That is expected and documented behavior: Dropping a database deletes the database from an instance of SQL Server and deletes the physical disk files used by the database. If the database or any one of its files is offline when it is dropped, the disk files are not deleted. These files can be deleted manually by using Windows Explorer. To remove a ...


115

Since I'm not really satisfied with any of the answers over on Stack Overflow, including the most heavily up-voted suggestion, and because there are a few things I'd like to address that Mike's answer does not, I thought I would provide my input here too. I placed a copy of this answer there as well. Making a log file smaller should really be reserved for ...


105

There is another way, which I now use in preference to the runas /netonly method. You can add the credentials to your profile in Windows using the Credential Manager found in the Windows control panel. Open Credential Manager Click "Add A Windows Credential" Populate the "internet or network address" field with the name and port number of the SQL instance ...


103

The MERGE statement has a complex syntax and an even more complex implementation, but essentially the idea is to join two tables, filter down to rows that need to be changed (inserted, updated, or deleted), and then to perform the requested changes. Given the following sample data: DECLARE @CategoryItem AS TABLE ( CategoryId integer NOT NULL, ...


102

In addition to the points in other answers, here are some key differences between the two. Note: The error messages are from SQL Server 2012. Errors Violation of a unique constraint returns error 2627. Msg 2627, Level 14, State 1, Line 1 Violation of UNIQUE KEY constraint 'P1U_pk'. Cannot insert duplicate key in object 'dbo.P1U'. The duplicate key value ...


101

Is a bad practice to create a transaction always? It depends on what context you are talking here. If it is an update, then I would highly recommend using TRANSACTIONS explicitly. If it is a SELECT then NO (explicitly). But wait there is more to understand first : Everything in sql server is contained in a transaction. When the session option ...


96

Nope. Here's a simple test: SELECT COALESCE(1, (SELECT 1/0)) -- runs fine SELECT COALESCE(NULL, (SELECT 1/0)) -- throws error If the second condition is evaluated, an exception is thrown for divide-by-zero. Per the MSDN Documentation this is related to how COALESCE is viewed by the interpreter - it's just an easy way to write a CASE statement. CASE is ...


92

Really Short Answer - In Place is okay. You can review your configuration afterwards and implement the best practices for SQL Server 2012. A Longer Answer on SQL Server Upgrades/Migrations So this is an opinion thing and there isn't a necessarily wrong or right answer but I prefer migration style upgrades over in-place for a lot of reasons. That being said ...


88

Dear [your name here]! Oh no, I'm sorry to hear that! Let's start with some basics to get you fixed up in a jiffy. The thing you're running into is called Parameter Sniffing It's a way out wiggy weird problem. The name rolls right off the tongue. Like the German word for squirrel. And it's usually your friend. When a query hits your server, a plan has ...


84

You need CROSS APPLY not join. The definition of table expressions involved in joins must be stable. I.e. They can't be correlated such that the table expression means something different dependant on the value of a row in another table. select f.ID, f.Desc, u.Field1, u.Field2 from Foo f Cross apply ut_FooFunc(f.ID, 1) u where f.SomeCriterion = ...


82

I've seen many uses when you need to project 'missing data'. Eg. you have a time series (an access log for instance) and you want to show the number of hits per day for past 30 days (think analytics dashboard). If you do a select count(...) from ... group by day you will get the count for every day, but the result will only have a row for each day you ...


80

I've worked on SQL Servers with 8 to 10 thousand databases on a single instance. It's not pretty. Restarting the server can take as long as an hour or more. Think about the recovery process for 10,000 databases. You cannot use SQL Server Management Studio to reliably locate a database in the Object Explorer. Backups are a nightmare, since for ...


79

Yes. Failing to specify WITH SCHEMABINDING means SQL Server skips the detailed checks it normally makes on the function body. It simply marks the function as accessing data (as mentioned in the link given in the question). This is a performance optimization. If it did not make this assumption, SQL Server would have to perform the detailed checks on every ...


78

Performance would be a BIG reason. Just because they can't change the data doesn't mean they can't affect the server. A poorly written query could bring the production environment to its knees, and potentially cause other issues (like tempdb overflows): SELECT * FROM BigTable A, OtherBigTable B ORDER BY Somecolumn That's a recipe for disaster. Notice ...


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