Archive for T-SQL

| February 25, 2014
5 comments

On a SQL Quest Using SQL Search by Red Gate

OLYMPUS DIGITAL CAMERA

Four SQL professionals gathered from the four corners of the world to share their SQL knowledge with each other and with their readers: Mickey Stuewe from California, USA, Chris Yates from Kentucky, USA, Julie Koesmarno from Canberra – Australia, and Jeffrey Verheul from Rotterdam – The Netherlands. They invite you to join them on their quest as they ask each other questions and seek out the answers in this collaborative blog series. Along the way, they will also include other SQL professionals to join in the collaboration.

Original Post: On a SQL Collaboration Quest 

The First Mission

For our first collaborative entry in this blog series, we have chosen to investigate Red Gate’s FREE tool, SQL Search. The question we posed to each other was: What is the purpose of Red Gate’s SQL Search and how do you use it in your daily work life?

The Search Begins

Right now I’m in retro-fit mode at work. I have an application that I recently inherited that needs a new feature. This requires me to add new columns to tables in the database(s), as well as find all the objects (Stored procedures, triggers, etc.) that reference the tables I need to modify to make sure they will still work and to add logic to support the new feature.

Red Gate has a whole suite of tools for me to use on my current adventure at work, but the tool I used the most this past week was their FREE tool, SQL Search. This tool will work with SQL 2005 and up.

Instead of writing about how to use SQL Search, I want to highlight some shortcut keystrokes that will improve the speed of using this awesome tool. Did I mention it is a FREE awesome tool?

The Task

I have a set of tables that I modified and I need to look at the code for all the objects that reference that set of tables to not only make sure they still worked properly, but to add code to support the new feature that was added. I’ll use the Adventureworks2008R2 database in my example below.

After I found the list of objects I needed to inspect, I did the following:

  1. Scripted out the object as an ALTER statement.
  2. Quickly searched for the table in the script (some of the scripts were 1000+ rows long).

Here is my favorite part

If you double-click one of the objects in the SQL Search list of found objects, that object will be found and selected in the in the Object Explorer. Unfortunately, if that object is a view, it takes several mouse clicks to get an ALTER script created, soooo…

With the help of Mark Holmes (b|t), we figured out what the hot key combination is to script out the highlighted object as an ALTER statement. You ready? SHIFT  F10 + S+A+N. Yes, that is a lot of keys, but if you are like me, keystrokes are faster than mouse clicks.

Note: I remember it as SHIFT F10 + SAN

The CTRL + F shortcut will take you into the Find dialog box where you can search for the name within the script. If you had been searching  for the same value prior to this script, then F3 is your best friend and it will take you directly to the search term in the ALTER script.

But wait! There’s more

If you are a Red Gate junkie like myself and have SQL Prompt installed, then there is another shortcut you can add between SHIFT + F10 + SAN and F3.  If you use CTRL K + CTRL Y then you can have the alter script formatted to your taste. (aka, you can read it faster. Win!)

Note: I remember it as CTRL + KY

image

Scenario Time

So here is what it would look like if you were using the Adventureworks2008R2 database and looking for the Person table.

  1. Enter Person into the search field
  2. Select the “Exact Match” option. (Optional)
  3. Select the “All Objects” from the list of objects
  4. Select “All Databases” from the list of databases (The search term is automatically searched. Hitting ENTER is not required.)
  5. Click on one of the objects listed
  6. SHIFT  F10 + S + A + N to create an ALTER script
  7. CTRL K + CTRL Y to format it (this step requires SQL Prompt)
  8. CTRL  F to pull up the SSMS search window to search within the script
  9. Type in Person and click enter. You’ll be taken to the first instance of Person
  10. Use F3 to find the other instances of Person in the script

Now to look at a second script. You already have the list of objects, so you don’t have to search for them again.

  1. Click on one of the objects listed.
  2. SHIFT  F10 + S + A + N to create an ALTER script
  3. CTRL K + CTRL Y to format it (this step requires SQL Prompt)
  4. Use F3 to find the other instances of Person in the script

Repeat these four steps for each script you need to look at. Win!

