Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [sql-server]

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.

5
votes
4answers
2k views

What's the reason for using double inner join in this SQL statement

I am looking at this legacy SQL Query, the bit I am not able to get is why it's inner joining same table twice on same columns, I am talking about Table1 and Table1 joined with alias "Table1Alias", ...
2
votes
1answer
30 views

SQL Server Management Studio - Actual Execution Plan - Actual Row Size and Actual Data Size

I am looking at an actual execution plan in Microsoft SQL Server Management Studio v 17.9.1 and I can't see a lot of actual values to compare against the estimates. For example, we have a connector ...
1
vote
1answer
38 views

Shrinking a SQL Server 2008 Standard database to move it into SQL Server 2014 Express

I need to shrink a popular CRM database with SSMS to move it onto a new server. I am going from SQL Server 2008 Standard to SQL Server 2014 Express. Database is in Simple Recovery Model. It needs to ...
84
votes
7answers
51k views

Guid vs INT - Which is better as a primary key?

I've being reading around reasons to use or not Guid and int. int is smaller, faster, easy to remember, keeps a chronological sequence. And as for Guid, the only advantage I found is that it is ...
2
votes
3answers
126 views

Insert self referencing entry into SQL server

I have a Person table that has a "created_by" column that references the primary id of the table itself. So, it could be an employee that adds another employee to the database. Works fine. But People ...
-6
votes
0answers
33 views

MS SQL database Sizing and Growth [on hold]

Can live production databases be resized while online at quiet times? I'm using SQL 2014.
8
votes
2answers
828 views

Tab color for registered servers in Management Studio

SSMS allows us to use custom color for each connection. Does anyone know where this information is saved? I'd like to keep the color setup but I keep losing it. edit When I connect from object ...
7
votes
1answer
179 views

Why is this stream aggregate necessary?

Check out this query. It's pretty simple (see the end of the post for table and index definitions, and a repro script): SELECT MAX(Revision) FROM dbo.TheOneders WHERE Id = 1 AND 1 = (SELECT 1); ...
-1
votes
1answer
22 views

Some sys.dm_io_virtual_file_stats columns interpretation needed

I need someone to help me understand two columns of dm_io_virtual_file_stats. Looking to my results of that procedure running and filtered against one database, I can see that the size_on_disk_bytes ...
0
votes
4answers
2k views

How to solve Error 927 'can not open database' while configuring DB mirroring

