SQL 2008 Features


MERGE Statement
One of the common tasks in database
management is the maintenance of lookup tables.  Maintenance of these
look-up tables are usually done using stored procedures where the normal task
is to add the new record if it does not exist or update the existing record if
it already exists based on the identified primary key of the table.  SQL
Server 2008 introduces a new statement called MERGE statement which combines
these functionalities into just a single statement.  The MERGE statement
performs an INSERT, UPDATE or DELETE operation on a target table based on the
results of a join with source table.

Sparse Column
SPARSE column are better at managing NULL and
ZERO values in SQL Server. It does not take any space in database at all. If
column is created with SPARSE clause with it and it contains ZERO or NULL it
will be take lesser space then regular column (without SPARSE clause).
In SQL Server 2008 maximum column allowed per
table is 1024. All the SPARSE columns does not count to this limit of 1024. The
maximum limit of SPARSE column is 100,000. In summary any table can have
maximum of 100,000 SPARSE and 1024 regular columns.

CREATE TABLE UnSparsed(ID INT IDENTITY(1,1),
FirstCol INT,SecondCol VARCHAR(100),
ThirdCol SmallDateTime)
GO
CREATE TABLE Sparsed(ID INT IDENTITY(1,1),
FirstCol INT SPARSE,
SecondCol VARCHAR(100) SPARSE,
ThirdCol SmallDateTime SPARSE)

DATE/TIME Data Types 
SQL Server 2008 introduces new date and time data types
DATE—A date-only type
TIME—A time-only type
DATETIMEOFFSET—A time-zone-aware datetime type
DATETIME2—A datetime type with larger fractional seconds and
year range than the 
existing DATETIME typE
22 New Features of Visual Studio 2008 for .NET Professionals
  • Transparent Data Encryption
Enable encryption of an entire database, data files, or log files, without the need for application changes. Benefits of this include: Search encrypted data using both range and fuzzy searches, search secure data from unauthorized users, and data encryption without any required changes in existing applications.
  • Extensible Key Management
SQL Server 2005 provides a comprehensive solution for encryption and key management. SQL Server 2008 delivers an excellent solution to this growing need by supporting third-party key management and HSM products.
  • Auditing
Create and manage auditing via DDL, while simplifying compliance by providing more comprehensive data auditing. This enables organizations to answer common questions, such as, "What data was retrieved?"
  • Enhanced Database Mirroring
SQL Server 2008 builds on SQL Server 2005 by providing a more reliable platform that has enhanced database mirroring, including automatic page repair, improved performance, and enhanced supportability.
  • Automatic Recovery of Data Pages
SQL Server 2008 enables the principal and mirror machines to transparently recover from 823/824 types of data page errors by requesting a fresh copy of the suspect page from the mirroring partner transparently to end users and applications.
  • Log Stream Compression
Database mirroring requires data transmissions between the participants of the mirroring implementations. With SQL Server 2008, compression of the outgoing log stream between the participants delivers optimal performance and minimizes the network bandwidth used by database mirroring.
  • Resource Governor
Provide a consistent and predictable response to end users with the introduction of Resource Governor, allowing organizations to define resource limits and priorities for different workloads, which enable concurrent workloads to provide consistent performance to their end users.
  • Predictable Query Performance
Enable greater query performance stability and predictability by providing functionality to lock down query plans, enabling organizations to promote stable query plans across hardware server replacements, server upgrades, and production deployments.
  • Data Compression
Enable data to be stored more effectively, and reduce the storage requirements for your data. Data compression also provides significant performance improvements for large I/O bound workloads, like data warehousing.
  • Hot Add CPU
Dynamically scale a database on demand by allowing CPU resources to be added to SQL Server 2008 on supported hardware platforms without forcing any downtime on applications. Note that SQL Server already supports the ability to add memory resources online.
  • Policy-Based Management
Policy-Based Management is a policy-based system for managing one or more instances of SQL Server 2008. Use this with SQL Server Management Studio to create policies that manage entities on the server, such as the instance of SQL Server, databases, and other SQL Server objects.
  • Streamlined Installation
