A database in suspect state restricts access to data or settings. Data in suspect databases can not be pulled out nor you can perform recovery operations. In such cases, no matter the problem is due to any corrupt data page or log file, recovery is a night mare. SQL Server 2005 has introduced a very useful state for working with suspect databases. You can set the suspect database to emergency state and then exploit the wide range of operations on data and settings.
Microsoft has launched Community Technology Preview (CTP1) of SQL Server 2011 code-named “Denali”. On download page i have noticed few of its new features, like
- “Always On” support by using multi-site clustering and the new availability group option
- Column-Based Query Accelerator for increased performance
- New server level roles
- Distributed replay for simplified testing
- Increased capabilities for FileStream and Full-Text Search, 2D spatial support and FileTable
- Unified environment for SQL Server development, Declarative Database Definition, Dynamically Edit Existing Databases, Round-Trip Code Refactoring, and Targeted Database Deployment
- In-memory column store technology for increased analytic performance
- Data Quality Services for knowledge-driven data cleansing and Impact Analysis and Lineage for Enterprise data integration management
Click here to visit download page where you can also find additional resources, BOL and installation requirements for Denali.
Question: Name some date time new data types added in SQL 2008 :
Explanation: References for these types:
Note: The Question is taken from SQLServercentral.com.
My article related to using synonyms in SQL Server 2005 is published on following address. Due to copy right restrictions i will not publish it here. You may visit it at using synonyms in SQL Server 2005
SQL Server 2008 meets or exceeds Oracle 11g in every area that matters to the business. The security and reliability is unrivaled, the integration is unparalleled, and the TCO is unmatched. SQL Server 2008 provides the best solution in terms of both performance and value for money at every level from PDAs to data centers. In coming lines we will compare some critical features in both.
SQL Server 2008 has many new features that Oracle 11g does not have as shown in the following table. A brief description of these features follows the table.
- Resource Governor provides consistent and predictable response times to end users. Organizations can allocate resources and define priorities for different workloads so that concurrent workloads do not interrupt consistent performance to end users. Resource Governor provides SQL Server with several important advantages over Oracle. By specifying minimum CPU and memory usage, you can prioritize workloads in order to guarantee that SLAs (service level agreements) are met for particular workloads in the database. Resource Governor also enables you to limit the amount of memory per resource pool, thereby preventing runaway queries.
- Partition-aligned indexed views enable you to create and manage summary aggregates in your relational data warehouse more efficiently and use them in scenarios where you previously could not use them effectively. Partition-aligned views improve query performance. In a typical scenario, a fact table is partitioned by date. Indexed views (or summary aggregates) can be defined on the fact table to help speed up queries. When you switch in a new table partition, the matching partitions of the partition-aligned indexed views defined on the partitioned table switch, too, and do so automatically.
- SQL Server PowerShell is a new provider for browsing and managing SQL Server databases, tables, and other database objects. The Windows PowerShell™ command-line interface supports more complex logic than Transact-SQL scripts to allow for more robust administration scripts. You can also use PowerShell scripts to administer other Microsoft server products so that administrators use a common scripting language across servers.
- Policy-Based Management is a new system for managing one or more instances of SQL Server 2008 by using SQL Server Management Studio. Use it to create policies to manage entities such as instances of SQL Server, databases, and other SQL Server objects on the database server. It gives database administrators (DBAs) full control of their database servers from an entirely new perspective. It is an easy-to-use and powerful tool for the DBA to use to implement standard configurations in the SQL Server environment.
- Filtered indexes enable indexing on a subset of rows in a table and provide numerous benefits. They provide space-saving and performance improvements when you insert or update content. Filtered indexes can greatly improve data-warehousing performance. For example, you can index only the data for the current month rather than the data for an entire year. You can create more filtered indexes per table to speed up queries. The SQL Server Database Engine Tuning Advisor can recommend filtered indexes for database tables. Filtered indexes provide support for heterogeneous table data in applications such as content management systems (Microsoft Office SharePoint® Server, for example) that have multiple properties for each data type such as a retail product catalog that has entries for books, CDs, and clothes in the same table, but the properties of these items differ. You can create filtered indexes for data according to properties or categories.Sparse columns efficiently manage empty data in a database because they enable NULL data to consume no physical space. SQL Server 2008 sparse columns can support wide tables that have up to 100,000 columns, whereas Oracle’s current limit is 1,000. Column sets support property-bag scenarios in content management systems such as Office SharePoint Server.
- Multithreaded partition access enables SQL Server 2008 to improve query-processing performance on partitioned tables for many parallel plans. Furthermore, multithreaded partition access changes the way in which parallel and serial plans are represented, and enhances the partitioning information that is provided in both compile-time and run-time execution plans.
- Column-prefix compression is part of the SQL Server 2008 advanced page compression techniques (dictionary-page compression and column-prefix compression). With column-prefix compression, SQL Server looks for a common byte pattern at the beginning of a column across all rows on the page. If it finds at least two instances of columns that have a common byte pattern, it stores that byte pattern once on the page and refers to this byte pattern from the respective columns
- Module signing using certificates gives SQL Server the ability to sign modules such as stored procedures, functions, triggers, or assemblies, within a databases. This means that you can temporarily elevate privileges without switching the user context. In addition, it is not possible to tamper with or modify the certificate (otherwise it is invalidated)
- SQL Server Data Services is a highly scalable, cost-effective, on-demand data storage and query-processing Web service. It is built on robust SQL Server technologies and helps guarantee a business-ready service level agreement that covers high-availability, performance, and security features. SQL Server Data Services is accessible by using standards-based protocols such as SOAP and REST for quick provisioning of on-demand data-driven and mashup applications. Businesses can store and access all types of data from origination to archival by using SQL Server Data Services. Users can access information on any device, from desktop computers to mobile devices.
It is relevant to mention here the great improvement in BI. Using same capabilities in Oracle will cost about 800 more.
SQL Server 2008 includes a fully integrated BI solution at no extra cost. The product includes support for enterprise-level data warehousing, online analytical processing (OLAP), reporting, scorecards, data mining, ETL, and key performance indicators (KPIs).
Unlike Oracle, these solutions are fully integrated so you can develop, manage, schedule, and deploy them by using the familiar SQL Server tools.
SQL Server 2008 has an extensive array of new data warehousing features including data and backup compression, partitioned table parallelism, star join query optimization, resource management, grouping sets, change data capture, the MERGE SQL statement, and scalable Integration Services.
Values for Teradata Customers
Teradata is the Microsoft ISV for Business Intelligence. By integrating the Microsoft BI solution and Teradata technologies, you can take advantage of the data warehousing and BI solutions from Teradata along with the enterprise-ready BI and performance-management solutions from Microsoft. By extending access to critical data to the decision makers who can affect business performance most, the combination of Microsoft and Teradata technologies helps organizations gain additional value from their Teradata data warehousing environment. The Microsoft/Teradata partnership ensures easier implementation of Microsoft BI solutions, faster availability, and easier adoption of new Microsoft and Teradata feature releases.
Integration with the Microsoft Office System
The Microsoft Office System is effectively the standard for desktop productivity. The integration of SQL Server data with the Office System brings the power of information to knowledge workers, reduces development time, reduces training and support costs, and increases productivity. Achieving the integration that is available at no extra cost with SQL Server 2008, would cost $30,000 per processor with Oracle.
Data-Mining Add-ins for Microsoft Office 2007
Data mining add-ins for the 2007 Office System are freely downloadable and improve the integration between the Office System and SQL Server 2008. They provide data mining, a prediction calculator, shopping-basket analysis, cross-validation, and reporting tools.
Total Cost of Ownership
Not only does SQL Server have a lower license cost when compared to Oracle 11g, but its better security, reliability, and productivity gives a greater return on investment. Furthermore, SQL Server 2008 includes features that in the Oracle product would cost much more per processor in addition to the cost of the Oracle base license.
The license price as not the only criterion to consider when you compare the costs of your system. The TCO is the price that you will pay and sometimes the least expensive licenses have the highest TCO. Oracle has a higher license cost than SQL Server with many other hidden costs, whereas SQL Server includes the tools that you require at no extra cost.
SQL Server includes the functionality that you require for an enterprise database solution; Oracle charges extra for this functionality. The following table shows the software price comparison between SQL Server 2008 and Oracle 11g for a standard, single processor, quad-core server. With SQL Server, the cost remains the same regardless of how many cores your processors have.
As with SQL Server 2008, there are many new features that have major impact regarding to optimization and control. Major updates are
- plan freezing
- changes in MERGE statement
- star join performance
- parallel scan of partitioned tables
- External Key Management
- Enhanced Auditing
- Enhanced Database Mirroring
- Hot Add CPU
- Performance data collection
- Extended Events
- backup compression