European Windows 2012 Hosting BLOG

BLOG about Windows 2012 Hosting and SQL 2012 Hosting - Dedicated to European Windows Hosting Customer

SQL Server Hosting - HostForLIFE.eu :: SQL Server In Memory Table Indexes

clock January 29, 2020 10:49 by author Peter
Now that I have written about In-Memory Tables and Migrating to In-Memory tables, let’s look at indexes and how they are created and how they work within those tables. As you can imagine indexes, called memory optimized indexes are different for these types of tables, so let’s see just how different that are from regular tables. Before we dive into this subject it is VERY important to note the biggest differences.

First, ALL memory optimized indexes MUST be created when the table is created or migrated. You cannot add indexes in an existing table without dropping and recreating the table. Secondly, currently you can only have 8 indexes per table including your primary key. Remember that every table must have a primary key to enforce a secondary copy for a minimum of schema durability This means you can only really add 7 additional indexes so be sure to understand your workloads and plan indexing accordingly.

Third, Memory Optimized Indexes only exists in memory they are not persisted to disk and are not logged in the transaction logs. Therefore, this means they are also recreated upon database startup and do incur a performance hit as they are rebuilt.

Next, there is no such thing as key lookups against an In-Memory table, as all indexes are by nature a covering index. The index uses a pointer to the actual rows to get the needed fields instead of using a primary key like physical tables do. Therefore, these are much more efficient in returning the proper data.

Lastly, there also is no such thing as fragmentation for these indexes, since these are not read from disk. Unlike on disk indexes, these do not have a fixed page length. On disk index use physical page structures within the B-Tree, determining how much of the page should be filled is what the Fill Factor does. Since this is not a requirement fragmentation does not exist.

Ok now that we made it through all of that, let’s look at the types of indexes you can create and gain an understanding of what they are and how they are created.

Nonclustered HASH Index
This index is used to access the In-Memory version of the table, called a Hash. These are great for predicates that are singleton lookups and not ranges of values. These are optimized for seeks of equality values. For example, WHERE Name = ‘Joe’. Something to keep in mind when determining what to include in your indexes is this; if your query has two or more fields as your predicate and your index only consists of one of those fields, you will get a scan. It will not seek on that one field that was included.

Understanding your workloads and indexing on the appropriate fields (or a combinations thereof) is important since you are limited to only 7 additional indexes. Given that this In-Memory OLTP is mainly focused on heavy insert/update workloads, and less so reading, this should be less of a concern.

These types of indexes are highly optimized and do not work very well if there are a lot of duplicate values in an index, the more unique your values better the index performance gains you will get. It is always important to know your data. When it comes to these indexes knowing your memory consumption plays a part. The hash index type is a fixed length and consume a fixed amount of memory determined upon creation. The amount of memory is determined by the Bucket Count value. It is extremely important to make sure this value is as accurate as possible. Right sizing this number can make or break your performance, too low of a number according to Microsoft “can significantly impact workload performance and recovery time of a database.

Look for my upcoming blog on determining bucket counts for more information. Meanwhile you can learn more about hash indexes at docs.microsoft.

Using T-SQL (both methods give the same result)
Example One (Note the index comes after the table fields)

CREATE TABLE [Sales]   
([ProductKey] INT NOT NULL,  
[OrderDateKey] [intNOT NULL,  
INDEX IDX_ProductKey HASH ([ProductKey]) WITH (BUCKET_COUNT = 100))  
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY)  

Example Two (Note the index comes after the field)
CREATE TABLE [Sales]  
([ProductKey] INT NOT NULL INDEX IDX_ProductKey HASH WITH (BUCKET_COUNT = 100),  
[OrderDateKey] [intNOT NULL)  
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY)  

Nonclustered Index
These are also used to access the In-Memory version of the table however, these are optimized for range values such as less than and equal to, inequality predicates and sorts orders. Examples are WHERE DATE between ‘20190101’ and ‘20191231’ and WHERE DATE <> ‘20191231’. These indexes do not require a bucket count or fixed memory amount. The memory consumed by these indexes are determined by the actual row counts and size of the indexed key columns which makes it a simpler to create.

Moreover, in contrast to hash indexes which needs all fields required for your predicate to be part of your index to get a seek, these do not. If your predicates have more than one field and your index has that one of those as its leading index key value, then you can still attain a seek.

Using T-SQL (both methods give the same result)
Example One (Note the index comes after the table fields)

CREATE TABLE [Sales]   
([ProductKey] INT NOT NULL,  
[OrderDateKey] [intNOT NULL,  
INDEX IDX_ProductKey ([ProductKey]))  
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY)

Example Two (Note the index comes after the field)

CREATE TABLE [Sales]  
([ProductKey] INT NOT NULL INDEX IDX_ProductKey,  
[OrderDateKey] [intNOT NULL)  
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY) 

Determining which index type to use can be tricky but Microsoft has given us a great guide in the below chart. As you can see there some key differences to how In- Memory table indexes, memory optimized indexes, work compared to the normal disk indexes we are used to. Like with any other table design it is important to consider your index needs before you embark on creating or migrating to memory optimized tables. You’ll be happy you did.



SQL Server Hosting - HostForLIFE.eu :: SQL Comments Statement

clock January 20, 2020 11:15 by author Peter

SQL Comments statement can make your application easier for you to read and maintain. For example, we can include a comment in a statement that describes the purpose of the statement within your application with the exception of hints, comments within SQL. The statement does not affect the statement execution. Please refer to using hints on using this particular form of comment statement. 
 
A comment can appear between any keywords, parameters, or punctuation marks in a statement. You can include a comment in a statement in two ways:

  • Begin the comment with a slash and an asterisk (/*). Proceed with the text of the comment. This text can span multiple lines.
  • End the comment with an asterisk and a slash (*/). The opening and terminating characters need not be separated from the text by a space or a line break.
  • Begin the comment with -- (two hyphens). Proceed with the text of the comment. This text cannot extend to a new line. End the comment with a line break.

Some of the tools used to enter SQL have additional restrictions. For example, if you are using SQL*plus, by default you cannot have a blank line inside a multiline comment.
 
For more information, please refer to the documentation for the tool you use as an interface to the database. A SQL statement can contain multiple comments of both styles. The text of a comment can contain any printable characters in your database character set.
 
The comment statement indicates the user-provided text. Comments can be inserted on a separate line, nested at the end of a SQL command line, or within a SQL statement. The server does not evaluate the comment. 
SQL Comment uses the two hyphens (--) for single-line or nested comments. Comments inserted with -- are terminated by a new line, which is specified with a carriage return. Character (U+000A), line feed character (U+000D), or a combination of the two in SQL comments.
 
There is no maximum length for comments. The following table lists the keyboard shortcuts that you can use to comment or uncomment text.
 
Syntax
    -- text_of_comment    

Examples
The following example uses the -- commenting characters.
 
Syntax
    -- Choose the sample database.     
    USE sample;     
    GO     
    -- Choose all columns and all rows from the Address table.     
    SELECT *     
    FROM OrderDetails     
    ORDER BY OrderId  ASC; -- We do not have to specify ASC because      
    -- that is the default.     


SQL Single Line Comments
Single line comments start with --. Any text between -- and the end of the line will be ignored (will not be executed). The following example uses a single-line comment as an explanation.
 
Syntax 
   --Select all:   
    SELECT * FROM OrderDetails ; 


The following example uses a single-line comment to ignore the end of a line.
 
Syntax
    SELECT * FROM OrderDetails -- WHERE OrderName='Coffee';  

The following example uses a single-line comment to ignore a statement.
 
Syntax
    --SELECT * FROM OrderDetails;   
    SELECT * FROM OrderDetails ;  


SQL Multi-line Comments
SQL Multi-line comments start with /* and end with */. Any text between /* and */ will be ignored. The following example uses a multi-line comment as an explanation.
 
Syntax 
    /*Select all the columns   
    of all the records   
    in the OrderDetails table:*/   
    SELECT * FROM OrderDetails;  


 The following example uses a multi-line comment to ignore many statements.
 
Syntax
    /*SELECT * FROM Customers;   
    SELECT * FROM Products;   
    SELECT * FROM Orders;   
    SELECT * FROM Categories;*/   
    SELECT * FROM OrderDetails;  


To ignore just a part of a statement, also use the /* */ comment. The following example uses a comment to ignore part of a line.
 
Syntax
    SELECT CustomerName, /*City,*/ Country FROM Customers;  

The following example uses a comment to ignore part of a statement
 
Syntax
    SELECT * FROM OrderDetails WHERE (OrderName LIKE 'L%'   
    OR OrderName LIKE 'R%' /*OR OrderName  LIKE 'S%'   
    OR OrderName LIKE 'T%'*/ OR OrderName LIKE 'W%')   
    AND OrderName ='Mango'   
    ORDER BY OrderrAddress;  


Summary
In this article, you learned how to use a SQL Comments statement with various options.

 



About HostForLIFE.eu

HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

We have offered the latest Windows 2016 Hosting, ASP.NET Core 2.2.1 Hosting, ASP.NET MVC 6 Hosting and SQL 2017 Hosting.


Tag cloud

Sign in