SQL Server 2008 introduces significant improvements to the service life cycle for SQL Server through the re-engineering of the installation, setup, and configuration architecture. These improvements separate the installation of the physical bits on the hardware from the configuration of the SQL Server software, enabling organizations and software partners to provide recommended installation configurations.
  • Performance Data Collection
Performance tuning and troubleshooting are time-consuming tasks for the administrator. To provide actionable performance insights to administrators, SQL Server 2008 includes more extensive performance data collection, a new centralized data repository for storing performance data, and new tools for reporting and monitoring.
  • Language Integrated Query (LINQ)
Enable developers to issue queries against data, using a managed programming language, such as C# or VB.NET, instead of SQL statements. Enable seamless, strongly typed, set-oriented queries written in .NET languages to run against ADO.NET (LINQ to SQL), ADO.NET DataSets (LINQ to DataSets), the ADO.NET Entity Framework (LINQ to Entities), and to the Entity Data Service Mapping provider. Use the new LINQ to SQL provider that enables developers to use LINQ directly on SQL Server 2008 tables and columns.
  • ADO.NET Data Services
The Object Services layer of ADO.NET enables the materialization, change tracking, and persistence of data as CLR objects. Developers using the ADO.NET framework can program against a database, using CLR objects that are managed by ADO.NET. SQL Server 2008 introduces more efficient, optimized support that improves performance and simplifies development.
  • DATE/TIME
SQL Server 2008 introduces new date and time data types:
    • DATE—A date-only type
    • TIME—A time-only type
    • DATETIMEOFFSET—A time-zone-aware datetime type
    • DATETIME2—A datetime type with larger fractional seconds and year range than the existing DATETIME type
The new data types enable applications to have separate data and time types while providing large data ranges or user defined precision for time values.
  • HIERARCHY ID
Enable database applications to model tree structures in a more efficient way than currently possible. New system type HierarchyId can store values that represent nodes in a hierarchy tree. This new type will be implemented as a CLR UDT, and will expose several efficient and useful built-in methods for creating and operating on hierarchy nodes with a flexible programming model.
  • FILESTREAM Data
Allow large binary data to be stored directly in an NTFS file system, while preserving an integral part of the database and maintaining transactional consistency. Enable the scale-out of large binary data traditionally managed by the database to be stored outside the database on more cost-effective storage without compromise.


  • Integrated Full Text Search
Integrated Full Text Search makes the transition between Text Search and relational data seamless, while enabling users to use the Text Indexes to perform high-speed text searches on large text columns.
  • Sparse Columns
NULL data consumes no physical space, providing a highly efficient way of managing empty data in a database. For example, Sparse Columns allows object models that typically have numerous null values to be stored in a SQL Server 2005 database without experiencing large space costs.
  • Large User-Defined Types
SQL Server 2008 eliminates the 8-KB limit for User-Defined Types (UDTs), allowing users to dramatically expand the size of their UDTs.
  • Spatial Data Types
Build spatial capabilities into your applications by using the support for spatial data.
    • Implement Round Earth solutions with the geography data type. Use latitude and longitude coordinates to define areas on the Earth's surface.
    • Implement Flat Earth solutions with the geometry data type. Store polygons, points, and lines that are associated with projected planar surfaces and naturally planar data, such as interior spaces.
------------------------------------------------------------------------------------------------------------
  • Backup Compression
Keeping disk-based backups online is expensive and time-consuming. With SQL Server 2008 backup compression, less storage is required to keep backups online, and backups run significantly faster since less disk I/O is required.
  • Partitioned Table Parallelism
Partitions enable organizations to manage large growing tables more effectively by transparently breaking them into manageable blocks of data. SQL Server 2008 builds on the advances of partitioning in SQL Server 2005 by improving the performance on large partitioned tables.
  • Star Join Query Optimizations
SQL Server 2008 provides improved query performance for common data warehouse scenarios. Star Join Query optimizations reduce query response time by recognizing data warehouse join patterns.
  • Grouping Sets
Grouping Sets is an extension to the GROUP BY clause that lets users define multiple groupings in the same query. Grouping Sets produces a single result set that is equivalent to a UNION ALL of differently grouped rows, making aggregation querying and reporting easier and faster.
  • Change Data Capture
