Tag Info

Hot answers tagged

26

The is no such thing as a 'primary index'. There is such a thing as a 'primary key' and also there is such a thing as a 'clustered index'. Distinct concepts, often confused. With this distinction in mind, lets revisit the question: Q1) Can the clustered index in a SQL Azure table be modified? A: Yes. Use WITH (DROP_EXISTING=ON): create table Friend ( ...


12

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


11

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


10

Read Inside SQL Azure: SQL Azure provides logical databases for application data storage. In reality, each subscriber’s data is actually stored multiple times, replicated across three SQL Server databases that are distributed across three physical servers in a single data center. Many subscribers may share the same physical database. ...


8

Use the SQL Azure Migration Wizard: The SQL Azure Migration Wizard (SQLAzureMW) gives you the options to analyzes, generates scripts, and migrate data (via BCP) from: SQL Server to SQL Azure SQL Azure to SQL Server SQL Azure to SQL Azure


7

Moving the data is pretty easy. If you are using full text search that feature isn't available in SQL Azure yet. Neither is FILESTREAM. If you require full text search then Azure isn't for you.


7

There isn't an explicit way to do this today, but that isn't a permanent scenario (can't reveal more due to NDA). 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 building dynamic SQL to perform the ...


7

Your query is no faster with the index because SQL Server has determined that it would be more efficient to do a Clustered Index Scan, than use the IX_ActCost_ScenarioID that you have defined and perform a Key Lookup to retrieve the extra data needed. As you've only defined the index on ScenarioID, with no INCLUDE columns, each extra column you wish to ...


7

Passwords are case-sensitive. Passwords should always be at least 8 characters long, and cannot exceed 128 characters. Passwords can include a-z, A-Z, 0-9, and most non-alphanumeric characters. Passwords cannot contain single quotes, or the login_name. https://msdn.microsoft.com/en-us/library/ms189751.aspx


6

I have used the MVC Mini Profiler as part of an application to profile SQL Azure but depending on your application it may or may not work. What kind of tasks are you doing that need profiling?


6

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


6

'BULK' rowset provider not supported in this version of SQL Server. BULK INSERT is not supported on SQL Azure. You can use bcp or write something that wraps SqlBulkCopy to bulk insert into SQL Azure. There's other ways to bulk insert aside from these, too. On executing it,my SQL management studio 2008,gives the following error : Don't confuse SQL ...


6

I wouldn't have been able to find the solution to this problem if you hadn't decided to open the VM on azure and check the version numbers, so thank you! After I saw your post, I googled "sql 12.0.2430.0" and found a hotfix from microsoft that fixed the problem. You can find the hotfix Here The update added full support for the new Azure SQL database ...


5

Try setting the "Collation Compatible" to True on the Linked Server definition. If you set it to False, it will pull the entire table down and do the compare on the receiving end. Consider the scenario where the local server is case insensitive, and the remote server is case sensitive, the results will differ based on where the compare is done.


5

It's a good practice to delimit all identifiers. In this particular case, you said the PK_dbo prefix was actually part of the index name. Therefore, the name has to be delimited appropriately, or it cannot be parsed unambiguously. Also, what's actually being renamed is an index, not an object. This should work after filling in the blanks: sp_rename ...


5

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


5

My suggestion would be: (a) talk to Azure support. This is not how it should be working AFAIK. (b) when building your list of indexes to rebuild/reorganize, add a NOT EXISTS clause to the criteria to eliminate any indexes with GUIDs as the leading key column: SELECT name, etc. FROM sys.indexes AS i INNER JOIN sys.dm_db_index_physical_stats AS s ON ... ...


5

You will want to load your data into a new table, doing this in small batches, then drop the existing table. I put together a quick example using the Sales.Customer table in AdventureWorks, something similar should work for you also. First, create your new table, complete with the new datatype you want to use: CREATE TABLE [Sales].[Currency_New]( ...


5

GUIDs only really cause performance problems for a couple of reasons. First their size. You are using a 16 byte identifier where an int(4 bytes) or even bigint(8 bytes) is much smaller. This doesn't really matter so much on a wide table or short table. If your table is fairly narrow then the percent increase is of course greater. A 2000 byte width vs ...


5

This is not present in SQL Server Express 2014 - see https://connect.microsoft.com/SQLServer/feedback/details/859491/deploy-database-to-windows-azure-sql-database-missing


5

I was wondering whether such useful query could also be used in standard ms sql server some how? No, CREATE DATABASE .. COPY OF .. is ONLY applicable to Azure SQL Database As a side note, Copying a database using the CREATE DATABASE statement is an asynchronous operation. Therefore, a connection to the SQL Database server is not needed for the ...


4

Despite what MSDN documentation says, no, it doesn't matter for GROUP BY queries. You can test it here, at: SQL-Fiddle test (SQL-Server 2012) CREATE TABLE test ( id INT IDENTITY(1,1) , a INT NOT NULL , b INT NOT NULL , c INT NOT NULL , d INT NOT NULL , PRIMARY KEY (id) ) ; CREATE NONCLUSTERED INDEX a_b_c_include_d_index ON test (a, b, c) ...


4

Compose a bcp script that exports the contents of all your tables to local files. Start by writing a query that will output a bcp command to export each table in your target database to a path on your destination machine: SELECT 'bcp ' + SCHEMA_NAME(schema_id) + '.' + name + ' out ' + ' D:\local_backup_directory\' + ...


4

Red Gate has an aptly named SQL Azure Backup Tool. It's free, but Red Gate has discontinued support for it.


4

Add a nullable column to the table then drop the column. That'll force SQL to flush the cache for that object. As for doing all the tables, a cursor should do the trick. Just use a column name that'll never exist in any table like 'zzzzzz_go_away' or something.


4

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 s=perspective, having nvarchar(max) will ...


4

Why not just create the table column with a case-insensitive, accent-insensitive collation? This prevents duplicates according to the collation rules, and allows the sort of searches you seem to require: CREATE TABLE Test ( col1 varchar(30) COLLATE SQL_Latin1_General_CP1_CI_AI PRIMARY KEY ); -- Success INSERT dbo.Test VALUES ('Gagné'); -- Failed, ...


4

I insert into this table small amount of data just about 1000 rows and it takes more than 4 minutes Remote data modifications through a linked server use the sp_cursor model. The effect is similar to issuing 1000 separate single inserts (one for each row). If a round trip takes 250ms, 1000 such trips will take 4 minutes and 10 seconds. Using a bulk ...


4

I have accidentally updated over 3,000 records in one of my tables hosted on Azure. You are out of luck (since you do not have any backups). Also, it is not possible to use the undocumented command fn_dblog in Azure. It is only supported in SQL Server. Also Transaction logs are no longer managed by the DBA in SQL Azure; this is automatically managed ...



Only top voted, non community-wiki answers of a minimum length are eligible