Test

Indexed View on SQL Server

Views are also known as virtual tables because the result set returned by the view has the same general form as a table with columns and rows, and views can be referenced the same way as tables in SQL statements. The result set of a standard view is not stored permanently in the database. Each time a query references the view, Microsoft® SQL Server™ 2000 dynamically merges the logic needed to build the view result set into the logic needed to build the complete query result set from the data in the base tables. The process of building the view results is called materializing the view. For more information, see View Resolution.

For a standard view, the overhead of dynamically building the result set for each query that references a view can be substantial for views that involve complex processing of large numbers of rows, such as aggregating large amounts of data, or joining many rows. If such views are frequently referenced in queries, you can improve performance by creating a unique clustered index on the view. When a unique clustered index is created on a view, the view is executed and the result set is stored in the database in the same way a table with a clustered index is stored. For more information about the structure used to store clustered indexes, see Clustered Indexes.

Note Indexed views can be created in any edition of SQL Server 2000. In SQL Server 2000 Enterprise Edition, the query optimizer will automatically consider the indexed view. To use an indexed view in all other editions, the NOEXPAND hint must be used.

Another benefit of creating an index on a view is that the optimizer starts using the view index in queries that do not directly name the view in the FROM clause. Existing queries can benefit from the improved efficiency of retrieving data from the indexed view without having to be recoded. For more information, see Resolving Indexes on Views.

Creating a clustered index on a view stores the data as it exists at the time the index is created. An indexed view also automatically reflects modifications made to the data in the base tables after the index is created, the same way an index created on a base table does. As modifications are made to the data in the base tables, the data modifications are also reflected in the data stored in the indexed view. The requirement that the clustered index of the view be unique improves the efficiency with which SQL Server can find the rows in the index that are affected by any data modification.

Indexed views can be more complex to maintain than indexes on base tables. You should create indexes only on views where the improved speed in retrieving results outweighs the increased overhead of making modifications. This usually occurs for views that are mapped over relatively static data, process many rows, and are referenced by many queries.

Requirements for the View

A view must meet these requirements before you can create a clustered index on it:

• The ANSI_NULLS and QUOTED_IDENTIFIER options must have been set to ON when the CREATE VIEW statement was executed. The OBJECTPROPERTY function reports this for views through the ExecIsAnsiNullsOn or ExecIsQuotedIdentOn properties.
  
  
• The ANSI_NULLS option must have been set to ON for the execution of all CREATE TABLE statements that create tables referenced by the view.
  
  
• The view must not reference any other views, only base tables.
  
  
• All base tables referenced by the view must be in the same database as the view and have the same owner as the view.
  
  
• The view must be created with the SCHEMABINDING option. SCHEMABINDING binds the view to the schema of the underlying base tables.
  
  
• User-defined functions referenced in the view must have been created with the SCHEMABINDING option.
  
  
• Tables and user-defined functions must be referenced by two-part names. One-part, three-part, and four-part names are not allowed.
  
  
• All functions referenced by expressions in the view must be deterministic. The IsDeterministic property of the OBJECTPROPERTY function reports if a user-defined function is deterministic. For more information, see Deterministic and Nondeterministic Functions.
  
  
• The SELECT statement in the view cannot contain these Transact-SQL syntax elements:
  • The select list cannot use the * or table_name.* syntax to specify columns. Column names must be explicitly stated.
    
    
  • A table column name used as a simple expression cannot be specified in more than one view column. A column can be referenced multiple times provided all, or all but one, reference to the column is part of a complex expression or a parameter to a function. For example, this select list is invalid:

    SELECT ColumnA, ColumnB, ColumnA
    

    These select lists are valid:

    SELECT ColumnA, COUNT(ColumnA), ColumnA + Column B AS AddColAColB FROM T1
    
    SELECT SUM(ColumnA), ColumnA % ColumnB AS ModuloColAColB, COUNT_BIG(*) FROM T1 GROUP BY ColumnA
    

  • A derived table.
    
    
  • Rowset functions.
    
    
  • UNION operator.
    
    
  • Subqueries.
    
    
  • Outer or self joins.
    
    
  • TOP clause.
    
    
  • ORDER BY clause.
    
    
  • DISTINCT keyword.
    
    
  • COUNT(*) (COUNT_BIG(*) is allowed.)
    
    
  • The AVG, MAX, MIN, STDEV, STDEVP, VAR, or VARP aggregate functions. If AVG, MAX, MIN, STDEV, STDEVP, VAR, or VARP are specified in queries referencing the indexed view, the optimizer can often calculate the needed result if the view select list contains these substitute functions.
  
  

  • Complex aggregate function	Substitute simple aggregate functions
    AVG(X)	SUM(X), COUNT_BIG(X)
    STDEV(X)	SUM(X), COUNT_BIG(X), SUM(X**2)
    STDEVP(X)	SUM(X), COUNT_BIG(X), SUM(X**2)
    VAR(X)	SUM(X), COUNT_BIG(X), SUM(X**2)
    VARP(X)	SUM(X), COUNT_BIG(X), SUM(X**2)

    
    

    For example, an indexed view select list cannot contain the expression AVG(SomeColumn). If the view select list contains the expressions SUM(SomeColumn) and COUNT_BIG(SomeColumn), SQL Server can calculate the average for a query that references the view and specifies AVG(SomeColumn).

  • A SUM function that references a nullable expression.
    
    
  • The full-text predicates CONTAINS or FREETEXT.
    
    
  • COMPUTE or COMPUTE BY clause.
