Browsing articles tagged with " SQL Server"

The Key to Keys – SQL Sat Huntington Beach

Apr 2, 2016   //   by Karen Lopez   //    //  No Comments

SQL Saturday HB logo

 

So many of us have learned database design approaches from working with one database or data technology. We may have used only one data modeling or development tool. That means our vocabularies around identifiers and keys tend to be product specific. Do you know the difference between a unique index and a unique key? What about the difference between RI, FK and AK?

These concepts span data activities and it’s important that your team understand each other and where they, their tools and approaches need to support these features.

We’ll look at the generic and proprietary terms for these concepts, as well as where they fit in the database design process. We’ll also look at implementation options in SQL Server and other DBMSs.

The Key to Keys: NTSSUG 17 March

Mar 17, 2016   //   by Karen Lopez   //    //  No Comments

Level: Intermediate

So many of us have learned database design approaches from working with one database or data technology. We may have used only one data modeling or development tool. That means our vocabularies around identifiers and keys tend to be product specific. Do you know the difference between a unique index and a unique key? What about the difference between RI, FK and AK? These concepts span data activities and it’s important that your team understand each other and where they, their tools and approaches need to support these features. We’ll look at the generic and proprietary terms for these concepts, as well as where they fit in the database design process. We’ll also look at implementation options in SQL Server and other DBMSs.

I’ll be speaking about keys: primary keys, surrogate keys, clustered keys, GUIDs, SEQUENCEs, alternate keys…well, there’s a lot to cover about such a simple topic.  The reason I put this presentation together is I see a lot of confusion about these topics. Some of it’s about terminology (“I can’t find anything about alternate keys in SQL Server…what the heck is that, anyway”), some of it is misunderstandings (“what do you mean IDENTITIES aren’t unique! of course they are…they are primary keys!”), some of it is just new (“Why the heck would anyone want to use a SEQUENCE?”).

Datatypes or Data Types?

Nov 23, 2015   //   by Karen Lopez   //   Blog, Data Modeling, DLBlog, Fun  //  No Comments

I conducted a Twitter poll last week about how to spell DATATYPE (or is it DATA TYPEs?).  Many compound words start out as two separate words, then get hyphenated, then concatenated to a new word.  We saw this with:

data base –> data-base –> database

I keep seeing data types spelled both ways (and never as data-type).

Ted Codd used DATA TYPE in his 12 Rules for a Relational Database Product.

Embarcadero ER/Studio and CA ERwin Data Modeler use DATATYPE in their products and occasionally use DATA TYPE in their help or documentation.

Oracle uses both spellings in their documentation.  Microsoft sticks heavily to DATA TYPE.

Twitter polls last for 24 hours and not all clients can see or vote on them. So consider this more of a fun question on social media.

image

How do you spell this concept? Are there other words you find with a variety of spellings?

Holiday Pairings with DBMSs & Datastores – Part I

Nov 26, 2014   //   by Karen Lopez   //   Blog, Data, Database, DLBlog, Fun, NoSQL, Parody, WTF  //  1 Comment

It’s the Wednesday before Thanksgiving weekend in the US and the rest of the world is thinking about how much MORE WORK we are going to accomplish while our American teammates are stuffing themselves with Tofurky and mashed potatoes….what?…hold on…. Oh, right. Turkey and a box of wine that someone’s sister got for a great deal at a Piggly Wiggly last June and has been saving it in the trunk of her car for her boozy in-laws (that would be YOU!).

This got me thinking about what sorts of holiday beverages the rest of us should be pairing with our database management systems and datastores. I have to say datastores because some pretentious people data professionals insist that database means relational database. Of course, those of us old experienced enough know that there were databases long before Ted Codd had that wild road trip with his friends  Raoul and Dr. Gonzo and discovered  developed the relational model for shared data banks.

