SQL Server 2008: Overview of Innovations

SQL Server 2008: Overview of Innovations

Microsoft SQL Server is a proprietary database management system.
providing network multi-user access
uses extended T-SQL query language.
It has been leading its history since 1989; the original version was created by Sybase.
In the previous 2005 version, CLR support was introduced, which allowed
write procedures using languages ​​that run on the .Net platform.

Version history:
• 1992 - SQL Server 4.2
• 1993 - SQL Server 4.21 under Windows NT
• 1995 - SQL Server 6.0, codename SQL95
• 1996 - SQL Server 6.5, codename Hydra
• 1999 - SQL Server 7.0, codename Sphinx
• 1999 - SQL Server 7.0 OLAP, codenamed Plato
• 2000 - SQL Server 2000 32-bit, codename Shiloh (version 8.0)
• 2003 - SQL Server 2000 64-bit, codename Liberty
• 2005 - SQL Server 2005, codename Yukon (version 9.0)
• 2008 - SQL Server 2008, codename Katmai (version 10.0)


SQL Server is used in many enterprises, with many companies still using the 2000 version
and many DBAs say that better than 2000 Microsoft has not yet been able to do
This is especially true for Management Studio.
')
SQL Server 2008 appeared in August 2008.

More information about the history of MSSQL can be found in WikiPedia



1. Assigning variables to a single line.

Now instead of:
DECLARE @myVar intSET @myVar = 5

You can write like this:
DECLARE @myVar int = 5


2. Mathematical syntax

DECLARE @myVar int = 5
SET @myVar += 1


3. Compression.

a) You can enable compression for tables / partitions of tables, indexes.
b) Levels of compressions - 2: 1) Line, 2) Page
c) lowercase is included in page compression.
In page compression, there is a compression type based on column prefix matching.
d) Backups are automatically compressed.

In general, it is reported that the processor load may increase and memory usage will decrease.

You can enable compression through Management Studio:
Instead of PAGE, you can use ROW and select table partitions.

And the generated compression script:

USE [test_db]

ALTER TABLE [dbo].[TestTable] REBUILD PARTITION = ALL
WITH (DATA_COMPRESSION = PAGE)


Or this:

USE [test_db]

CREATE NONCLUSTERED INDEX IX_INDEX ON TestTable (TestTableVarFieldOne)
WITH ( DATA_COMPRESSION = PAGE ) ;


Additional information
License Cons: Compression is available only in SQL Server 2008 Enterprise and Developer editions.



4. Appeared indexes with filtering.

CREATE NONCLUSTERED INDEX IX_TestTable_OneON TestTable(TestTableVarFieldOne)
WHERE TestTableVarFieldOne = 'SampleText'

True, for some reason they did not work for me.
Apparently it was impossible to organize the primary key ...


Examples are described here: [1] [2]

What is it for:

For example, we know that the column very often includes some text request from the category = 'SampleText', we put this index, and our query execution plan becomes tasty and fast.


5. SQL Server 2008 has automatic auditing.
It is called from Management Studio from the Security group.

Let's create an audit rule, by the way, the “Maximum” parameter, followed by the “File Path” parameter, means how many files will be created in the folder.

Now we put the audit on the server as a whole or the Database.




For example, we need to put an audit on who viewed the data from the “TestTable” table, which has “db_datareader” rights:



To do this, we define the type of action (there are, by the way, about 30), the class of the object (DB / Schema / Object), the name of the object and the group of rights.

The same can be done in T-SQL:

USE [test_db]
GO

CREATE DATABASE AUDIT SPECIFICATION [TestTableAuditOnView]
FOR SERVER AUDIT [TestAudit]
ADD (SELECT ON OBJECT::[dbo].[TestTable] BY [db_datareader])WITH (STATE = OFF)GO

Well, then do not forget to make our audit "Enable"
You can read more about auditing here or here.


6. New debugger.

Here you can see the screencast: http://www.screencast.com/t/Dgohd2wCkfG

The debugger itself is a simple reminder of a strongly debugger in Visual Studio,
walks in breakpoints, and in Step Into / Step Out, you can put Watch, watch Call Stack,
automatically go into triggers.

In the 2005 version, this could be done either from Visual Studio,
or, as far as I remember, from Business Intelligence Studio and which is not very convenient,
and rights for developers will have to give no more, no less than "sysadmin"

Read about debugging in SQL Server 2005


7. Transparent database encryption.

Transparent encryption is also TDE, a very useful feature, but most likely it will be useful only on specific tasks when the data is critical from a security point of view, or when it is necessary to solve the problem:
“Who will guard the guards themselves” —when one administrators need to give access to one, second to second, and third to third, and if someone from this triple drags off a magazine or backup or mdf files, they will be useless because of encryption.



Read more: [here] or [here]

License Cons: Available only in Developer / Enterprise versions.


8. Freezing the query plan (Plan freezing).

