In the simplest terms a Foreign Key in one table refers to the Primary Key of another table. Let’s go back to our demo tables products and productSales. –Products: productName price quantity color ———– —– ——– —– pencil 0.25 100 yellow pen 0.99 73 blue paper 1.00 500 white Mountain Dew 1.25 8 green –prodctSales:…
Tag: T-SQL
SQL 102 – Users
Before you got started working with SQL Server, someone had to set up a user account for you. Now that you’re learning to become a database administrator you need to learn to allow others to have access to SQL Servers. Granting a user access to a database takes three steps. First, create a login, second…
SQL 201 – Views
Previously I showed you how to create tables. The normal goal for designing a table is to store data in such a way that each table contains one group of facts that are highly related to each other. For example, if you had built a contact database, you’d have one table for people, one for…
SQL 201 — User Defined Functions
Back in SQL 101, I introduced you to some functions, GETDATE(), ISNULL(string1, string2), etc. All of these functions come defined in Microsoft SQL before you even install it on your machine. But that’s just the beginning. You can create your own functions to do tasks too! There are two types of user defined functions, table…
TOP PERCENT
We have a multi-threaded process that grabs it’s fair share records and then processes them. It basically looks at the total number of records to process, and takes the top 10% of the records and marks them so that no other process will grab those records. If you haven’t done a lot with TOP, then…
Beginning SQL Server 2005 Programming
I was looking for a good reference book to use in creating training lessons for the site. I have to say this book is great for that. Lucky for me, it’s not as good at actually teaching you. I’d say it does a little better than the Books On Line, but not by a lot….
Determine the Space Used by Tables
Eventually you’ll be faced with running out of space on a SQL server. Usually, before you get more drive space, you’re asked to find out if you can get rid of any information you’re holding onto in the server. You come up with a list of tables in databases that you feel you could remove…
Inter-Database Queries
I’ve had this question come up a few times now. “How do I copy data from one database to another?” It’s after getting a question like that, I explain fully qualified database object names. That’s a mouthful! Basically it’s a fancy way of saying the “full name” of a database object, like a table or…
SQL 201 — CREATE PROCEDURE
Now that you have the basics of SQL, I think it’s time to learn something a bit more involved. If you need to accomplish a task within SQL you will run a series of T-SQL statements. If you want to run those same steps over and over again, you could save them to a file,…
SQL 201 – CREATE TABLE
CREATE TABLE I’ve showed you how to get data out of a table, put data in, change it, and delete it. But I haven’t showed you how to create your own table….until now. CREATE TABLE tableName ( columnName <datatype> ) This statement is pretty straightforward, you have to choose the name of the table, and…