My tip to all is to have a deployment script that only contains what you realy need and not a generated ones that specifies each and every default option. Why: SQL Azure does not know many of the default options and therefore the script fails in that case.
]]>
DECLARE @MyText AS NVARCHAR(100) = 'She's the boss';
You can already see on the syntax coloring that something is wrong. To put a single quote inside a string literal you must precede it with another single quote
DECLARE @MyText AS NVARCHAR(100) = 'She''s the boss';
If you run the whole batch below
DECLARE @MyText AS NVARCHAR(100) = 'She''s the boss';
SELECT @MyText;
You will see it produces the desired result: She's the boss
To give you another example the text ‘I am between single quotes’ would look like this
DECLARE @MyText AS NVARCHAR(100) = '''I am between single quotes''';
SELECT @MyText;
I got a very strange request: We have to constantly count the rows in a table can we increase the performance of count(*)
So lets analyze the problem step by step.
The table is a typical table the actual columns have no effect so I replaced them with just one column in the example to make things easier; Let's start by creating a heap
CREATE TABLE MyTable
(
ID intNOTNULLIDENTITY(1,1),
Payload varchar(300)NOTNULL
)
and fill it with random data
DECLARE @i ASint= 1;
WHILE (@i < 1000)
BEGIN
INSERTINTO MyTable(Payload) VALUES (REPLICATE('ABC', 20 + @i % 80));
SET @i += 1;
END
Now lets see where we are on a heap
STATISTICSIOON
SELECT COUNT(*)FROM MyTable
Table 'MyTable'. Scan count 1, logical reads 25, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
To count the rows SQL Server must do a full table scan and reads each page exactly once.
Let's see if an index will help us…
CREATE CLUSTEREDINDEX CI_MyTable ON MyTable(ID); SELECT COUNT(*)FROM MyTable;
Table 'MyTable'. Scan count 1, logical reads 27, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server now performs a clustered index scan which is in fact a table scan plus the overhead to find the first page. Actually a little bit more work than the table scan.
The trick to get it faster is to create an index that uses less space that the table (SQL Server will always select the smalest index to execute a COUNT(*))
The Smallest INDEX you can create on a table that has a clustered index is a secondary index that is composed just out of the clustered key:
CREATE INDEX IX_ID ON MyTable(ID);
SELECT COUNT(*)FROM MyTable;
Table 'MyTable'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Now it just has to Scan the leaf nodes of the secondary index
Careful this kind of index has very limited use besides the example above.
Other uses would be for example: SELECT xyz WHERE uvw IN (SELECT ID FROM MyTable)
]]>This are either tips and tricks that define a very uncommon special case or are just for very very high end solutions and the practical application is limited. So watch out for the category and don't be surprised when you read a post marked like this when the content does not apply to you.
But on the other hand there will be a bunch of SQL Server users that are just looking for that information
]]>
You can backup full, differential and log backups to multtiple locations the MIRROR TO option is build in into all of them
I do not backup full backups to two locations (you can alsways take the backup before the last to start your restore if you lose the last one) but I backup my logs to two locations to reduce the risk of a broken log chain by a missing or corrupt file.
If you are really paranoid you should write the data trough two different network cards from different manufactuers with different drivers to avoid data corruption by a faulty driver and then have a constant verify by restore on all destinations.
]]>