C-R-U-D The Basics

C-R-U-D The Basics

What is CRUD? Well, there are a lot of things I can think of when I use the term CRUD; however within the wonderful world of technology CRUD is an acronym that surprisingly enough, when I polled, didn’t really give a straight answer. That really surprised me; so with that said I will walk you through the basics of the concept.

C-reate

The letter “c” stands for create; more specifically inserting some form of data by various methods or means into a repository or holding take. I want to take this down to its simplest form which the below example will depict:

 

Insert

 

Inserting data into the PastHist table is as simple as the T-SQL query; but don’t just limit the thought of creation by this simplest form. You could have data being created through front end apps, SSIS packages, or many other methods.

 

R-ead

Depends on who you ask or talk to. The letter “r” can stand for read or retrieve. A simple retrieve statement can be a Select as below:

 

Select

 

SelectResults

 

What is this he didn’t use a NOLOCK – don’t worry I can save that for another blog post but as you can see in its simplest form retrieving the data out of the table you just inserted into is not that cumbersome.

 

U-pdate

The letter “u” can mean update or modify if you will. Let’s see how easy it is to do a simple update statement below:

Let’s update the name for our record to be John where the id = 1

 

Update

 

UpdateResults

 

As you can see the data has been updated and John is now our data set in the name field column.

 

D-elete

The letter “d” stands for delete or destroy. Below is a simple script to delete the data out of the table:

 

Delete

 

There are some methods I could have used to remove the data; such as Truncate Table but this is not the time or place for me to distinguish between the two. I chose the delete method on the search criteria of id = 1

Results after the deletion:

 

DeleteResults

 

Conclusion

This is breaking down C-R-U-D into its simplest form. Please do not limit your thinking to these very basic simple queries. Expound on it and your thought process on what all C-R-U-D entails. As with anything you find on the internet DO NOT take queries and just execute them blindly on any environment. Doing so makes you assume the risk.

What else are others saying?

I tell you what; check out what my other colleagues have to say on something they learned recently around C-R-U-D:

 

CollaborateImageOn a SQL Collaboration Quest

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.

2 Comments

Passwords – A T-SQL Tuesday Topic

LateBeing engrossed with the daily tasks at hand I completely missed this month’s T-SQL Tuesday is being brought to you by Sebastian Meine Blog | Twitter.

Although the time frame has passed I would be amiss if I didn’t continue on my journey of joining in these block parties; with that said I’m going to write what I “would” have contributed. This month’s topic is intriguing in that it can cover a wide array of discussion – Passwords.

When I think of passwords I think of etiquette. I cannot tell you how many times I have been on calls, meetings, emails, and the list could go on of scenarios that relate to passwords where users just don’t think or take into consideration the impact of their actions. To me the last four words are the key, “impact of their (our) actions“.

Password Etiquette

  • Conference Calls – how many times have you been on a production call with numerous individuals and hear someone say, “Okay here is the user name and password?” If you have then you are not the only one. Credentials should be kept out of the hands of unnecessary individuals.
  • Open Text Passwords in tables – check into encrypting those; protect yourself before you realize breaches have occurred and you are left holding the bag.
  • Email – transmitting password information via email; not a big fan of. This kind of relates back to the conference call section; who all is on the email? Are you sending it to Project Managers and the like? Probably not the best choice to make.
  • Backups sent offsite – do you have any backups going off site? Is any pertinent credentials contained in the dB and if so are your backups being encrypted before shipping them off?
  • Length – Look at the length of the passwords you are creating; how strong is the password you are making?
  • Sharing – don’t do it; simple enough.

All the above reflects, what I deem, good etiquette. That barely scratches the surface. You have to take into consideration many other factors one of them being a policy.  Small, big, medium – whatever kind of shop you are in define out what the best practice is for your shop and then adhere to it. A good reference could be found on Technet Best Practices

Lastly, if you feel as though a password has been compromised be proactive and take the necessary steps to change it. Don’t wait for something to happen; you be the game changer.

Get your defense model in place and let the good times roll.

,

Leave a comment

SQL Prompt 6.4 – Get It

SQLPrompt

This guy looks just like me a few weeks ago only one difference…..he has hair!!! Okay seriously though, I felt just like this guy not long ago and the reason for this was due to the fact I have become accustomed to specific tools and utilities that I utilize on a daily basis. Is this a good thing, eh, maybe or maybe not but end of the day I like to be efficient, dynamically nimble (if that is an expression), and in control of what my work entails daily.

Now, leading up to this extraordinary blog post you have to understand something. I’m in a RedGate shop pampered with some very nice utilities at my disposal. One such utility is their SQL Prompt utility.

One of the nice features I like about the utility is the recover tab within SSMS that shows me past history of tabs I had open within SQL. Can I tell you how many times this has come in handy? If that doesn’t meet your fancy then how about the code snippets that you can save and reuse later, or maybe the nice IntelliSense it provides while coding.

This leads me into why I looked like our Data Professional up above…..I recently was without my SQL Prompt and you thought I’d been taken back to the stone ages. I went to type out some code and low and behold nothing, I got NOTHING. No tables popping up; no columns to review…..nothing. After the sheer shock and awe subsided I knew I was in for a long day. Needless to say it didn’t take me long to figure out what went wrong with my prompt and how to get it back.

What’s this mean for you guys who are reading this? Glad you asked.

Red Gate’s new version is out so go check it out. Some of the new enhancements are noted below:

  • Execute current statement
  • Insert semicolons
  • Code highlighting
  • Use object definition case
  • Rename variables and aliases
  • Suggestions improvements
  • Qualify object names improvements
  • Remove square brackets
  • Nullability of columns
  • Choose environments to install to
  • New formatting options
  • New tab history options
  • Improved loading dialog box
  • Support for restricted accounts
  • Various usability improvements
  • Many bug fixes

So download the free trial and test drive it; what do you have to lose? Maybe you’ll like it and maybe you won’t but don’t wait and let time go by when you could be becoming more efficient in your everyday work.

If you use the product already drop me a line; if you don’t use the product then tell me why not.

,

Leave a comment

What I Wish I Had Known Sooner…as a DBA

AttitudeYesterday I took a minute and to review some posts by some Data Professionals and in doing so I came across and article by Erin Stellato. She had been tagged by Mike Walsh who posted an article regarding 4 Attitudes I Wish I Had Earlier as a DBA

At the very end of Erin’s post she mentions “I’m not tagging anyone in this post by name, but if you’re thinking “I wish she had tagged me” then you’ve just been tagged.”

This spawned some thinking yesterday and last night; looking back I see growth, mistakes, resolutions, solutions, and much more that has brought me where I am today.  A lot has transpired; and I still have a lot more left to learn and understand.

Balance 

I have been told throughout the years I do not have an off switch. My mind is constantly running on things that may have occurred throughout the day, solutions to issues that I or someone has experienced recently, answers to forum questions, multiple projects running at the same time, and the list goes on. With that said you have to have some form of balance; I’ve seen many people burn out of the years and it is something that I’ve had to continually work on throughout my own career. Am I there yet – nope; but that’s okay. I’ve come along way in that area and will continue to work on it.

Flexibility

I’m the DBA what I say goes; it’s my way or the highway. Yeah that syndrome will end catastrophically and fast. Being a DBA (or as I like to say Data Professional) you encounter and work with many people from all faucets of business. It is almost a fine art of communication (which can be a point in and of itself), learning how to work together with other teams. Does this mean that you should not stand your ground for your beliefs – nope. What it does mean is that you will always see a gap; there has always been one. The network guys will blame the DBAs, the DBAs will blame the developers and network guys, the developers will blame the DBAs. It’s a constant endless loop.

End of the day, all the groups under the blanket of a company are in the same fight and on the same team regardless of if you are in networking, DBA land, development, phone systems – whatever the case is. We have to find a way to work together for the common good of the company.

Wrote a piece awhile back on bridging that gap

Provide Leadership Through Service

Last year I came across and article that Grant Fritchey wrote around Providing Leadership Through Service. This is something that has really resonated with me more so now to which I wish I would have learned earlier on in my career. Whether you are a contractor, own your own business, or work in a shop full time view your efforts as providing a service to the company you are with.  It will change how you view and reflect on your position; we should be humbled and honored to work within an environment we’ve been handed, but at the same time be bold and provide that leadership that companies so richly desire and seek

Learn To Say “NO”

