SQL Server 2008 Development

[Mel]

MICROSOFT SQL SERVER 2008 DEVELOPERS this one's for you.

The ink is dry on my SQL Server 2008 Administration course. Now the focus is on all the SQL Server developers. I plan to have my SQL Server 2008 Development course rolling off the presses by the end of summer. Developing robust data driven applications requires a range of skill sets. As a database developer you will need to understand what it takes to protect your organization's valuable data, provide fast, secure access to that data and support managed updates. This course will give you the keys to making that happen.

Okay, "The ink is dry" and "rolling off the presses" analogies don't apply to DVD courses and eLearning, but you get the point. Stay tuned for more updates.

¡Ciao!

[Mel]

Just having a little fun with SQL Server 2008 ....


Ranking Functions first made their SQL server debut in SQL Server 2005. The beauty of T-SQL and many of the other SQL Server add-ons is they can simplify your life as a DB developer. Ranking Functions are another example of this benefit. Ranking Functions produce meta data or data about your data. Having lots of information in your SQL database is great, but the real goal is to use your data to drive your business goals. That takes more than just inserting, updating and deleting rows of data. You need to have quick access to data based on various business needs. Let's say you are embarking on a new business campaign where you target one set of messages towards the top 3rd of your customers, another message towards customers in the middle 3rd sales volume range and on down the list. The order history information in your database can give you what you need, but you have to dig for it, do some number crunching and if you calculate it right you get your answers. So what do you think? Is this a common need? Perhaps. You could write a user defined function (UDF) or stored procedure to give you your answer, but you don't have to because SQL 2008 Server comes with a set of built-in functions related to this type of task.

The NTILE Function

WITH TotalPerOrder (OrderNumber, OrderTotal) AS
(
SELECT SALES.OrderDetails.OrderNumber,
SUM( CAST(SALES.OrderDetails.Quantity*SALES.Flowers.Sal esPrice AS smallmoney) ) AS OrderTotal
FROM SALES.Flowers, SALES.OrderDetails
WHERE SALES.Flowers.FlowerID = SALES.OrderDetails.FlowerID
GROUP BY OrderNumber
)

SELECT TotalPerOrder.*,
NTILE(3) OVER (ORDER BY OrderTotal) AS [Sales Groups]
FROM TotalPerOrder
ORDER BY [Sales Groups] DESC, OrderTotal DESC


What I have here is first a common table expression (WITH TotalPerOrder ) that returns the total for an order and then I use one of the built-in Ranking Functions NTILE to break all my order information in to groups.

http://forums.trainsignal.com/imagesCustom/SQL_NTILE_Chart.jpg

So using the NTILE function I can greatly simplify the task of pulling useful results from my SQL Server 2008 data.:cool:

[SQLDEV]

Hi

Any dates on when 'SQL Server Database Development Training' will be available.

It seems the availability of SQL Server 2008 Database Development Training material has been non-existent, Microsoft only released their version in May 2009. Even courses in my area/country have been cancelled due to only a
few candidates signing up for the course.


Regards
Kevin

[Lisa]

Sorry to hear you are having so much trouble getting SQL development training!

At this time Train Signal is on track to release the course in September.

:)Lisa

[Biggles77]

Just having a little fun with SQL Server 2008 ....

:eek::eek::eek: Mel, you need help........and fast!! :D Fun with SQL.....:rolleyes:

[Mel]

I know. I'm a little strange. It's an amazing thing however when you really like something that most people would never find interesting (such as database technology) and it's profitable too. ;)
I just love this country!

[zappydaddy]

Hi Mel,

I'm a big fan of your training videos, would like to ask some questions:

1. Once the database is created with or without Filegroups, is it still possible to go back to the database properties and change the file locations from its initial locations c:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA to another F:\DATA using the GUI?

2. Once the database maintenance is created to backup the database, how do I change or modify what it needs to backup?


3. Is there a way to create schema, and also can you change the table schema membership from dbo to other using the GUI or it has to be done via T-SQL?


Thanks in Advance

P.S.
If using SQL Server 2008 R2, Microsoft changed the program paths from c:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\ to c:\Program Files\Microsoft SQL Server\MSSQL10_5.MSSQLSERVER\MSSQL\

[Mel]

1. Once the database is created with or without Filegroups, is it still possible to go back to the database properties and change the file locations from its initial locations c:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA to another F:\DATA using the GUI?

The GUI has a Copy function. Once into it however there is a place where you can choose to Move the DB vs. Copy. I have not used it myself, but it is there. Right click on the DB name. Select Tasks and then click Copy. Give it a try and let me know what you find out.

3. Is there a way to create schema, and also can you change the table schema membership from dbo to other using the GUI or it has to be done via T-SQL?

To move a table to a new schema do the following: Right click the table. Choose Edit. Press F4 to bring up the Properties window. Choose a different schema from the schema drop down in the Properties window.

I don't have an answer handy for the second item on your list, but I am fairly certain there is something in BOL on that. Hope this helps!

[zappydaddy]

Hi Mel,

Thanks for the info, will try your suggestion and let you know, btw do you know if TS is planning to create training courses for BI?

P.S.
Thanks to your videos I was able to understand some basic stuff in regards to security and filegroups, you rock!!!

[Mel]

That is ultimately not my decision, but I am told that Train Signal SQL Server 2008 BI Training is currently in the queue for early 2010. Stay tuned!!!

[Mel]

Someone sent me this via email:

Hey Mel
Thanks for the trainsignal videos, they have been very helpful. Just a quick question on using INNER JOIN vs where clause, is there any big difference between the two? Any reason to use one over the other? In the Sql Server Database Development video there is not one INNER JOIN, just a personal preference? We have a bunch of snow in Southwest Colorado, been skiing yet?
Thanks

I kept away from inner vs outer vs cross vs full outer joins etc, etc, etc.
in that course because the where clause was the best route for an intro SQL course.

The following two statements produce the same results in our Veronicas database, but the first is more "english-like".

select C.FirstName, C.LastName, O.OrderDate
from SALES.Customers as C, SALES.Orders as O
where C.CustomerNumber = O.CustomerNumber and
C.State = 'IL'

select C.FirstName, C.LastName, O.OrderDate
from SALES.Customers as C inner join SALES.Orders as O
on C.CustomerNumber = O.CustomerNumber and
C.State = 'IL'

You can do a lot more with the various types of JOINS vs the WHERE clause. Once you start down the path of INNER JOINS you really should get into the others as well. Right now BI is next on my course list but an Advanced Developer course is not off the radar and would be a good place to include the types of joins.

Now on to ski conditions...
We have not been out to the slopes yet. Spent a few days in Chile last fall. Hopefully we can hit the rockies first quarter 2010.:cool:

[greenmatter]

http://www.imgsnatch.com/img/w/N.gifThat is ultimately not my decision, but I am told that Train Signal SQL Server 2008 BI Training is currently in the queue for early 2010. Stay tuned!!!

Looking forward to it-

[Space1202]

Sounds great and look forward to it.
And I can say, we have been getting great early season here in the Sierras :)
Skiing is going great!
http://www.thepictureshare.com/img/4/B.jpg

[zappydaddy]

Hi Mel,

Would like to know if the BI course will include everything (Analysis, Reporting and Integration services) or it will be one course for each?