All posts

2015

Enabling Instant File Initialization

Found a couple good walkthroughs on enabling instant file initialization. However, I’m becoming more familar with the nuances of various setups and found it confusing in trying to map the correct user/group to enable this option. In my case, I had the SQL Service running under NT SERVICE/MSSSQLSERVER and as such this logic wasn’t showing up when trying to find groups/users to add to the necessary permissions. Lo and behold…

I typed it in manually and it worked. If time permits …

  read more

Upgrade from SQL 2014 Evaluation to Developer Edition

Couldn’t find documentation showing that upgrade from SQL 2014 evaluation version was possible to developer edition. I just successfully converted an evaluation version to developer edition.

  1. Obtain the key for the developer edition (in my case I had to download the .ISO from MSDN downloads, and go through the installation wizard to get it)
  2. Run the installation center app for SQL Server 2014
  3. Select edition upgrade
  4. Plug in your new serial from the developer edition. Pretty simply, but …
  read more

Utilizing the power of table parameters to reduce IO, improve performance, decrease pollution, and achieve world peace...

I was dealing with a challenging dynamic sql procedure that allowed a .NET app to pass in a list of columns and a view name, and it would generate a select statement from this view. Due to requirements at the time, I needed the flexibility of the “MAIN” proc which generated a dynamic select statement, while overriding certain requested views by executing a stored proc instead of the dynamic sql.

During this, I started looking into the string parsing being completed for a comma …

  read more

SQL Sentry Pro Explorer is worth it...

Updated: 2017-04-21
Another great bit of news from reviewing this older post I wrote… SQL Sentry Pro is now a free tool thanks to the generosity of the Sentry One team! It’s a must have. Go download it for sure.
  read more

Restoring a database that doesn't exist

When restoring a database that doesn’t exist, say for instance when a client sends a database to you, you can’t use the option to restore database, because there is no database matching to restore. To get around this you need to use the Restore Files and Filegroups option and then restore the database.   read more

Statistics Parsing

Never really enjoyed reading through the statistics IO results, as it makes it hard to easily guage total impact when you have a long list of tables. A friend referred me to: http://www.statisticsparser.com/ This site is great! However, I really don’t like manually copying and pasting the results each time. I threw together a quick autohotkey script that will detect your clipboard change event, look for “scan count” keyword, and then open a “chrome app”, paste the …

  read more

Dev Tools: The File Searcher/Launcher to rule them all

Why does this not have more recognition? In the experimentation of various file management and launching apps, I’ve tried several (Launchy, Listary, etc), but none have offered the speed and customization of Find and Run Robot. This app is a life saver for the power user! Here is an example of how you can have a hotkey to immediately launch a customized google search. The group alias gives you extensibility to filter the text you are typing to identify this alias of “Google Me” …

  read more

Case of the Mondays... causing me to randomly redefine the Scope of Global

Today, I was reminded that global temp tables scope lasts for the session, and doesn’t last beyond that. The difference is the scope of the global temp allows access by other users and sessions while it exists, and is not limited in scope to just the calling session. For some reason I can’t remember, I had thought the global temp table lasted a bit longer. Remembering this solved the frustration of wondering why my adhoc comparison report was empty….. #mondayfail SQLMag …

  read more

2014

Dev Tools: XYplorer (review 1) - Catalog

currently on version 14.60 I’m a big fan of finding tools that help automate and streamline things that should are routine actions.Surprisingly, I’ve found it incredibly challenging to move away from the default Windows Explorer for file management, as the familiarity it offers makes it somewhat tough to be patient with learning an alternative, especially if the alternative offers more complication. That’s where I stood with XYPlorer for sometime. It is a developer’s tool …

  read more

OR pattern causing indexing scans in parameter based queries

#Tl;dr

(time constraints prevented me from reworking significantly)

“or-condition-performance” article on SQL Server Pro was forwarded over to me to research by a friend who indicated that using a variable with an or pattern had historically caused table scans. This was a suprise to me as all previous queries with optional parameters I’d used in the past seemed to use index seeks. I had to dig into this a little deeper to see if had been missing this in my past work and needed …

  read more

Generate Random Date With Starting Point

If you want to create sample random samples when dealing with date calculations to test your results, you can easily create a start and end point of randomly created dates. This is a snippet I’ve saved for reuse:

DATEADD(day, (ABS(CHECKSUM(NEWID())) % $Days Seed Value$), '$MinDate$')

This should let you set the starting point (Min Date) and choose how far you want to go up from there as a the seed value. Ie, starting point 1/1/2014 with seed of 60 will create random dates up to 60 days …

  read more

Finding Groups - Consecutive Months

A step by step explanation on one way to get a consecutive period of months, which could easily be adapted to days, years, or other values. I’ll continue on this track and post a tutorial on eliminating overlapping dates soon.

Eliminate Overlapping Dates

I was looking for an efficient way to eliminate overlapping days when provided with a historical table that provided events that could overlap. In my case, I had dates show the range of a process. However, the multiple start and end dates could overlap, and even run concurrently. To eliminate double counting the days the process truly was in play I needed a way to find eliminate the overlap, and eliminate duplicate days when running in parallel. I researched ways to complete this and found the …

  read more

Scalar functions can be the hidden boogie man

