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
20
votes
2answers
134k views

Detecting the locked table or row in SQL Server

I'm trying to understand/learn how to track down the details of a blocked session. So I created the following setup: create table foo (id integer not null primary key, some_data varchar(20)); ...
20
votes
1answer
2k views

What's the anatomy of a columnstore index?

One of the new features in SQL Server 2012 codenamed Denali is the Columnstore index. I know a good bit about regular old row-store indexes, like the b-tree structure, differences in storage between ...
20
votes
2answers
4k views

Why does DELETE leave a lingering effect on performance?

At the end is a test script for comparing the performance between a @table variable and a #temp table. I think I've set it up correctly - the performance timings are taken outside of the DELETE/...
20
votes
4answers
889 views

Advice on diagnosing a “sometimes” slow query

I have a stored procedure which returns results from an indexed view via a covering index. Usually, it runs fast (~10ms), sometimes it can run up to 8 seconds. Here's an example random execution (...
19
votes
6answers
6k views

What is SQL Server “Denali”? What's new?

I found a new title called SQL Server "Denali" in the drop down list on MSDN, but I didn't find much information about it: What's New (this documentation is for preview only as I see) Top 9 New ...
19
votes
3answers
24k views

SQLCMD command is not able to insert accents

I am trying to run sqlcmd.exe in order to setup a new database from command line. I am using SQL SERVER Express 2012 on Windows 7 64 bits. Here's the command I use: SQLCMD -S .\MSSQLSERVER08 -V 17 -...
19
votes
2answers
1k views

Lock CREATE TABLE

