Search

musings of extofer

and his secret identity, Gabriel Villa

Open Search
Category

Programming

TSQL Tuesday #20: T-SQL Best Practices

It’s T-SQL Tuesday again, and although I seldom write on Tuesday, I attempt to participate. The brainchild of Adam Machanic (Blog|Twitter), T-SQL Tuesday invites new and existing SQL Server bloggers to post about the same topic on the same day.  This time around, the topic is “T-SQL Best Practices”, hosted by Amit Banerjee (Blog|Twitter). 


What is T-SQL, anyway?
T-SQL, or Transact-SQL, is Microsoft and Sybase’s proprietary extension to SQL.All applications that talk to a SQL instance use T-SQL statements to talk to the server, regardless of the user interface or the application.

T-SQL is a proprietary top-down procedural programming language. It was originally developed jointly by Microsoft and Sybase for Sybase SQL Server on UNIX until 1993. From that point forward, Microsoft SQL Server was developed for NT Server. T-SQL is not an object-oriented programming language with object or methods and it does not compile into binaries. It is, however, a Server Side processing code used to query data. When I develop T-SQL, I treat it as any other programming language.

For T-SQL best practices, I recommend using programming guidelines similar to those of C#, Java or VB.Net.

User Defined Functions
I have always been a fan of functions. To “bring out the Fun in Functions”, I learned that in any programming language, if you write it more than once, write a function; if you write it three times, stop programming and start a new career. In T-SQL, a UDF can return a single value (Scalar Function) or return a set of data (Table Value).

Use naming conventions
Use common naming conventions to name your variables. This standardization allows any programming to read your code and understand what is being processed. First, figure out the purpose of the variable, then give the variable a precise name, and finally, imply its Data Type. For instance, the date variable @BirthDate [datetime] is easy to understand. However, if you named it @BornColumn [datetime], somebody down the line would be thinking “Huh?”It would not be so interpretive.

Legible Code
Like most word processing programs, SSMS will automatically wrap your lines of code we all write lengthy blocks of code if you don’t press at certain points you could theoretically have only one long line of code the best thing to do is to turn off word wrap for better legibility to do this, select Tools, Options, Text Editor, Transact-SQL, under Settings Turn off Word Wrap for legibility.

Commenting Code
In T-SQL, make concise comments describing the stored procedure or trigger. Best practice for commenting code is to make it understandable when you revisit this code years later. Dates and initials on comments can also alleviate problems.
There are two ways to comment code in T-SQL:

— Two hyphens create one line for commenting
GO
CREATE PROCEDURE sp_TSQL2sDay

/* This is a block of comments

The end delimiter of this comment
is an asterisk and front slash */
GO
ALTER PROCEDURE sp_TSQL2sDay

Version Control
SVN, Mercurial, Git, and SourceSafe are a few well-known version control systems to apply to source code. Although how do you source control T-SQL? It is not common to build the scripts in a programming IDE (Integrated Development Environment). Red-Gate has a product to source control schemas and data, but T-SQL saves in the database as a Server Side Script. I don’t use Red-Gate’s product, but I recommend that you save the scripts, whether stored procedures, triggers, or UDF, as a .sql file, and add them to your version control of choice.

If ifs and buts were candy and nuts, we’d all have a Merry Christmas. If pigs could fly, we’d all need stronger windshield wipers….As much as I wish this could be a feature rich post on how to create a UDF or implement version control on .sql files, it’s not. My sincere hope is that this will soon lead to that. In the meantime, these are just a few of my most recommended rules of thumb when developing T-SQL.

0

The Source Code Tool Belt

