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.
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 parallelism, this would become even more pronounced. Suggest reading "Death by UDF" section by Kevin Boles. This comment is buried at the very end of the chapter. He indicates
"One final parting gift: scalar UDFs also void the use of parallel query plans, which is why the FormatDate UDFpegged only ONE CPU core on my laptop! " (Page 194-summary)
{{< admonition type="info" title="Updated: 2016-03-18" >}} Cleaned up formatting. This is older code limited to procs and functions. I'm sure there is a better way to do this now, but leaving here as it might help someone else in the meantime. {{< /admonition >}}
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 to search all procedures and functions, and script the replacement across multiple databases, to streamline this type of conversion.
I'll post up one for views and synonyms later, as my time was limited to post this. In my case, this script was built to replace DB1 with DB2, and I had to accomplish this across several databases at once.
This script might help save you some time!
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 most recent article, so if you aren't running SQL Server 2012, make sure you select the < 2012 documentation to be sure it is accurate.
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:
/* CHECK CONSTRAINT TO ENSURE SET OF COLUMNS IS NULL OR IF UPDATED,
THAT ALL COLUMNS IN SET ARE UPDATED Columns: delete_date null delete_by_id null delete_comment null PASS CONDITION
1: IF ALL COLUMNS NULL = PASS PASS CONDITION
2: IF ALL COLUMNS ARE UPDATED/NOT NULL = PASS
FAIL: IF 1,2 OF THE COLUMNS ARE UPDATED, BUT NOT ALL 3 THEN FAIL
*/
/* GENERATE CHECK CONSTRAINT ON ALL SELECTED TABLES TO REQUIRE ALL DELETE
DATE COLUMNS TO BE UPDATED CORRECTLY */
select
t.TABLE_SCHEMA
,t.TABLE_NAME
,script_to_remove_if_exists = ' IF exists (select * from sys.objects where name =''check_' + t.TABLE_SCHEMA + '_' + t.TABLE_NAME + '_softdelete_requires_all_delete_columns_populated_20130718'') begin alter table ' + t.TABLE_SCHEMA + '.' + t.TABLE_NAME + ' drop constraint check_' + t.TABLE_SCHEMA + '_' + t.TABLE_NAME + '_softdelete_requires_all_delete_columns_populated_20130718 end '
,script_to_run = ' alter table ' + t.TABLE_SCHEMA + '.' + t.TABLE_NAME + ' add constraint check_' + t.TABLE_SCHEMA + '_' + t.TABLE_NAME + '_softdelete_requires_all_delete_columns_populated_20130718 check ( ( case when delete_date is not null then 1 else 0 end + case when delete_by_id is not null then 1 else 0 end + case when delete_comment is not null then 1 else 0 end ) in (0, 3) ) '
from
INFORMATION_SCHEMA.TABLES t
where
t.TABLE_NAME like 'mytablename%'
and exists (select
*
from
INFORMATION_SCHEMA.COLUMNS C
where
t.TABLE_CATALOG = C.TABLE_CATALOG
and t.TABLE_SCHEMA = C.TABLE_SCHEMA
and t.TABLE_NAME = C.TABLE_NAME
and C.COLUMN_NAME = 'delete_by_id')
and exists (select
*
from
INFORMATION_SCHEMA.COLUMNS C
where
t.TABLE_CATALOG = C.TABLE_CATALOG
and t.TABLE_SCHEMA = C.TABLE_SCHEMA
and t.TABLE_NAME = C.TABLE_NAME
and C.COLUMN_NAME = 'delete_comment')
and exists (select
*
from
INFORMATION_SCHEMA.COLUMNS C
where
t.TABLE_CATALOG = C.TABLE_CATALOG
and t.TABLE_SCHEMA = C.TABLE_SCHEMA
and t.TABLE_NAME = C.TABLE_NAME
and C.COLUMN_NAME = 'delete_date')
order by
t.TABLE_SCHEMA asc
go
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 effectively. Highly recommend!
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
from
sys.database_role_members drm
inner join sys.database_principals dp
on drm.member_principal_id = dp.principal_id
)
select
db_name()
,dp.name
,stuff((
select distinct
', ' + p.permission_name
from
sys.database_permissions p
where
dp.principal_id = p.grantee_principal_id
and p.major_id = 0
and p.state = 'G'
for xml path(''), type
).value('.', 'varchar(max)'), 1, 1, ''
) as general_permissions
,stuff((
select distinct
', ' + p.permission_name
from
sys.database_permissions p
where
dp.principal_id = p.grantee_principal_id
and p.major_id = 0
and p.state = 'D'
for xml path(''), type
).value('.', 'varchar(max)'), 1, 1, ''
) as deny_permissions
,stuff((
select distinct
', ' + p.permission_name + ' on ' + object_schema_name(p.major_id) + '.' + object_name(p.major_id)
from
sys.database_permissions p
where
dp.principal_id = p.grantee_principal_id
and p.major_id <> 0
for xml path(''), type
).value('.', 'varchar(max)'), 1, 1, ''
) as specific_permissions
,stuff((
select distinct
', ' + r.name
from
roleMembers r
where
r.role_principal_id = dp.principal_id
for xml path(''), type
).value('.', 'varchar(max)'), 1, 1, ''
) as current_active_members
from
sys.database_principals dp
order by
dp.name asc;
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 the computed column itself in the computed column definition, ie typo. Beware!
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 manually. However, it is difficult to always make sure the file is saved, review previous version that may be want had overrode, or even review what statements you actually executed. SSMSToolsPack has historically offered a great logging option. However, as an individual it was hard to justify the cost out of my own pocket. SSMSBoost has provided a great solution! Note that this was recently added (April), and is a "rough draft" , with minimal interface options, yet provides an amazing solution that I've found to offer a great solution. In addition to the other great features that SSMSBoost offers (which I'll write about in the future), SSMSBoost now offers 3 unique solutions to saving work.
3.Tab History Logging If you crash SSMS, close SSMS without saving tabs, or have some unsavory Windows behavior that requires SSMS to be restarted, don't fear... your tabs are saved. When restarting you can select restore tabs and begin work again. I've found this feature to be a lifesaver!
Instructions I recommend for setup and searching your entire sql history nearly instantly.
Basic Settings I choose (If you aren't using Bitstreamfont... you are missing out)
CurvyTabs = true HtmlExtensions = html;htm;xhtml;shtml;shtm;php;asp;jsp;sql InitialSorting = -8
Initial Sorting = -8 means that instead of sorting by "match %" which I didn't find helpful for me, to sort by modified date in desc order. This means I'll find the most most recent match for the text I'm searching for at the top of my list.
Setup your custom indexes. I setup separate indexes for executed statements and editor history so I could filter down what I cared about and eliminate near duplicate matches for the most part. Right click in blank space to create index.
I setup as follows:
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 ) + '.' + object_name( object_id )
,column_name = name
,cc.is_persisted
,cc.Definition
from
sys.computed_columns cc
order
by full_object_name asc