We’re rewarding the question askers & reputations are being recalculated! Read more.

Questions tagged [sql-server-2012]

SQL Server 2012 (major build version 11.00.xxxx). Please also tag sql-server.

Filter by
Sorted by
Tagged with
15
votes
3answers
2k views

Optimizing a CTE hierarchy

Update below I have a table of accounts with a typical acct/parent account architecture to represent a hierarchy of accounts (SQL Server 2012). I created a VIEW using a CTE to hash out the hierarchy, ...
15
votes
2answers
10k views

What effect will reducing the size of a varchar column have on the database file?

We have a number of tables in our database that have VARCHAR(MAX) columns where a VARCHAR(500) (or something much smaller than max) will suffice. Naturally I want to clean these up, and get the sizes ...
15
votes
2answers
441 views

Index on a persisted computed column not seekable

I have table, called Address, that has a persisted computed column called Hashkey. The column is deterministic but not precise. It has a unique index on it that is not seekable. If I run this query, ...
14
votes
3answers
14k views

Can I connect to a SQL Server 2000 instance from SQL Server Management Studio 2012?

I would like to update my development machine to SQL Server 2012 but I still manage some (very old) SQL Server 2000 machines. Wil my 2012 SSMS be able to connect to those SQL Server 2000 machines? ...
14
votes
2answers
3k views

What is the logic behind ISNUMERIC for certain special characters?

The ISNUMERIC function has some unexpected behavior. The MSDN documentation says: ISNUMERIC returns 1 when the input expression evaluates to a valid numeric data type; otherwise it returns 0. Valid ...
14
votes
1answer
30k views

Running total to the previous row

I need some help with windowing functions. I know you can calculate the sum within a window and the running total within a window. But is it possible to calculate the previous running total i.e. the ...
14
votes
1answer
2k views

Forcing an index spool

I know its something that should be avoided for performance reasons, but am trying to show a condition where it appears as a demo on how to make sure it does not appear. However, I end up with a ...
14
votes
1answer
11k views

Troubleshooting SOS_SCHEDULER_YIELD wait

Running our corporate ERP (Dynamics AX 2012), I noticed our production environment seemed much slower than our development systems. After performing the same activities in both the development and ...
14
votes
1answer
29k views

Benefits of using WITH TABLOCK on an INSERT

Under some circumstances, doing an INSERT INTO <tablename> (WITH TABLOCK) will be faster due to minimal logging. Those circumstances include having the database in the BULK_LOGGED recovery model....
14
votes
1answer
32k views

What does role db_owner allow

I have been trying to troubleshoot a login that is not able to view certain tables in a SQL Server 2012 database. In doing so I find I don't quite understand what membership in the db_owner role ...
14
votes
3answers
4k views

SQL Server query slow when paginated

I am seeing some strange behaviour with the following T-SQL query in SQL Server 2012: SELECT Id FROM dbo.Person WHERE CONTAINS(Name, '"John" AND "Smith"') ORDER BY Name Executing this query alone ...
14
votes
1answer
19k views

Comparing two queries in SQL Server 2012

I am comparing two queries in SQL Server 2012. The goal is to use all of the pertinent information available from the query optimizer when choosing the best query. Both queries produce the same ...
14
votes
2answers
954 views

In Search of FILESTREAM Insider Information

When the FILESTREAM feature is activated on Microsoft SQL Server 2012 then SQL Server will create a "hidden" share on the system. The share is defined as follows: Sharename FILESTREAM_SHARE ...
14
votes
5answers
98k views

SQL Server Isnull Returning 1900-01-01 when field is null

The following piece of code is returning 1900-01-01 when the DOB field is null. I wanted (and expected) it to return an empty string ('') but it's not. How should I proceed to get my desired results? ...
14
votes
1answer
4k views

Why would you use a managed service account rather than a virtual account in SQL Server 2012?

In SQL Server 2012, service accounts are created as virtual accounts (VAs), as described here, as opposed to managed service accounts (MSAs). The important differences I can see for these, based on ...
14
votes
2answers
1k views

SQL Server 2016 vs 2012 insert performance

I have two SQL Server instances on the same server: Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64) Standard Edition (64-bit) Microsoft SQL Server 2016 (SP1-CU5) (KB4040714) - 13.0.4451.0 (X64) ...
13
votes
5answers
58k views

Detecting changes in a SQL Server table

