Tag Info

Hot answers tagged

20

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


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

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


6

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


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.


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

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


4

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


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


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

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

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

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

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

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

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


3

Removing Execution Plans from the Procedure Cache SQL Azure currently doesn’t support DBCC FREEPROCCACHE (Transact-SQL), so you cannot manually remove an execution plan from the cache. However, if you make changes to the to a table or view referenced by the query (ALTER TABLE and ALTER VIEW) the plan will be removed from the cache. Ref: here


3

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


3

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


3

Traditionally SQL Server relied on technologies like SQL Server Replication, Log Shipping or Change Data Capture/Change Tracking to replicate data across multiple locations. Unfortunately none are available in SQL Azure. Instead SQL Azure has the SQL Data Sync feature: SQL Data Sync (Preview) is a service of SQL Database that enables you to synchronize ...


3

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

Study the following: BEGIN TRANSACTION, ROLLBACK TRANSACTION, COMMIT TRANSACTION Also read Paul Randall's A SQL Server DBA myth a day: (26/30) nested transactions are real Also read XACT_ABORT @@ROWCOUNT IF ...ELSE GOTO @@TRANCOUNT More advanced topics, optional until they're not: SET TRANSACTION ISOLATION LEVEL Gail Shaw's intro to deadlocks Then ...


2

I noticed there's a new tool in this list, it's Idera's Azure SQL Database Backup. It's free and they usually make good tools, so it's worth a try. Another way of exporting databases (but this time only schema, no data) to Azure is by using DAC packages (.dacpac files extracted from Management Studio or Visual Studio 2010). This works only from SQL 2008 R2 ...


2

In addition to the other answers, you can also use the CSS SQL Azure Diagnostics tool from Microsoft support to run some queries on the server that show you missing indexes and popular queries.


2

You probably can't as the procedure cache is shared across all the databases which are hosed on the server that your database is hosted on. All you can do it wait for the old queries to be flushed from cache using normal aging out that the procedure cache uses. Forcing a recompile of the table objects would probably do the trick as well.



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