I am finally back in the writing saddle again with a spree of tidbits for the month of April 2015. These articles will focus on learning a simple TSQL command or technique. Today, I want to talk about how synonyms can be used to preserve backward compatibility objects while allowing for schema redesign in existing databases. The CREATE SYNONYM and DROP SYNONYM key words were added to the TSQL language in the 2005 version. The main purpose of the construct is to provide a simple name to reference a database…
Tag: TSQL
Is my string a number?
I have been recently devoting a-lot of time answering questions on STACKOVERFLOW. Many times, you are just helping and/or teaching younger SQL Server developers and/or administrators the tricks of the trade. Sometimes you come across a GEM, a question that you can blog about so that when they are asked again, you have a well constructed answer. One classic questions asked by new database developers is whether or not the user input is a number? I thought it was an excellent opportunity to review what tools are available given a…
Transferring Logins Between Servers
There are two main categories of logins in SQL Server. There are WINDOWS logins in which password restrictions are controlled by the Active Directory (AD) policy. Then there are SQL logins in which the database administrator has more control. Lately, I have been crafting new database servers with the latest hardware to make sure my organization is ready for growth during the next five years. This includes installing the current version of SQL Server and migrating all the applications from the old server to the new server. In short, transferring…
Replacing Data – Part 2
I have been recently devoting a-lot of time answering questions on STACKOVERFLOW. Many times, you are just helping and/or teaching younger SQL Server developers and/or administrators the tricks of the trade. Sometimes you come across a GEM, a question that you can blog about so that when they are asked again, you have a well constructed answer. One such question was recently asked. How do you replace all the digits in a credit card, expiration date and authorization code string? In my last article, I created a inline table valued…
Replacing Data – Part 1
I have been recently devoting a-lot of time answering questions on STACKOVERFLOW. Many times, you are just helping and/or teaching younger SQL Server developers and/or administrators the tricks of the trade. Sometimes you come across a GEM, a question that you can blog about so that when they are asked again, you have a well constructed answer. One such question was recently asked. How do you replace all the digits in a credit card, expiration date and authorization code string? The Transaction SQL language does come with a REPLACE() string…
Date/Time Functions – SWITCHOFFSET()
This is the last short articles or tidbits on Transaction SQL date/time functions. It has surely been a-lot of work to write 41 articles on every way to slice and dice dates, times and offsets. Today, I am going to talk about the SWTICHOFFSET() function. This function is useful when you want to convert time from one zone to another. The function takes a date/time offset variable as input as well as the new offset value. It returns a date/time offset variable in the new zone. The examples below convert…
Date/Time Functions – @@LANGUAGE
I can almost see the end of my writing of short articles or tidbits on Transaction SQL date/time functions. We already talked about functions like DATENAME() that returns the month or day of a given date/time variable as as string. How does this function know what to return when the default language is Russian? Today, I will be talking about @@LANGUAGE, a configuration function which returns the current session value and SET LANGUAGE, a set statement which changes the value for the current session (SPID). Last but not least, the…
Date/Time Functions – @@DATEFIRST
I am on the home stretch with my writing of short articles or tidbits on Transaction SQL date/time functions. We already talked about functions like DATEPART() that returns the day of the week as an integer and DATENAME() that returns the day of the week as text. But what is the first day of the week? The default for the US English language is 7, or Sunday. However, this setting differs between countries. Today, I will be talking about @@DATEFIRST, a configuration function which returns the current session value and…
Date/Time Functions – EOMONTH()
I linger on to the end with my writing of short articles or tidbits on Transaction SQL date/time functions. Most, if not all, modern day computer systems collect data which contains dates and times. Sometimes we want to know the start date and end date of each month. Before SQL Server 2012, you used to have to set the date to the first of next month. Then use the DATEADD() function to back date by one day to get the end of month date. Today, I will be exploring the…
Date/Time Functions – ISDATE()
I am tireless with my writing of short articles or tidbits on Transaction SQL date/time functions. Most, if not all, modern day computer systems collect data which contains dates and times. Sometimes data comes from external sources in a textual format. How do we know if the data is a valid date/time variable? Today, I will be exploring the ISDATE() function that takes a character string as input and returns an integer as output. A numeric 1 represents a valid date and a numeric 0 represents an invalid date. The…