In my application, with a DB running on SQL Server 2012, I've got a job (scheduled task) that periodically executes an expensive query and writes the results to a table that can later be queried by ...
13
votes
3answers
19k views

Eliminate Key Lookup (Clustered) operator that slows down performance

How can I eliminate a Key Lookup (Clustered) operator in my execution plan? Table tblQuotes already has a clustered index (on QuoteID) and 27 nonclustered indexes, so I am trying not to create any ...
13
votes
3answers
8k views

Is it possible to estabilish Dedicated Administrator Connection (DAC) using SSMS?

Is it possible to connect to the DAC using SSMS? If I understand correctly, it should be possible by using "admin:" prefix before the server name, but I receive the following message: Dedicated ...
13
votes
4answers
297 views

NULL value self comparison in a table

I am always puzzled regarding some mysterious t-sql behavior, like the following -- Create table t and insert values. use tempdb CREATE TABLE dbo.t (a INT NULL); -- insert 3 values INSERT INTO ...
13
votes
1answer
14k views

Why do we use FILE = 1 in scripts for restoring a database

Recently when I came across the script for restoring a database, I got a doubt on why we have to use "FILE = 1"? Can't we restore a database without that statement!? Basically, what it is used for?
13
votes
1answer
10k views

Move Primary Key to Filegroup (SQL Server 2012)

