xVelocity Columnstore Indexes in SQL Server 2012

With the release of SQL Server 2012, Microsoft introduced a new type of nonclustered index called xVelocity columnstore index in the SQL Server database engine. It is based on VertiPaq in-memory data compression technology. The xVelocity memory optimized columnstore indexes are one of the most significant performance and scalability enhancements in SQL Server 2012. The xVelocity columnstore indexes speed up the processing time for common data warehousing queries — particularly in situations where very large quantities of data have to be aggregated and accessed quickly. According to Microsoft, under certain conditions, the xVelocity columnstore index provides queries with speed improvements of 4X, 10X or even 100X.

Checkout my article here, in which I discussed how these indexes work and how the data is stored in a columnstore index.

This article is published on SearchSQLServer.techtarget.com.

Advertisements

Database index design and optimization: Some guidelines

Well-designed indexes are an important part of database optimization because they are the key factor in achieving optimal query performance and response time. In the meantime, poorly designed indexes, such as missing or excessive indexes on tables, indexes placed on the wrong column or inadequate index maintenance strategies, can be a source of impaired query and database performance.

Checkout my article here, in which I shared I some guidelines, which you can follow to make indexes more effective, and improve performance during the creation, implementation and maintenance of indexes.

This article is published on SearchSQLServer.techtarget.com.

Six top SQL Server 2012 management tools you should know about

Management tools play a vital role in enterprise database management. This is because the well-integrated tools extend the administrator’s capabilities, whereas a random collection of tools can lead to confusion, operational mistakes, high training costs and poor database administrator (DBA) productivity.

To keep up with these new demands of complex enterprise database management solutions, Microsoft ships SQL Server software with a broad group of several graphical management tools that help DBAs and developers efficiently create, manage and maintain SQL Server solutions, and allow them to quickly resolve complex performance and configuration problems.

Checkout my article here, in which I gave an overview of the key features of these graphical SQL Server 2012 management tools.

This article is published on SearchSQLServer.techtarget.com.

The pros and cons of virtualizing SQL Server environments

Virtualization is a hot trend in the computing world, offering businesses substantial cost and performance benefits that include server consolidation, reducing power consumption and the effortless creation of virtual development and test environments. As a result of this trend, many organizations are interested in virtualizing SQL Server, and for a good reason. These benefits of virtualized environments increase business continuity and allow database administrators (DBAs) and IT professionals to build an agile IT infrastructure. That is why just about everyone in IT is using virtualization to some extent, although many DBAs are still not sure about whether they should virtualize their SQL Server infrastructure or what benefits they would be able to achieve by doing this.

Checkout my article here, in which I discussed the pros and cons of virtualizing SQL Server environments.

This article is published on SearchSQLServer.techtarget.com.

SQL Server 2012 security: Changes for the newest version

Database infrastructure security is extremely crucial for any organization, which is why Microsoft has invested heavily in SQL Server security features in recent years. Microsoft redesigned the SQL Server database security engine from scratch for SQL Server 2005, and since then, SQL Server security has improved with each update.

Microsoft SQL Server 2012 continues this trend with an extensive collection of new security features and enhancements. These enhancements not only help organizations to improve access controls to data, but also to achieve the highest level of data protection and compliance. Also, these features help make SQL Server arguably the most robust common database platform from a security perspective, with less vulnerability and fewer security patches needed to maintain the system.

Checkout my article here that list all security enhancements which DBAs expect in SQL Server 2012.

This article is published on SearchSQLServer.techtarget.com.

Why SQL Server security matters: A case study

Database technologies are an essential component of many information systems because they store a large amount of sensitive corporate data such as customer information and other confidential business data. Because of this, it is not wrong to say that databases such as SQL Server are the most valuable asset for any organization, and that is why they must be secured from internal and external attacks.

Checkout my article here, in which I presented a sample case study of a company who is facing security issues such as database backups are stolen, viruses attack servers, the database has unauthorized changes because there SQL Servers is lax.

This article is published on SearchSQLServer.techtarget.com.

Transact-SQL analytic functions in SQL Server can help solve problems quickly

Like other mainstream commercial database systems, SQL Server supports analytic functions in Transact-SQL to depict complex analytical tasks. With the help of these analytic functions, we can perform common analyses, such as ranking, percentiles, moving averages and cumulative sums that can be expressed concisely in a single SQL statement.

The first batch of Transact-SQL analytic functions came with the release of SQL Server 2005, which included a variety of ranking functions, such as ROW_NUMBER, RANK, DENSE_RANK and NTILE. SQL Server 2012 introduced eight more Transact-SQL analytic functions: PERCENT_RANK, CUME_DIST, PERCENTILE_CONT, PERCENTILE_DISC, LEAD, LAG, FIRST_VALUE and LAST_VALUE.

Checkout my article here, in which I explained and demonstrated the use of Transact-SQL analytic functions. With the help of these analytic functions, we can quickly solve complex analytical tasks and also eliminate the use of programming self-joins, correlated subqueries and/or use fewer temporary tables compared to the counterparts without such functions.

This article is published on SearchSQLServer.techtarget.com.