Tag Archives: indexes

Book: SQL Server 2014 Development Essentials

I am very happy to announce that my first book “SQL Server 2014 Development Essentials” has now been published by Packt Publishing.

SQL Server 2014 Development Essentials, ISBN: 9781782172550

SQL Server 2014 Development Essentials, ISBN: 9781782172550

SQL Server 2014 Development Essentials is an easy-to-follow yet comprehensive guide that is full of hands-on examples, which you can follow to successfully design, build, and deploy mission-critical database applications with SQL Server 2014. You can view and download the table of contents, sample chapter, code files and the preface of this title on Packt Publishing website here.

Who this book is for?

If you are a database developer, architect, or administrator who wants to learn how to design, implement, and deliver a successful database solution with SQL Server 2014, then this book is for you. This book will provide you with all the skills you need to successfully create, design, and deploy databases using SQL Server 2014. You will also learn how to add, modify, and delete data stored within a database. You will use Transact-SQL statements to create and manage advanced database objects that include scalar and table-valued functions, views, stored procedures, and triggers. Finally, you will learn about how SQL Server works, how indexes and statistics improve query performance, and the new SQL Server 2014 in-memory technologies.

From this book, you will learn:

  • Get introduced to SQL Server 2014’s new in-memory database engine
  • Understand SQL Server database architecture and relational database design
  • Use joins, subqueries, CTEs, and windowing functions to write advanced Transact-SQL queries
  • Learn about tools that let you monitor SQL Server database performance
  • Identify and troubleshoot blocks or deadlocks that might slow down a system
  • Design, create, and manage advanced database objects that include scalar and table-valued functions, views, stored procedures, and triggers
  • Use SQL Server 2014’s structured error handling blocks to handle errors that occur in the Transact-SQL batches and programmable objects

You can order it at Packt Publishing, Amazon UK, Amazon.com and Barnes & Noble. It will also be available at O’Reilly Media and Safari Books Online.

Please feel free to contact me via twitter (@BasitAali) or via email, if you have any questions or feedback about this book.

Advertisement

Understanding SQL Server Query Optimization – Part 3

A critical part of database design and management is index design. Index design involves balancing space requirements and the resource overhead required to keep indexes up-to-date compared to the performance improvements resulting from the indexes. You can either use dynamic management functions (DMFs) and views (DMVs) or Database Engine Tuning Advisor to identify index needs based representative server activity.

Click here to read full article on SQL-Server-Performance.com…

Guide to SQL Server Table Indexes – (Part 4)

Checkout the part-4 of my five part article series on Guide to SQL Server Table Indexes here, in which you will learn about index design, and the suitable candidates for index key and non-key columns. I this article, you will also learn about various SQL Server indexes options, such as pad-index and fill-factor.

This article is published on SQL-SERVER-PERFORMANCE.COM.

Guide to SQL Server Table Indexes (Part 3)

Checkout the part-3 of my five part article series on Guide to SQL Server Table Indexes here, in which you will learn about remaining index types available in SQL Server, and what are these indexes advantages and dis-advantages.

This article is published on SQL-SERVER-PERFORMANCE.COM.

Guide to SQL Server Table Indexes (Part 1)

One of the most important tasks for every database administrator (DBA) is to ensure that query times are consistent with service-level agreement (SLAs) or within user expectations. One of the most effective techniques to achieve this objective is to create indexes on tables for your queries. SQL Server uses indexes to sort and organize table data. It creates indexes based on ordering values from one or more selected columns. SQL Server automatically creates indexes when you define a primary key or unique constraint. You can use indexes to manage and track rows in a table or an indexed view.

Indexes improve the performance on most data scans by reducing the overall time query takes to run and the amount of work and resources it consumes. The amount of performance improvement depends on the size of the tables involved, the index design, and the type of query. You can see the role of indexes by observing what happens during queries and data manipulation.

Checkout the part-1 of my five part article series on Guide to SQL Server Table Indexes here, in which you’ll learn about the basics of SQL Server database table’s indexes, the difference between the clustered and non-clustered indexes, and how the leaf nodes, non-leaf nodes, and heaps associated with data storage.

This article is published on SQL-SERVER-PERFORMANCE.COM.

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.

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.

XML Support and SQL Server (Part 6)

Checkout the sixth part of my XML support and SQL Server article series here.

In this part, you will learn about XML indexes (primary and secondary), and how they can effectively improve xQuery performance. You will also learn how you can manage the XML indexes using SQL Server Management Studio.

This article is published on SSWUG.org.