We’re rewarding the question askers & reputations are being recalculated! Read more.

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).

Filter by
Sorted by
Tagged with
40
votes
5answers
43k views

Can I add a unique constraint that ignores existing violations?

I have a table which currently has duplicate values in a column. I cannot remove these erroneous duplicates but I would like to prevent additional non-unique values from being added. Can I create a ...
28
votes
4answers
55k views

EF Code First uses nvarchar(max) for all strings. Will this hurt query performance?

I have some databases created using Entity Framework Code First; the apps are working and in general I'm pretty happy with what Code First lets me do. I am a programmer first, and a DBA second, by ...
27
votes
6answers
7k views

Changing the use of GETDATE() in the entire database

I need to migrate an on-premises SQL Server 2017 database to an Azure SQL database, and I'm facing some challenges since there's quite a bit of limitations to go through. In particular, since an ...
25
votes
1answer
15k views

How can I alter an existing Primary Key on SQL Azure?

I want to modify an existing primary key on a SQL Azure table. It currently has one column, and I want to add another. Now, on SQL Server 2008 this was a piece of cake, just did it in SSMS, poof. ...
21
votes
1answer
1k views

Where do this Constant Scan and Left Outer Join come from in a trivial SELECT query plan?

I have this table: CREATE TABLE [dbo].[Accounts] ( [AccountId] UNIQUEIDENTIFIER UNIQUE NOT NULL DEFAULT NEWID(), -- WHATEVER other columns ); GO CREATE UNIQUE CLUSTERED INDEX [AccountsIndex] ...
15
votes
1answer
367 views

Poor performing subquery with date comparisons

When using a subquery to find the total count of all prior records with a matching field, the performance is terrible on a table with as little as 50k records. Without the subquery, the query executes ...
14
votes
4answers
1k views

Why does TSQL return the wrong value for POWER(2.,64.)?

