34
The answer is "yes". You can do this with a filtered index (see here for documentation).
For instance, you can do:
create unique index t_col on t(col) where id > 1000;
This creates a unique index, only on new rows, rather than on the old rows. This particular formulation would allow duplicates with existing values.
If you have just a handful of ...
29
The semantics of the two statements are different:
The first does not set the value of the variable if no row is found.
The second always sets the variable, including to null if no row is found.
The Constant Scan produces an empty row (with no columns!) that will result in the variable being updated in case nothing matches from the base table. The left ...
27
Larger nvarchar (max) data items (over 8000 bytes or so) will spill over into text storage and require additional I/O. Smaller items will be stored in-row. There are options that control this behaviour - see this MSDN article for more details.
If stored in-row there is no significant I/O performance overhead; there may be additional CPU overhead on ...
answered Aug 20 '13 at 16:19
ConcernedOfTunbridgeWells
16.6k2 gold badges53 silver badges70 bronze badges
25
Kendra here (the author of sp_BlitzIndex)
First, thanks for being interested in the procedure and trying it out.
Azure doesn't expose all the dynamic management views that we get in the boxed product. I do actually reference sys.dm_db_partition_stats, but there's other info I need to get from sys.partitions for other users. (Is it using compression? What ...
24
Yes you can do that.
Here is a table with duplicates:
CREATE TABLE dbo.Party
(
ID INT NOT NULL
IDENTITY ,
CONSTRAINT PK_Party PRIMARY KEY ( ID ) ,
Name VARCHAR(30) NOT NULL
) ;
GO
INSERT INTO dbo.Party
( Name )
VALUES ( 'Frodo Baggins' ),
( 'Luke Skywalker' ),
( 'Luke Skywalker' ),
( 'Harry ...
23
Although this doesn't answer your specific question, it may preclude you from needing to ask the question in the first place: It's possible to set a length on your string variables in your C# model class, which will cause Entity Framework to generate SQL that uses a fixed-length nvarchar type (e.g. nvarchar(50)), instead of nvarchar(max).
For example, ...
21
If I let the server decide which index to use, it picks IX_MachineryId, and it takes up to a minute.
That index is not partitioned, so the optimizer recognizes it can be used to provide the ordering specified in the query without sorting. As a non-unique nonclustered index, it also has the keys of the clustered index as subkeys, so the index can be used to ...
18
We found that with contained databases / contained users you must specify:
GRANT CONNECT TO [YOUR_USER]
Otherwise CONNECT seems to be revoked by default. Once we made the above change, we could access the database.
17
From the online documentation:
POWER ( float_expression , y )
Arguments
float_expression
Is an expression of type float or of a type that can be implicitly converted to float
The implication is that whatever you pass as the first parameter is going to be implicitly cast to a float(53) before the function is executed. However, this is not (...
17
With the index definition that you have for IDX_my_nme, SQL Server will be able to seek using the ActionDate column but not with the Address column. The index contains all of the columns needed to cover the subquery but it likely isn't very selective for that subquery. Suppose that almost all of the data in the table has an ActionDate value of earlier than '...
17
Use the SQL Server tool to export the database objects definition to a SQL file which should include: tables, views, triggers, SPs, functions, and so on
Edit the SQL file (make a backup first) using any text editor that allows you to find the text "GETDATE()" and replace it with "[dbo].[getlocaldate]()"
Run the edited SQL file in Azure SQL to create your ...
16
The filtered unique index is a brilliant idea but it has a minor disadvantage - no matter if you use the WHERE identity_column > <current value> condition or the WHERE identity_column NOT IN (<list of ids for duplicate values here>).
With the first approach , you will still be able to insert duplicate data in the future, duplicates of ...
15
What would be the best way to implement this change?
I would work the other way around. Convert all your timestamps in the database to UTC, and just use UTC and go with the flow. If you need a timestamp in a different tz, you can create a generated column using AT TIME ZONE (as you did above) that renders the time stamp in that specified TZ (for the app). ...
14
First off, just to be clear: SQLCLR / .NET / C# / VB.NET cannot query the database. Only T-SQL can query SQL Server. So in order for SQLCLR code to get data or interact with SQL Server in any way, it must establish a SqlConnection, like any other .NET app, and submit T-SQL, or execute a Stored Procedure.
Yes, you can call a SQLCLR (whether it is C# or VB....
14
The result of 264 is exactly representable in float (and real for that matter).
The problem arises when this precise result is converted back to numeric (the type of the first POWER operand).
Before database compatibility level 130 was introduced, SQL Server rounded float to numeric implicit conversions to a maximum of 17 digits.
Under compatibility level ...
14
It's referring to Azure SQL Database which uses RCSI by default.
Isolation Level SQL
Database default database wide setting is to
enable read committed snapshot isolation (RCSI) by having both the
READ_COMMITTED_SNAPSHOT and ALLOW_SNAPSHOT_ISOLATION database options
set to ON, learn more about isolation levels here. You cannot
change the database default ...
13
Azure SQL now directly supports this
Azure SQL Database directly supports clearing the proc cache of the current user database without any hacks:
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
Additional Information
The following script (by Shannon Gowen) can be used to watch the process step-by-step:
-- run this script against a user ...
13
So this may be an old question, but the problem is still relevant today.
The only way that you can alter the administrator of an Azure Sql Server, is to create a new SQL Server. Once the administrator has been set, you can reset their password, but not the name.
I just ran into this same issue and had the same #facepalm moment.
13
It looks like this table was defined with dynamic data masking, and the user that the application uses to access the database doesn't have permission to view masked data (which is good!).
This is why the behavior differs between the application and SSMS: I expect you're using a higher-privileged user when running the query from SSMS
Here's a demo:
CREATE ...
12
There isn't an explicit way to do this today, but that isn't a permanent scenario (the DBCC command is still not supported, but read up on Query Store). Even when the schema change hit is acceptable, it may not be what you want, because it will invalidate all plans related to the underlying object, not just the bad one.
Not looking for credit for this, but ...
12
Whether a heap or a clustered index, you should find that you will be able to reclaim space in a table by rebuilding:
ALTER INDEX ALL ON dbo.myTableName REBUILD;
Note that reclaiming space in a table, and shrinking a database, are two completely separate things. Shrinking a database should be an exceptional thing - don't shrink a database just to free up ...
11
Microsoft has changed the procedure. What you need to do is Export/Import a Data-tier Application via a BACPAC file. Microsoft explains it here (in the section Backup Package (.bacpac):
http://msdn.microsoft.com/en-us/library/ee210546.aspx
Here it is in a nutshell:
Use SQL Server Management Studio 2014
Right-click your local database name, then Tasks / ...
11
The (formerly) accepted answer iswas incorrect as it iswas a bad and misleading test. The two queries being compared do not do the same thing due to a simple typo that causes them to not be an apples-to-apples comparison. The test in the accepted answer is unfairly biased in favor of the CAST operation. The issue is that the CONVERT operation is being done ...
11
It seems likely the very large ntext data is highly fragmented, causing a large amount of random I/O (or other inefficiencies) when locating LOB fragments to delete. Maybe the elastic thingy needs more I/O horsepower too.
You may need to export and reload the data to solve this problem. Copying to a new table, dropping the old, then renaming the new would ...
11
The optimizer can produce an optimal plan for this query:
Sample schema
CREATE PARTITION FUNCTION PF (datetime2(7)) AS
RANGE RIGHT FOR VALUES
(
'20180101','20180102','20180103','20180104','20180105','20180106','20180107','20180108','20180109','20180110',
'20180111','20180112','20180113','20180114','20180115','20180116','20180117','20180118','...
11
What you're basically trying to do is measure storage speeds in the cloud. Unfortunately, that's not necessarily repeatable or reliable - it can suffer from noisy neighbors, other people doing development on the same database, varies with your storage type, etc.
Rather than clearing the buffers or measuring query runtime, try starting with measuring logical ...
11
Under "normal" conditions, no, data in VARCHAR and NVARCHAR columns is not de-duped (although duplicate attribute and/or element names in a single XML value are reduced to a unique instance).
Using one of the Data Compression options is probably your best bet. Here are some things to consider:
Unicode Compression (part of Row Compression) only ...
10
Indexing the biggest concern. From BOL:
Columns that are of the large object (LOB) data types ntext, text,
varchar(max), nvarchar(max), varbinary(max), xml, or image cannot be
specified as key columns for an index.
If you can't index properly, you are going to have slow queries. And from a data integrity perspective, having nvarchar(max) will allow ...
10
The manage button is no longer available for V12 SQL databases on Azure. Official response from support engineer at Azure:
The Manage button is not available on Azure SQL Database V12. It is
only compatible with the previous version of azure SQL Database, V2
(or V11). With the new database that you created, you may use either
Visual Studio, or SQL ...
10
You should use the Azure migration wizard instead of using SSMS create script.
How to: Use the SQL Azure Migration Wizard
Behind the scenes it uses bcp to transfer your data to Azure.
For the future, SSMS is not meant to handle large .sql files. Even a 1 or 2 MB file will crash SSMS. Instead you should use command line tools like sqlcmd or bcp - depending ...
Only top voted, non community-wiki answers of a minimum length are eligible
