All versions of Microsoft SQL Server (not MySQL). Please also add a version-specific tag like sql-server-2016 if that is relevant to the question.

learn more… | top users | synonyms (2)

1
vote
0answers
941 views

No SQL Server instances show in upgrade setup

I'm trying to upgrade SQL Server 2005 sp4 to SQL Server 2008 R2. However on the upgrade wizard doesn't show me any instances to upgrade. Any ideas? The instance is there just fine. I can connect ...
1
vote
1answer
5k views

SQL Server 2012 Express fails at repair install, produces error 5178

My SQL Server Express Service will not start up. To produce this problem, I basically cloned my old hard drive (Which had SQL Server Express 2012 installed) to a new hard drive (Seagate Momentus XT ...
2
votes
1answer
229 views

Merge replication issue : conflicts with new data

I set up merge replication with SQL Server 2008, I am having issues when creating new data on clients and trying to synchronize with the remote server. Pulling data from the server works fine. But ...
4
votes
1answer
515 views

performance issue caused by IO?

Just curious to get an issue sanity checked . . . I'm trying to ascertain whether a performance issue experienced by users is the result of the app, server it sits on or SQL Server. Looking at ...
1
vote
1answer
363 views

Error creating XTP Table in SQL Server 2014 CTP1 with Powershell

Having a couple of hours to play I created a SQL Server 2014 box on Azure and followed This SQLServerCentral Post to create a File Group and table with T-SQL. All good. I then decided to take a look ...
4
votes
2answers
15k views

Splitting a large table to improve performance

This is a follow-up to an earlier question. I have a SQL Server 2008 R2 Standard server, that holds a single database, which itself has almost nothing except a large table. The table is 100+ million ...
0
votes
1answer
3k views

How to create a trigger in a different database?

Is it possible to create a stored procedure that creates a table trigger (DDL) in a different database than the one the stored procedure itself resides. The databases are on the same server instance. ...
0
votes
1answer
1k views

Extract time portion of Datetime2(7) in hhmmssfff format

I need to extract the time portion of a datetime2(7) column in hhmmssfff format and I am doing it like this: DECLARE @mdate AS DATETIME2(7) SET @mdate = '2012-03-15 10:13:27.5437431' SELECT LEFT(...
4
votes
1answer
276 views

View with fallback (performance/optimization question)

I have a table with tariffs for stuff; the tariffs table is not important in this scenario, the "tariff values" are. In this Demonstration SQL Fiddle the tariff_plan is the FK to the tariffplans table ...
3
votes
1answer
247 views

Execution Plan Regeneration

we have a database server with Sql Server 2008R2 that runs some queries every day; now these queries are fast (I suppose Sql Server has in memory some good execution plans). If we restart the ...
5
votes
2answers
31k views

The value could not be converted because of a potential loss of data [closed]

Let me start out by clarifying that this is not a duplicate question, nor a potential duplicate for that matter. I have tried implementing every answer to every single variant that already exists of ...
0
votes
3answers
938 views

How can I insert '&' symbol in data

I have a source table where one of column has data like: Column SD SD .. .. JK Jk .. .. I want to load this data into staging in format of: Column S&D S&D .. .. J&K J&k .. .. ...
5
votes
1answer
374 views

Distributed transaction and Read Committed Snapshot

Distributed transactions are not supported for snapshot isolation level in SQL Server 2008 R2. But what about read committed snapshot?
1
vote
1answer
389 views

Avoiding table level locking [closed]

I am maintaining a queue in a database table. An SSIS package checks the queue every two minutes. If it finds anything then it runs a job. I am experiencing some sort of table level locking. The ...
3
votes
4answers
5k views

Unique constraint across two columns

I need to add a constraint with two columns that says if any given value is present in one of the columns, then: 1) It cannot be duplicated in the same column. 2) It cannot be duplicated in the ...
2
votes
3answers
474 views

What Are the Log File Names?

I moved my database files. When I go attach the MDF file by itself, SQL Server will tell me that it can't find the log files. How do I go about asking the MDF what files it is expecting before ...
1
vote
1answer
213 views

Detect Convert_Implicit usages

