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.
29,901
questions
0
votes
2answers
27 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
0answers
16 views
How can parse column names using ordinal position (not name) from XML data in SQL Server?
I have several hundred tables in a database that have the same structure:
SomeId, Pos, Varying Number of Other Fields
So, for example, one table may look like this:
PersonId, Pos, Hobby, Degree
12345, ...
1
vote
1answer
26 views
Use CTE to fetch most recent row for each ID
I just recently started learning about the wonders of CTEs and how they can be used, instead of WHILE loops, to optimize performance.
For context, I am automating the process of updating firmware on ...
-3
votes
0answers
26 views
Doctors appointment
practitioners
Id (PK) 1 2
name Dr-1 Dr-2
schedules
Id (PK) 1 2
practitioner_id (F)K 1 1
start time 8:00 17:00
end time 13:00 21:00
Duration int 30 30
fee int 700 700
appointments
Id(PK) 1 2
...
0
votes
0answers
16 views
Appending another column onto an existing UNION
I have the following query- I am trying to add another column, however I don't want any of the "WHERE" clauses from the previous 2 SELECT statements to take affect... so I can't just input ...
0
votes
0answers
3 views
ALTER COLUMN NOT NULL, not enough space in the log file
I am trying to run the following DDL
ALTER TABLE [dbo].[table_name] ALTER COLUMN [Value] [float] NOT NULL
This generates a large amount of log file data, and there is not enough space for it on the ...
0
votes
1answer
39 views
Are there any things to consider before granting VIEW SERVER STATE and VIEW DEFINITION to Developers?
I would like to grant below to permissions to Developers' group, on Production SQL Server:
VIEW SERVER STATE
VIEW DEFINITION (server level)
This is done to make them able to query some of dynamic ...
0
votes
2answers
49 views
Strange behavior of TOP 1 clause sql server
I have a SQL Server database and restored it on another server on a different machine. Now I run following same query on both DBs which produces same below result set.
select person_id, visit_id, ...
0
votes
1answer
30 views
Use previous state of data
I am trying to carry forward the previous state of data wherever data is not available. For example, I have following data;
Month
Order
Amount
1
Item1
100
1
Item2
200
1
Item3
300
2
NULL
NULL
3
...
0
votes
2answers
37 views
How to set constraint sql 2 colume, Data is not available elsewhere and must be used only
How to set constraint sql 2 colume, Data is not available elsewhere and must be used only.
Example is OK
NAME
REV
ID
PI
A
0
1
PA001
A
1
1
PA001
Example is Error
NAME
REV
ID
PI
A
0
1
PA001
A
1
1
...
0
votes
0answers
35 views
General question on building a database
Sorry if this question is too general or if it sounds dumb. I have no background in database, but am in need of some help.
Here is my problem:
I need to build a database for a series of stream flow ...
0
votes
0answers
32 views
How to adjust memory grants via hints
We are having issues on our READABLE secondaries with lots of waits around RESOURCE_SEMAPHORE
We have approx. 800 GB set as MAX server memory setting
Our ongoing task is to fix those SELCT queries but ...
2
votes
2answers
30 views
In SQL Server, what's the impact of a checkpoint to log records in log buffer?
Does checkpoint operations flush everything in the log buffer to log file? Or just the log records relating to the dirty pages that are about to be flushed? I found some inconsistent description about ...
0
votes
1answer
39 views
Index Reorganize fails
I am using the default SQL Server Maintenance task to reorganize index only which was running until I got the below error.
Executing the query "ALTER INDEX [PK_abc] ON " failed with the
...
0
votes
3answers
39 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?
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
1answer
15 views
CREATE FUNCTION inside BEGIN TRY and END TRY
I want to use CREATE FUNCTION in a block that will "swallow" errors, but this
BEGIN TRY
CREATE FUNCTION test (@ID int)
RETURNS int
AS
BEGIN
RETURN(2 * @ID)
END
END TRY
BEGIN CATCH
END ...
0
votes
1answer
20 views
Implement AKA in SQL Server Master Data Services
I'm working with a bunch of scientists and we're trying to implement a master data solution for the ingredients they use in formulations. The problem with their historic data is Scientist #1 will use ...
3
votes
1answer
140 views
Azure SQL Database throwing error 1132 “The elastic pool has reached its storage limit” error, even if it's not full
I'm trying to execute the following (sample) batch in an Azure SQL Database inside an Elastic Pool:
drop table if exists [dbo].[InsertTest];
create table [dbo].[InsertTest] (
[id] uniqueidentifier,...
0
votes
1answer
30 views
Low Procedure cache
On a SQL Server instance I have, at times the Procedure cache drops quite considerably. Down from 5GB to 3GB and on the rare occasion there in something in the workload which causes it to drop down ...
0
votes
1answer
19 views
Cant delete a user
Before exporting to azure, it is required to delete all users. There is one user I cant dele because it says that the user owns a schema. I can not find that this user owns a schema. where do I go to ...
3
votes
4answers
572 views
Why changing the column in the ORDER BY section of window function “MAX() OVER()” affects the final result?
I have a table with below structure and it's data :
create table test_table
(
Item_index int,
Item_name varchar(50)
)
insert into test_table (Item_index,Item_name) values (0,'A')
insert into ...
-1
votes
1answer
36 views
Does using PK and FK effect performance in negative way?
Few companies I noticed do not use PKs and FKs. I don't understand the logic of not using Clustered Indexes and I hear some say it can cause issues because FK couples records together and takes up ...
1
vote
1answer
32 views
Need help merging multiple full SQL Server Backups into single database
Got a really strange issue with SQL Server database backup files I am getting backup files from the state of California for petroleum production. Each year a database with the same name is created ...
0
votes
1answer
27 views
Read-Scale Availability Group between a Transactional subscriber and another machine
I am trying to configure Read-Scale replica between a transactional subscriber and a 3rd machine. I could create the group. But, when i attempted to add the DB (which is a transactional - subscriber) ...
1
vote
1answer
33 views
How Restore backup as overwrite on a database in use in SQL Server?
I Want to restore a backup with overwriting on a database in use
for this problem I want to create a query base on these steps :
1- Remove All Connections [MyDB]
2- Create a backup from [MyDB]
3- ...
1
vote
0answers
29 views
delete or rollback an insert if a trigger on insert fails
is it possible to delete or roll back insert statement if a trigger on that insert statement fails due to some reason.
for eg i have a trigger on insert into employe(id,name) values(1,'ali')
trigger
...
1
vote
1answer
28 views
Does SQL Server upgrade to table locks at total locks or session locks?
I’ve a table that both has mass sequential inserts at the end of the CI and random (very distributed) reads+updates. Naturally, the mass inserts should not block the random access. RCSI is used, so ...
0
votes
0answers
24 views
Non cluster indexing with combination or on single entity for multiple columns
I am new to db design I also read concepts of non cluster indexing and know about the combined non cluster indexing but in my scenario I am having user table with multiple search on column
table ...
1
vote
1answer
39 views
Can I restore my SQL Server database to a point before the last full backup with my current backup strategy?
If I have this backup strategy(weekly full backup and 1 hour log backup), can I restore the database to the green highlighted period of time? Btw, does log backup 2 includes all log records that have ...
-1
votes
1answer
45 views
Check if contract conflict with another contract query
I have a database with these tables as the bellow picture showed:
The user can create a new contract each contract may have one or more shops and these shops status changes depending on the contracts,...
0
votes
2answers
56 views
Subqueries in SQL Server
I am taking a SQL course and am completely stumped on this question.
Using a subquery have a query that returns:
CustomerID, EmailAddress, FirstName, LastName, '#3: Customers who have ordered than 1 ...
-2
votes
2answers
39 views
How to Cast today data as bigint to Data - 202104171830
I have got today's data as Bigint: 202104171830. How could I cast this Bigint value to today's date ? Please help. MSSQL
3
votes
1answer
187 views
What's the difference between equality (=) and inequality (<>) on BIT field in SQL Server?
Is there difference between WHERE [BitField] = 1 and WHERE [BitField] <> 0?
They seem logically equivalent.
SQL Server, however, disagrees and creates slightly different execution plans. (Which ...
2
votes
2answers
78 views
How to make large “in” queries in C# for an indexed field in MSSQL?
In mssql, how can I run effectively this query:
select id from table where field in (gigantic list of ordered longs)
Table schema:
table:
{
id primary key
field long
} index findex (field)
I ...
1
vote
1answer
24 views
Invalid Column Name on CONCAT_WS
Sorry if this seems like rambling I'm not typically a DB person just trying to work through an issue.
I have two tables cases and legal_entity the data on the two tables looks like this
legal_entity
...
0
votes
1answer
22 views
Query Store System View JOINs
In SQL Server 2016, is there an optimal/correct way to JOIN sys.query_store_query_text and sys.query_store_query (joinable using query_text_id) with sys.query_store_runtime_stats? I don't see any ...
0
votes
0answers
22 views
SQl Server log shipping monitor security configuration and update problems
I'm setting up log shipping between instances A and B with the instance C as a monitor. The shipping itself is working fine but I'm somehow unable to make the monitoring work. The log shipping status ...
4
votes
1answer
198 views
No parallelism in Express Edition of SQL Server
I have SQL Server 2019 Express Edition (CU8) on Windows Server 2019 (1809 version) and all of my queries go serial with NoParallelPlansInDesktopOrExpressEdition in NoparallelPlanReason property.
Is it ...
0
votes
1answer
23 views
How to test user permissions
I have two server users, lets say admin and notadmin. I need to test notadmin's permissions. I need set and test VIEW SERVER STATE permission on notadmin, but usually I have access only to database ...
-1
votes
2answers
30 views
Migrate bak-file to older version of SQL server using command line [duplicate]
I want to copy a bak file from sql server 2019 and restore it to a sql server 13.0.5026.0.
The conversion from one version to another that I have seen this far is using a wizard.
However, I need to do ...
2
votes
1answer
201 views
Data files half empty in SQL Server. Is that a problem?
I have a multiple TB database and I have been doing some clean-up and dropped many tables. So now the data files are half empty. If I don't care about releasing the space to the operating system, is ...
1
vote
1answer
24 views
Reduce transaction log file utilisation
I have a SQL Server 2017 database that currently has an 80GB data file and a 90GB log file. I want to get the log file down to a more manageable size, because I am about to run some big scripts which ...
0
votes
0answers
30 views
Set SPN Service principle name for SQL Server Service account - Best practice
I am looking for guidance on Microsoft recommended best practice regarding setting the SPN for the service account that runs the SQL Server service (MSSSQLSERVER)
When setting up a new server (or ...
0
votes
1answer
23 views
What is the most performant way to group by and join high cardinality columns in column based table?
I currently have a SQL Server, 20 column fact table that could grow even wider into the hundreds. The current row count is about 9 million records. The indexing strategy is currently row-based. The ...
0
votes
0answers
30 views
TempDB started constant disk IO
At around 10am yesterday I started seeing constant read/write activity to the TempDB database via our RedGate SQL monitor.
This is unusual because usually our disk operations are <100kb/s however ...
0
votes
2answers
62 views
SQL Server Management Send Mail as Part of a Job
I have a SQL Job that I would like to send an email through, but for some reason I can't get any details on why the email is not sending. This code works as expected when executed in the query window, ...
1
vote
0answers
34 views
How do SSIS connection strings send passwords?
I have a SQL server that pulls data from another SQL server via SSIS. We recently converted the server targeted by SSIS to VM. Since the VM conversion I have been getting "login failed for user '(...
0
votes
1answer
31 views
Why is SELECT USER_NAME() returning my windows credentials and not the database user mapped to a windows security group that I am in?
I am trying to implement Row Level Security and have mapped an AD Security Group to a user in my test Database. When trying to select data from a table that I have granted access to I am not getting ...
-1
votes
0answers
20 views
How to estimate run time for for moving clustered index to new filegroup?
Need to move 3 semi-large (millions of rows) related tables with clustered indexes to a new filegroup on the SAN.
Is there a way to estimate how long dropping / recreating clustered index will take? ...
0
votes
0answers
21 views
SSIS package run error indicates wrong credentials after VM migration
First, I am not a SQL DB admin. I inherited this system and working to migrate away from it...that said:
I have an old MSServer2000 SQL DB we migrated to VM. That server offloads data to another SQL ...
