Questions tagged [azure-sql-database]
Azure SQL Database is a relational database-as-a-service from Microsoft. This tag is for questions about Azure SQL Database, NOT SQL Server hosted in an Azure VM (use azure-vm).
950
questions
1
vote
1
answer
37
views
Azure SQL Database Elastic Pool Resource Limit Exceeded - How to Resolve?
I'm encountering an issue with my Azure SQL Database where I'm receiving the following error:
System.Data.Entity.Core.EntityCommandExecutionException: An error occurred while executing the command ...
- 111
0
votes
1
answer
54
views
How can I copy a database schema from my local Sql Server to Azure SQL Database
I figured the way to do this was to go to my existing database (Sql Server 16.0.1105.1 developer edition) in SSMS and run Tasks | Generate Scripts...
This includes a FullTextTable. So I then got the ...
- 171
0
votes
1
answer
24
views
How do I change the password for a login on Azure SQL Database via SSMS
I'm in SSMS connected to my SQL Database on Azure
I right clicked on the login - no properties choice in the menu
How can I change the password for a login?
- 171
1
vote
0
answers
25
views
Azure Elastic Job - a stored procedure does not run on the target database
The target group NPBlue contains 3 databases
NPBlue 882E2367-93C0-46CC-A4ED-90AE5F2860EB Include SqlDatabase 69B479D8-34FA-48A6-9A3D-6B302864B9AD NULL NULL NULL x-database-weu.database....
- 11
0
votes
0
answers
48
views
Failed to connect to Azure SQL Database using ODBC Driver 17 with Authentication=ActiveDirectoryMsi
I was trying to connect the Azure Container Instance to my Azure SQL Database using Azure Active DIrectory User Managed Identity. But I can't establish a connection using ODBC Driver 17.
Error Message:...
- 1
0
votes
1
answer
63
views
Reducing Fragmentation on a clustered columstore index
I have a database with a clustered columnstore index on it. The table has 1,685,123,846 records in it.
I use the ola hallengre script monthly to rebuild the index, but it takes so long to run before ...
- 101
0
votes
1
answer
277
views
Grant VIEW DATABASE STATE on master DB
I am asked to provide access to VIEW DATABASE STATE in the master DB of an Azure SQL Server instance (Premium P6) to an external user (vendor) in order some investigation to be done.
When run "...
- 1
0
votes
1
answer
76
views
Best solution for storing metadata and data from documents?
First of all do I want to mention that I'm fairly new to databases and big data.
Background:
I'm working for a large company who wished to unify all their documentation.
At it's core of my project am ...
- 101
0
votes
2
answers
74
views
How does SQL Server row-level security impact foreign key relationships?
I have a multi-tenant application sharing a single Azure SQL database using row-level security. The security predicate keys off of a "TenantId" column in all tables. Simplified, I have the ...
- 143
0
votes
1
answer
55
views
How route traffic to a Azure SQL server via a jump server to prevent whitelistingof dev machine?
I have to connect to Azure SQL server (e.g. mydatabase.azuredb.windows.net:1433). In order to do so, i have to inform my ADMIN to whitelist my machine's IP, works fine that day. NExt day my machine ...
- 101
3
votes
3
answers
358
views
Getting a SORT operator when I have an index
On a Azure SQL db (SQL2019 compat), I have an ETL process that populates HISTORY tables in a DeltaTrack pattern.
In the Proc, there's an UPDATE to the HISTORY table which the query engine is using a ...
- 441
0
votes
1
answer
13
views
Partions on a partitioned table getting created with the same partition number in Azure SQL database
I am creating a table with partitioning logic. Below is the partition function and scheme:
DECLARE @StartDate datetime2 = '20230401';
DECLARE @YearlyQuartersPartitionFunction nvarchar(max) = N'CREATE ...
- 1
-4
votes
2
answers
136
views
Calculate total_worker_time in minutes [closed]
I have an easy query which can show me the top 10 elapsed time by query_plan_hash and query_hash:
PRINT '-- top 10 elapsed time by query_plan_hash and query_hash --'
SELECT TOP 10 query_plan_hash
...
- 1,504
0
votes
2
answers
96
views
DATE Constraint is ignored on insert
I must be missing something somewhere..
Azure SQL
DispatchedOn is a DateTime column
ALTER TABLE [table] WITH CHECK ADD CONSTRAINT [CK_ReportDate6MonthRollGreater] CHECK ((CONVERT([date],[...
- 177
0
votes
1
answer
184
views
How to optimize the SQL query which uses cte, group by and run for 23 hours?
I have a query which first performs joins inside cte and then based on cte join with other table and perform group by with some aggregations and get the top 15 records.
This query is taking 23 hours ...
- 129
0
votes
1
answer
33
views
How to get access to this server?
I'm a total newbie with data analysis background. Today I was passed on this information as in the image below to remotely get access to a database, which I don't know what app it uses.
Can you take a ...
- 103
-1
votes
2
answers
141
views
Data retrieving is taking more than 4hrs to fetch 400K records
I have a table with 15 million rows, it's a parent table with 12 child tables.
Even for a simple count query, it is taking hour's to complete.
select count(1) from table
where col_filter >= 'number'...
- 129
-3
votes
1
answer
66
views
Shortcutting/Clowning/Virtualising Azure SQL Database Bulk Loads
I'm currently working on a solution to bulk load (almost) structured data into an Azure SQL Database. The long term plan is to use ADF, but the client is keen to have some static data loaded early in ...
- 117
0
votes
1
answer
32
views
Is it good for performance if multiple full text indexes of SQL Server are connected to one full text catalog?
Now my situation is as follows.
A table called APPR_DOC_MASTER was created to service multiple customers, and this table was created as a separate table by dividing it into schemas for each customer.
...
- 1
0
votes
1
answer
127
views
Procedure to clone a schema in SQL Server
On Azure SQL, we use schema-based multitenancy. To add a new tenant, we want to copy a schema that acts as a template for a new tenant schema. What is the easiest way to clone a schema, incl. the data?...
- 101
0
votes
0
answers
376
views
Sometimes simple queries are slow, high buffer IO wait times the cause?
I have a Azure SQL database, Premium P2: 250 DTUs, using 250GB of storage.
Sometimes even simple queries runs slow, for example by getting a single row from a simple 300mb table with with 500k rows, ...
- 479
5
votes
1
answer
140
views
Is there some fundamental reason that TransactionScope does not work as expected?
For the following code...
using System.Transactions;
using Microsoft.Data.SqlClient;
var connectionString = "...";
using (var scope = new TransactionScope(TransactionScopeOption....
- 83.3k
-1
votes
1
answer
113
views
Azure DB - Converting Base64 Encoded into Binary Found in Databricks Python
I am going nuts on this. We have a base64 encoded value:
"+G1mQl3A30MKQsCBxMXwyMOc/AZEvpcRRI13mUOy3SNDEFjyQuVQ0UKHFsdCJ/HCQqEvuEE5Lb5AAg=="
We needed decoded to binary. In python (databricks)...
- 11
0
votes
2
answers
66
views
Moving tables to different database within same sql server
There is a SQL server, there are around 100 databases in it. I have to query a few tables from one of the databases. when I query, it's very slow and I think CPU utilization is very high at that time. ...
- 101
1
vote
1
answer
45
views
Can I develop a Dashboard Report for Data Allocated vs Max Storage for my Azure Database?
I have an Azure SQL Server Database - Managed Instance I'd like to monitor. Here is the widget that Azure shows me on the database summary page. I'd like to put this widget, or at least the ...
- 113
1
vote
0
answers
52
views
Getting a SORT operator when number of rows being updated is above a threshold [duplicate]
I'm working a table that has VARCHAR(18) as the PK. I'm putting this here in case it's significant.
We have a SP that runs as part of an ETL that updates X rows each time.
I've noticed that when the ...
- 441
0
votes
1
answer
51
views
How can I copy a database in Azure SQL Server to Azure SQL Managed Instance in different Azure Subscription
I created an Azure SQL managed instance using terraform in Azure Subscription A.
I am trying to copy the databases in an existing Azure SQL Server in Subscription B to the SQL Managed Instances I ...
- 1
2
votes
1
answer
249
views
How can I use efficient index seek to find the latest row filtered on a small subset of rows?
I have a transaction table with transactions for accounts. I want to fetch the latest transaction for a (small amount of) accounts. If I only filter on one account, the DB will use efficient index ...
- 479
3
votes
3
answers
144
views
Ordering on two columns is less efficient than order on only the first, why?
I have a table that stores transactions for accounts, and I want to select out the latest transaction for a specific account. But the DB (Azure SQL) seems to fetch all transactions for the account, ...
- 479
0
votes
1
answer
76
views
Azure SQL Backup Restore
How long will it take to restore 40GB full backup in Azure SQL database, Azure SQL Managed Instance?
- 1
0
votes
2
answers
131
views
Linked Servers and Application Intent
I'm attempting to improve performance of a set of views that we have on a local server, pulling data from another set of views on a remote Azure database.
Our system provider advised us to use ...
- 188
-1
votes
1
answer
41
views
Azure SQL Database :: SHRINKDATABASE increase the unallocated space
I'm trying to squeeze as much Azure SQL Databases as possible in order to make them fir inside a SQL Elastic Pool.
I'm using this query that allows me to compare the unallocated space before and after ...
- 1,504
-1
votes
1
answer
226
views
SQL Elastic Pools: "The semaphore timeout period has expired" while REBUILD index
I'm shrinking databases that are already on a SQL Elastic Pool.
The goal is to fit as much as possible databases in a unique Elastic Pool.
In order to do so I've stubble upon a blog that suggest to ...
- 1,504
1
vote
3
answers
243
views
How to shrink databases and claim more storage
I have created a SQL elastic pool but I have no more storage:
I run DBCC SHRINKDATABASE (N'db1') on every single database to claim space following this guide: Reduce Azure SQL Database Size but it ...
- 1,504
0
votes
0
answers
11
views
Azure SQL How to Check Partition Logic
I have table where i have partitioned it by day , my table
CREATE TABLE test
(
date DATE,
/// other fields
) on dailyPartitionScheme(date)
When i do the query
Select * from test where ...
- 101
2
votes
2
answers
56
views
Azure SQL Design Recommendation
I have a use case where I have a job that inserts around 30 million rows every hour. It's the kind of snapshot we are taking for some data source and we are interested only in the latest snapshot for ...
- 121
0
votes
0
answers
59
views
Azure Elastic Job running a stored procedure says it completes but is faster than in SSMS
I've set up an Azure Elastic Job to run a Stats Update on several Azure SQL Shards.
When I run this SP on the shards from SSMS some are quick and some take over an hour.
When I run the Elastic Job it ...
- 101
0
votes
0
answers
58
views
SQL Server - SYSDATETIMEOFFSET() returns identical values even with an added delay
I am trying to understand some odd behavior in SQL Server. I've seen it in SQL Server 2019 and in Azure SQL Server.
The gist of it is that I get the same value from the SYSDATETIMEOFFSET() function ...
- 188
4
votes
2
answers
3k
views
Login failed for user '<token-identified principal>'
I have an Azure SQL Database as well as an Azure App Service.
I use an App Registration to connect the user via Active Directory, I get the error Login failed for user '<token-identified principal&...
- 141
0
votes
1
answer
38
views
Geo Replication on different tiers?
We have an Azure SQL Database running in the S4 Tier in UK South and a read-only replica in Canada East running in the S1 tier.
Regarding the synchronisation of data, does a mismatch in tiers affect ...
- 103
1
vote
0
answers
58
views
Azure SQL: How can I share replicated db access with specific users?
I have 2 Azure SQL instances :
db-main which is my main DB, containing both my-db and master databases.
db-replica which contains a readonly replica my-db@db-main
I have currently a user my-db-admin ...
- 111
1
vote
1
answer
337
views
read only replica of azure sql database within the same server
I have the following bicep to create a sql server and sql database:
resource sqlServer 'Microsoft.Sql/servers@2021-02-01-preview' = {
name: sqlServerName
location: location
identity: {
type: ...
- 111
0
votes
1
answer
147
views
SQL Server alternatives to triggers for default-value logic
In SQL Server, we have a pattern of creating triggers to set default date and GUID on insert:
CREATE TRIGGER [dbo].[trgInsertMyTable] ON [dbo].[MyTable] FOR INSERT AS
BEGIN
UPDATE [dbo].[MyTable]...
- 765
0
votes
0
answers
73
views
How can we get the query history related to user tables only?
Following query, from Microsoft doc, returns the text of the queries run by a user, as well as the queries run internally by sql server (for internal usage).
Question: How can we refine this query to ...
- 289
3
votes
1
answer
150
views
Distinct Sort Operator with no Order, Distinct, Grouping
I have two queries that are mostly the same bar a where expression. They are UNION ALL'd together for a complete result set. There is no grouping, nor ordering, nor distinct in the queries. However, ...
- 115
14
votes
1
answer
2k
views
Is this a bug or I did something wrong when I created the table?
I have a table in a Azure SQL Instance (12.0.2000.8) and I found this weird behaviour when I query it.
The column is defined as Integer, but it does not respond to the "WHERE" filters, also ...
- 243
0
votes
3
answers
632
views
Audit trigger to only track when specific columns change, and only log the old and new values for the changed columns
I have a table with 75 columns and need to track about 15 of those columns to log out the old and new values anytime the data in any of those 15 columns change (updates only, no inserts or deletes). ...
- 3
0
votes
1
answer
56
views
Improving the performance of STEquals
We have a table containing all polygons (geometry/geography) of the objects in our application.
This is because most polygons need to have a 'geometry' (RDNew) and a 'geography' (WGS84) version of the ...
- 119
-3
votes
1
answer
52
views
Improve M:N Top Query so that it is indexable on Azure SQL [closed]
I have a ticket system which has a pretty easy schema for my Azure SQL v150 Database.
CREATE TABLE [dbo].[Tickets](
[Id] [int] IDENTITY(1,1) NOT NULL
)
CREATE TABLE [dbo].[TicketComments](
[...
- 95
2
votes
1
answer
172
views
Attempting to find the average time spent in a specific mode per day
I've been asked to find the amount of time spent between various modes of a system on a daily basis. The database has a table that provides a starting datetime and updates every 15 minutes, providing ...
- 23