You hear time and time again how 50 to 80 Percent of Data science projects is spent on data wrangling munging and transformation of raw data into something usable. For me personally. I’ve automated a lot of those steps. I built tools over the last 20 years that help me do more in less time….
Tag: T-SQL
SQL Azure Incompatibilities list
While 99% of what you do in SQL is supported in SQL Azure, there is a small list of things you’ll have to redesign, or at least reconsider before implementing your code on SQL Azure. The following is a list of items that are well documented, but I want to create a more complete list. …
SQL Azure DATETIME Functions
Microsoft SQL has always included a number of date and time functions. These results of these functions were always based on the operating system for the machine the SQL server was running on. But what results will you get when you’re dealing with SQL Azure? The server(s) are all virtual. They are all based on…
SQL 102-SELECT * Overhead
How many of you have heard me rant against using “SELECT * FROM tableName”? If you have I’m sure you’ve heard me mention the following reasons why SELECT * is evil. the more data you ask for, the longer it takes to return that data extra paging table locking hinders future attempts to create a…
Microsoft SQL Reporting Services for PHP
In an outstanding move on Microsoft’s part, Developers have released an open-source project to help PHP developers integrate SQL Server Reporting Services from their PHP applications! Using this new toolkit programmers can begin doing that today! This project offers a simple programming interface to: list available reports manage rendering of those reports and allow parameter…
Converting VARCHAR to DATETIME
Recently a post was made on LinkedIn asking how one would convert a date stored in a VARCHAR field could be converted to a DATETIME so date math could be performed. Unfortunately, this problem is more common than it should be. If users are given a free-form field to enter data, they’ll often enter unreliable…
Code Review
I’d like to start a new series that will help you learn some of the techniques I’ve used in the past. Some of these techniques are useful, some aren’t. Using SQL is a constant learning process. You have to be willing to look at your solutions and throw out those ideas that no longer work…
INDEX ON partition_scheme_name, filegroup_name, and default
Consider the CREATE INDEX statement below: CREATE INDEX ix_tableName_columnName ON tableName (columnName) ON { partitionSchemeName (columnName) | fileGroupName | default } What’s it all about? Let’s take these one at a time, and dig in a little bit. partitionSchemeName (columnName) Before you can use this option, you have to have your partition scheme defined. Check…
ASC, DESC, and Filtered Views
I know I’ve covered quite a bit of the CREATE INDEX statement, but there is even more to learn. Today I want to cover ASC, DESC, and the WHERE clause for indexes. ASC | DESC By default, indexes are created in ascending (ASC) order. This is fine most of the time. You’re going to be…
SQL 102 – Indexes With Included Columns
I’ve shown you how to create indexes, CLUSTERED versus NONCLUSTERED indexes, today I want to build on that. Let’s say you have a lookup query that takes names, and returns an ID for that name, you can actually build your query in a way where it would look up that data using just an index. …