With Change Data Capture, changes are captured and placed in change tables. It captures complete content of changes, maintains cross-table consistency, and even works across schema changes. This enables organizations to integrate the latest information into the data warehouse.
  • MERGE SQL Statement
With the introduction of the MERGE SQL Statement, developers can more effectively handle common data warehousing scenarios, like checking whether a row exists, and then executing an insert or update.
  • SQL Server Integration Services (SSIS) Pipeline Improvements
Data Integration packages can now scale more effectively, making use of available resources and managing the largest enterprise-scale workloads. The new design improves the scalability of runtime into multiple processors.
  • SQL Server Integration Services (SSIS) Persistent Lookups
The need to perform lookups is one of the most common ETL operations. This is especially prevalent in data warehousing, where fact records need to use lookups to transform business keys to their corresponding surrogates. SSIS increases the performance of lookups to support the largest tables.
  • Analysis Scale and Performance
SQL Server 2008 drives broader analysis with enhanced analytical capabilities and with more complex computations and aggregations. New cube design tools help users streamline the development of the analysis infrastructure enabling them to build solutions for optimized performance.
  • Block Computations
Block Computations provides a significant improvement in processing performance enabling users to increase the depth of their hierarchies and complexity of the computations.
  • Writeback
New MOLAP enabled writeback capabilities in SQL Server 2008 Analysis Services removes the need to query ROLAP partitions. This provides users with enhanced writeback scenarios from within analytical applications without sacrificing the traditional OLAP performance.
  • Enterprise Reporting Engine
Reports can easily be delivered throughout the organization, both internally and externally, with simplified deployment and configuration. This enables users to easily create and share reports of any size and complexity.
  • Internet Report Deployment
Customers and suppliers can effortlessly be reached by deploying reports over the Internet.
  • Manage Reporting Infrastructure
Increase supportability and the ability to control server behaviour with memory management, infrastructure consolidation, and easier configuration through a centralized store and API for all configuration settings.
  • Report Builder Enhancements
Easily build ad-hoc and author reports with any structure through Report Designer.
  • Forms Authentication Support
Support for Forms authentication enables users to choose between Windows and Forms authentication.
  • Report Server Application Embedding
Report Server application embedding enables the URLs in reports and subscriptions to point back to front-end applications.
  • Microsoft Office Integration
SQL Server 2008 provides new Word rendering that enables users to consume reports directly from within Microsoft Office Word. In addition, the existing Excel renderer has been greatly enhanced to accommodate the support of features, like nested data regions, sub-reports, as well as merged cell improvements. This lets users maintain layout fidelity and improves the overall consumption of reports from Microsoft Office applications.
  • Predictive Analysis
SQL Server Analysis Services continues to deliver advanced data mining technologies. Better Time Series support extends forecasting capabilities. Enhanced Mining Structures deliver more flexibility to perform focused analysis through filtering as well as to deliver complete information in reports beyond the scope of the mining model. New cross-validation enables confirmation of both accuracy and stability for results that you can trust. Furthermore, the new features delivered with SQL Server 2008 Data Mining Add-ins for Office 2007 empower every user in the organization with even more actionable insight at the desktop.

 

New Features in SQL Server 2008

The release of SQL Server 2008, code-named Katmai, is around the corner. Scheduled for 2008, it promises to add several important features to Microsoft's premier database server. Some of these features Microsoft didn't have time to put in the SQL Server 2005 release, while others are new.

7. Enhanced Office Integration


SQL Server 2008's enhanced integration with Microsoft Office 2007 enables users to create database-enabled reports directly from either Microsoft Office Word 2007 or Excel 2007. These reports can then be published and shared by using Microsoft Office SharePoint Server 2007.

6. Language-Integrated Query


LINQ is Microsoft's latest application data-access technology. It enables Visual Basic and C# applications to use set-oriented queries that are developed in the native language, rather than requiring that the queries be written in T-SQL. LINQ uses the native SqlClient to connect to SQL Server, and its development-time connection lets developers create strongly typed queries.

5. Transparent Failover


Database mirroring in SQL Server 2005 enables clients to automatically redirect to a mirrored server in the event of a failure in the principal database. However, it requires special client configuration to specify the mirror server. SQL Server 2008 will allow clients to automatically fail over to a mirrored server without requiring any manual configuration on the client.

