joeblogs.ie

Discovered this website at the weekend from Brent Ozar providing tutorials, training videos and even posters. It has some excellent content particularly for query tuning.

SQL Server sql server

A very misleading line in the SQL server 70-431 book states “…keep in mind that indexes are limited to a maximum of 900 bytes.” Was not sure what this meant, but became much clearer after referring to MSDN Just to clarify it is the sum of the lengths of the fixed-size columns that make up the index that cannot exceed 900 bytes. Below is a way of determining the size of a potential index:
SELECT SUM(max_length)AS TotalIndexKeySize FROM sys.columns WHERE name IN (N'AddressLine1', N'AddressLine2', N'City', N'StateProvinceID', N'PostalCode') AND object_id = OBJECT_ID(N'Person.Address');

SQL Server sql server, 70-431

Available to download from here

SQL Server sql server

This is an extremely useful presentation from Brad McGehee describing the best practices for SQL server developers should know to administer SQL server Available here

SQL Server sql server

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'C:\my_mdb.MDB';'ADMIN';'' , table_name) Reference: http://msdn.microsoft.com/en-us/library/ms190312.aspx

SQL Server t-sql, openrowset

Some guidelines that may help to improve performance in SQL server by reducing disk contention: - Put data files on separate drive from OS files - Put transaction log files on a separate drive from data files, reduces contention between data and transaction log files And if at all possible put tempdb on a separate drive.

SQL Server 70-431

Received this error when trying to open surface configuration area in SQL server. Solution? Restart and all ok

SQL Server sql server

After running into problems with collation again, decided to delve deeper into collation. So What does SQL server use collation for? It uses collation to determine how non-Unicode character data is stored and how to sort and compare Unicode and non-Unicode date.

So if all character data is stored as Unicode, does this circumvent any potential collation problems in SQL server? According to this article from MSDN this is precisely the way to avoid this:

The easiest way to manage character data in international databases is to always use the Unicode nchar, nvarchar, and ntext data types in place of their non-Unicode equivalents (char, varchar, and text). If all the applications that work with international databases also use Unicode variables instead of non-Unicode variables, character translations do not have to be performed anywhere in the system. All clients will see exactly the same characters in data as all other clients.

SQL Server collation

This table shows which service pack applies to which version of SQL server 2005:

SQL Server sql server

Frustratingly the 64-bit version of SQL server does not support Jet OLE DB so bang goes any applications which need to export data to Access…Not sure if there is an alternative.

Abstract below is from this MSDN article

Some .NET Framework Data Providers and native OLE DB providers might not be available in 64-bit versions. For example, the Microsoft OLE DB Provider for Jet, which connects to Access databases and Excel spreadsheets, is not available in a 64-bit version.

SQL Server linked server