SQL 101 : Revising Replication

SQL Server Replication Crib Sheet

SQL Server 2005 Replication Enhancements

New replication features in SQL Server 2008 and what they mean to you

Boost replication performance with SQL Server 2008

Replication Optimization Tips (old post but largely still relevant)

SQL 101 : Indexes

22/04/2010
Moved content to Indexes fixed page.

SQL 101 : Temporary Tables

CREATE TABLE #Table1 (ID INT, Name VARCHAR(50) )

There are 2 types of temporary table. Both are stored in the tempdb database.

Local Temporary Tables (prefix #)
Available to the current session (connection per user) only.
Deleted when the session ends (user disconnects).

Global Temporary Tables (prefix ##)
Available to the all connected sessions, after creation.
Deleted when all sessions that used the temp table disconnect.

Global Temp tables are especially useful when dealing with dynamic sql to pass data between sql sessions e.g. sp_executesql results sets.

SQL 101 : SQL 2005 Schemas

‘User Schema Separation’ is allegedly an ANSI SQL 1999 standard but documentation on ansi standards is not freely available on the web.

2000 : [server].[database].[owner].object

2005 : [server].[database].[schema].object

Prior to SQL 2005, each database object had 1 owner.

In SQL Server terminology, the concept of a Schema was introduced in SQL 2005 to provide a logical way to group database objects, ease administration and address security.

Benefits of SQL 2005 Schema addition >

Database Structure	Organization Subdivide database into areas of interest / business area by assigning tables with schema names. For example in the AdventureWorks database > HumanResources.Employee Person.Address Sales.CreditCard This is especially helpful in databases with a large number of tables,
Easier Assignment of permissions	Logical Permissions Grouping Permissions assigned at schema level make it easier to control relevant access to developers etc by subject area. Reduced Administration One command versus many when granting access to a set of objects when grouping them by schema.
Objects not reliant on users	Duplicate Objects In SQL 2000, objects being assigned to users means that 2 users could create objects with the same name which is confusing to say the least. Removing Users Objects not being directly tied to user accounts means that dropping user accounts is much easier. It can be accomplished without either dropping all the owned objects or changing the object owner for any linked objects. Users that OWN a schema now cannot be dropped however without changing the schema ownership.
Ease of teamworking	Multiple users can share a schema Multiple users can OWN a schema through a role or group membership. ‘Default Schema’ setting at user level prevents users having to explicitly reference a schema, for example – if they always use they same one.
Security	The addition of schemas means that combined with object permissions, many more levels of security can be achieved. Access to system objects can now be controlled by a user’s permissions on the SYS schema, unless of course their server role overrides this.
Script Order	Encompassing create statements for related objects inside the same schema makes scripting order unimportant. For example > CREATE SCHEMA UserInfo CREATE TABLE Logins ( UserId INT NOT NULL REFERENCES Users (UserId), LoginDate DATETIME ) CREATE TABLE Users ( UserId INT NOT NULL PRIMARY KEY ) GO

SQL 2000 / 2005 Underlying tables >

	2000 table	2005 tables
Server level	syslogins	sys.server_principals
Database level	sysusers	sys.database_principals sys.schemas

SQL 101 : Security Links

SQL 2005

Microsoft Whitepaper - SQL Server 2005 Security Best Practices - Operational and Administrative Tasks
http://www.microsoft.com/technet/prodtechnol/sql/2005/sql2005secbestpract.mspx

SQL Server 2005 Best Practices Analyzer
http://www.microsoft.com/downloads/details.aspx?FamilyId=DA0531E4-E94C-4991-82FA-F0E3FBD05E63&displaylang=en

Technet - SQL Sever 2005 - Security & Protection
http://technet.microsoft.com/en-gb/sqlserver/bb331769.aspx
Publish Post
SQL 2000

SQL Server 2000 Security Features & Best Practices
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sp3sec01.mspx


General Security

Microsoft Security Bulletin
http://www.microsoft.com/technet/security/Bulletin/advance.mspx

sqlsecurity.com
http://www.sqlsecurity.com/

insecure.org
http://insecure.org/

SQL 101 : Storage

SQL Server Storage : Pages

A SQL database is made up of logical pages. A page is 8Kb (8192 bytes) in size and is used by both tables and indexes to store information. 8Kb is therefore the size of an input/output unit in SQL server.

*** Some special pages also exist for systems management purposes for example the ‘Global Allocation Map’, ‘Index Allocation Map’ and ‘Page Free Space’.

In versions up to SQL 2000, 8kb was the maximum row size in a table.

Prior to SQL 2005, defining a table with a larger row size is possible, but populating the columns fully was not.

For example, in SQL 7 - 2000 , the sql >

create table rubbishtable (

rubbishcolumn1 varchar (8000),
rubbishcolumn2 varchar (8000)

)

Returns the warning >

Warning: The table 'rubbishtable' has been created but its maximum row size (16023) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.

SQL 2005

In SQL 2005, the page size is still 8Kb but you can exceed 8k per row.
This is achieved by moving the largest column to a second page into a ‘ROW_OVERFLOW_DATA unit’. The original page is then updated by adding a 24 byte pointer to this second page. This is known as the ‘IN_ROW_DATA allocation unit’.

You don’t see this functionality happening. ‘Oh great’ , ‘I can now fit more data into a row’ is likely to be a developer’s response to this new feature.

From a DBA’s point of view however, utilising this new functionality has performance implications.
Performing queries on tables where the data is split across pages will be marginally slower due to 2 pages being returned for each offending record.

Also, when updates are done which force the creation or deletion of overflow pages this will cause fragmentation in the database.



SQL Server Storage : Extents

An Extent is a group of 8 pages and hence is (8x8kb) 64Kb in size. This is the smallest unit of data SQL Server can allocate.

There are two types of extent.

Uniform Extent All 8 pages are assigned to a single object.

Mixed Extent More than 1 (small) object is placed inside the extent, ensuring space is not wasted.




SQL Server Storage : Files

Each datafile in a SQL database has 2 names, a logical file name (used to refer to the data store from transact SQL) and a physical one. The Physical one is the one stored on the operating system.


SQL databases have 3 filetypes.

Every database has a primary data store. The recommended (but not enforced) file extension is .MDF.

Some databases have (1 or more) secondary data files. The recommended (but not enforced) file extension is .NDF.

Log Files should have a .LDF extension. The log file (also known as the transaction log) holds a log of changes which may be helpful in recovering the datanase. When the recovery model is set to ‘simple’ they contain minimal information however.


SQL Server Storage : File Groups

File groups come in 2 flavours, ‘Primary’ or ‘User Defined’. Either of these can be the default file group i.e where all user objects are created (unless filegroup is specified).

File groups allow you to place heavily accessed tables in a different filegroup. Reasons for this could be to improve database performance (e.g housing the filegroup on a different disk array) or to backup that data separately.

SQL 101 : Date Formatting

Casting a value as DateTime validates it -

-- SQL DateTime Validation
SELECT CAST('2006-03-31' AS datetime) -- Succeeds, 31 days in March
SELECT CAST('2006-04-31' AS datetime) -- Fails, only 30 days in April

SELECT CAST('2006-02-29' AS datetime) -- Fails, No 29th day in Feb 2006
SELECT CAST('2004-02-29' AS datetime) -- Succeeds, was a leap year
SELECT CAST('2002-02-29' AS datetime) -- Fails, No 29th day in Feb 2002
SELECT CAST('2000-02-29' AS datetime) -- Succeeds, was a leap year

When language is set to us_english, date format of 'mdy' expects the month to be provided before the day.

SET LANGUAGE us_english  -- Changed language setting to us_english.
SELECT CAST('2006-03-31' AS datetime) -- works
SELECT CAST('03-31-2006' AS datetime) -- works
SELECT CAST('31-03-2006' AS datetime) -- fails

When language is set to british, date format of 'dmy' expects the day to be provided before the month.

SET LANGUAGE british  -- Changed language setting to British.
SELECT CAST('2006-03-31' AS datetime)  -- fails
SELECT CAST('2006-31-03' AS datetime)  -- works
SELECT CAST('31-03-2006' AS datetime) -- works

In both of these cases, SQL correctly interprets the year, whether at the start or end of the string.
The failures generate -
Msg 242, Level 16, State 3, Line 7
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

Using set dateformat to override the language setting -

SET DATEFORMAT 'dmy'
-- example
SET LANGUAGE us_english  -- Changed language setting to us_english.
SET DATEFORMAT 'dmy'
SELECT CAST('31-03-2006' AS datetime) -- now succeeds.

Best Practice is to use a language neutral date format -

-- ISO 8601 format doesnt care about language >
SELECT CAST('2006-03-31T00:00:00' AS datetime)
-- Neither does removing the '-'
SELECT CAST('20060331' AS datetime)

More on language neutral date formats here - http://www.karaszi.com/SQLServer/info_datetime.asp

2 ways to get the current time & date -

SELECT CURRENT_TIMESTAMP
SELECT getdate()

SQL 101 : Network Debugging

Testing Connectivity	ping / tracert / pathping
Establish session on Port number	telnet
Server Name Lookup	NSLookup
View / Configure IP Address	ipconfig