Skip to content

Table Partitioning in SQL Server – The Basics

This post is part 1 of 2 in the series Table Partitioning in SQL Server
SQL Server Table Partitioning Cheat Sheet

There are many benefits of partitioning large tables. You can speed up loading and archiving of data, you can perform maintenance operations on individual partitions instead of the whole table, and you may be able to improve query performance. However, implementing table partitioning is not a trivial task and you need a good understanding of how it works to implement and use it correctly.

Being a business intelligence and data warehouse developer, not a DBA, it took me a while to understand table partitioning. I had to read a lot, get plenty of hands-on experience and make some mistakes along the way. (The illustration to the left is my Table Partitioning Cheat Sheet.) One of my favorite ways to learn something is to figure out how to explain it to others, so I recently did a webinar about table partitioning. I wanted to follow that up with focused blog posts that included answers to questions I received during the webinar. This post covers the basics of partitioned tables, partition columns, partition functions and partition schemes.

Continue reading →

Table Partitioning Webinar for Pragmatic Works

Pragmatic WorksPragmatic Works hosts and records free webinars every Tuesday and Thursday, called Free Training on the T’s. In March 2015 they wanted to highlight Women in Tech (WIT) by hosting nine webinars with speakers from the SQL Server community. I was very honored and excited to be invited, and a little nervous since it was my first ever webinar.

The recording of my webinar Table Partitioning in SQL Server: A Magic Solution for Better Performance? is now available, and the slide deck is available on my SlideShare:

The introduction was not included in the recording, but it can be summed up as “thank you to Pragmatic Works for supporting WIT, thank you for attending my webinar, my name is Cathrine and this is an introduction to table partitioning” :) There were some technical issues so the sound is missing for about a minute in the middle of the webinar, and I couldn’t see any questions in the chat. To all of you who asked questions, I’m sorry for not answering during the webinar, but I have now received the transcript and will answer your questions in upcoming blog posts.

Upcoming blog posts
Ed Leighton-Dick (@eleightondick) recently announced a #SQLNewBlogger challenge where those who participate will write one blog post every week in April. I have decided to participate in this challenge, and I encourage everyone else to do the same! :) I got a lot of great questions during my webinar that I want to answer in more detail with scripts, step-by-step instructions and pictures, so I have picked Table Partitioning as my topic for the challenge. You can find all the blog posts in the Table Partitioning in SQL Server Archive, and check back every week in April if you want to learn more about Table Partitioning basics! :)

SQL Server, SSIS and Biml Data Types

Biml (Business Intelligence Markup Language) - SQL Server, SSIS and Biml Data TypesThe table below is a simplified mapping between SQL Server, SSIS and Biml Data Types. It does not include all possible mappings or all data types, but is meant as a quick reference while developing and learning Biml. I mainly created it as a cheat sheet for myself, but I hope other Biml developers will find it useful!

Can’t remember this post’s URL? Neither can I. You can use this short URL instead: cathrinew.net/BimlDataTypes :)

Cheat Sheet for SQL Server, SSIS and Biml Data Types

SQL ServerSSISBiml
bigintDT_I8Int64
binaryDT_BYTESBinary
bitDT_BOOLBoolean
charDT_STRAnsiStringFixedLength
dateDT_DBDATEDate
datetimeDT_DBTIMESTAMPDateTime
datetime2DT_DBTIMESTAMP2DateTime2
datetimeoffsetDT_DBTIMESTAMPOFFSETDateTimeOffset
decimalDT_NUMERICDecimal
floatDT_R8Double
geographyDT_IMAGEObject
geometryDT_IMAGEObject
hierarchyidDT_BYTESObject
image (*)DT_IMAGEBinary
intDT_I4Int32
moneyDT_CYCurrency
ncharDT_WSTRStringFixedLength
ntext (*)DT_NTEXTString
numericDT_NUMERICDecimal
nvarcharDT_WSTRString
nvarchar(max)DT_NTEXTString
realDT_R4Single
rowversionDT_BYTESBinary
smalldatetimeDT_DBTIMESTAMPDateTime
smallintDT_I2Int16
smallmoneyDT_CYCurrency
sql_variantDT_WSTRObject
text (*)DT_TEXTAnsiString
timeDT_DBTIME2Time
timestamp (*)DT_BYTESBinary
tinyintDT_UI1Byte
uniqueidentifierDT_GUIDGuid
varbinaryDT_BYTESBinary
varbinary(max)DT_IMAGEBinary
varcharDT_STRAnsiString
varchar(max)DT_TEXTAnsiString
xmlDT_NTEXTXml

(* These data types will be removed in a future version of SQL Server. Avoid using these data types in new projects, and try to change them in current projects.)

Continue reading →

Business Intelligence Fagdag & SQL Server 2012 Lansering

Quality Hotel 33 huset i dag over 300 personer med minst én felles interesse: Business Intelligence. Årets fagdag ble kombinert med norsk lansering av SQL Server 2012, med presentasjoner av blant annet BI-guru Rafal Lukawiecki, Microsoft og Platon.

Microsoft, Platon, Evry, Avanade, Affecto, Inmeta Crayon, Itera Consulting, Nextbridge, Glasspaper og ikke minst Capgemini var tilstede med stands, og ansatte fra store og små bedrifter hadde møtt opp for faglig påfyll.

Dagen startet med en kikk på eksisterende og kommende trender innen Business Intelligence: Big Data, Cloud, Mobile BI og Self-Service. Petter Stordalen fortalte om BI-visjonen i Choice Hotels og hvordan Microsoft-teknologier brukes for å monitorere sosiale medier og fintune strategier fra dag til dag. Videre fortsatte sesjonene i to spor, overordnet Business Intelligence og et teknisk dypdykk i De Fantastiske 12 i SQL Server 2012.

SQL Server 2012 Fantastic 12

De Fantastiske 12 omhandlet de tre hovedtemaene Mission Critical Confidence, Breakthrough Insights og Cloud on Your Terms.

Brukere stiller stadig større krav til bedre tilgjengelighet, sikkerhet og ytelse. AlwaysOn er et enormt skritt i riktig retning, med enklere failover, clustering og sikkerhet. ColumnStore Indexes kan forbedre ytelsen dramatisk og gjøre spørringer lynraske. (Aggregering av 12 millioner rader på under 2 sekunder er imponerende!) PowerPivot og Power View gjør data enkelt tilgjengelig for analyser.

I SQL Server 2012 er Master Data Services og Data Quality Services integrert, og det har aldri før vært så enkelt å vedlikeholde og kvalitetssikre data. Data administreres av sluttbrukere gjennom kjente grensesnitt som Excel og webapplikasjoner, og ETL-utviklere kan enkelt sjekke og laste data gjennom nye komponenter i SQL Server Integration Services (SSIS).

Hybrid IT tok for seg sømløs integrering og synkronisering mellom løsninger og data lokalt, i private skyer og i den offentlige skyen.

Til slutt ble SQL Server Data Tools presentert som et integrert verktøy for DBAer og utviklere. Det har aldri før vært så enkelt å administrere databaseprosjekter! Kodekontroll, kodekvalitet, utrulling, refaktorering, oppgradering, sporbarhet og versjonering er nå integrert i ett verktøy – som på toppen av det hele er gratis.

I tillegg til faglig påfyll ble det servert en nydelig lunsj og middag – og for mange var dagens to minste høydepunkter self-served softis og popkorn ;)