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
votes
3answers
118 views

Add Contents in a Column and make them 0 [closed]

http://sqlfiddle.com/#!3/96f11/3 In the above fiddle, I need the required output in the Fiddle. ie., The UserID column in Filtered Table and Main table are equal. I need to get the Amt column data ...
3
votes
4answers
8k views

Speed up large clustered index creation?

I have a large table, the row count of the table is more than 3 billion, the data space for this table is about 120 GB. And Intel Xeon CPU E5645 @2.4GHz(2 processors), 24 CPUs, 64G memory, 64bit ...
2
votes
1answer
827 views

Visualiser for spatial query results

This question Whats the easiest way to get sql 08 spatial data onto a map? (and many other articles) recommends Craig Dunn's Geoquery, but all the download links appear to be kaput. SQL Server ...
4
votes
2answers
572 views

Total Memory capped far below available memory

We are testing out a new install of SQL Server 2012 on a virtual server and the memory allocated to SQL Server seems to be capped around 16 GB even though much more is available. What could cause ...
2
votes
1answer
782 views

Rebuilding Unique Index with uniqueidentifier in SQL Azure never succeeds

We have a number of tables (~1M records) that have a column on them defined as: [GlobalID] [uniqueidentifier] NOT NULL that gets auto-populated with newid(). We use this ID for synchronizing data ...
1
vote
1answer
1k views

Composite field in SQL Server, does it exist?

I'm designing a table to be used by a Web App, and my framework requires that the primary key be a surrogate key. But I also need an extra field which will be a composite of 2 other fields. In other ...
3
votes
2answers
749 views

limit the number of rows returned when a condition is met?

Is it possible to limit the number of rows returned when a condition is met? I am working on a query to check if a student is ready to graduate, and they need to meet a certain number of credits per ...
1
vote
3answers
3k views

Easier way to handle so many isnull() situation

Is there any good way to avoid writing so many times isnull() function inside sproc ? I have stored procedure that use almost 30 times isnull() function, I think that I am miss a concept , but until I ...
1
vote
2answers
309 views

How to create trigger on database that 'triggers' when mirroring role changes from mirror to principal

I want to perform some sql when my database mirroring state changes
5
votes
1answer
209 views

Fulltext stoplist replication

In MS SQL Server 2008 R2 there is replication of table with fulltext index on it. But stoplist that is associated with replicated fulltext index doesn't replicate. Is there any possibility to ...
7
votes
2answers
4k views

tempdb logs on same drive as tempdb data or with other logs? [duplicate]

For many reasons I only have 3 hard drives (RAIDed and in an Always-On AG) for all my database files: D: Data E: Logs F: Tempdb Should the tempdb log file go on F: with the data file(s) or on E:? ...
4
votes
2answers
16k views

Does a simple select query acquire locks?

I am very new to SQL Server, and would like to understand whether the following, very simple select statement would take any locks. Select * from Student; Please consider the case where the ...
4
votes
2answers
80k views

Login failed for user 'domain\username'. (Microsoft SQL Server, Error: 18456)

I am facing this problem when connecting to local system db, but when I connect any server it connects. Login failed for user 'domain\username'. (Microsoft SQL Server, Error: 18456) For help, click: ...
4
votes
4answers
11k views

Logging queries and other T-SQL

I would like to know if SQL Server 2008 R2 has a default logging scheme for SELECT statements (or any other T-SQL for that matter). If yes, where can I see it? If not, how could I set it up?
3
votes
1answer
544 views

How to determine cause of runtime increase given two query plans with SpillToTempDb warning

Two actual query plans were captured for two executions of one query: Plan "Fast" took ~1s and occurs about 90% of the time. Plan "Slow" took ~16s. Since the graphical plans look ...
4
votes
1answer
563 views

SQL Server 2012 indirect checkpoint

I am trying to understand where to draw a fine line for Indirect Checkpoint that was introduced in SQL Server 2012. Based on my understanding SQL Server does checkpoint to flush dirty pages out to ...
4
votes
2answers
2k views

Transactional Replication Concerns

We are debating an architecture that employs sql server 2012 and transaction replication. Idea is to offload reporting activity to a secondary server and have the ability to include/exclude what is ...
5
votes
2answers
12k views