• If GROUP BY is not specified, the view select list cannot contain aggregate expressions.
  
  
• If GROUP BY is specified, the view select list must contain a COUNT_BIG(*) expression, and the view definition cannot specify HAVING, CUBE, or ROLLUP.
  
  
• A column resulting from an expression that either evaluates to a float value or uses float expressions for its evaluation cannot be a key of an index in an indexed view or a table.

Example for creating Indexed View :

USE AdventureWorks;
GO
--Set the options to support indexed views.
SET NUMERIC_ROUNDABORT OFF;
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT,
    QUOTED_IDENTIFIER, ANSI_NULLS ON;
GO
--Create view with schemabinding.
IF OBJECT_ID ('Sales.vOrders', 'view') IS NOT NULL
DROP VIEW Sales.vOrders ;
GO
CREATE VIEW Sales.vOrders
WITH SCHEMABINDING
AS
    SELECT SUM(UnitPrice*OrderQty*(1.00-UnitPriceDiscount)) AS Revenue,
        OrderDate, ProductID, COUNT_BIG(*) AS COUNT
    FROM Sales.SalesOrderDetail AS od, Sales.SalesOrderHeader AS o
    WHERE od.SalesOrderID = o.SalesOrderID
    GROUP BY OrderDate, ProductID;
GO
--Create an index on the view.
CREATE UNIQUE CLUSTERED INDEX IDX_V1 
    ON Sales.vOrders (OrderDate, ProductID);
GO
--This query can use the indexed view even though the view is 
--not specified in the FROM clause.
SELECT SUM(UnitPrice*OrderQty*(1.00-UnitPriceDiscount)) AS Rev, 
    OrderDate, ProductID
FROM Sales.SalesOrderDetail AS od
    JOIN Sales.SalesOrderHeader AS o ON od.SalesOrderID=o.SalesOrderID
        AND ProductID BETWEEN 700 and 800
        AND OrderDate >= CONVERT(datetime,'05/01/2002',101)
GROUP BY OrderDate, ProductID
ORDER BY Rev DESC;
GO
--This query can use the above indexed view.
SELECT  OrderDate, SUM(UnitPrice*OrderQty*(1.00-UnitPriceDiscount)) AS Rev
FROM Sales.SalesOrderDetail AS od
    JOIN Sales.SalesOrderHeader AS o ON od.SalesOrderID=o.SalesOrderID
        AND DATEPART(mm,OrderDate)= 3
        AND DATEPART(yy,OrderDate) = 2002
GROUP BY OrderDate
ORDER BY OrderDate ASC;
GO

Source : 
http://msdn.microsoft.com/en-us/library/aa933148.aspx
http://msdn.microsoft.com/en-us/library/ms191432.aspx

Simone Inzaghi: I Stayed Out Of Love For Lazio

Lazio's goal scoring hero this weekend, Simone Inzaghi has declared that he stayed at the capital club and of love for the Biancocelesti colours...

A late goal from Simone Inzaghi spared Lazio’s blushes at the Stadio Olimpico as the league leaders managed to salvage a 1-1 draw against newly promoted Lecce. The hosts looked to be heading for only their second defeat of the season following Simone Tiribocchi’s first-half opener for Lecce, but Biancoceleste substitute Inzaghi was in the right place at the right time to snatch an equaliser and a point for Lazio in the final minute.

All the talk at Lazio in recent times has revolved around the goals scored by Goran Pandev and Mauro Zarate, as well as the imminent return of captain Tomasso Rocchi from injury. This week is different though, as Simone Inzaghi grabbed the headlines after his late goal salvaged a point against Lecce this weekend and kept them top of the table.