As the cast of Home Improvement was heard to say, “What time is it?  TOOL TIME!”
probably violating some copyrights
Every profession has its tools of the trade.  Country doctors have the little black bag.   Cable guys bring Velcro totes with crimpers and punchdown tools.   The Maytag Repairman brings – nothing.  Anyway, don’t you wish you had tools like Handy Mandy, those talking tools that do the work themselves? Or wonder how to get Kevin Smith’s Mallrats character Silent Bob’s utility belt with grapplers or lassos, “Bigitty Bong”?  We’re talking about the right tool for the right job.   Here are some tools you can confidently hang on your source code tool belt.
Notepad++
For text editing, you can use the Windows Notepad by pressing the Windows© Key and the letter ‘R’, then typing “notepad”; or you can use other text editors, such as Programmer’s Notepad, Notepad2, and Notepad ++.   In a post earlier this year I listed Notepad++ as one of my favorite tools.  I use Notepad++ as my text editor for several reasons:  it handles multiple documents; it displays line numbers, which is valuable in writing code; and, with syntax support for just about every popular programming language from ADA to YAML, Notepad++ is just short of Visual Studio’s IntelliSense© in its ability to detect keywords and operators.
Notepad++ configured in Obsidian Style
How can you use Notepad++ instead of the default MS Notepad?   There are several techniques in doing this and you can search them online, however, here’s my preferred technique:
1. After installing Notepad++, go to the Notepad++ directory (C:\Program Files\Notepad++).
2. Copy the Notepad++.exe file and paste a copy of it in the same location.  This copied file will be “C:\Program Files\Notepad++ – Copy.exe”.
3. Rename the copied file “C:\Program Files\Notepad++ – Copy.exe” to “npp.exe”.
4. Go to your System Environment Variables:
  • Right click on “My Computer”
  • Click on Properties, Advanced, System Settings, Environment Variables
  • Under “System Variables”, choose “Path”, then click Edit
  • Go to the end of the Variable Value and type a semi-colon “;” followed by the Notepad++ path (C:\Program Files\Notepad++).
  • Click OK.
5. Open the run command, type “npp”, and press Enter.
6. Notepad++ should start.
Grep 
If you’re like me, you probably have a mish mash of code samples dating back to when you first learned to write a User Defined Function in SQL, or created your first class in C++.   These code snippets might be saved in text files or even .vbs, .js, .cpp or .sql files. When it comes time to share or reuse that code, it’s a pickle of a problem to find it.  I will be the first to admit that I don’t have my code samples organized 100% of the time.
Here’s a great utility for this problem:  It’s called Windows Grep, and it searches content within files   (for you computer trivia collectors, the word “grep” was originally a Unix command meaning “global regular expression print “).  If I want to share a snippet of code that I know I wrote and saved as a .vb file, I use Grep to find this for me.  For instance, I can enter the word “Loop”, select my path “E:” drive, and search for all VB files, “ *.vb”.   Grep will then return any VB files with a Loop in the content.   
Evernote
If you want a more organized method to search for code, use Evernote to organize your code snippets. Evernote is a great note taker that will synchronize your files online for all devices listed under your account.  “How is a note taker useful with source code?” you may ask.  For one, it saves your files online.  Also, you can categorize your code in Notebooks by language, i.e., JavaScript, Batch, C#, SQL and so on.  Each file can be tagged with specific keywords, for instance “Adapters”, ”Mouseovers”, or “TableValueParameters”.  This makes finding code easier.  You can select a Notebook of language like C# and narrow down the C# snippets to only show you code with “Adapters”. To add your current collection of snippets to Evernote, it’s easy:
Evernote view of my C# Notebook
1. Install Evernote
2. Go to Tools, Import Folders
3. Begin Organizing files into Categories and Tags
Notepad++, Grep, and Evernote already make a powerful source code toolbelt.  I might be leaving out ExamDiff, the free, easy to use code comparison tool with a few remembering capabilities, navigation and command options, and the ability to detect live changes.  I might also be leaving out the “new to me” CodeRush Xpress, Visual Studio plugin with features for navigation, refactoring, and much more. 
So, get out your laptop or toolbox of choice and put these freely available tools in there.  You’ll then be well equipped to master your source code.
0

10 Super Powers Developers, Designers, and DBAs should have

I would like to send a tweet telepathically. I would like to jump over datacenters with one leg. I would like to eat Cheetos© without orange fingers, cool my Mt. Dew© with my bare hands and warm my coffee by looking at it. These powers would be mine if I were bitten by a radiation-infected squirrel, or hit by meteor residue that was actually a piece of a planet from another universe. However, being a mere mortal, I have been forced to acquire my super powers in other ways as a professional in the field of software, web and database production.

I‘ve been developing software for over a decade and have worked in various industries. When I worked for a consulting firm, I quickly learned that I needed a well rounded skill set to be able to give a client what was needed at any moment. My skills are always being put to the test. Just last week I had to solve some problems that my superiors couldn’t. So now I wonder, would I have been hired if I lacked those skills? Would I have been promoted?
Every now and then I read an article listing the skill set every Information Technology professional should have. I’d like to share my own list of skills that I believe developers, designers and DBAs should have.


Know how to fix common PC problems. Since you are in IT, don’t be surprised when family, friends or coworkers ask you to fix their computers. Unless you prefer to wear the infamous “No, I will not fix your computer!” t-shirt, you don’t want to get caught with your pants down, not knowing how to map a drive or printer, edit a registry, configure email POP accounts, or clean out a virus. I believe anyone in the field, from Jr. Programmer to CTO of a corporation, would rather NOT put in a call to Systems Support to fix a minor PC problem.

Understand general systems administration. IT professionals should know basic networking, how to trace a network and run cable. Do you know what I mean when I say “White Orange, Orange, White Green, Blue, White Blue, Green, White Brown, Brown”? Can you to trace a route and analyze packets from one IP network to another? You need to understand basic networking to understand a trace, why IP numbers change, why it makes stops, and so on. Permissions are also important in that rights must be granted or revoked for files or data you could be programming for. You need to understand these underlying hardware and software components.


Write a script. Shell script, or batch programming as it’s also referred, is needed to be able to manipulate files and directories, and to perform other system operations. You don’t have to be a programmer to know your OS shell commands. Unix Shell, MS-DOS, and PowerShell are examples of interpreters that will execute scripts. Other scripting languages like AppleScript or Windows Scripting Host are interpreted by an engine other than the OS command line.

Create and test backups. This topic cannot be stressed enough and should be in two parts. First, implement source control. Source control is very important. It allows you to check versions in, and versions out, and it manages changes in your code. Some source control tools include Subversion, Git or Visual Source Safe. Second, be able to back up your files, and even your version control repositories. I have found it easy to use tools such as TeraCopy, Dropbox, or Allwaysync to back up synchronized versions of code or databases to other drives or even offsite disk space. In either case, always test your backups. If you don’t test backups periodically (I recommend once a month for each occurrence), you won’t know if the backups are complete or corrupt.

Train other developers, designers or DBAs. It is the opinion of many that “The Best Way to Learn is to Teach”. You should be able to take the time to explain the back-story of your work to others. Training forces you to increase your knowledge on certain topics. If you can explain something to others, then you really know it. There’s tremendous satisfaction in seeing your team, colleague or friend implement a concept you taught.


Contribute to the Open Source community. More and more, I see job postings with open source community requirements. My first thought is, “these are FOSS (Free and Open Source) companies requiring this”, but then, I’ve seen the same requirements in .NET jobs. Open source was once a subculture; now it’s a more of a mainstream obligation for a developer to share code, contribute to large projects, test and debug, publish documentation, and generally participate in the tradition. SourceForge was one of the first popular sites to host open source projects; along came CodePlex, Google Code, and GitHub, just to name a few.


Participate in social networking. Make social networking work for you, not against you. Define your personal brand and share that brand on Twitter and LinkedIn. Make connections with other professionals. Get the word out. See how Social Networking has worked for me.


Blog. Actively manuscript your knowledge or experience in an IT related weblog (blog). Share code, design ideas, database tips or other relative material. You are in IT, have a dot com (website) by your name. Become a resource.


Participate in online help. Don’t keep your knowledge to yourself; share the wealth that is your intelligence. Some of the best places to help others are forums such as StackOverflow and the MSDN forums. Another way of participating is to post questions to your social networks, for instance, the SQL Server Community. To post help questions to this community, use the #sqlhelp hash tag.


Do some public speaking. Start by attending user group meetings and speaking to your peers. I started with a 101 in a user group, then had abstracts selected to conferences. Get comfortable with a topic you know and get the nerve to make a presentation on it. You use all your super powers here – sharing source code, giving help, and training.


Wolverine
If you are not Wolverine or have Spidey-sense, use this guide to pinpoint and sharpen your skills. With these 10 superpowers under your belt, you should be ready and able to meet, and even enjoy, some of your own software, web, and database challenges.

0

Class of 2011: New era of programmers

 

“If I knew then, what I know now”. Back in the Dark Ages when I decided on a career in programming, resources were limited. The Internet was not evolved. I waited in computer labs just to get an hour on an x86 PC with a 14.4 modem to connect via Kermit, an Internet terminal service. I didn’t know which direction to take or what language(s) to learn. I had a degree in Mass Communications but wanted to start my career over using the DIY method. It’s taken me years to get where I am now. Nowadays, beginning to program is not as difficult as it was for me. If I had the opportunity to do it again and shorten the trip, this would be my advice:


First, dedicate a computer to programming. In my recent series about hacking, “School of Hacks”, I recommend a Unix-like operating system, or Linux distribution, but that’s not necessary. If you only want to learn programming, Windows is fine. Furthermore, I’ll be sharing information for Linux and Windows operating systems.


Second, select a semicolon programming language, and learn the language. Programming languages vary – some update frequently, others seldom change. Learning a semicolon language makes it all better when the common methods and functions are similar within the array of that language. Once you learn one language, it would not be too hard to learn another. Kind of like learning the grammar and semantics of Spanish, then translating that to Italian. What’s important is that you are learning programming logic, i.e., conditional statements, while loops, and switches. For you to begin, here are a few highly and more important, semicolon languages to choose from: C#, C, C++, Java and Python.

Third, download and get some hands-on experience with an Integrated Development Environment (IDE). The IDE is the locale where the code compiles. In the past, it was more of a challenge getting the hands on experience with an IDE. Nowadays you can download the IDE’s and install them on the same PC you use for programming. Each semicolon programming language has its preferred IDE. Let’s Start with C#. I currently work with C#, although my first language was a flavor of BASIC then onto Microsoft’s Visual Basic for Windows programming. Windows programming is now readily available online even for Microsoft .Net Framework. You can now get a Microsoft Visual C# Express version at no cost. On the same .Net Framework, if you wish to learn C++, you could download Microsoft Visual C++ Express free version. Microsoft Visual C# and C++ are for Microsoft Windows development only. There are also non Microsoft IDEs for Windows programming, such as Eclipse IDE for C and C++. These IDEs are also available for Mac and Linux. If you are interested in C or C++ development specifically for Linux platforms, I recommend GCC, the GNU Compiler Collection. Although, you might see that the GNU Compiler Collection handles Java, I recommend Sun’s, I mean – Oracle’s Java. Java is a powerful, popular and widely used programming language. It was created by Sun Microsystems, and it now owned by Oracle. That being said, you can download a Java IDE from the Oracle site in the Sun Developer Network in flavors for Microsoft Windows, Linux, Solaris and Mac. You can download the NetBeans or the EE Bundle. Lastly, I recommend Python as a good beginner language. You can follow my article, Installing Python on Linux, or you can download Python 2.7.1 Windows Installer.

Fourth, get these tools and put them in your programmer’s toolbox: First, a printable PDF file called Python Cheat Sheet, and a C Style, or semicolon language Cheat Sheet for C, C++ and Java. Also, a printable Visual C++ Key Binding Poster, a Visual C# Key Binding Poster.

Fifth, use a good map to show you the way.  From the curriculum taught in “Introduction to Computer Science” at MIT (don’t ask), I gathered these topics you can reference to be on your way to learning programming:

  • Hello World
  • Operators
  • Types
  • Boolean
  • Conditional Statements (If Statements)
  • Loops
  • Methods and Functions
  • String Operations
  • List and Collections

Sixth, practice, practice, practice. I can only take you so far. You can continue the DIY method, all you need to Google keywords, “learn” or “programming”, the topic, and the programming language, for example: “learn C# Operators“. And lastly, you need to practice programming. Once you mastered one language, you can graduate to more complicated Object-oriented programming and topics, like:

  • Objects
  • Inheritance
  • Polymorphism

So far, this lesson has cost you nothing, except a computer which you probably already had. It doesn’t take much money or time to get started. Like I said before, if I knew then what I know now, I would have started on a semicolon, or C like programming language, I would have begun with free and open source software, or even just with free software IDE. I would have spent more time understanding arrays as a collection or loops. Whether you’re a high school graduate in Cocoa Beach, Fla. or an elementary school graduate in Colorado Springs, this is a good start. It’s a new era of learning, and you can be a new programmer at no cost.

0

Class of 2011: New era of programmers

“If I knew then, what I know now”. Back in the Dark Ages when I decided on a career in programming, resources were limited. The Internet was not evolved. I waited in computer labs just to get an hour on an x86 PC with a 14.4 modem to connect via Kermit, an Internet terminal service. I didn’t know which direction to take or what language(s) to learn. I had a degree in Mass Communications but wanted to start my career over using the DIY method. It’s taken me years to get where I am now. Nowadays, beginning to program is not as difficult as it was for me. If I had the opportunity to do it again and shorten the trip, this would be my advice:


First, dedicate a computer to programming. In my recent series about hacking, “School of Hacks”, I recommend a Unix-like operating system, or Linux distribution, but that’s not necessary. If you only want to learn programming, Windows is fine. Furthermore, I’ll be sharing information for Linux and Windows operating systems.


Second, select a semicolon programming language, and learn the language. Programming languages vary – some update frequently, others seldom change. Learning a semicolon language makes it all better when the common methods and functions are similar within the array of that language. Once you learn one language, it would not be too hard to learn another. Kind of like learning the grammar and semantics of Spanish, then translating that to Italian. What’s important is that you are learning programming logic, i.e., conditional statements, while loops, and switches. For you to begin, here are a few highly and more important, semicolon languages to choose from: C#, C, C++, Java and Python.

Third, download and get some hands-on experience with an Integrated Development Environment (IDE). The IDE is the locale where the code compiles. In the past, it was more of a challenge getting the hands on experience with an IDE. Nowadays you can download the IDE’s and install them on the same PC you use for programming. Each semicolon programming language has its preferred IDE. Let’s Start with C#. I currently work with C#, although my first language was a flavor of BASIC then onto Microsoft’s Visual Basic for Windows programming. Windows programming is now readily available online even for Microsoft .Net Framework. You can now get a Microsoft Visual C# Express version at no cost. On the same .Net Framework, if you wish to learn C++, you could download Microsoft Visual C++ Express free version. Microsoft Visual C# and C++ are for Microsoft Windows development only. There are also non Microsoft IDEs for Windows programming, such as Eclipse IDE for C and C++. These IDEs are also available for Mac and Linux. If you are interested in C or C++ development specifically for Linux platforms, I recommend GCC, the GNU Compiler Collection. Although, you might see that the GNU Compiler Collection handles Java, I recommend Sun’s, I mean – Oracle’s Java. Java is a powerful, popular and widely used programming language. It was created by Sun Microsystems, and it now owned by Oracle. That being said, you can download a Java IDE from the Oracle site in the Sun Developer Network in flavors for Microsoft Windows, Linux, Solaris and Mac. You can download the NetBeans or the EE Bundle. Lastly, I recommend Python as a good beginner language. You can follow my article, Installing Python on Linux, or you can download Python 2.7.1 Windows Installer.

Fourth, get these tools and put them in your programmer’s toolbox: First, a printable PDF file called Python Cheat Sheet, and a C Style, or semicolon language Cheat Sheet for C, C++ and Java. Also, a printable Visual C++ Key Binding Poster, a Visual C# Key Binding Poster.

Fifth, use a good map to show you the way.  From the curriculum taught in “Introduction to Computer Science” at MIT (don’t ask), I gathered these topics you can reference to be on your way to learning programming:

  • Hello World
  • Operators
  • Types
  • Boolean
  • Conditional Statements (If Statements)
  • Loops
  • Methods and Functions
  • String Operations
  • List and Collections 

Sixth, practice, practice, practice. I can only take you so far. You can continue the DIY method, all you need to Google keywords, “learn” or “programming”, the topic, and the programming language, for example: “learn C# Operators“. And lastly, you need to practice programming. Once you mastered one language, you can graduate to more complicated Object-oriented programming and topics, like:

  • Objects
  • Inheritance
  • Polymorphism 

So far, this lesson has cost you nothing, except a computer which you probably already had. It doesn’t take much money or time to get started. Like I said before, if I knew then what I know now, I would have started on a semicolon, or C like programming language, I would have begun with free and open source software, or even just with free software IDE. I would have spent more time understanding arrays as a collection or loops. Whether you’re a high school graduate in Cocoa Beach, Fla. or an elementary school graduate in Colorado Springs, this is a good start. It’s a new era of learning, and you can be a new programmer at no cost.

0

Installing Python on Linux

I’m back. So last time I wrote, School of Hacks – Part 2, I emphasized the use or learning and understanding a UNIX/Linux Operating System. Based on the feedback, it goes without saying that learning Python is a popular response to learn next.

I plan to get you started on the right foot and in doing so, I anticipate we will program a strong password generator. The very first lesson of course, is this lesson: Installing Python on Linux. In part 2 of this series, I described the the meaning behind Linux distributions, most Linux distributions come with Python installed. However, I will show you how to install Python on Debian and Fedora Linux. I chose these two distributions because they are the two major distros other systems are based on. Ubuntu, Knoppix, Linspire and others are Debian based while Fedora is RPM based just as Mandriva, SUSE and all Red Hat versions.

RPM Based Python installation
  • Boot up your favorite RPM based Linux ditro, I’m using Fedora.
  • Go to the Python for Linux RPM page at http://www.python.org/download/releases/2.4/rpms/
  • Download the Binaries for Fefora Core 3, they are i386 RPM
  • When the download is complete, open a console and go to the python-2.4.2.4….. file
  • Make sure you have root access, otherwise type the following commands
localhost:~$ su
Password: [enter your root password]

  • Type in the following command
  • rpm -1 python2.4-2.4-1pydotorg.i386.rpm
  • you should get a message, to read message
cat /var/spool/mail/root | less

  • You should be able to start Python by typing Python on the console. This command can also be used prior to installation or to see the Python version currently installed.
Debian based systems could also already be pre-installed with Python. However, if need be, installing on a Debian based system might be a little easier.
Debian Based Python installation

  • Boot up your favorite Debian based ditro, I’m running Debian 5
  • Open a console and make sure you have root access, otherwise type the following commands
localhost:~$ su
Password: [enter your root password]
  • Type in the following command
localhost:~# apt-get install python

  • Do you want to continue? [Y/n] Y

  • At this point, you should be able to start Python on the console on a Debian based system.
Although this covers a broad range of distributions, the categories of Linux distributions also include Gentoo, a portage package distro.
If you can go to a console, and  type:

python
1+1

you should see and answer of 2, if so, you have properly installed Python on Linux and this should get you started in programming Python.
0

Blog at WordPress.com.

Up ↑

Design a site like this with WordPress.com
Get started