4. Log Stream Compression


SQL Server 2008's new support for log stream compression enhances the performance of database mirroring by automatically compressing the log stream that's sent between the database mirroring participants. This minimizes network bandwidth use.

3. Data Compression


SQL Server 2008's data compression enables you to compress data stored in the database. This reduces storage requirements and can actually improve the performance of workloads that have high I/O requirements. SQL Server 2008 also supports compressing backups.

2. FileStream Data


It's back—originally slated as part of SQL Server 2005, the new FileStream object allows large binary objects (LOBs) to be stored in the file system. The system maintains links between the database object and the file system object, insuring they have transactional consistency similar to a native database object yet providing the performance of native file system storage.

1. Ability to Hot-Add CPUs


SQL Server 2005 introduced the ability to hot-add memory, and SQL Server 2008 extends this with the ability to hot-add CPUs on supported platforms with no downtime, enhancing scalability. This ability will also be an important foundation feature for the dynamic data center, as future virtualization support in the Windows Server 2008 R2 timeframe will support hot-adding for memory, CPU, and networking.

  •  
  • 1. New Features Presentation The SQL Server Team The SQL Server Team RemoteDBA Experts
  • 2. Agenda • Introductions • Top SQL 2008 new features • How we can help • Questions • Wrap-up Much More than Just Great DBA’s
  • 3. Much More Than Just Great DBAs • Focusing on world-class remote database support for all major database products • Over 60 customers and thousands of database servers under our control • Oracle Ace, Microsoft Certified DBA’s on staff • Almost 70 years combined SQL DBA experience. Much More than Just Great DBA’s
  • 4. Top SQL 2008 New Features • Management Studio Improvements • Compression • Resource Governor • New Data Types • Performance Data Collector • Transparent Data Encryption • SQL Server Audit • Extended Events • Change Data Capture • Power Shell Much More than Just Great DBA’s
  • 5. Management Studio Improvements • Activity Monitor • Object Explorer Details • Object Search • Multi Server Queries • Intellisense • T-SQL Debugger Much More than Just Great DBA’s
  • 6. Activity Monitor At a glance performance information Automatically run a trace • Graphs • Processes • Data File I/O • Recent Expensive Queries Much More than Just Great DBA’s
  • 7. Activity Monitor - Graphs • Percent Processor Time • Waiting Tasks • Database I/O • Batch Requests per Second Much More than Just Great DBA’s
  • 8. Activity Monitor - Processes • Provides metrics for key resources • Identifies potential trouble spots • Measures the time a worker thread has to wait gain access to server resources Much More than Just Great DBA’s
  • 9. Activity Monitor – Resource Waits • Provides metrics for key resources • Identifies potential trouble spots • Measures the time a worker thread has to wait gain access to server resources Much More than Just Great DBA’s
  • 10. Activity Monitor – Data File I/O • Quickly determine databases that have high disk usage. • Columns can be sorted and filtered Much More than Just Great DBA’s
  • 11. Activity Monitor – Recent Expensive Queries • Displays the queries in cache that have consumed the most resources. • Right click to display execution plan • Right click to display the entire query Much More than Just Great DBA’s
  • 12. Activity Monitor – Object Explorer Detail • 36 possible columns of information about a database • Views are automatically remembered • CTRL-C copies the information to the clipboard in a tab delimited format, past into Excel to create reports Much More than Just Great DBA’s
  • 13. Activity Monitor – Object Search • Searches for all objects within the selected scope: server, database, tables. • Wild card characters can be used. Much More than Just Great DBA’s
  • 14. Activity Monitor – Multi-Server Queries • Queries all registered servers within the server group. • Returns results for each server. Much More than Just Great DBA’s
  • 15. Activity Monitor – Intellisense • Highlights syntax errors. – Keywords – Object names Much More than Just Great DBA’s
  • 16. Activity Monitor – Intellisense • Completes variables, commands, or object names • Incremental as you type Much More than Just Great DBA’s
  • 17. Activity Monitor – Intellisense • Displays parameters for procedures and functions. • Can display a list of available objects for selection. Much More than Just Great DBA’s
  • 18. Activity Monitor – T-SQL Debugger Much More than Just Great DBA’s
  • 19. Activity Monitor – T-SQL Debugger • Steps into and over T-SQL code. • Developers will be more comfortable with features that they have become accustomed to in .Net • Displays the values of local variables and the call stack. • Start by hitting the F11 key. Much More than Just Great DBA’s
  • 20. Compression • Data Compression – This includes row and page level compression and occurs within the .mdf files. • Backup Compression – Occurs only when data is backed up Much More than Just Great DBA’s
  • 21. Data Compression • Row Level = simplest method of compression. – Reduces the amount of metadata used to store a row. – Uses less CPU cycles to implement. • Page Level = higher level of compression – Higher level of CPU usage Much More than Just Great DBA’s
  • 22. Data Compression - Implementation • Implemented through SSMS Much More than Just Great DBA’s
  • 23. Data Compression - Implementation • Wizard interface, estimates savings. • Caution should be used when implementing in production Much More than Just Great DBA’s
  • 24. Resource Governor • Implementation is straightforward. • Specific security account (DAC) needs to be created. • Implemented with T-SQL • Improper configuration can cause severe performance issues. • Takes effect when a “Resource Contention” occurs Much More than Just Great DBA’s
  • 25. New Data Types – Date and Time • DATE - Stores date only in YYYY-MM-DD format. • TIME -Stores time in hh:mm:ss.nnnnnnn format. • DATETIME2 - More precision than existing DATETIME type. • Format is YYYY-MM-DD hh:mm:ss:nnnnnnnm • DATETIMEOFFSET - Similar to DATETIME2, stores Time Zone Information New Data Types – FILESTREAM • Stored in NTFS file system • T-SQL Insert, Update, Deletes operations can be used on datatypes. Much More than Just Great DBA’s
  • 26. New Data Types – Spatial • Implemented as .Net CLR data type • Conforms to Open Geospatial Consortium standards • Indexes are spatial, stored as a grid based hierarchy • GEOGRAPHY – Stores ellipsoidal data (Longitude and Latitude) • GEOMETRY – Stores planar data (X, Y Coordinates) New Data Types – HIERARCHYID • Implemented as .Net CLR data type • Represents node position in a hierarchical structure Much More than Just Great DBA’s
  • 27. Performance Data Collector • Management Data Warehouse • Server Activities acts as central repository • Performance Reports • Data Collection Sets • Easy Wizard Configuration • Disk Usage • Scheduled jobs run on regular • Query Statistics basis Much More than Just Great DBA’s
  • 28. Performance Data Collector • Different configurations for each Data Collection Set • Built in reports for each Data Collection Set • Can be customized Much More than Just Great DBA’s
  • 29. Transparent Data Encryption • Encrypted in both Memory and on Disk • Key based, allowing for a finer level of security • Encrypts entire database • Easy to administer • Resource requirements are minimal Much More than Just Great DBA’s
  • 30. SQL Server Audit • Granular, captures SELECT, INSERT, UPDATE, DELETE, REFERENCES and EXECUTE • Uses Extended Events engine, increased speed • Capture and view audit results • Audits at the instance or database level • Audits multiple activity types. • Stores audit events to file • Easy to implement using SSMS Much More than Just Great DBA’s
  • 31. SQL Server Audit • Create a new audit with SSMS Much More than Just Great DBA’s
  • 32. SQL Server Audit • Name the audit • Choose items to be audited. • Each audit appears in SSMS Much More than Just Great DBA’s
  • 33. Extended Events • Generic event handling system • Captures, stores and acts on troubleshooting SQL Server, Windows, and applications. • 254 events can be captured from SQL Server • Correlates SQL, Windows, and Application events • Supports SQL Auditing. • Implemented with T-SQL • Handled by the Event Engine • Common format con be combined with different tools • Less overhead than other tools Much More than Just Great DBA’s
  • 34. Change Data Capture • Captures and records Insert, Update, and Delete activity. • Data is stored in a format that can easily be consumed by an application. • Data can then be used to synchronize an OLAP server. Much More than Just Great DBA’s
  • 35. PowerShell • Part of Windows, SQL 2008 ships with PowerShell snap-in • Built on .Net framework 2.0 • Powerful scripting that replaces Kix, Perl, VBScript Much More than Just Great DBA’s
  • 36. Questions? For copies of the this presentation please email Michael John mjohn@remotedbaexperts.com Much More than Just Great DBA’s
  • 37. References • Brad McGehee ISBN: 978-1-906434-06-9 • Microsoft MSDN • SQL Server Central • http://www.microsoft.com/sqlserver/2008/en/us/default.aspx Much More than Just Great DBA’s