Despite going through tough times with the club over the last four years, and feeling unwanted for spells, the player stayed in the capital to fight for his spot on the team. “I was sure that I would return to being competitive,” said the player. “I trained at Formello, away from the squad, working hard and with determination, hoping that the situation would change,” said the player.

“It's not easy to work hard when you know that you are starting from last place, but I didn't give up. I stayed because of my love for this club,” asserted Inzaghi.

[ From Milist Lazio-Indonesia]

Lotito Has High Scudetto Hopes

Lazio president Claudio Lotito Believes his side can win the Scudetto this season and he has warned the usual suspects that his players will fight right until the end...

The Aquilotti are flying sky high as they sit comfortably at the top of the Serie A table with Scudetto rivals Inter. Lotito has been building the club up from the brink when they fell into crisis under the Sergio Cragnotti reign. The chief believes his side can go all the way this term and repeat the glory the Biancocelesti enjoyed back in 2000, when they won the Tricolore last.

"We will be fighting every team this season," Lotito said. "We have constructed a squad with the understanding that they are not inferior or have anything less than the other teams. "The team has to maintain the determination and the humility that has been shown thus far. It will be a difficult season and many teams have strengthened but the results are the most important at the end of the day."

Lotito will be hoping his side continue to rack up points. Lazio have the added bonus of not playing in Europe this term, unlike the rest of their Scudetto competitors.

[From Milist Lazio-Indonesia]

What are you gonna be ?

Watch out with your logo !!

What do you think ? It looks like a child doing ...



























How about this ? What are they doing ? hehehe ...








Can you imagine this ?

















It's Weird logo

















Well, it looks like a dancing people ... but ... what if you see it clearly ? .... can you imagine it ?










hmmm ... i think it's missplaced











How about this ?

SQL Server Database Tuning

Tuning your SQL Server !!! Well, I've already heard about it because in my old-office was a problem with the SQL Server. I was using SQL Server 2005 SP2. The query was so long to be executed. And At the first time, i had no idea to fix it. But i googgled in the internet and find something that tuning my SQL Server. I will write down the tuning list, If you have any idea please add it.

a. Reside your TempDB and your User Database.
Why ? Because TempDB is used for temporary Data. So it needs more I/O to improve the performance. If your User Database puts on the same drive with TempDB, The I/O will getting busy, cause they need to serve TempDB Database and your database.

b. Create Filegroup that you need
Creating filegroup as many as you need. Don't create only .mdf and .ldf file. Because if your database is getting bigger than now, the I/O can be stuck. So, you'd better create a several filegroups. it will help your performance. The best practice is build a filegroup based on your processor. if your processor is 4 and you can build 4 datafile at one filegroup.

c. Use Autogrow by Mb, Not Percentage
It's important. Because if you use Mb to growth your database, it's constant. But if you use percentage, it's flexibel. The Problem is, again and again if your database is getting bigger and bigger, Let say 20GB and your Autogrow percentage is 10%. The SQL Engine will growth your database about 2GB. it's need more time. But if you use Mb, Let say 100MB, it's always constant growth - always 100MB- and it needs less time than percentage

d. Create the proper Index
Index is the important part on your database. You can use index to improve your search data but it will hurt when your database is doing Insert and Update. So, Create the proper index, not just create index. I've read some articles, When your indexed columns is less than 5% data, so you need create the index, but more than that, i suggest you to remove the index. Index also needs at column that become a primary in tables. Because primary key is need to join tables

e. Create your query efficiently
Well, it's too difficult to be explained. But you can use execution plan to find the query isn't efficient to be executed. Let say, Don't use '*' in your select query. and the others. Don't use cursor oftenly. Cursor needs more processor and memory to allocate it. So, your processor will busy. But don't forget, sometimes cursor is very useful

Ok, maybe it's only that i know. I hope it would be useful..

Lazio News : 2 Points Thrown away

When you’re coming off a stretch of games against Sampdoria, Milan, Fiorentina and Torino (on the road), and you’ve picked up 9 of 12 points, there’s no way in hell you can be happy with a draw against Lecce. Especially when you’re playing at home.

The season may be long, but if I’ve learned anything as a calcio fan, it’s that every single point counts, and we should not be dropping any against Lecce. No way no how. These are games that need to be won.