So while the Yanks are dipping into their third helping of candied yams with marshmallows on top, we in the rest of the world can be productive. But since we aren’t in the US no one will think us odd for supplementing  our work with appropriate hydration products. To help you out, I’m recommending holiday beverage pairings based on what data technology you are touching over the next four days. Four blissful days of the most productive work week of the year.

Comma Separated Values Files (CSV files)

Moonshine in Ball Jar. Chris Herbert.

CSVs were the original data hipster, before XML became cool. It was text. In a file. On a disk, with magnets, that spun around. Your data might be comma-delimited or length delimited…it doesn’t really matter.  It’s text and anything you want to do with it you have to do by hand. So obviously, these data formats pair well with moonshine, the homemade booze of choice in most of the US. This data could also pair well with bathtub gin, but only if the bathtub is clean enough. Typically not an option, therefore, for DBAs and most of the rest of us in IT.

Excel

For Excel I’m going to split the pairings based on vintage year of release.  Excel is the perfect candidate. Let’s not kid each other here: business are run on spreadsheets, not databases (Ed. – I don’t think you can say that Me:  It’s my blog and besides that I’m drinking, I mean sampling, all these pairings. In other words, I may or may not be drunk already).

That means that Excel is everyone’s real data storage solution of choice, so it pairs well with the common denominator beer of choice, Budweiser. It’s cheap, there’s always some in the fridge, and it works, for the most part. So for Microsoft Excel 2003 and earlier, this Bud’s for you. The working man.  The typing women.  Yes, you.

Excel + Power BI

image

But with the release of Excel 2013 and self-serve BI via the Power…wait, hold on…. let me look up to see if it has a space or not…okay, there’s a space today….Power BI, Excel gets a bit more kick. Power Query, Power View, Power Pivot, Power Stuff, really. We’ll need the picante version of Budweiser Chelada. A chelada is beer plus Clamato. Clamato is tomato juice mixed with clam juice. The spicier version is Chelada Picante, so Budweiser + Tomato Juice + Clam Juice + salt + lime + chili peppers. Phew! I need an Excel slice and bad pie chart to show this. But I’m too busy drinking. This isn’t to say that Excel plus Power BI isn’t a great BI tool framework, but if it’s your datastore, you’ll need more booze in the morning. And like its primo the Bloody Mary, a Chelada makes for a great 7 AM meeting drink.

Oracle

Glenfiddich 50

Ah. Now we are on to the expensive stuff. Larry has to fund his yachts some way (Yes, that joke is getting old. But I’m drinking, so it’s okay. I mean sampling, so it’s okay.) Again, we need to factor in various versions of the Oracle suite (Presidential Suite, I should say) of products.

 

Exadata

For Exadata, I recommend this lovely 50 year old scotch. At about $30,000 a bottle, this is the equivalent of about one licensing core with no add-ons. It’s older than Oracle itself, but 50 is the new 25. So I hear.

It’s perfect for you and your sales guy to share. In the Presidential Suite. Of a club. but the price point seems spot on for the work you’ll be doing and the rate you should be charging.  You are charging Oracle rates, aren’t you?

Oracle Enterprise Edition

For Oracle Enterprise Edition, nothing beats a XO Cognac. With hints of rose petals, tobacco and leather, it’s going to feel just like it did when you signed that license agreement on your date with the Oracle sales guy. (Ed. – I don’t think you can say that. Me: I just did. And I’m drinking. You, editor, have never had to date an Oracle salespro as part of your assigned project duties and these pairing samples aren’t going to drink themselves.) The good news is that this comes with fine Baccarat Crystal glasses (aff link). They are free, until you use them. After you use them, of course you will have to pay for support on the glasses, at 23% of list price per annum.

Oracle Standard Edition

hennessy0.jpg

With Oracle Standard Edition, I recommend a  pint of Hennessy with a silicone flask cover to ensure you have a good grip on your booze and yourself. Both the Hennessy and Standard Edition fit “affordable and full featured”, if by full featured they mean “keeps you warm in the data centre”. If you are using Standard Edition, you can’t afford those grown up bottles yet because they aren’t paying you Larry rates. Plus the silicone will remind you of… (Ed.- STOP.  Right there.  Just STOP.  Me: Okay!!! {shhh…I may refactor this when my editor is sleeping off his turkey.})

