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
12
votes
1answer
2k views

How does SQL Server's optimizer estimate the number of rows in a joined table?

I am running this query in the AdventureWorks2012 database: SELECT s.SalesOrderID, d.CarrierTrackingNumber, d.ProductID, d.OrderQty FROM Sales.SalesOrderHeader s JOIN Sales....
12
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 ...
12
votes
2answers
612 views

During a log backup is the data backed up to the start or end of the operation?

Say I am running a log backup, and that log backup takes 10 minutes to complete. During that 10 minute window, further transactions are run. Given the below example, which transactions does the log ...
12
votes
2answers
9k views

What does a “Buffer cache hit ratio” of 9990 mean?

I got this query from a blog post: SELECT object_name, counter_name, cntr_value FROM sys.dm_os_performance_counters WHERE [object_name] LIKE '%Buffer Manager%' AND [counter_name] = 'Buffer cache hit ...
12
votes
1answer
35k views

How to transfer the ownership of the dbo schema in SQL Server 2012

I've accidentally given a user the ownership of the db_owner schema (using the check box in the UI as below) and now I cannot: Transfer ownership to another user Drop the user from DB (though I can ...
12
votes
3answers
4k views

Why would the Parallelism (Repartition Streams) Operator Reduce Row Estimates to 1?

I'm using SQL Server 2012 Enterprise. I've come across a SQL Plan that is exhibiting some behavior I don't find entirely intuitive. After a heavy Parallel Index Scan operation, a Parallelism (...
12
votes
2answers
4k views

Will a Full-Text Catalog with Track changes: AUTO automatically update if Change Tracking for that table is not enabled?