In another application I was struck by bad design: multiple threads execute an EnsureDatabaseSchemaExists() method concurrently, which looks basically like this: IF NOT EXISTS (SELECT * FROM sys....
19
votes
3answers
9k views

Unique Identifier with Extra Characters Still Matching in Select

We are using SQL Server 2012 with a unique identifier and we've noticed that when doing selects with additional characters added onto the end (so not 36 chars) it still returns a match to a UUID. For ...
19
votes
1answer
2k views

Is it safe to rely on the order of an INSERT's OUTPUT clause?

Given this table: CREATE TABLE dbo.Target ( TargetId int identity(1, 1) NOT NULL, Color varchar(20) NOT NULL, Action varchar(10) NOT NULL, -- of course this should be normalized Code int ...
18
votes
1answer
3k views

“Cannot create a row of size 8074 which is greater than the allowable maximum row size of 8060” while altering the table

I am trying to alter a column in a table. The existing table is like this: CREATE TABLE [dbo].[table]( [id1] [int] NOT NULL, [id2] [int] NOT NULL, [id3] [int] NOT NULL, [name] [nvarchar](...
18
votes
4answers
21k views

SSMS Tools Pack Alternative [closed]

Are there any tools out there comparable to SSMS Tools Pack 2012? The licensing ($30 per machine, $100 for any number of machines... for 3 months) leaves a lot to be desired and I wasn't sure of what ...
18
votes
1answer
832 views

Multi-statement TVF vs Inline TVF Performance

Comparing some of the answers on the Palindrome question(10k+ users only, since I've deleted the answer), I'm getting confusing results. I proposed a multi-statement, schema-bound TVF which I thought ...
18
votes
1answer
4k views

Plan cache size and reserved memory

When running a query including the Actual Execution Plan, the root operator (SELECT) tells me that the Cached Plan Size is 32KB. A query that joins sys.dm_exec_cached_plans and sys....
17
votes
3answers
24k views

Restrict update on certain columns. Only allow stored procedure to update those columns

I have sensitive price columns that I would like to have updated only through a stored procedure. I would like all code or manual attempts to alter values in these price columns to fail if it is not ...
17
votes
2answers
52k views

Shrink Transaction Log While Using AlwaysOn Availability Group

We are using AlwaysOn Availability Group feature of SQL Server 2012. Regular full database backups and transaction log backups are done every day on the secondary database. I have read here doing the ...
17
votes
2answers
135k views

Converting a VARCHAR to VARBINARY

I've been keeping a log of expensive running queries, along with their query plans, in a table to allow us to monitor trends in performance and identify areas that need optimising. However, it's come ...
17
votes
3answers
46k views

Copy maintenance plans to a different server

Is there a way to copy maintenance plans from one SQL Server 2012 to another? Both servers are not accessible via the same copy of Management Studio at the same time (on different networks).
17
votes
3answers
47k views

Error message - view server state permission was denied - when connecting to SQL Server 2014 using SQL Server 2012 Management Studio

When right-clicking on a table and selecting "select top 1000 rows", I get this error: Is there any workaround, other than the obvious upgrade to SQL Server 2014?
17
votes
3answers
1k views

Why is this join cardinality estimate so large?

I am experiencing what I think is an impossibly high cardinality estimate for the following query: SELECT dm.PRIMARY_ID FROM ( SELECT COALESCE(d1.JOIN_ID, d2.JOIN_ID, d3.JOIN_ID) PRIMARY_ID ...
17
votes
3answers
36k views

Check progress of alter index reorganize / rebuild

How can I check the progress / status when I submit an alter index reorganize / rebuild ?
17
votes
2answers
8k views

Limit connection rights on SQL Server

I have an app to deploy in production that uses 'honor system' security. That is, all users connect to the DB using a SQL user/passwd credential and the app manages permissions itself. The latter part ...
17
votes
6answers
23k views

SQL Server Management Studio 2012: How to have folders in the SSMS project?

I'm using SSMS 2012 to talk to the SQL server 2012 and Azure SQL servers we use. I admit I'm not a SQL expert so I've been saving most of my SQL scripts for future reference. I've quickly run into 20 ...
17
votes
5answers
65k views

What is the fastest way to export a table to a text file

I will have a SQL Server 2012 database and a table with 3 million rows and maybe 50 columns. What will be the fastest way for an unattended background .net process (maybe issues some SQL or Powershell ...
17
votes
1answer
10k views

Warning in query plan “Cardinality Estimate”

create table T(ID int identity primary key) insert into T default values insert into T default values go select cast(ID as varchar(10)) as ID from T where ID = 1 The query above has a warning in ...
17
votes
2answers
55k views

How can I tell how many Cores SQL Server is actually using?

I have two servers running SQL Server. Server 1: SQL Server 2008 R2 Express (4 core) Server 2: SQL Server 2012 Developer Edition (8 core) As far as I am aware SQL Server 2008 R2 Express should only ...
16
votes
6answers
12k views

Determine 3rd Friday of each month

I need to determine the dates which are the "3rd Friday of each month" for a date range of "1.1.1996 - 30.8.2014" in SQL Server. I expect I should use a combination of DENSE_RANK() and PARTITION BY() ...
16
votes
2answers
2k views

Is it possible for SQL statements to execute concurrently within a single session in SQL Server?

I have written a stored procedure which makes use of a temporary table. I know that in SQL Server, temporary tables are session-scoped. However, I have not been able to find definitive information on ...
16
votes
3answers
4k views

Why would SQL Server ignore an index?

I have a table, CustPassMaster with 16 columns in it, one of which is CustNum varchar(8), and I created an index IX_dbo_CustPassMaster_CustNum. When I run my SELECT statement: SELECT * FROM dbo....
16
votes
3answers
2k views

Where to learn SQL Server Service Broker?

Are there any good resources to learn Service Broker? I'm looking for a newbie-friendly guide.
16
votes
5answers
1k views

What is the appropriate index architecture when forced to implement IsDeleted (soft deletes)?

Currently, we have an existing database and application that is fully functional. I do not have the ability to change the architecture at this point. Today, each table in the database has an "...
16
votes
1answer
8k views

Why is adding a NOT NULL column with a default constraint instantaneous?

CREATE TABLE TestTab (ID INT IDENTITY(1,1), st nvarchar(100)) INSERT INTO TestTab (st) values ('a') INSERT INTO TestTab (st) values ('b') INSERT INTO TestTab (st) values ('c') INSERT INTO TestTab (st)...
16
votes
2answers
11k views

Unexpected gaps in IDENTITY column

I'm trying to generate unique purchase order numbers that start at 1 and increment by 1. I have a PONumber table created using this script: CREATE TABLE [dbo].[PONumbers] ( [PONumberPK] [int] ...
16
votes
3answers
4k views

Wrapping query in IF EXISTS makes it very slow

I have the below query : select databasename from somedb.dbo.bigtable l where databasename ='someval' and source <>'kt' and not exists(select 1 from dbo.smalltable c where c.source=l.source) ...
16
votes
2answers
61k views

SQL Server 2012 Restore backup to new DB Name

I seem to remember that, in 2008, you could restore a backup to a new copy of a DB, by changing the name in the "Destination Database" field from the restore-wizard. It would create a brand new DB, ...
16
votes
3answers
35k views

Implications of changing compatibility mode in SQL Server from 100 to 110

I have a database Mydatabase created in SQL Server 2008 R2. I have upgraded to SQL Server 2012. I was trying to execute below query to calculate percentile select Distinct [KEY],PERCENTILE_CONT(0.25)...
16
votes
7answers
72k views

Error 3154 while restoring a backup using WITH REPLACE

I have SQL 2012 with SP1 installed on my computer. I made a backup of a database test.bak. I have a database with the name test2 which is the same database, but the data changed. I want to restore ...
15
votes
7answers
48k views

How do I deal with FK constraints when importing data using DTS Import/Export Wizard?

I am trying to use the SQL Server Import and Export Wizard to copy data from my production db to my dev db but when I do it fails with the error "The INSERT statment conflicted with the FOREIGN KEY ...
15
votes
4answers
9k views

How can I convert a key in a SQL Server deadlock report to the value?

I have a deadlock report that tells me that there was a conflict involving waitresource="KEY: 9:72057632651542528 (543066506c7c)" and I can see this: <keylock hobtid="72057632651542528" dbid="9" ...
15
votes
3answers
2k views

Get streak count and streak type from win-loss-tie data

I made a SQL Fiddle for this question if that makes things easier for anyone. I have a fantasy sports database of sorts and what I'm trying to figure out is how to come up with "current streak" data ...
15
votes
3answers
6k views

Securely generate a UNIQUEIDENTIFIER in SQL Server

I intend to be using a UNIQUEIDENTIFIER as an access key that users can use to access certain data. The key will act as a password in that sense. I need to generate multiple such identifiers as part ...
15
votes
3answers
50k views

SQL Server not using all CPU cores/threads

After upgrading our SQL Server's hardware, we noticed in the Windows Task Manager that the SQL instance is only using half of the threads available to it: . The server has the following hardware ...
15
votes
2answers
2k views

How can wait times be higher than clock time?

When I'm tracking waits with sp_BlitzFirst, I get this detail: <?ClickToSeeDetails -- For 20 seconds over the last 5 seconds, SQL Server was waiting on this particular bottleneck. -- ?> ...
15
votes
2answers
20k views

Could a null column be part of a primary key?

I'm developing a SQL Server 2012 database and I have a question about a One-to-Zero-Or-One relationship. I have two tables, Codes and HelperCodes. A code could have zero or one helper code. This is ...
15
votes
2answers
27k views

How to create a read-only server role on SQL Server 2012?

I am granting the "View any database" permission when creating a new server role, but realized this permission only allows the user to view the system databases. I am trying to create a server role ...
15
votes
4answers
22k views

How to changes or update local server connection in Maintenance plan job

Two days back our client changed one of our Dev Server name After Server Renamed, All my maintenance jobs and other jobs are failing because server name mismatch. We are using sql server 2012 ...
15
votes
2answers
7k views

Newer Version of SQL Server Less Stable?

Our database architect told a client of ours that SQL Server 2014 is a poor choice over 2012 because it lacks the performance and stability advantages that 2012 has. Everything I've read contradicts ...
15
votes
6answers
35k views

SQL Server 2012 slower than 2008

I migrated a large website and database from an older server (Windows 2008 / SQL Server 2008 / 16 GB RAM / 2 x 2.5 GHz Quad Core / SAS disks) to a newer, much better server (Windows 2008 R2 / SQL ...
15
votes
2answers
14k views

Is SQL Server 2012 Management Studio “Express” any different?

Are there any differences from the version of SSMS that comes as part of the SQL Server 2012 Enterprise Installer and the SQL Server 2012 Express Management Studio?
15
votes
3answers
61k views

How to get CPU usage by database for particular instance?

I have found the following queries to detect the CPU usage by database, but they are showing different results: WITH DB_CPU_Stats AS ( SELECT DatabaseID, DB_Name(DatabaseID) AS [DatabaseName], ...
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, ...