How can I move a clustered primary key to a new filegroup? I already have found a possible "algorithm" but it is horribly inefficient: Drop non-clustered indexed (requires them to be resorted and ...
13
votes
3answers
3k views

Is total_elapsed_time in DMV sys.dm_exec_requests completely inaccurate?

I am running SQL Server 2012 and am trying to put some queries together for monitoring using the DMVs. However, when looking at the total_elapsed_time field in the sys.dm_exec_requests DMV, the ...
13
votes
3answers
4k views

Backup internals - What happens when a backup job is running - in terms of locking and performance overhead in SQL Server?

For MySQL I know the database is backed up table by table in SQL Statements, this results in locking and if you update columns while backing up you might end up in integrity problems. To my ...
13
votes
3answers
15k views

SQL Server auto-trim of varchar value in equal comparison but not like comparison

I came across some interesting behaviour on SQL Server (observed in 2005 and 2012) today that I was hoping someone could explain. A query doing a comparison using = on an NVARCHAR field ignored the ...
13
votes
2answers
26k views

Resetting a SQL Server 2012 sequence

I'm in the process of testing and populating a specific table that leverages the SEQUENCE object. In this process I'm testing populating the table with tens of thousands of insert lines (as I'm ...
13
votes
2answers
23k views

Fill in missing dates with data value from previous populated date for group

Picture help desk tickets that gets transfered between departments. We want to know what the department is at the end of the day for each ticket for each day that the ticket is open. The table ...
13
votes
1answer
21k views

Restoring a SQL Server 2012 database in high availability

I have a database which is in always-on high availability mode synchronized with another database on a different instance. How can I restore from a .bak file into the primary database using T-SQL? I'...
13
votes
2answers
109k views

Installing SQL Server 2012 Express fails with error “ Could not find the Database Engine startup handle.”

I'm attempting to install SQL Server Express 2012 w/ SP1 x64 on Windows 7 SP1 x64. This machine has previously had SQL Server Express 2012 installed. This instance was upgraded to SQL Server 2012 ...
13
votes
3answers
12k views

Can I create a user defined table type and use it in the same transaction?

When I execute the following (in management studio, GO will separate the commands into batches) use tempdb begin tran go CREATE TYPE dbo.IntIntSet AS TABLE( Value0 Int NOT NULL, Value1 Int ...
13
votes
3answers
17k views

How best to maintain SQL log file sizes

I'm somewhat of a new DBA and I'm managing a SQL Server 2012 instance that has a fair amount of activity. I'm running in Full Recovery mode because we need point in time recovery. Right now, I'm ...
13
votes
2answers
2k views

Are the cost percentages in this SQL Server plan over 100% for a valid reason?

I'm looking through the plan cache, looking for low-hanging optimization fruit and came across this snippet: Why are many of the costs listed above 100% ? Shouldn't that be impossible?
13
votes
2answers
11k views

Relating ExecutionInstanceGUID to the SSISDB

The 2012 release of SQL Server Integration Services, SSIS, has delivered an SSISDB catalog which tracks the operations of packages (among other things). The default package execution for solutions ...
13
votes
1answer
4k views

How accurate is the sys.partition.rows column?

The system view sys.partitions has a column "rows" that is the total number of rows in a given partition. For a table that is not partitioned (or only has one partition depending on how you look at ...
13
votes
1answer
1k views

Workspace Memory Internals

Per my reading books on SQL Server 2008 Internals and Troubleshooting (borrowed from local library in Illinois) by Christian Bolton, Brent Ozar etc. I am trying to seek understanding and confirmation ...
13
votes
1answer
532 views

Why is the secondary selective index not used when the where clause filters on `value()`?

Setup: create table dbo.T ( ID int identity primary key, XMLDoc xml not null ); insert into dbo.T(XMLDoc) select ( select N.Number for xml path(''), type ) from ( ...
13
votes
1answer
605 views

Clustered index not used in a delete statement

I have a SQL Server table defined as follow CREATE TABLE [dbo].[Production_Detail] ( [Id] [bigint] NOT NULL DEFAULT (NEXT VALUE FOR [dbo].[Production_Detail_Seq]), [Meta_Data_ID] INT NOT NULL ...
13
votes
1answer
847 views

Why does SQL Server refuse to update these statistics with anything but fullscan?

I noticed a relatively long running (20 min+) auto update statistics operation in a daily datawarehouse build. The table involved is CREATE TABLE [dbo].[factWebAnalytics]( [WebAnalyticsId] [...
13
votes
1answer
6k views

Using SQL Server 2012 Standard with Bitlocker

We're trying to use Bitlocker to secure an SQL Server 2012 database. We don't have any issue getting bitlocker to work... the issue we have is that once the drive is locked, SQL Server can no longer ...
13
votes
1answer
4k views

Is it possible to record incoming parameter values in a procedure call while tracing in SQL Server Profiler?

Using SQL Server Profiler (I'm on SQL Server 2012), I'm trying to generate a useful trace that shows the parameter values, not just the SQL with variable names. The stored procedure walks through a ...
13
votes
2answers
993 views

Large Variation in Bulk Insert time

So I have a simple Bulk Insert process to take data from our staging table and move it into our datamart. The process is a simple data flow task with default settings for "Rows per batch" and the ...
12
votes
3answers
1k views

Determine whether mixed mode auth is enabled without logging in?

Is it possible in SQL Server to determine whether mixed mode authentication is enabled without logging on to SQL Server?
12
votes
5answers
3k views

How to hide/disable tables without dropping them to check redundancy?

I have to maintain and extend an old legacy system which contains webservice methods and database tables that are no longer used. Since I’m not entirely sure that the tables are really redundant, I’m ...
12
votes
4answers
15k views

SQL Server 2012 is putting [brackets] around Table and Column Names

When you drag the Columns folder to editor Window, the full list of Columns are added without Brackets. If you drag columns individually they have Brackets. Is there anyway to turn that off? I'm ...
12
votes
5answers
3k views

How do I limit a SQL stored procedure to be run by one person at a time?

I have a stored procedure that basically selects values from one table and inserts them to another, a sort of archiving. I want to avoid multiple people from doing that at the same time. While this ...
12
votes
3answers
4k views

What is the effect of having an opened transaction in MSSQL for too long?

I'm just wondering what happens if you begin a transaction in a DB and forgot to commit or rollback it. Will the server be down? Lets say you left it for 3 days. There are also users who are using it ...
12
votes
4answers
86k views

CREATE FILE encountered operating system error 5 (Access is denied.)

I'm trying to execute the following script in SQL Server Management Studio: USE [master] GO CREATE DATABASE [test1] ON PRIMARY ( NAME = N'test1', FILENAME = N'C:\Program Files\Microsoft SQL ...
12
votes
3answers
7k views

How can I reliably determine if a given SQL Server instance has the “Lock Pages in Memory” right?

The "Lock Pages in Memory" right can be granted to the service account used by SQL Server. This allows SQL Server to prevent memory being paged to disk. I've noticed several of our SQL Server ...
12
votes
5answers
8k views

View locks acquired during query execution (SQL Server)

Query execution plan does not show the locking details by default, is it possible to view the locks, along with the type, acquired during the execution of a query?
12
votes
4answers
23k views

Logins aren't syncing across Availability Groups

We have 2 servers in an AlwaysOn group. While the user accounts within each synchronized database exist on both servers, the database instance level logins only exist on one of the servers. Ie ...