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)

2
votes
1answer
43 views

Slow Delete's of LOB data in SQL Server

I have a tables for logging, and a stored procedure to purge old data that has very slow DELETE performance, beyond what makes sense to me. I am looking for how to modify the tables or DELETE ...
1
vote
2answers
31 views

Return multiple columns (int datatype) inside a CASE expression

How can I get multiple columns from a CASE expression? with cte as ( select ROW_NUMBER() over (order by id)'Rownumber', id from dbo.Test ) select case when (b.id-a.id) > 1 ...
2
votes
1answer
419 views

I keep getting an error 10060 trying to connect object explorer in Visual Studio 2013 to an Azure Database

Using Win 7, Visual Studio 2013, (or Sql management tool 2014) This is for a class and its all software on the trial period. In Visual Studio server explorer - I can connect to the Azure Server I ...
0
votes
2answers
20 views

AlwaysOn Async read takes more time when compared to that of Sync secondary

AlwaysOn Async read takes more time when compared to that of Sync secondary (by a small fraction). Is this expected behaviour? I was under the impression that ASYNC must be faster than the SYNC.
1
vote
2answers
51 views

Can we commit the transaction when the sql server trigger fails?

I am trying to avoid data loss when the insert trigger on a sql table fails. I am testing it out using the following sql. I am trying this with the following scenario and my code is failing. When an ...
0
votes
2answers
52 views

Trim Trailing Whitespace, Tab, Newline, Carriage Returns, etc

I am working with SQL Server 2008 and I am looking for a function like ltrim and rtrim which will also remove leading and trailing tabs, double spaces, carriage returns, line feeds, etc. There are a ...
8
votes
1answer
16k views

Planned release date for the next version of SQL Server [closed]

Has Microsoft published a roadmap for the next version, identifying a planned release date? If not, how long do you think before the next version becomes production ready?
11
votes
0answers
93 views
+100

Keeping (Partial) Backups Small when using SQL Server FILESTREAM

I have a database with nearly 1TB of FILESTREAM data that I don't need backed up (if the data was deleted it'd be recreated automatically in a couple of hours, so it just isn't important). Most of the ...
-3
votes
1answer
27 views

Disk Space issue [on hold]

I am facing one problem regarding Disk Space issue. One Disk size is 249 GB,it contains only .mdf files. Now the Disk utilization is 84%. How can i reduce Disk utilization less than 80%.
3
votes
0answers
21 views

Chance of skipping extended events when using an offset for the file target

I am exploring extended events and came across a question I can't solve using the Microsoft documentation : When reading events from the file target we can specify and initial offset so we can read ...
0
votes
1answer
9 views

Server configuration SQL Server 2008

I am really fresh in the DB world so firstly,sorry about my noob question. I have a data warehouse project to perform in a group of 3 people. When installing SQL Server 2008 R2 Developer in my own ...
0
votes
0answers
11 views

Error connecting from other SQL instance using powershell script

I have a job to run a powershell script that will rebuid fragmented index through source SQL instance to target SQL instance. The poweshell script didn't run due to the following error: Sqlcmd: ...
1
vote
1answer
32 views

What happens when two separate processes perform a full backup against the same database?

I have an instance of SQL Server 2012 RTM running. I have two separate applications that want to do a backup of a database lets call them "AppFull" and "AppFullCopyOnly". AppFull does a full ...
16
votes
7answers
391 views

Group daily schedule into [Start date; End date] intervals with the list of week days

I need to convert data between two systems. First system stores schedules as a plain list of dates. Each date that is included in the schedule is one row. There can be various gaps in the sequence ...
1
vote
1answer
36 views

Capitalize only the first letter of a name

I have a table Vendors that contains a column VendorContactFname, which is written in lowercase. I want to capitalize only its first letter. I am thinking of concatenating an Upper on the first ...
8
votes
1answer
194 views

Can I Improve Performance on Bloated System Tables?

Background: I have numerous databases with a large number of VIEW's, and an extremely large number of SYNONYM's. For example, one db has more than 10k VIEW's and 2+ million SYNONYM's. General ...
0
votes
1answer
21 views

TSQL Solution to backup ALL databases on ALL instances of a server?