select POWER(2.,64.) returns 18446744073709552000 instead of 18446744073709551616. It seems to have only 16 digits of precision (rounding the 17th). Even making the precision explicit select power(...
14
votes
2answers
1k views

Why is my index not being used in a SELECT TOP?

Here's the run-down: I'm doing a select query. Every column in the WHERE and ORDER BY clauses are in a single non-clustered index IX_MachineryId_DateRecorded, either as part of the key, or as INCLUDE ...
14
votes
4answers
25k views

Azure SQL Database “Login failed for user” in application, but works fine in SSMS

I wanted to try out the contained database users feature on Azure SQL Database V12, but I'm having a problem authenticating that seems odd to me. I created a database called Classifier. I added my ...
14
votes
1answer
532 views

Can I tell when and who updated my database from v11 to v12 on Azure?

Earlier today I noticed that all my Azure SQL database servers where updated from v11 to v12. I am the only one in my company that should be touching those dials, but not the only person in my company ...
13
votes
2answers
2k views

How do I get Brent Ozar's sp_BlitzIndex to run on Azure?

I downloaded the SQL Server First Aid Kit from Brent Ozar's website. When I tried to run the sp_BlitzIndex script against my master database via Microsoft Sql Server Management Studio while logged in ...
12
votes
5answers
7k views

How can I remove a bad execution plan from Azure SQL Database?

DBCC FREEPROCCACHE doesn't work in Azure SQL DB. How else can I force a plan to kick itself out of the cache in a way that won't hurt a production system (i.e. I can't just go alter tables willy nilly)...
12
votes
5answers
14k views

How do I copy my SQL Azure database to a local SQL Server instance?

I have an OLTP database hosted on a SQL Azure instance. I want to pull a copy of the database down from the cloud so I can run some heavy extracts and OLAP-style queries against it without impacting ...
12
votes
2answers
874 views

Why does my WHERE clause benefit from an “included” column?

According to this answer, unless an index is built over the columns which are used to restrict, the query will not benefit from an index. I have this definition: CREATE TABLE [dbo].[JobItems] ( [...
12
votes
7answers
8k views

How can I profile SQL Azure?

I am writing a web site that uses SQL Azure heavily. However, it is painfully slow. Is there an easy way to profile the live SQL Azure instance?
12
votes
1answer
5k views

Slow order by SQL Server

In my application, I have a query which performs a search in "files" table. The table "files" is partitioned by "f"."created" (see the table definition and has ~26 million rows for the client 19 ("f"...
11
votes
1answer
10k views

How to change name for administrator login on azure sql server

I have myserver.database.windows.net with administrator login John. I'd like to change it to Peter. It is possible, if so, how ? Alter which works with local sql server, doesn't work here ALTER ...
11
votes
1answer
303 views

Migrating from a physical database to a cloud database

What are the implications of moving a physical database created in Microsoft SQL server 2008R2, that makes use of advanced features like Full-Text Search on varchar(MAX) FILESTREAM BLOBS to SQL Azure? ...
10
votes
3answers
386 views

Filter condition not correctly applied to Clustered Columnstore index

Using the below example, the predicates are the same, however the top statement (correctly) returns 0 rows, the bottom statement returns 1 - even though the predicates do NOT match: declare @barcode ...
10
votes
1answer
207 views

Optimize index on a 2,135,044,521 row table

I have an I/O problem with a large table. General stats The table has the following main characteristics: environment: Azure SQL Database (tier is P4 Premium (500 DTUs)) rows: 2,135,044,521 1,275 ...
10
votes
1answer
372 views

Unknown _TransactionIndex table in Azure SQL Database

Just noticed today that there is a random table in my Azure SQL Database that is named "_TransactionIndex_{guid}" It has 1 column, Id, that contains 8 GUIDs. Couldn't find any information about this ...
10
votes
2answers
231 views

Windowing offset based on time stamp

I'm writing a query that will be used to page results for a social feed. The concept is the mobile app will request N items, and provide a starting datetime which I have called @CutoffTime below. ...
9
votes
3answers
6k views

“Deploy Database to SQL Azure” Task missing

I recently installed SQL Server 2014 Express on a workstation I have and am trying to deploy to SQL Azure. I've done this before on a previous workstation using SQL Server 2012 Express by doing the ...
9
votes
2answers
566 views

SQL Server: Covering indexes including all columns?

Our team has inherited an application and associated database. The previous developers appear to have enforced a rule where every index, on every table, has an INCLUDE clause to always add every ...
9
votes
2answers
8k views

What is the best way to create a database diagram for SQL Azure without having to install SQL Server?

I have a database on SQL Azure and want to create a database diagram for that, but don't want to have to install SQL server and copy the database etc. Is there any way that I can do it that anyone ...
9
votes
2answers
1k views

Clustered indexing is now must - why?

Earlier, there were non-conclusive to me debates / discussions on whether to (always) engage / avoid clustered indexes. Well, I understood that they are to be used sometimes with proper + specific ...
9
votes
1answer
2k views

Why is my Azure SQL (SQL Server) database getting overloaded with data IO for periods at a time?

I am running an Azure SQL database under the S2 edition (50 DTUs). Normal use of the server usually hangs around 10% DTU. However, this server regularly gets into a state where it will send DTU usage ...
8
votes
1answer
3k views

Finding the default isolation level in SQL Server 2017 and Azure SQL DB

I'm reading a book related to transaction and concurrency. In one paragraph it is mentioned that: In on-premises SQL Server instance the default isolation level is Read-Committed based on locking ...
8
votes
1answer
2k views

TOP(1) BY GROUP of very huge (100,000,000+) table

Setup I have a huge table of ~115,382,254 rows. The table is relatively simple and logs application process operations. CREATE TABLE [data].[OperationData]( [SourceDeciveID] [bigint] NOT NULL, ...
8
votes
6answers
8k views

Rebuild Very Large Primary Key Index

I have a SQL database that is hosted on Azure. The problem is that the size is getting out of control, I can see up to 99% fragmentation in the Primary Key clustered indexes. I'm able to rebuild all ...
8
votes
3answers
9k views

How to know the user doing a delete action on an audit table, when using a shared login?

Background information: I'm creating a collection of audit tables to keep track of updates and deletes to a set of data tables for my app. Audit records are created via triggers. DML in my app's ...
8
votes
2answers
4k views

What is causing high CPU usage from this query/execution plan?

I have an Azure SQL Database that powers a .NET Core API app. Browsing the performance overview reports in the Azure Portal suggests that the majority of the load (DTU usage) on my database server is ...
7
votes
3answers
17k views

Inserting date from string: CAST vs CONVERT

Consider the following two ways to convert a datetime varchar string into a date field: SELECT convert(date, '2012-12-21 21:12:00', 20) -- Only date is needed SELECT cast('2012-12-21 21:12:00' as ...
7
votes
4answers
6k views

How do DTUs in Standard and Premium performance tiers in SQL Azure compare?

We recently observed a serious performance degradation in a SQL Azure database running in Standard3 performance tier - CPU utilization went from ten percent to fifty percent to almost one hundred ...
7
votes
3answers
5k views

SSMS 2014 “Deploy Database” feature has old SQL Azure settings

I'm attempting to test-deploy an existing SQL Server 2005 database into SQL Azure using the "Deploy Database to Windows Azure SQL Database" task in SQL Server Management Studio 2014. When the wizard ...
7
votes
5answers
3k views

Most efficient way to retrieve a sub-query COUNT grouped by top table?

Given the following schema CREATE TABLE categories ( id UNIQUEIDENTIFIER PRIMARY KEY, name NVARCHAR(50) ); CREATE TABLE [group] ( id UNIQUEIDENTIFIER PRIMARY KEY ); CREATE TABLE logger (...
7
votes
2answers
2k views

Help understanding why a deadlock occurred on row level index lock

I have the following deadlock xml <deadlock> <victim-list> <victimProcess id="process3340d548c8" /> </victim-list> <process-list> <process id="...
7
votes
1answer
3k views

“Manage” option vanished on portal for Azure SQL database

I had a database which I deleted and it used to have a "Manage" option which would open the online sql management portal. I created a new database, now I don't have the "Manage" option anymore. I ...
7
votes
2answers
7k views

Will altering order of columns in GROUP BY affect performance?

I have a SQL query like this: SELECT A, B, (CASE WHEN C=0 THEN 0 ELSE 1 END), COUNT(D) FROM SomeTable GROUP BY A, B, (CASE WHEN C=0 THEN 0 ELSE 1 END) On a huge dataset the actual execution plan ...
7
votes
1answer
2k views

How do I prevent a massive SELECT from blocking other statements?

Our SQL Azure database contains a massive SELECT statement that runs something like once per day. The heavy SELECT statement contains no locking hints. Recently we observed some stalls in production ...
7
votes
2answers
7k views

Slow JOIN on tables with millions of rows

In my application I have to join tables with millions of rows. I have a query like this: SELECT DISTINCT "f"."id" AS "FileId" , "f"."name" AS "FileName" , "f"."year" AS "FileYear" , "vt"."value"...
7
votes
1answer
1k views

SSIS OLE DB Source Editor Data Access Mode: “SQL command” vs “Table or view”

In BIDS, I'm running a Data Flow Task & I'm wondering what the difference is behind the scenes between using "Table or view" vs "SQL Command" for the Data Access Mode for an OLE DB Source. For ...
7
votes
1answer
4k views

Perform reads and writes in the same transaction

I'm trying to implement transactions in my application. I'm just trying to implement it like the example shown in the BeginTransaction() documentation. Public Shared Sub Process(wwid As String, ...
7
votes
1answer
2k views

SELECT COUNT(*) on a view is orders of magnitude slower than SELECT * on the same view

The View CREATE VIEW [dbo].[vProductList] WITH SCHEMABINDING AS SELECT p.[Id] ,p.[Name] ,price.[Value] as CalculatedPrice ,orders.[Value] as OrdersWithThisProduct FROM ...
7
votes
2answers
379 views

Help finding join without predicate

Much like a related question by swasheck, I have a query that historically has suffered from performance problems. I was looking through the query plan on SSMS and noticed a Nested Loops (Inner Join) ...
7
votes
3answers
4k views

Implementing an encrypted table in SQL Azure

I'm moving an App to Azure and need to achieve PCI compliance for a couple columns in a single table. I realize I can encrypt the data before I store it, but I was wondering if there was a feature in ...
7
votes
1answer
512 views

Measure SQL Azure Transaction Rate

We are on SQL Azure v12 Standard/S2 Service Tier and are not satisfied with the performance so I am trying to measure where the bottleneck is. I realize that we could be hitting the limits of the S2 ...
7
votes
1answer
250 views

Storing CREATE EXTERNAL DATA SOURCE in a SSDT project without exposing the DATABASE SCOPED CREDENTIAL or MASTER KEY secret;

I have a SSDT project that deploys to Azure SQL DB. Recently I added an external table. This required an external data source as follows: CREATE EXTERNAL DATA SOURCE [data_warehouse] WITH ( ...
7
votes
0answers
305 views

Cardinality estimation on tables with large cardinality variance between keys

Context I have stumbled on an issue in SQL Azure where the Sort-operator is spilling over into TempDB due to poor row estimates. We are querying a master table that is joined with a number of detail ...
6
votes
3answers
14k views

“login failed for user ” on SQL Azure from azure hosted web application

I have an Azure ASP.net MVC web application which uses a SQL Azure db. It was working fine for some months (someday for some reason Azure changed the name of the server and I had to set that again but ...