T-SQL: Schemabinding

A view is simply a virtual table or a stored select statement.

One option with creating views that I recently came across is the SCHEMABINDING option.
Basically, the SCHEMABINDING option “locks” the table design of a view’s underlying table(s) so that they cannot be changed. Without SCHEMABINDING, you could have the following scenario:

CREATE VIEW MyView
AS
SELECT ColumnA
, ColumnB
, ColumnC
FROM MyTable

Suppose you then alter the table MyTable to remove ColumnC. This would break the view MyView because it refers to the now non-existent ColumnC from MyTable.

If, on the other hand, we created MyView in the following way:

CREATE VIEW MyView WITH SCHEMABINDING
AS
SELECT ColumnA
, ColumnB
, ColumnC
FROM MyTable

and attempted to then delete column ColumnC from MyTable, we would get a warning message that this is not possible since the table is bound to view MyView. Note that this warning occurs whether you are removing or adding a column to the underlying table.

If you really did want to make a change to the table, you would first have to alter the view to remove the WITH SCHEMABINDING option, then make the table change.

T-SQL Escape Character

In T-SQL, I frequently use the LIKE clause when searching for a string in a VARCHAR or NVARCHAR column. For example, suppose I wanted to search a table that contains user comments for all strings that contain the word ‘cheque’ somewhere within the comment. I would simply use the following SELECT statement:

SELECT *
FROM dtComment C
WHERE C.Comment LIKE ‘%cheque%’

This would return all rows that contain the word ‘cheque’ anywhere within the comment.

Suppose I wanted to return all rows that contain the word ‘cheque’ followed by something in square brackets (i.e. ‘[' and ']‘). It turns out that the square brackets are reserved in T-SQL LIKE clauses to allow searching for a single character within a range of characters (See the LIKE description in MSDN) . The square brackets are used , so you cannot simply write :