Don’t Stop Yet

If you want to read more about SQL Search, don’t forget to check out these blog posts:

To follow our quest for SQL knowledge through this collaborative project, follow the #SQLCoOp tag on Twitter.

Category: Community, Red Gate, SQL CoOp, T-SQL, Tools | Tags: , , , , , ,
| February 11, 2014
4 comments

T-SQL Tuesday #51- Don’t Crap Out While Betting On Table Functions

My good friend Jason Brimhall (b|t) is hosting this month’s T-SQL Tuesday blog party. The party was started by Adam Machanic (b|t) in December of 2009. As a compliment to the upcoming debut of the Las Vegas SQL Saturday, Jason has taken up a betting theme. He wants to know our stories of when we bet it all on a risky solution and won or lost.

Instead of telling you about the past, I want to help you win big at the table today. I really don’t want you to crap out while betting on the wrong table functions.

Snake Eyes

There are two types of table functions Multi-line Table Functions and In-Line Table Functions. There is a huge difference between the two of them.

Multi-line table functions sound great. You write as much code as you need in them and they will return all the data in a table variable. This is where the weighted dice rolls snake eyes every single time. You see, the statistics for a table variable always, always says there is only one row in the table being returned. It doesn’t matter if there are a hundred, a thousand, or a million rows. The statistics will say one. Which means the optimizer has a good chance of loosing when it picks the execution plan for that query.

Let’s Take a Look at the Bets

For my example, I have a simple query that returns 43 rows out of a Tally table. Notice that the index estimates 43 rows will be returned, which is great, because that is exactly on the money!

TSQLTuesday51_1

 

If we put that same query inside of a multi-line table function, we get an estimated number of rows of 1 (snake eyes!).

 

TSQLTuesday51_2

 

TSQLTuesday51_3

 

Double Down

An in-line table function will return the same result set, but there are some limitations on its construction. The entire query within the in-line table function needs to be done in only one statement.

Note: You can get very creative with Common Table Expressions (CTE) if need be.

There are two benefits to using an in-line Table Function. One, is that the Estimated Number of Rows will be accurate (or as accurate as the statistics on the table), and two, the “inside” of the in-line table function is not masked in the Execution Plan. It is plopped right into the middle of the calling query. (Yes, “plopped” is a technical term. )

 

TSQLTuesday51_4

 

TSQLTuesday51_5 

Last Call

So…Remember to double down on in-line table functions and don’t crap out on the snake eyes of the multi-line table function.

Thanks for all the fish

Thanks go out to Jason Brimhall for hosting this month’s T-SQL Tuesday blog party. Please visit his website at http://jasonbrimhall.info/, or better yet come to Las Vegas for their SQL Saturday and thank him in person.

Category: Execution Plans, Performance, SQL Functions, T-SQL, T-SQL Tuesday | Tags: , , , , , , ,
| February 3, 2014
Comments Off on Questions and Answers for Pragmatic Work’s Presentation on the Tablix Control

Questions and Answers for Pragmatic Work’s Presentation on the Tablix Control

ChalkBoardThursday, January 23rd I had the opportunity to present for Pragmatic Works as part of their “Training on the T’s” where they provide free one-hour training every week on Tuesdays and Thursdays. I gave my presentation entitled, Scalable SSRS Reports Achieved Through the Powerful Tablix. Below are the questions (and answers) that the attendees asked during my presentation.

View Presentation
Slide deck and demos downloads

Questions and Answers for Demo 1

Q: In the first presentation, the 1st tablix, do territory group and country region has the same datasets, meaning does country region have territory?

A: A tablix or a set of nested tablix can only use 1 dataset. My dataset joined several tables, two of which were Sales.SalesTerritory and PersonCountryRegion. (I’m using the Adventureworks2008R2 database.)
Here is how they are related:
SELECT
cr.Name AS CountryRegion
,st.Name AS Territory
FROM
sales.SalesTerritory AS st
JOIN Person.CountryRegion AS cr ON st.CountryRegionCode = cr.CountryRegionCode