Extract part of string based on nth instance of character

I am trying to find a way to extract part of the strings below. I need everything between the 6th and 7th \ character. \\fileServerA\d$\LiteSpeed\Wednesday\ServerA\Tlog\DBA1_TLOG_20110504_0333.SLS ...
8
votes
3answers
818 views

How to insert in a table with only an IDENTITY column?

Given a table with only an IDENTITY column, how do you insert a new row? I've tried the following: INSERT INTO TABLE (Syntax error) INSERT INTO TABLE VALUES() (Syntax error) INSERT INTO TABLE (Id) ...
2
votes
1answer
120 views

How to enforce a nullable foreign key?

I have a relation between two tables. The foreign key table can have a row related in the primary table. There's a way to enforce the value of the FK column to be NULL or one of the values of the PK ...
6
votes
2answers
4k views

How do I change SQL Server 2012 Availability Group DNS subdomain?

Questions: how can I get my availability group to be registered as ag-ewgtest.sql.company.com given that my server thinks of itself as server.company.com. Setup: my SQL Server 2012 availability ...
0
votes
1answer
50 views

How to get default locking hint of a table

Is there is any way to get default locking hint using a query. I want to know whether the locking use by my transaction is tablock | rowlock | nolock etc.
1
vote
2answers
581 views

Can we Configure Change Data Capture in another Database?

I want to apply Change Data Capture(CDC) to database DB1's tables and want to log everything in DB2. So, could it be possible to capture table DB1's changes to DB2?
1
vote
0answers
631 views

Registering a SSDT database project as a data tier application fails

I am having problems publishing a SSDT database project and registering it as a data tier application. Let me explain. I have a database (A) which references two other databases (B & C) through ...
2
votes
3answers
3k views

How to backup to local drive and network drive simultaneously?

I can backup to a local drive and mirror: BACKUP DATABASE [xxx] TO DISK = N'XXXX.bak' MIRROR TO DISK =N'Network path \XXX.bak' WITH INIT --directly to network drive BACKUP DATABASE [xxx] TO DISK ...
0
votes
1answer
319 views

Recover data from accidental update

I have done an incorrect update and hence I restored the DB with a backup from the previous day. I have not taken a copy of the corrupted data before restoring the backup. Is there any possibility to ...
3
votes
2answers
4k views

Using T-SQL to dynamically create Extended Properties

I would like to add Description and Status in the Extended Properties of a large database to all my tables and columns so that someone can fill in the values. It would be very laborious to add these ...
1
vote
3answers
72 views

What is the benefit of two FK, when one of them can be deduced from another table?

The third party Microsoft SQL Server database I'm working on is using the structure which can be illustrated with this example: Let's take three tables: Shop which is the topmost level and ...
0
votes
2answers
3k views

Running a Job from a Stored Procedure in another server?

How can I run a job from another server using a stored procedure? Let's say server 1 : db1 server 2 : db2 username : testssis password : testssispass sqljob found in db2: job1 Here is the part ...
2
votes
2answers
4k views

INSERT INTO #temp taking 1000 times longer than SELECT INTO #temp

I am shredding an xml blob that is being passed to me and loading a temp table for further processing. One of the things I noticed while working through the process is that performing an INSERT INTO ...
2
votes
2answers
507 views

Can view adapt to implicitly reference user's default schema?

A bit of background. I have a base application and most clients use it as standard. However some clients have small code and database customisations. Each of these clients has their own branch and ...
12
votes
3answers
25k views

After moving database (backup, restore), I have to re-add user

I occasionally move a database (SQL Express 2012) from a development machine to a server, or vice versa, using database backup and restore in SQL Server Management Studio. Whenever I do this, the ...
2
votes
2answers
2k views

Shrinking log files with SMO

I know I can use the following t-SQL to shrink a log file: BACKUP LOG db1 TO DISK = '\\server\share\db1_log1.trn'; DBCC ShrinkFile([db1_log], 0); How do I do the same with SMO? I tried: $server = ...
10
votes
3answers
8k 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 ...
2
votes
3answers
3k views

Msg 21, Level 21, State 1, Line 1 Warning: Fatal error 9001