I have this database table that is suppose to keep the full-text index up-to-date. However I am not seeing it happening at all (no error in the log because the last log I see was when I triggered it ...
12
votes
3answers
10k views

sqlpackage.exe ignoring BlockOnPossibleDataLoss?

I have a dacpac that has set the BlockOnPossibleDataLoss set to false, however the deployment is blocked when I run it with sqlpackage.exe, telling me "The column [a] is being dropped, data loss could ...
12
votes
1answer
938 views

Does CREATE TABLE SomeSchema.#TempTableName have a bug?

Simple test-bed: USE tempdb; GO /* This DROP TABLE should not be necessary, since the DROP SCHEMA should drop the table if it is contained within the schema, as I'd expect it to be. */ ...
12
votes
2answers
4k views

SSIS 2012 Create environment variable fails

I'm working a script to port an Environment from one server to another. I am running into an issue calling catalog.create_environment_variable wherein I get the error "The data type of the input value ...
12
votes
1answer
7k views

Upgrade SQL Server 2005 to SQL Server 2012

I have a test environment set up with the following: Virtual Machine (Hyper-V) Windows Server 2008 R2 SP1(x64-bit) Windows SQL Server 2005 Developer Edition SP4 (x64-bit)(default instance name) 1 ...
12
votes
1answer
3k views

SQL Server 2012 Page Life Expectancy resets to 0 after about 50 days

I've noticed an odd behavior on a 2-server HA cluster and I was hoping someone could confirm my suspicion, or maybe offer some other explanation... Here is my setup: A 2-server SQL 2012 SP1 ...
12
votes
2answers
11k views

SQL Server database design for “archived but available” data

We have this large database (>1TB) that we intend to "shrink". The database revolves around one main entity, let's call it "Visit". For discussion, let's say it is a database for a medical practice. ...
12
votes
1answer
174 views

Is this a symptom of an overloaded server?

I've been trying to diagnose slow-downs in an application. For this I've logged the SQL Server extended events. For this question i'm looking at one particular stored procedure. But there are a ...
12
votes
1answer
2k views

What is the cause of this problem with CONVERT()?

Consider the following two statements: PRINT CONVERT(NUMERIC(38, 0), 0x0100000001, 0); PRINT CONVERT(NUMERIC(38, 0), 0x0100010001, 0); Both statements return -1; isn't that incorrect since the ...
12
votes
1answer
2k views

SQL Server 2012 Standard server with 50 instances won't uninstall instances

I suspect this is an issue because we hit the 50 instance max limit of SQL Server. At the limit it won't install more instances, naturally. However, it seems it won't UNinstall them either. That's ...
11
votes
5answers
2k views

How to have more than 100 entries in case statement as a variable

I wrote a case statement with > 100 choices where I am using the same statement in 4 places in a simple query. The same query twice with a union between them but also is doing a count and therefore ...
11
votes
6answers
19k views

Migrate from SQL Server 2000 to 2012 without a 2005 or 2008 instance

I have come across three old databases sitting on SQL Server 2000 which I need to move to 2012. I believe the standard approach is to restore into a 2005 or 2008 instance, update, re-export and ...
11
votes
1answer
768 views

SQL Server stored procedure naming

We have started naming our stored procedures as [16_TestStoredProc]. Are there any implications naming a stored procedure like this? I am not going to explain why we are doing this. It is not that I ...
11
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 ...
11
votes
8answers
49k views

Cannot start SqlLocalDB instance with my Windows account

I'm the administrator and simply run the command: sqllocaldb start v11.0 Result: Start of LocalDB instance "v11.0" failed because of the following error: Error occurred during LocalDB instance ...
11
votes
3answers
338 views

Why would optimiser choose Clustered Index + Sort instead of Non-Clustered Index?

Given the next example: IF OBJECT_ID('dbo.my_table') IS NOT NULL DROP TABLE [dbo].[my_table]; GO CREATE TABLE [dbo].[my_table] ( [id] int IDENTITY (1,1) NOT NULL PRIMARY KEY, [foo] ...
11
votes
2answers
3k views

MAXDOP = 1, Query Hints and Cost Threshold For Parallelism

If an instance has MAXDOP set at 1 and query hints are used to allow specific queries to go parallel, is the Cost Threshold For Parallelism value still used by SQL to decide whether or not to actually ...
11
votes
2answers
56k views

Transaction in a stored procedure

I need to perform an UPDATE and an INSERT in a single transaction. That code works fine on its own, but I'd like to be able to call it easily and pass in the required parameters. When I try to nest ...
11
votes
5answers
23k views

Freeing Unused Space SQL Server Table

I have a table in SQL Server 2012 Express with a lot of unused space. I need to free up space in the database. | NAME | ROWS | RESERVED | DATA | INDEX_SIZE | UNUSED | |----...
11
votes
4answers
2k views

What is the maximum number of local variables that can participate in SET operation?

I have a stored procedure that contains business logic. Inside it I have around 1609 variables (don't ask me why, this is how the engine works). I try to SET a variable to the concatenated value of ...
11
votes
3answers
15k views

Passing info on who deleted record onto a Delete trigger

In setting up an audit trail I have no problem tracking who is updating or inserting records in a table, however, tracking who deletes records seems more problematic. I can track Inserts/Updates by ...
11
votes
3answers
809 views

Is it possible to choose RAISERROR or THROW depending on SQL Server version?

Here's my code right now: BEGIN TRY INSERT INTO TABLE (F1,F2,F3) VALUES ('1','2','3') END TRY BEGIN CATCH ;THROW END CATCH Works great, unless it's run on a machine with SQL 2008. I'd like to have ...
11
votes
1answer
1k views

CHECK constraint to enforce ###.###.#### pattern match

I would like to have a check constraint that enforces this regex pattern for a multi-part code: ^\d{3}\.\d{3}\.\d{4}$ ...three numbers, a period, three numbers, a period, four numbers. Do I need to ...
11
votes
1answer
13k views

How to allow to execute stored procedures but not writes?

I would like to set up a "reading" user on my SQL Server 2012 instance. I would like to allow him the right to execute any stored procedure, or function, or direct SQL statement, which retrieves data ...
11
votes
5answers
30k views

SQL Server 2012 Can't log in with machine account

We're migrating an IIS/SQL Server site from 2008 to 2012, and I'm having a problem with authentication. We use Windows Authentication to authenticate as DOMAIN\COMPUTER$, but this isn't working, ...
11
votes
2answers
5k views

Direct the OUTPUT of an UPDATE statement to a local variable

I would like to do this : DECLARE @Id INT; UPDATE Logins SET SomeField = 'some value' OUTPUT @Id = Id WHERE EmailAddress = @EmailAddress -- this is a parameter of the sproc Is this even ...
11
votes
6answers
48k views

Cannot continue the execution because the session is in the kill state. while building clustered index

I get the following error while trying to create a clustered index The statement has been terminated. Msg 596, Level 21, State 1, Line 0 Cannot continue the execution because the session is in ...
11
votes
3answers
10k views

db_owner unable to drop database - Error 615, SQL Server

I have a SQL Server 2012 database running on amazon EC2. I've created a user to be able to create, edit and drop databases. I gave the new user with dbcreator server role. My user can connect ...
11
votes
2answers
14k views

Storing IP addresses - varchar(45) vs varbinary(16)

I am going to create a table with two fields - ID as BIGINT and IPAddress as either varchar(45) or varbinary(16). The idea is to store all unique IP addresses and use a reference ID instead the actual ...
11
votes
1answer
3k views

How does SQL Server generate a query execution plan that adds up to 6,000%? [duplicate]

Today I was on The Heap and was looking at a query plan for something I thought could be improved. However, it created something that shook my belief in the SQL Server query optimizer. Can I still ...
11
votes
2answers
2k views

SQL Server 2012 Availability Group is it “AlwaysON”?

In a traditional SQL Server cluster when failover occurs, all clients that are connected to the SQL Server failed instance lose connections and each client must re-establish a new connection to ...
11
votes
2answers
7k views

sp_executesql with user defined table type not behaving correctly

Problem: is there a known issue with user defined table types as parameters to sp_executesql? Answer - no, I'm an idiot. Set up script This script creates one each of table, procedure and user ...
11
votes
3answers
5k views

Find the identity of the client firing a query in SQL Server without using triggers?

I'm currently using Change Data Capture (CDC) to track data changes, and I wish to track the host name and IP address of the client submitting the query that made the changes. If there are 5 different ...
11
votes
2answers
3k views

Understanding statistics, execution plans, and 'ascending key problem'

I'm trying to better understand (conceptually) the relationship between statistics, execution plans, stored procedure execution. Am I correct in saying that statistics are only used when creating the ...
11
votes
1answer
4k views

Sequence is reusing

I have a sequence that generates tracking numbers for objects in my system. It had been working fine for quite some time. Last week we noticed that it was starting to re-use values. What seems to ...
11
votes
1answer
3k views

SQL Server 2012 Simple Recovery Model with LOG_BACKUP log_reuse_wait_desc

While I'm doing my own investigation, does anyone know why a database in SIMPLE recovery model has LOG_BACKUP for the log_reuse_wait_desc? SQL Server 2012 SP1. Database created just a few weeks ago. ...
11
votes
3answers
5k views

Improving the performance of STIntersects

Table T_PIN has 300,000 pins and T_POLYGON has 36,000 polygons. T_PIN has this index: CREATE SPATIAL INDEX [T_PIN_COORD] ON [dbo].[T_PIN] ( [Coord] )USING GEOGRAPHY_GRID WITH (GRIDS =(LEVEL_1 = ...
11
votes
1answer
8k views

“truncate log on checkpoint” option in SQL Server

Long story but our long term consultants (former employees) wrote a custom script years back (2006 or so) to interface with Tivoli Storage Manager and it appears to be checking for a SQL Server DB ...
11
votes
2answers
458 views

SARG cardinality estimate, why not full-scan?

Why there is no full-scan (On SQL 2008 R2 and 2012)? Test data: DROP TABLE dbo.TestTable GO CREATE TABLE dbo.TestTable ( TestTableID INT IDENTITY PRIMARY KEY, VeryRandomText VarChar(50), ...
11
votes
2answers
4k views

Is SQL Server data compression categorically good for read-only databases?

Some literature on SQL Server data compression I read state that the write cost increases to about four times what would normally be required. It also seems to imply that this is the primary downside ...
11
votes
1answer
2k views

Restoring differential backup creates DEFUNCT log file?

Here is my problem. I'm trying to move a database to a new server via a full restore, then cutover with a quick differential backup/restore. I can do a full restore without a problem, but when ...
11
votes
2answers
5k views

Handling time zones in data mart/warehouse

We are starting to design the building blocks of a data mart/warehouse and we need to be able to support all time zones (our clients are from all over the world). From reading discussions online (and ...
11
votes
3answers
28k views

Best practice for tempdb log file

I have read many blogs here and there about how to configure tempdb data files but i didnt found any information regarding the tempdb log file. Here's the strategy that I'm presently using with my ...
11
votes
1answer
4k views

Job not running on Schedule

So I have a basic SQL agent job that runs a Robocopy script to move all files from one folder to another. Job is a pretty basic set up. With a pretty basic schedule. And yet it has yet to run. I ...