I suspect that my Sql Server is using CONVERT_IMPLICIT a lot because many of my fields are set as string in NHibernate while they are set as varchar in database. Is there any option to detect when ...
-1
votes
2answers
132 views

How do I avoid duplicate rows using FREETEXT?

I have the following query: SELECT cat.CategoryId,cat.CategoryName,cat.CategoryImageURL,cat.CategoryImageData, ap.AppName,ap.AppImageData,ap.AppDetails,ap.AppId FROM Category cat,App ap WHERE ...
4
votes
4answers
11k views

How do I delete only related records in a multi-key MERGE in SQL Server?

Suppose you have something like this: Source table (variable): Values ( LeftId INT NOT NULL, RightId INT NOT NULL, CustomValue varchar(100) NULL ) Target table: Mapping ( LeftId INT NOT ...
37
votes
1answer
10k views

What event information can I get by default from SQL Server?

I often see questions where people want to know if a certain thing happened, or when it happened, or who performed the action. In a lot of cases, SQL Server just doesn't track this information on its ...
1
vote
0answers
52 views

Backing up the SQL Application

I have a Windows 2008 R2 server that needs to be periodically reset to a clean state. Normally I would look at creating an image of the system drive, however due to organizational reasons I cannot use ...
1
vote
0answers
824 views

Transactional Replication Failing Suddenly - Distributor Error

I have two databases (both SQL Server 2008 R2) taking part in transactional replication. This exists to pass data to a customer of ours, so I am troubleshooting from the Publisher side. Replication ...
4
votes
1answer
6k views

Large table and poor performance - what to do next?

We have a Windows 2008 R2 / SQL Server 2008 R2 (Standard) server that is used to host a single database. The database itself mainly consists of a single large table containing both live and historical ...
0
votes
2answers
611 views

Drop a SQL Server database

Taking too long to drop a 500gb+ SQL Server database. My question being what ways can we drop a database other than through GUI or T-SQL? Would deleting the .mdf and .ldf files from the server help? I ...
3
votes
1answer
2k views

Restoring database to UNC path on local drive

When I try to restore a database using a restore command with a local UNC path: RESTORE DATABASE [dbname] FROM DISK = N'\\PC91\D\backup.BAK' WITH FILE = 1, MOVE N'test' TO N'\\PC91\D\dbname.MDF', ...
1
vote
1answer
195 views

Suppress repeated performance condition alerts

I have set SQL Server performance condition alert with details as follows: Object : SQL Server:Databases Counter : Percent Log Used Instance : My database name Alert If ...
4
votes
1answer
771 views

Is there any interruption of service when switching recovery models?

I need to switch to FULL recovery model for several SQL Server 2008 databases. However these are in production. Whilst doing these things out of hours is good practice I wanted to plan for any down ...
3
votes
2answers
500 views

Need to change alphanumeric characters to next character

I have 2 columns Name, Passport No. in a table with more than 100k rows. Name Passport No. KOP A245 COS C990 After the update it should look like this. Name Passport No. LPQ B356 DPT ...
12
votes
2answers
31k views

How to partition an existing non-partitioned table

I have an existing table with data: dbo.Test (col1,col2,col3....) ON [PRIMARY] I need to change this table to be partitioned like this: dbo.Test(col1,col2,col3....) ON Ps_Date(Col2) How I can I ...
3
votes
1answer
177 views

Conversions between any data type and the binary data types are not guaranteed to be the same between versions of SQL Server

I just found that on the SQL Server 2012 MSDN documentation for binary and varbinary types, they mention the following at the end of the article: Conversions between any data type and the binary ...
8
votes
3answers
1k views

Updating a big replicated Dimension (SQL Server PDW)

We use a SQL Server PDW appliance for our data warehouse. One of the tables in our warehouse is a replicated table with about 20 million rows. As part of our ETL process we need to expire old records ...
3
votes
1answer
2k views

Are passwordless logins like ##MS_SQLResourceSigningCertificate## , ##MS_AgentSigningCertificate## a vulnerability?

I'm new to MS SQL Server. Colleagues working in IT security, have run an scan showing come DB users with null password. Some of them are ( # included ) ##MS_SQLResourceSigningCertificate## ##...
4
votes
2answers
721 views

Create a new database in SQL Server 2008 R2, but it is not Blank

When I try to create new database in SQL Server 2008 R2, it was created completely, but it is not a blank and empty database, there are many tables that come from another database. I also tried ...
3
votes
1answer
252 views

Mathematical method for spotting irregularity in numbers flow

I am trying to build an engine which is going to predict orders for items inside shop. Three main variables are: Time-dates (x-axe etc 1.1.2013,1.2.2013 etc I do calculation one per day) Available ...
4
votes
0answers
295 views

SQL Server insert randomly takes 10+ seconds to complete? [closed]

I have an insert statement that is being generated by the .NET Entity Framework. In most cases, this particular insert will execute in 0ms according SQL Server Profiler. One out of every 30 or so ...
2
votes
1answer
79 views

Defining a process for troubleshooting performance issues [closed]

The issue of poor database performance is one which presents itself from time to time and there are many techniques which can be employed to examine the root cause of slowness. Does anyone have a ...
3
votes
1answer
182 views

Replace column values?

I recently got into SSIS and haven't been able to find what I am trying to achieve anywhere on the web. I am trying to take some rows as input, and then replace one specific column's value with ...
7
votes
1answer
318 views

Auto Update Statistics Duration and Interruptions

Our production SQL Server 2008 R2 sometimes begins to perform slowly: all queries begin to work too slow (and sometimes all queries just hang and wait something then continue). This usually lasts ...
2
votes
2answers
4k views

scramble the value of one column and unscramble it?

I want to change all the values in one column, as it is a production database and I don't want to give the data to testers without obfuscation. Example: Name Name ---- --> ----...
2
votes
1answer
595 views

Grouping over multiple data dimensions (pivot)

I am logging an Events table, and want an output (as seen at the bottom of this answer), the issue is that I am getting the data in rows, but want them to be displayed column-wise. The two dimensions ...
3
votes
1answer
604 views

How to track overhead of Change Tracking in SQL Server

We're going to implement a solution based on Change Tracking feature from SQL Server. Before going in production (even after), we'd like to keep track of the overhead the CT will introduce. Once in ...
0
votes
1answer
3k views

LEFT vs CHARINDEX for matching the start of a string in T-SQL

I want to match the beginning of a string in a WHERE clause. While I recognise that so often database-performance is dependant on the underlying data structure, is there a best-practice for doing this?...
9
votes
1answer
2k views

How important are transaction log VLFs for performance?

How important are VLFs when considering database performance? What would describe an optimal situation for VLFs?
6
votes
2answers
537 views

Should I add a column or 1-1 table if property is usualy null

There's a new unique property we need to add, which is relevant only to a specific subset (less than 1%). We thought it's better to add a new table which will be 1-1, than to add a new column which ...
10
votes
1answer
10k views

SQL Server nvarchar(max) vs nvarchar(n) affects performance

This is SQL Server 2008 R2 SP2. I have 2 tables. Both are identical (data and indexing), except the first table has a VALUE column as nvarchar(max) and the second has the same column as nvarchar(800)...
2
votes
1answer
113 views

Using queries instead of loops to modify database principals

I need to check for the existence of certain users on some database servers, each of which has over 1,000 databases, and create the users if they don’t exist and assign proper database roles to them. ...
3
votes
4answers
2k views

Replication fails because of a non-existent foreign key

I have been trying to get a previously working replication to start working. I have tried many settings, but I can't seem to make it work. Here is the scenario. I dropped all the FK constraints ...
4
votes
2answers
10k views

Possible to run multiple Reporting Services (SSRS) instances on different machines with databases on same machine?

With SQL Server 2012, can SSRS instances be setup on two different machines, with both instances using the same machine for their SQL Server databases? Machine 1 SQL Server 2012 Database Server ...
5
votes
2answers
3k views

Disk Space Considerations for Rebuilding a large Clustered Primary Key Index

We have a table in our database with over 200 million rows and a Clustered Primary Key Index which is over 100GB. The Primary Key is only so large because many more millions of rows have been archived ...
4
votes
1answer
744 views

Locking in “Read Committed Snapshot”

If an update is run on a table with "Read Committed Snapshot" isolation level and a commit is pending eg: update table1 set col1 = col1 + 1 where PKcol < 3 update table1 set col1 = col1 + 1 ...