SQL SERVER – 2008 New Features
This article describes the top 5 features of SQL Server Management Studio 2008. With the release of SQL Server 2008 Microsoft has upgraded SSMS with many new features as well as added tons of new functionalities requested by DBAs for long time.
SQL Server 2008 has been released for a year now. In SQL Server 2000, DBA had to use two different tools to maintain the database as well as the query database, specifically SQL Server Enterprise Manager and SQL Server Query Analyzer. With the release of SQL Server 2005 both of these tools are combined into one tool: SQL Server Management Studio. For a while DBA who were fan of Query Analyzer requested to bring it back but as they keep on using SQL Server Management Studio (SSMS) they realized that it was much more convenient if every task related to SQL Server could be accomplished using SSMS. With the release of SQL Server 2008 Microsoft has upgraded SSMS with many new features as well as added tons of new functionalities requested by DBAs for long time.
Let us go over a few of the important new features of the SSMS 2008. The list of SQL Server 2008 SSMS improvements is very long. I have selected my favorite 5 features and we will go over them.
IntelliSense for Query Editing
Multi Server Query
Query Editor Regions
Object Explorer Enhancements
Activity Monitors
IntelliSense for Query Editing
This is my favorite feature so far. If you have used Visual Studio before, this feature may not be new to you. Implementation of IntelliSense is not as advanced as in Visual Studio but it is still very useful and well implemented.

After implementing IntelliSense, DBAs and developers will not have to remember all the syntax or browse online references. IntelliSense offers a few additional features besides just completing the world. You can see those option from SSMS Menu >> Edit >> IntelliSense >> (See the available options)

There are a total of five options available in Edit Menu. I suggest to experiment with all of them while playing with IntelliSense.
List Members
Parameter Info
Quick Info
Complete Word
Refresh Local Cache
If your IntelliSense does not bring up recently created objects, try “Refresh Local Cache” as described above, or press CTRL + SHIFT + R.
IntelliSense is a new feature and it will take some time to get adjusted to it. If any developer does not like this option, it can be turned off from Menu >> Query >> (deselect) Enable IntelliSense.
MultiServer Query
Usually DBA don’t manage only one database; they have many servers to manage. There are cases when DBA has to check the status of all the servers. I have seen one of the DBA who used to manage 400 servers, writing query using XML_CMDSHELL where he wanted to find out what the status of fullback up on all the servers was. In one of the recent consultancy job, when I had to find out if all the three servers were upgraded with Services Packs (SP), I ran a query to find version information on all the three instance separately in three windows.
SSMS 2008 has a feature to run a query on different servers from one query editor window. First of all make sure that you registered all the servers under your registered server. Once they are registered Right Click on server group name and click New Query as shown in the image below.

Now in the opened query window run the following query (you can find it in the sample code for this article):
SELECT
SERVERPROPERTY('Edition') AS Edition,
SERVERPROPERTY('ProductLevel') AS ProductLevel,
SERVERPROPERTY('ProductVersion') AS ProductVersion
Query above will give the result shown in the image below. Note that we have only three columns in the SELECT but our output contains four columns. The very first column is the “Server Name” and it is added by SQL Server to indentify rows belonging to a specific server.

If all of the above servers are registered with “central server” – the option which is right below it, other administrators can also register to those entire servers by simple registering one central server.
Query Editor Regions
This feature exists in many programming languages already but now it is newly introduced in SSMS 2008. The reason I am highlighting this feature is because there are cases when T-SQL code is longer than hundreds of lines and after a while it keeps on getting confusing.
The regions are defined by the following hierarchy:
From first GO command to next GO command.
Statements between BEGIN – END, BEGIN TRY – END TRY, BEGIN CATCH – END CATCH
Multiline statements
In the following code snippet I have reported an example which has five coding regions (you can find it in the sample code for this article). I have made small comment besides them describing their region type.
CREATE PROCEDURE MyTest -- Region Type 1
AS
BEGIN -- Region Type 2
SELECT 1
BEGIN -- Region Type 2
SELECT 2;
SELECT * -- Region Type 3
FROM sys.objects;
SELECT * -- Region Type 3
FROM sys.columns;
SELECT 3;
END
END
GO
Regions can be collapsed as well as expanded by clicking the small sign of ‘-’ or ‘+’ besides them. The following image shows a fully expanded region code snippet and a fully collapsed code snippet.

If there is any single-line statement, it does not qualify for a coding region.
Object Explorer Enhancements
Object Explorer Detail initially looks the same as the previous version but when right clicked on the bar with labels it reveals what it can do. This feature looks the same as the Vista OS folder option but when looked at how it is implemented for SQL Server data, it is really amazing. Object Explorer Detail view can be enabled by either going to Menu >> View >> Object Explorer Detail or pressing F7.

In Object Explorer Detail the new feature is Object Search. Enter any object name in the object search box and the searched result will be displayed in the same window as Object Explorer Detail.

Additionally, there are new wizards which help you perform several tasks, from policy management to disk monitoring. One cool thing is that everything displayed in the object explorer details screen can be right away copied and pasted to Excel without any formatting issue.
Activity Monitors
This feature is totally revamped in SSMS 2008. When opening “Activity Monitor” it shows a screen similar to the following image.

There are four graphs 1) % Processor Time, 2) Waiting Tasks, 3) Database I/O, 4) Batch Requests/Sec
Additionally, there are four tabs which also reflect some statistics of the above four graphs 1) Processes, 2) Resource Waits, 3) Data File I/O, 4) Recent Expensive Queries. Clicking on the tables will expand the tabs as shown in the following image.
All the four tabs provide very important information, however the one which I refer most is ‘Recent Expensive Queries’. Whenever I find my server running slow or having any performance related issues my first reaction is to open this tab and see which query is running slow. I usually look at the query with the highest number for Average Duration. The Recent Expensive Queries monitors only show queries which are in the SQL Server cache at that moment.






Sql Features 2008
10. SQL Auditing: the ability to audit at the server, database and table levels. Need an Audit? SQL Server 2008 is your friend
9. Spatial Data Types: Non-relational data types for mapping and GPS style applications. Wimbledon Tennis and SQL? Think Spatial, Man! 
8. Change Tracking: the ability to track changes without timestamp columns and triggers. Need a Change? Try Katmai!
7. Intellisense in SQL Server Management Studio: interactive help as you type giving object names and syntax support similar to Visual Studio. Intellisense in SSMS at last
6. Enhanced Database Mirroring: including better reliability and performance with automatic data page repair. Database Mirroring 2008 style
5. SSMS Debug: the ability to debug any Transact-SQL within SSMS using breakpoints etc. SSMS 2008 Debug – very cool…
4. Multi-Server Queries: being able to submit a batch of queries against multiple servers simultaneously. Multi-Server Queries – a nice touch in SQL 2008
3. Policy Based Management: the ability to manage multiple servers from a central set of policies (previously named the Declarative Management Framework). Group Policy for SQL Server 2008 – the Declarative Management Framework (DMF)  
2. Filestream Data: being able to store binary large objects out on the file system while still being a transactional and recoverable part of the database. So you want to store movies in a SQL Server database? Go ahead, make my day.
And No. 1: (drum roll please!)
Transparent Database Encryption: The ability to encrypt an entire database without having to change any code in your application adding an additional layer to your data security. Shipper lost another backup tape? No worries in SQL Server 2008


Different Types of SQL Server Triggers
Posted By : Shailendra Chauhan, 05 May 2011
Updated On : 25 Sep 2012
  Version Support : SQL Server 2005,2008,2012
  Keywords : DDL Triggers, DML Triggers, CLR Triggers, LogOn Triggers, Types of Triggers pdf, Sql Server Triggers