SELECT *
FROM dtComment C
WHERE C.Comment LIKE ‘%cheque [%’

You need to escape the square bracket so that the LIKE clause actually searches for the character ‘[‘. The syntax for escaping the square bracket is the following:

SELECT *
FROM dtComment C
WHERE C.Comment LIKE ‘%cheque ![%’ ESCAPE ‘!’

Here, the ! is the escape character, and we use the ESCAPE keyword to tell T-SQL to interpret literally the character immediately following the escape character. So the above SELECT statement is essentially saying “Literally interpret the character directly following the exclamation point.”

 

 

Getting Married Today!

Well, I’m doing it. I’m tying the knot today. In less than 2 hours I will be a married man. We’re getting married at city hall. Surprisingly I’m not nervous. But I bet I will be in about an hour! It will be strange to think of myself as a married man.

Programming Topics I’d Like to Learn

The more I learn about programming, the more I’m aware of how much I still don’t know. I guess that’s the price of learning. In my head, I have this growing list of topics that I’m aware that I don’t yet fully understand. So I figured it’s high time I compile this list and post it here, once and for all. Of course, by the very nature of what this list is about, it is bound to grow at an accelerating rate as I become even more knowledgeable in programming. So I’m going to keep updating it as necessary.

As I begin to understand each topic on this list, I will mark the item as complete by changing the font to strikethrough . If a topic is particularly big or interesting to me, I might break it down in sub-topics and indicate that with a bulleted sub-list on this page.

Some of the topics here are pretty general (for example “ASP.NET”). If a topic is general, my intention for putting it on this list is that I get a decent, working-knowledge understanding of it. So, in the case of ASP.NET, I’d like to get good enough at it that I can build a small web application on my own in a relatively short period of time.

So here is the list, as of today, October 16, 2008.

 

 

• ASP.NET
• C#
  • Var
  • IEnumerable
• SQL Server
  • New features of SQL Server 2008
  • Reporting Services
  • Analysis Services
  • Integration Services
• C++
• Java
• J2EE
• Oracle
• DB2
• Sybase
• PostgreSQL
• MySQL
• Sharepoint
• PHP
• Javascript
• XML
• UML
• Perl
• SOAP
• Unit Testing
• Web Services
• VB Script
• MVC Framework
• jQuery
• VB
• LINQ
• Eclipse
• Subversion
• IIS
  • 5.1
  • 6.0
  • 7.0
• Apache
• django
• Python
• Shell scripting
• WPF
• WCF
• SVN
• Visual Studio
  • Advanced topics
• Flash
• Silverlight
• Expression Blend
• XAML
• Swing
• NetBeans
• Linux
• Unix
• Mobile Application Development
• Ruby
  • On Rails
• CSS
• Win Forms
• Web Forms
• Regex
• Ajax
• Dreamweaver
• Design Patterns
• Delphi
• Multithreading
• Object Oriented Analysis and Design
• ActionScript
• SmallTalk
• Namespaces
• Anonymous Types
• String Split
• Override
• Format Provider
• CultureInfo
• ToShortDateString
• Enumerable Range
• String.Join
• Dictionary
• Yield (IEnumerable)
• Interface
• Base Class
• Delegates/Events
• Middleware
• SMS/MMS
• WAP
• Framework
• What is a load balancer
• What is a wrapper class

Learning HTML, XHTML, HTML-DOM, Javascript and jQuery

Today, I’m going to learn all of the above. I already spent some time on Saturday reviewing HTML. It’s funny – it’s such a basic thing now, HTML, and yet I’ve never really taken the time to sit down and learn it. No wonder I get a bit confused when I come across certain things. I didn’t even know what XHTML is. Now I know. It is basically the “new and improved” HTML. It conforms to the XML syntax. It’s the default when creating new web applications in newer application development environments, like Visual Studio 2008.

As of right now, I’m going to review the HTML-DOM, or Document Object Model. I reviewed it a bit on Friday, but I really want to grasp it now. So, what is the HTML DOM?

In short, it is:

• A standard object model for HTML
• A standard programming interface for HTML
• Platform- and language- independent
• A W3C standard

The HTML DOM defines the objects and properties of all HTML elements, and the methods (interface) to access them.

The DOM models HTML as a set of node objects. The nodes can be accessed with Javascript or other programming languages.

JavaScript Objects

Follow the links to learn more about the objects and their collections, properties, methods and events. Contain lots of examples!

Object	Description
Window	The top level object in the JavaScript hierarchy. The Window object represents a browser window. A Window object is created automatically with every instance of a <body> or <frameset> tag
Navigator	Contains information about the client’s browser
Screen	Contains information about the client’s display screen
History	Contains the visited URLs in the browser window
Location	Contains information about the current URL

HTML DOM Objects

Follow the links to learn more about the objects and their collections, properties, methods and events. Contain lots of examples!

Object	Description
Document	Represents the entire HTML document and can be used to access all elements in a page
Anchor	Represents an <a> element
Area	Represents an <area> element inside an image-map
Base	Represents a <base> element
Body	Represents the <body> element
Button	Represents a <button> element
Event	Represents the state of an event
Form	Represents a <form> element
Frame	Represents a <frame> element
Frameset	Represents a <frameset> element
Iframe	Represents an <iframe> element
Image	Represents an <img> element
Input button	Represents a button in an HTML form
Input checkbox	Represents a checkbox in an HTML form
Input file	Represents a fileupload in an HTML form
Input hidden	Represents a hidden field in an HTML form
Input password	Represents a password field in an HTML form
Input radio	Represents a radio button in an HTML form
Input reset	Represents a reset button in an HTML form
Input submit	Represents a submit button in an HTML form
Input text	Represents a text-input field in an HTML form
Link	Represents a <link> element
Meta	Represents a <meta> element
Option	Represents an <option> element
Select	Represents a selection list in an HTML form
Style	Represents an individual style statement
Table	Represents a <table> element
TableData	Represents a <td> element
TableRow	Represents a <tr> element
Textarea	Represents a <textarea> element

Event Handlers

New to HTML 4.0 was the ability to let HTML events trigger actions in the browser, like starting a JavaScript when a user clicks on an HTML element. Below is a list of the attributes that can be inserted into HTML tags to define event actions.

Attribute	The event occurs when…	IE	F	O	W3C
onabort	Loading of an image is interrupted	4	1	9	Yes
onblur	An element loses focus	3	1	9	Yes
onchange	The content of a field changes	3	1	9	Yes
onclick	Mouse clicks an object	3	1	9	Yes
ondblclick	Mouse double-clicks an object	4	1	9	Yes
onerror	An error occurs when loading a document or an image	4	1	9	Yes
onfocus	An element gets focus	3	1	9	Yes
onkeydown	A keyboard key is pressed	3	1	No	Yes
onkeypress	A keyboard key is pressed or held down	3	1	9	Yes
onkeyup	A keyboard key is released	3	1	9	Yes
onload	A page or an image is finished loading	3	1	9	Yes
onmousedown	A mouse button is pressed	4	1	9	Yes
onmousemove	The mouse is moved	3	1	9	Yes
onmouseout	The mouse is moved off an element	4	1	9	Yes
onmouseover	The mouse is moved over an element	3	1	9	Yes
onmouseup	A mouse button is released	4	1	9	Yes
onreset	The reset button is clicked	4	1	9	Yes
onresize	A window or frame is resized	4	1	9	Yes
onselect	Text is selected	3	1	9	Yes
onsubmit	The submit button is clicked	3	1	9	Yes
onunload	The user exits the page	3	1	9	Yes

That’s all I’m going to say about HTML DOM. Now, onto Javascript.

What is JavaScript?

• JavaScript was designed to add interactivity to HTML pages
• JavaScript is a scripting language
• A scripting language is a lightweight programming language
• JavaScript is usually embedded directly into HTML pages
• JavaScript is an interpreted language (means that scripts execute without preliminary compilation)
• Everyone can use JavaScript without purchasing a license

JavaScripts in the body section will be executed WHILE the page loads.

JavaScripts in the head section will be executed when CALLED.

Update: 4:31 pm

So it turns out that Javascript is actually quite simple. There’s not much to it. That is the point of it I guess. It is supposed to be lightweight and fast. A few new things I learned about Javascript:

 

• There are two special statements that can be used inside loops: break and continue. The break command will break the loop and continue executing the code that follows after the loop (if any). The continue command will break the current loop and continue with the next value. 
• The javascript equivalent to C#’s foreach statement is for (variable in object)
• The complete Javascript Event Reference:
  FF: Firefox, N: Netscape, IE: Internet Explorer 

  Attribute	The event occurs when…	FF	N	IE
  onabort	Loading of an image is interrupted	1	3	4
  onblur	An element loses focus	1	2	3
  onchange	The user changes the content of a field	1	2	3
  onclick	Mouse clicks an object	1	2	3
  ondblclick	Mouse double-clicks an object	1	4	4
  onerror	An error occurs when loading a document or an image	1	3	4
  onfocus	An element gets focus	1	2	3
  onkeydown	A keyboard key is pressed	1	4	3
  onkeypress	A keyboard key is pressed or held down	1	4	3
  onkeyup	A keyboard key is released	1	4	3
  onload	A page or an image is finished loading	1	2	3
  onmousedown	A mouse button is pressed	1	4	4
  onmousemove	The mouse is moved	1	6	3
  onmouseout	The mouse is moved off an element	1	4	4
  onmouseover	The mouse is moved over an element	1	2	3
  onmouseup	A mouse button is released	1	4	4
  onreset	The reset button is clicked	1	3	4
  onresize	A window or frame is resized	1	4	4
  onselect	Text is selected	1	2	3
  onsubmit	The submit button is clicked	1	2	3
  onunload	The user exits the page	1	2	3

 

New M (or D) Programming Language

I just read something on Stackoverflow that there is a new programming language coming out from Microsoft called M. Or perhaps it’s called D. In either case, it’s a new modeling language currently in the works by Microsoft. 

I have no idea what it’s about. But for once, I’d like to be get to know something as soon as it comes out. I don’t want to be left behind on this one. All the other technologies currently in use have millions of experts worldwide. So the fact that I’m just getting started learning something that is already widely in use makes it a bit intimidating. 

Here is an opportunity to follow something as it gets more well-known and popular. I want to be in the know right from the start. So I’m going to follow this one closely. Who knows – it might become the next Java or C#. You never know. I’d like to know everything there is to know about it.

Here is a blog post about it:http://blogs.zdnet.com/microsoft/?p=1430

Who I want to be as a programmer

I want to be at level of mastery in programming such that I am extremely confident in my skills and abilities. This means that I have several hundred pieces of evidence that I have previously done an impressively good job in a programming assignment. I know from history that I have done things right and properly in a timely manner, and value was provided from my work. 

I am increasingly sought out for my knowledge in programming from my peers at work. People come to me for not only application-specific questions, but also for general “how do you” type questions for something I’m not even involved in. The words “ask Francois, he knows how to do that” or “he’s the go-to guy for that” are often heard at the office. I frequently spend time explaining concepts to coworkers and the conversation usually ends with “OK, I get it now. Thanks, Francois you’re a lifesaver”. I am never annoyed by these questions because not only am I happy to be considered so valuable at work, but I’m also happy to talk about something I am so interested in and passionate about. Programming is not only my job. It is also my hobby and my passion. I think about programming when I’m not at work – not because I’m stressed about it, but rather because I’m fascinated by it. It’s fun. I enjoy it. I love thinking about it. I’d do it even if I never had to work another day in my life. This is what I do in my free time because I love it. 

When I code I do it beautifully. I take pride in my work, and it shows. The programs I write are not just thrown together quickly so I can put the assignment behind me and relax and check out. Rather, they are each and every one of them exciting to me – new challenges, puzzles I can’t wait to attack because I know how much fun I get from taking a problem apart, thinking about a solution, trying it, testing it, correcting it, testing it again, and continuing the process until I get it right. To me, each new problem is not something to worry about – no, it is like reaching a new screen in a computer game I haven’t finished yet. I don’t yet know how I’m going to complete this. I just know that I eventually will, and it’s going to be really fun to try and figure out how to do it. I know it probably won’t happen on the first try and if it did then it really wasn’t all that fun to begin with (would I enjoy a video game that I could easily get through on the first try every new level?). Problems are just new, never-before-played levels. When you first start a video game and you’re not quite sure you like it yet, having too difficult a level early on is frustrating and makes you think “This game sucks”. So don’t take on something too difficult too soon. Take on small problems (like a simple form or method with parameters, etc.) and solve them fully and correctly until you can do it without thinking about it anymore. Then move on to something slightly more complex, like multiple forms that interact with each other. You’ll have to think about it for a few minutes, but after some thought you’ll get it and be confident in the solution. When you can do those problems easily without thinking about it, take on something even more complicated, like writing a small windows forms application. It might take you a little while, a couple of hours perhaps. But you’ll figure it out eventually, and you’ll know it works. Do it again, and try it a little differently. At this level of problem, there should be a few correct ways of solving the problem. Get through different methods of doing it. Even if you can’t wait to try something new. Be patient. Do this completely and do it well. Your career depends on it. 

Here’s how I do things now: I’m assigned a problem. I immediately make up that my performance and skillset is currently being questioned, and that so far I haven’t done anything impressive at work. I’m getting the job done, but I’m nobody special, and wouldn’t be missed if i left (from a productivity/skill value point of view anyway). So I’m being scared and paranoid. Then I think about how little I actually know. How, compared to everyone else at work, I know the least. Everyone is at least somewhat more skilled than me. I’m embarrassed and ashamed. This doesn’t leave me at all feeling confident. After a few minutes of wasting time surfing the web for nothing in particular, I say to myself “OK, you have to do this. You can figure this out”. But who I’m actually being about it at that moment is dreadful and unconfident. One of two things happens next.

1) Either I don’t solve the problem, and after a few nervous hours of just trying anything and everything to no avail, I give up and ask for help. At this point, I am again embarrassed and ashamed, and I really become self-loathing for a while. I tell myself I should have been able to get this. If I had been smart or a good worker, I would have figured it out. But no, I’m stupid and a bad worker. I am lazy and a fraud. I don’t deserve any of the rewards of being a good programmer or employee. I will never amount to anything because of what a loser I am. Maybe if I didn’t spend so much time smoking pot or masturbating, I would actually have a chance at being somebody in this life. But no, I am just a failure and will always be that way;

OR

2) I DO solve the problem, and am temporarily relieved that I “got through this one”. I’ve staved off discovery of my being a fraud for one more day. I’m safe again, temporarily. It’s never enough though. I never feel truly safe. I feel like I’ve just outrun the school bully and made it home safely. I’m safe for the night. I feel temporarily ok because I know nothing will happen tonight. But what I do feel is the underlying dread that tomorrow, I’m going to have to deal with this again and I don’t want that to happen. I feel scared, because i think I’m the only one at work who sucks so bad at his job. I feel utterly alone. Any temporary victories I have are out of luck, or because I did things right but just this time because I was just having a good day. 

 

All of this has to change. It starts with deciding who you want to be. Who I want to be is

confident
valuable
happy
proud
fun
patient
integrous

Now, decide what those things mean to you. How does it look to be these things at work in real life?

Confident - I know what I’m doing, and I know that I know what I’m doing. I believe in myself. I have a history of success and I know it. I believe that I can achieve anything, and that the time I take to achieve it was exactly how long it should have taken me. I believe that whatever I needed to do to achieve what I set out to achieve was exactly what I should have done. There was never any other “right way”. There is just the way I do it, and for me and my life, that’s the right way. 

Valuable - I make a difference. I make other peoples’ lives better. Without me, someone’s life would be of less quality. At work, I make a difference. I help people. I solve problems. I make something look and work nicer. I make things work faster. I make things work. My work is admired. It is useful to everyone who comes across it. 

Happy - I enjoy my life. I like being here. There is nothing else I’d rather be doing at this very moment. It’s so cool that I get paid to do this. 

Proud - My work is important to me. It’s an extension of who I am. It’s very important to me that my work be functional, easy to understand, maintainable and sustainable. It’s my craft. It’s my art. It’s my legacy. I have room for making mistakes. I don’t take mistakes lightly, but I don’t make myself wrong for them either. I learn from them. I know not to make the same mistakes again. I feel like I’m getting better and better every day. I love to show off the work I’ve done. I like talking about how I did it. I like talking about my ideas with other smart people, and potentially even seeing a better way to do it. 

Fun - I love programming. It’s like a game to me. A game I’m really, really REALLY good at. I don’t just play it to check out and relax though. It’s almost like a multiplayer online game with millions of players, and many of them are just beginners. A few have been playing a few weeks and are starting to get the hang of it. A few have played for several months now and are really becoming known for being good at one or two things in particular about the game. Even fewer are those have been playing for over a year – they are at among the highest levels in the game. They know what they’re doing. Then, there’s me. I’m at the highest levels. There are only a few others like me. I love being at the top. Everyone in the game wishes they were me. Some of them will be eventually, but right now, I’m at the top. It’s awesome. It feels powerful even. But mostly, it’s just so much fun being at this level. It’s like something really fun and ultimately meaningless that I nevertheless spent a good portion of my life on, and so it’s important to me. But still fun.

Patient - I know things take time to learn. I accept that. I know that I don’t know everything. I accept that. I have faith that I will eventually know what there is to know. However long it takes to get there is ok with me.

Integrous - I say what I’ll do and I do what I say. I make a plan for everything. I know what needs to be done before I start. Then I do it.

 

Now GO AND BE THOSE. Every one of them. Every day. You won’t get it right the first day. Or the second. You won’t feel like being them all the time. Be them anyway – just for practice for when you DO want to be them, you’ll be that much better at it by then. Give up any feelings of silliness or loserness or anger or impatient or anything else that might get in the way. Just be who you want to be. Every day. Without fail. All of them. You will suck at it at first. That’s ok. Do it anyway. You’ll be better at it in a week, but you’ll still suck. That’s ok. Do it anyway. You’ll be better in a month. And again in 3 months. At some point, it will actually start to feel natural to you. It might take a few weeks or months, but it will happen. And it will be so wonderful when it does, because now you can really take off with it. Now that it actually feels natural to be this way, you can start to perfect. You can start to experiment. Constantly step out of your comfort zone. Grow. Get better. Become more. You can do this. You are awesome. Your life depends on this. Your happiness depends on this. This is who you really are. Don’t waste another day. Today is the best day to do this. 

 

First Post

This is my first post on this new blog. What this blog will be about: Basically everything. All I want is a convenient medium to keep track of the daily goings-on of my life, as well as my thoughts, opinions, things I’ve learned and experienced, etc. What will be the subjects? Who knows? Programming, computers, politics, relationships, sex, dogs, drugs, the weather, books, exercise, nature, travel…whatever. All things that interest me. I have no desire for this blog to be something that everyone reads; I don’t care if anyone ever sees it or not. It’s for me entirely. I want to keep track of the things I find interesting.

That’s all. More later, fuckers!

Oh yeah – since I don’t care if anyone ever sees this, there will be lots of swearing and inappropriate shit on here…fuckers.