I'm pretty sure what I'm wanting with my current implementation is impossible, the sys.databases catalog view which is scoped at the service level to be aware of other SQL Server instances (lol). But ...
3
votes
1answer
167 views

SQLCMD stuck on “set textsize 4096”

I am using SQL Data Compare to generate a script that updates data in a QA database (SQL Server 2012). The script is around 500mb and I added a GO every 100 lines. When I start the script with: ...
1
vote
1answer
62 views

AlwaysEncrypted Conversion Fails

I'm trying to encrypt a column with AlwaysEncrypted and I get the following error. Could not deploy package. Warning SQL72012: The object [AlwaysEncryptedDemo] exists in the target, but it ...
0
votes
3answers
41 views

Minimum value within a set (or window) within a three column table

I only have SQL Server 2008R2, therefore I do not have all the window functions available since SQL Server 2012. I have a table that looks like this: I would like to retrieve a result set like ...
17
votes
5answers
581 views

SARGable WHERE clause for two date columns

I have what is, to me, an interesting question on SARGability. In this case, it's about using a predicate on the difference between two date columns. Here's the setup: USE [tempdb] SET NOCOUNT ON ...
5
votes
3answers
173 views

Selecting one row from each group based on a Varchar field

Given a table Avenger whose data looks like this: id Avenger Type Power 1 Captain America foo 2 2 Captain America bar 3 3 Me foo 5 4 You ...
0
votes
0answers
7 views

Database migration from SQL2005 to SQL 2012. SQL groups Creation with Securables

I moved 7 databases to MS SQL server 2005 to MS SQLserver 2012. Both SQl are on different servers. I copied window level user group and created SQL server level groups in SQL server 2012. Now my ...
3
votes
1answer
27 views

Do temporal tables log changes when there are none?

Unfortunately I don't have ready access to a SQL 2016 instance to test this. If I had a temporal table with the following schema: create table blah ( foo int identity(1,1), baa int ) and I ...
3
votes
3answers
820 views

Can the OUTPUT clause create a table?

I'm doing an update like this: UPDATE dbo.Table1 SET BirthDate = b.BirthDate FROM Table1 a JOIN Table2 b ON a.ID = b.ID And I want to use the OUTPUT clause to back up my changes. UPDATE ...
3
votes
3answers
118 views

SQL Logins in db_owner role on database cannot create stored procedures

I started locking down permissions in our test environment by removing sysadmin permission from generic accounts. The first account, used by several developers, is a local SQL login. I gave the ...
1
vote
1answer
43 views

Slow-running query returns incomplete resultset

So I'm trying to get some data from an SQL Server 2008 instance, the query is quite simple in structure but it has an extremely poor performance, it takes just under a minute to retrieve a bit over ...
4
votes
1answer
43 views

How can a database parameter be used on `sp_addextendedproperty`?

I have found the MSDN definition and code examples of 'sp_addextendedproperty'. In Example A on that page, I see an example of how to add an extended property to a database. USE AdventureWorks2012; ...
1
vote
2answers
25 views

Using Variables to contain column names for SQL Server Merge statemtns

I'm trying to automate the column list grab for a merge job, and running into a bit of a head scratcher. I don't want to have to manually type out all of the column names, and thought I may be able to ...
0
votes
3answers
34 views

how can database snapshot to be used to recover data?

I know the logic behind database snapshot and how it works. But my problem is how can database snapshot to be used to recover data? can we take a backup of database snapshot and restore it in a new ...
11
votes
3answers
178 views
+250

Database stored procedure with a “preview mode”

A fairly common pattern in the database application I work with is a need to create a stored procedure for a report or utility that has a "preview mode". When such a procedure does updates, this ...
0
votes
0answers
18 views

BallPark Upgrade Cost SQLServer 2014 standard with SA to Enterprise Edition with SA [on hold]

Anybody know a link or has a ballpark way of calculating upgrading from 2014 Standard Edition SA to 2014 Enterprise edition with SA? Failed when searching google land in this endeavor.
1
vote
1answer
22 views

Using a join operation with conditionals

I have two tables that I'm working with in my database. I am using SQL Server 2014. One is 'versions', that contains version_id, version_number, start_date, end_date and server_id. The other is ...
0
votes
2answers
42 views