Sybase

File:Honey-Fruit-Mead-Brewing.jpg

Oh, the father of SQL Server, sort of.  At least they have a common ancestor in the evolution of databases.  Do people use this DBMS anymore?  I’m so old experienced that I can remember when Sybase was called SQL Server 6.5 (Ed. – I don’t think that’s true.  Me: Shhh..don’t yell so loud. I’m sampling here.  And I’ve lost my Glog.  Mead…whatever.)

If you are supporting this DBMSs, I recommend mead. Because your Sybase DBA has been around so long it was probably his first drink. Don’t get me wrong: fruit, alcohol, honey – it’s all good stuff.   If you use Sybase as a special one-off DBMS to support a vendor package, I recommend a can of Redbull and a shot of whatever you find under the sink in Executive washroom.  Even if it’s a cleaning product. Maybe add a teaspoon of honey.

DB2

File:Burning Manhattan Cocktail.png

Ah, DB2. One of the first fully relational databases (Sorry, Ted) I ever worked with. I’d have to say that this classic DBMS, in mainframe, client server and whatever zombie hellspawn we classify AS/400 Power i as will always be close to my heart.  I want to go old school here, but still classy, so I’m going to recommend a Manhattan. Who can’t love rye (“Canadian whisky”), sweet vermouth and a cherry? Really, this is the only “manly” drink that legitimately requires a cherry.  For the Power i series, I’ll have to go a bit more retro and recommend that you just pick up some fallen fruit in your office parking lot.  It’s probably well fermented at this time of year.

 

SQL Server

image

SQL Server 2008 R2D2 and earlier
I recommend here Tito’s Vodka in airline bottles. Prior to version 2012, SQL Server was primarily licensed with a server + CALS licensing scheme. You bought a server license, plus CALs for each user/device/whatever your salesperson said needed a license.  I like Tito’s vodka. It’s good and affordable. Plus I can buy airline bottles of it on the plane which helps me with portion control, much like those 100 calorie snack packs of Oreos one eats to lose weight. So I’m told. FYI: a good flight attendant cuts you off after 6 of these little bottles. A bad one cuts you off after two. A fantastic flight attendant says "Oh, did I give you your 6 bottles already? No? Okay, have these." The same thing worked well with CALs – the more you had the happier everyone one was.  It was easy to plan for new servers because this is the way it had always been done. But now….

image

Then along came SQL Server 2012 and a new licensing model. CALs were gone and core licensing came along. That means magnum 6+ litre-sized bottles of vodka for everyone! But not everyone can afford to fill those magnum bottles to the top with high-priced vodka. The good news is if you’ve already purchased giant bottles, you can pour out a few core’s worth of liquid to save money. Then cry about the room in the bottle that could be filled with vodka but isn’t.  In the long run, the new licensing model is simpler.  But for now, you might just have to live with less kick until you can right-size your servers.

Microsoft Windows Azure SQL Database Azure SQL DB

SNAGHTML6f6584

I’m not even sure what the brand name for cloud products are any more.  It’s not just Microsoft, either.  I figure since there are no boxes of DVDs to ship around, brand names change on a whim.  Just like trends in database design.  For Microsoft Azure cloud-based databases and datastores the good news is that you can just pay as you drink. Just like at your local bar. You don’t have to do much – most of the administration and maintenance is taken care of for you.  At work home I recommend you purchase a Star Trek Replicator and order a Blue Cloud cocktail every time you create a database. The good news is you can also have them delivered. This is known as Cocktail as a Service (CaaS). You need to go put this into your cloud budget request now, before we hit year end. Do it now.

Finally…