No is not a bad word; although at times we think it is. Early on in my career I wanted to fix and do everything I could to help people. Don’t get me wrong; I still enjoy helping people. If you don’t learn to say no you will soon spread yourself to thin. This is a constant struggle that I still endure sometimes as I want to help everyone I meet with questions, projects, etc. The growth that I have had over the years has taught me to do the helping more methodical. At times I do have to say no, and that’s okay.

Conclusion

Well there you have it; not very fancy but is from the heart and from a data professional who realizes he will never be perfect but will continue to grow on this SQL journey.

I’m not tagging anyone but offering the same avenue that I was given. If you’d like to blog about what Mike has presented then by all means; give it a go.

Thanks Mike for getting this started; brought back some fond memories for myself.

,

2 Comments

ISPACs – Got To Love Em

Being a DBA and working with various teams, I have become accustomed to deploying SSIS packages. I’ll even go back further and, dare I say, DTS packages (I hope everyone did not just fall on the floor while reading that). Now-a-days this Database Professional does more deploying packages than developing them; with that said I was pleased when 2012 came along.

When the newer version of SSIS was released it offered several new features, one of those features was building a deployment model thus producing an ISPAC file. I remember first coming across this and thinking, “Wow, this is all packaged up”. While I won’t go into the specifics of how to build the deployment I will show you the step by step process of deploying the ISPAC file.

Step 1

Locate the ISPAC file. Once located double click on the ISPAC file to gain entry into the deployment wizard.

Ispac

 

Step 2

What kind of Integration Services project do you want to deploy? Since we are dealing with an ISPAC file on our local directory we will select the source path we found from Step 1. Simply browse to the path location and click ok.

Source

 

Step 3

So you have your ISPAC file selected and you want to deploy it; where does it go? You have the capability to supply any Server Name for rapid deployment. The path reflects the SSISDB catalog which is required for SSIS 2012 packages to be deployed. If your SSIS package is already on the server you can simply choose to overwrite the existing file at deployment time.

Destination

 

Step 4

After you select the destination path; it is then time for review. The review section will provide you with a great overview of the Source path of the file being deployed and the destination location.

Review

Step 5

Once verified and the deployment button is hit we are off to the races. The ability to have quick insight into the deployment and the methodology to save the report for future use is stellar. If your deployment did fail; at this step you would be able to dive into the error in the result pain on the right hand side.

Deploy

 

Seems simple enough? The deployment method in SSIS 2012 has proved beneficial for myself and is a welcomed aspect. Earlier this year I completed a post on the utilization of PowerShell and how to deploy 2012 SSIS packages and that can be found here.

I tell you what; check out what my other colleagues have to say on something they learned recently around SSIS:

 

CollaborateImageOn a SQL Collaboration Quest

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.

,

2 Comments

And Just Like That….Three Years

Time

Time is a constant. Anything and everything requires time it seems, and with that said I received a reminder today that three years have passed since I first started this adventure called blogging.

 

With this blog you will not find perfection, instead you will find a Data Professional who has grown more over the last three years of his career than he did the first ten. Is it by sheer circumstance? No, I don’t believe so. Don’t get me wrong; learning my first ten years was ongoing; I just wasn’t prepared for what the last three years had in store for me. Looking back I see phenomenal people who took a chance on me both from my career standpoint and from a community standpoint. People who pushed me never to give up when things didn’t necessarily go the way I would have liked for them to; or the countless hours of advice I would seek out from people who graciously pointed me in the right direction.

 

Attending PASS Summit 2011 was the career changer for me. Inspiration and collaboration ensued when I arrived back home and it has been an enjoyable ride. The blog has morphed from infancy (The SQL Corner) to what it is today (The SQL Professor). With steady growth over the past three years I’m pleased with the reception it has gotten and look forward to the future and what it holds.

 

I used to get discouraged at the quantity I was producing; I quickly realized that it isn’t quantity it is quality and that content is key and cannot be substituted. One of my favorite blog posts wasn’t even a technical post; no it was one of influential people that have helped me in my career and more so in the past three years. Investing time in others, writing, and continual learning will be my focus this upcoming year; focusing a lot more on the quality of content.

 