Steps I followed: Create DB on primary. Took full backup and log backup. Restored full and log on secondary in NORECOVERY mode (now tb shows restoring satus on secondary I guess this is because ...
1
vote
1answer
34 views

SSPI error on windows authenticated remote access only

We are trying to figure out what is wrong with our server. Right now i have many accounts that are sql accounts and windows authenticated (domain accounts). When i connect with ANY windows ...
-2
votes
0answers
46 views

I need to merge multiple row to single column without using XML path

SELECT sku, size, color, product_code, sku_config = CASE WHEN sku = 'parent' THEN 'sku=' + product_code + '_' + STUFF((SELECT sku_config = '|' + sku FROM dbo.Products AS ...
-2
votes
1answer
44 views

I need to concat data from multiple row into one column without changing the structure of actual table during export [on hold]

I have a table item in which SKU, size, color is there and product_code is the same for parent and child product. When I import a product to e-com, under my parent, child product should be displayed....
-2
votes
0answers
46 views

INTIME and OUTIME separate from column on the base of status 1 and 2 [on hold]

I have data in column now i want to separat it status wise one col Status(1)IN and Col(2)Out
-1
votes
1answer
27 views

Can't execute insert query [on hold]

INSERT INTO Datos ( NumEstacion, Fecha, NumParametro, NumFuncion, Valor, Control, Tipo ) Select 1821, Fecha, NumParametro, NumFuncion, Valor, ...
2
votes
1answer
17 views

'Ungroup' data based on column

I'm using SQL Server 2012 and I have a table A that has the Id and PseudoId columns and a bunch of others I am not interested in. What I am interested in, is the PseudoId especially. This PseudoId has ...
15
votes
3answers
1k views

SQL server changes XML structure when inserted

I'm inserting some XML data to an XML column in SQL server but after the data has been inserted it has been changed by sql server. Here is the data I insert <xsl:value-of select="...
4
votes
3answers
365 views

Does Compatibility Level allow unsupported functions?

I've got a SQL Server 2016 instance with a database on it, running with a compatibility level of 2008. However, the following dummy proc creates, and executes just fine. CREATE PROC usp_TestFormat AS ...
0
votes
1answer
27 views

How to Remove Multiple Databases Marked with “Recovery Pending”

Recently I tried to clean my appilcations which used SQL database(s), therefore, the leftover databases are now marked with "Recovery Pending". Right now I can follow this to delete them manually. ...
2
votes
1answer
39 views

Custom sort before ROW_NUMBER() for paging

I have the following SELECT results.*, coinCat.*, cat.* FROM (SELECT c.*, ROW_NUMBER() OVER (ORDER BY c.CoinId) AS RowNumber FROM coins c) AS results INNER JOIN CoinCategoriesCategories ...
2
votes
1answer
19 views

Report transition from Access to Crystal (multiple queries)

I have just started a new job where I am responsible for building and improving reports. There is one report I am trying to move from Access to Crystal. The data is stored in an application called ...
9
votes
1answer
560 views

HADR high worker thread usage

Why would an availability group's number of worker threads in an HADR pool increase well beyond minimum usage of "typically, there are 3–10 shared threads" per replica? In one case we've observed ...
1
vote
1answer
58 views

T-SQL Splitting rows into multiple rows

I have a unique request here. I have to split rows for each ID into separate rows, but moving sets of columns into separate rows. Kind of hard to explain with just that so here is a visual ...
0
votes
1answer
30 views

Fix deleted transaction log file in SQL Server

I have made the biggest mistake and deleted the transaction log file. Is there a way to fix this issue? I have the backup file, is there a way to restore it or reset everything again?
3
votes
3answers
84 views

How to use IN clause in sql server so that it selects everything if nothing is provided for its list argument?

Assume I have a sql query like this SELECT * FROM TABLE where country in ('USA', 'UK') This query works as expected that is it selects all rows where country column is either 'USA' or 'UK'. Now ...
8
votes
3answers
910 views

High tempdb disk I/O during merge replication of BLOBs

Having a merge publication for replicating BLOBs (type image), got very high tempdb disk I/O for my size of data. Publication is download-only and has no filters. High disk I/O is caused by ...
0
votes
1answer
62 views

single or multiple databases advice needed

I know there are a few of these posts, but not in relation to my specific quandary. I've always gone the 'per user' SQL Server DB rather than 'one DB for all' because: a) I thought speed wise it's ...
7
votes
1answer
551 views

Why is my Azure SQL (SQL Server) database getting overloaded with data IO for periods at a time?

I am running an Azure SQL database under the S2 edition (50 DTUs). Normal use of the server usually hangs around 10% DTU. However, this server regularly gets into a state where it will send DTU usage ...
0
votes
0answers
36 views

Best way to loop through mulitple rows for an entity in a table with many entities — SQL Server

I have a table in my SQL Server DB that is sturctured as Entity_ID varchar(255) Production_Date date ProductValue float The table is indexed by Entity_ID and Production_Date. The dates are one ...
3
votes
0answers
39 views

What Triggers sp_recompile in database without triggers, stored procedures, etc?

In a database with only tables and indexes (ie no stored procs, functions, triggers, etc). We get a call to sp_recompile that blocks table selects / updates randomly (ie once a month but in a highly ...
92
votes
8answers
15k views

Does SQL Server read all of a COALESCE function even if the first argument is not NULL?

I'm using a T-SQL COALESCE function where the first argument will not be null on about 95% of the times it is ran. If the first argument is NULL, the second argument is quite a lengthy process: ...
0
votes
2answers
339 views

Converting NVARCHAR to VARCHAR

I have several large tables with many NVARCHAR(MAX) columns. This database is only for US use and I don't think there are any foreign characters or letters in any of the columns so I wanted to look ...
0
votes
0answers
49 views

High CPU and Memory usage on SQL Server 2008 R2 [on hold]

There is high CPU and Memory usage for the last three days on one of SQL Servers I support. I checked sp_whoisactive, there is no long running query that is consuming much CPU and memory, however I ...
1
vote
2answers
83 views

How to stop the SQL Server Agent Job Scheduler with T-SQL?

I have a job that I manually start in coordinated time periods that does a bunch of heavy lifting (mostly restoring backups and configuring for a different server) There are several jobs scheduled to ...
1
vote
0answers
32 views

Unable to join secondary replica to the availability group by SQL Server error 47106

We were trying to create availability group with 3 synchronous replicas, where we couldn't give the names specified in /etc/hosts as node specifiers in the create availability group in the primary ...
0
votes
1answer
27 views

`Encrypt Connection` causes an invalid certificate error even though server-side encryption is forced

I have a SQL Server set up to force encrypted connections from all client. It has a standard SSL certificate, using Let's Encrypt as the CA. Browsing to the server via a web browser verifies that the ...
1
vote
1answer
562 views

Generate DDL Statement from XSD file

I have a database schema exported into XML format. My goal is to create a DDL statement from the XSD files that I can use to import the data into a different platform without manually having to ...
4
votes
1answer
275 views

How to get sum & latest activity in same result?

Table_1 Name | Activity | LogTime A | 0 | 2018-12-17 10:16:04.877 A | 1 | 2018-12-15 10:16:04.877 A | 0 | 2018-12-16 10:16:04.877 A | 0 ...
0
votes
1answer
23 views

Update badly formed XML to allow parsing

I have inherited a production system that needs some fine tuning to enable easy extract of the result set. A record gets added to a table which contains an XML field, with contents similar to the ...
2
votes
1answer
46 views

Could a busy database affect others on the same server?

I have a very busy database with lots of reads and writes. I want to create another database on the same server, but I am wondering if that busy database will slow down the performance of the second ...
1
vote
2answers
575 views

SQL Server on ssd seems slow

We recently moved SQL Server to SSD and the performance gain is not that great. SQLIO shows enormous gains (15-100 times faster), getting sequential read/write speed up to 6GB/s. But when we do a ...
0
votes
0answers
347 views

performance impact of using synonyms

I have 2 databases that reside on the same server. I am trying to read static data from one database in another database using synonym. And this caused no issues since we are not even using link ...
19
votes
8answers
49k views

A query that lists all mapped users for a given login

When looking at the properties of a particular login, it's possible to see a list of users mapped to that login: I profiled SQL Server Management Studio (SSMS) and I see that SSMS connects to every ...
2
votes
2answers
605 views

How to flatten and pivot a normalised hierarchy table

I have an OrgChart table that looks something like this: PositionCode PositionName ParentCode Level AA0001 CEO 0 AA0002 CFO AA0001 1 AA0003 ...
0
votes
0answers
34 views

How to debug database server freeze (not accepting connections, timeouts etc)

Apologies if this question is a little bit vague but we are having issues for a couple of days now and have no clue on what may be wrong. We have a pair of Windows servers with SQL Server installed ...
4
votes
1answer
440 views

mssql server set up fail with errror on debian linux

I was trying to install SQL server on my Debian Linux. Installation part went well but when I tried to run setup file I am getting following error message. Is there any way to fix this issues. Thanks ...
3
votes
1answer
385 views

Table size increases after setting nullable column to null

I'd like to reduce database size by removing some data that is not useful anymore from a SQL Server Express database - 10 GB database size limit. So I have a table that is 5783 MB and has 4 columns ...
0
votes
1answer
86 views

Migrate Database

I'm trying to migrate my database to another server, an older version. Currently my database is on SQL Server 2014, but I need to add migrate it to SQL Server 2012 because that's the one my server has....
2
votes
1answer
21 views

Is CREATE EXTERNAL DATA SOURCE Azure PaaS elastic databases broken?

I have set up Azure elastic database queries before by using the following on Azure PaaS databases: On the data source database: CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<encryption key>'; ...
-1
votes
1answer
35 views

Subtract column data from two unrelated tables

this is my database diagram i want to determine blood remaining in each bank i am having a problem because the tables donation and transfusion are not related how i can subtract? i have created ...