As you know, SQL Server sometimes tries to change the query plan, depending on how the data has changed (database schema).

It is necessary for two things:
1) So that the server does not waste time recalculating the plan,
2) so that the server does not "optimize" the plan
This is a fairly large amount of work, so it's better to look at Virtual Lab.

Later, if this topic is interesting, it can be opened.

Virtual Lab


9. Resource Governor (separation and control over server resources).

In Management Studio, the menu item is in the “Management” group in the Object Explorer window.


You can read [here]
And see [here]


10. New data types (DATE, TIME, DATETIMEOFFSET, DATETIME2, Hierarchyid, GEOMETRY, GEOGRAPHY, FILESTREAM)

a) DATE - store only the date
b) TIME - store only time
c) DATETIMEOFFSET - store the date and time with “+” or “-” offsets
d) DATETIME2 - store the date and time from January 1, 0001 to December 31, 9999
e) HierarchyId - we store the data of hierarchies and the hierarchy tree will be rather compact.

By the way, this type is well described in the article XaocCPS ( http://habrahabr.ru/blogs/sql/27774/ )


f) Geometry and Geography are special types that contain vector objects:

Object	Descripton
Point	A location
Multipoint	A series of points.
LineString	A series of zero or more points connected by lines.
MultiLineString	A set of linestrings
Polygon	A contiguous region is described as a set of closed linestrings.
Multipolygon	A set of polygons.
GeometryCollection	A collection of geometry types.

g) FileStream - store data in the file system
You can read [here]

The differences are that the distances in the Geography type are expressed as degrees of longitude and latitude, and Geometry in a specific Unit.

You can read [here] , the screencast on Geography


11. Table Value Parameters (you can transfer tables as a parameter)

What may be the need to transfer a table from the application to the database?

1) In order to reduce the number of INSERT / UPDATE operations,
2) To transfer some parts of the business logic layer to the server

Pros:
1) Strong typing
2) Sort
3) We can use indexes in these tables (primary key)
4) Convenience

Minuses:
You can bloat the bad, especially for people who are very creative (for example, the procedure generates HTML code)


Sample SQL code:

Use testDatabase
GO
CREATE TYPE Customer AS TABLE (id int, CustomerName nvarchar(50), postcode nvarchar(50));
GO
CREATE TABLE Customers (id int, CustomerName nvarchar(50));
GO
CREATE TABLE CustomerPostCodes(id int, postcode nvarchar(50));
GO
/*
INSERT INTO Customers VALUES (1, 'Bob')
INSERT INTO CustomerPostCodes VALUES (1, 'ASD')
INSERT INTO Customers VALUES (2, 'Jack')
INSERT INTO CustomerPostCodes VALUES (2, 'QWE')
INSERT INTO Customers VALUES (3, 'Gill')
INSERT INTO CustomerPostCodes VALUES (3, 'ZXC')
GO
*/
CREATE Procedure AddCustomers (@customer Customer READONLY)
AS
INSERT INTO Customers
SELECT id, CustomerName FROM @customer

INSERT INTO CustomerPostCodes
SELECT id, postcode FROM @customer
GO

DECLARE @myNewCustomer Customer;
INSERT INTO @myNewCustomer VALUES (1, 'Harry', 'NEW')
EXEC AddCustomers @myNewCustomer
GO

SELECT * FROM Customers
SELECT * FROM CustomerPostCodes
GO

Drop table Customers;
go

Drop table CustomerPostCodes;
go

Drop procedure AddCustomers;
go

Drop type Customer;
go




From C #, respectively, passed to the DataTable parameter.
View screencast


12. Goodies of the New Management Studio:

a) IntelliSense (survived yet)


b) Convenient lighting (tooltips):

Unfortunately, “+ =” is only a mathematical operator ((

c) She supports the Addins.

d) Integrated new server and convenience features - let me have “only 1000 lines”




e) Activity Monitor updated
It is easier to write that here you can see the “big picture”, although it’s still better to watch the private details of the SQL Profiler, it is more suited for this, and these counters are partially borrowed from Windows Server 2008 / Vista “Performance Monitoring”.

In general, we see who, why, where, what, and how much it will cost nm resources.


Pretty pleasant "logger recent resource-intensive requests":


Especially nice to see immediately the execution plan for the query


Cons: You can not remove unnecessary columns or change their places ...


f) Multi-server requests.

You can perform the query (s) on a server group, for this you need to open the menu item (View -> Registered Servers -> New Query).



PS

SQL Server 2008 on memory usage seems to me more efficient than 2005, Management Studio has become more convenient, new data types can give more convenience in development, I also see that a lot has been added, but for about another year I will not install on production servers due to the fact that I will wait for numerous product reviews, especially for feedback from those who will use TDE, DataTable as a parameter and stability.


Pss

Do not minus much, the first post, I will do less pictures later, ran to work.