Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

BIT Barbarian

After working in the mortgage industry for 7 years, I transitioned into Business Intelligence and began learning SQL and .NET. My goal is to integrate my business knowledge into my development to intelligently analyze and find solutions to problems. Blogging offers me an option to share what I've learned as well as receive feedback on better practices and solutions.

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.

/*******************************************************
identifying gaps on a month  
*******************************************************/
if object_id('tempdb..#accounts') is…

Read more

0 comments, 62 reads

Posted in BIT Barbarian on 19 May 2014

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.… Read more

0 comments, 117 reads

Posted in BIT Barbarian on 11 February 2014

Renaming all references inside stored procedures and functions can be migraine worthy without a little help…

If you run across migrating or copying a database structure for some purpose, yet need to change the database references or some other string value inside all the procedures and functions to point to the newly named object, you are in for a lot of work!

I built this procedure… Read more

0 comments, 299 reads

Posted in BIT Barbarian on 13 August 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… Read more

0 comments, 291 reads

Posted in BIT Barbarian on 24 July 2013

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

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… Read more

0 comments, 462 reads

Posted in BIT Barbarian on 18 July 2013

SSMS 2012 Extender for the times you want some organization to the random pile of objects gathering in the dusty confines of that database

When dealing with large amounts of objects in a database, navigation can be tedious with SSMS object explorer. This extender organizes the objects into groups based on schema, helping a developer easily navigate to the appropriate object. The current version didn’t work for views, but the other objects were grouped… Read more

0 comments, 261 reads

Posted in BIT Barbarian on 16 July 2013

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.

/*******************************************************
Some Basic Info on Database principals, permissions, 
explicit permissions, and if role, who is in this role currently
*******************************************************/
;
with
	roleMembers
	as (
			select
				drm.role_principal_id
				,dp.principal_id
				,dp.name…

Read more

0 comments, 420 reads

Posted in BIT Barbarian on 31 May 2013

A moment of void in the cranium reveals a recursive computed column with an esoteric message

Msg 402, Level 16, State 1, Line 67
The data types varchar and void type are incompatible in the add operator.
I came across this error today when I accidentally used a computed column in a temp table, that referenced itself. This very unhelpful message was caused by referring to… Read more

0 comments, 351 reads

Posted in BIT Barbarian on 30 May 2013

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.

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 ) +…

Read more

0 comments, 207 reads

Posted in BIT Barbarian on 22 May 2013

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… Read more

0 comments, 492 reads

Posted in BIT Barbarian on 22 May 2013

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… Read more

0 comments, 307 reads

Posted in BIT Barbarian on 21 May 2013

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… Read more

0 comments, 652 reads

Posted in BIT Barbarian on 1 May 2013

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. 

Read more

0 comments, 14,278 reads

Posted in BIT Barbarian on 30 April 2013

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… Read more

0 comments, 20 reads

Posted in BIT Barbarian on 23 April 2013

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… Read more

0 comments, 204 reads

Posted in BIT Barbarian on 22 April 2013

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… Read more

0 comments, 103 reads

Posted in BIT Barbarian on 18 April 2013

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… Read more

0 comments, 2,066 reads

Posted in BIT Barbarian on 18 April 2013