Questions tagged [sql-server]
All versions of Microsoft SQL Server (not MySQL). Please also add a version-specific tag, like sql-server-2016, since it is often relevant to the question.
34,097
questions
0
votes
2
answers
77
views
Can you do an update of a column while selecting for the same column
In MS SQL I am doing this - I think it's working correctly but want to check.
UPDATE BillingInvoice
SET CustomerId = Customer.CustomerId
FROM Customer
WHERE BillingInvoice.CustomerId = Customer....
0
votes
1
answer
129
views
sp_Blitz shows "write_conflict" from In-memory OLTP
My team came across a In-Memory OLTP (Hekaton) transaction issue with several of our machines showing that there were a varying number of write conflicts per machine. We do have Memory-Optimized ...
0
votes
2
answers
252
views
Order of Key Columns in Index
The execution plan for this query appears to be making a very bad choice (of course I know it is not):
SELECT TOP 1 E_PER_ID FROM E WHERE E_CUS_ID = 1912 AND E_TYPE = 'R' ORDER BY E_ID DESC
The two ...
0
votes
1
answer
559
views
SQL Server SSMS - Edit 200 rows functionality - is it possible for two or more people to alter each other's work using this functionality?
Let's consider a scenario:
Two or more people are working on a table that stores configuration data for an app. There are multiple configurations in that table, so each person can work on it's own ...
0
votes
2
answers
2k
views
WHEN run query on SQL Server, it is very slow. How to solve execution plan issues?
I work with SQL Server and face an issue that a query is very slow.
INSERT INTO [ExtractReports].[dbo].[TradeCodesDelete] (PartID,
Code,
...
0
votes
1
answer
386
views
Scheduling Next Run date for weekly agent jobs
On SQL Server I used maintenance plan wizard to create three jobs:
Job
Schedule
Job 1
Every 3 week on Sunday
Job 2
Every 3 week on Sunday
Job 3
Every 3 week on Sunday
I want the three jobs to run ...
0
votes
1
answer
461
views
Can not enable lock page in memory in SQL Server Express edition
For enabling Lock Page In Memory, I tried to do this: first of all I find my SQL Server account name with this code:
SELECT service_account
FROM sys.dm_server_services
This code returns:
NT Service\...
0
votes
2
answers
479
views
SQL Server Try_Cast returns Null value
Related Questions
,TRY_CAST(Question_Text AS xml).value('(/p/text())[1]','nvarchar(1000)') AS Question_Text2
With the help of CharlieFace on my previous post I discovered the use of Try_Cast ...
0
votes
2
answers
501
views
Concerns with dynamically calling stored procedures inside a stored procedure?
We have a need to have a single stored procedure that will dynamically call one of over hundreds of stored procedures. Our concern is that this may cause issues with the plan cache and suffer from ...
0
votes
1
answer
68
views
Can Availability Group be configured in a below structure?
I have Production and DR clusters as depicted on a picture below.
My question is whether there is a way to configure Availability Group from datacenter1 to datacenter2?
I tried to configure regular ...
0
votes
1
answer
2k
views
Generate random password using a T-SQL function
I have this code in PLSQL but I am unable to convert it to SQL Server.
I want to generate a random password that contains a mixture of upper and lowercase, numbers, and special characters in T-SQL ...
0
votes
3
answers
724
views
How to select sys.views of another database from master databse?
I know I can get views metadata using sys.views system catalog view. But now I want to create a centralized function in my master database to be called from other databases.
For example, I want to ...
0
votes
1
answer
208
views
Server name contains port
One of our clusters returns the port number as part of the Server Name. See example below
SELECT SERVERPROPERTY ('ServerName')
-------------------------------------------------------------------------...
0
votes
1
answer
442
views
How to query database integrity check duration time (how much time did it take to complete dbcc checkdb) [duplicate]
How to know how much time did it take to run database integrity check for all databases on a instance.
I simply need when it started and when its finished.
I am able to query when the last check db ...
0
votes
3
answers
360
views
SQL Server TDE - encryption_state 2 stuck at 100% complete
I have enabled TDE on two databases.
One is around 700 GB and that completed over the night and has encryption_state=3
The other one is almost 2 TB. That one has encryption_state=2 and ...
0
votes
1
answer
159
views
Is there a way to customize SQL Server Performance Dashboard?
The report in SQL Server Management Studio presents System CPU Utilization broken out by a minute. Is there a way to specify the interval - perhaps by 10 seconds?
0
votes
1
answer
199
views
sp_BlitzCache and SPID
I'm using sp_BlitzCache and it's very helpful, I ran this command:
Sp_BlitzCache @SortOrder = 'duration';
GO
How do you relate the query with a SPID as I found the query with the longest duration? So ...
0
votes
1
answer
246
views
Modify the IO cost computation formula in MS SQL Server to reflect negligible SSD seek-times?
The query optimizer of MS SQL Server seems to use a fixed formula for estimating IO costs. This formula seems to be based on a fixed value, reflecting the seeking time of a magnetic hard disk, plus a ...
0
votes
3
answers
2k
views
Storing huge file collection in database
I'm in a planning phase of developing a web site where public users will be able to store their documents, by nature it could be anything like documents, pdf, audio, video or images.
We are expecting ...
0
votes
1
answer
308
views
On premise to cloud restore - Automated?
What's the most automatic way to restore a on-premise SQL Server database to Azure SQL Database?
Has anybody done or seen such a solution?
Scenario:
Need to do a weekly refresh of a dev database from ...
0
votes
1
answer
4k
views
How to use PowerShell Invoke-Sqlcmd to query and table and update a PowerShell $variable?
How do I update a PowerShell variable using an Invoke-Sqlcmd statement?
Eg.
$PSVariable
Import-Module sqlserver
$QueryStatement = "SELECT $PSVariable = Max(Data) FROM MyDatabase"
...
0
votes
1
answer
48
views
SQL Server failover
I have an in-house server running multiple applications which uses MSSQL as a DB. I have two public IPs. I have purchased a failover service from Azure, so when my one ISP goes down, it points the ...
0
votes
2
answers
435
views
Do wide included column indexes affect inserts?
I had to create indexes with included columns due to high I/O, nested loops joins, and key look ups that blocked inserts:
SELECT * FROM Table WHERE (Column1 = 'ED69K')
I created an index on ...
0
votes
1
answer
97
views
Using parameter with cursor in stored procedure
I'm using SP that gets date as parameter, I'm trying to use the varable inside the query but SQL doesn't seems to "see" it
alter procedure PipeReport
@1 as DATE
as
begin
DECLARE @SqlCD ...
0
votes
1
answer
10k
views
An expression of non-boolean type specified in a context where a condition is expected, near 'AND' [closed]
ALTER PROCEDURE [dbo].[usp_BaseScan_CycleCount_Report]
@StartDate varchar(10),
@EndDate varchar(10)
AS
BEGIN
IF @StartDate AND @EndDate IS NOT NULL
SELECT *
FROM dbo....
0
votes
1
answer
1k
views
Linked server only works if I am on the machine itself. I can't use it from my local computer. what is missing?
I have a few queries that I use to monitor log shipping. Sometimes I want to run those queries on a different server. I usually do that through a linked server.
I have a server called SQLDEV4-TS that ...
0
votes
1
answer
289
views
How to reduce the maximum degree of parallelism at session level?
I have inherited a legacy application that includes a job that creates performance issue at instance level. The DBA told us about this and our team needs to fix the stored procedure that is the most ...
0
votes
1
answer
352
views
Return Current Connection CName
I'm wondering if anyone knows if there is a way to return the name that was used to connect to the SQL server rather than the actual server's name. For instance I typically connect to the server via a ...
0
votes
2
answers
392
views
Storing Live Timeseries Data in SQL Server Azure Database
I have an SQL Server Azure Database and am trying to store time-series data that I get from multiple sensors.
Data Source:- 5-minute data is obtained via an API.
Current table structure: -
Timestamp |...
0
votes
2
answers
509
views
How to "normalize" external data before insertion into "Data Warehouse" like database?
I want to be database agnostic but let's say we're inserting data into SQL Server which is to be used as a data warehouse.
There are MILLIONS of email records from an email platform that wasn't too ...
0
votes
2
answers
480
views
Total logical reads per database on SQL Server
Is there a way to get the total running logical reads\writes per database from last restart? Trying to monitor what dbs are most used and if that changes over time.
0
votes
3
answers
691
views
SQL Server - Is there a way to query the specific Size and Space available settings for each Database, including system databases on an instance? [duplicate]
I am trying to query the two default database properties as seen in the picture below for all databases on an instance, including system databases.
I've used various scripts such as the following, but ...
0
votes
1
answer
60
views
I am wanting to use UDTT in my database, but we have CLR turned off, and according to the docs for create type, all types require a CLR assembly
Is this true? Or can I use User defined Table Types with having CLR off? (I.e. they don't require the use of CLR)
If they do require CLR, is there an alternative I can use for my stored procedure ...
0
votes
3
answers
626
views
Space consumed by a column in non clustered index
I m planning to add a key column index in one of the existing non clustered index in a big table which have millions of rows rather than create a new non clustered index. Is that any way for me to ...
0
votes
1
answer
565
views
SQL Server - Round a number when it's one decimal before .50
I would like to know if there is a way to round a number if it's one decimal before 50.
i.e,
I am using the below code and it works, it gives 8.50:
select Round(8.49,1) = 8.50
The problem comes when ...
0
votes
2
answers
89
views
SQL Server - Overlapping NC Indexes, Drop Question
I'm doing some index cleanup/tuning on a SQL '14 DB and ran across a 6MM row, 16 field table with a sound-looking clustered index and two nonclustered, non-unique indexes having the following ...
0
votes
2
answers
16k
views
How to Duplicate Column in same table in SQL
I'm pretty basic with SQL and need help with the following task:
I already have a table with data in it. What I would like to do is to Duplicate a Column (Including the data) in the same table but ...
0
votes
1
answer
4k
views
Add a column at a specific position in system versioned temporal table
I need to add a column to a temporal table. The Microsoft documentation only suggests appending the new column using the following code:
ALTER TABLE dbo.Department
ADD TempColumn INT;
However, ...
0
votes
1
answer
2k
views
SQL Server 2019 (RTM) - 15.0.2000.5 period has expired after upgrade to CU 8 [closed]
Hi i have MS SQL with version
Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64) Sep 24 2019 13:48:23 Copyright (C) 2019 Microsoft Corporation Enterprise Evaluation Edition (64-bit) on Windows ...
0
votes
2
answers
934
views
A slow query on MS-SQL-Server 2019 cannot be optimized
In the following thread I received some important and valuable information on how I can improve a slow query on MS-SQL-Server 2019.
However, that didn't do very much.
First I deleted all non-clustered ...
0
votes
1
answer
587
views
Is the BACPAC from a TDE encrypted DB also encrypted?
If you create a BACPAC of an encrypted database, will the data in the BACPAC be encrypted?
We're currently using on-prem SQL Server and use TDE to encrypt our customer databases. We also have a ...
0
votes
2
answers
227
views
How to query cached plans that contain a specific missing index?
I see a potential missing index for a table but would like to first query all cached plans that have that specific missing index (so I can later test those queries after the new index is built). How ...
0
votes
1
answer
67
views
Log files in Always on Disaster Recovery Situation
In my Always on Avaliability group setup there are four nodes in the cluster in two datacenters, an avaliability group has for replicas, one on each node.
Two replicas in the same datacenter have sync ...
0
votes
2
answers
171
views
Clarifying delayed durability
I'm considering applying delayed durability in a system I work on and want to make sure I have the correct mental model in order to avoid data loss. My understanding is it basically means COMMIT ...
0
votes
3
answers
461
views
Safe and secure implementation of logon trigger in SQL Server 2014 Express edition?
I've to implement the following requirement:
Access to SQL Server instance shall be allowed only from a C# application.
Users shall not be able to access any database (even those in which they have ...
0
votes
1
answer
521
views
Ola Hallengren DataBaseBackup to URL results in un-equal file sizes causing IO Device Error
we have been using Ola's Maintenance Solution and Database backup direct to URL since we migrated our system to Azure IAAS. We have multi terabyte DB's (approx 40% Filestream data) and therefore ...
0
votes
4
answers
199
views
Have to run same queries against many servers (30+), use CURSOR or LOOP
I have to do the following 30 times
connect to server with unique IP using TSQL
download a table to temp table
do some query on the temp table (same query 30times)
at end union all the tables for a ...
0
votes
1
answer
252
views
Is columnstore a good option for UPDATES with WHERE clauses?
I am developing a database project on SQL Server and I am thinking whether using columnstore index is a good idea.
The project consists of a table (A) that will hold a large number of rows, with many ...
0
votes
1
answer
123
views
How to optimize and estimate DB recovery times?
Would like to optimize recovery time for db's after failover to passive node in high availability cluster.
Assuming full recovery model...
What are the steps to take to optimize DB recovery time for a ...
0
votes
2
answers
227
views
EMP_DEPT database. Try to SELECT records with smallest salary, NULL column record is missing
SQL Server 2017: I have the EMP_DEPT (SCOTT schema) database with a few additional records inserted. Two of them have ZERO base salary (PORTMAN, RENO) and one employee (MCQUEEN) has NULL in the salary ...