Trying to share some code ideas

logo

Category: Microsoft T-SQL

SQL with specific Microsoft T-SQL commands

Column Names by Table with Row Counts

How big is it? Big? Really Big? Ginormous???

Yesterday, I posted a quick query Column Names by Table on how to search for a column in any table in the database. Today’s query will extend that functionality to let you know how many rows are in the table while avoiding the dreaded

SELECT   COUNT(*)   FROM dbo.SuperHugeTable;

Column Names by Table

Where is it? Seriously, where is it?

We’ve all had the experience of trying to locate a specific column name or wanting to see the meta-data for columns in a specific or multiple tables. By reading the information schema tables we can see the meta-data and find all occurences of the column in the database. This information can be quite useful when needing to join columns and finding some hidden relationships.

Add a Space Before Upper Case Letter

“Why doesn’t this function exist?”

We have all had the situation where there is a need for a little custom function to modify strings or do simple math on a column. Today I am going to show how to create a User Defined Function (UDF) in Microsoft SQL.

Datatype Cast in T-SQL

“Making a square peg fit into a round hole”

There are many times when dealing with data that you need to transfigure one data set to allow for comparison purposes.

SQL has built in functions that easily allow us to transfigure data. There are some considerations when using the functions, most importantly understanding what will happen to the data set during the process.

Today I will talk about the CAST function.

SQL Update Many Rows

When working with a large table the UPDATE statement can easily bring even the most robust server to a screeching halt. In this posting I will show an alternative so even the largest tables can be updated without negatively impacting server performance.

Updating a million rows

Microsoft Access and ANSI

“What you don’t know can’t hurt you can drive you bonkers.” – R. Hedgins

Today’s posting will deal with an option setting that can minimize interoperability issues between Microsoft’s nuanced interpretations of standards.

Microsoft Access and Microsoft SQL

SQL Union – New Ideas

“Union of ideas begets a surprising confidence” – J. Madison

Today’s post will deal with an SQL command many of us may be familiar with but may forget exists, or haven’t thought about other uses for it.

UNION

In simplest terms the UNION command allows you to join separate SQL queries into one result set. The UNION command has one optional option “ALL”

Views, we need all the Views

There are times when you want to find all of the views that are in your Microsoft SQL database.  Especially when there are 1,000’s of views.  Each with the naming convention of the previous 5 DBA’s personal whims.  Normally as a database ages the views will become duplicative and add to the general clutter that gets in the way of efficiency.

List all views in a database

Effective use of Collate

“Talk about reaching over your shoulder to scratch the back of your knee” — Dave M.

My former co-worker Dave was full of such colorful phrases, and this was one of his favorites. He would use the phrase anytime he saw someone using an inefficient method to accomplish a task.

In this vein, I am going to attempt to share some tips, tricks, and helpful hints about how to accomplish things quicker and easier. Some of you out there may be already aware of these tips, but others may not. Also, if you have some hints to share send them along, so everyone can benefit.

How to Collate

Powered by WordPress & Theme by Anders Norén