October CASSUG Monthly Meeting

Our October meeting will again be online. NOTE: you MUST RSVP on Meetup at https://www.meetup.com/Capital-Area-SQL-Server-User-Group/events/273734124/ to view the Zoom URL!

Our October guest speaker is Elizabeth Noble!

Topic: Streamline Database Deployments

Our online meeting schedule is as follows:

  • 6:00: General chat, discussion, and announcements
  • 6:30: Presentation

We usually wrap up between 7:30 PM and 8:00 PM.

Please RSVP to this Meetup, then use the online event URL to join (note: you MUST RSVP for the URL to be visible). We will send out a meeting password as we get closer to the event.

September CASSUG Monthly Meeting @CASSUG_Albany #SQLUserGroup #SQLFamily

Our September meeting will again be online. NOTE: you MUST RSVP to this Meetup (https://www.meetup.com/Capital-Area-SQL-Server-User-Group/events/272490472/) to view the Zoom URL!

Our September guest speaker is Mindy Curnutt!

Topic: An Introduction to using the Spatial Data features within SQL Server

Over a decade ago Microsoft added the capability to store and work with geometry and geography data types. (Wait…what’s this you say?!?) Well, yes…it’s been there for quite some time now. You can actually store spatial data in SQL tables (in the form of points, lines and polygons). There are also many powerful, built-in functions that allow the manipulation and calculation of results around this data. This is a powerful, but sadly underused feature of the product given that IoT tends to be so focused around the location of things.

Come to this session to learn about:

  • The two elusive data types (geography and geometry)
  • How to load spatial data
  • Where you can get lots and lots of free spatial data to supplement your existing systems
  • See an example of real-world Spatial data in use (aggregating truck positions for tax calcs)
  • Write a SQL query from a bitmap picture (pure amusement)

You will learn:

About two special data types, geography and geometry

  • Understand the different spatial routines and functions, what they do, what type of result they return, and how you would use each
  • Watch a real world business case where using spatial data made things so much easier

About Mindy:

Mindy Curnutt is 7X Microsoft Data Platform MVP, Friend of Redgate and Idera ACE Alumni. She has been actively involved in the SQL Server Community for nearly two decades, presenting at various User Group Meetings, SQLPASS Summits, as well as Conferences & SQLSaturdays across North America. For two years, she was a Team Lead for the SQLPASS Summit Abstract Review Process and for years served as one of the 3 SQLPASS Summit Program Managers. She was a SME for SQL 2012 & 2014 MS SQL Server Cert Exams and helped to author the MS Press Book “SQL Server 2014 – Step by Step”, co-authored “Voices from the Data Platform” and “SQL Server 2017 Administration Inside-Out” and in 2018 was featured in the book “Data Professionals at Work”. In 2018 Mindy was the VP of Strategic Partnerships for the Non-Profit Girls+Data (www.girlsanddata.org) which strived to bring awareness about careers in Data Science and Analytics to young women in Junior High School (ages 10-14). She was the President of the N Texas SQL Server User’s Group from 2017-2020. In 2020 she was elected to serve on the Board of Directors for PASS.

Mindy serves as a mentor to others, (particularly the Transportation & Logistics industry), helping to educate and promote scalable and sustainable SQL Server architecture and design. She is passionate about Data Security, Accessibility, Usability, Scalability, and Performance. Mindy Curnutt is an Independent Consultant. You can follow Mindy at her blog, curnuttdatasolutions.com and on Twitter where she’s known as @sqlgirl

Our online meeting schedule is as follows:
6:00: General chat, discussion, and announcements
6:30: Presentation

We usually wrap up between 7:30 PM and 8:00 PM.

Please RSVP to this Meetup, then use the online event URL to join (note: you MUST RSVP for the URL to be visible). We will send out a meeting password as we get closer to the event.

August CASSUG Monthly Meeting @CASSUG_Albany #SQLUserGroup #SQLFamily

Our August meeting (Monday, August 10, 6 pm) will again be online. Use the Meetup URL (https://www.meetup.com/Capital-Area-SQL-Server-User-Group/events/271750998/) to RSVP. NOTE: you MUST RSVP to the Meetup to view the Zoom URL!

Our August guest speaker is Deborah Melkin!

Topic: Single Statement, Many Changes: How One Statement Can Modify Multiple Tables

You can only insert, update, or delete from one table at a time. At least that’s what they tell us when we first learn to write SQL statements. However, that one statement could modify multiple tables, and we may or may not even realize it is happening.In this session, we will examine how a single data manipulation (DML) statement could change data for many tables. We will approach these from two different angles: implicit database design & explicit SQL code and objects. Syntax, performance gains, and gotchas of these different methodologies will be discussed. Finally, we will explore often overlooked changes that occur further downstream as a result of our DML statement.When you leave, you will understand and appreciate how a DML statement against one table affects not only that table but how it can have a ripple effect of changes throughout your entire database.

About Deborah: Deborah Melkin has been working as a database professional with SQL Server for almost 20 years. She spends her days helping programmers with all aspects of database design, queries, performance, and deployment. In 2016, she began her blog, Deb the DBA. Soon after that, she began speaking at SQL Saturdays and user groups. Deborah is a board member of the New England SQL Server User group (NESQL) and was recently named as an IDERA ACE Class of 2020. She also won Speaker Idol at PASS Summit 2019. In her spare time, Deborah can usually be found doing something musical.

Our online meeting schedule is as follows:
6:00: General chat, discussion, and announcements
6:30: Presentation

We usually wrap up between 7:30 PM and 8:00 PM.

Please RSVP to this Meetup (https://www.meetup.com/Capital-Area-SQL-Server-User-Group/events/271750998/), then use the online event URL to join (note: you MUST RSVP for the Zoom URL to be visible). We will send out a meeting password as we get closer to the event.

See you there!

#PASSSummit 2020 #SQLFamily

It’s that time of year, when aspiring PASS speakers find out whether or not they’re speaking at PASS Summit. I was fortunate to be selected to speak last year, and I had the time of my life! If you want to read more about it, check out my synopsis of it from last year!

I got the official email notification yesterday (and now that the list is up, I can say it publicly). Alas, I was not selected this year. Oh well. C’est la vie.

That said, I was (and still am) excited about being selected last year. To be selected to speak at PASS Summit just once is a great honor and a nice feather in my cap. To be selected again would be a bonus. And although I wasn’t selected this year, it won’t preclude me from applying again for next year… and the next… and the next.

Unfortunately, given my current employment — and subsequently, my financial — situation, it is highly unlikely that I will be able to attend this year’s Summit, even if COVID-19 has forced it to a virtual event. Although the fact that the event is virtual means prices are reduced, they are still too high for me to attend (unless, between now and then, I land a job and my new employer would be willing to pay my registration fee — note to any future employer who might be reading this: here is a letter that makes the case as to why it would be good to send me to PASS Summit! Note that this link downloads an MS Word document). “Virtual” does not mean “free;” there are a number of expenses that still need to be paid, even for an online event. My friend Monica Rathbun wrote a nice article about what it financially takes to put on a PASS Summit, even a virtual one.

I went through the speakers list, and I was happy to see that a number of my #SQLFamily friends were selected to speak! Congrats to all of you who were chosen!

And although I might not be able to attend this year, if you’re able to get to a PASS Summit, I highly suggest you do so! You’ll learn a lot, and it’s a great time!

Coming up July 25 (a week from Saturday): #SQLSaturday #961, Albany #SQLSatAlb #SQLSatAlbany #SQLSat961

On July 25, a week from Saturday, CASSUG will host SQL Saturday #961, Albany! This is a daylong conference covering a variety of data, technical, and professional topics! And this year, we are going virtual, so you don’t have to be in Albany to attend!

To register for the event, and for more information, go to our SQL Saturday website!

And oh yes, did I mention that I’m speaking? I will be doing my presentation on networking!

SQL Saturday is always a good time, and the Albany event, which is near and dear to my heart, is one of my favorite events of the year. I’ll see you, virtually, a week from Saturday!

#SQL101: Create tables from CSV flat files

With my previous article about getting into REST applications, I figured it would be a good idea for me to set up a data source so I could practice. Besides, I had reinstalled SQL Server 2019 on my machine, and I needed to import some data so that I could brush up on my SQL skills as well.

Being the baseball nut that I am, of course, I had to import baseball statistics, so I decided to reimport the most recent data from Sean Lahman’s baseball database. The last time I did this exercise, I downloaded a database format. I don’t remember what format I used (the links all say “Access” — which I don’t remember downloading), but the files I used had an .sql extension. This time, I used the comma-delimited version, which downloaded a zip file containing files with a .csv extension.

I wanted to import the files directly into my database and have them create the tables upon doing so, so I opened up my SSMS, created a new Baseball database, and looked into how to do this. After poking around a bit (and a little bit of Googling), I found that flat files could be imported by right-clicking the database of your choice (in the below example, “Baseball”), clicking Tasks, and selecting Import Flat File.

Selecting this opened an Import Flat File wizard. First, it prompted me to select the input file. (Note: if you are importing multiple files, as I did for this little exercise, the wizard is smart enough to remember your last folder when you click Browse.)

When it looks at the flat file, it gives you a preview of the data that you’re importing. Since, for this exercise, I’m importing comma-delimited flat files, it was able to put my data into nice, neat columns.

Clicking “Next” brought me to a screen where I could modify the columns. I like this option a lot, as it gives me an opportunity to set up my data schema the way I want. If you’re a SQL or database newbie, I strongly suggest that you learn about primary keys and data types and take the time to set them up at this point.

In this particular example, I set my yearID to char(4), stint to int (I will likely change this to tinyint), teamID to char(3), lgID to char(2), and pretty much everything after lgID to int. I also set my first five columns as a composite primary key and everything else to be nullable.

I must have set these columns up successfully, because when I ran it, it did so without complaining.

I wish I could say that I imported all of my flat files without a hitch, but I did run into a few that didn’t run successfully the first time. Here are some of the issues that I came across.

  • I had opened a file in Excel to check data types, forgotten to close it, and the import complained that it couldn’t work because the file was still open.
  • I miscalculated a few field sizes, and came across messages saying that my column sizes were too short (for example, setting nvarchar(10) for a column that included data with 15 characters).
  • There were a few cases where I simply had the wrong data type.
  • My Pitching table included a column for ERA, which I was surprised to see. Reason: ERA (Earned Run Average, for those of you who are baseball-challenged) is a calculated statistic, like batting average. However, batting average was not included in the Batting table. So, I set the column data type to float. However, when I tried to import it, it failed. When I looked at the data, I found entries under ERA that said “inf” (for “infinity”)*. In this case, I did some data cleansing. I got rid of these entries and saved the flat file. It then imported with no problem

(*Some of you might be wondering, how do you get an ERA of infinity? Answer: you give up runs without getting anyone out! Mathematically, you would get a divide-by-zero error for calculating ERA, but in baseball parlance, it means you give up runs and can’t get anyone out!)

So hopefully at this point, you now have an idea as to how to import flat files into a SQL Server database (and maybe even got a small taste of data types and primary keys). And hopefully, this little utility saves you a lot of grief when trying to import flat files.

July CASSUG Monthly Meeting @CASSUG_Albany #SQLUserGroup #SQLFamily

Greetings, data enthusiasts!

Our July 15 meeting will again be online. NOTE: you MUST RSVP to this Meetup (see instructions below) to view the Zoom URL!

This is our annual July meeting in which we feature lightning talks, and discuss our upcoming SQL Saturday!

Want to do a lightning talk for our July monthly meeting? Contact Greg Moore at mooregr@greenms.com by July 8!

Lightning talks should be around 10 to 15 minutes in length. It can be about any topic that appeals to data professionals, including (but not limited to) T-SQL, performance tuning, BI, tools, and professional development. New and novice speakers are especially welcome and encouraged to apply!

Our online meeting schedule is as follows:
6:00: General chat, discussion, and announcements
6:30: Presentation

We usually wrap up between 7:30 PM and 8:00 PM.

Please RSVP to our Meetup at https://www.meetup.com/Capital-Area-SQL-Server-User-Group/events/268246385/ then use the online event URL to join (note: you MUST RSVP to the Meetup for the Zoom URL to be visible). We will send out a meeting password as we get closer to the event.

See you then!

June CASSUG Monthly Meeting @CASSUG_Albany #SQLUserGroup #SQLFamily

Our June speaker is Hilary Cotter!

Topic: SQL Server Replication

Replication is a native SQL Server component used to distribute and aggregate data between SQL Servers and other heterogeneous data sources. In this presentation, Hilary Cotter, covers how to effectively chose and deploy optimal SQL Server replication solutions. He also covers performance tuning, optimization, monitoring and integrating replication into your DR solutions.

About Hilary:

Hilary Cotter is an industry veteran and has been a SQL Server MVP for 17 years. He specializes in replication, SQL HA technologies, full-text search, perform acne tuning and SQL Server Service Broker. He has worked for many fortune 500 companies implementing cutting edge replication solutions. He has written and co-authored several books, white papers and authored Microsoft exams. He has answers over 10,000 questions on the Microsoft SQL server forums, some of them correctly.

Our online meeting schedule is as follows:
6:00: General chat, discussion, and announcements
6:30: Presentation

We usually wrap up between 7:30 PM and 8:00 PM.

To join the meeting:
Go to our Meetup group event (https://www.meetup.com/Capital-Area-SQL-Server-User-Group/events/268274240/ — a Zoom meeting link is included in the event) to RSVP. We will send out a meeting password as we get closer to the date/time.

May CASSUG Monthly Meeting @CASSUG_Albany #SQLUserGroup #SQLFamily

Our May CASSUG monthly meeting will be virtual! It will be on Monday, May 11, at 6 pm (EDT).

Our May speaker is Argenis Fernandez! He will talk about SQL Server Virtualization and I/O Patterns

To join the meeting, use the following link and follow the instructions: https://datto.zoom.us/j/99185518142?pwd=eDRZZXFLdis0YVBjU3BXRGdtQTdjdz09

For more information, see our Meetup link at https://www.meetup.com/Capital-Area-SQL-Server-User-Group/events/267751970/

Hope to see you online at the event!

Uninstalling SQL Server

As I wrote earlier, I discovered, much to my chagrin, that I vastly underestimated how much disk space I would need when I purchased my laptop. So I went to Amazon and bought a new 2TB internal SSD for my machine. It’ll take about a week or so to arrive. Until it gets here, I needed to free up some space on my drive.

I decided to uninstall SQL Server. I don’t need it immediately, and I can reinstall it later once I get my new SSD.

So, I first went to my Control Panel and looked at uninstalling SQL Server. But when I got there, I saw a number of things under Programs and Features, as you can see below. What, exactly, am I supposed to uninstall?

I went to my old and trusted friend, Google, for advice, and found this article (among others).

The first step is backing up your data. Well, I don’t have any data to back up, so that isn’t an issue at the moment. It likely will be somewhere down the line, but at the moment, I don’t anticipate that to be an issue. That said, if you do have important data that you want to save, make sure that you back it up first!

Next, they tell you to stop the services. I opened Services (hit the Windows key and type “Services”) and look for any SQL-related services. I shut down MSSQLSERVER, SSAS, SSIS, and so on — basically, anything that looked like it was tied to SQL Server. I checked everything listed under “SQL Server.” I also looked under “Microsoft,” but I didn’t immediately see anything related to SQL Server when I did.

I then followed the steps to uninstall. Step 2 tells you to “search for ‘sql’ in the search box,” but what it doesn’t tell you is that you need to do that from Apps and Features, not from Settings. That step is missing from the instructions. I selected Microsoft SQL Server (Version) (Bit) per step 3 — for me, it was Microsoft SQL Server 2019 (64-bit) –and selected Uninstall. I selected the appropriate prompts, and off it went.

I didn’t think to time how long it took to run, but by my estimate, uninstalling SQL Server took about twenty minutes to run. It appeared to uninstall cleanly; I didn’t see any errors. By the time it was finished, I had about 16GB of new space on my drive.

So, that’s my adventure in uninstalling SQL Server to clear space on my drive. When my new SSD arrives, I’ll make sure it gets reinstalled. (And stay tuned for a future article about installing my new SSD on my laptop.)