It’s an honor and privilege to blog about my SQL adventures as a Data Professional. I won’t always be perfect, but as with anything you’ll get a Data Professional who will give a 110% and try to provide solutions to everyday issues that are incurred in work life situations. I recently re-read an article by Grant Fritchey (Blog|Twitter) – Leadership Through Service; this is something that has resonated with me and is a thought that is a good basis for a strong foundation. I want to view this upcoming year as such “Leadership Through Service”

 

 I would be amiss if I didn’t thank Chris Shaw (Blog|Twitter) and John Sansom (Blog|Twitter) for investing time in me and pushing me to continue to grow and get better. I could easily throw an additional 10-12 people in there  but the two I mentioned have stood by me. They’ve seen me grow, stumble, fall, picked me up, and encouraged me along my journey. Is that not what our Community is about? If we continue to reach one person and that person reaches one person the SQL Community will continue to grow and thrive.

 

Time is a constant. Time is passing by. What will you make of your time?

,

Leave a comment

T-SQL Tuesday #57 – SQL Family and Community

SQL-Tuesday.jpgMaybe it is just me, but these monthly block parties seem to be coming around quicker and quicker as time continues to fly by. My good friend Jeffrey Verheul (Blog | @DefJef) is getting the opportunity to host this month and he wants to know about “SQL Family and the Community”.

If you have been around SQL long enough you will find out that the terms SQL Family and Community are mentioned often. For me, and my experiences, it is a close knit group of data professionals that are willing to share their life experiences, everyday issues, problems solving skills, idea bouncing, and much more. I have been involved with SQL for over 14 years now and only the last 4 years have I been active in the community; not because I didn’t want to, but I was not privy to the magnitude of how far the SQL Family and Community reach is.

It’s no secret for those who know me. I’m sports oriented; having played baseball, football, soccer, and basketball my whole life and then college basketball I’ve been around team oriented methodologies my whole life. That background has it’s pros and cons, but the disciplines and insights I garnered from those experiences has fit nicely with being a Data Professional.

Having those days behind me for the most part (yes I am an avid runner still and do partake in a good game of basketball at times) my team now is the SQL Community.

Pre-Game

Preparing for a big game you have to look at the whole picture. Countless hours of practice, repetition, running play after play goes into a season not to mention conditioning. The camaraderie built during those times with your teammates enables you to trust them on the court. Guess what, being a data professional you have to be all in. To me that means continuous work day in and day out to help hone your skill set, and part of that work has been working with the SQL Community and Family.

The Game

G-A-M-E-T-I-M-E

The day of the game is here and it’s go time. You look at your teammates (SQL Community) and you are in a huddle right before tip off. The opponent is huge, a foot taller than your tallest guy, yet you are determined. This is what you’ve prepared for. Will you rise to the challenge?

  • You will get assists in your career where you were stumped and didn’t have a clue; then someone from the SQL Community will provide you with that spark that enables you to get over the hump.
  • You will grab that rebound when someone, other than you is stumped, and pick that person up when they need help.
  • You will block that shot when a someone is wanting SA rights to your server.

When your number is called to come into the game (becoming active in the SQL Community) will you answer the call? Will you come into the game with an attitude of we got this? Have you prepared?

Outcome of Game

A team is just that; a team. I remember vividly to a point in time when we were in the off season, but conditioning. We had already completed multiple wind sprints and were wrapping up running intervals. Getting to the finish line we looked back and one of our teammates was struggling. He was to the point of running then stopping; did we leave him out there? A resounding NO – the ones that finished ran back out and surrounded our teammate. He was part of our team and no way were we going to let him struggle alone, and hopefully; we could give him the confidence to finish. That to me epitomizes the SQL family and community. Sure we are a finicky bunch at times, frustration occurs, and we don’t always get along. That happens on every team; however at the end of the day we have each others back. We win as a team and lose as a team.

Summary

If you are not active in the SQL Community then you are missing out. It’s time; the horn has sounded and the game is on. Time is ticking down; will you get in the game? A saying that a coach told me a long time ago has stuck with me ~ “Somewhere someone is practicing getting better; what will you be doing?” Let’s make this Community the best we can.

What is T-SQL Tuesday

T-SQL Tuesday is a recurring blog party, that was started by Adam Machanic (Blog | @AdamMachanic). Each month a blog will host the party, and everyone that want’s to can write a blog about a specific subject. If you are interested and have been blogging for a bit drop Adam a line.

,

1 Comment

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: