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
13
votes
1answer
876 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
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
1k 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
17k 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
2answers
591 views

ALTER VIEW drops Index from View

as the header line suggests: Using an ALTER VIEW statement on a VIEW that has an index on it will drop this (all?) indexes from the VIEW without warning. I would prefer the ALTER VIEW statement to ...
12
votes
5answers
4k 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
5answers
5k 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
2k views

Creating Non-Clustered Index on Non-Persisted Computed Column SQL Server

I am struggling to find any documentation on how SQL Server actually stores a non-persisted computed column. Take the following example: --SCHEMA CREATE TABLE dbo.Invoice ( InvoiceID INT ...
12
votes
2answers
76k 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 ...
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
2answers
10k 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
3answers
17k 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 ...
12
votes
2answers
5k 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
6k 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
6k 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 ...
12
votes
1answer
8k views

A possible infinite recompile was detected for SQLHANDLE

I have been finding strange error messages on the sql error log: Bocss: same deadlock taking place every hour – needs investigating Also lots of recompiles are listed in the error log for other ...
12
votes
3answers
6k 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 ...
12
votes
1answer
1k 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
3answers
6k 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 = ...
12
votes
2answers
5k 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
922 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] [...
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
12k 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
233 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
3k 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
3answers
2k views

NOLOCK hint changes order of records returned

There is a clustered index on a table Client field LastName. When I simply dump all records from the table, they appear in the alphabetical order unless (nolock) hint is used as in the query in ...
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
22k 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
798 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
3answers
5k 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 ...
11
votes
3answers
495 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
4k 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
8k views

Computed column cannot be persisted because the column is non-deterministic

I know this is not the first time this type of question has been asked. But why in the following scenario is the persisted computed column being created "non-deterministic". The answer should always ...
11
votes
4answers
37k views

Find all IP's or users who access a specific SQL Server

Let's say I found a SQL Server and I have no idea which applications connect to it. Maybe I find one application but I am not sure if it is the only one using it. Is there a nice way of finding all ...
11
votes
2answers
78k views

Optimizing queries for 25+ million rows

I am using MS SQL, and I have to run several queries on the same table on different criteria. At first I ran each query on the original table although they all share some filtering (i.e Date, status). ...
11
votes
5answers
34k 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
5answers
26k 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
884 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
2k 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
6answers
51k views

How to fix a messed up replication on MS SQL Server

I restored a database from a backup. The database uses replication to publish to a different server. Assuming the database restore would break the replication, I tried to delete the replication and re-...
11
votes
1answer
14k 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
6answers
59k 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 the ...
11
votes
2answers
16k 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
3answers
12k 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
513 views

SQL Server sample Update of Statistics misses highest RANGE_HI_KEY on ascending key column

I'm trying to understand how Statistics sampling works and whether or not the below is expected behaviour on sampled statistics updates. We have a large table partitioned by date with a couple of ...

1 2 3
4
5
88