THE SQL Server Blog Spot on the Web

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

Greg Low (The Bit Bucket: IDisposable)

Ramblings of Greg Low (SQL Server MVP, MCM and Microsoft RD) - SQL Down Under

  • BETWEEN vs >= and <=

    I love it when I get queries that are actually easy to answer.

    Today, one of my developer friends asked me if it was better to use BETWEEN or to use >= and <= when filtering for a range of dates.

    From a logic perspective, I like the idea that a single predicate expresses your intent rather than needing two predicates to do the same. For example, consider the following two queries:

    image

    I’d argue that the first one expresses the intent slightly more clearly than the second query. The intent is to find orders in a particular range of dates. Having that as a single predicate expresses that intent slightly more clearly than having to assemble the intent from multiple predicates. At least I think so.

    But the bigger question is about performance. It’s easy to see that they are identical. If you enter the following query against the AdventureWorks database:

    image

    Then request an estimated execution plan (Ctrl-L), you’ll see this:

    image

     

    The missing index warning isn’t relevant to this discussion and if you hover over the Clustered Index Scan, you’ll see this:

    image

    Note under the Predicate heading that SQL Server has converted the original BETWEEN predicate into a pair of >= and <= predicates anyway. You’ll find it does the same for LIKE predicates as well. LIKE ’A%’ becomes >= ’A’ AND < ’B’.

    So performance is identical. It’s more of a style issue, and I think that BETWEEN is (only very) slightly more expressive so I’d prefer it.

    UPDATE: Aaron Bertrand posted a pertinent comment on this. I would only lean to using BETWEEN if I’m strictly working with dates or other types of discrete values (ints, etc.), not with datetime values that actually contain times. If that was the case, I’d definitely lean towards the separate predicates.

  • Database on a diet (Part 1)

    Hi Folks,

    My next article for SQL Server Magazine is out now. I’ve decided that New Year is the time that most people discuss diets and databases don’t have to be an exception.

    I’m discussing one large client who had a database that really needed to be trimmed down. That involved a whole range of mechanisms and the outcome was great: major size reduction and significant performance improvement.

    I’ve decided to discuss this across a series of articles, each covering part of the story about how the reductions were produced and the rationale for each.

    You’ll find part one here now: http://sqlmag.com/database-performance-tuning/data-tales-case-database-diet-part-1

    Enjoy !

  • New-Object : Constructor not found. Cannot find an appropriate constructor for type

    This one is here so that I don’t forget it in future.

    I’ve been writing Powershell again all day today and I spent ages trying to find this error when instantiating a particular object.

    Bottom line is that there was a valid constructor that took a single string argument. However, I was passing an empty string to the constructor. Let’s just leave it at “The error message returned is interesting”.

    Hope it helps someone else.

  • Icons and the real world

    Just realised that book icons are another one that will probably lose meaning over time. That'll apply to book icons for dictionaries and thesauruses too.

    image

     

    Phone icons are already pretty odd as many kids would never have seen a phone that looks like those icons.

    image

     

    And the Save icons that are a picture of a 3 1/2 inch floppy disk are basically meaningless now.

    image

    I had to laugh when I heard a kid who saw a 3 1/2 inch floppy for the first time comment that someone had made "a actual save icon". I suppose that's how it seemed to him.

    The open icons are often manilla folders. They still have some life left in them but not much.

    image

    There are still some clipboards around but not many.

    image

    Video icons that show sprocket holes each side of a film are basically meaningless now.

    image

    All the mail, email, mail merge, etc. icons that show letters are fast becoming meaningless. (Ask Australia Post about that) And adhesive labels?

    image  image

    Even the latest versions of Office (2016) have old style lightbulbs for ideas. That'll quickly be meaningless.

    image

    The slideshow icon in PowerPoint is an old style roll-up projector screen. And it's timing icons are all analog clocks (limited lifespan). It's screenshot icons are cameras that very few kids would recognize.

    image   image

    And so on and so on. I wonder how long it will be before many of the icons that were designed to represent common physical things will lose all their physical equivalence. Does that then make it harder for newcomers to computing to recognize anything?

  • SQL Down Under–Show 65–Jen Underwood and Power BI

    Hi Folks,

    We’ve taken a fairly long break but now back into the swing for SQL Server 2016 and Power BI.

    The first of the new series of shows is a show on the current state of Power BI with Jen Underwood.

    You’ll find it here: http://www.sqldownunder.com/Podcasts

    or subscribe download like any other podcast. Feed link is: http://www.sqldownunder.com/SQLDownUnderMP3Feed.xml.

    Enjoy!

    Regards,

    Greg

  • Data Tales #5: The Case of the Rogue Index

    Hi Folks,

    The fifth in my Data Tales series has been published at SQL Server Magazine. It’s the Case of the Rogue Index. You’ll find it here:

    http://sqlmag.com/database-administration/data-tales-5-case-rogue-index

    Enjoy!

  • My Sessions from Ignite Australia on the Gold Coast now online

    Hi Folks,

    Couldn’t make it to Ignite? The team from Microsoft Australia recorded all the sessions and they are online now.

    Here are the three sessions that I delivered:

     

    Azure Datacamp Power Hour:   http://www.sqldownunder.com/links/5

    Things I Wish Developers Knew About SQL Server: http://www.sqldownunder.com/links/6

    Working With SQL Server Spatial: http://www.sqldownunder.com/links/7

     

    Enjoy!

  • Data Tales #4: The Case of the Phantom Duplicate

    I’ve posted another in my Data Tales series for SQL Server Magazine.

    It’s “The Case of the Phantom Duplicate”. You’ll find it here: http://sqlmag.com/sql-server/data-tales-4-case-phantom-duplicate

    Enjoy!

  • Data Tales #3: The Case of the Stubborn Log File

    Hi Folks,

    Another of the Data Tales series that I've been writing for SQL Server Magazine has been published. It's The Case of the Stubborn Log File.

    You'll find it here:

    http://sqlmag.com/sql-server/data-tales-3-case-stubborn-log-file

    Enjoy!

  • AzureCon is this month–register now!

    AzureCon is the main #Azure related conference each year, and of course, it’s an online conference.

    It’s coming up at the end of this month (September).

    It’s time to register to see what ScottGu and the people from the Azure team have to tell us.

    http://www.sqldownunder.com/links/200002

  • Data Tales #2: The Case of the Exploding Table

    Hi Folks,

    Another of the Data Tales series that I've been writing for SQL Server Magazine has been published. It's the Case of the Exploding Table.

    You'll find it here:

    http://sqlmag.com/sql-server-2012/data-tales-2-case-exploding-table

    Enjoy! 

  • Data Tales #1: The Case Of The Auto-Truncating Table

    Tim Ford (SQL Agent Man: http://thesqlagentman.com/) recently got me involved in writing a column for SQL Server Magazine.

    I’m planning to write an article each fortnight describing something of interest that I’ve recently found while consulting/mentoring at client sites.

    The first of those articles is now live: http://sqlmag.com/sql-server/case-auto-truncating-table

    Enjoy!

  • FIX: Internal service error when refreshing Personal Gateway for Power BI

    We recently started working with the new Personal Data Management Gateway for Power BI. Overall, we really like it but the error messages in most of Power BI have left much to be desired.

    One error that we were encountering made us feel like the service was flaky as it seemed to happen randomly. When we tried to refresh a dataset, we got this error:

     

    RefreshError

     

    The Power BI team came to the rescue and worked out what was happening. Turns out that you cannot currently refresh more than once every 5 minutes. That also includes within 5 minutes of your initial upload. Unfortunately, this is the error returned when you attempt it.

    Apparently this 5 minute limit is going to be removed soon and hopefully that will be one less error we might see.

  • Passed my Chinese HSK3 Exam–Thanks to all that helped

    One of my biggest goals for this year was to try to pass the HSK 3 exam. I wanted to do it as a validation of my efforts to learn Chinese.

    HSK (Hanyu Shui Ping Kaoshi - 汉语水平考试) is the exam given to foreigners to assess their level of Chinese. (Hanyu is the Chinese language, Shuiping basically means a level of achievement, and Kaoshi is an exam). The organisation that runs it is called Hanban.

    There are six levels of exam.

    • Level 1 is “Designed for learners who can understand and use some simple Chinese characters and sentences to communicate, and prepares them for continuing their Chinese studies. In HSK 1 all characters are provided along with Pinyin.”
    • Level 2 is “Designed for learners who can use Chinese in a simple and direct manner, applying it in a basic fashion to their daily lives. In HSK 2 all characters are provided along with Pinyin as well.”
    • Level 3 is “Designed for learners who can use Chinese to serve the demands of their personal lives, studies and work, and are capable of completing most of the communicative tasks they experience during their Chinese tour.”
    • Level 4 is “Designed for learners who can discuss a relatively wide range of topics in Chinese and are capable of communicating with Chinese speakers at a high standard.”
    • Level 5 is “Designed for learners who can read Chinese newspapers and magazines, watch Chinese films and are capable of writing and delivering a lengthy speech in Chinese.”
    • Level 6 is “Designed for learners who can easily understand any information communicated in Chinese and are capable of smoothly expressing themselves in written or oral form.”

    While I’d love to achieve Level 6 one day, my medium term goal is Level 5. That’s the level required for students entering Chinese universities. But my goal for this year was Level 3. It included 100 points for listening, 100 points for reading, and 100 points for writing. I managed 275 all up, which I am super happy about.

    I need to thank all my Chinese buddies on Facebook who endlessly answer my mundane questions about Mandarin.

    But my biggest thanks needs to go to all at eChineseLearning.com. Spending an hour one-on-one with a teacher three times each week has made an enormous difference. For most of this period, Amy was my teacher. Amy (and most of the teachers including my current teacher Bella) is based in Wuhan, China. If you have any interest in getting serious about Mandarin Chinese, I strongly suggest talking to them. If you mention me, we both get some free time but that’s not my main concern. I’d just love to see more people learning Mandarin. It’s going to be (and already is) a very important language in the future. Estimates are that 1 in 4 children born today will be native Mandarin speakers. (And for interest, 1 in 5 will be native Spanish).

    I’ve found that learning Mandarin has already opened up another whole world to me.

    Onwards to Level 4 !   加油!

  • Cannot determine next value for a sequence

    One of the most anticipated new features in SQL Server 2012 was the introduction of sequences. Prior to SQL Server 2012, developers had a choice of IDENTITY columns or a roll-your-own table mechanism.

    Sequences allow us to create a schema-bound object that is not associated with any particular table. For example, if I have a Sales.HotelBookings table, a Sales.FlightBookings table, and a Sales.VehicleBookings table, I might want to have a common BookingID used as the key for each table. If more than the BookingID was involved, you could argue that there is a normalization problem with the tables but we'll leave that discussion for another day.

    Recently when working with sequences however, I found a problem with their implementation. It works as described but is not useful.

    So let's start by creating the schema and the sequence:

    image

     

    We could then use this schema as the default value for each of the three tables:

    image

    All this is as expected. One question that often arises though, is "how do I know the last value for a given sequence". The answer provided is to query the sys.sequences view. We can do this as follows:

    image

    The current_value colum in sys.sequences is defined as follows:

    Datatype: sql_variant NOT NULL

    The use of sql_variant here makes sense as the view needs to be able to provide the current value for all sequences, regardless of data type. Sequences can be created with any built-in integer type. According to BOL, the possible values are:

    • tinyint - Range 0 to 255
    • smallint - Range -32,768 to 32,767
    • int - Range -2,147,483,648 to 2,147,483,647
    • bigint - Range -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807
    • decimal and numeric with a scale of 0.
    • Any user-defined data type (alias type) that is based on one of the allowed types.

    The output of that column is described as:

    The last value obligated. That is, the value returned from the most recent execution of the NEXT VALUE FOR function or the last value from executing the sp_sequence_get_range procedure. Returns the START WITH value if the sequence has never been used.

    And this is where I have a problem with how it's defined. When you have never retrieved a value from the sequence, there is no last value obligated. What it does return is the first value that will be generated, but has not yet been generated:

    clip_image001

    The documentation is correct but the behaviour is bizarre. I believe that this column should return NULL. Otherwise, there is no way to tell that this value has not yet been generated.

    If I generate a new value and then query it again ie:

    image

    Note that the same value is returned:

    clip_image001[1]

    It's only when I request it another time, that I see the expected value:

    clip_image002

    So the problem is that when you read the current value from the sys.sequences view, there's no way to know if this is the last value obligated or the next one that will be obligated.

    I'd really like to see this behaviour changed. Given that the SQL Server team rates backwards compatibility highly, an alternative would be to add a new column to sys.sequences that indicates that the sequence has never been used. There is a column is_exhausted. At a pinch, that could be set for new sequences.

    Thoughts?

    If you agree, you can vote here: https://connect.microsoft.com/SQLServer/feedback/details/1461552

This Blog

Syndication

Tags

No tags have been created or used yet.

Archives

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