Blog Posts

Types of Indexes (or Indices)

1/31/2017

Types of indexes (Types of Indices) see Video below!

DBAs are constantly facing challenges with making queries run faster. One of the top tuning procedures is to add an index to the table or tables in order to increase the optimizer's accessibility to the requested data.
*Note - The first three definitions are excerpts from lyrics in my index song. The remaining definitions come from the referenced link below. Again, my intentions are to inspire listeners to learn through my songs. For example - The different types of indexes in SQL Server 2016 are now ingrained in my memory!

Unique – “A unique index will contain – no duplicates – no row is the same – according to the key that is… Every row in the table or view – it’s gotta be new!
Uniqueness can be a property of clustered and nonclustered indices.”
Clustered – “..and speaking of Clustered – it is implemented as a B-tree index structure. There can only be one per table – sorted in order… and I don’t mean sort of. So without it all you have is a heap – with data pages that aren’t even linked…
Non-Clustered – Not we got a locator (woo!) – pointing to the data row’s key value. It can be in any order – have as many as you wanna - On a table or a view - or a clustered index too.”
Hash - With a hash index, data is accessed through an in-memory hash table. Hash indexes consume a fixed amount of memory, which is a function of the bucket count.
Filtered - An optimized nonclustered index, especially suited to cover queries that select from a well-defined subset of data. It uses a filter predicate to index a portion of rows in the table. A well-designed filtered index can improve query performance, reduce index maintenance costs, and reduce index storage costs compared with full-table indexes.
Memory-optimized non-clustered indexes - For memory-optimized nonclustered indexes, memory consumption is a function of the row count and the size of the index key columns
Included columns - A nonclustered index that is extended to include nonkey columns in addition to the key columns.
Computed columns - An index on a column that is derived from the value of one or more other columns, or certain deterministic inputs.
Spatial - A spatial index provides the ability to perform certain operations more efficiently on spatial objects (spatial data) in a column of the geometry data type. The spatial index reduces the number of objects on which relatively costly spatial operations need to be applied.
XML - A shredded, and persisted, representation of the XML binary large objects (BLOBs) in the xml data type column.
Full-text - A special type of token-based functional index that is built and maintained by the Microsoft Full-Text Engine for SQL Server. It provides efficient support for sophisticated word searches in character string data.
Columnstore - An in-memory columnstore index stores and manages data by using column-based data storage and column-based query processing.
Columnstore indexes work well for data warehousing workloads that primarily perform bulk loads and read-only queries. Use the columnstore index to achieve up to 10x query performance gains over traditional row-oriented storage, and up to 7x data compression over the uncompressed data size.

ONLINE REFERENCE: https://msdn.microsoft.com/en-us/library/ms175049.aspx

1 Comment

Data Protection

12/16/2016

0 Comments

Security in the world of data has become very important. Computer viruses, Identity theft, hacked websites and more have put “data security” at the forefront of this new era of "having lots of data and storage." This is the first time in known human history that we have been able to store as much data as we have! Even at this very moment that you are reading this, data storage technology is improving at radical speeds. Not only are we able to store more data than we could yesterday, but we are now able to retain, save and archive this data with better safety precautions. Increased Disaster recovery plans over time have given us the abilities to have more insight so that data will tell us what we have done wrong in the past as well as how we can improve our future. Companies are now realizing that data is what acquires new customers, retains customers, generates money, etc… Bottom line - data doesn’t lie! I can go on and on about the importance of protecting data. Here are a few security items mentioned in my new song:

Physical protection – Where does your data live? Is the server in a secure location? Who has access to it?
Disaster Recovery – Can your infrastructure handle a disaster? Having a cloned server at a different site that can assist with keeping the database going just in case.
Firewall – Network security that can monitor traffic in and out of servers.
Update fixes – It is important to install updates to address bugs and security issued from time to time (my song says always… but … it depends)
Disable unused data protocol – for example… if you are using TCP/IP – disable shared memory and named pipes.
Monitoring – alerts against the database can help out tremendously. Long queries, failed jobs, storage issues, etc…
Transparent Data Encryption – Encrypts data at rest: Data files, Logs and backups.
Xp_cmdshell – Allows admionitrators to use command shell through SSMS or t-sql
Disable SA – This is a default account. Knowing that there is a login called SA gives a hacker 50% closer to his goal!
Passwords – change your SQL login or Windows authenticated password from time to time.
SQL Browser – This service can really come in handy where there are multiple instances of SQL Server running on a server. It should be disabled on single instance servers.
SQL Injection – Within application code, nasty sql statements can be scripted within the entry fields to do unimaginable things within a database. In my song, I make a funny about the developer handling this issue, but in fact, having the right database permissions can also help to eliminate SQL injection.
Always encrypted – Encrypts sensitive data and only those with key can see data.
TLS – or Transport Layer Security is a protocol that provides security over a network between two applications.
Row Level Security – This feature allows the admin to restrict a user or role’s access to the row level only.
DDM – or Dynamic Data Masking limits the amount of sensitive data seen by user. For example: having the last 4 digits of a social security number or bank account card shown as opposed to having the complete number exposed.
Data – My last statement or lyric in this song is a theory that puts in perspective the importance of data. It may come across as crude, but I mention that the data is more important than a company’s hardware, real estate and/or even people

A FEW REFERENCE Sites:
https://www.mssqltips.com/sqlservertip/3159/sql-server-security-checklist/
http://searchsqlserver.techtarget.com/tip/Microsoft-SQL-Server-security-best-practices-checklist/

0 Comments

Wow! Positive responses!

12/15/2016

0 Comments

Wow! I really do appreciate all of the positive responses I have received regarding my SQL Server 2016 song! This only confirms my belief that I should continue with my journey. One of my goals is to complete enough songs so that I can create an album. My hope - is that these songs help others as much as they have helped me with my studies and retaining knowledge about SQL Server.

If you have any suggestions, comments or criticisms ... please don't hesitate to let me know!

Thanks again everyone! I'll keep it going... next song... "Data Protection"

Peace,

KillaDBA

0 Comments

Hello World!

11/27/2016

0 Comments

As a DBA I always have to read up on new features with SQL Server. Even though I read about and mostly use these new features, I rarely remember which feature is new to a particular SQL Server version. At times it can be difficult for me to retain information that I have not experienced firsthand. My first song is about SQL Server 2016 features. I realized that if I created a song about the new features, it would help me to speed up my aspirations to become a subject matter expert and also to remember these new features as time goes on! I cover most of the popular new features in my lyrics. I created the music, lyrics and also the melody (which is my personal favorite)

So... without further delay... I present to you "SQL Server 2016"

0 Comments

Forward>>

KillaDBA

Types of Indexes (or Indices)

Data Protection

Wow! Positive responses!

Hello World!

KillaDBA

Archives

Categories