These pairings are based on my experiences purchasing, renting, and borrowing these technologies.  Your mileage may vary.  If so, I’d love to hear about it below.  And yes, I have more pairings coming soon about other data formats and databases (Ed. – Please promise me you aren’t going to write about NoSQL datab…datastores.  Me: I promise {I will}).

Thanks to my co-samplers & writers Joey D’Antoni (blog | @jdanton ), Karen’s Whisky (@karens_whisky) and Thomas LaRock (blog | @sqlrockstar)  You should follow them because they know booze and snark.

You might also like: 5 Things to Do to Help Your Family’s Tech Ecosystem Over the Holidays with Pairings

The Minimalist DBA: DBA Fundamentals

Nov 27, 2013   //   by Karen Lopez   //   Blog, Data, Database, Database Design, Events, Snark, Speaking, SQL Server  //  1 Comment

What do you think are the minimum skills a person should have before they are allowed to manage a database?  Does it matter whether or not it’s a production database?  Does it matter how much data is there? What kind of data? Is recovery a goal or a symptom?  Does it matter how old you are? Or how old the database is?  What is the meaning of all this anyway?

Thomas LaRock ( blog | @sqlrockstar ) and I will be talking about what a Minimalist DBA is, what skills we think they need, and how to ensure that they have them on 3 December at Noon EST for the DBA Fundamentals Virtual Chapter of PASS.

image
               "The best DBA is a lazy DBA…or at least a Minimalist DBA"

Every profession has a core set of responsibilities that are expected of every practitioner.  For anyone that has the letters “DBA” in their job description their job function is a black box to anyone on the outside. "What do you do here?" is a common question for most DBAs.

Some DBAs are a part-time data modelers, SAN admins, VM admins. Sometimes they know all about security, or Active Directory, or .NET. It differs from one shop to another. Whether it is day one or one hundred in your career as a DBA you need to make certain you stay focused on your core duties. If you slip up then you will find out why DBA often stands for Default Blame Acceptor.

Attend this webinar to make sure that no matter what your level of efficiency and laziness you are able to focus on the bare essentials (the minimum) necessary to be a Rockstar DBA."

Karen Lopez is a senior project manager and architect for InfoAdvisors. A frequent speaker at conferences and local user groups, she has 20+ years of experience in project and data management on large, multi-project programs. Karen is a chronic volunteer, a SQL Server MVP, and an active advocate for science, technology, engineering, and mathematics (STEM) education and data quality. She isn’t a DBA, but loves to talk and debate about the effectiveness of lazy DBAs.  She isn’t sure if the minimalist thing is a strength or an excuse. 

