Tag Info

Hot answers tagged

22

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


9

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

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


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


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

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


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

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


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

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


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

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


4

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


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


3

Short answer: as of April 20, 2012 column encryption is not supported in SQL Azure, see: http://msdn.microsoft.com/en-us/library/windowsazure/ee336253.aspx and search for ENCRYPT. You will see that all of the encryption functions are listed as unsupported. If you need to encrypt your data at rest and have to support SQL Azure then you are going to have to ...


3

It won't help you trace existing calls from your website but you can still obtain interesting performance metrics by running SQL commands from this tool directly: Enzo SQL Baseline. It offers options to load commands in parallel to simulate load and helps you compare performance metrics over multiple runs.


3

Group by your condition SELECT ItemCreationDate, COUNT(ItemId) AS ComputedCount, CASE WHEN ItemUnitsCount = 0 THEN 2 ELSE 1 END AS CountType FROM Items GROUP BY ItemCreationDate, CASE WHEN ItemUnitsCount = 0 THEN 2 ELSE 1 END;


3

SELECT ItemCreationDate , SUM(CASE WHEN ItemUnitsCount <> 0 THEN 1 ELSE 0 END) AS CountType1 , SUM(CASE WHEN ItemUnitsCount = 0 THEN 1 ELSE 0 END) AS CountType2 FROM dbo.Items GROUP BY ItemCreationDate


3

Matthew, I don't have direct experience with SQL Azure but I think same rules apply here as a normal SQL Server instance. 280 mb is a very very small database and the cost of fragmentation is almost 0. Coming to the size of this small database, I don't think you can control that nor should you be worried. The above is because when SQL Server creates a new ...


3

SSIS could certainly take care of this for you but as with many things there are several ways to complete the task. 1) You have several options within SSIS to complete the copy process, Copy Database is one of them. You could backup the database and restore it using a SQL Script. You may need to use the file system task to move the backup file but that ...


3

Developers should produce code, and there are obviously many ways to sync code changes (source control). Never touch the database. Ever. If they need any change in the database, modify the code that deploys/upgrades the database. Rails's Migrations are an excellent example of doing it the right way. Visual studio has Database Projects. You can always roll ...


3

Okay, first thing, I need to pick at this a little bit: We went down the route of making these reads readUncommitted since the operations on the data are absolutely not mission critical. Using READ UNCOMMITTED/NOLOCK should only be considered when the accuracy of the results is not critical, because that's what the transaction isolation level ...


3

I recommend either of the following: Michell Ufford's index defragmentation script Ola Hallengren's index and statistics maintenance script Either of these can accomplish the task of maintaining indexes without requiring you to rebuild the scripts when indexes are added or removed. One of the biggest problems I've seen with using the maintenance plan ...


3

Have you looked at SQL Server Data Tools? This has schema compare built-in, and can also identify objects and language constructs in your on-premises database that aren't compatible with Azure. It is free to download from Microsoft.


3

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



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