That said, we should be careful about being too dramatic. This wasn’t a loss after all, and if you were paying attention, you would have noticed a bevy of scoring chances wasted, as well as a never say die spirit that I think really showed how much character and determination the team has. Put simply, the scoreline flatters Lecce tremendously, because had Pandev, Ledesma, Mauri, and Foggia converted some of their great opportunities, it could (and probably should) have been a rout. Sometimes, things just don’t go your way.

This isn’t to say there wasn’t anything troubling from this performance, as the defense and goalkeeping on Lecce’s goal left a whole lot to be desired. It’s true that the header clearly caught Carrizo by surprise, but the half-assed swipe he took at the ball was embarrassing, and definitely not his finest work. I watched him play a lot last year in Argentina, so I know how good he is. But with gaffe’s like this (and, some would say, in the Milan game), I can see why some Biancoceleste fans are wondering just what kind of keeper we have. Six games, I think, is pretty early to start criticizing a player still adjusting to a new league and country, but it’s obvious that he can’t keep getting beaten by crap like this…especially when Lecce provided little in the way of offense before or after that fluke.

Of course, Carrizo can’t do everything alone either, and Rozehnal and Siviglia should be taken to task for being out of position, and allowing Tiribocchi to even get a head on the ball. I’ve said in the past that I thought Radu and Rozehnal would be our strongest central pairing, but now I’m wondering if Cribari doesn’t offer a touch more security. Hard to say if he’s a better bet than the Czech international, but I’m curious what Delio will decide next game. I’d also like to see Mourad Meghni get a start over Mauri, but who knows if Rossi will make that change, especially since the overall team performance was for the most part, solid.

If a mistake was made on Saturday though, it was probably in waiting so long to bring Inzaghi on. For most of the game Lecce forced the Aquile out on the wings, crowding the middle and jamming up to seven men in the box. Free flowing, pass-happy football was not going to be on the cards. Basically, the game was calling out for either a big target man (which we don’t have) or an out and out poacher of the highest order (which we do, even if he’s rusty). Inzaghino’s name was written all over this affair, and he didn’t waste much time tying it up. Delio might have to get Simone more involved, at least until Balboa comes back.

Anyway, still sharing top spot…let’s get back to winning ways in Bologna. Forza Lazio.

[From www.lazio.theoffside.com]

Inzaghi: "I apologize for anger"

Lazio-Lecce is also (mostly) Simone Inzaghi. And his outburst of anger worldwide, with the ball between his hands and the race at midfield, a goal that lifted the biancoceleste the bitter taste of defeat, a goal that should have returned the momentum to resume the journey. But meanwhile repeated cry that "bastard, bastard", addressed to the bench, his coach. Why did not play because it was put out pink.

"I apologize, I escaped, that goal was as free of the one, I was hoping to come in first, I wanted to break the world ... Now I have to thank Rossi, who gave me this opportunity to play. My summer was difficult, very difficult, but I never spring. The goal is my life, but we must play to score, I will always get ready. "

Rossi will forgive him? Says the coach: "I'm happy for him, is the only one who can do those dirty goals." And the president Lotito said: "With Simone I have a wonderful relationship." Forgiveness is what made it. O seems?

[From Milist Lazio-Indonesia]

Yesterday was Yesterday ...

Yesterday was sunday. I had just arrive from pekalongan by train to jakarta and changed it using motorcycle to depok. I had just arrived at jakarta at 5 o'clock in the morning and went to my home at jakarta to took my motorcycle. and then i went to depok - of course after shubuh prays - and arrived at 6 o'clock. Well, actually, i was so tired and went to sleep but i couldn't. So, I played Internet and Chatted with my friend. After 7.30 AM, i went to sleep .. because i was so sleepy. Zzzz... Zzzzz... Zzzzz....

I woke up at 00.30 PM and my mother asked to join a lunch at "Mang Engking" UI. At the first time, i didn't approve it because i had an encounter at wallstreet 5 PM. but later, i want to because it's relation about food ... - I love food and i love eat -. Well they departed at 01.15 PM by car and i went to "Mang Engking" UI by motorcyle. Because i wanted to go at wallstreet after lunch at there. but, in the middle of my ... it was raining and i was getting wet. The raining was so hard and i had to find a shelter before my clothes are more getting wet. I had waited for 5 minutes, 10 minutes ... Hmmmm..... there was not a sign the rain would be calm. i was going through to the middle of raining.