Q: When you add a Row Group with that new column that doesn’t let you merge with the other cells to the right… what you can do is split that column, and then you’ll be able to merge.

A: Yes. This is true, but you still can’t merge across the dotted lines that are introduced into the Matrix.

Q: I always supposed that group levels had to be to the left of the dashed line. But you showed that you can delete those columns/rows but keep the grouping. Does this have any effect on the data in the columns on the left or the right of the dashed line?

A: No, it does not. If the GUI asks If you want to delete only the row/column or the row/column and the group, make sure to indicate that you only want to delete the row/column. This will keep the group, which is what we were after.

Q: Did you have to create the row groups (lower left corner), or were they available dimensions?

A: I’m not sure about this question, please email me some more information so that I can answer the question better.

 

Questions and Answers for Demo 2

Q: Could you have added the expression on the image itself and not add the extra row?

A: Not for this technique. If I would have added the expression to the image to show/hide it, then it would have left “white space” where the image would normally go when the image was hidden. By repeating the row with a different layout, we can reclaim the ‘white space” for the comment field to use.

Questions and Answers for Demo 3

Q: For creating the Emp Phone list, is there a reason why you used a table and created the group as opposed to using a matrix? Thanks!

A: This is a great question. You can use either controls and end up with the same result. I had been demonstrating the matrix control in the other demos, so I wanted my viewers to see how you could start with a table. This becomes handy if they requirements of the layout change. It’s important that you know that you don’t have to start all over, but you can change one control into the other because all three controls (table, matrix, and list) are all based on the Tablix template.

Q: For you last demo where you had dynamic columns for phone numbers, what did you have to change in the detail row?

A: In the detail row, I had to change the detail row to a group. I did this by going into the Detail group properties and adding a group on BusinessEntityID. I then added Last Name, First Name, and BusinessEntitty ID as the fields to sort by. This step is needed because the employee names are repeated once for each address type and each phone number type.

 

General Questions and Answers

Q: How did you make your color palette?

A: This is a great question. Here are the steps.

    1. Add a table to a blank report that will become your template report
    2. Create a cell for each color in your color pallet.
    3. Change the dimensions of each cell to have a height of 0.15 and a width of 0.15.
    4. Change the background property of each cell to a different color in your color pallet.

Now you can make the other changes to the report to create a template report and save it in the template folder for BIDS/SSDT.

Q: How to avoid the columns getting merged on exporting to an excel

A: This is one of those questions that is helpful to also explain to all your end users since they ask the same question. The answer is not the best answer, but it is better than what we had 10 years ago. If you save your report as a CSV file, Excel will still automatically open it and the columns will not be merged. The downside is you lose charts and any formatting. The upside is the columns are no longer merged and the end user can use more functionality of Excel. They do need to be reminded that they will have to save it as an Excel file after opening it in order to save any formatting features that they have added to the file.

Q: Is there a way to fix the tablix to a set number of rows, and an exact amount of vertical space for the tablix? It seems that the tablix ‘reserves’ some additional space below the tablix which interferes when placing report items below the tablix.

A: This can be a challenging problem and it requires a lot of testing if different results set sizes to get the layout to work out the way you want. I will be creating a blog post soon on how to mimic a “fixed row” layout. You can contact me to get the the blog post early if you want.

I haven’t noticed the tablix control “reserving” space. To best figure out what is causing this extra white space do the following. Show the lines on the outside of the tablix and the control that you want to “butt up” against the tablix control You may find that the extra space is on the inside of the control instead of the outside of the control. A couple of properties to look at as are padding and Borderwidth. By changing them, you will reduce white space.

I have noticed that it can be difficult to drag and drop another control right up against another control. In those cases I do some math. By adding the Top and Height properties of the tablix you get the location of the end of the control. Then add 1 or 2 more to that value to have the Top value for the next control.

Q: Is there a way to link one tablix to multiple Datasets?

A: Unfortunately no, but there are some workarounds. There are a couple of functions that were introduced in 2008 R2 that will allow you to reference a value in another dataset, but it won’t allow you to show multiple values. A second way is to use a subreport within the tablix. I try to minimize this, since it can add ALOT of overhead. The third way, would be to create a larger dataset with repeating data that can be grouped. This is what I did in my third demo in order to create multiple addresses and multiple phone numbers for each employee. Again, you have to weigh the benefit of the technique against how much data will be used in the report.

Q: This is a great presentation. How can I get a copy of the RDLs, and the datasets?

A: You can go to my resource page to download the presentation and the demos. Pragmatics has the recording so that you can watch the presentation again. I’ve also provided the links at the top of this post.

Q: Will a transcript or summary of this demo be available for review?

A: You can go to my resource page to download the presentation and the demos. Pragmatics has the recording so that you can watch the presentation again. I’ve also provided the links at the top of this post.

Q: Will you be sharing your ‘Knowledge Based Document’ with us?

A: Unfortunately I can’t, because I don’t have references in it for code I found on the internet or in books. I will however be spending time writing short posts with tips this year, so keep an eye on my blog and on mssqltips.com for posts.

 Q: Thanks – this was a great presentation.

A: Thank you. I’m glad everyone enjoyed it and that everyone had so many questions. 🙂

Category: Community, Pragmatic's Free Training On The T's, Speaking, SSRS, T-SQL | Tags: , ,
| January 13, 2014
2 comments

Tribal Award Winner for Best New Community Voice

2013 was an amazing year for me and winning this award was the cherry on top. I want to not only thank the people who voted for me, but for the people who nominated me.

Thank You!

I appreciate your belief in me and for accepting me into the SQL Family.

While we can’t all clap together, we can tweet our approval. Please help me recognize the four others who were nominated with me. It’s a big planet, and they have contributed in their countries and abroad to be noticed and nominated too.

  • Koen Verbeeck (b|t) from Belgium
  • William Durkin (b|t) from Germany
  • Rob Sewell (b|t) from United Kingdom
  • Mark S. Rasmussen (b|t) from Denmark

Very Sincerely,
Mickey Stuewe

Category: Award, Red Gate, Speaking, T-SQL | Tags: , ,
| January 13, 2014
One comment

I Will Be Speaking This Month for Pragmatics

Microphone_YetiThis month I will be sharing my popular presentation, Scalable SSRS Reports Achieved Through the Powerful Tablix on Pragmatic’s webinar series, “Free Training on the T’s”. I will be going over different ways to use the Tablix control in this one hour presentation and it will be aired live, on 1/23/2014, 11:00 AM EST / 8:00 am PST. Click here to sign up.

Category: Community, Pragmatic's Free Training On The T's, Speaking, SSRS, T-SQL | Tags: , , , ,
| December 31, 2013
Comments Off on Reflections in the 2013 Mirror

Reflections in the 2013 Mirror

2013Reflections-lake2013 was a wild ride. I had set up my goals in December of 2012 and surpassed some of them half way through the year. I hope that I continue reaching my goals in 2014. If I do, it will be another amazing year.

Here are the goals I had and how I measured up.

Being Mentored

I knew I wanted to be mentored, I even wrote a post about it here. I had no idea how valuable it would turn out to be. After I wrote my post, one of my friends convinced me to ask Grant Fritchey (b|t) if he would mentor me. I really like his speaking style and the topics he speaks on. So, I held my breathe, sent him an email, and he said yes. I couldn’t believe it.

We Skyped all through 2013. Our meetings were very valuable for me. You see, like many people, my confidence isn’t that high, but Grant believes in me. He encouraged me to go after my goals. He critiqued my abstracts and even my first speaking event. This was definitely an achieved goal.

Speaking

This goal I blew out of the water. My goal was to speak at 5 events. I picked this number because there are 5 SQL Saturdays in California where I live. I ended up speaking at 15 different events. I exceeded my goal by 300%! Here is the full list of 2013 events and here is a summary of the events:

  • 6 User Group Meetings
  • 5 SQL Saturdays
  • 3 Women in Technology panels (I moderated 2 at SQL Saturday’s I was already attending, so I didn’t count them in the total count)
  • 2 Red Gate events (You can view one of my sessions here.)
  • 1 Conference (Dev Connections)

I’ve already listed the SQL Saturday’s I plan to apply for. The conferences will be added as I’m accepted as well as the user group meetings. Here is the list of 2014 events so far.

Blogging

My blogging goals were only half met. The first part of my goal was to write on my personal blog (this one) at least once a month. Since I like to participate in the T-SQL Tuesday Blog Parties, I knew this was most likely achievable. The other half of my goal was to write once a month for mssqltips.com. I did not reach this goal at all because there just wasn’t enough time. I was only able to submit one tip this year.

For my person blog, I exceeded my expectations. Since this was my first full year having a blog, I compared the first half of the year with the second half of he year. In that time, I doubled my page views. I had 40 posts for the year, 18 of which were in December, 8 of which were for the T-SQl Blog Party, and 1 was an incredibly fun (and funny) story based on the pictures that Pat Wright (b|t) took at PASS Summit.

Here are the 3 posts that had the most hits this year:

Obtaining a New Job

It took six months of carefully identifying exactly what I wanted to do and going through interviews and talking to recruiters, but this past July I finally took a leap into a new job and I’m glad I did. I miss my former colleagues, but I’ve acquired some pretty cool new ones too.

Friends of Red Gate (ForG)

Red Gate has my absolute favorite tools. They also have some amazing people working for them. This year had the privledge of being part of their Friends of Red Gate program. This program conects various proffesionals who are power users of their products with developers and project managers at Red Gate. We get to disucss new features and how to improve existing features. It’s a wonderful program and I think their product is better for having the program.

Unexpected Surprises

Those were my goals for 2013, but I had some surprises along the way that have shaped me this year and are worth mentioning.

Jan 2013 SQL CruiseSQL Cruise

In December of 2012 I won a trip (the registration) on Tim Ford’s (b|t) SQL Cruise from SQL Sentry. The cruise was for a week in the Caribbean. Three wonderful days at sea listening to amazing speakers like Kevin Kline (b|t) and Allen White(b|t). And three wonderful days in port. I thoroughly enjoyed my experience and recommend it to ALL of you.

Authoring

Half way through the year, Marlon Ribunal (b|t) approached me and asked if I wanted to help finish the book he was writing on reporting services. I was very honored that he asked me and I said yes. Marlon and I had our book published this past October. It’s called SQL Server 2012 Reporting Services Blueprints.

I also had the honor of being included in an ebook by Red Gate called 45 Database Performance tips for Developers. I’m very honored to be included in this endeavor with Grant Fritchey (b|t), Jonathan Allen (aka Father Jack) (b|t), Phil Factor (b|t), K. Brian Kelly (b|t), Ike Ellis (b|t), and Louis Davidson (b|t).

Volunteering

I was so impressed with the first SQL Saturday that I ever attended, that I agreed to help out with two local SQL Saturdays (Huntington Beach and San Diego). Since I enjoyed the planning and running around with my head cut off, I’ve agreed to help out again this next year.

I also help the Women In Technology virtual chapter (WIT). I had the honor of moderating three panels this past year, one of which was at PASS Summit in front of 600+ people. You can view the event here.

One of my most proud moments occurred this month, but it really started in September. (You can read the back story here.) I created an event for Jason Strate (b|t) and Bradley Ball (b|t) so that they could speak in Orange County. I had no idea if one person would show up or 100. QuickStart Intelligence hosted the event and they helped find attendees by calling all of their former students. Between my contacts, QuickStart’s former students, and Jason and Bradley’s reputations I was able to get 48 people sign up and around 30 people attend the event. I also found a sponsor for the pizza so it didn’t cost me or any of the attendee’s a dime. I call that a win!

Nominations

I thought that my 2013 SQL year was at a close once December hit, but I was wrong. Red Gate put out a call for nominations for their new Tribal Awards and I was nominated for Best New Community Voice. I wrote about it here. All that I can say about this one, is that I’m pleasantly surprised and that I’m secretly hoping that Koen from Belgium wins.

2013CheersThank You

I want to thank all of you for reading my posts this year, for the comments that you leave, and for coming back time and time again. Cheers to you!

Where am I going now?

So what does 2014 hold for me? You’ll have to wait for my next post. (Evil grin inserted here.)

Category: Reflections, Speaking, T-SQL | Tags: , , , , , , ,
| December 23, 2013
Comments Off on Tribal Awards – Nominated For Best New Community Voice

Tribal Awards – Nominated For Best New Community Voice

In December Red Gate introduced the Tribal Awards. Some of the awards are fun like “SQL Karaoke Performer of the Year” and others are more serious like “Blog of the Year”. They collected nominations and I found out that I was selected for “Best New Community Voice”. I’m very honored to be included with the other four gentlemen… all from Europe.

  • Koen Verbeek from Belgium
  • William Durkin from Germany
  • Rob Sewell from United Kingdom
  • Mark S. Rasmussen from Denmark

Now that the nominees have been selected, the community can now vote for the winners of the Tribal Awards, but be quick. The voting ends on January 3rd with the winners being announced on January 7th.

Category: Award, Community, Red Gate, T-SQL | Tags: , ,
| December 17, 2013
Comments Off on SQL Advent Calendar – Day 17 – Now, Which Tables Still Need a Trigger?

SQL Advent Calendar – Day 17 – Now, Which Tables Still Need a Trigger?

AdventCalendar17First Post in this series: SQL Advent Calendar 2013-Day 1- Placeholders in SQL Prompt Snippets

As I was hanging our family Advent Calendar up, I thought I would make one for you, my readers. I’ll be sharing my scripts from two of my favorite products as well as tips on how to create your own.

Oh where, oh where did my trigger go?

On Day 8, Day 9, and Day 10 I wrote about using 4 Change tracking fields in tables. Two of the fields, ModifiedBy and ModifiedDTS are updated in a trigger. If you are slowly making changes to many tables, you can loose track of which tables have the triggers you need.

This little snippet for SQL Prompt, called FindTrig, will get two result sets for you. The first result set is a comma delimited list of tables that have the ModifiedDTS field, but do not have a trigger with the pattern trg__u. The second result set will show you a the same list of tables and the triggers they already have. This will be helpful when the code to update ModifiedBy and ModifiedDTS was added to a different trigger than expected. You definitely don’t want two triggers updating the same two fields. That will cause an infinite loop of updates.

USE <DBName,string, $DBNAME$>
GO
/* ***********************************************************************************
Purpose:    Creates a comma delimited list of tables that may need a trigger to populate
			the ModifiedBy and ModifiedDTS fields
Author:     mstuewe
Date:       12/17/2013
************************************************************************************ */
DECLARE @Tables AS nvarchar(max) 

	SELECT
		@Tables = ISNULL(@Tables + ',', '') + t.name
    FROM
        sys.objects AS t
        JOIN sys.all_columns AS c ON c.object_id = t.object_id
        LEFT JOIN sys.Triggers AS Tr ON tr.name = 'trg_' + t.name + '_iu'
                                                 OR tr.name = 'trg_' + t.name + '_u'
    WHERE
        t.type = 'U'
        AND c.NAME = 'ModifiedDTS'
		AND tr.name IS NULL
	ORDER BY 
		t.name

	IF @Tables IS NULL
		PRINT 'All tables have a trigger.'
	ELSE
	BEGIN
		SELECT @Tables AS TableNames

		SELECT
			t.NAME AS TableName
			,tr.name AS ExistingTrigger
		FROM
			(
			SELECT
				t.object_id
				,t.name
			FROM
				sys.objects AS t
				JOIN sys.all_columns AS c ON c.object_id = t.object_id
				LEFT JOIN sys.Triggers AS Tr ON tr.name = 'trg_' + t.name + '_iu'
														 OR tr.name = 'trg_' + t.name + '_u'
			WHERE
				t.type = 'U'
				AND c.NAME = 'ModifiedDTS'
				AND tr.name IS NULL
			) AS t
			LEFT JOIN sys.Triggers AS Tr ON Tr.parent_id = t.object_id
		ORDER BY 
			t.name
	END
GO

As an example, I created three tables with the four changing tracking fields, Employee, Phone, and PhoneType. I added the trigger to Employee and Phone, but the trigger for Phone had it’s name change. After looking at the results returned, I would know that I only need to create a trigger script for the PhoneType table.

 

AdventCalendar2013Day17_Img1

 

Note: You can download this snippet from my Script Library under Resources. It’s called FindTrig.

 

Previous Post In Series: SQL Advent Calendar – Day 16 – Snippet For In-Line Table Functions

Category: Red Gate, Script, SQL Advent Calendar, T-SQL | Tags: , , ,
| December 16, 2013
2 comments

SQL Advent Calendar – Day 16 – Snippet For In-Line Table Functions

AdventCalendar16First Post in this series: SQL Advent Calendar 2013-Day 1- Placeholders in SQL Prompt Snippets

As I was hanging our family Advent Calendar up, I thought I would make one for you, my readers. I’ll be sharing my scripts from two of my favorite products as well as tips on how to create your own.

Three French Hens, I mean User Defined Functions

There are three kinds of User Defined Functions that you can create in SQL Server.

  1. Scalar Functions
  2. Multi-line Table Functions
  3. In-Line Table Functions

They all have a purpose but unfortunately the first two listed above are misunderstood and misused the most. Below are a couple of articles on the differences and the performance problems that can be caused by Scalar and Multi-line Table Functions.

I created this snippet called NewFun for SQL Prompt when I was first learning the differences between these function types, and I still use to today. Why? It saves key strokes.

USE <DatabaseName, string,$DBNAME$>
GO
--Use Shift + Ctrl + M to set Template Parameters
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'<schemaowner,string,dbo>.<sprocname,string,>') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION <schemaowner,string,dbo>.<sprocname,string,>
GO
/* ***********************************************************************************
Purpose:    [Brief Description]
Notes:      
Sample:    
            EXEC <schemaowner,string,dbo>.<sprocname,string,> [Parameters]
Author:     $USER$
Date:       $DATE$

Revision History
(Change Date)	(Author)		(Description of Change)
-----------------------------------------------------------------
************************************************************************************ */
CREATE FUNCTION <schemaowner,string,dbo>.<sprocname,string,>
(
	--Insert Parameters
)
RETURNS Table
AS 

	RETURN 
	(
		$CURSOR$
	)

GO

 

Note: You can download this snippet from my Script Library under Resources. It’s called NewFun.

Previous Post In Series: SQL Advent Calendar Day -15 – Naming Default Constraints

Next Post In Series: SQL Advent Calendar – Day 17 – Now, Which Tables Still Need a Trigger?

 

Category: Red Gate, Script, SQL Advent Calendar, T-SQL | Tags: , , , ,
| December 15, 2013
One comment

SQL Advent Calendar Day -15 – Naming Default Constraints

AdventCalendarDay15First Post in this series: SQL Advent Calendar 2013-Day 1- Placeholders in SQL Prompt Snippets

As I was hanging our family Advent Calendar up, I thought I would make one for you, my readers. I’ll be sharing my scripts from two of my favorite products as well as tips on how to create your own.

Standardization

I truly believe in having coding standards. I think the best standards that a team can have, are the ones that they are willing to adhere to. If no one agrees that commas belong on the left rather than the right, then why does your standards say they have to be on the left?

One standard that I really like to have, and is supper easy to do using macros,  is having decent names for indexes and constraints. DF_Mytable_1837489274 does absolutely nothing for me. But DF_Mytable_isActive, is helpful when looking at all the constraints on my table.

ER Studio DA has three macros to help with these naming standards. Name foreign Key Constraints, Name Primary Key Constraints, and Index Naming. When creating default constraints in ER Studio Data Architect ( ER Studio DA), I found that I was getting random numbers on my default names in SQL Server. This was especially true if I created my defaults from Domains. (Domains are templates for fields. I wrote about Domains here.)So I created an additional macro called Name Default Constraints-Selected to round out this collection of macros.

My particular standard for naming defaults is as follows: DF__. If you have different standards that can be derived from information in the field, then you can change the pattern on line 42. Take a look at lines 36 and 37 too. These two lines are necessary when working with Domains. They make it possible to edit the default name for a field that is tied to a domain.

This macro will perform two different actions. It will create a name for defaults that don’t have one, and it will ask the user if they want to change a name that does not adhere to the standards. After the macro has completed, a dialog box is displayed showing how many defaults were named and how many had their name updated.

'----------------------------------------------------------------------------
' PURPOSE:  	This macro will create and update Default Constraint names on
'				Attributes For All selected Entities. If the attribute is
'				bound, it will overwrite the bound default and name the default.
' Author:   	Mickey Stuewe
' Date:     	12/15/2013
'----------------------------------------------------------------------------
Option Explicit

Sub Main

	Dim objModel As Model
	Dim objSubModel As SubModel
	Dim objSelObject As SelectedObject
	Dim objAttrib As AttributeObj
	Dim objEntity As Entity
	Dim iCounter As Integer
	Dim iNCcounter As Integer
	Dim iunBoundCounter As Integer
	Dim strDefaultName As String

	Set objModel = DiagramManager.ActiveDiagram.ActiveModel
	Set objSubModel = objModel.ActiveSubModel

	If Not objModel.Logical Then

		' Iterate through all the selected objects in the current
		' model.
		For Each objSelObject In objSubModel.SelectedObjects
			If objSelObject.Type = 1 Then  'Entities
				Set objEntity = objModel.Entities.Item(objSelObject.ID)
				For Each objAttrib In objEntity.Attributes
					If Len(objAttrib.DeclaredDefault) > 0 Then
						'if the attribute default is bound, then unbind it so that it can be named.
						If objAttrib.DomainId > 0 Then
							'You have to override the bound default value and the default text (name).
							objAttrib.EnableOverride (1,True)
							objAttrib.EnableOverride (2,True)
							iunBoundCounter = iunBoundCounter + 1
						End If

						strDefaultName = "DF_" & objEntity.EntityName & "_" & objAttrib.AttributeName
						If Len(objAttrib.DefaultName) = 0 Then
							objAttrib.DefaultName = strDefaultName
							iCounter = iCounter + 1
						ElseIf objAttrib.DefaultName <> strDefaultName Then
							If MsgBox("The name of the Default Constraint does not match our naming standards. Would you like to replace it?" & vbCrLf & _
								vbCrLf & vbCrLf & "Attribute: " & objAttrib.AttributeName & " - Default Name: " & objAttrib.DefaultName & " - Value: " & _
								objAttrib.DeclaredDefault & vbCrLf & " Standardized Name: " & strDefaultName, _
								vbYesNo + vbQuestion, "Stadardize Name?") = vbYes Then
									objAttrib.DefaultName = strDefaultName
									iCounter = iCounter + 1
							End If
						End If
					Else
						iNCcounter = iNCcounter + 1
					End If
				Next objAttrib
			End If
		Next objSelObject

		MsgBox iunBoundCounter & " had their defaults unbound so that they could be named." & vbCrLf & vbCrLf & _
				iCounter & " Default Constraints had their name created or updated." & vbCrLf & vbCrLf & _
				iNCcounter & " Attributes were not modified.", vbOkOnly + vbInformation, "Default Constraint Name(s) Created or Updated"

	Else
		MsgBox "You are in a Logical model. Move to a Physical model to name of Default Constraints.", vbOkOnly + vbCritical, "Need Physical Model"
	End If
End Sub

 

Note: You can download this macro from my Script Library under Resources. It’s called Name Default Constraints – Selected.

 

Previous Post In Series: SQL Advent Calendar – Day 14 – Working With Forms in a Macro in ER Studio Data Architect

Next Post In Series: SQL Advent Calendar – Day 16 – Snippet For In-Line Table Functions

Category: Database Modeling, ER/Studio Data Architect, Macros, Script, SQL Advent Calendar, T-SQL | Tags: , , , ,
« Older Entries   Recent Entries »
%d bloggers like this: