Quick introduction to SQL, databases and relational theory

Many people find it daunting when they think about Databases and SQL. I know I found it very scary in the beginning and my early experiences with Database developers left me feeling very small and I thought that maybe I was not good enough to succeed in this field.

However, I was luckily given a quick 30 minute overview from one of the senior developers and he gave me a whole load of terms to research and understand. This gave me the foundation of my Database career and I hope that I can provide the same information to whomever reads this post.

What is a database?

A database is an organised collection of data. Databases are used to store all sorts of information like academic records, medical history, sports statistics and many many more areas.

Why use databases?

The main reasons for using databases are
• Data consistency and ability to enforce data rules
• Ability to easily retrieve large amounts of data
• Ability to manage security
• Ability to open up and share the data to more users

In the past one of the main criticisms about databases has been that they were slow but improvement in both hardware and software have eliminated this issue.

What are tables, rows and columns?

A table is similar to a spreadsheet where the data is held in an organised and structured way which is comprised of columns and rows.

Each column contains a set of similar types of data such as post codes, dates of birth or colours

A row is simply data or information for the columns. For example, if the column is “Grades” each row may be comprised of “A-E”.

What is null?

A null is a missing or unknown value. It is not a zero, empty text string or one or more blank spaces.

Nulls occur when a value is not added to a column when the record is made.

What is normalisation?

Normalisation is the process of organising data in tables and columns to both reduce the amount of  data redundancy and to improve the data integrity in the database.

What is a primary key?

These are a unique attributes which identifies each row. These are generally a number increasing in a sequential manor but they can be comprised of other data. The primary key will identify that record throughout the database and helps maintain data integrity. An example of a primary key is a Customer ID.

What is a foreign key?

When you want two tables to have a relationship with one another normally this is done by replicating the primary key into another table which thus creates a parent-child table relationship. By enforcing the relationship the referential integrity is maintained.  A classic primary and foreign key example is Customer and Order details.

What are relationships?

These are how you connect information between tables and they compromise of three types

  • One to one
  • One to many
  • Many to Many

What is a view?

A view is a virtual table which is comprised of one or more base tables. It is not a physical table as it retrieves information from base tables.

What is an index?

An index is an object which helps speed up the retrieval of data. It is specified on one or more columns and there are quite a few different types of indexes. A simple way of thinking about an index is to think about the index or contents page of a book which helps direct you to the correct page without searching through each page individually.

 

My top tips for new programmers

Over the years I have written quite a lot of code. These are the tips that I wish I had known when I had started out and would have saved me a lot of head scratching and restless nights:

  • Create a “to do” list. Always write down your tasks and keep it updated as you work through the problem. Your memory is limited and you can often forget something which may be critical later.
  • Always use variables and don’t hardcode. Generally this will make your life easier and it makes the application more flexible.
  • Code comments are your friend. Many times I returned to code I have written and never known what a function or piece of code is expected to do. If I had taken some time out to put some comments into the code it would have saved me hours troubleshooting issues.
  • Always break a big problem into a series of small problems. Sometimes a big problem can feel daunting and be more difficult than it seems. Break the problem down into smaller blocks and then solve each block one at a time. It’s okay to skip a block and return to a previous block as it may give you clues to fixing the skipped block.
  • Errors are not a bad thing they tell you where the problem is. This will help you to solve the error more easily and doesn’t mean you’re a bad programmer. Only be worried when something goes wrong and you don’t get an error message.
  • Never reinvent the wheel. It is more than likely someone has faced the same problem as you and it is okay to copy their solution.
  • A difficult problem doesn’t mean you’re a failure. There will be times when you face a problem which you find hard to grasp. It is perfectly acceptable to find things difficult.
  • Know who to contact in your ogranisation to ask questions. When you have very little information and need more details it is infinitely better when you have a list of go to people for all the applications you will be supporting.
  • 99% of your work will never be seen by anyone. Your solution may not necessarily be the most elegant one but try to keep it simple.
  • Prototypes do not have to always end up in production. Building things allows us to learn from coding. What you learnt is better than whether it ends up in production. Sometimes it is better to start again than fix problems with some poorly written code.
  • Read code of more experienced programmers. When you get passed some code don’t just deploy it. Read it and review it. You might learn something or you might pick up a problem which will save everyone time.
  • Don’t stop learning. Programming can change quite rapidly. If you don’t invest in learning new things you might end up encountering it for the first time when you are debugging something in production

Feel free to comment with any additional tips which you may have.

 

Book Review: Learn Windows PowerShell in a Month of Lunches, Second Edition by Don Jones

During December I decided to dedicate myself to learning Powershell for the next few months. Many of the blogs I follow and some of my respected peers have recommended the Learn Windows PowerShell in a Month of Lunches book and ashamedly I have owned the book and never gotten past the first chapters.

The reason I never did do much with Powershell is because I wasn’t sure it was worth my time and wasn’t sure how I could apply it. However, after seeing the great work of Chrissy LeMaire on DBATOOLS and Rob Sewell at DBA Reports I knew that I could make my life a lot easier. I know from their work that they have done a very large amount of work which I did manually could be automated. Therefore I now had sufficient motivation  to start my Powershell journey.

The book is compromised of the following chapters:
1 Before you begin
2 Meet PowerShell
3 Using the help system
4 Running commands
5 Working with providers
6 The pipeline: connecting commands
7 Adding commands
8 Objects: data by another name
9 The pipeline, deeper
10 Formatting—and why it’s done on the right
11 Filtering and comparisons
12 A practical interlude
13 Remote control: one to one, and one to many
14 Using Windows Management Instrumentation
15 Multitasking with background jobs
16 Working with many objects, one at a time
17 Security alert!
18 Variables: a place to store your stuff
19 Input and output
20 Sessions: remote control with less work
21 You call this scripting?
22 Improving your parameterized script
23 Advanced remoting configuration
24 Using regular expressions to parse text files
25 Additional random tips, tricks, and techniques
26 Using someone else’s script
27 Never the end
28 PowerShell cheat sheet 

Chapter By Chapter Review

1. Before you begin

This is a nice short intro into Powershell and shouldn’t take anyone long to finish.

2 Meet PowerShell

This chapter is mainly about customising the GUI/ISE to whatever your preference is and along with first chapter can probably be done in one lunch.

3. Using the help system

I loved this chapter as the key success within Powershell is becoming familiar and efficient with the Help system and this chapter gives you those skills.

4 Running commands 

Finally we are doing something and in this chapter you are introduced to some simple one liners and how they can be constructed.

5 Working with providers 

In this chapter you learn all about PSproviders.

6 The pipeline: connecting commands 

The pipeline is a critical component of Powershell and is explained in an eloquent way.

7 Adding commands 

Now we start dooing some real stuff with Powershell and I found the Lab at the end particularly enjoyable.

8 Objects: data by another name 

A key concept within Powershell is everything is an object and it is explored in this chapter.

9 The pipeline, deeper 

More awesome Pipeline stuff.

10 Formatting—and why it’s done on the right 

Finally I learned how people were making there reports with Powershell and now I had that knowledge.

11 Filtering and comparisons 

Does what it says on the tin.

12 A practical interlude 

I loved this chapter as it was a series of challenges which helped give me confidence with my Powershell knowledge.

13 Remote control: one to one, and one to many

After this chapter I was a little scared about how easy it would be to hack on a large scale using Powershell.

14 Using Windows Management Instrumentation 

WMI had always scared me and now it doesn’t after using it and its newer CIM implementation.

15 Multitasking with background jobs 

Does what it says on the tin.

16 Working with many objects, one at a time 

Finally we get to the FOREACH command.

17 Security alert!

After this chapter some of my earlier hacking concerns were a little alleviated.

18 Variables: a place to store your stuff

I liked this chapter as I finally feel like we are properly starting to script.

19 Input and output

I wasnt too sure about this chapter as it contained quite basic information  and probably coulkd have been added to another chapter.

20 Sessions: remote control with less work

I understand how sessions work but I am not sure it needed a whole chapter.

21 You call this scripting? & 22 Improving your parameterized script

Finally we are doing some really cool stuff and it is quite surprising how easy it is to create scripts for Auomating Administration tasks.

23 Advanced remoting configuration

I must admit I read the chapter and quickly forgot what was contained in this chapter as I doubt I will need to remember all that detail.

24 Using regular expressions to parse text files

My hatred of regular expressions has now extended to another programming language.

25 Additional random tips, tricks, and techniques

A nice few tidbits of information.

26 Using someone else’s script

A great chapter which is a must read.

27 Never the end

A nice little send off into the big bad world with some Powershell.

28 PowerShell cheat sheet 

A nice little reminder of things which has been covered.

In conclusion this book was a great start to my Powershell journey and I am now working through the Learn PowerShell Toolmaking in a Month of Lunches.

Why you should learn programming

I remember when one of my friends got a summer job where he was asked to remove certain special characters from filenames and replace it with an underscore. He complained it was boring and tedious but it paid well so he stuck at it.

He was expected to do a certain amount of files per hour and he was determined to hit that target as soon as he could so that he could spend the remainder of the hour surfing the internet. The internet at that time was still a rarity and he decided he needed to find a quicker way to complete his work so that we could free up more time.

After much searching and lots of testing he wrote a simple program which renamed the files for him. Unfortunately for him some of the code he cut and pasted without properly understanding its function looped through all the directories on that particular drive completing weeks of work into a few hours. He was shocked at the speed, efficiency and most devastating of all the prospect of losing his job.

He plucked up some courage and showed his manager what he had done. His manager was astounded at his cleverness and told him if he could setup the process to run daily and then maintain it ongoing he would employ him part time permanently. This led to a fruitful source of income as over the next few years he did very little modification to the system whilst picking up a monthly paycheck.

This is the power of programming and why you should learn programming  as with only an entry level of knowledge anyone can recreate the program my friend had created.

I bet there are dozens of daily tasks which you do which could similarly be automated like

  • moving files
  • downloading files
  • giving custom notifications in response to events
  • manipulating excel sheets
  • importing and exporting data
  • and hundreds of more similar tasks.

Think about how much time you could save and how much you could benefit by automating things in your life. Hopefully the benefit you could receive is enough to answer Why you should learn programming.

What would you like to learn or for me to talk about?

Hi all.

Just a quick blog post to say that I would like to thank everyone who has visited this site, sent me emails or comments on social media. It makes all my efforts worthwhile and further motivates me to continue blogging.

Now I would like to reach to everyone and find out what they would like to learn more about. This helps me to cater for my audience and give me new ideas for future blog posts.

Thanks once again to everyone and have a great day.

 

 

How to change the collation of a database in SQL Server?

Have you ever wondered how to change the collation for a database in SQL Server?

Well the other day I received a database from a third party supplier and the first step was to change the Collation of the database. My questioning about why they would supply the database in the incorrect collation setting was met with a wall of silence. Therefore I had to put in my documentation how to change the Collation of the database.

I did it the quick way by right click on database > Select properties > options > Select appropriate collation from dropdown menu next to Collation.

Database properties
Database properties

That was a good enough solution at the time but for my documentation I would always add the T-SQL so that anyone could follow the guide without making mistakes.

Below is an example of how to change the Collation via T-SQL

ALTER DATABASE AdventureWorks2012 COLLATE SQL_Latin1_General_CP850_BIN

Please let me know whether you found this guide useful and leave a message in the comments.

SQL Server Collations Find current collation and all supported collations

Have you ever needed to check your database or SQL instance to find out its collation? Have you ever wanted to check whether a collation is supported by Windows or SQL Server?

Well I am going to show you how to find out the information quickly and easily.

The below query will show the instance level collation.
SELECT SERVERPROPERTY(‘collation’)

1

The query below will show the collation setting at the database level

 

SELECT DATABASEPROPERTYEX(‘AdventureWorks2012’, ‘Collation’) SQLCollation

2

Sometimes its important to know the collations which the OS supports and the following query will help list all the collations which are supported by the OS.

 

SELECT * FROM sys.fn_helpcollations() WHERE name NOT LIKE ‘SQL%’;

3

When doing migrations it is very important to ensure that the Collation setting is maintained and to know whether it is supported by the SQL instance. The below query will help you easily and quickly ascertain that information.

 

SELECT * FROM sys.fn_helpcollations() WHERE name LIKE ‘SQL%’;

4

 

Hopefully that covers all your collation gathering requirements but if you do have another technique which I have missed then please leave them in the comments below.

 

Generate random numbers in PowerShell with Get-Random

Generate random numbers in PowerShell with Get-Random

Have you ever wanted to generate random numbers for passwords, test data…. and wondered how you could easily generate them?

Well I was confronted by the same situation on one of our servers where I needed some random numbers for inputting wait times to test an application but didn’t have my usual tool of choice ( MS Excel ) available to me.

I remembered the Get-Random command from memory but used the Get-Help command to allow me to use the correct syntax.

Executing Get-Random will produce a random number.

get-random
get-random

 

This was quite a good start but then I needed only 4 digit figures and I then had to use the minimum and maximum parameters.

Get-Random -Maximum 9999 -Minimum 1000

2

The above command solved that problem for me and I think it reflects how easily readable the PowerShell language is.

The last scenario I had was producing random numbers for some temporary passwords. I looked at the help for Get-Command and noticed the inputobject and count parameters which I thought would fulfil my need.

Get-Random -InputObject (10000..99999) -Count 3

3

The above command shows the range of values which I gave and the number of random numbers I needed in the Count parameter.

Hopefully the above will help you with your random number needs. Please add your feedback or other solutions in the comments below.

 

ASCII String function in SQL Server

Have you wondered what the ASCII value of a character is whilst using SQL Server? Or needed to quickly check the ASCII value? Or wondered how the ASCII Function worked?

Well the other day I saw one of the developers using the ASCII function which I was not familiar with so I decided to a bit of exploring.
SELECT ASCII( ‘A ‘)
SELECT ASCII( ‘B ‘)
SELECT ASCII( ‘C ‘)
SELECT ASCII( ‘D ‘)
SELECT ASCII( ‘E ‘)
SELECT ASCII( ‘F ‘)
1
The above query shows how easy it is to return ASCII values.
2
The second query shows how you can get the ASCII values from values within a column.
I hope that you liked the above post and hope that you can tell me about your adventures with the ASCII function.

 

List Hotfixes installed with PowerShell with Get-Hotfix

Have you ever wondered what updates have been applied to your Windows operating system?

Well a quick and easy way to do that is to use the PowerShell command Get-Hotfix. This will list out the description, the HotfixID and when it was installed.

I used this on a few PCs where it appeared that updates were not being applied and it showed me when the last updates were installed and hence helped me identify the PCs which were having the trouble and disconnect them from the network.

1