Thomas LaRock is a Microsoft Certified Master, a SQL Server MVP, a VMWare vExpert, and a Microsoft Certified Trainer with over 15 years’ experience in the IT industry in various roles such as programmer, developer, analyst, and database administrator. He is also the author of “DBA Survivor: Become a Rock Star DBA” (http://dbasurvivor.com) and has participated in the technical review of several other books.

Currently, Thomas is a Technical Evangelist for Confio Software. This role allows for him to work with a variety of customers to help solve questions regarding database performance tuning and virtualization. Thomas also serves on the Board of Directors for PASS as Vice President of Marketing. You can find out more information about him at his blog: http://thomaslarock.com/resume/.

You can probably expect our usual level of snark, debate, levity and great info for this presentation.  Bring your ideas and snark, too.  I always ensure that the audience is part of the presentation, so expect more the a slew of bullet points and demos.  And even though this is hosted by a SQL Server organization, all we will be talking about will be applicable to multiple platforms.  That’s how real enterprise database systems are anyway, right?

You’ll need to register, but it’s free.  By the way, if you also register on that site, you’ll become a member of that chapter.  And that’s free, too.

#SQLLinkBait Contest Winner (CLICK HERE NOW!)…

Sep 16, 2013   //   by Karen Lopez   //   Awesome, Blog, Data, Fun, Snark, SQL Server  //  4 Comments

Recently on Twitter I ran a fun contest to come up with the best SQL blog post title link bait.  Link baiting is a controversial approach to blogging.  The term itself is controversial actually.  In some contexts it just means using a great title.  But this contest was looking for the the best example of a different use of the term, one that’s a little bit more on the dark side of blogging:

Linkbait Definition #2 – “Attracting Link Attention with Controversy”
A lot of folks seem to suggest that certain things people write on the web or create on their sites are “just for the linkbait” – these can include negative or derogatory pieces, inflammatory material, and anything else that designed to incite or provoke a reaction from one or many online communities or blogs.

From <http://moz.com/blog/the-two-kinds-of-linkbait>

That’s the definition I was going for in this contest.  An snark we did receive.   I think this means that the SQL & Twitter communities have seen a lot of linkbaiting.  This was a short contest, with just under 300 posts and about 100 people participating in the conversation, but it reached more than 1 million potential impressions.  That’s the power of social media.

(click to enlarge this chart)

image

The full report on this hashtag can be found at http://keyhole.co/realtime/zA3oKj/

 

Some honourable (dishonourable?) mentions:

Our crack team of judges – Allen Kinsel ( blog | @AllenKinsel ), Thomas LaRock ( blog | @SQLRockstar ) and I picked  Tracy McKibben’s ( blog | @RealSQLGuy ) entry.  I’ll be DMing you, Tracy, with info for collecting your prize pack.  I sure hope you are following me (hint, hint).

I’d love to see some of you take this challenge to heart and write those blog posts.  Hmmm.  Maybe a new contest idea?

Update: It seems that Twitter or WP is having a bit of an issue rendering some of the tweets. Perhaps they will fix this data quality problem soon. Anyway, congrats Tracy.

Be The Next Microsoft Employee: @Data_Model and @VenusBarbie get makeovers – Behind the Scenes

Jul 24, 2012   //   by Karen Lopez   //   Blog, Fun, Speaking, SQL Server  //  2 Comments

Technical Barbies wait for filming to beginAs I shared with you previously, I’m a guest judge on Microsoft Learning’s new reality show, Be The Next Microsoft Employee.  In this contest, four SQL Server DBAs compete for a chance to work at Microsoft.  They have to go through all the normal Microsoft interview processes PLUS compete in front of cameras, crew and the Internet on a series of data-related challenges. The challenge I participated in, filmed on the Microsoft campus, was the last one and it was a doozy.

It should be no surprise to you that I took the Technical Barbies (@Data_Model and @VenusBarbie) along for the filming.  Since this was a hiring contest, I also brought along Working Woman Barbie, who comes with a suit that also turns into a glittery dance dress for after work fun.  Working Woman Barbie can talk, too.  She says fun things, but she talks too much about Ken.

When we arrived at the shoot, the first thing the Barbies got was a professional makeover.  Stylist Mimi Pettibone of StellarStyle.com has previous professional experience styling Barbies and action figures.  HOW LUCKY WAS THAT?  Mimi also gave me some great tips about styling and posing the Barbies.  Fishing line seems to be a key component.  I also showed her how I used clear braid rubber bands to help keep Barbie from losing her cell phone and shoes.  Just like I do.  Or should.

I live by a man’s code, designed to fit a man’s world, yet at the same time I never forget that a woman’s first job is to choose the right shade of lipstick.
– Carole Lombard

I’m not sure I agree with Ms. Lombard there, but both the Barbies and I had to wear lipstick for this shoot.  That’s how I knew I was on a reality show…just a different type of reality.

We took some before and after pictures.  I think I pretty much looked the same as VenusBarbie.  Mimi worked wonders with me.

VenusBarbie before

VenusBarbie in the chair

Working Woman Barbie in the chair

Data Model in the chair

Mimi did a fabulous job, don’t you think?

Technical Barbies after

Pages:123»

Subscribe via E-mail

Use the link below to receive posts via e-mail. Unsubscribe at any time. Subscribe to www.datamodel.com by Email


Recent Comments

Categories

Archive

UA-52726617-1