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.

Filter by
Sorted by
Tagged with
0
votes
2answers
40 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.
1
vote
2answers
29 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
32 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
32 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
20 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
1answer
17 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
43 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
54 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
39 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
3answers
44 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
36 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
33 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
31 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
40 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
16 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
141 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
576 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
30 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
189 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
23 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
200 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
25 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
31 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 ...

1
2 3 4 5
599