Ran across a comment the other day that scalar functions prohibit parallelism for a query when included. I thought it would be worth taking a look, but didn’t take it 100% seriously. Came across the same indication today when reviewing MVP deep dives, so I put it to the test.Turns out even a simple select with a dateadd in a scalar format was affected enough with that one action to drop 5% on the execution plan. When dealing with merge or other processes that would benefit from …

  read more

2013

Shortcut to reference examples, syntax, and definitions straight from SSMS

I’ve never really used the F1 key for help files with most applications. I was surprised at the usefulness in SSMS I discovered today that uses scripting to actually get you MSDN articles relevant to your current selection in the query editor.

If you have a keyword selected and want to view details, definition, and examples on it, you can highlight the phrase or select the word, press F1, and SSMS will pull up the appropriate MSDN article. The only issue I ran into was that it pulls up the …

  read more

Check Constraints can help enforce the all or nothing approach when it comes

If you have a set of columns inside your table that you want to allow nulls in, however if one of the columns is updated force all columns in the set to be updated, use a check constraint. In my case, I had 3 columns for delete info, which were nullable. However, if one value was updated in there, I want all three of the delete columns to require updating. I created the script below to generate the creation and removal of these constraints on a list of tables:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11 …
  read more

TSQL Snippet for viewing basic info on database principals and their permissions

Quick snippet I put together for reviewing basic info on database users/principals, permissions, and members if the principal is a role.:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
/*******************************************************
    Some Basic Info on Database principals, permissions, explicit permissions, and if role, who is in …
  read more

View computed columns in database

Snippet to quickly view computed column information. You can also view this by doing a “create table” script. This however, was a little cleaner to read and view for me.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

select
    database_name = db_name()
    ,object_schema_name = object_schema_name( object_id )
    ,object_name = object_name( object_id )
    ,full_object_name = object_schema_name( object_id ) + '.' + object_name( object_id )
    ,column_name = name
    ,cc. …
  read more

On how to Googlify your SQL statements for future searching

For sake of future generations, let’s begin to reduce typing and reuse code we’ve built. I think we can all agree that TSQL statements are often repeated. Ideally, snippets should be created to reduce repeated typing and let us focus on logic and content. However, some statements may not really be “snippet worthy”, and just be quick adhoc queries. In the past, the solution for saving queries for reuse or reference in the future would be to just save in the projects folder …

  read more

Get synonym definitions for all databases in server

If you want to audit your enviroment to look at all your synonyms and see where they are pointing, you can use exec sys.sp_MSforeachdb to loop through databases, and even filter. It will save some coding. However, my research indicates it is probably a bad practice to rely on this undocumented function as it may have issues not forseen and fully tested.

Additionally, support may drop for it in the future. I recreated what I needed with a cursor to obtain all the synonym definitions into a temp …

  read more

Calculating the next beginning of month and the current end of month

Handling dates is always a fun challenge in T-SQL! Finding the current end of month and next months beginning of month is straight forward, but I like to find new ways to do things that take less coding, and hate date conversions that require a lot of manipulation of characters and concatenation. This was what I came up with for avoiding character conversions and concatenation for finding the current BOM (beginning of month) and EOM (end of month) values. Adjust according to your needs. Cheers! …

  read more

Installing SSMS 2012 all by it's lonesome

SQL Server Management Studio (SSMS) is not offered as a standalone download on MSDN. Installation requires the user to download the sql server installation package and choose to install only this single feature. For developers, SQL Developer edition is a great choice.

Here’s some screenshots to give you a guide on installing SSMS by itself when working with the full installer.

  read more

snippet designate a certain time of the day in getdate()

Snippet to designate a certain time of the day to evaluate in the current day. If you need to limit a result to the current date after a particular time, strip the time out of the date, and concatenate the current time together with it, and then convert back to datetime2.

1
select convert(datetime2(0),cast(cast(getdate() as date) as varchar(10)) + ' 09:00 ')
  read more

Native SSMS a second class citizen no longer...

Intellisense can be a boon to adding quick development. Quick hints on scope specific variables, syntax suggestions, function descriptions and more provide a valuable tool to productive coding.Coding in SQL Server Management Studio (SSMS) has greatly improved over the version releases, but it still lags behind the power of Visual Studio’s intellisense, template insertions with “fill in the blank” functionality .

Additionally, the lack of automatic indentation means that lining …

  read more

dynamic sql and a char crash

Dynamic SQL can be helpful, but a pain to debug. I spent hours today working on figuring out why my simple date comparison in dynamic SQL wasn’t working. Found out that the remote database I was connecting to had a char date instead of a datetime. I found the comparison of CHARDATE > VARCHARDATE failed to error out, but also failed to give a proper result set. Changing the look-up to ensure both dates were converted to date fixed the issue. During this debugging I was reviewing my …

  read more

How to default SSRS date parameters to the first and last day of the the previous month

Populating default dates in SSRS can be helpful to save the user from having to constantly input the date range they normally would use. When a report is pulled for last month’s information, defaulting the date fields for the user can help streamline their usage of the report, instead of them manually selecting with the date-picker control in SSRS. The formula’s I used were:

1
2
3
Beginning of Current Month (EOM) DateSerial(Year(Date.Now), Month(Date.Now), 1)
Beginning of Last …
  read more