Because of raining, many streets at Depok had a waterpool and there was a traffic jam. It made me to slow my motorcyle... At last ... i had finally arrived at "Mang Engking" UI ... yuhuuuuuuu ... i was going to lunch .... ssrrrrrrrrppppppppppppppp ....
After lunch my family ... i went to wallstreet to met my teacher there... and you know, i was late to come in the class. Although i was late, the teacher let me in to join the class. he is a new teacher at wallstreet. his name is eric from canada. Before he joined wallstreet, he has already worked at EF - English First - for 3,5 years. and he said ... "EF sucks... they just want a money, the methodology is sucks... i think wallstreet it's better" .... hehehehehe .... Are you sure eric ? Wallstreet better than EF ? for me ... sometimes, Wallstreet methodology is not good .... but if we follow the method .. it's really good .... but i don't know.

Finally, i had passed the encounter and move to Level UpperWaystage 2.. Amieen .... and i went to home and watching The IRON MAN movie. I didn't watch the Lega Calcio last night cause My favorite Team "Lazio" has played.

IRON MAN The Movie

Last night i saw IRON MAN The movie. i think it was fantastic movie. The story is like another american superhero, such as spiderman, fantastic four, batman and others. But, IRON Man isn't a mutant. he is real a man who just wearing a steel suit. The Visual effect is wonderful thing. When the missile flying in the air and spread to be many sub missile. WOW ... That's great. And also IRON MAN visual effect when he battled in the air against two raptors.

Tony Sparks is an intellegent person who build a missile by himself. He have a company called Starks Industries. When he demonstrated in Afganistan about his new missile - Jericho -. he is kidnapped by terorist and made him to build a jericho missile. In prison at afganistan, Tony Sparks is dying. Fortunately, his life is helped by another american person at afganistan, Yinsen. Tony lost his heart when he was kidnapped and Yinsen changes tony's heart with electromagnetic for his life.

At prison, Tony don't make a missile instead making a suit from steel. The terorist knows it and want to stop it. But it's too late, because the suit has made but haven't installed yet. Yinsen knows it, and he sacrifices himself just for having a time to install the suit. After installed, Tony battles with the afganistan terorrist. and they made him escaped from prison. In the middle of dessert. Tony is helped by his friends, Rhodes and taking to the Stark Industries, helding a press conferences with journalist. and he said "I am Shutting down this company". his friend, Obadiah is confuse. What happened? Why?. In private chat, Tony said that he didn't want to make something to kill people, vise versa he wanted to make something to help people. That's why he shutdown the company.

At his lab, Tony is planning to build a new suit from Steel better than before. Before that, he changed his electromagnetic heart with the new one and the old one is placed in his lab. The Suit can flying, have a weapon, and computerized to detect a target enemy. and the suit is more flexible than before he built. And after testing many case using a new suit. there is a problem with the ice. When he is flying to out of spaces, it's freeze and all computer is down. but after that he fix it. There's a war in afganistan, civilian against terrorist. And Tony Sparks knew it and flying to the afganistan to save the civilian using a new suit and he did it. After he saved the civilian, he comes back to malibu. But USA Radars detected him like a something unknown things. They sent 2 raptors to case it. and the battle is begin. At the end of story of battle, Tony made it.

Meanwhile, Obadiah wants to make a suit like tony does. he calls a professor to build the new suit. It's bigger than tony's suit but more slower and is not flexible as suit as tony. But the professor is confuse because they can't build a suit. "It's Impossible, Even the power doesn't exits" said the professor, and obadiah are very angry and says "Tony built this suit in prison using a crap". But it's known that the power is from tony's heart - Electromagnetic heart - and steal it from tony. And It works. The power is on, and The new suit - called Sector 16 - has made it. The battle is begin, The Sector 16 versus The IRON MAN. Because the Sector 16 is bigger and stronger than Tony's Have - IRON MAN -, he can't be beaten. IRON MAN Always loose the way to win and lost the change to win. But, IRON MAN have a new capability. It can fly and unfortunately, The Sector 16 also can fly. Tony escapes to out of space and Obadiah with the Sector 16 is chasing it. But The problem is emerge and Tony Said "How's your ice problem ?" Obadiah said "What the problem with the ice? "... and The Sector 16 computer is down and fall to the ground. IRON MAN thinks it will be over, but isn't. The Sector 16 is wake up and chasing the IRON MAN. They are battle at the Roof. and again, IRON MAN hasn't a power to beat Sector 16. and also have a problem with the Power, The power is empty. IRON MAN has a plan to kill Sector 16 using the electromagnetic at his lab. and It works. Sector 16 computer now are really down.

At the end of story, at a press conferences. Tony Said "The Truth is .... I am the IRON MAN"