Migrate SQL server data to new schema

I have a database that was originally created by a couple of people for a small business unit (about 8 staff and 10 customers) in MS Access. The original 'developer' was a non-geek who had been sent ...
-1
votes
1answer
32 views

How can I do a SQL Server 2000 bak?

I have 2 databases .mdf SQL Server 2000, and I need to restore them on SQL Server 2014. After some research, I saw that the best approach is making a .bak of this 2 files. Well, I need to install SQL ...
0
votes
1answer
32 views

List of queries run

I'm looking for a DMV query for SQL Server 2008 R2 which shows me all queries (full history) run by a specific user. I have this code for now: SELECT * FROM sys.dm_exec_query_stats qs CROSS APPLY ...
0
votes
1answer
13 views

Granting only SELECT VIEW permissions to specific Views to a specifc user on a MSSQL database

I have landed in a scenario were I have to let a user inside a database which is not his. Hence I want to make sure that he is only able to SELECT from the views that I deem he can read from. I have ...
0
votes
1answer
13 views

Generically get all related records from SQL Server Database based off of Primary Key of one table

In Sql Server it is possible to visually see all relations a table has by creating a diagram of one table and right clicking and selecting 'Add Related Tables.' Is this also possible to do for one ...
82
votes
4answers
11k views

Why are numbers tables “invaluable”?

Our resident database expert is telling us that numbers tables are invaluable. I don't quite understand why. Here's a numbers table: USE Model GO CREATE TABLE Numbers ( Number INT NOT NULL, ...
1
vote
1answer
16 views

Looking for a better way with Reporting Db server

I have a "reporting" db server which is basically a clone of production using backup and restore in a stored proc to handle restore , user , permission etc. Backups/restores happen 1x per day at a ...
0
votes
1answer
21 views

Can mirror and witness have same port number? [on hold]

When configuring database mirroring, the mirror and the witness have generated the same port number. When I hit next, it errors out with: End points for instances for the same server must use ...
3
votes
1answer
256 views

Can see other user's databases in shared hosting

This looks like a security concern but I do not know enough about SQL Server to know. Using a shared hosting service, if I go to restore a database I'm able to see other user's databases. I'm not ...
0
votes
1answer
21 views

data validation Datawarehouse ETL

Need a suggestion from Datawarehouse and ETL experts. I have a project which has bunch of fact and dimension table, and we wire the facts using surrogate keys to dimension (religiously following ...
0
votes
1answer
625 views

Select Query takes more than a minute to retrieve data

I have a table that has 99 columns, all of them are not needed but they were there when we started. There is a Primary Key (Objectuid) which is a GUI and has the Clustered Index on the PK. The Table ...
28
votes
4answers
3k views

Is NOLOCK always bad?

I am a Report Developer who wants to make my queries as efficient as possible. I used to work with a DBA who told me - I believe because I was always dealing with reports on a Production Server - to ...
1
vote
1answer
175 views

After Insert Trigger never fires using Import Wizard

Using Import Wizard I tried importing tables tcc_Block and PROJECT_IDENTITY. These 2 table structures already exist in SQL Server and they are related with ProjectID columns. PROJECT_IDENTITY has ...
5
votes
2answers
838 views

Symmetric difference operation in Transact-SQL?

I have always known about the UNION operator in SQL, but only recently discovered that there were other set operators, INTERSECT and EXCEPT. I haven't been able to find an operator that does the ...
2
votes
1answer
526 views

Command fails as CmdExec job but succeeds from console

I have a SQL Server 2014 Agent Job (Type: "Operating system (CmdExec)", Run as: "SQL Server Agent Service Account", Owner: sa) that executes the following command: powershell -ExecutionPolicy Bypass ...
-3
votes
0answers
12 views

show invalid object name [on hold]

when this code runs on mysql database then fine but when this code run on sql server that showing error com.microsoft sql server exception invalid object name... private void ...
2
votes
1answer
38 views

How do I generate the ENCRYPTED_VALUE for CREATE COLUMN ENCRYPTION KEY?

I can make a certificate in the windows certificate store for the master column key easily enough with: $cert = New-SelfSignedCertificate ` -Subject "Always Encrypted Sample Cert" ` ...