We recently moved to a SQL Server that's on a VPS and every now and then we get a 9001 error when trying to do an update. Rebooting the server fixes the problem. I changed the maximum server memory ...
4
votes
2answers
3k views

Error while taking backup using SSMS

I am using SSMS. I have about 5 logins. For a particular login I have all server roles other than sysadmin. It is a sql login (not windows authentication). In user roles it has all permission for ...
2
votes
1answer
602 views

Paging in SQL Server 2012 vs. previous versions

I have used lot of paging code in SQL Server 2008. The format is With CTE( -- SQL With RowNumber ) -- SELECT ALL Page N This is working fine. But I have heard that SQL Server 2012 has better ...
4
votes
2answers
2k views

Executing a table type parameter stored procedure or function within a select clause

Just a quick question. I have a stored procedure that takes two parameters, a varchar (table name) and a tabletype (range of primary key ID values), and uses them to return all FK connections to and ...
1
vote
0answers
61 views

Slow Response: Initially it's slow and on subsequent restart of the host it works normally

We have SQL Server 2008 R2 32 bit installed on the following hardware: CPU: Intel Xeon E5310 @ 1.60 GHz RAM: 4 GB HDD: Raid Server: HP Proliant 120 OS: Microsoft Windows Server 2003 R2, Standard ...
1
vote
1answer
147 views

Key lookup partition pruning

I have a query inner joining multiple tables all of which are partitioned and have 10 partitions each: SELECT A.COL1, B.COL2, C.COL3 FROM A INNER JOIN B ON A.ID = B.ID INNER JOIN C ON A.ID = C.ID ...
0
votes
1answer
873 views

Using a table swap for a data load but I have triggers

I'm working on re-writing a table load. The current method is a daily job that deletes from the table, then re-loads it. The complaint is that if the load fails the users have no data. The users ...
17
votes
2answers
14k views

ALLOW_SNAPSHOT_ISOLATION and READ_COMMITTED_SNAPSHOT

Most of the forum and example online always suggest to have both ALLOW_SNAPSHOT_ISOLATION and READ_COMMITTED_SNAPSHOT set to ON whenever someone is asking snapshot, row versioning or similar question. ...
1
vote
2answers
168 views

Dictate edition during unattended install?

I am creating an automated build of SQL Server installation which is run from a batch file. Is there any way to change the registry, or other method, of deciding which SQL Server edition (Either ...
1
vote
2answers
539 views

Generic SQL Job Scheduler for multiple RDBMS's?

I have been searching for an answer to this, but can't seem to find anything. So my problem is this - we have an environment with MS SQL Server 2008, MySQL, and RedShift, and have some complex ...
2
votes
1answer
130 views

How can I identify the number of times a View is called in SQL Server?

How can I identify the number of times a View was called in SQL Server? I am looking for an answer that is similar to this below which shows how it can be done for stored procedures. How can I ...
1
vote
3answers
164 views

T-SQL Issues With Defining 'AS'

I am creating a fully dynamic application but have ran into a bit of a hiccup. There are multiple 'undefined' fields that can be defined by the users. The only problem is redisplaying them. Currently ...
4
votes
2answers
5k views

Can I copy the table structure of a #temp table to a new physical table?

I have a #tempTable that was created using SELECT * INTO #tempTable FROM OPENROWSET('Microsoft.Ace.OLEDB.12.0', 'Excel 8.0;Database=MyFileName.xls', 'SELECT * FROM [Sheet1$]') Is there an easy way ...
1
vote
1answer
242 views

Query not returning items in the original order

Consider the following set, which is the content of the table variable @ProductContent that was just created for the query that runs against it: Notice how items are sorted by media_type_id, ...
3
votes
2answers
99 views

Identity proceeds as normal, but with a leading 10…?

So I noticed a very strange thing when tinkering with my database. I had just inserted a row which was ID 47, then after inserting some more, I realised that somehow my Identity had skipped all the ...
2
votes
3answers
1k views

Is transaction log lost after backup on SQL Server?

I have database that has regular nightly backup (full recovery, full backup). I noticed some irregularities in data and wanted to check out transaction log to see what happened. I restored earlier ...