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.
23,892 questions
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 ...
7
votes
1answer
178 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);
...
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 ...
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.
-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 ...
-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 ...
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
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 ...
-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,
...
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
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
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?
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 ...
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 ...
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 ...
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 ...
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 ...
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 ...
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
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
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 ...
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 ...
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 ...
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 ...
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>';
...
0
votes
1answer
29 views
Kimball Fact Table have both Begin and End date?
We are creating Kimball Data warehouse, conducting Valuation and permits for Property in the Northeast cities.
Quick sum up:
create table dbo.FactValuation
(
FactValuationId int primary key ...
-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....
1
vote
0answers
38 views
Getting attended, absent and no class days from database attendance
Problem
the problem is that I am able to get the days instructors record attendance and only that, I am unable to get the days they didn't make attendance and the days the instructors were not ...
-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 ...
6
votes
1answer
124 views
How to split XML array into separate rows (while upholding consistency)
I am working on the database dump of this exact stack exchange section. While I am working on it I have encountered one issue that I am currently unable to solve.
In the XML File Posts.xml the ...
2
votes
0answers
24 views
Data Warehouse Kimball Interest Rate and Interest Payment Amount
I am modelling a bank account.
Should interest rate be in the DimCheckingAccountId (which shows ProductDimensions, or should it be in fact table, or both). Same with another LoanAccount, should ...
0
votes
1answer
26 views
How to Use OPENROWSET function within a Transactional SSIS Package
I have an SSIS Package and one of its components is "Execute SQL TasK"
and I've written an OPNEROWSET query inside this task. When I configure the transaction option of the whole package from "...
0
votes
2answers
38 views
Will TDE encryption protect my SQL database from hacker in this scenario
I'm a bit understand that TDE encryption protect the database from being stolen and read by a hacker. However, I don't understand if it can help me in this scenario: If a hacker got into my server and ...
-1
votes
0answers
27 views
Set Default Date Format in SSMS Query Results
Basically looking for an equivalent to Oracle's:
ALTER SESSION SET NLS_DATE_FORMAT = '[date_format]';
This stems from nearly having a nervous breakdown from years of Excel formatting DateTime cell ...
2
votes
1answer
82 views
what does a “/” mean after a sql statement
I have a script that we are using to update our ERP database to a new version,
UPDATE ENUM_CODES SET COLUMN_NAME = 'PAY_STATUS' WHERE TABLE_NAME = 'PAYABLE' and COLUMN_NAME = 'STATUS'
/
but i dont ...
0
votes
1answer
40 views
Correct Model for Users in a Collection with Items in that Collection
Sorry for my vague title, but I didn't really find how to succinctly formulate my issue. I have the following story I want to model:
There are Users that belong to a certain Group. These Users can ...
0
votes
1answer
25 views
Replication stalling on one subscriber
I currently have transactional replication set up for three databases from the same Publisher to the same three Subscribers in SQL Server 2005. Publisher and Distributor are on the same server.
One ...
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 ...
5
votes
2answers
890 views
How many rows can SQL Server process in a single INSERT statement into a table?
To illustrate my question, the following is a query detecting order's id which have not been inserted in a data warehouse and inserts them:
With NewOrders
As
(
Select OrderID From Orders
...
0
votes
0answers
15 views
Log files not created while pentaho job run
I have to get data in my Salesforce org from 3rd party database via Pentaho job run process. Where I was getting error in generating log files so I enabled HOP, now job is running but logs files are ...
5
votes
1answer
76 views
Find groups with exactly one value: COUNT(DISTINCT x) = 1 vs MIN(x) = MAX(x)
Given this data:
gid | val
1 | a
1 | a
1 | a
2 | b
3 | x
3 | y
3 | z
the following queries return groups (gid) that contain exactly one distinct value (val):
SELECT gid FROM t GROUP ...
3
votes
1answer
62 views
SQL Function, get value to send to CLR
I have a CLR to use the powershell to have access to the RichTextBox Object from .net.
On the SQL Function I have created like this:
CREATE FUNCTION [dbo].[FicheiroChines](@rtf [nvarchar](max), @...
4
votes
1answer
79 views
High processor utilization when running a stored procedure
I have a stored procedure which was running fine till 12/11 with average execution time under 2 hours. From Tuesday (12/11) onward, the stored procedure is running continuously for more than 3 hours ...
1
vote
1answer
20 views
SQL Server - Select parent row or child row if no parent
I have a single table, Issue. This table has Id and ChildId columns.
=============
Id | ChildId
1 | NULL
2 | 3
3 | NULL
4 | 1
5 | NULL
6 | NULL
=============
I need to select from this table ...
0
votes
1answer
41 views
Auto incrementing a foreign key value the same as the primary key of the parent table in SQL when inserting data?
I have the following two connected tables as shown below in the Microsoft SQL Server.
When inserting data from visual studio, the foreign key in the Reservations table (Customer_ID) should be auto ...
1
vote
0answers
39 views
Plan compile time almost the same resulting in same plan creation, but no plan reuse
Issue:
Queries are being executed fast after eachother, but plan reuse is only happening for 1 or a few executions. Many of these queries and query plans are found on the server.
Result of this is ...
0
votes
1answer
25 views
Why the concept of missing index groups is provided in SQL Server?
I know about missing indexes, and that each index belongs to a missing index group which has a unique handle identifier. I want to know why groups are useful when we can just deal with individual ...