Triggers are database object. Basically these are special type of stored procedure that are automatically fired/executed when a DDL or DML command statement related with the trigger is executed. Triggers are used to assess/evaluate data before or after data modification using DDL and DML statements. These are also used to preserve data integrity, to control server operations, to audit a server and to implement business logic or business rule.
Types of Triggers
In Sql Server we can create four types of triggers Data Definition Language (DDL) triggers, Data Manipulation Language (DML) triggers, CLR triggers and Logon triggers.
  1. DDL Triggers
    In SQL Server we can create triggers on DDL statements (like CREATE, ALTER, and DROP) and certain system defined stored procedures that perform DDL-like operations.
    Example : If you are going to execute the CREATE LOGIN statement or the sp_addlogin stored procedure to create login user, then both these can execute/fire a DDL trigger that you can create on CREATE_LOGIN event of Sql Server.
    We can use only FOR/AFTER clause in DDL triggers not INSTEAD OF clause means we can make only After Trigger on DDL statements.
    DDL trigger can be used to observe and control actions performed on the server, and to audit these operations. DDL triggers can be used to manage administrator tasks such as auditing and regulating database operations.
  2. DML Triggers
    In SQL Server we can create triggers on DML statements (like INSERT, UPDATE, and DELETE) and stored procedures that perform DML-like operations. DML Triggers are of two types
    1. After Trigger (using FOR/AFTER CLAUSE)
      This type of trigger fires after SQL Server finish the execution of the action successfully that fired it.
      Example : If you insert record/row in a table then the trigger related/associated with the insert event on this table will fire only after the row passes all the constraints, like as primary key constraint, and some rules. If the record/row insertion fails, SQL Server will not fire the After Trigger.
    2. Instead of Trigger (using INSTEAD OF CLAUSE)
      This type of trigger fires before SQL Server starts the execution of the action that fired it. This is differ from the AFTER trigger, which fires after the action that caused it to fire. We can have an INSTEAD OF insert/update/delete trigger on a table that successfully executed but does not include the actual insert/update/delete to the table.
      Example : If you insert record/row in a table then the trigger related/associated with the insert event on this table will fire before the row passes all the constraints, such as primary key constraint and some rules. If the record/row insertion fails, SQL Server will fire the Instead of Trigger.
  3. CLR Triggers
    CLR triggers are special type of triggers that based on the CLR (Common Language Runtime) in .net framework. CLR integration of triggers has been introduced with SQL Server 2008 and allows for triggers to be coded in one of .NET languages like C#, Visual Basic and F#.
    We coded the objects(like trigger) in the CLR that have heavy computations or need references to objects outside the SQL Server. We can write code for both DDL and DML triggers, using a supported CLR language like C#, Visual basic and F#. I will discuss CLR trigger later.
  4. Logon Triggers
    Logon triggers are special type of trigger that fire when LOGON event of Sql Server is raised. This event is raised when a user session is being established with Sql Server that is made after the authentication phase finishes, but before the user session is actually established. Hence, all messages that we define in the trigger such as error messages, will be redirected to the SQL Server error log. Logon triggers do not fire if authentication fails. We can use these triggers to audit and control server sessions, such as to track login activity or limit the number of sessions for a specific login.
    Synatx for Logon Trigger
      

    1. CREATE TRIGGER trigger_name
      2. 

    2. ON ALL SERVER
      3. 

    3. [WITH ENCRYPTION]
      4. 

    4. {FOR|AFTER} LOGON
      5. 

    5. AS
      6. 

    6. sql_statement [1...n ] 
      7.
Syntax for Trigger 
    

  1. CREATE TRIGGER trigger_name
    2. 

  2. ON {table|view} 
    3. 

  3. [WITH ENCRYPTION|EXECUTE AS] 
    4. 

  4. {FOR|AFTER|INSTEAD OF} {[CREATE|ALTER|DROP|INSERT|UPDATE|DELETE ]} 
    5. 

  5. [NOT FOR REPLICATION] 
    6. 

  6. AS 
    7. 

  7. sql_statement [1...n
    8.

No comments:

Post a Comment

Subscribe to: Posts (Atom)