THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Dejan Sarka

  • T-SQL Querying

    We are close to the publishing day of the T-SQL Querying book. Of course, like always in this series, the main author of the book is Itzik Ben-Gan. This time, besides me, Adam Machanic and Kevin Farlee are the coauthors. The information I want to share now is that you can get a substantial discount if you preorder the book today, Monday, February 16th, 2015. Pearson is running the Presidents Day Event and giving the following discounts for this and some other products:

    • Buy 1, Save 35%
    • Buy 2, Save 50%
    • Up to 70% off on featured video titles

    You can preorder the book using this link. Once the page opens, just click the President’s Day Sale banner and select our or any other book on sale.

    Happy querying!

  • SQL Server 2014 Upgrade Technical Guide

    If you plan to upgrade to SQL Server 2014, then this technical guide is a must. With 429 pages, it is a complete book, yet still available as a free download here. It covers all products and features in the SQL Server suite, and upgrades from versions 2005, 2008, 2008 R2, and 2012. It supplements the information available in Books Online. Besides the actual upgrade, the white paper also covers planning and pre- and post-upgrade tasks.

    This is the fourth upgrade technical guide I coauthored – all together, I was involved in versions 2008, 2008 R2, 2012, and 2014. This is the complete list of authors: Ron Talmage, Richard Waymire, James Miller, Vivek Tiwari, Ken Spencer, Paul Turley, Danilo Dominici, Dejan Sarka, Johan Åhlén, Nigel Sammy, Allan Hirt, Herbert Albert, Antonio Soto, Régis Baccaro, Milos Radivojević, Jesús Gil, Simran Jindal, Craig Utley, Larry Barnes, Pablo Ahumada. Thanks to everybody for a smooth process of writing and editing!

  • PASS SQL Saturday #356 Slovenia Recapitulation

    So the event is over. I think I can say for all three organizers, Mladen Prajdić, Matija Lah, and me, that we are tired now. However, we are extremely satisfied. It was a great event. First few numbers and comparison with SQL Saturday #274, the first SQL Saturday Slovenia event that took place last year.

    SQL Saturday #274

    SQL Saturday #356




    Show rate



    Proposed sessions



    Selected sessions



    Selected speakers






    The numbers nearly doubled. We are especially proud of the show rate; with 95%, this is much better than average for a free event, and probably the highest so far for a SQL Saturday. We asked registered attendees to be fair and to unregister if they know they can’t attend the event in order to make room for those from the waiting list. An old Slovenian proverb says “A nice word finds a nice place”, and it works. 36 registered attendees unregistered. Therefore, we have to thank to both, the attendees of the event and those who unregistered.

    Of course, as always, we also need to thank to all of the speakers, sponsors and volunteers. All volunteers were very helpful; however, I would like to especially point out Saša Mašič. Her work goes well beyond simple volunteering. I must mention also the FRI, the Faculty of Computer and Information Science, where the event was hosted for free. It is also worth mentioning that we are lucky to live in Ljubljana, such a beautiful city with extremely nice inhabitants who like to enjoy good food, hanging around and mingling, and long parties. Because of that we could be sure in advance that both speakers and attendees from other countries would enjoy spending time here also outside the event, that they would feel safe, and get help whenever they would need it.

    From the organizational perspective, we tried to do our best, and we hope that everything was OK for speakers, sponsors, volunteers, and attendees. Thank you all!

  • SQL Saturday #356 Slovenia Registrations

    SQL Saturday #356 Slovenia is practically full. OK, actually we have already reached the expected number of registrations (200). We have raised the number to 220, and we are close to that number as well. Therefore, we (the organizers, Matija, Mladen and me) need to ask all of you who are registered and already know that you will not be able to attend: please unregister and make room for those who would like to attend, but did not register yet. And you who would like to register, please do it as soon as possible, in order to get the confirmation immediately or to be at least at the top of the waiting list.

    We would like to make an appeal to all of you who are registered: please come. Please remember that this conference was made possible because of the speakers, who are using their time and come on their own expenses to give you state of the art presentations, because of the sponsors, who are giving us and financing the venue, the food, the raffle awards, and more, and of course, because of many volunteers who spend their free time to help with the organization. We are also paying a fixed number of meals to the catering company; therefore, we would throw the money away for those who are registered and would not come. In short: all you need to do is to wake up, get out of bed, get into a good mood, and come to the event to get top presentations, good food and meet friends!

    Thank you all!

  • SQL Saturday #356 Slovenia Schedule Is Alive!

    We are approaching the PASS SQL Saturday #356 Slovenia event. Today, we published the schedule. With so many submissions, we had a hard time to select only 24 sessions. Unfortunately, we could not accommodate all of the speakers who submitted sessions. However, we decided to invite to speakers dinner also the speakers that were not selected. Therefore, if you submitted some proposals and you can’t find yourself on the schedule, don’t give up. Please join us anyway, and enjoy the dinner (and the party) with other speakers and with us anyway! You will also get a personal invitation.

    Potential attendees, the conference is filling up quickly. If you want to join the event, please register soon.

    Finally, potential sponsors, we are still accepting new sponsors.

  • PASS SQL Saturday #356 Slovenia – Speakers and Sessions Submitted Breakdown by Country Final

    So this is the final analysis of the speakers and the sessions submitted for the PASS SQL Saturday #356 Slovenia event, December 13th, Ljubljana, Slovenia. Call for speakers closed on October 15th. The number of proposed sessions and speakers is impressive. I am really honored and humbled by the number of foreign speakers that sent their proposals. I simply can’t remember a conference in Slovenia dealing with MS technology with that many top speakers from foreign countries. Thank you all speakers! Definitely an event that is worth visiting.


  • PASS SQL Saturday #356 Slovenia Pre-Conference Seminars

    I am proud and glad I can announce two top pre-conference seminars at the PASS SQL Saturday #356 Slovenia conference. The speakers and the seminars titles are:

    Both seminars will take place on Friday, December 12th, in the classrooms of our sponsor Kompas Xnet. The price for a seminar is € 149, with early bird price at € 119. Early bird price is valid until October 31st.

    I am also using this opportunity to explain how and why we decided for these two seminars. The decision was made by the conference organizers, Matija Lah, Mladen Prajdič, and Dejan Sarka. There was a lot of discussion in different social networks about PASS Summit pre-conference seminars lately. If you have any objections for our seminars, please do not start big discussions in public; please tell them to the three of us directly.

    First of all, unlike at the PASS Summit seminars, the speakers are not going to earn big money here, and therefore it is not really worth spending much time and energy on our decision. We think that any of the speakers who sent proposals for our SQL Saturday could present a top quality seminar. We would like to enable seminars for every speaker that wants to deliver one. However, in a small country, we will have already hard time to fill up the two seminar we have currently. Our intention is to reimburse at least part of the money the speakers spent on their own for travelling expenses and accommodation. In our opinion, it makes sense to do this for the speakers that spent the most for the travelling. Coming here from USA is expensive, and it also takes three days in both directions. That’s why we decided to organize the seminars for the first two speakers from USA.

    Of course, this is not the last event. If everything goes well with SQL Saturday #356 and with the seminars, we will definitely try to organize more events in the future, and invite more speaker to deliver a seminar as well.

    Thank you for understanding!

  • PASS SQL Saturday #356 Slovenia – Details for my Countrymen

    For a change, I am posting a blog in Slovenian language. I am posting the details about PASS SQL Saturday #356 Slovenia. Of course, everybody is invited to attend the conference, submit proposals, or even join us as a sponsor. The vast majority of presentations will be in English language anyway. So here are the details for my countrymen.

    Slovenska skupnost SQL Server ne miruje. Pripravljamo nov dogodek, že drugič bo to PASS SQL Saturday. Tokrat bomo dogodek organizirali v soboto, 13. decembra, in sicer v prostorih FRI, Ljubljana ( To bo tudi edini dogodek v drugi polovici letošnjega leta v Sloveniji, kjer bodo predavanja posvečena strežniku SQL Server.

    Ideja za dogodke pod skupnim imenom PASS SQL Saturday se je porodila v svetovni skupnosti uporabnikov sistem SQL Server, v skupnosti Professional Association for SQL Server, oziroma PASS ( Ti dogodki so nekakšen odgovor na ekonomsko in predvsem na krizo izobraževanja. Pomanjkanje vlaganja v izobraževanje je namreč vsesplošen svetovni problem. V SQL Server skupnosti smo se odločili poskusiti ponuditi uporabnikom strežnika SQL Server vsaj nekaj, torej vsaj brezplačen dogodek.

    Kako to poteka? Prvi del enačbe predstavljamo predavatelji. Ne le, da predavanja na SQL Saturday niso plačana; vsak predavatelj si celo sam krije vse stroške. Seveda predavatelj obenem porablja svoj prosti čas. V osnovi gre za enostavno načelo »jaz tebi, ti meni«. Preden sami dodate, kaj jaz tebi in kaj ti meni, naj to razložim. Slovenski predavatelji, predvsem slovenski MVP-ji za SQL Server, pogosto predavamo na podobnih dogodkih v tujini. Tako nam ni težko pridobiti tujih predavateljev za naš dogodek. Vsi skupaj tako lahko omogočimo vsem lokalnim skupnostim vrhunska predavanja.

    Drugi del enačbe so sponzorji. Dogodki so sicer precej poceni, saj, kot sem že omenil, ni stroškov s predavatelji. Potrebujemo predavalnice ter nekaj hrane in pijače. Nekaj denarja primakne PASS, predvsem pa je vedno zraven velika podpora s strani Microsofta. Naj še posebej izpostavim podporo v Sloveniji. Ko smo lansko leto prvič organizirali dogodek, smo šli na resnični minimum stroškov. Poskusili smo najti sponzorja, ki bi nam omogočil prostor brezplačno. Ko smo razložili, za kakšen dogodek gre, najprej na sestanku SLODUG, potem pa še osebno, je bil odziv neverjeten. V dveh dneh smo dobili štiri ponudbe za brezplačen prostor! Še več, tudi pri hrani je ponudnik šel v posel praktično brez dobička; tako dobro kosilo za tako ceno, kot smo jo imeli lani, bo težko ponoviti.

    Tretji del enačbe ste udeleženci. Lanskoletni odziv je presegel naša pričakovanja. Pa ne gre zgolj za pasivne udeležence; dobesedno otepati smo se morali prostovoljcev, ki so želeli kakorkoli pomagati. Še več, tudi razmerje med tistimi, ki so prišli, in količino prijav, je bilo najboljše na svetu. Drugače rečeno, osip je bil veliko pod svetovnim povprečjem. To je res nekaj vredno, ko vidiš, da ljudje z navdušenjem in s spoštovanjem sprejmejo dogodek.

    Naj se za hip povrnem na prvi del enačbe, na predavatelje. SQL Saturday ni namenjen le uveljavljenim predavateljem; dobrodošli so tudi začetniki, tudi tisti, ki bi radi stopili na pot predavanj. Če mislite, da lahko začnete delati kariero na tem področju, se registrirajte kot PASS uporabnik, prijavite na spletno stran našega dogodka (, ter pošljite predloge za predavanja. Svetujemo, da pošljete predloge in tudi predavate v angleškem jeziku. Le tako lahko računate tudi na morebitni prodor v tujino.

    Letos bomo poskusili vpeljati več novosti. Prvo predstavljata dva predkonferenčna seminarja, ki bosta v petek, 12. decembra, v prostorih Microsoft Slovenija. Seminarja ne bosta brezplačna, bo pa cena zelo razumna. En seminar bo posvečen relacijskemu delu, en pa poslovni inteligenci. Namen seminarjev je predvsem en: omogočiti vsaj delno povrnitev stroškov tistim predavateljem, ki pridejo resnično od daleč. Taki predavatelji ne samo, da plačajo veliko za letalske vozovnice, ampak tudi porabijo skupaj kar tri dni za pot k nam in nazaj domov. Druga novost je četrti sklop predavanj, ki bo odprt za sorodne tehnologije, torej tehnologije, ki uporabljajo ali zlorabljajo SQL Server, kot na primer .NET in SharePoint Server. Tretja novost bo, če bodo sponzorji zainteresirani, kratka predstavitvena predavanja sponzorjev.

    Naj še omenim, da smo letos začeli s še enim, prav posebnim in slovensko izvirnim dogodkom. To je bil junijski SQL piknik, ki je bil ravno tako izjemno lepo sprejet, čeprav je šlo za bistveno manjši dogodek. Upamo, da bo tudi ta dogodek postal tradicionalen. Vsekakor načrtujemo SQL piknik ponoviti prihodnje leto.

    Kaj lahko storite vi? Veliko. Predvsem se prijavite. Prijavite se kot udeleženci, prijavite se kot prostovoljci, prijavite se na konferenco, prijavite se na seminar. Lani nas je bilo skupaj 150. Naj nas bo letos 200. Pomagate nam lahko tudi tako, da najdete sponzorje. Morda je ravno vaše podjetje zainteresirano za predstavitev na tej konferenci? Še enkrat – to bo edini dogodek v preostanku leta, kjer bo zbrana bolj ali manj celotna skupnost uporabnikov strežnika SQL Server. Pa še nekaj – tako kot lani, tudi letos se dogodka res udeležite. S tem boste pokazali največ spoštovanja predavateljem, sponzorjem in organizatorjem. Hvala vnaprej.

  • PASS SQL Saturday #356 Slovenia

    And we are alive!

    I am proud to announce the PASS SQL Saturday #356 Slovenia event. The date is December 13th. In December, Ljubljana is even more beautiful than usually, and it is definitely a party city. Therefore, together with great sessions, a lot of reasons to come. Welcome!

  • SQLBits XII

    Back home from SQLBits XII, back to normal life. However, I am already missing the conference. I have spoken with quite a few other speakers, and our conclusion is, as my friend Niko Neugebauer said: this conference is simply second to none. Great content, and very interesting split into three days: seminars if you wish to get more personal touch and in-depth knowledge about specific topic the first day, a regular high-end conference the second day, and a free community conference the third day. This way, everybody can find something for her- or himself. In addition, I liked very much the way how the food was served – in many small dishes. This way you can taste many different dishes, and there were nearly no queues. And what to say about the Steampunk party? If you were not there, you should be sorry.

    Therefore, thank you all organizers and volunteers for your work and your time, and for inviting me there. Thank you all attendees for making this conference possible, and especially thanks to those who visited my seminar and my session. I promise I will upload my material as soon as it would be possible. And finally, thank you all speakers for you contribution and for the many funny hours we spent together. I hope we all meet next year again.

  • Conferences – 2nd Semester 2014 - Update

    I have to correct the post from a month ago, I had to make some changes in my plans.

    1. SQLBits 2014, July 17th-19th: high time to register, pre-conf seminars start in two days. I have an interesting pre-con seminar there – Advanced Data Modeling Topics. In addition, I have a session as well. This is now over.

    2. Kulendayz 2014, September 5th – 7th: I guess I will speak there. However, The most important thing there is to cool down:-) However, besides eating Kulen and other Slavonia specialties, I am giving a Data Quality and Master Data Management precon and a talk during the conference.

    3. SQL Saturday #344 Tirana, October 4th: Never been in Albania, looking forward to visit Tirana. Seems like this one is either cancelled or moved.

    4. SQL Saturday #311 Bulgaria, October 11th: Visiting Sofia after many years. Nice people, good rakija.

    5. Sinergija 14, October 20th – 21st, Belgrade, Serbia: Best barbecue. Besides great sessions, of course.

    6. SharePoint dnevi 2014, October 21st - 22nd, Portorož, Slovenia: I think the conference party would not be the same if I don’t appear there:-)

    7. PASS Summit 2014, November 4th - 7th, Seattle, WA, USA: Can’t miss the most important community conference in the world.

    8. SQL Saturday #355, November 22nd, Parma, Italy – I guess you all heard for Parma Prosciutto and Parmigiano Reggiano.

    9. SQL Saturday #359, December 6th, Istanbul, Turkey: I am not 100% whether I will make it, but I definitely hope to get some real kebab, köfte, dolma, sarma…

    10. SQL Saturday Ljubljana, December 13th, Ljubljana, Slovenia: I am co-organizing this event. Date reserved, call for speakers is going out soon. Consider coming – most of the session will be in English. Maybe not the best parties, but definitely the longest. Ljubljana lives all night. This will be the best possible ending of the 2014 conferences.

    Hope we will meet at some of these events.

  • Data Modeling Resources

    You can find many different data modeling resources. It is impossible to list all of them. I selected only the most valuable ones for me, and, of course, the ones I contributed to.

    • Books
      • Chris J. Date: An Introduction to Database Systems – IMO a “must” to understand the relational model correctly.
      • Terry Halpin, Tony Morgan: Information Modeling and Relational Databases – meet the object-role modeling leaders.
      • Chris J. Date, Nikos Lorentzos and Hugh Darwen: Time and Relational Theory, Second Edition: Temporal Databases in the Relational Model and SQL – all theory needed to manage temporal data.
      • Louis Davidson, Jessica M. Moss: Pro SQL Server 2012 Relational Database Design and Implementation – the best SQL Server focused data modeling book I know by two of my friends.
      • Dejan Sarka, et al.: MCITP Self-Paced Training Kit (Exam 70-441): Designing Database Solutions by Using Microsoft® SQL Server™ 2005 – SQL Server 2005 data modeling training kit. Most of the text is still valid for SQL Server 2008, 2008 R2, 2012 and 2014.
      • Itzik Ben-Gan, Lubor Kollar, Dejan Sarka, Steve Kass: Inside Microsoft SQL Server 2008 T-SQL Querying – Steve wrote a chapter with mathematical background, and I added a chapter with theoretical introduction to the relational model.
      • Itzik Ben-Gan, Dejan Sarka, Roger Wolter, Greg Low, Ed Katibah, Isaac Kunen: Inside Microsoft SQL Server 2008 T-SQL Programming – I added three chapters with theoretical introduction and practical solutions for the user-defined data types, dynamic schema and temporal data.
      • Dejan Sarka, Matija Lah, Grega Jerkič: Training Kit (Exam 70-463): Implementing a Data Warehouse with Microsoft SQL Server 2012 – my first two chapters are about data warehouse design and implementation.
    • Courses
      • Data Modeling Essentials – I wrote a 3-day course for SolidQ. If you are interested in this course, which I could also deliver in a shorter seminar way, you can contact your closes SolidQ subsidiary, or, of course, me directly on addresses or This course could also complement the existing courseware portfolio of training providers, which are welcome to contact me as well.
      • Logical and Physical Modeling for Analytical Applications – online course I wrote for Pluralsight.
      • Working with Temporal data in SQL Server – my latest Pluralsight course, where besides theory and implementation I introduce many original ways how to optimize temporal queries.
    • Forthcoming presentations
      • SQL Bits 12, July 17th – 19th, Telford, UK – I have a full-day pre-conference seminar Advanced Data Modeling Topics there.
  • Working with Temporal Data in SQL Server

    My third Pluralsight course, Working with Temporal Data in SQL Server, is published. I am really proud on the second part of the course, where I discuss optimization of temporal queries. This was a nearly impossible task for decades. First solutions appeared only lately. I present all together six solutions (and one more that is not a solution), and I invented four of them.


    My previous blog post “Truncate Table – DDL or DML Statement?” got quite a few comments. Now I am continuing with a similar discussion: is the ALTER TABLE SWITCH [PARTITION] DDL (Data Definition Language) or DML (Data Manipulation Language) statement? At least it is clear that this is not a Data Control Language (DCL) statement.

    Again, I can find a lot of arguments why this would be a DDL statement. First of all, we all know the classical categorization of the statements:

    · DDL includes CREATE, ALTER, and DROP statements

    · DCL includes GRANT, REVOKE, and DENY statements

    · DML includes SELECT, INSERT, UPDATE, DELETE, and MERGE statements.

    SQL Server changes system pages when you use this statement. In addition, you need elevated permissions to use the statement. Clearly, from the syntax perspective, it is a DDL statement.

    However, logically, you just move the data from one table or partition to another table or partition. You do not change the schema at all. Therefore, semantically, this is a DML statement. And again, in my opinion, the logical perspective is the most important here, because this is the main point of the Relational Model: work with it from the logical perspective, and leave the physical execution to the underlying system.

    Let me show how this statement works. I start with the clean-up code, if some of the objects I am going to create in the tempdb system database already exist.


    Next, let me create the partition function and the partition scheme, create a demo table dbo.FactInternetSales and populate it with the data from the dbo.FactInternetSales from the AdventureWorksDW2012 demo database. I will also create two additional tables, one for the new data load, and one for the data from the oldest partition of the dbo.FactInternetSales table.


    Note that the table for the new data includes a check constraints that guarantees that all of the data can be switched to a single partition of the partitioned table. I am loading the dbo.FactInternetSalesNew table with the last data you can find in the demo database, internet sales for year 2008. Let me check the data in all three tables, and also all partitions of the partitioned table.


    If you check the results, you can see that there is data in three partitions of the partitioned table, and in the table for the new data. Next step is to switch the data from the new data table to a single partition of the partitioned table.

    Can I do the same thing with a single DML statement? The TRUNCATE TABLE works logically similarly to the DELETE statement without the WHERE clause if a table is without a trigger and without the identity property. Does something similar exist for the ALTER TABLE SWITCH [PARTITION] statement? The answer is, of course, yes. You can use the composable DML statements with the OUTPUT clause. With the next statement, I am moving all of the data from the oldest partition of the partitioned table to the table created for the old data.


    Let’s check where the data is now, and if schema has anyhow changed.


    If you execute the statements, you can clearly see that the effect of the last composable DML statement was completely the same as the effect of the ALTER TABLE SWITCH [PARTITION] statement. The schema did not change a bit. Therefore, the ALTER TABLE SWITCH [PARTITION] is clearly a DML statement.

  • Truncate Table – DDL or DML Statement?

    Many times, categories of concepts and things overlap. It can be hard to categorize some items in a single category. The SQL TRUNCATE TABLE statement is an example of an item that is not so easy to categorize. Is it a DDL (Data Definition Language) or DML (Data Manipulation Language) statement?

    There is an ongoing discussion about this topic. However, if you quickly bingle for this question, you get the impression that the majority is somehow leaning more toward defining the TRUNCATE TABLE statement as a DDL statement. For example, Wikipedia clearly states: “In SQL, the TRUNCATE TABLE statement is a Data Definition Language (DDL) operation that marks the extents of a table for deallocation (empty for reuse).” Disclaimer: please note that I do not find Wikipedia as the “ultimate, trustworthy source” – I prefer sources that are signed!

    Some of the reasons why many people define the statement as a DDL statement include:

    • It requests schema locks in some systems
    • It is not possible to rollback it in some systems
    • It does not include a WHERE clause
    • It does not fire triggers in some systems
    • It resets the autonumbering column value in some systems
    • It deallocates system pages directly, not through an internal table operation
    • and more.

    On the other hand, it looks like there is only one reason to treat the statement as a DML statement:

    • Logically, you just get rid of the data, like with the DELETE statement.

    Even the Wikipedia article that I referred to says “The TRUNCATE TABLE mytable statement is logically (though not physically) equivalent to the DELETE FROM mytable statement (without a WHERE clause).”

    Like many times, I have to disagree with the majority. I understand that the categorization is somehow confusing, and might even be overlapping. However, the only reason for categorizing the TRUNCATE TABLE statement in the DML category is “THE” reason in my understanding. One of the most important ideas in the Relational Model is the separation between the logical and the physical level. We, users, or people, if you wish, are manipulating with data on the logical level; the physical implementation is left to the database management system. And this is the important part – logically, when you truncate table, you don’t care how this statement is implemented internally, you just want to get rid of the data. It really does not matter what kind of locks a system uses, does it allow WHERE clause or not, etc. The logical point is what matters. Therefore, I would categorize the TRUNCATE TABLE statement as a DML statement.

    Of course, this is a purely theoretical question, and is really not important for your practical implementation. As long as your app is doing what it should do, you don’t care too much about these nuances. However, IMO in general there is not enough of theoretical knowledge spread around, and therefore it makes sense to try to get the correct understanding.

    But there is always a “but”. Of course, I have immediately another question. What about the ALTER TABLE mytable SWITCH [PARTITION…] TO… statement? ALTER statements have been defined as DDL statements forever. however, again, logically you are just moving the data from one table to another. Therefore – what? What do you think?

This Blog


Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement