Tag Archives: TSQL2sDay

T-SQL Tuesday #41 Presenting and Loving It

SQL TuesdayThis months T-SQL Tuesday party is being hosted by Bob Pusateri (a.k.a. SQL BOB) (B | T). Bob’s topic for this month is intriguing to me as I wrestled with this for some time first starting out – Presenting!

I think for me personally, I really started to think about presenting after returning from my first Pass Summit (2011). Sure, I’ve given speeches in college, and talked openly in discussions but something drew me to speaking on a topic that I enjoy working with everyday ~ SQL.

  • First Presentation – my first presentation went surprisingly well. I had been in front of large crowds on many occasions so talking wasn’t my initial problem; my initial problem was knowing that the attendees came to get info and to learn, and I needed to be sure I was prepared to give that to them.
  • Investment – I started to look at speaking as investing time in others. I cannot begin to tell you how beneficial and fortunate I have been to study, learn, and tap into some of the best minds in the SQL industry. They have taken the time to invest in me to further my knowledge and career; I should do the same and help others along the way if I can. You see that is what is so great about the SQL Community; the community is there and always helps.

I know there are other people out there that are hesitant to take the plunge in speaking. I have personally experienced some great benefits to speaking. For those that are hesitant these might be worth considering.

  • Gain knowledge – when you are presenting on a topic it drives you to gain more knowledge about the topic. Knowing the ins and outs and going through test results and scenarios.
  • Networking – meeting new faces in the community; getting involved with peers
  • SQL Saturday’s – attending new SQL Saturday’s in different locations
  • Giving back – as I already stated giving back to the community that has helped me in so many ways over the years

Speaking is like anything else you set your mind to. Practice and you will get better; if you haven’t taken the plunge yet I encourage you to try it once; see how you like it; you might find something out about yourself.

Want to host a party?

So you want to know if you are eligible to host a T-SQL Tuesday party? Glad you asked, you can contact Adam Machanic (B | T) who is the founder of T-SQL Tuesday.

T-SQL Tuesday #040: File and Filegroup Wisdom

SQL Tuesday

It’s that time again for the T-SQL Tuesday party! This party was created by none other than Adam Machanic (Twitter). If you are interested in hosting a party at some point this year give him a shout; you need to have participated in two T-SQL Tuesdays along the way and also maintain your own blog for at least 6 months.

Now that we have what the party is all about let’s get into what this month’s party is centered around Filegroups and his hosted by Jen McCown / MidnightDBA

My focus today is garnered toward indexes on filegroups and what they can do to your index strategy. I’m a big fan of having strategies when tackling issues, problems, or even believe it or not from the beginning of a project. Placing indexes on filegroups carefully can improve query performance (at the same time I want to note that indexes can also hurt performance in some situations so thorough testing needs to be taken into consideration).

Back from my 2008 R2 studies, if my memory serves me correctly, indexes are stored in the same filegroup by default; a non-partitioned clustered index and the associated table always reside in the same filegroup however you can do one of three things:

  1. You can partition both clustered and non clustered indexes to span multiple filegroups
  2. Create non clustered indexes on a filegroup
  3. Move a table from one filegroup to another

You can achieve performance gains by created non clustered indexes on a different filegroup if the filegroups are using different physical drives. The data and index information can be read in parallel by the multiple disk heads when the physical drives are on their own controllers.

One cannot foresee the access that will transpire or when it will happen, a better decision to spread your tables and indexes across all file groups might be of help. This would guarantee all disks are being used and accessed because all data and indexes would be spread evenly across all disks.

To bring this all back together you can think of a filegroup in its simplest of forms. Every database that you create has at least a data file and a log file, and every database has a primary filegroup. The filegroup contains the primary data file and any secondary files that are associated with it. One filegroup can contain multiple mdf/ndf files.

In the end I have seen significant gains with indexes being placed on specific filegroups, but as I stated before it is good to test all this out. Set up some scenarios on your test server and start doing some test cases to prove different theories and ideologies. One thing to remember as well is not every case is the same; ensure that the decisions you are making is good for what you are working on; never take a suggestion and drop it into a production environment. Prove the statement to be true or false no matter who it comes from.

Well, that’s a wrap for today’s party. Until next month…….

T-SQL Tuesday #38 – Standing Firm

SQL TuesdayIt seems as though months are coming and going at an alarming rate but nonetheless I can say I am enjoying the ride. Today’s T-SQL Tuesday party is hosted by Jason Brimhall (B | T) T-SQL Tuesday #38 – Standing Firm.

Jason has provided a three word topic that entails the words resolve, resolute, and resolution…given these three words one must come up with a story based on one of them.

I believe I have chosen mine and have taken a somewhat different spin on it and how I applied it to T-SQL and my DBA career.

RESOLVE

The word as some various meanings but pertaining the expression I would like to use; I will use it literally as a noun “A determination or decision; a fixed purpose”.

Many of us who are DBA’s (which I am proud to be one) have all taken various routes, various avenues if you will, along the way to get where we are at. Growing up I had a passion and desire to play the game of basketball. I would train very hard; my days would consist of waking up at 4:00 a.m. prior to school, go to the gym where I would meet my assistant coach, shoot 700 jumpers and 100 free throws, shower and go to school, practice after school with team, and then repeat the same process daily. My summers consisted of going to various camps in the United States where coaches from all over would attend with my hope of one day gaining a scholarship. I am 6′ foot tall and not overly fast I was always told that I was too small to play college and too slow. The day finally came for me to graduate and guess what ~ I made it to college to play the game I had worked so hard at for so long.

After my career was over now what? I started out in Physical Therapy but a friend of mine showed me a program he had developed. Once I saw the inner workings of what he was doing it I was intrigued and went down the path of becoming a developer to which led me into the DBA world. You know what; along those same lines I got the same rhetoric from people….”You can’t do that; you don’t know enough”; “You can’t do that; stick with sports its to long of a road for you”. Resolvea determination or decision; a fixed purpose. You see I had a fixed purpose when playing basketball. I knew what I wanted and where I wanted  to go. The same can be said with becoming a DBA or if you have a desire to learn something in T-SQL; if you  have a desire to learn something new in SQL or track down a long running query and improve it; show some resolve – have a fixed purpose. Don’t ever let anyone stand in front of your dreams to become something you want to be. It takes hard work and dedication and a drive to become not just a good DBA but an exceptional DBA.

In the end you will build a strong character and life values. What I learned on the court has carried over to my work habits. I love being part of a team, but I also enjoy when the game is on the line and SQL tasks are at hand and it is fourth quarter and systems are down I want to be the one to take the last shot.

I said all that to say this, if you are faced with “potholes” or “adversity” along your journey from a technical standpoint show some resolve. The community as a whole is here to help and don’t lose sight of your endpoint; granted that endpoint may change many times but remain purpose driven and adapt when need be.

Hosting a T-SQLTuesday Party

If you are interested in hosting a T-SQL Tuesday Party contact the founder Adam Machanic (B | T). It is a great way to get involved in the community and provide an avenue to share your ideas.

 

T-SQL Tuesday #37: Invite To Join Me In a Month of Joins

TSQL2sdayThis month’s T-SQL Tuesday is being hosted by Sebastian Meine (blog | twitter) and his topic is anything and everything about “joins”. He’s dedicted the whole month of December to this topic.

Having had the opportunity to do extensive SQL work as a developer when first starting out I have been accustomed to joins for some time. Joins are one of the basic constructions of SQL and Databases as such – they combine records from two or more database tables into one row source. Depending on join type and join restrictions returned row count can be from 0 till all possible combinations of involved tables. Databases are built to make joins as efficient as possible. It means almost always joining data in database is more efficient than doing that somewhere else. It also means one has to know the power and possibilities of joins to fully exploit their strength.

Types of Joins

These are the most common joins that I have ran into:

  • INNER JOIN – only rows satisfying selection criteria from both joined tables are selected.
  • LEFT OUTER JOIN – rows satisfying selection criteria from both joined tables are selected as well as all remaining rows from left joined table are being kept along with Nulls instead of actual right joined table values.
  • RIGHT OUTER JOIN – rows satisfying selection criteria from both joined tables are selected as well as all remaining rows from right joined table are being kept along with Nulls instead of actual left joined table values.
  • FULL OUTER JOIN – rows satisfying selection criteria from both joined tables are selected as well as all remaining rows both from left joined table and right joined table are being kept along with Nulls instead of values from other table.
  • CROSS JOIN – A cross join performs a cartesian product on the tuples of the two sets.

Usually cross joins are used quite rarely; some of the scenarios could be as follows:

  • Possibility to generate high amount of rows. As we can see from relatively small tables there is possibility to get quite monstrous numbers.
  • Find out all possible row combinations of some tables. Mostly this is useful for reports where one needs to generate all combinations for example all nationalities x genders for persons.
  • To join a table with just one row. Most often used to get some configuration parameters.

ALIAS JOINING

Some other questions I’ve been asked over the years is proper aliasing. I’ve had people use the a,b,c, method and a short table def. for example:

Example 1

Select *

from table 1 a (nolock)

inner join table 2 b on a.id = b.id

Example 2

Select *

from customer cust (nolock)

inner join address adr on cust.id = adr.id

The Performance

I’ve seen queries astronomicaly long with nasty subselects, joins, union alls, etc. Alot of times in my query tuning and looking at poorly designed queries, stored procedures, udf’s etc it is quickly seeable whether joins are being used correctly or incorrectly. When one takes the proper time to review the database and the schema and then build the queries with powerful joins performance can be quite optimal.

Future T-SQL Tuesdays

I urge you to take the time to check out Sebastians site and hey while I’m at it if you feel like you want to host a T-SQL Tuesday give Adam Machanic a shout. I can tell you from experience that I’m glad I did. Getting involved is a great way to get going with the SQL Community and the SQL family.

Thanks Sebastian for hosting this month!

The Roundup – #TSQL2sday

Another T-SQL Tuesday has come and gone and I’ve got to admit that all the responses that were received are pretty awesome and even had a few responses from first timers that attended the PASS Summit 2012. While all the posts were great, I had one that blew me away and is exactly what the Community is about. Kendal Van Dyke allowed a post to be tied to his blog by Andy Levy who currently didn’t have a blog and is fairly new to SQL. If you get a chance check it out.

One theme that I read over and over again is that we are one huge family and we have each others backs. The Community has a whole goes out of their way to assist our fellow members. Below is the roundup from everyone who chimed in. Thanks everyone for their participation!

Remember if you are interested in hosting please contact Adam Machanic (B|T)

POSTS

Jason BrimHall – SQL Family – After Summit 2012 | SQL RNNR

Rob Farley – Rob Farley : SQL Community – stronger than ever

Aaron Bertrand – T-SQL Tuesday : Reflections on the PASS Summit and our community

Robert Davis – T-SQL Tuesday #36 – SQL Community | SQLSoldier

Chris Shaw – Esprit de Corps T-SQL Tuesday #36 « Chris Shaw’s Weblog

Valentino Vranken – BI: Beer Intelligence? · PASS Summit 2012: Impressions Of A First Timer

Steve Jones – T-SQL Tuesday #36 – What Does Community Mean? « Voice of the DBA

Oliver Asmus – T-SQL Tuesday #36 – What Does the SQL Community Mean To You? | OliverAsmus.com

Edwin Sarmiento – PASS Summit and The Value Of Building A Community

Wayne Sheffield – T-SQL Tuesday #36 – Community

Mike Fal – T-SQL Tuesday #36 (#tsql2sday) – Coolness factor | Art of the DBA

Robert Pearl – TSQL Tuesday – What SQL Community Means To Me

Tamera Clark – SQL Community and Family

Kendal Van Dyke (On Behalf of Andy Levy) – T-SQL Tuesday #36 – SQL Community (Guest Post)

Kerry Tyler – T-SQL Tuesday #36: What Does the SQL Community Mean to You (Me)?

Argenis Fernandez – T-SQL Tuesday #36 (#tsql2sday)– Post-PASS Summit Depression

SQL Asylum – T-SQL Tuesday #36 SQL Community how you can get involved

Dev Jef: – T-SQL TuesDay #36 – What does the community mean to you? « SQL from the Trenches

Mickey Stuewe – T-SQL Tuesday #36 – What Community Means to a Newbie « Mickey’s T-SQL Ponderings

David Maxwell – T-SQL Tuesday #36: What Community Means To Me | David Maxwell on SQL Server

Stacia Misner – Data Inspirations » We are (SQL) Family! (T-SQL Tuesday #36)

Jenny Salvo – T-SQL Tuesday: What Does the SQL Community Mean to You? | Salvo(z)