Microsoft Corp. today announced key milestones achieved within the business intelligence (BI) marketplace, including IDC’s recognizing Microsoft as one of the fastest-growing BI vendors in 2006. In IDC’s report, “Worldwide Business Intelligence Tools 2006 Vendor Shares,”* analysts found that Microsoft had a growth rate of 28 percent, the highest among the top 10 industry vendors. In addition, Microsoft® SQL Server™ 2005 was acknowledged by The OLAP Report as the No. 1 online analytical processing (OLAP) server on the market.
Microsoft’s continued investment in delivering high-performing, low-cost BI solutions for all business users via the Microsoft Office experience and SQL Server 2005 has customers taking notice.
“Like most companies, we experience a constant flow of change. Having the ability to monitor my business, analyze key business data, act on it and see real-time results gives me the competitive edge my business needs to survive,” said Michael Saunders, corporate vice president and chief information officer for Kellwood Co. “Microsoft has given us the ability and confidence to allow our employees at all levels to make informed decisions.”
SQL Server has established itself as an enterprise-class data platform. A recent BZ Research study found that 74.7 percent of enterprises use SQL Server, compared with 54.5 percent for the nearest competitor.
The Rise in Demand for Pervasive Business Intelligence
In its analysis, IDC states that the BI market grew by more than 11 percent in 2006. The BI tools market continues to be driven by the need for improved performance management and, to a lesser extent, compliance. “Performance management can take on the form of various decision-support and reporting functions to improve revenue, profit and operational efficiency; decrease costs; uncover new opportunities; or mitigate risk,” said Dan Vesset, an analyst with IDC. “Microsoft had another strong year in this market.”
To give businesses a step in the right direction, Microsoft will launch Microsoft Office PerformancePoint™ Server 2007 on Sept. 19, and SQL Server 2008 is scheduled to ship in the second quarter of 2008. Customers are invited to attend an event on Sept. 20 to learn more about Office PerformancePoint Server 2007. More information about the event is available at http://www.windowsitpro.com/roadshows/performancepoint.
“We are focused on helping our customers achieve higher levels of business performance by allowing them to take advantage of the rich BI capabilities offered through Microsoft business intelligence,” said Chris Caren, general manager of Office Business Applications at Microsoft. “We are excited to help companies integrate efficiently with one another to ensure all employees are held accountable for their actions across the entire business.”
In preparation for the upcoming releases, Microsoft issued its fourth and final community technology preview (CTP) for Microsoft Office PerformancePoint Server 2007 as well as the July CTP for SQL Server 2008.
The CTP program has over 10,000 active members. The latest preview of PerformancePoint Server 2007 is available to the public at https://connect.microsoft.com/site/sitehome.aspx?SiteID=181&wa=wsignin1.0, and enables customers and partners to preview and provide feedback on the latest pre-release versions of both products. The SQL Server 2008 July CTP can be downloaded at http://connect.microsoft.com/sqlserver. Feedback received from customers and partners during previous CTP programs has led to significant improvements in both products and closely aligned them with the needs of customers.
Bridging the BI Divide
Both releases promise significant enhancements to the BI landscape. PerformancePoint Server 2007 helps organizations align their processes by streamlining into a single application the monitoring, analysis and planning activities needed to improve business performance. By deploying PerformancePoint Server 2007, businesses can achieve better results by allowing individuals across the organization to improve performance. SQL Server 2008 will help organizations deliver a more secure, reliable data platform for storing business-critical information and delivering the right information to all users, while reducing the time and cost of managing data.
PerformancePoint Server is tightly integrated with the familiar and easy-to-use Microsoft Office system, allowing organizations to better align employees across divisions and make them accountable for their actions. In addition, PerformancePoint Server 2007 takes advantage of the enterprise-grade reliability, high performance, security technology and scalability of SQL Server 2005, enabling more people at all levels of the organization to transform disparate enterprise data into shared information they can use to make decisions and take actions that improve business outcomes.
“TCS and Microsoft are working together to invest in developing industry-leading, world-class BI solutions that enable our customers to experience certainty,” said Santosh Mohanty, global director and head of the Business Intelligence and Performance Management practice at Tata Consultancy Services Ltd. “We are seeing significant market momentum and double-digit growth for BI solutions built on the industry-leading Microsoft technology stack. This growth, being fueled by demand from some of the largest corporations in the world, has enabled us to aggressively push our strategic objective of building a $100 million BI practice on the Microsoft technology stack.”
* Source: IDC, Doc #207422, June 2007
Tuesday, August 21, 2007
SQL Server 2008 Live Meeting Event on August 21st on DateTime function
Don't forget, today is the day of the SQL Server 2008 Live Meeting Event. Here is what I got in my inbox:
We wanted to remind you that on August 21st at 11am Pacific, that we will holding our 3rd Live Meeting event of the month of July. Join Michael Wang as he explains all the interesting features the new function "DateTime" will include. We will some examples of how "DateTime" will work in SQL Server 2008, and will be fielding all your questions as well. So make sure you get there early for all the fun!
New Datetime Data Type
08/21/07 @ 11am Pacific
We wanted to remind you that on August 21st at 11am Pacific, that we will holding our 3rd Live Meeting event of the month of July. Join Michael Wang as he explains all the interesting features the new function "DateTime" will include. We will some examples of how "DateTime" will work in SQL Server 2008, and will be fielding all your questions as well. So make sure you get there early for all the fun!
New Datetime Data Type
08/21/07 @ 11am Pacific
Monday, August 20, 2007
Do you know how NULLIF and non-deterministic functions work?
Run this first
CREATE TABLE #j (n varchar(15))
DECLARE @a int
SET @a = 1
WHILE @a <= 1000 BEGIN
INSERT #j
SELECT NULLIF(REPLICATE('1', RAND()*2) , ' ')
SET @a = @a + 1
END
Go
After that is done run this query
SELECT * FROM #j WHERE n = ' '
You will get back between 200 and 300 rows. What just happened? In our insert we use this NULLIF(REPLICATE('1', RAND()*2) , ' ')
What this does is the following: if REPLICATE('1', RAND()*2) equals ' ' then it will insert a NULL, so where do the blanks come from? Well let's find out.
Run this
SET SHOWPLAN_TEXT ON
SELECT NULLIF(REPLICATE('1', RAND()*2) , ' ')
and we see the following
--Compute Scalar(DEFINE:([Expr1000]=If (replicate('1', Convert(rand(NULL)*2))=' ') then NULL else replicate('1', Convert(rand(NULL)*2))))
This can also be written like this
SELECT CASE WHEN REPLICATE('1', RAND()*2) =' '
THEN NULL ELSE REPLICATE('1', RAND()*2) END
See what happens? First SQL evaluates if REPLICATE('1', RAND()*2) is ' ' if that is not ' ' then the same code gets executed again and thus could be a blank
If you use a variable this can never happen.
Here is an example, keep hitting F5 and you will see it will never be blank
DECLARE @val float
SET @val = RAND()
SELECT NULLIF(REPLICATE('1', CONVERT(int, @val*2)) , ' ')
CREATE TABLE #j (n varchar(15))
DECLARE @a int
SET @a = 1
WHILE @a <= 1000 BEGIN
INSERT #j
SELECT NULLIF(REPLICATE('1', RAND()*2) , ' ')
SET @a = @a + 1
END
Go
After that is done run this query
SELECT * FROM #j WHERE n = ' '
You will get back between 200 and 300 rows. What just happened? In our insert we use this NULLIF(REPLICATE('1', RAND()*2) , ' ')
What this does is the following: if REPLICATE('1', RAND()*2) equals ' ' then it will insert a NULL, so where do the blanks come from? Well let's find out.
Run this
SET SHOWPLAN_TEXT ON
SELECT NULLIF(REPLICATE('1', RAND()*2) , ' ')
and we see the following
--Compute Scalar(DEFINE:([Expr1000]=If (replicate('1', Convert(rand(NULL)*2))=' ') then NULL else replicate('1', Convert(rand(NULL)*2))))
This can also be written like this
SELECT CASE WHEN REPLICATE('1', RAND()*2) =' '
THEN NULL ELSE REPLICATE('1', RAND()*2) END
See what happens? First SQL evaluates if REPLICATE('1', RAND()*2) is ' ' if that is not ' ' then the same code gets executed again and thus could be a blank
If you use a variable this can never happen.
Here is an example, keep hitting F5 and you will see it will never be blank
DECLARE @val float
SET @val = RAND()
SELECT NULLIF(REPLICATE('1', CONVERT(int, @val*2)) , ' ')
Article: LINQ to SQL (Part 6 - Retrieving Data Using Stored Procedures)
LINQ to SQL is a built-in O/RM (object relational mapper) that ships in the .NET Framework 3.5 release, and which enables you to model relational databases using .NET classes. You can use LINQ expressions to query the database with them, as well as update/insert/delete data.
This is the 6th article that Scott Guthrie has written about LINQ. The first 5 can be found below.
Part 1: Introduction to LINQ to SQL
Part 2: Defining our Data Model Classes
Part 3: Querying our Database
Part 4: Updating our Database
Part 5: Binding UI using the ASP:LinqDataSource Control
What is covered in the 6th article?
To SPROC or not to SPROC? That is the question....
The Steps to Map and Call a SPROC using LINQ to SQL
How to Map a SPROC to a LINQ to SQL DataContext
How to Call our Newly Mapped SPROC
Mapping the Return Type of SPROC Methods to Data Model Classes
Handling SPROC Output Parameters
Handling Multiple Result Shapes from SPROCs
Supporting User Defined Functions (UDFs)
Summary
Read the article here: http://weblogs.asp.net/scottgu/archive/2007/08/16/linq-to-sql-part-6-retrieving-data-using-stored-procedures.aspx
This is the 6th article that Scott Guthrie has written about LINQ. The first 5 can be found below.
Part 1: Introduction to LINQ to SQL
Part 2: Defining our Data Model Classes
Part 3: Querying our Database
Part 4: Updating our Database
Part 5: Binding UI using the ASP:LinqDataSource Control
What is covered in the 6th article?
To SPROC or not to SPROC? That is the question....
The Steps to Map and Call a SPROC using LINQ to SQL
How to Map a SPROC to a LINQ to SQL DataContext
How to Call our Newly Mapped SPROC
Mapping the Return Type of SPROC Methods to Data Model Classes
Handling SPROC Output Parameters
Handling Multiple Result Shapes from SPROCs
Supporting User Defined Functions (UDFs)
Summary
Read the article here: http://weblogs.asp.net/scottgu/archive/2007/08/16/linq-to-sql-part-6-retrieving-data-using-stored-procedures.aspx
Monday, August 13, 2007
SQL Server 2008 Webchat and Live Meeting Series
SQL Server 2008 July CTP Web Chat
July CTP Public Web Chat
08/13/07
Add to my Calendar
SQL Server 2008 July CTP Live Meeting Schedule
Enterprise Scale Reporting Engine
08/15/07
AS Time Series Stability
08/17/07
New Datetime Data Type
08/21/07
Ordpath For SQL Server
08/28/07
July CTP Public Web Chat
08/13/07
Add to my Calendar
SQL Server 2008 July CTP Live Meeting Schedule
Enterprise Scale Reporting Engine
08/15/07
AS Time Series Stability
08/17/07
New Datetime Data Type
08/21/07
Ordpath For SQL Server
08/28/07
Friday, August 10, 2007
Summer SQL Teaser #12 Missing Dates
Stole this tease from my friend Mark.
The teaser for this week is not really a teaser, this time you will have to write some code instead of guessing/knowing.
First create this table of numbers
SET NOCOUNT ON
CREATE TABLE numbers(num int primary key)
DECLARE @l int
SELECT @l =0
WHILE @l <= 1000 BEGIN
INSERT numbers VALUES(@l)
SET @l = @l + 1
END
GO
Below is a table, return all the dates for July 2007 where Joe does not have a LoginDate entry. So in this case the output is 28 rows
DECLARE @LOGIN TABLE (id int IDENTITY(1,1), Name varchar(20), LoginDate datetime )
INSERT @LOGIN VALUES ('Joe', '20070701')
INSERT @LOGIN VALUES ('Joe', '20070710')
INSERT @LOGIN VALUES ('Joe', '20070720')
INSERT @LOGIN VALUES (Fred, '20070702')
INSERT @LOGIN VALUES (Fred, '20070707')
INSERT @LOGIN VALUES (Fred, '20070711')
Expected output
Joe 2007-07-02 00:00:00.000
Joe 2007-07-03 00:00:00.000
Joe 2007-07-04 00:00:00.000
Joe 2007-07-05 00:00:00.000
Joe 2007-07-06 00:00:00.000
Joe 2007-07-07 00:00:00.000
Joe 2007-07-08 00:00:00.000
Joe 2007-07-09 00:00:00.000
Joe 2007-07-11 00:00:00.000
Joe 2007-07-12 00:00:00.000
Joe 2007-07-13 00:00:00.000
Joe 2007-07-14 00:00:00.000
Joe 2007-07-15 00:00:00.000
Joe 2007-07-16 00:00:00.000
Joe 2007-07-17 00:00:00.000
Joe 2007-07-18 00:00:00.000
Joe 2007-07-19 00:00:00.000
Joe 2007-07-21 00:00:00.000
Joe 2007-07-22 00:00:00.000
Joe 2007-07-23 00:00:00.000
Joe 2007-07-24 00:00:00.000
Joe 2007-07-25 00:00:00.000
Joe 2007-07-26 00:00:00.000
Joe 2007-07-27 00:00:00.000
Joe 2007-07-28 00:00:00.000
Joe 2007-07-29 00:00:00.000
Joe 2007-07-30 00:00:00.000
Joe 2007-07-31 00:00:00.000
Your task is to write code that will return that resultset.
1) you have to use the number table I provided
2) you cannot use a calendar table
Shortest code wins, I got mine down to 207 characters (and no, it is not all in 1 line)
I will post my code tomorrow
The teaser for this week is not really a teaser, this time you will have to write some code instead of guessing/knowing.
First create this table of numbers
SET NOCOUNT ON
CREATE TABLE numbers(num int primary key)
DECLARE @l int
SELECT @l =0
WHILE @l <= 1000 BEGIN
INSERT numbers VALUES(@l)
SET @l = @l + 1
END
GO
Below is a table, return all the dates for July 2007 where Joe does not have a LoginDate entry. So in this case the output is 28 rows
DECLARE @LOGIN TABLE (id int IDENTITY(1,1), Name varchar(20), LoginDate datetime )
INSERT @LOGIN VALUES ('Joe', '20070701')
INSERT @LOGIN VALUES ('Joe', '20070710')
INSERT @LOGIN VALUES ('Joe', '20070720')
INSERT @LOGIN VALUES (Fred, '20070702')
INSERT @LOGIN VALUES (Fred, '20070707')
INSERT @LOGIN VALUES (Fred, '20070711')
Expected output
Joe 2007-07-02 00:00:00.000
Joe 2007-07-03 00:00:00.000
Joe 2007-07-04 00:00:00.000
Joe 2007-07-05 00:00:00.000
Joe 2007-07-06 00:00:00.000
Joe 2007-07-07 00:00:00.000
Joe 2007-07-08 00:00:00.000
Joe 2007-07-09 00:00:00.000
Joe 2007-07-11 00:00:00.000
Joe 2007-07-12 00:00:00.000
Joe 2007-07-13 00:00:00.000
Joe 2007-07-14 00:00:00.000
Joe 2007-07-15 00:00:00.000
Joe 2007-07-16 00:00:00.000
Joe 2007-07-17 00:00:00.000
Joe 2007-07-18 00:00:00.000
Joe 2007-07-19 00:00:00.000
Joe 2007-07-21 00:00:00.000
Joe 2007-07-22 00:00:00.000
Joe 2007-07-23 00:00:00.000
Joe 2007-07-24 00:00:00.000
Joe 2007-07-25 00:00:00.000
Joe 2007-07-26 00:00:00.000
Joe 2007-07-27 00:00:00.000
Joe 2007-07-28 00:00:00.000
Joe 2007-07-29 00:00:00.000
Joe 2007-07-30 00:00:00.000
Joe 2007-07-31 00:00:00.000
Your task is to write code that will return that resultset.
1) you have to use the number table I provided
2) you cannot use a calendar table
Shortest code wins, I got mine down to 207 characters (and no, it is not all in 1 line)
I will post my code tomorrow
Thursday, August 09, 2007
SQL Server Monitor Gadget for Windows Vista Sidebar
Conchango are making available version 1 of our SQL Server Monitor Gadget for Windows Vista SidebarThe gadget does exactly what it says on the tin; it monitors a specified SQL Server database for various metrics and displays them on the Windows Vista Sidebar. It is provided for use by SQL Server DBAs that require a nice'n'easy way of monitoring important metrics on their SQL Server installations. In version 1 the gadget provides the following information:
Data file size - total file size of the database data files
Data file used - amount of used space on those data files
Data file used % - The percentage of the total data size that has been used
Log file size - total file size of the database log files
Log file used - amount of used space on those log files
Log file used % - The percentage of the total log size that has been used
Watch the demo, read more and download the gadget here: http://blogs.conchango.com/jamiethomson/archive/2007/08/09/Announcing-SQL-Server-Monitor-Gadget-for-Windows-Vista-Sidebar.aspx
Tuesday, August 07, 2007
Agile: Planning Poker and Scrum
Who said you can’t gamble at work and what is Scrum anyway? Scrum is an agile process for developing software. With Scrum, projects progress via a series of iterations called sprints. A sprint can be 2 weeks, 3 weeks or a month. The sprint is a deliverable, after the completion of the sprint you are supposed to have delivered working piece of code. This might be a subset of a product. The reason to deliver frequently is so that the client can comment on the product. Instead of waiting 3 months before the client sees the product only to say that this is not what the envisioned they will see it earlier and give you feedback. This will save you a lot of time, trust me on this one! If you want to learn more about scrum then download this excellent 90 page book in pdf format by Henrik Kniberg (Scrum and XP from the Trenches)
Planning Poker
Have you ever been in a meeting where the question was asked how long it would take to do a certain task? What usually happens is this: the first person will say 16 hours and the next 3 people will pick something close or even the same value. With planning poker you don’t know what the other people said until every person decided. This is how it works: everyone has a bunch of cards which are numbered between 0.5 and 48 (with gaps), a need more info card and a need coffee break card. These cards can be used for days or hours, it depends how big the task is. So it the same question is asked then everyone puts a card down with the number facing down. Then all the people turn the cards and the group looks at the numbers. At this point you will see strange things every now and then, some people have 2 hours some people have 32 hours for the same task. The reason for this is because some of the people didn’t completely understand what is involved and might need more information. You go around the room and everyone explains how they picked their number. This is where you will find out that some people didn’t understand the task and need more info. Sometimes you will find out that a task needs to be split up, a task should not take longer than 1 ideal day to complete. After you have completed the whole process a couple of times you will find out that your team is much better at estimating the time it will take to complete a task.
There is a website where you can do planning poker online, the URL is http://www.planningpoker.com/
We made our own, we all picked a different picture for the back of the cards and everyone has the same numbers for the front.
To learn more about Scrum visit these URLs
http://agilemanifesto.org/
http://www.mountaingoatsoftware.com/scrum
http://www.xprogramming.com/xpmag/whatisxp.htm
If you want to read books about scrum then I recommend these two
Agile Project Management with Scrum (Microsoft Professional)
Agile Software Development with SCRUM
And never ever call a certified Scrum Master Scrumbag ;-)
Planning Poker
Have you ever been in a meeting where the question was asked how long it would take to do a certain task? What usually happens is this: the first person will say 16 hours and the next 3 people will pick something close or even the same value. With planning poker you don’t know what the other people said until every person decided. This is how it works: everyone has a bunch of cards which are numbered between 0.5 and 48 (with gaps), a need more info card and a need coffee break card. These cards can be used for days or hours, it depends how big the task is. So it the same question is asked then everyone puts a card down with the number facing down. Then all the people turn the cards and the group looks at the numbers. At this point you will see strange things every now and then, some people have 2 hours some people have 32 hours for the same task. The reason for this is because some of the people didn’t completely understand what is involved and might need more information. You go around the room and everyone explains how they picked their number. This is where you will find out that some people didn’t understand the task and need more info. Sometimes you will find out that a task needs to be split up, a task should not take longer than 1 ideal day to complete. After you have completed the whole process a couple of times you will find out that your team is much better at estimating the time it will take to complete a task.
There is a website where you can do planning poker online, the URL is http://www.planningpoker.com/
We made our own, we all picked a different picture for the back of the cards and everyone has the same numbers for the front.
To learn more about Scrum visit these URLs
http://agilemanifesto.org/
http://www.mountaingoatsoftware.com/scrum
http://www.xprogramming.com/xpmag/whatisxp.htm
If you want to read books about scrum then I recommend these two
Agile Project Management with Scrum (Microsoft Professional)
Agile Software Development with SCRUM
And never ever call a certified Scrum Master Scrumbag ;-)
Showtime You Tricked Me, Shame On You
A couple of days ago I was flipping channels (like any man ought to do without interference from the wife/girlfriend). I noticed The Hills Have Eyes 2 just started, it had 2007 as the year made. I found that a little bit strange but for all you know it was one of those straight to video releases. I checked it out on IMDB and it only got 5 stars. This is the plot outline according to IMDB: “A team of trainees of the National Guard brings supply to the New Mexico Desert for a group of soldiers and scientists that are installing a monitoring system in Sector 16. They do not find anybody in the camp, and they receive a blurred distress signal from the hills. Their sergeant gathers a rescue team, and they are attacked and trapped by deformed cannibals, having to fight to survive.”
I am thinking to myself “Okay I’ll bite and watch for 30 minutes, if it sucks I will turn it off”. I am watching this movie and the first thing I noticed is that it looks like it is taking place in the late 70s. One of these deformed freaks shows up and he looks like a puppet. How is the makeup in this movie so much worse than in the first movie? I watch for another 10 minutes and I turned it off. Next day I check IMDB again and what do I find? Yes there is a sequel of the original: The Hills Have Eyes Part II (1985). It has a 3.2 star rating. This movie doesn’t deserve more than 1 star. If you hate someone then buy this movie for that person because the person WILL suffer watching this garbage.
I am thinking to myself “Okay I’ll bite and watch for 30 minutes, if it sucks I will turn it off”. I am watching this movie and the first thing I noticed is that it looks like it is taking place in the late 70s. One of these deformed freaks shows up and he looks like a puppet. How is the makeup in this movie so much worse than in the first movie? I watch for another 10 minutes and I turned it off. Next day I check IMDB again and what do I find? Yes there is a sequel of the original: The Hills Have Eyes Part II (1985). It has a 3.2 star rating. This movie doesn’t deserve more than 1 star. If you hate someone then buy this movie for that person because the person WILL suffer watching this garbage.
Monday, August 06, 2007
Becoming A Better Programmer In 6 Months: The First 20 days
Here is an update of what I accomplished in the first 20 days
Read the book lifehacker
Read the book Microsoft SQL Server 2005 Integration Services
Read the book Extending SSIS 2005 with Script
Read 1 chapter of Learning Python, Second Edition
Played around with the July CTP of SQL Server 2008
So in the first 20 days I have read 3 books however two books are very thin. I will need that time later when I start on much thicker books like Code Complete and Inside Microsoft SQL Server 2005: T-SQL Querying
I will also make a small change to the list instead of Expert SQL Server 2005 Integration Services I will read Core Python Programming
I also started tinkering with Python, those guys are a bunch of jokers. if you type "import this" in a Python command line window you get this output
win32
Type "help", "copyright", "credits" or "license" for more information.
>>> import this
The Zen of Python, by Tim Peters
Beautiful is better than ugly.
Explicit is better than implicit.
Simple is better than complex.
Complex is better than complicated.
Flat is better than nested.
Sparse is better than dense.
Readability counts.
Special cases aren't special enough to break the rules.
Although practicality beats purity.
Errors should never pass silently.
Unless explicitly silenced.
In the face of ambiguity, refuse the temptation to guess.
There should be one-- and preferably only one --obvious way to do it.
Although that way may not be obvious at first unless you're Dutch.
Now is better than never.
Although never is often better than *right* now.
If the implementation is hard to explain, it's a bad idea.
If the implementation is easy to explain, it may be a good idea.
Namespaces are one honking great idea -- let's do more of those!
>>>
In the past week I also played around with the new date data types in SQl server 2008, I have filed a bug/typo which I found in Books On Line.This week I will concentrate on the book Learning Python, Second Edition during weekends and lunch hours, in the evening I will read Practices of an Agile Developer
This is it for the update. The original post can be found here: http://sqlservercode.blogspot.com/2007/07/become-better-developer-in-6-months.html
A more detailed post about the first 10 days can be found here: http://sqlservercode.blogspot.com/2007/07/becoming-better-programmer-in-6-months.html
I am also glad to say that most of the people I tagged in the original post have responded
Friday, August 03, 2007
Summer SQL Teaser #11 NULLIF
Here is a fun teaser. NULLIF will return a null value if the two specified expressions are equivalent.
So to give an example
DECLARE @v varchar
SET @v = ' '
SELECT NULLIF(@v,' ')
That returned NULL because @v and ' ' are the same
Now run this first
CREATE TABLE #j (n varchar(15))
DECLARE @a int
SET @a = 1
WHILE @a <= 1000 BEGIN
INSERT #j
SELECT NULLIF(REPLICATE('1', RAND()*2) , ' ')
SET @a = @a + 1
END
Then without running try to guess if the following query will return any rows
SELECT * FROM #j WHERE n = ' '
So to give an example
DECLARE @v varchar
SET @v = ' '
SELECT NULLIF(@v,' ')
That returned NULL because @v and ' ' are the same
Now run this first
CREATE TABLE #j (n varchar(15))
DECLARE @a int
SET @a = 1
WHILE @a <= 1000 BEGIN
INSERT #j
SELECT NULLIF(REPLICATE('1', RAND()*2) , ' ')
SET @a = @a + 1
END
Then without running try to guess if the following query will return any rows
SELECT * FROM #j WHERE n = ' '
Wednesday, August 01, 2007
SQL Server 2008 Has Nanosecond Precision?
It looks like SQL Server 2008 has nanosecond + microseconds precision for the time datatype
If you run the following
[edit]I just looked at BOL and yes nanoseconds = ns, microsecond = mcs when used in dateadd[/edit]
DECLARE @t time
SELECT @t ='0:0'
SELECT @t AS Time1,DATEADD(ms,1,@t) AS TimeMilli,
DATEADD(ns,10000,@t) AS TimeNano1,DATEADD(ns,100,@t) AS TimeNano2
Time1 00:00:00.0000000
TimeMilli 00:00:00.0010000
TimeNano1 00:00:00.0000100
TimeNano2 00:00:00.0000001
Another interesting thing is that you can not use 0,'0' or ' ' to assign a value
These 3 will all fail
DECLARE @t time
SELECT @t =' '
DECLARE @t time
SELECT @t ='0'
DECLARE @t time
SELECT @t =0
But this will succeed
DECLARE @t time
SELECT @ =''
If you run the following
[edit]I just looked at BOL and yes nanoseconds = ns, microsecond = mcs when used in dateadd[/edit]
DECLARE @t time
SELECT @t ='0:0'
SELECT @t AS Time1,DATEADD(ms,1,@t) AS TimeMilli,
DATEADD(ns,10000,@t) AS TimeNano1,DATEADD(ns,100,@t) AS TimeNano2
Time1 00:00:00.0000000
TimeMilli 00:00:00.0010000
TimeNano1 00:00:00.0000100
TimeNano2 00:00:00.0000001
Another interesting thing is that you can not use 0,'0' or ' ' to assign a value
These 3 will all fail
DECLARE @t time
SELECT @t =' '
DECLARE @t time
SELECT @t ='0'
DECLARE @t time
SELECT @t =0
But this will succeed
DECLARE @t time
SELECT @ =''
SQL Server Notification Services Removed from SQL Server 2008
From the last section (5.0 Deprecated Features) in the read me file
5.0 Deprecated Features
This section covers SQL Server 2005 features that are no longer included with SQL Server 2008.
5.1 SQL Server Notification Services Removed from SQL Server 2008
SQL Server Notification Services will not be included as a component of SQL Server 2008, but will continue to be supported as part of the SQL Server 2005 product support life-cycle. Moving forward, support for key notification scenarios will be incorporated into SQL Server Reporting Services. Existing Reporting Services functionality, such as data driven subscriptions, addresses some of the notification requirements. Features to support additional notification scenarios may be expected in future releases.
There you have it, no more Notification Services
5.0 Deprecated Features
This section covers SQL Server 2005 features that are no longer included with SQL Server 2008.
5.1 SQL Server Notification Services Removed from SQL Server 2008
SQL Server Notification Services will not be included as a component of SQL Server 2008, but will continue to be supported as part of the SQL Server 2005 product support life-cycle. Moving forward, support for key notification scenarios will be incorporated into SQL Server Reporting Services. Existing Reporting Services functionality, such as data driven subscriptions, addresses some of the notification requirements. Features to support additional notification scenarios may be expected in future releases.
There you have it, no more Notification Services
SQL Server 2008 July CTP Has Been Released, The 10 New Features
Download it here: https://connect.microsoft.com/SQLServer/content/content.aspx?ContentID=5395
What is new?
Enterprise Reporting Engine
Improvements represent the two major infrastructure changes for Reporting Services. Reporting Services enhances the processing engine and rendering extensions to enable new functionality, such as Tablix support, and scalability as well as remove the dependency on IIS. Additionally, new report designer and configuration tool are provided that improve usability and workflow for RS customers.
Analysis Services Time Series
This improvement adds a new time series forecasting algorithm (ARIMA: Auto Regressive Integrated Moving Average) to the data mining algorithm suite that provides more stable long term predictions.
T-SQL Improvements
Object Dependencies: The object dependencies improvement provides reliable discovery of dependencies between objects through newly introduced catalog view and dynamic management functions. Dependency information is always up-to-date for both schema-bound and non-schema-bound objects. Dependencies are tracked for stored procedures, tables, views, functions, triggers, user-defined types, XML schema-collections, and more.
Performance Data Collection
Collect data from various sources in SQL Server and OS to help with performance troubleshooting and server maintenance. With this improvement, organizations improve their analysis of common performance issues:
· Define what data is collected and organize the collection into collection sets
· Start/stop/manipulate collection sets programmatically (T-SQL and .NET API)
· Define where data is stored (relational database)
· View data through reports in SQL Server Management Studio.
· Provide platform to plug in more data collectors in the future.
Extended Events
SQL Server Extended Events is a general event-handling system for server systems. The Extended Events infrastructure supports the correlation of data from SQL Server, and under certain conditions, the correlation of data from the operating system and database applications. In the latter case, Extended Events output must be directed to Event Tracing for Windows (ETW) in order to correlate the event data with operating system or application event data.
Database Mirroring Enhancements
SQL Server 2008 builds upon the momentum of SQL Server 2005 by providing a more reliable platform with enhanced database mirroring:
Automatic bad page repair – allows the principal and mirror machines to transparently recover from 823/824 types of data page errors by requesting a fresh copy of the corrupted page from the mirroring partner.
Log stream compression – compression of the outgoing log stream in order to minimize the network bandwidth used by database mirroring.
Miscellaneous performance enhancements:
using asynchronous log write requests on the mirror in order to shorten the log write time and thus speed-up the commit acknowledgement.
better utilization of the mirroring log send buffers in order to pack multiple smaller log blocks into a single network send.
Supportability and diagnosability improvements:
additional performance counters to allow for more granular accounting of the time spent across the different stages of the DBM log processing.
new DMVs and extensions of existing views in order to expose additional information about the mirroring sessions.
ORDPATH Improvement
ORDPATH improvement provides an important new functionality to our customers who use hierarchical data. It provides a superior way of modeling hierarchies in SQL Server by introducing the HierarchyID system data type and corresponding built-in methods which are designed to make it easier to store, query and operate hierarchical data. HierarchyID is also optimized for representing trees, the most common type of hierarchical data.
Large User-Defined Types Improvement
Large user-defined types allows users to expand the size of defined data types by eliminating the 8‑KB limit.
DATE/TIME Data Types
SQL Server 2008 introduces new date and time data types. The new data types enable applications to have separate date and time types, larger year ranges for date value, larger fractional seconds precision for time value, time-zone offset aware datetime type that containing date, time and time zone offset portion, user defined option on fractional seconds precision of time related types and datetime2 and datetimeoffset provide standards conformant semantics. Along with the T-SQL support on the new types, both native (ODBC, OLEDB) and managed (SqlClient) providers also provide the full support through the client driver APIs.
Improved XML Support
To leverages the new date and time types, SQL Server’s XML Schema collection now provides full support for the xs:date, xs:time and xs:dateTime data types. Support for union types is also enhanced by returning correct results for “instance of” queries when union types are involved, and adding support for lists of unions and unions of lists constructs in XML Schemas.
What is new?
Enterprise Reporting Engine
Improvements represent the two major infrastructure changes for Reporting Services. Reporting Services enhances the processing engine and rendering extensions to enable new functionality, such as Tablix support, and scalability as well as remove the dependency on IIS. Additionally, new report designer and configuration tool are provided that improve usability and workflow for RS customers.
Analysis Services Time Series
This improvement adds a new time series forecasting algorithm (ARIMA: Auto Regressive Integrated Moving Average) to the data mining algorithm suite that provides more stable long term predictions.
T-SQL Improvements
Object Dependencies: The object dependencies improvement provides reliable discovery of dependencies between objects through newly introduced catalog view and dynamic management functions. Dependency information is always up-to-date for both schema-bound and non-schema-bound objects. Dependencies are tracked for stored procedures, tables, views, functions, triggers, user-defined types, XML schema-collections, and more.
Performance Data Collection
Collect data from various sources in SQL Server and OS to help with performance troubleshooting and server maintenance. With this improvement, organizations improve their analysis of common performance issues:
· Define what data is collected and organize the collection into collection sets
· Start/stop/manipulate collection sets programmatically (T-SQL and .NET API)
· Define where data is stored (relational database)
· View data through reports in SQL Server Management Studio.
· Provide platform to plug in more data collectors in the future.
Extended Events
SQL Server Extended Events is a general event-handling system for server systems. The Extended Events infrastructure supports the correlation of data from SQL Server, and under certain conditions, the correlation of data from the operating system and database applications. In the latter case, Extended Events output must be directed to Event Tracing for Windows (ETW) in order to correlate the event data with operating system or application event data.
Database Mirroring Enhancements
SQL Server 2008 builds upon the momentum of SQL Server 2005 by providing a more reliable platform with enhanced database mirroring:
Automatic bad page repair – allows the principal and mirror machines to transparently recover from 823/824 types of data page errors by requesting a fresh copy of the corrupted page from the mirroring partner.
Log stream compression – compression of the outgoing log stream in order to minimize the network bandwidth used by database mirroring.
Miscellaneous performance enhancements:
using asynchronous log write requests on the mirror in order to shorten the log write time and thus speed-up the commit acknowledgement.
better utilization of the mirroring log send buffers in order to pack multiple smaller log blocks into a single network send.
Supportability and diagnosability improvements:
additional performance counters to allow for more granular accounting of the time spent across the different stages of the DBM log processing.
new DMVs and extensions of existing views in order to expose additional information about the mirroring sessions.
ORDPATH Improvement
ORDPATH improvement provides an important new functionality to our customers who use hierarchical data. It provides a superior way of modeling hierarchies in SQL Server by introducing the HierarchyID system data type and corresponding built-in methods which are designed to make it easier to store, query and operate hierarchical data. HierarchyID is also optimized for representing trees, the most common type of hierarchical data.
Large User-Defined Types Improvement
Large user-defined types allows users to expand the size of defined data types by eliminating the 8‑KB limit.
DATE/TIME Data Types
SQL Server 2008 introduces new date and time data types. The new data types enable applications to have separate date and time types, larger year ranges for date value, larger fractional seconds precision for time value, time-zone offset aware datetime type that containing date, time and time zone offset portion, user defined option on fractional seconds precision of time related types and datetime2 and datetimeoffset provide standards conformant semantics. Along with the T-SQL support on the new types, both native (ODBC, OLEDB) and managed (SqlClient) providers also provide the full support through the client driver APIs.
Improved XML Support
To leverages the new date and time types, SQL Server’s XML Schema collection now provides full support for the xs:date, xs:time and xs:dateTime data types. Support for union types is also enhanced by returning correct results for “instance of” queries when union types are involved, and adding support for lists of unions and unions of lists constructs in XML Schemas.
Tuesday, July 31, 2007
Say Hello To My New Boss
Yes it looks like it is a done deal: News Corp. Appears to HaveEnough Votes to Clinch Deal Bancroft family members owning 32% of Dow Jones & Co.'s overall votes have agreed to support News Corp.'s $5 billion bid for Dow Jones, the publisher of The Wall Street Journal, according to people familiar with the matter.
That level of support is likely more than enough to guarantee News Corp. enough votes to clinch the deal.
Let's see what happens next, maybe I will get some new toys (64 CPU SQL boxes) to play with. I'll happily take the outdated MySpace equipment also ;-)
Cannot resolve collation conflict for equal to operation.
You set up your linked server, you write a query which joins two tables, you execute the query and the error message is this
Cannot resolve collation conflict for equal to operation
What does this mean? This mean that the collation on the two tables is different
Let's look at an example. Le's create two tables, onme with Traditional_Spanish_CI_AI collation and one with the default. The default collation for me is SQL_Latin1_General_CP1_CI_AS.
CREATE TABLE #Foo (SomeCol varchar(50) COLLATE Traditional_Spanish_CI_AI)
CREATE TABLE #Foo2 (SomeCol varchar(50))
INSERT #Foo VALUES ('AAA')
INSERT #Foo VALUES ('BBB')
INSERT #Foo VALUES ('CCC')
INSERT #Foo VALUES ('DDD')
INSERT #Foo2 VALUES ('AAA')
INSERT #Foo2 VALUES ('BBB')
INSERT #Foo2 VALUES ('CCC')
INSERT #Foo2 VALUES ('DDD')
Now run this query and you will get the error message
SELECT * FROM #Foo F1
JOIN #Foo2 f2 ON f1.SomeCol = f2.SomeCol
Server: Msg 446, Level 16, State 9, Line 1
Cannot resolve collation conflict for equal to operation.
Now add COLLATE Traditional_Spanish_CI_AI to #Foo2 SomeCol
SELECT * FROM #Foo F1
JOIN #Foo2 f2 ON f1.SomeCol = f2.SomeCol COLLATE Traditional_Spanish_CI_AI
That works, if you add COLLATE SQL_Latin1_General_CP1_CI_AS to #Foo SomeCol that will work also
SELECT * FROM #Foo F1
JOIN #Foo2 f2 ON f1.SomeCol COLLATE SQL_Latin1_General_CP1_CI_AS = f2.SomeCol
If you want to know what these collations mean then run the following query (yes that is not a typo it is indeed ::).
SELECT *
FROM ::fn_helpcollations()
WHERE name in('SQL_Latin1_General_CP1_CI_AS','Traditional_Spanish_CI_AI')
Traditional_Spanish_CI_AI
Traditional-Spanish,
case-insensitive,
accent-insensitive,
kanatype-insensitive,
width-insensitive
SQL_Latin1_General_CP1_CI_AS
Latin1-General,
case-insensitive,
accent-sensitive,
kanatype-insensitive,
width-insensitive for Unicode Data,
SQL Server Sort Order 52 on Code Page 1252 for non-Unicode Data
Cannot resolve collation conflict for equal to operation
What does this mean? This mean that the collation on the two tables is different
Let's look at an example. Le's create two tables, onme with Traditional_Spanish_CI_AI collation and one with the default. The default collation for me is SQL_Latin1_General_CP1_CI_AS.
CREATE TABLE #Foo (SomeCol varchar(50) COLLATE Traditional_Spanish_CI_AI)
CREATE TABLE #Foo2 (SomeCol varchar(50))
INSERT #Foo VALUES ('AAA')
INSERT #Foo VALUES ('BBB')
INSERT #Foo VALUES ('CCC')
INSERT #Foo VALUES ('DDD')
INSERT #Foo2 VALUES ('AAA')
INSERT #Foo2 VALUES ('BBB')
INSERT #Foo2 VALUES ('CCC')
INSERT #Foo2 VALUES ('DDD')
Now run this query and you will get the error message
SELECT * FROM #Foo F1
JOIN #Foo2 f2 ON f1.SomeCol = f2.SomeCol
Server: Msg 446, Level 16, State 9, Line 1
Cannot resolve collation conflict for equal to operation.
Now add COLLATE Traditional_Spanish_CI_AI to #Foo2 SomeCol
SELECT * FROM #Foo F1
JOIN #Foo2 f2 ON f1.SomeCol = f2.SomeCol COLLATE Traditional_Spanish_CI_AI
That works, if you add COLLATE SQL_Latin1_General_CP1_CI_AS to #Foo SomeCol that will work also
SELECT * FROM #Foo F1
JOIN #Foo2 f2 ON f1.SomeCol COLLATE SQL_Latin1_General_CP1_CI_AS = f2.SomeCol
If you want to know what these collations mean then run the following query (yes that is not a typo it is indeed ::).
SELECT *
FROM ::fn_helpcollations()
WHERE name in('SQL_Latin1_General_CP1_CI_AS','Traditional_Spanish_CI_AI')
Traditional_Spanish_CI_AI
Traditional-Spanish,
case-insensitive,
accent-insensitive,
kanatype-insensitive,
width-insensitive
SQL_Latin1_General_CP1_CI_AS
Latin1-General,
case-insensitive,
accent-sensitive,
kanatype-insensitive,
width-insensitive for Unicode Data,
SQL Server Sort Order 52 on Code Page 1252 for non-Unicode Data
Monday, July 30, 2007
Have You Seen 300?
Tomorrow 300 is coming out on DVD. Have you seen 300? Are you going to buy it or rent it? I did not see it but I ordered theWidescreen Two-Disc Special Edition
. I heard from several people I have talked to that they loved it when they saw in the movie theater and they can’t wait to watch it again. I do have the special edition of Sin City which is Frank Miller’s first movie. I watched that several times. If you have that once make sure you watch the green screen fast forward version.
So what is 300 about? Spartan King Leonidas and 300 Spartans fight to the last man against Persian King Xerxes and his army of over one million soldiers, while in Sparta, Queen Gorgo attempts to rally support for her husband. The story is framed by a voice-over narrative by the Spartan soldier Dilios. Through this narrative technique, various fantastical creatures are introduced, placing 300 within the genre of historical fantasy.
So what are you going to do?
A) Buy the regular version
B) Buy the Widescreen Two-Disc Special Edition
C) Buy the HD-DVD version
D) Buy the Blu-Ray version
E) Rent it
F) Wait for Cable
G) Wait for Network TV
H) Not watch it at all
Obviously my pick is B
Friday, July 27, 2007
Summer SQL Teaser #10 ROLLBACK
This one is not so much a teaser but it will show you what you can do in case you want to insert data in a logging table after a rollback occurs
Without running this try to guess what the counts of the three tables will be after the rollback
CREATE TABLE Test (id int)
CREATE TABLE #Test (id int)
DECLARE @Test table (id int)
BEGIN TRAN
INSERT INTO @Test VALUES(1)
INSERT INTO Test VALUES(1)
INSERT INTO #Test VALUES(1)
ROLLBACK TRAN
SELECT '@test',COUNT(*) FROM @Test
SELECT ' test',COUNT(*) FROM Test
SELECT '#test',COUNT(*) FROM #Test
DROP TABLE Test,#Test
Without running this try to guess what the counts of the three tables will be after the rollback
CREATE TABLE Test (id int)
CREATE TABLE #Test (id int)
DECLARE @Test table (id int)
BEGIN TRAN
INSERT INTO @Test VALUES(1)
INSERT INTO Test VALUES(1)
INSERT INTO #Test VALUES(1)
ROLLBACK TRAN
SELECT '@test',COUNT(*) FROM @Test
SELECT ' test',COUNT(*) FROM Test
SELECT '#test',COUNT(*) FROM #Test
DROP TABLE Test,#Test
Thursday, July 26, 2007
Visual Studio 2008 Beta 2 Released!
Visual Studio Team System 2008 Beta 2 Team Suite (VPC)
Microsoft Visual Studio 2008 is the next-generation development tool for Windows Vista, the 2007 Office System, and the Web.
Visual Studio 2008 Beta 2 Standard Edition
Microsoft Visual Studio 2008 is the next-generation development tool for Windows Vista, the 2007 Office System, and the Web.
Visual Studio 2008 Beta 2 Team Foundation Server (VPC)
Microsoft Visual Studio 2008 is the next-generation development tool for Windows Vista, the 2007 Office System, and the Web.
MSDN Library for Visual Studio 2008 Beta 2
MSDN Library provides access to essential programming information, including technical reference documentations, white papers, software development kits and code samples necessary to develop web services and applications. This is an updated version of the MSDN Library for Visual Studio 2008 Beta 2.
Visual Studio Team System 2008 Beta 2 Team Suite
Microsoft Visual Studio 2008 is the next-generation development tool for Windows Vista, the 2007 Office System, and the Web.
Visual Studio 2008 Beta 2 Professional Edition
Microsoft Visual Studio 2008 is the next-generation development tool for Windows Vista, the 2007 Office System, and the Web.
Visual Studio 2008 Beta 2 Team Foundation Server
Microsoft Visual Studio 2008 is the next-generation development tool for Windows Vista, the 2007 Office System, and the Web.
Visual Studio Code Name "Orcas" Beta 1 Professional (self-extracting install)
Microsoft Visual Studio Code Name "Orcas" is the next generation development tool for Windows Vista, the 2007 Office System, and the Web.
Visual Studio Code Name "Orcas" Beta 1 Team Foundation Server (VPC)
Microsoft Visual Studio Code Name "Orcas" is the next generation development tool for Windows Vista, the 2007 Office System, and the Web.
Microsoft Visual Studio 2008 is the next-generation development tool for Windows Vista, the 2007 Office System, and the Web.
Visual Studio 2008 Beta 2 Standard Edition
Microsoft Visual Studio 2008 is the next-generation development tool for Windows Vista, the 2007 Office System, and the Web.
Visual Studio 2008 Beta 2 Team Foundation Server (VPC)
Microsoft Visual Studio 2008 is the next-generation development tool for Windows Vista, the 2007 Office System, and the Web.
MSDN Library for Visual Studio 2008 Beta 2
MSDN Library provides access to essential programming information, including technical reference documentations, white papers, software development kits and code samples necessary to develop web services and applications. This is an updated version of the MSDN Library for Visual Studio 2008 Beta 2.
Visual Studio Team System 2008 Beta 2 Team Suite
Microsoft Visual Studio 2008 is the next-generation development tool for Windows Vista, the 2007 Office System, and the Web.
Visual Studio 2008 Beta 2 Professional Edition
Microsoft Visual Studio 2008 is the next-generation development tool for Windows Vista, the 2007 Office System, and the Web.
Visual Studio 2008 Beta 2 Team Foundation Server
Microsoft Visual Studio 2008 is the next-generation development tool for Windows Vista, the 2007 Office System, and the Web.
Visual Studio Code Name "Orcas" Beta 1 Professional (self-extracting install)
Microsoft Visual Studio Code Name "Orcas" is the next generation development tool for Windows Vista, the 2007 Office System, and the Web.
Visual Studio Code Name "Orcas" Beta 1 Team Foundation Server (VPC)
Microsoft Visual Studio Code Name "Orcas" is the next generation development tool for Windows Vista, the 2007 Office System, and the Web.
Wednesday, July 25, 2007
Becoming A Better Programmer In 6 Months: The First 10 days
Here is an update of what I accomplished in the first 10 days
Read the book lifehacker
Read the book Microsoft SQL Server 2005 Integration Services
Read 126 pages of Extending SSIS 2005 with Script
Installed PostgreSQL, Python, Eclipse and Django.
Now you may ask yourself how I could have read all these things in 10 days. This is because I have to convert a whole bunch of packages from DTS to SSIS. So I did read a lot at work about SSIS. As you can see I sneaked the Extending SSIS 2005 with Script book in there which was not on my original list. I actually did all the example in that book. SSIS is pretty cool, the only thing which was frustrating (at first) was that you cannot modify a connection string with script like in DTS. However you can use Package Configurations to do that. This is important if you have to import a daily Excel file with a different filename every day. So as your first step in your package you just update the configuration table. Here is a small example
DECLARE @i char(8)
SELECT @i = CONVERT(CHAR(8),GETDATE()-1,112)
UPDATE dbo.[SSIS_Configurations]
SET ConfiguredValue = 'E:\SSISExcel\ida' + @i + '.csv'
WHERE ConfigurationFilter ='CSV'
AND PackagePath ='\Package.Connections[FlatFileCSV].Properties[ConnectionString]'
I will write a blogpost with more details and screenshots within the next couple of days.
I though the Microsoft SQL Server 2005 Integration Services book was pretty good. I saw some mixed reviews on Amazon but I do not agree with that at all. The book is well organized, easy to read and the examples are easy to follow. I recommend this book to anyone who has to learn SSIS.
Another book I read is lifehacker, this books shows hacks that you can use to improve your technical life. One of the hacks that I have implemented is the JunkDraw hack. You create a folder called JunkDraw, this is where you save all your downloaded content. Then there is the VB Script which is scheduled to run once a day and deletes all the files which are older than 2 weeks from this folder. So if you downloaded something and you did not move it from the folder it will be gone. How many files/apps/trial/beta apps have you downloaded, moved to a folder and never looked at again? Exactly this will prevent that kind of clutter.
I mentioned that I would like to learn a new language, so I went a little overboard because in addition to a new language I have also chosen a new database and a framework. The language is Python which was created by Guido van Rossum. Python is a scripting language and pretty popular among the FLOSS guys/girls. This of course will prepare me to play around with IronPython and the DLR once that is finalized. The DB I picked is PostgreSQL, I have chosen PostgreSQL instead of MySQL because I just can’t install a DB where you can enter invalid days. Another reason is that PostgreSQL is recommended with the framework that I picked. I picked Django over TurboGears and Ruby on Rails because I have heard some good things about it, one of them being performance. So last Sunday 5AM I installed PostgreSQL, Django, Python, Eclipse and the Eclipse Python plugin Pydev on a windows box and got the initial setup to work.
I will keep you posted on my progress once every 10 days but so far it is going good ;-)
Here is the link to the original Become a Better Developer... in 6 months article
Read the book lifehacker
Read the book Microsoft SQL Server 2005 Integration Services
Read 126 pages of Extending SSIS 2005 with Script
Installed PostgreSQL, Python, Eclipse and Django.
Now you may ask yourself how I could have read all these things in 10 days. This is because I have to convert a whole bunch of packages from DTS to SSIS. So I did read a lot at work about SSIS. As you can see I sneaked the Extending SSIS 2005 with Script book in there which was not on my original list. I actually did all the example in that book. SSIS is pretty cool, the only thing which was frustrating (at first) was that you cannot modify a connection string with script like in DTS. However you can use Package Configurations to do that. This is important if you have to import a daily Excel file with a different filename every day. So as your first step in your package you just update the configuration table. Here is a small example
DECLARE @i char(8)
SELECT @i = CONVERT(CHAR(8),GETDATE()-1,112)
UPDATE dbo.[SSIS_Configurations]
SET ConfiguredValue = 'E:\SSISExcel\ida' + @i + '.csv'
WHERE ConfigurationFilter ='CSV'
AND PackagePath ='\Package.Connections[FlatFileCSV].Properties[ConnectionString]'
I will write a blogpost with more details and screenshots within the next couple of days.
I though the Microsoft SQL Server 2005 Integration Services book was pretty good. I saw some mixed reviews on Amazon but I do not agree with that at all. The book is well organized, easy to read and the examples are easy to follow. I recommend this book to anyone who has to learn SSIS.
Another book I read is lifehacker, this books shows hacks that you can use to improve your technical life. One of the hacks that I have implemented is the JunkDraw hack. You create a folder called JunkDraw, this is where you save all your downloaded content. Then there is the VB Script which is scheduled to run once a day and deletes all the files which are older than 2 weeks from this folder. So if you downloaded something and you did not move it from the folder it will be gone. How many files/apps/trial/beta apps have you downloaded, moved to a folder and never looked at again? Exactly this will prevent that kind of clutter.
I mentioned that I would like to learn a new language, so I went a little overboard because in addition to a new language I have also chosen a new database and a framework. The language is Python which was created by Guido van Rossum. Python is a scripting language and pretty popular among the FLOSS guys/girls. This of course will prepare me to play around with IronPython and the DLR once that is finalized. The DB I picked is PostgreSQL, I have chosen PostgreSQL instead of MySQL because I just can’t install a DB where you can enter invalid days. Another reason is that PostgreSQL is recommended with the framework that I picked. I picked Django over TurboGears and Ruby on Rails because I have heard some good things about it, one of them being performance. So last Sunday 5AM I installed PostgreSQL, Django, Python, Eclipse and the Eclipse Python plugin Pydev on a windows box and got the initial setup to work.
I will keep you posted on my progress once every 10 days but so far it is going good ;-)
Here is the link to the original Become a Better Developer... in 6 months article
This has to be one of the worst planned projects in recent Database history
http://www.tek-tips.com/viewthread.cfm?qid=1391668&page=1
here is the question
I have a situation where a person can have more then one item ordered. I need to layout the information as follows:
The information is in the same table and Item Ordered is in relationship to Person instead of Item Description.
I posted this same question on the Oracle forum, because the project is being done using two databases. Sql Server for development and Oracle for Production. I would like to get the SQL Server version of how to implement the select statement.
It gets better
Yes, it is crazy that two database are being used to develop the system, but the people who make the decisions claimed that in the preliminary stages Oracle was causing problems.
So, they switched to SQL Server as the development database. Of course the end result it that the customer expects to implement Oracle. I suspect that someone was just too lazy to learn Oracle.
And better
I asked my manager why Oracle and SQL Server and she stated that they were having load balancing issues (whatever that means). And when errors occured they were not sure how to fix them and it took too much time. At the beginning of the project there may not have been enough Oracle talent to tackle the problems. The Oracle talent available has been here for about 4 years before the project started. So, I wonder how much knowlege they DO have. I feel that an consultant should have been invested in. So, right now when stuff is put into testing for production we have to flip-flop between SQL Server and Oracle.
What? Who came up with that reason? This is just incredible. What do you think?
here is the question
I have a situation where a person can have more then one item ordered. I need to layout the information as follows:
Person Item Ordered Item Description
----------------------------------------------
1 1 of 2 Item1
1 2 of 2 Item2
2 1 of 1 Item1
3 1 of 3 Item3
3 2 of 3 Item2
3 3 of 3 Item1
.
.
The information is in the same table and Item Ordered is in relationship to Person instead of Item Description.
I posted this same question on the Oracle forum, because the project is being done using two databases. Sql Server for development and Oracle for Production. I would like to get the SQL Server version of how to implement the select statement.
It gets better
Yes, it is crazy that two database are being used to develop the system, but the people who make the decisions claimed that in the preliminary stages Oracle was causing problems.
So, they switched to SQL Server as the development database. Of course the end result it that the customer expects to implement Oracle. I suspect that someone was just too lazy to learn Oracle.
And better
I asked my manager why Oracle and SQL Server and she stated that they were having load balancing issues (whatever that means). And when errors occured they were not sure how to fix them and it took too much time. At the beginning of the project there may not have been enough Oracle talent to tackle the problems. The Oracle talent available has been here for about 4 years before the project started. So, I wonder how much knowlege they DO have. I feel that an consultant should have been invested in. So, right now when stuff is put into testing for production we have to flip-flop between SQL Server and Oracle.
What? Who came up with that reason? This is just incredible. What do you think?
Tuesday, July 24, 2007
Visual Studio Team Edition for Database Professionals Service Release 1 Is Here
Visual Studio Team Edition for Database Professionals Service Release 1 has been Released
Overview
This service release addresses the top issues that were found through feedback from customers and partners. This release includes the following features:
• Cross-database references
Support is improved to enable you to reference objects in different databases by using database project references or referencing a database metafile (.dbmeta). This support will reduce or eliminate the cross database reference warnings within a database project.
• Improved file support within SQL Server file groups
You may define files within file groups as database project properties instead of having to create files and file groups within the pre-deployment storage script.
• Variables
A Variables page is added to the database properties. This new page enables you to define setvar variables for use in the deployment scripts. Additionally, SR1 supports the latest service pack release from Microsoft SQL Server 2005 (SP2). The SR1 also supports the Windows Vista operating system.
The knowledge base (KB) article describing this service release is here http://support.microsoft.com/kb/936612/
The actual download is here:
http://www.microsoft.com/downloads/details.aspx?FamilyID=9810808c-9248-41a5-bdc1-d8210a06ed87&displaylang=en
Overview
This service release addresses the top issues that were found through feedback from customers and partners. This release includes the following features:
• Cross-database references
Support is improved to enable you to reference objects in different databases by using database project references or referencing a database metafile (.dbmeta). This support will reduce or eliminate the cross database reference warnings within a database project.
• Improved file support within SQL Server file groups
You may define files within file groups as database project properties instead of having to create files and file groups within the pre-deployment storage script.
• Variables
A Variables page is added to the database properties. This new page enables you to define setvar variables for use in the deployment scripts. Additionally, SR1 supports the latest service pack release from Microsoft SQL Server 2005 (SP2). The SR1 also supports the Windows Vista operating system.
The knowledge base (KB) article describing this service release is here http://support.microsoft.com/kb/936612/
The actual download is here:
http://www.microsoft.com/downloads/details.aspx?FamilyID=9810808c-9248-41a5-bdc1-d8210a06ed87&displaylang=en
Sunday, July 22, 2007
Summer SQL Teaser #9 @@TRANCOUNT
Here is another quick teaser. What will be the values of the print statements? Try to guess it before running this code
SET
ANSI_DEFAULTS ON PRINT '#1 == ' + LTRIM(STR(@@TRANCOUNT))BEGIN
TRANSACTIONROLLBACK
Friday, July 20, 2007
Summer SQL Teaser #8 Comments And Go
Without running the following two blocks of code what do you think will the output be?
/* code 1
SELECT GETDATE()
GO */
SELECT GETDATE()
/* code 2
SELECT GETDATE()
GO
*/
SELECT GETDATE()
/* code 1
SELECT GETDATE()
GO */
SELECT GETDATE()
/* code 2
SELECT GETDATE()
GO
*/
SELECT GETDATE()
Some Pics
I have uploaded some pics on Flickr that I took a while back.
There is Paris, Amsterdam, Hawaii, Croatia and New York. Two of them you can see below. To see the NYC and Amsterdam night shots visit this URL: http://www.flickr.com/photos/denisgobo/tags/nightshot/
Amsterdam
New York city
There is Paris, Amsterdam, Hawaii, Croatia and New York. Two of them you can see below. To see the NYC and Amsterdam night shots visit this URL: http://www.flickr.com/photos/denisgobo/tags/nightshot/
Amsterdam
New York city
Thursday, July 19, 2007
The Internet has Crashed
Don't worry they got it back to work, watch the video here: http://www.scaryideas.com/video/3017/
Tuesday, July 17, 2007
Non-Technical: Happy Birthday Twins
HTML Source EditorWord wrap
Today my twins are one year old. It is supposed to be a little easier from now on (until they hit 2 that is). Here is one picture.
If you want to see more you can go here: http://www.flickr.com/photos/denisgobo/
This is the last non technical post I will make for a while, My next post will be about Scrum and planning poker.
I see that Hugo Kornelis and Adam Machanic responded to my tagging. Good, three slackers people left.
Monday, July 16, 2007
Become a Better Developer... in 6 months
I just listened to the latest Hanselminutes podcast: Be a Better Developer in Six Months
Scott Hanselman asks “what are you going to do in the next 6 months to become a better developer”?
He suggest reading books, nerd dinners, having lunches together with other non competitive companies, watch webcasts together during lunch and code reading.
Here is what I am going to do for the next 6 months
I am going to read a technical book every 10 days and review every single book
That should be possible now that my twins are one year old (tomorrow). I have a bit more free time at night to read. Here is the list of books, some of them I have read, some I have partially read.
Code Complete (reread)
I think this is one of those books that you should read once a year.
Practices of an Agile Developer
Some good stuff in here, in ordered it a couple of months ago but did not read it yet.
Inside Microsoft SQL Server 2005: T-SQL Querying (partial reread)
I read several chapters but did not read the whole book.
Inside Microsoft SQL Server 2005: The Storage Engine (reread)
I have read parts of this one; I have read the 2000 edition several times.
Refactoring (reread)
I was thinking Design Patterns (GOF) or this one. As you can see I have chosen Refactoring.
Prefactoring
Why refactor when you can prefactor? I just skimmed through it in the book store and it looks promising.
Open Sources 2.0
Open Sources 2.0 is a collection of insightful and thought-provoking essays from today's technology leaders that continues painting the evolutionary picture that developed in the 1999 book Open Sources: Voices from the Revolution.
Pragmatic Unit Testing in C# with NUnit
New edition.
Building the Data Warehouse (reread)
Read this one several years ago, will read it again
Expert SQL Server 2005 Integration Services
Will read this together with the one below at work; have to convert about 60 DTS packages to SSIS.
Microsoft SQL Server 2005 Integration Services
Beautiful Code
In this unique and insightful book, leading computer scientists offer case studies that reveal how they found unusual, carefully designed solutions to high-profile projects. You will be able to look over the shoulder of major coding and design experts to see problems through their eyes.
Pro SQL Server 2005 Database Design and Optimization (reread)
Read this will read it again
The Art of SQL
Heard some good stuff about this book.
Getting Things Done
We all need some help with organizing our lives.
Lifehacker (reread)
Getting ThingsDone for the computer person, very useful stuff inside.
Framework Design Guidelines (reread)
Very nice book, you will learn why something was done a certain way. Good tips on what to avoid and what should be done.
New language Book probably Python or Ruby( you decide)
Here is a pic of the books I have at home, the others I have at work or I still have to purchase them.
I will watch 2 web casts a week during lunch time and review those also.
I will look at high quality source code from open source projects and also from the book Beautiful Code. I will go to CodePlex to download a couple of open source projects and will study the source code
I will learn a new language (I actually got this from Ken Henderson who suggests to learn a new language every year) and rewrite one of the current applications in that language. This way I don’t have to worry about logic problems and design, I just have to translate the code.
I will learn a new technology. I am thinking either WCF or WPF
I will keep updates on Pownce (sorry folks no invites left) everyday The reason I am doing this is so that someone can call me out in case I don’t keep my promise. This is similar to stopping smoking but not telling anyone, if you do that then who knows you stopped so that they can confront you?
I know this is cheesy but I will do it anyway, I will tag 5 people I (kind of) know and I want them to tell us their plans.
Adam Machanic
Louis Davisdson
Peter DeBetta
Mladen Prajdic
Hugo Kornelis
And I will tag 5 people whose blogs I read but I don’t know them
Jeff Smith
Jason Gaylord
Jeff Altwood
Matija Lah
Ward Pond
And you the reader, what will you do in the next 6 months to become a better developer?
Cross posted from here: http://sqlblog.com/blogs/denis_gobo/archive/2007/07/16/1746.aspx
Scott Hanselman asks “what are you going to do in the next 6 months to become a better developer”?
He suggest reading books, nerd dinners, having lunches together with other non competitive companies, watch webcasts together during lunch and code reading.
Here is what I am going to do for the next 6 months
I am going to read a technical book every 10 days and review every single book
That should be possible now that my twins are one year old (tomorrow). I have a bit more free time at night to read. Here is the list of books, some of them I have read, some I have partially read.
Code Complete (reread)
I think this is one of those books that you should read once a year.
Practices of an Agile Developer
Some good stuff in here, in ordered it a couple of months ago but did not read it yet.
Inside Microsoft SQL Server 2005: T-SQL Querying (partial reread)
I read several chapters but did not read the whole book.
Inside Microsoft SQL Server 2005: The Storage Engine (reread)
I have read parts of this one; I have read the 2000 edition several times.
Refactoring (reread)
I was thinking Design Patterns (GOF) or this one. As you can see I have chosen Refactoring.
Prefactoring
Why refactor when you can prefactor? I just skimmed through it in the book store and it looks promising.
Open Sources 2.0
Open Sources 2.0 is a collection of insightful and thought-provoking essays from today's technology leaders that continues painting the evolutionary picture that developed in the 1999 book Open Sources: Voices from the Revolution.
Pragmatic Unit Testing in C# with NUnit
New edition.
Building the Data Warehouse (reread)
Read this one several years ago, will read it again
Expert SQL Server 2005 Integration Services
Will read this together with the one below at work; have to convert about 60 DTS packages to SSIS.
Microsoft SQL Server 2005 Integration Services
Beautiful Code
In this unique and insightful book, leading computer scientists offer case studies that reveal how they found unusual, carefully designed solutions to high-profile projects. You will be able to look over the shoulder of major coding and design experts to see problems through their eyes.
Pro SQL Server 2005 Database Design and Optimization (reread)
Read this will read it again
The Art of SQL
Heard some good stuff about this book.
Getting Things Done
We all need some help with organizing our lives.
Lifehacker (reread)
Getting ThingsDone for the computer person, very useful stuff inside.
Framework Design Guidelines (reread)
Very nice book, you will learn why something was done a certain way. Good tips on what to avoid and what should be done.
New language Book probably Python or Ruby( you decide)
Here is a pic of the books I have at home, the others I have at work or I still have to purchase them.
I will watch 2 web casts a week during lunch time and review those also.
I will look at high quality source code from open source projects and also from the book Beautiful Code. I will go to CodePlex to download a couple of open source projects and will study the source code
I will learn a new language (I actually got this from Ken Henderson who suggests to learn a new language every year) and rewrite one of the current applications in that language. This way I don’t have to worry about logic problems and design, I just have to translate the code.
I will learn a new technology. I am thinking either WCF or WPF
I will keep updates on Pownce (sorry folks no invites left) everyday The reason I am doing this is so that someone can call me out in case I don’t keep my promise. This is similar to stopping smoking but not telling anyone, if you do that then who knows you stopped so that they can confront you?
I know this is cheesy but I will do it anyway, I will tag 5 people I (kind of) know and I want them to tell us their plans.
Adam Machanic
Louis Davisdson
Peter DeBetta
Mladen Prajdic
Hugo Kornelis
And I will tag 5 people whose blogs I read but I don’t know them
Jeff Smith
Jason Gaylord
Jeff Altwood
Matija Lah
Ward Pond
And you the reader, what will you do in the next 6 months to become a better developer?
Cross posted from here: http://sqlblog.com/blogs/denis_gobo/archive/2007/07/16/1746.aspx
Saturday, July 14, 2007
Best Practice: Backups
What if I told you to take your latest production backup, restore it on a different machine and try using the database? Are you comfortable with that task? Do you think it will work? When was the last time you tested your backups?
Do you even have a backup?
Why am I asking all these things? Because your data is as good as your last good backup. Is your data backed up regularly? You will say “Of course it is we use [Insert expensive backup solution here] for all our enterprise backups”. Prove it, go to work on Monday and ask them to give you the latest backup. I bet out of a 100 people who ask this question to their backup team there will be several people without a backup file.
Here is another problem: three years ago the backups were taking about 1 hour. The backup started at 12 it would be done at 1, at 1:30 a job from another machine would ftp the file down. Two years later the backup takes 2 hours to complete, you didn’t realize this. Can you guess what will happen if you try to restore once of those backup that were moved by FTP? I will tell you it won’t work. What if there is no backup and you do a FTP? Oh yes the 0kb file will be created.
Where do you keep your backups?
Are you backups in the same building? If you would say yes then you have a big problem. Let me tell you a little story. I worked for a company in New York City between 2001 and 2005. This company had their office in WTC tower one. To be safe they kept their backups in WTC tower two. Well I don’t have to tell you what happened with the backup. If you do store your backup offsite (and why wouldn’t you?) make sure it is at least 100 miles away. If you don’t want to go that far from your current location then pick a location which is safe from floods, fires and not worthy to attack.
Where is your Source Code?
Do you backup your source code? Most people will say they keep it in Subversion or Visual Source Safe. But does that get backed up? What happens if your building goes up in flames? What we do is we have a full source code backup every day. In addition to that we also have differential backups every n revisions. We have jobs that create these backups and then FTP them to 3 different locations. If you have 20 developers and you lose 6 hours of work then you have lost 120 * $$ (you do the math). This is the best case scenarios. If the backup was in the building together with all the workstations then you got a lot bigger problem to deal with.
SQL developers are notorious for not using source control. They will tell you that the database backup is their source control. A source control system does not have to be expensive; we use Subversion (which is free and better than VSS). You can either use Tortoise or the plugin for Visual Studio to do your check ins.
Do you even have a backup?
Why am I asking all these things? Because your data is as good as your last good backup. Is your data backed up regularly? You will say “Of course it is we use [Insert expensive backup solution here] for all our enterprise backups”. Prove it, go to work on Monday and ask them to give you the latest backup. I bet out of a 100 people who ask this question to their backup team there will be several people without a backup file.
Here is another problem: three years ago the backups were taking about 1 hour. The backup started at 12 it would be done at 1, at 1:30 a job from another machine would ftp the file down. Two years later the backup takes 2 hours to complete, you didn’t realize this. Can you guess what will happen if you try to restore once of those backup that were moved by FTP? I will tell you it won’t work. What if there is no backup and you do a FTP? Oh yes the 0kb file will be created.
Where do you keep your backups?
Are you backups in the same building? If you would say yes then you have a big problem. Let me tell you a little story. I worked for a company in New York City between 2001 and 2005. This company had their office in WTC tower one. To be safe they kept their backups in WTC tower two. Well I don’t have to tell you what happened with the backup. If you do store your backup offsite (and why wouldn’t you?) make sure it is at least 100 miles away. If you don’t want to go that far from your current location then pick a location which is safe from floods, fires and not worthy to attack.
Where is your Source Code?
Do you backup your source code? Most people will say they keep it in Subversion or Visual Source Safe. But does that get backed up? What happens if your building goes up in flames? What we do is we have a full source code backup every day. In addition to that we also have differential backups every n revisions. We have jobs that create these backups and then FTP them to 3 different locations. If you have 20 developers and you lose 6 hours of work then you have lost 120 * $$ (you do the math). This is the best case scenarios. If the backup was in the building together with all the workstations then you got a lot bigger problem to deal with.
SQL developers are notorious for not using source control. They will tell you that the database backup is their source control. A source control system does not have to be expensive; we use Subversion (which is free and better than VSS). You can either use Tortoise or the plugin for Visual Studio to do your check ins.
DMVStats (A SQL Server 2005 Dynamic Management View Performance Data Warehouse ) Released
Over the last year, Tom Davidson has been working on a tool called DMVstats with some of his CAT colleagues. DMVstats collects performance oriented DMVs into a data warehouse, and provides a methodology called 'Waits' and 'Queues' to identify and track down performance issues. Drill-through analysis is provided by reporting services reports.
DMVStats 1.01
A SQL Server 2005 Dynamic Management View Performance Data Warehouse
Introduction
Microsoft SQL Server 2005 provides Dynamic Management Views (DMVs) to expose valuable information that you can use for performance analysis. DMVstats 1.0 is an application that can collect, analyze and report on SQL Server 2005 DMV performance data. DMVstats does not support Microsoft SQL Server 2000 and earlier versions.
Main Components
The three main components of DMVstats are:
• DMV data collection
• DMV data warehouse repository
• Analysis and reporting.
Data collection is managed by SQL Agent jobs. The DMVstats data warehouse is called DMVstatsDB. Analysis and reporting is provided by means of Reporting Services reports.
Download it here: http://www.codeplex.com/sqldmvstats/
Friday, July 13, 2007
Summer SQL Teaser Datetime Yet Again
Okay one more quick teaser
You have this date '2007-01-01 00:00:00.001'
When adding 1 or 2 milliseconds to that date what will be the result?
SELECT DATEADD(ms,1,CONVERT(datetime, '2007-01-01 00:00:00.001'))
SELECT DATEADD(ms,2,CONVERT(datetime, '2007-01-01 00:00:00.001'))
You have this date '2007-01-01 00:00:00.001'
When adding 1 or 2 milliseconds to that date what will be the result?
SELECT DATEADD(ms,1,CONVERT(datetime, '2007-01-01 00:00:00.001'))
SELECT DATEADD(ms,2,CONVERT(datetime, '2007-01-01 00:00:00.001'))
Summer SQL Teaser: Datetime
First create this table
CREATE TABLE #DateMess (SomeDate datetime)
INSERT #DateMess VALUES('20070710')
INSERT #DateMess VALUES('20070711')
INSERT #DateMess VALUES('20070712')
INSERT #DateMess VALUES('20070713')
This should be easy for most people, but not everyone knows this.
Without running the query do you know how many rows you will get back from the query
SELECT *
FROM #DateMess
WHERE SomeDate <= '2007-07-12 23:59:59.999'
I created this teaser because of a response that Celko made here:
http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_thread/thread/345a73f93cf6a684/
CREATE TABLE #DateMess (SomeDate datetime)
INSERT #DateMess VALUES('20070710')
INSERT #DateMess VALUES('20070711')
INSERT #DateMess VALUES('20070712')
INSERT #DateMess VALUES('20070713')
This should be easy for most people, but not everyone knows this.
Without running the query do you know how many rows you will get back from the query
SELECT *
FROM #DateMess
WHERE SomeDate <= '2007-07-12 23:59:59.999'
I created this teaser because of a response that Celko made here:
http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_thread/thread/345a73f93cf6a684/
Wednesday, July 11, 2007
Oracle Unveils Oracle Database 11g
I know a lot of us don't just work with SQL Server so I decided to share this one
Oracle today introduced Oracle(r) Database 11g, the latest release of the world's most popular database. With more than 400 features, 15 million test hours, and 36,000 person-months of development, Oracle Database 11g is the most innovative and highest quality software product Oracle have ever announced.
"Oracle Database 11g, built on 30 years of design experience, delivers the next generation of enterprise information management," said Andy Mendelsohn, senior vice president of Database Server Technologies, Oracle. "More than ever, our customers are facing the challenges of, rapid data growth, increased data integration, and data connectivity IT cost pressures. Oracle Database 10g pioneered grid computing, and more than half of Oracle customers have moved to that release. Oracle Database 11g delivers the key features our customers have asked for to accelerate broad adoption and growth of Oracle grids; representing real innovation, that addresses real challenges, as told to us by real customers."
Oracle Database 11g can help organizations take control of their enterprise information, gain better business insight, and quickly and confidently adapt to an increasingly changing competitive environment. To do this, the new release extends Oracle's unique database clustering, data center automation, and workload management capabilities. With secure, highly available and scalable grids of low-cost servers and storage, Oracle customers can tackle the most demanding transaction processing, data warehousing, and content management applications.
Real Application Testing Helps Reduce Time, Risk and Cost of Change
Oracle Database 11g features advanced self-management and automation features to help organizations meet service level agreements. For example, with organizations facing regular database and operating system software upgrades, and hardware and system changes, Oracle Database 11g introduces Oracle Real Application Testing, making it the first database to help customers test and manage changes to their IT environment quickly, in a controlled, cost effective manner.
Increase Return On Investment for Disaster Recovery Solutions
In Oracle Database 11g, Oracle Data Guard enables customers to use their standby database to improve performance in their production environments as well as provide protection from system failures and site-wide disasters. Oracle Data Guard uniquely enables simultaneous read and recovery of a single standby database making it available for reporting, backup, testing and 'rolling' upgrades to production databases. By offloading workloads from production to a standby system, Oracle Data Guard helps enhance the performance of production systems and provides a more cost-effective disaster recovery solution.
Enhanced Information Lifecycle Management and Storage Management
Oracle Database 11g has significant new data partitioning and compression capabilities, for more cost-effective Information Lifecycle Management and storage management. Oracle Database 11g automates many manual data partitioning operations and extends existing range, hash and list partitioning to include interval, reference and virtual column partitioning. In addition, Oracle Database 11g provides a complete set of composite partitioning options, allowing storage management that is driven by business rules.
Building on its long-standing data compression capabilities, Oracle Database 11g offers advanced data compression for both structured and unstructured (LOB) data managed in transaction processing, data warehousing, and content management environments. Compression ratios of 2x to 3x or more for all data can be achieved with the new advanced compression capabilities in Oracle Database 11g.
Total Recall of Data Changes
The new release also features "Oracle Total Recall," enabling administrators to query data in designated tables "as of" earlier times in the past. This offers an easy, practical way to add a time dimension to data for change tracking, auditing, and compliance.
Maximum Availability of Information
Oracle has consistently led the industry in protecting database applications from planned and unplanned downtime. Oracle Database 11g continues this lead by making it easier for administrators to meet their users' availability expectations. New availability features include Oracle Flashback Transaction which makes it easy to back out a transaction made in error, as well as any dependent transactions; Parallel Backup and Restore which helps improve the backup and restore performance of very large databases; and 'hot patching,' which improves system availability by allowing database patches to be applied without the need to shut databases down. In addition, a new advisor - Data Recovery Advisor - helps administrators significantly reduce recovery downtime by automating problem investigation, intelligently determining recovery plan and handling multiple failure situations.
Oracle Fast Files
The next-generation capability for storing large objects (LOBs) such as images, large text objects, or advanced data types � including XML, medical imaging, and three-dimensional objects - within the database. Oracle Fast Files offers database applications performance fully comparable to file systems. By storing a wider range of enterprise information and retrieving it quickly and easily, enterprises can know more about their business and adapt more rapidly.
Faster XML
Oracle Database 11g includes significant performance enhancements to XML DB, a feature of Oracle database that enables customers to natively store, and manipulate XML data. Support for binary XML has been added offering customers a choice of XML storage options to match their specific application and performance requirements. XML DB also enables manipulation of XML data using industry standard interfaces with support for XQuery, Java Specification Requests (JSR)-170 and SQL/XML standards.
Transparent Encryption
Oracle Database 11g continues to build on its unmatched security capabilities through the addition of significant enhancements. The new release features improved Oracle Transparent Data Encryption capabilities beyond column level encryption. Oracle Database 11g offers tablespace encryption that can be utilized to encrypt entire tables, indexes, and other data storage. Encryption is also provided for LOBs stored in the database.
Embedded OLAP Cubes
Oracle Database 11g also provides data warehousing innovations. OLAP cubes are enhanced to behave as materialized views in the database. This allows developers to use industry standard SQL for data query, but still benefit from the high performance delivered by an OLAP cube. New Continuous Query Notification features allow applications to be immediately notified when important changes are made to database data without burdening the database with constant polling.
Connection Pooling and Query Result Caches
The performance and scalability features in Oracle Database 11g are designed to help organizations maintain a highly performant, scalable infrastructure to provide users' with the best quality of service. Oracle Database 11g further enhances Oracle's position as the industry's performance and scalability leader with new features such as Query Result Caches which improves application performance and scalability by caching and reusing the results of often called database queries and functions in database and application tiers, and Database Resident Connection Pooling which improves the scalability of web-based systems by providing connection pooling for non-multi-threaded applications.
Enhanced Application Development
Oracle Database 11g offers developers a choice of development tools, and a streamlined application development process that takes full advantage of key Oracle Database 11g features. These include new features such as Client Side Caching, Binary XML for faster application performance, XML processing, and the storing and retrieving of files. In addition, Oracle Database 11g also includes a new Java just-in-time Compiler to execute database Java procedures faster without the need for a third party compiler; native integration with Visual Studio 2005 for developing .NET applications on Oracle; Access migration tools with Oracle Application Express; and SQL Developer easy query building feature for fast coding of SQL and PL/SQL routines.
Enhanced Self-Management and Automation
The manageability features in Oracle Database 11g are designed to help organizations easily manage enterprise grids and deliver on their users' service level expectations. Oracle Database 11g introduces more self-management and automation that will help customers reduce their system management costs, while increasing performance, scalability, availability and security of their database applications. New manageability capabilities in Oracle Database 11g include Automatic SQL and memory tuning, a new Partitioning Advisor which automatically advises administrators on how to partition tables and indexes in order to improve performance, and enhanced performance diagnostics for database clusters. In addition, Oracle Database 11g includes a new Support Workbench which provides an easy-to-use interface that presents database health-related incidents to administrators along with information on how to quickly manage the resolution of incidents.
Oracle is the #1 Database: Gartner 2006 Worldwide RDBMS Market Share Reports 47.1 Percent Share for Oracle
Gartner recently published their market share numbers by operating system for 2006 based on total software revenues. According to Gartner, Oracle:
* Has 47.1 percent share (up from 46.8 percent in 2005);
* Has revenue growth of 14.9 percent, faster than the market average of 14.2 percent with US$7.2 Billions in revenues; and,
* Continues to hold more market share than its two closest competitors combined.
About Oracle Database 11g
Oracle Database is the only database designed for grid computing. With the release of Oracle Database 11g, Oracle is making the management of enterprise information easier than ever; enabling customers to know more about their business and innovate more quickly. Oracle Database 11g delivers superior performance, scalability, availability, security and ease of management on a low-cost grid of industry standard storage and servers. Oracle Database 11g is designed to be effectively deployed on everything from small blade servers to the biggest SMP servers and clusters of all sizes. It features automated management capabilities for easy, cost-effective operation. Oracle Database 11g's unique ability to manage all data from traditional business information to XML and 3D spatial information makes it the ideal choice to power transaction processing, data warehousing, and content management applications.
Oracle today introduced Oracle(r) Database 11g, the latest release of the world's most popular database. With more than 400 features, 15 million test hours, and 36,000 person-months of development, Oracle Database 11g is the most innovative and highest quality software product Oracle have ever announced.
"Oracle Database 11g, built on 30 years of design experience, delivers the next generation of enterprise information management," said Andy Mendelsohn, senior vice president of Database Server Technologies, Oracle. "More than ever, our customers are facing the challenges of, rapid data growth, increased data integration, and data connectivity IT cost pressures. Oracle Database 10g pioneered grid computing, and more than half of Oracle customers have moved to that release. Oracle Database 11g delivers the key features our customers have asked for to accelerate broad adoption and growth of Oracle grids; representing real innovation, that addresses real challenges, as told to us by real customers."
Oracle Database 11g can help organizations take control of their enterprise information, gain better business insight, and quickly and confidently adapt to an increasingly changing competitive environment. To do this, the new release extends Oracle's unique database clustering, data center automation, and workload management capabilities. With secure, highly available and scalable grids of low-cost servers and storage, Oracle customers can tackle the most demanding transaction processing, data warehousing, and content management applications.
Real Application Testing Helps Reduce Time, Risk and Cost of Change
Oracle Database 11g features advanced self-management and automation features to help organizations meet service level agreements. For example, with organizations facing regular database and operating system software upgrades, and hardware and system changes, Oracle Database 11g introduces Oracle Real Application Testing, making it the first database to help customers test and manage changes to their IT environment quickly, in a controlled, cost effective manner.
Increase Return On Investment for Disaster Recovery Solutions
In Oracle Database 11g, Oracle Data Guard enables customers to use their standby database to improve performance in their production environments as well as provide protection from system failures and site-wide disasters. Oracle Data Guard uniquely enables simultaneous read and recovery of a single standby database making it available for reporting, backup, testing and 'rolling' upgrades to production databases. By offloading workloads from production to a standby system, Oracle Data Guard helps enhance the performance of production systems and provides a more cost-effective disaster recovery solution.
Enhanced Information Lifecycle Management and Storage Management
Oracle Database 11g has significant new data partitioning and compression capabilities, for more cost-effective Information Lifecycle Management and storage management. Oracle Database 11g automates many manual data partitioning operations and extends existing range, hash and list partitioning to include interval, reference and virtual column partitioning. In addition, Oracle Database 11g provides a complete set of composite partitioning options, allowing storage management that is driven by business rules.
Building on its long-standing data compression capabilities, Oracle Database 11g offers advanced data compression for both structured and unstructured (LOB) data managed in transaction processing, data warehousing, and content management environments. Compression ratios of 2x to 3x or more for all data can be achieved with the new advanced compression capabilities in Oracle Database 11g.
Total Recall of Data Changes
The new release also features "Oracle Total Recall," enabling administrators to query data in designated tables "as of" earlier times in the past. This offers an easy, practical way to add a time dimension to data for change tracking, auditing, and compliance.
Maximum Availability of Information
Oracle has consistently led the industry in protecting database applications from planned and unplanned downtime. Oracle Database 11g continues this lead by making it easier for administrators to meet their users' availability expectations. New availability features include Oracle Flashback Transaction which makes it easy to back out a transaction made in error, as well as any dependent transactions; Parallel Backup and Restore which helps improve the backup and restore performance of very large databases; and 'hot patching,' which improves system availability by allowing database patches to be applied without the need to shut databases down. In addition, a new advisor - Data Recovery Advisor - helps administrators significantly reduce recovery downtime by automating problem investigation, intelligently determining recovery plan and handling multiple failure situations.
Oracle Fast Files
The next-generation capability for storing large objects (LOBs) such as images, large text objects, or advanced data types � including XML, medical imaging, and three-dimensional objects - within the database. Oracle Fast Files offers database applications performance fully comparable to file systems. By storing a wider range of enterprise information and retrieving it quickly and easily, enterprises can know more about their business and adapt more rapidly.
Faster XML
Oracle Database 11g includes significant performance enhancements to XML DB, a feature of Oracle database that enables customers to natively store, and manipulate XML data. Support for binary XML has been added offering customers a choice of XML storage options to match their specific application and performance requirements. XML DB also enables manipulation of XML data using industry standard interfaces with support for XQuery, Java Specification Requests (JSR)-170 and SQL/XML standards.
Transparent Encryption
Oracle Database 11g continues to build on its unmatched security capabilities through the addition of significant enhancements. The new release features improved Oracle Transparent Data Encryption capabilities beyond column level encryption. Oracle Database 11g offers tablespace encryption that can be utilized to encrypt entire tables, indexes, and other data storage. Encryption is also provided for LOBs stored in the database.
Embedded OLAP Cubes
Oracle Database 11g also provides data warehousing innovations. OLAP cubes are enhanced to behave as materialized views in the database. This allows developers to use industry standard SQL for data query, but still benefit from the high performance delivered by an OLAP cube. New Continuous Query Notification features allow applications to be immediately notified when important changes are made to database data without burdening the database with constant polling.
Connection Pooling and Query Result Caches
The performance and scalability features in Oracle Database 11g are designed to help organizations maintain a highly performant, scalable infrastructure to provide users' with the best quality of service. Oracle Database 11g further enhances Oracle's position as the industry's performance and scalability leader with new features such as Query Result Caches which improves application performance and scalability by caching and reusing the results of often called database queries and functions in database and application tiers, and Database Resident Connection Pooling which improves the scalability of web-based systems by providing connection pooling for non-multi-threaded applications.
Enhanced Application Development
Oracle Database 11g offers developers a choice of development tools, and a streamlined application development process that takes full advantage of key Oracle Database 11g features. These include new features such as Client Side Caching, Binary XML for faster application performance, XML processing, and the storing and retrieving of files. In addition, Oracle Database 11g also includes a new Java just-in-time Compiler to execute database Java procedures faster without the need for a third party compiler; native integration with Visual Studio 2005 for developing .NET applications on Oracle; Access migration tools with Oracle Application Express; and SQL Developer easy query building feature for fast coding of SQL and PL/SQL routines.
Enhanced Self-Management and Automation
The manageability features in Oracle Database 11g are designed to help organizations easily manage enterprise grids and deliver on their users' service level expectations. Oracle Database 11g introduces more self-management and automation that will help customers reduce their system management costs, while increasing performance, scalability, availability and security of their database applications. New manageability capabilities in Oracle Database 11g include Automatic SQL and memory tuning, a new Partitioning Advisor which automatically advises administrators on how to partition tables and indexes in order to improve performance, and enhanced performance diagnostics for database clusters. In addition, Oracle Database 11g includes a new Support Workbench which provides an easy-to-use interface that presents database health-related incidents to administrators along with information on how to quickly manage the resolution of incidents.
Oracle is the #1 Database: Gartner 2006 Worldwide RDBMS Market Share Reports 47.1 Percent Share for Oracle
Gartner recently published their market share numbers by operating system for 2006 based on total software revenues. According to Gartner, Oracle:
* Has 47.1 percent share (up from 46.8 percent in 2005);
* Has revenue growth of 14.9 percent, faster than the market average of 14.2 percent with US$7.2 Billions in revenues; and,
* Continues to hold more market share than its two closest competitors combined.
About Oracle Database 11g
Oracle Database is the only database designed for grid computing. With the release of Oracle Database 11g, Oracle is making the management of enterprise information easier than ever; enabling customers to know more about their business and innovate more quickly. Oracle Database 11g delivers superior performance, scalability, availability, security and ease of management on a low-cost grid of industry standard storage and servers. Oracle Database 11g is designed to be effectively deployed on everything from small blade servers to the biggest SMP servers and clusters of all sizes. It features automated management capabilities for easy, cost-effective operation. Oracle Database 11g's unique ability to manage all data from traditional business information to XML and 3D spatial information makes it the ideal choice to power transaction processing, data warehousing, and content management applications.
Tuesday, July 10, 2007
SQL Server 2008 will launch on Feb. 27, 2008
Turner announced that Windows Server® 2008, Visual Studio® 2008 and Microsoft SQL Server™ 2008 will launch together at an event in Los Angeles on Feb. 27, 2008, kicking off hundreds of launch events around the world. As the next wave of innovation from Microsoft’s Server and Tools Business, these three products will provide a reliable and security-enhanced enterprise platform, serve as the foundation for the next generation of Web-based service applications, and broadly support virtualization and business intelligence. Windows Server 2008, SQL Server 2008 and Visual Studio 2008 represent tremendous opportunities for partners and customers, and as part of the launch wave throughout 2008, Microsoft is planning extensive and far-reaching IT pro, developer and partner outreach, including worldwide training, online and virtual events, as well as myriad resources that will be made available in the coming months to help ensure partners and customers are ready to capitalize on the new benefits offered by these products.
Read the rest here: http://www.microsoft.com/presspass/press/2007/jul07/07-10WPCDay1PartnersPR.mspx
Read the rest here: http://www.microsoft.com/presspass/press/2007/jul07/07-10WPCDay1PartnersPR.mspx
Giving Away 2 Invites For Pownce
I have 2 invites left for Pownce.
Leave me a comment here (explaining why you want/need that invite, also leave your home page URL) and send an email to sqlservercode AT gmail.com (include the home page you left in the comment) if you want one.
Best 2 comments will get the invite.
I will announce the winners tomorrow (July 11 2007) at 6AM EST
Leave me a comment here (explaining why you want/need that invite, also leave your home page URL) and send an email to sqlservercode AT gmail.com (include the home page you left in the comment) if you want one.
Best 2 comments will get the invite.
I will announce the winners tomorrow (July 11 2007) at 6AM EST
Monday, July 09, 2007
SQL Controversy: Capitalizing Keywords
Do you write code like this?
set ROWCOUNT 10
select Products.ProductName as TenMostExpensiveProducts, Products.UnitPrice
from Products
order by Products.UnitPrice desc
Or like this?
SET ROWCOUNT 10
SELECT Products.ProductName AS TenMostExpensiveProducts, Products.UnitPrice
FROM Products
ORDER BY Products.UnitPrice DESC
Do we need to capitalize the keywords, functions and statements when we have syntax coloring built into the product?
Look if you use SPUFI with DB2 I understand (see image below)
Here is another example this time without color.
set ROWCOUNT 10
select Products.ProductName as TenMostExpensiveProducts, Products.UnitPrice
from Products
order by Products.UnitPrice desc
SET ROWCOUNT 10
SELECT Products.ProductName AS TenMostExpensiveProducts, Products.UnitPrice
FROM Products
ORDER BY Products.UnitPrice DESC
And yes I agree the bottom query is much easier to read
But with syntax coloring do you still need this? It is a big pain in the neck to use that CapsLock/Shift key every time you type a keyword. There are tools of course like SQL Formatter which will make it much easier.
Remember Hungarian notation, In VB you would write sLastName(string), iCounter(integer)? Well that is gone also, who needs it when you have IntelliSense?
I think the lowercase sql code is easier on the eyes. So what do you think?
set ROWCOUNT 10
select Products.ProductName as TenMostExpensiveProducts, Products.UnitPrice
from Products
order by Products.UnitPrice desc
Or like this?
SET ROWCOUNT 10
SELECT Products.ProductName AS TenMostExpensiveProducts, Products.UnitPrice
FROM Products
ORDER BY Products.UnitPrice DESC
Do we need to capitalize the keywords, functions and statements when we have syntax coloring built into the product?
Look if you use SPUFI with DB2 I understand (see image below)
Here is another example this time without color.
set ROWCOUNT 10
select Products.ProductName as TenMostExpensiveProducts, Products.UnitPrice
from Products
order by Products.UnitPrice desc
SET ROWCOUNT 10
SELECT Products.ProductName AS TenMostExpensiveProducts, Products.UnitPrice
FROM Products
ORDER BY Products.UnitPrice DESC
And yes I agree the bottom query is much easier to read
But with syntax coloring do you still need this? It is a big pain in the neck to use that CapsLock/Shift key every time you type a keyword. There are tools of course like SQL Formatter which will make it much easier.
Remember Hungarian notation, In VB you would write sLastName(string), iCounter(integer)? Well that is gone also, who needs it when you have IntelliSense?
I think the lowercase sql code is easier on the eyes. So what do you think?
Friday, July 06, 2007
Summer SQL Teaser Non Existing Database
Here is a simple teaser, BTW I assume you don't have a database named WasabiDb or do you?
USE WasabiDb
IF @@Error <> 0
PRINT 'db doesn''t exist'
USE WasabiDb
GO
IF @@Error <> 0
PRINT 'db doesn''t exist'
If you would run this in one shot (hit F5) how many of the error messages below will you see
Server: Msg 911, Level 16, State 1, Line 1
Could not locate entry in sysdatabases for database 'WasabiDb'. No entry found with that name. Make sure that the name is entered correctly.
And how many 'db doesn't exist' messages will you see
USE WasabiDb
IF @@Error <> 0
PRINT 'db doesn''t exist'
USE WasabiDb
GO
IF @@Error <> 0
PRINT 'db doesn''t exist'
If you would run this in one shot (hit F5) how many of the error messages below will you see
Server: Msg 911, Level 16, State 1, Line 1
Could not locate entry in sysdatabases for database 'WasabiDb'. No entry found with that name. Make sure that the name is entered correctly.
And how many 'db doesn't exist' messages will you see
Wednesday, July 04, 2007
SQL Server 2005 Best Practices Analyzer Released, End Of Support For SQL Server 2000 SP3a In 6 Days
End of Support for SQL Server 2000 Service Pack 3a
Support for SQL Server 2000 Service Pack 3a (SP3a) will end on July 10, 2007.
Microsoft will end technical support on this date, which also includes security updates for this Service Pack. Microsoft is ending support for this product as part of our Service Pack support policy, found http://support.microsoft.com/lifecycle.
Customers running SQL Server 2000 Service Pack 3a are encouraged to migrate to SQL Server 2000 Service Pack 4 or SQL Server 2005. Remaining current on your service pack installation ensures that your products remain supported per the Support Lifecycle policy. Additionally, your software benefits from the many enhancements, fixes, and security updates provided through the latest service pack.
Read more here: http://blogs.msdn.com/sqlreleaseservices/archive/2007/07/02/end-of-support-for-sql-server-2000-service-pack-3a.aspx
SQL Server 2005 Best Practices Analyzer (July 2007) Realeased
It does not say CTP anywhere on this page so I assume that this is a 'production' version.
Get it here: http://www.microsoft.com/downloads/details.aspx?FamilyID=da0531e4-e94c-4991-82fa-f0e3fbd05e63&DisplayLang=en
Support for SQL Server 2000 Service Pack 3a (SP3a) will end on July 10, 2007.
Microsoft will end technical support on this date, which also includes security updates for this Service Pack. Microsoft is ending support for this product as part of our Service Pack support policy, found http://support.microsoft.com/lifecycle.
Customers running SQL Server 2000 Service Pack 3a are encouraged to migrate to SQL Server 2000 Service Pack 4 or SQL Server 2005. Remaining current on your service pack installation ensures that your products remain supported per the Support Lifecycle policy. Additionally, your software benefits from the many enhancements, fixes, and security updates provided through the latest service pack.
Read more here: http://blogs.msdn.com/sqlreleaseservices/archive/2007/07/02/end-of-support-for-sql-server-2000-service-pack-3a.aspx
SQL Server 2005 Best Practices Analyzer (July 2007) Realeased
It does not say CTP anywhere on this page so I assume that this is a 'production' version.
Get it here: http://www.microsoft.com/downloads/details.aspx?FamilyID=da0531e4-e94c-4991-82fa-f0e3fbd05e63&DisplayLang=en
SSIS Script Task In SQL Server 2008 Can Use VB Or C#
Where do I send a thank you letter? Finally we are allowed to use C# in the SQL Server Integration Services Script Task. I always wondered why SQL Server 2005 only uses VB and not C#, you can use C# in the SQLCLR but not in a Script Task. It turns out that SSIS in SQL Server 2005 uses VSA (Visual Studio for Applications) but SQL Server 2008 will use VSTA (Visual Studio Tools for Applications). Lets put these 2 right under each other.
Visual Studio for Applications
Visual Studio Tools for Applications
See the only (confusing) difference is the word Tools. So VSTA does support C#. I guess that if you come from a heavy DTS ActiveX usage background VB would be natural to you. I never felt at home with VB.NET, I switched to C# because I was also using Java and it was easier to make the switch to C#.
Enough whining from me, here are 2 screenshots that I took from the latest SQL Server 2008 June CTP. Have a nice holiday, don't overeat
Visual Studio for Applications
Visual Studio Tools for Applications
See the only (confusing) difference is the word Tools. So VSTA does support C#. I guess that if you come from a heavy DTS ActiveX usage background VB would be natural to you. I never felt at home with VB.NET, I switched to C# because I was also using Java and it was easier to make the switch to C#.
Enough whining from me, here are 2 screenshots that I took from the latest SQL Server 2008 June CTP. Have a nice holiday, don't overeat
Subscribe to:
Posts (Atom)