Enlightment People

Menghilangkan Tag HTML dari String

2008-08-20T03:46:00.000-07:00

REReplaceNoCase(string,"<[^>]*>","","ALL")

WORKING COLDFUSION 8 AND SQL 2005 XML

2008-08-01T11:39:00.000-07:00

SELECT Tag, Parent,

[Agents!1!],

[Agent!2!AgentID],

[Agent!2!Fname!Element],

[Agent!2!SSN!Element],

[AddressCollection!3!Element],

[Address!4!AddressType!Element],

[Address!4!Address1!Element],

[Address!4!Address2!Element],

[Address!4!City!Element]

FROM (

SELECT

1 AS Tag,

NULL AS Parent,

0 AS Sort,

NULL AS 'Agents!1!',

NULL AS 'Agent!2!AgentID',

NULL AS 'Agent!2!Fname!Element',

NULL AS 'Agent!2!SSN!Element',

NULL AS 'AddressCollection!3!Element',

NULL AS 'Address!4!AddressType!Element',

NULL AS 'Address!4!Address1!Element',

NULL AS 'Address!4!Address2!Element',

NULL AS 'Address!4!City!Element'

UNION ALL

SELECT

2 AS Tag,

1 AS Parent,

AgentID * 100,

NULL, AgentID, Fname, SSN,

NULL,NULL, NULL, NULL, NULL

FROM @Agent

UNION ALL

SELECT

3 AS Tag,

2 AS Parent,

AgentID * 100 + 1,

NULL,NULL,NULL, NULL,

NULL, NULL, NULL, NULL, NULL

FROM @Agent

UNION ALL

SELECT

4 AS Tag,

3 AS Parent,

AgentID * 100 + 2,

NULL,NULL,NULL,NULL,NULL,

AddressType, Address1, Address2, City

FROM @Address

) A

ORDER BY Sort

FOR XML EXPLICIT

Here is the complete listing of the code.

/*

Borrowed from Kent's code

*/

declare @agent table

(

AgentID int,

Fname varchar(5),

SSN varchar(11)

)

insert into @agent

select 1, 'Vimal', '123-23-4521' union all

select 2, 'Jacob', '321-52-4562' union all

select 3, 'Tom', '252-52-4563'

declare @address table

(

AddressID int,

AddressType varchar(12),

Address1 varchar(20),

Address2 varchar(20),

City varchar(25),

AgentID int

)

insert into @address

select 1, 'Home', 'abc', 'xyz road', 'RJ', 1 union all

select 2, 'Office', 'temp', 'ppp road', 'RJ', 1 union all

select 3, 'Home', 'xxx', 'aaa road', 'NY', 2 union all

select 4, 'Office', 'ccc', 'oli Com', 'CL', 2 union all

select 5, 'Temp', 'eee', 'olkiu road', 'CL', 2 union all

select 6, 'Home', 'ttt', 'loik road', 'NY', 3

/*

End Borrow

*/

SELECT Tag, Parent,

[Agents!1!],

[Agent!2!AgentID],

[Agent!2!Fname!Element],

[Agent!2!SSN!Element],

[AddressCollection!3!Element],

[Address!4!AddressType!Element],

[Address!4!Address1!Element],

[Address!4!Address2!Element],

[Address!4!City!Element]

FROM (

SELECT

1 AS Tag,

NULL AS Parent,

0 AS Sort,

NULL AS 'Agents!1!',

NULL AS 'Agent!2!AgentID',

NULL AS 'Agent!2!Fname!Element',

NULL AS 'Agent!2!SSN!Element',

NULL AS 'AddressCollection!3!Element',

NULL AS 'Address!4!AddressType!Element',

NULL AS 'Address!4!Address1!Element',

NULL AS 'Address!4!Address2!Element',

NULL AS 'Address!4!City!Element'

UNION ALL

SELECT

2 AS Tag,

1 AS Parent,

AgentID * 100,

NULL, AgentID, Fname, SSN,

NULL,NULL, NULL, NULL, NULL

FROM @Agent

UNION ALL

SELECT

3 AS Tag,

2 AS Parent,

AgentID * 100 + 1,

NULL,NULL,NULL, NULL,

NULL, NULL, NULL, NULL, NULL

FROM @Agent

UNION ALL

SELECT

4 AS Tag,

3 AS Parent,

AgentID * 100 + 2,

NULL,NULL,NULL,NULL,NULL,

AddressType, Address1, Address2, City

FROM @Address

) A

ORDER BY Sort

FOR XML EXPLICIT

/*

OUTPUT:

Vimal

123-23-4521

Home

abc

xyz road

RJ

Office

temp

ppp road

RJ

Jacob

321-52-4562

Home

xxx

aaa road

NY

Office

ccc

oli Com

CL

Temp

eee

olkiu road

CL

Tom

252-52-4563

Home

ttt

loik road

NY

MSSQL Table Size

2008-07-13T11:49:00.000-07:00

CREATE TABLE #SpaceUsed (name sysname,rows bigint,reserved sysname,data sysname,index_size sysname,unused sysname)

DECLARE @Counter int
DECLARE @Max int
DECLARE @Table sysname

SELECT name, IDENTITY(int,1,1) ROWID
INTO #TableCollection
FROM sysobjects
WHERE xtype = 'U'
ORDER BY lower(name)

SET @Counter = 1
SET @Max = (SELECT Max(ROWID) FROM #TableCollection)

WHILE (@Counter <= @Max)
BEGIN
SET @Table = (SELECT name FROM #TableCollection WHERE ROWID = @Counter)
INSERT INTO #SpaceUsed
EXECUTE sp_spaceused @Table
SET @Counter = @Counter + 1
END

SELECT * FROM #SpaceUsed

DROP TABLE #TableCollection
DROP TABLE #SpaceUsed

SQL SERVER 2005 WORKSHOP (DAY 3)

2008-06-18T01:24:00.000-07:00

TOOLS
- PSSDIAG OR SQLDIAG
capturing entire proses among the database server

- Installation Tips
change Computer properties and adjust processor usage for optimizing background
process, and adjust memmory usage for application
- Performance Monitor
set to capture SQL Server Performance, not less than 4/sec cuz in influence the
server performance

* Buffer Cache: shoulbe graeter than 90% cuz indicate number of page in cache
* Totap Pages: low number indicate frequent disk I/O or thrashing.
add more memory
* Total Server Memory: if high than physical memory and consistant,
add more memory
- Physical Disk Counters
* Avg Disk Queue Length: monitor number of read/write that queued,
shoul be no more than 2 times of spindels
* Disk/sec Read: <8ms
* Disk/sec Write: <8ms (non cached), <1ms (cached)
- CPU Counters
* %Processor Time: should be less than 90%
* Contex Switches/sec: monitoring the processor switch among threads
* Processor Queue Length: number of thread waiting for processor time. shoul be 0
* %Privilleged Time: berapa besar waktu yg dibutuhkan untuk melakukan process
kernel. kalo tinggi kemungkinan ada masalah di I/O
* %User Time: kalo tinggi berarti kemungkinan ada proses blocking yg lama.

Newer Tool
- SQL Nexus http://sqlnexus.net, used for analyzing data from SQLDiag
- Performance Analysis of Logs (PAL, http://codeplex.com/PAL)
- SQL Performance Dashboard (http://microsoft.com)
Integrated management report for toubleshooting performance
- SQL Server 2005 Best Practice Analyzer (PAL) from microsoft.com
Detect issues which do not comply with best practice
Detect missing patches, error in event logs and error logs

SQL Server TSQL Coding Conventions, Best Practices, and Programming Guidelines

2008-06-17T00:56:00.000-07:00

By : Vyas Kondreddi
May 10, 2001 Printer friendly

Databases are the heart and soul of many enterprise applications, and it is very essential to pay special attention to database programming. I've seen in many occasions where database programming is overlooked, thinking that it's something easy that be done by anyone. This is wrong.

For a better performing database you need a real DBA and a specialist database programmer, let it be for Microsoft SQL Server, Oracle, Sybase, DB2 or whatever! If you don't use database specialists during your development cycle, databases often end up becoming the performance bottleneck. I decided to write this article in order to put together some of the database programming best practices so that my fellow DBAs and database developers can benefit!

Here are some programming guidelines and best practices, keeping quality, performance and maintainability in mind. This list many not be complete at this moment, and will be constantly updated. BTW, special thanks to Tibor Karaszi (SQL Server MVP) and Linda (lindawie) for taking time to read this article and providing suggestions.

Decide upon a database naming convention, standardize it across your organization, and be consistent in following it. It helps make your code more readable and understandable. Click here to see the database object naming convention that I follow.

Make sure you normalize your data at least to the 3rd normal form. At the same time, do not compromise on query performance. A little bit of denormalization helps queries perform faster.

Write comments in your stored procedures, triggers and SQL batches generously, whenever something is not very obvious. This helps other programmers understand your code clearly. Don't worry about the length of the comments, as it won't impact the performance, unlike interpreted languages like ASP 2.0.

Do not use SELECT * in your queries. Always write the required column names after the SELECT statement, like:
SELECT CustomerID, CustomerFirstName, City

This technique results in reduced disk I/O and better performance.

Try to avoid server side cursors as much as possible. Always stick to a 'set-based approach' instead of a 'procedural approach' for accessing and manipulating data. Cursors can often be avoided by using SELECT statements instead.
If a cursor is unavoidable, use a WHILE loop instead. I have personally tested and concluded that a WHILE loop is always faster than a cursor. But for a WHILE loop to replace a cursor you need a column (primary key or unique key) to identify each row uniquely. I personally believe every table must have a primary or unique key. Click here to see some examples of using a WHILE loop.

Avoid the creation of temporary tables while processing data as much as possible, as creating a temporary table means more disk I/O. Consider using advanced SQL, views, SQL Server 2000 table variable, or derived tables, instead of temporary tables.

Try to avoid wildcard characters at the beginning of a word while searching using the LIKE keyword, as that results in an index scan, which defeats the purpose of an index. The following statement results in an index scan, while the second statement results in an index seek:

SELECT LocationID FROM Locations WHERE Specialities LIKE '%pples'
SELECT LocationID FROM Locations WHERE Specialities LIKE 'A%s'

Also avoid searching using not equals operators (<> and NOT) as they result in table and index scans.

Use 'Derived tables' wherever possible, as they perform better. Consider the following query to find the second highest salary from the Employees table:

SELECT MIN(Salary)
FROM Employees
WHERE EmpID IN
(
SELECT TOP 2 EmpID
FROM Employees
ORDER BY Salary Desc
)

The same query can be re-written using a derived table, as shown below, and it performs twice as fast as the above query:

SELECT MIN(Salary)
FROM
(
SELECT TOP 2 Salary
FROM Employees
ORDER BY Salary DESC
) AS A

This is just an example, and your results might differ in different scenarios depending on the database design, indexes, volume of data, etc. So, test all the possible ways a query could be written and go with the most efficient one.

While designing your database, design it keeping "performance" in mind. You can't really tune performance later, when your database is in production, as it involves rebuilding tables andindexes, re-writing queries, etc. Use the graphical execution plan in Query Analyzer or SHOWPLAN_TEXT or SHOWPLAN_ALL commands to analyze your queries. Make sure your queries do an "Index seek" instead of an "Index scan" or a "Table scan." A table scan or an index scan is a very bad thing and should be avoided where possible. Choose the right indexes on the right columns.

Prefix the table names with the owner's name, as this improves readability and avoids any unnecessary confusion. Microsoft SQL Server Books Online even states that qualifying table names with owner names helps in execution plan reuse, further boosting performance.

Use SET NOCOUNT ON at the beginning of your SQL batches, stored procedures and triggers in production environments, as this suppresses messages like '(1 row(s) affected)' after executing INSERT, UPDATE, DELETE and SELECT statements. This improves the performance of stored procedures by reducing network traffic.

Use the more readable ANSI-Standard Join clauses instead of the old style joins. With ANSI joins, the WHERE clause is used only for filtering data. Where as with older style joins, the WHERE clause handles both the join condition and filtering data. The first of the following two queries shows the old style join, while the second one shows the new ANSI join syntax:

SELECT a.au_id, t.title
FROM titles t, authors a, titleauthor ta
WHERE
a.au_id = ta.au_id AND
ta.title_id = t.title_id AND
t.title LIKE '%Computer%'

SELECT a.au_id, t.title
FROM authors a
INNER JOIN
titleauthor ta
ON
a.au_id = ta.au_id
INNER JOIN
titles t
ON
ta.title_id = t.title_id
WHERE t.title LIKE '%Computer%'

Do not prefix your stored procedure names with "sp_". The prefix sp_ is reserved for system stored procedure that ship with SQL Server. Whenever SQL Server encounters a procedure name starting with sp_, it first tries to locate the procedure in the master database, then it looks for any qualifiers (database, owner) provided, then it tries dbo as the owner. So you can really save time in locating the stored procedure by avoiding the "sp_" prefix.

Views are generally used to show specific data to specific users based on their interest. Views are also used to restrict access to the base tables by granting permission only on views. Yet another significant use of views is that they simplify your queries. Incorporate your frequently required, complicated joins and calculations into a view so that you don't have to repeat those joins/calculations in all your queries. Instead, just select from the view.

Use User Defined Datatypes if a particular column repeats in a lot of your tables, so that the datatype of that column is consistent across all your tables.

Do not let your front-end applications query/manipulate the data directly using SELECT or INSERT/UPDATE/DELETE statements. Instead, create stored procedures, and let your applications access these stored procedures. This keeps the data access clean and consistent across all the modules of your application, and at the same time centralizing the business logic within the database.

Try not to use TEXT or NTEXT datatypes for storing large textual data. The TEXT datatype has some inherent problems associated with it. For example, you cannot directly write or update text data using the INSERT or UPDATE statements. Instead, you have to use special statements like READTEXT, WRITETEXT and UPDATETEXT. There are also a lot of bugs associated with replicating tables containing text columns. So, if you don't have to store more than 8KB of text, use CHAR(8000) or VARCHAR(8000) datatypes instead.

If you have a choice, do not store binary or image files (Binary Large Objects or BLOBs) inside the database. Instead, store the path to the binary or image file in the database and use that as a pointer to the actual binary file stored elsewhere on a server. Retrieving and manipulating these large binary files is better performed outside the database, and after all, a database is not meant for storing files.

Use the CHAR data type for a column only when the column is non-nullable. If a CHAR column is nullable, it is treated as a fixed length column in SQL Server 7.0+. So, a CHAR(100), when NULL, will eat up 100 bytes, resulting in space wastage. So, use VARCHAR(100) in this situation. Of course, variable length columns do have a very little processing overhead over fixed length columns. Carefully choose between CHAR and VARCHAR depending up on the length of the data you are going to store.

Avoid dynamic SQL statements as much as possible. Dynamic SQL tends to be slower than static SQL, as SQL Server must generate an execution plan every time at runtime. IF and CASE statements come in handy to avoid dynamic SQL. Another major disadvantage of using dynamic SQL is that it requires users to have direct access permissions on all accessed objects, like tables and views. Generally, users are given access to the stored procedures which reference the tables, but not directly on the tables. In this case, dynamic SQL will not work. Consider the following scenario where a user named 'dSQLuser' is added to the pubs database and is granted access to a procedure named 'dSQLproc', but not on any other tables in the pubs database. The procedure dSQLproc executes a direct SELECT on titles table and that works. The second statement runs the same SELECT on titles table, using dynamic SQL and it fails with the following error:

Server: Msg 229, Level 14, State 5, Line 1
SELECT permission denied on object 'titles', database 'pubs', owner 'dbo'.

To reproduce the above problem, use the following commands:

sp_addlogin 'dSQLuser'
GO
sp_defaultdb 'dSQLuser', 'pubs'
USE pubs
GO
sp_adduser 'dSQLUser', 'dSQLUser'
GO
CREATE PROC dSQLProc
AS
BEGIN
SELECT * FROM titles WHERE title_id = 'BU1032' --This works
DECLARE @str CHAR(100)
SET @str = 'SELECT * FROM titles WHERE title_id = ''BU1032'''
EXEC (@str) --This fails
END
GO
GRANT EXEC ON dSQLProc TO dSQLuser
GO

Now login to the pubs database using the login dSQLuser and execute the procedure dSQLproc to see the problem.

Consider the following drawbacks before using the IDENTITY property for generating primary keys. IDENTITY is very much SQL Server specific, and you will have problems porting your database application to some other RDBMS. IDENTITY columns have other inherent problems. For example, IDENTITY columns can run out of numbers at some point, depending on the data type selected; numbers can't be reused automatically, after deleting rows; and replication and IDENTITY columns don't always get along well.
So, come up with an algorithm to generate a primary key in the front-end or from within the inserting stored procedure. There still could be issues with generating your own primary keys too, like concurrency while generating the key, or running out of values. So, consider both options and go with the one that suits you best.

Minimize the use of NULLs, as they often confuse the front-end applications, unless the applications are coded intelligently to eliminate NULLs or convert the NULLs into some other form. Any expression that deals with NULL results in a NULL output. ISNULL and COALESCE functions are helpful in dealing with NULL values. Here's an example that explains the problem:

Consider the following table, Customers which stores the names of the customers and the middle name can be NULL.

CREATE TABLE Customers
(
FirstName varchar(20),
MiddleName varchar(20),
LastName varchar(20)
)

Now insert a customer into the table whose name is Tony Blair, without a middle name:

INSERT INTO Customers
(FirstName, MiddleName, LastName)
VALUES ('Tony',NULL,'Blair')

The following SELECT statement returns NULL, instead of the customer name:

SELECT FirstName + ' ' + MiddleName + ' ' + LastName FROM Customers

To avoid this problem, use ISNULL as shown below:

SELECT FirstName + ' ' + ISNULL(MiddleName + ' ','') + LastName FROM Customers

Use Unicode datatypes, like NCHAR, NVARCHAR, or NTEXT, if your database is going to store not just plain English characters, but a variety of characters used all over the world. Use these datatypes only when they are absolutely needed as they use twice as much space as non-Unicode datatypes.
Always use a column list in your INSERT statements. This helps in avoiding problems when the table structure changes (like adding or dropping a column). Here's an example which shows the problem.

Consider the following table:

CREATE TABLE EuropeanCountries
(
CountryID int PRIMARY KEY,
CountryName varchar(25)
)

Here's an INSERT statement without a column list , that works perfectly:

INSERT INTO EuropeanCountries
VALUES (1, 'Ireland')

Now, let's add a new column to this table:

ALTER TABLE EuropeanCountries
ADD EuroSupport bit

Now run the above INSERT statement. You get the following error from SQL Server:

Server: Msg 213, Level 16, State 4, Line 1
Insert Error: Column name or number of supplied values does not match table definition.

This problem can be avoided by writing an INSERT statement with a column list as shown below:

INSERT INTO EuropeanCountries
(CountryID, CountryName)
VALUES (1, 'England')

Perform all your referential integrity checks and data validations using constraints (foreign key and check constraints) instead of triggers, as they are faster. Limit the use triggers only for auditing, custom tasks and validations that can not be performed using constraints. Constraints save you time as well, as you don't have to write code for these validations, allowing the RDBMS to do all the work for you.

Always access tables in the same order in all your stored procedures and triggers consistently. This helps in avoiding deadlocks. Other things to keep in mind to avoid deadlocks are: Keep your transactions as short as possible. Touch as few data as possible during a transaction. Never, ever wait for user input in the middle of a transaction. Do not use higher level locking hints or restrictive isolation levels unless they are absolutely needed. Make your front-end applications deadlock-intelligent, that is, these applications should be able to resubmit the transaction incase the previous transaction fails with error 1205. In your applications, process all the results returned by SQL Server immediately so that the locks on the processed rows are released, hence no blocking.

Offload tasks, like string manipulations, concatenations, row numbering, case conversions, type conversions etc., to the front-end applications if these operations are going to consume more CPU cycles on the database server. Also try to do basic validations in the front-end itself during data entry. This saves unnecessary network roundtrips.

If back-end portability is your concern, stay away from bit manipulations with T-SQL, as this is very much RDBMS specific. Further, using bitmaps to represent different states of a particular entity conflicts with normalization rules.

Always add a @Debug parameter to your stored procedures. This can be of BIT data type. When a 1 is passed for this parameter, print all the intermediate results, variable contents using SELECT or PRINT statements and when 0 is passed do not print anything. This helps in quick debugging stored procedures, as you don't have to add and remove these PRINT/SELECT statements before and after troubleshooting problems.

Do not call functions repeatedly within your stored procedures, triggers, functions and batches. For example, you might need the length of a string variable in many places of your procedure, but don't call the LEN function whenever it's needed, instead, call the LEN function once, and store the result in a variable, for later use.

Make sure your stored procedures always return a value indicating their status. Standardize on the return values of stored procedures for success and failures. The RETURN statement is meant for returning the execution status only, but not data. If you need to return data, use OUTPUT parameters.

If your stored procedure always returns a single row resultset, consider returning the resultset using OUTPUT parameters instead of a SELECT statement, as ADO handles output parameters faster than resultsets returned by SELECT statements.

Always check the global variable @@ERROR immediately after executing a data manipulation statement (like INSERT/UPDATE/DELETE), so that you can rollback the transaction in case of an error (@@ERROR will be greater than 0 in case of an error). This is important, because, by default, SQL Server will not rollback all the previous changes within a transaction if a particular statement fails. This behavior can be changed by executing SET XACT_ABORT ON. The @@ROWCOUNT variable also plays an important role in determining how many rows were affected by a previous data manipulation (also, retrieval) statement, and based on that you could choose to commit or rollback a particular transaction.

To make SQL Statements more readable, start each clause on a new line and indent when needed. Following is an example:

SELECT title_id, title
FROM titles
WHERE title LIKE '%Computer%' AND
title LIKE '%cook%'
Though we survived the Y2K, always store 4 digit years in dates (especially, when using cCHAR or INT datatype columns), instead of 2 digit years to avoid any confusion and problems. This is not a problem with DATETIME columns, as the century is stored even if you specify a 2 digit year. But it's always a good practice to specify 4 digit years even with DATETIME datatype columns.

As is true with any other programming language, do not use GOTO, or use it sparingly. Excessive usage of GOTO can lead to hard-to-read-and-understand code.

Do not forget to enforce unique constraints on your alternate keys.

Always be consistent with the usage of case in your code. On a case insensitive server, your code might work fine, but it will fail on a case sensitive SQL Server if your code is not consistent in case. For example, if you create a table in SQL Server or a database that has a case-sensitive or binary sort order, all references to the table must use the same case that was specified in the CREATE TABLE statement. If you name the table as 'MyTable' in the CREATE TABLE statement and use 'mytable' in the SELECT statement, you get an 'object not found' error.

Though T-SQL has no concept of constants (like the ones in the C language), variables can serve the same purpose. Using variables instead of constant values within your queries improves readability and maintainability of your code. Consider the following example:

SELECT OrderID, OrderDate
FROM Orders
WHERE OrderStatus IN (5,6)

The same query can be re-written in a mode readable form as shown below:

DECLARE @ORDER_DELIVERED, @ORDER_PENDING
SELECT @ORDER_DELIVERED = 5, @ORDER_PENDING = 6

SELECT OrderID, OrderDate
FROM Orders
WHERE OrderStatus IN (@ORDER_DELIVERED, @ORDER_PENDING)

Do not use column numbers in the ORDER BY clause. Consider the following example in which the second query is more readable than the first one:

SELECT OrderID, OrderDate
FROM Orders
ORDER BY 2

SELECT OrderID, OrderDate
FROM Orders
ORDER BY OrderDate

Well, this is all for now folks. I'll keep updating this page as and when I have something new to add. I welcome your feedback on this, so feel free to email me. Happy database programming!

SQL SERVER 2005 WORKSHOP (DAY 2)

2008-06-16T19:40:00.000-07:00

SQL PROFILER
- Deadlock handle. kondisi dimana terjadi saling tunggu antar dua proses.

How data stored in MSSQL SERVER 2005
- Data tersimpan dalam Page = 8KB.
- Extend = 8 Page, dedicated (one data), dynamic (many data) = 64KB
- HIP = unindexed table, index id = 0
- Const Varchar, if updated, kekurangan size, maka akan dipindah ke page yg kosong.

INDEXED
- Clustered Index, index dimana level terbawah dari leaf itu adalah data itu sendiri
Sifat:
- data stored berurutan.
- sebaiknya untuk kolom yg tidak sering diupdate
- hanya boleh ada satu clustered index dalam satu table
- Non-Clustered Index
def: - sama sama menggunakan B-TREE
- sebaiknya gunakan untuk kolom yg sering digunakan untuk join
- kolom dengan data yg besar, dan sebisa mungkin jarang update
- Unique Index, contstraint cant be duplicated data
- Index include column, artinya index menyertakan sebuah kolom lain,
misal NIK dan nama customer

- Full-Text, managed index by service MSFTESQL, can be populate,
used to search based on phrase
- XML Index, indexing XML data

REINDEXING TASK use: DBCC REINDEX (SQL2000) ALTER INDEX (SQL2005)
- command Alter Index Reorganize (shorter but noy optimal)
- command Alter Index Rebuilt (take longer but better)

RECOVERY MODEL
- Simple: setiap transaksi, transaction log langsung di truncate setelah commit.
Tidak bisa melakukan Recovery model Log.
pros:Minimize Log file size.
const: bila disaster, hanya bisa balikin backup dari backup terakhir.
usage: for data tidak penting
- Full: setiap transaksi dicatat di transaksi log, tanpa truncate transaction log.
transaction log baru di truncate sampai dilakukan full backup
harus direstore dalam waktu berurutan
- Bulk Log: transaksi biasa di log, bulk insert tidak di log. Menjembatani antara
Full dan Simple Recovery

backup data vs backup transaction log means:
backup data is backuping data (physic),
backup transaction log mean backup command (DML) smaller size,
harus ada Fullbackup.

Recover master Database:
- Use CD Installer, run Setup.exe choose option rebuild master database RebuildM.exe
- Master, MSDb, TempDB, Model, MssqlResources are system database: If only master database exist, restart service with -M Statement

B-TREE = pemisahan data kedalam tree, misal besar di tree kanan, kecil di tree kiri

Database SNAPSHOT
adalah replica dari sebuah database yg bersifat readonly. tujuannya untuk menghindari akases langsung ke database aslinya.
Pada saat Database Mirrorring, Destination Database biasanya readonly, nah agar tetap dapat digunakan bisa dibuatkan sebuah Snapshot

SQL SERVER 2005 WORKSHOP (DAY 1)

2008-06-16T02:02:00.000-07:00

System database
Master, Model, MsDB

SQLNEXUS, MBSA (Microsoft Based Security Analyzer)
Local Service user must be a normal user type, not as local system (administrator)
RebuiltAmp --> corrupt Master database lalu recovery system database - 2000
Install - Rebuilt System database, restore System database pada sql 2005

Maintenance Plan:
DB Backup (FULL, PARTIAL, DEFFERENTIAL)
Integrity Backup (execute DBCC checkdb)
Sebelum backup: integrity check, index defrag, update statistic

Common Problem and Issues
Raid 0 = striping (fast), cons fault tolerance
Rain 1 = pro fault tolerance, best practice for OS, or transaction log (ldf)
1 + 0 = best practice for Data Storage
Raid 5 = Parity, cons write time longer cuz write to much disk

Best Practice Building Database Server:
OS, LOG, DATA dipisah. Log write sequences, Data random read or write

CONFIG:
/PAE (Page Address Extension) expand memory until 16 GB (agar OS 32bit bisa extend memory)
/3GP /USERVA3030 (alokasi memory kernel 1 GB, EPP 3 GB)
/AWE (Address Windows Extension) untuk menseting OS agar SQL server bisa pake Memory > 4GB

SQL SERVER CONFIGURATION
ALIAS : untuk membuat virtual name dari sebuah server

Save A WEB Page to MS WORD APP

2008-05-13T23:27:00.000-07:00

ACTION="CONNECT"
CLASS="Word.Application"
NAME="objWord"
TYPE="COM">

ACTION="CREATE"
CLASS="Word.Application"
NAME="objWord"
TYPE="COM">

/* This will open Word if running locally */
objWord.Visible = true;
/* This returns the 'Documents' collection the Word Object */
objDoc = objWord.Documents;
/* Create a new document */
newDoc = objDoc.Add();
/* Save the document to a location */
newDoc.SaveAs("D:\template_new.doc");
/* We specify the range of '0' -- start at the beginning of the document */
docRange = newDoc.Range(0);
/* Add text to the range */
docRange.Text = "Hello World!";
/* Save the changes */
newDoc.Save();
/* Close the document */
newDoc.Close();
/* Quit Word */
objWord.Quit();

Check Disk Space

2008-03-03T22:29:00.000-08:00

By Bjorn Jensen
	http://www.developer.be/index.cfm/fuseaction/tutorialDetail/GroupID/63/tutorialName/Checking_Diskspace.htm

What is the ID for the record I just inserted?

2008-02-15T12:50:00.001-08:00

by: Pablo Varando

What is my ID for the record I just inserted?

A question I get quite often from visitors and members of EasyCFM.COM is "if I insert a record, is there a way I can get the ID of the record I just inserted, without having to connect to the database again?"

The answer of course is yes ;) first let me explain how your database structure would look for this example (keep in mind that this is a small example and your table will be different, all you need for this example to work properly is an IDENTITY FIELD in your SQL server. If you need help setting up an identity field, please post a request to the forums [I will be writing a tutorial on this at a later time and will update this page with the link at a latter time... for now please use the forums ;] ...) Now, lets look at the database itself,

cfquery name="qGetUser" datasource="MyDSN"
INSERT INTO users (firstName, lastName, email )
VALUES ( '#FORM.LastName#', '#FORM.LastName#', '#FORM.email#' );
cfquery

select thisID = @@identity

The ID of the item just inserted is: #qGetUser.thisID#

That more or less does it :) Let me explain what it is you saw and what it does....

First you do an insert (as you would any other insert) with the difference that you add a ";" at the end of it. (Notice it in bold). The next thing you did was SELECT thisID = @@identity.... this works similar to a regular SELECT statement... but it basically just returns the ID of the item just inserted to the database!

2008-02-15T07:49:00.000-08:00

Server Monitor API In Coldfusion 8

By Raymond Camden
Expert Author
Article Date: 2007-06-25

In an earlier post I talked a bit about how the Admin API has been updated in ColdFusion 8. For folks who don't know what this is - it is a way to get into the internals of ColdFusion directly from CFML.

It requires the ColdFusion Admin password, which may be a negative for some, but if you have access to it, you can do quite a bit. As an example, I could build an installer for BlogCFC that would prompt the user for the CF Admin password and then handle creating the datasource for you.

So it's cool. We know that. But how cool is it in ColdFusion 8?

Well you know that server monitor that ships with the product? If you haven't seen it, you need to spend some time with it. I spent 5 minutes running it with BlogCFC and saw immediate, serious performance issues I was able to correct. 5 minutes to improve my application in a dramatic way is worth the cost of ColdFusion 8 right there. But did you know that 100% of what you see in the Flex application is available to you via code?

Added to the Admin API is a new CFC, the Server Monitoring CFC. This CFC gives you access to everything you see in the Flex app, and maybe a bit more. Now it is important to note that each method has different requirements for server monitoring. Some require that memory tracking is turned on. Some require nothing. Some return data but will return more data if memory tracking is on. You should read the docs carefully to see what each method requires.

What follows is a short list of some of what is available...

Get Active Session Count

This lets you get the total number of active sessions for the server - or you can supply a ColdFusion Application name and get the number just for that application.

Get Average Response Time

This is a great metric. It tells you - on average - how quickly your templates are responding.

Get Heartbeat

This returns a structure of general information. This includes a variety of items like server up time, average request time, current requests, etc.

Get Memory Utilization Summary

As you can guess - this returns information about your memory usage. It tells you how much data your Application, Server, and Session scope data is using.

Get Hit Count Stats

Now this one is truly cool. Along with telling you how many normal template hits you have - it can tell you the number of web service hits - the number of Flash Remoting hits - even the number of direct HTTP hits to your CFCs.

Get Logged In User Count

Returns the number of users logged in via CFLOGINUSER.

Get All Application Scope Memory Used

This returns all your applications and the amount of RAM they are using. Have a machine that is running a bit slow? Run this and it will tell you if one application in particular is being a bad boy. You can follow it up with getApplicationScopeMemoryUsed which returns specific data about one particular application.

Cool stuff, eh? It is important to note that using these features will have an impact on your server performance. I had a demo online, but I could really tell the impact of the monitoring so I turned it off. But if you want to see the code I used (with a different Admin password of course, I trust some of you guys... ;), look here:

<cfinvoke component="cfide.adminapi.administrator" method="login" adminPassword="mypasswordcanbeatupyourpassword">

<cfinvoke component="cfide.adminapi.servermonitoring" method="getActiveSessionCount" returnVariable="result">


Total session count =

<cfdump var="#result#">

<

cfinvoke component="cfide.adminapi.servermonitoring" method="getActiveSessionCount" returnVariable="result" cfapplicationname="lighthousepro"

>


For app lighthousepro =

<cfdump var="#result#">

<cfinvoke component="cfide.adminapi.servermonitoring" method="getAverageResponseTime" returnVariable="result">

<cfoutput>Average response time is #result#</cfoutput>

<cfinvoke component="cfide.adminapi.servermonitoring" method="getHeartbeat" returnVariable="result">
<cfdump var="#result#" label="Heart Beat">

<

pv

<

cfinvoke component="cfide.adminapi.servermonitoring" method="getMemoryUtilizationSummary" returnVariable="result"v

<cfdump var="#result#" label="Memory Utilization">

<cfinvoke component="cfide.adminapi.servermonitoring" method="getHitCountStats" returnVariable="result">
<cfdump var="#result#" label="Hit Count Stats">

<cfinvoke component="cfide.adminapi.servermonitoring" method="getLoggedInUserCount" returnVariable="result">


Logged in users =

<cfdump var="#result#">

Tahapan Dalam Membangun "World Class Website"

2008-02-05T05:24:00.000-08:00

Let’s Get Started … but How?
- Planning to Stay on Schedule and Within Budget
  - Effective Pre-planning
  - Doing your Homework and Making Educated Decisions
  - Developing a Web Site Style Guide
  - Managing Like Magic: Effective Organization
  - Working Together as a Team
- Summary
What to Find Out: Initial Questions to Answer
- Establishing your Target Audience
  - Who will Visit and Why?
  - How will they Visit?
- Identifying your Initial Goals
- Identifying your Budget
  - Domain Name Costs
  - Web Hosting
  - Branding and Marketing
  - Bringing in the Experts
  - How Much is a Picture Worth?
  - Ecommerce
  - Computer Software and Hardware
- Summary
Preparing Web Site Content
- Gathering and Itemizing Content
- Providing Accessible Content
- Preparing Web Copy
- Preparing Images
- Summary
Managing all the Content
- Assessing your Web Site Content
- Determining your Content Management Needs
  - Web Authoring Tool Features
  - Content Management System Features
- Summary
Web Site Usability: Focusing on the User
- Creating a User-friendly Index Page
- Ensuring your Web Site Focuses on Users
  - Providing Fast-loading Web Pages
  - Accommodating User Abilities, Disabilities, Browsers, and Alternative Devices
  - Ensuring Predictability
  - Providing User-friendly Corrections for User Errors
- Summary
Color
- Preparing a Color-friendly Work Environment
- Choosing a Color Scheme
- Using Color to Enhance Functionality
  - Using Color to Enhance Readability
- Summary
Information Architecture
- Laying the Foundations
- Improving Findability
- Organizing for Success
  - Organization Schemes
  - Organizing Content Structure
- Preparing Web Page Information Architecture
  - Information Architecture for Ecommerce Pages
- Organizing Directories
- Using Friendly URIs, URLs, and Filenames
  - Hiding Filename Extensions as Much as Possible
- Summary
Navigation
- Golden Rules for Effective Web Site Navigation
- Creating User-centered Global Navigation
- Creating Local or Section Navigation
- Providing Internal Page Navigation
- Adding Supplemental Navigation
  - Creating Sitemaps
  - Creating Site Indexes
  - Creating Search
- Summary
Best Coding Practice: W3C Standards and Recommendations
- Magic Markup
  - Separating Content from Presentation
  - Ensuring the Integrity of your Markup
- Excellent XHTML
- Spectacular CSS
  - Using Appropriate CSS Naming Conventions
  - Beware of Browser Bugs and Problems
  - Using CSS for Print
- Summary
Creating Accessible Web Sites
- The Bare Bones of Creating an Accessible Web Site
  - Creating Valid, Structured, Semantic Markup
  - Providing Accessible Navigation
- Color for the Masses
- A Thousand Words to Each Picture
  - Accessible Image Maps
- Multi-purpose Multimedia
- In Good Form
- Sturdy Tables
- Summary
Web Site Optimization
- Creating Clean, Lean Markup
  - Minimizing URLs
  - Optimizing CSS
  - Optimizing JavaScript
- Supporting Speedy Server Responses
- Optimizing Images, Multimedia, and Alternative Formats
- Summary
Search Engine Optimization
- Successful SEO in a Nutshell
- Working with Keywords and Keyphrases
  - Using Keywords and Keyphrases in your Markup
- Being Search Engine-Friendly
- Avoiding Being Banned by Search Engines
- Getting Listed
- Creating an Ongoing Links Campaign
- Planning for Ongoing Maintenance
- Summary
Design
- Basic Design Principles
- Effective Typography
- Brainstorming
- Finalizing the Design
- Summary
Testing
- Getting Started
- Good Testing Practice
- General Testing
  - Solid Security
- Accessibility Testing
  - Conducting a Preliminary Review
  - Conducting a Comprehensive Review
  - Testing with a Graphical Browser
  - Testing with a Voice or Text-only Browser
- Usability Testing
- Summary
Preparing for Launch
- Conducting Final Checks
  - Checking Templates and Markup
  - Checking Layouts
  - Finalizing Content
  - Checking Functionality
  - Checking Server-side Requirements
- Conducting a Soft Launch
- Launching the Web Site
- Completing your Web Site Handover
  - Checking and Finalizing Project Documentation
  - Creating a Handover Package
  - Archiving Project Documentation and Files
- Summary
Post-launch Follow-up
- Conducting a Post-launch Review
- Completing Initial Post-launch Tasks
- Orientating New Staff
- Getting Attention
- Managing Maintenance
  - Undertaking Daily Duties
  - Conducting Monthly Minding
  - Performing Periodic Processes
  - Conducting Annual Activities
- Collecting Data
  - Collecting and Using Quantitative Data
  - Collecting and Using Qualitative Data
- Summary
A. Ecommerce Checklists
- Assessing Ecommerce Content Usage and Management Needs
- Features to Seek in a Shopping Cart Program

Naga Bonar Jadi Dua

2008-02-02T05:59:00.001-08:00

Directed by: Deddy Mizwar

Pesannya dalem banget. Di masa sekarang jarang ada film yg mampu membawakan sebuah pesan hingga bener-bener sampai ke hati penontonnya. Bagaimana tidak, pemilihan tokohnya benar-benar pas, Tora Sudiro merupakan Icon generasi muda, jadi pesan yg disampaikan melaluinya akanlah mudah diterima oleh generasi muda, karena memang banyak orang akan meniru apa yg dilakukan tokoh idolanya. Seorang Deddy Mizwar yg berperan sebagai ayah dari seorang anak yg berbudaya modern, mampu menjadi teladan bagi anaknya meskipun hidup berbeda jaman. Keteladanan sang ayah yg amat mencintai bangsa dan negaranya terlihat dari betapa hormatnya dia kepada para pahlawan bangsa ini dimasa lalu.
Singkatnya pesannya adalah cintailah negerimu ini sebelum kau mati dimakan cacing. Paling tidak dengan cara gampang yaitu janganlah membuang sampah sembarangan, buanglah pada tempat yg semestinya atau tempat sampah. Kalau cara kecil ini saja tidak bisa dilakukan, maka akan semakin rusaklah lingkungan negara kita. lalu... APA KATA DUNIA!!!

Simplify Life

2008-02-02T05:58:00.000-08:00

Pernah dengar bahwa pikiran manusia itu (otak) adalah ciptaan Tuhan yg amat mengagumkan. Dialah yg membawa kita pada perubahan seperti sekarang ini, merekam kejadian di masa lalu, mengira-ngira kemungkinan di masa yg akan datang, serta membantu dalam mengambil sebuah keputusan. Sungguh sebuah mahakarya yg tiada tandingannya.

Terkadang pikiran juga dapat dikendalikan, ditentang, dan rekayasa sedemikian rupa sehingga mengikuti kemauan hati kita.

Hidup juga dikendalikan oleh pikiran kita. Jika pikiran sudah dikendalingan oleh keinginan yg muluk-muluk dan busuk, maka kita akan kehilangan cara pikir rasional, yg artinya bertindak diluar perhitungan. Kalau sudah bertindak diluar rasio yang terjadi adalah kesalahan-kesalahan dalam bertindak yg menimbulkan penyesalan.

itulah sampah-sampah yg harus disingkirkan dari dalam diri kita. Membuat segalanya menjadi simple dengan berpikir sederhana, bertindak dengan penuh perhitungan, niscaya akan menghindarkan kita dari segala kesalahan dan penyesalan yg tidak perlu.

so keep it simple stupid
Do What You Wanna Do, Be Whatever You Wanna Be
Jangan lupa berbuat baik untuk orang lain

Fantastic Four - Rise of the Silver Surfer

2008-02-02T05:57:00.003-08:00

Dalam film ini dikisahkan kemunculan sesosok mahluk aneh yg mengendarai sebuah papan selancar berwarna perak, dengan itu dia dijuluki si Silver Surfer.
Kedatangannya ke Bumi dalam rangka menjaga eksistensi dari tuannya (kekasihnya) dengan cara datang ke planet-planet untuk dihisap energina hingga planet-planet tersebut menjadi rusak bahkan hancur.
Ketika si Human Torch berkelahi sendirian dengan si Silver Surfer, dia mendapatkan sebuah pengaruh yg akan menyebabkan perpindahan (bertukar) energi dengan apa yg disentuhnya.
Nah terdapat suatu, yg nenek gw bilang, blooper dari film ini. Misal jika si Human Torch menyentuh The Thing, maka si Human Torch badannya menjadi berbatu seperti The Thing, demikian pula sebaliknya. Namun apa yg terjadi ketika si Human Torch bersentuhan dengan ketiga rekannya yg lain, harusnya si Human Torch mendapat kekuatan The Thing, Mr. Fantastic, dan Invisible Woman. Yang terjadi justru sedikit aneh dan tidak konsisten dengan kondisi yg terjadi akibat pertempuran Human Torch dan Silver Surfer. Seharusnya Human Torch mendapatkan kehebatan dari 3 rekannya yg lain, lalu kehebatannya berpindah berpindah ke tiga rekannya yg lain. Ini hukum pertukaran (switch/exchange) bukan kolaborasi..
But overall this movie entertine enough, menambah imanjinasi kita berdasar gagasan si pembuat film..

Gajah Mada, Hamukti Palapa (3)

2008-02-02T05:55:00.000-08:00

Written by: Langit Kresna Hariadi

Pada dasarnya hanya sebuah novel dengan cerita karangan dari imanjinasi sang penulis.
Apa yg dituturkan akan sebuah kejadian bersejarah dimasa lalu, sungguh mengasikkan karena pada dasarnya saya menyenangi cerita kepahlawanan, keteladanan dari seorang tokoh serta sepak terjangnya dalam mewujudkan apa yg menjadi prinsip hidupnya. Gajah Mada, yg sampai pada seri ke tiga dari 5 buku yg ada, masih merupakan cerita menarik. Sifat-sifat yg seperti inilah yg kiranya harus dimiliki semua orang dalam memandang negara sendiri. Seperti sebuah pesan yg patut dipahatkan di hati setiap warga negara: Jangan tanya apa yg negara berikan padamu, akan tetapi bertanyalah apa yg telah kamu perbuat untuk negaramu.
Sangat jelas dan tajam makna yg terkandung didalamnya. Gajam Mada adalah seorang yg mengabdikan seluruh hidupnya demi negaranya. Ia rela meninggalkan apa yg seharusnya ia bisa nikmati untuk mewujudkan kebesaran dan kesatuan negara, berjuang tanpa henti, belajar tanpa henti, dan juga tidak lupa selalu bercermin dari masa lalu. Masa lalu adalah sejarah, tetapi hendaknya tidak menyebabkan kesalahan di masa lalu terulang kembali di saat sekarang. Untuk itulah Gajah Mada amat selalu hati-hati dalam bertindak. Hasilnya, tepat! Sungguh orang yg luar biasa. Tak perlu diragukan lagi, sosok Gajah Mada sangat dibutuhkan negara sekarang ini, paling tidak untuk mengingatkan kita semua bahwa wilayah Nusantara ini tercipta melalui perjuangan yg tanpa mengenal lelah di masa lalu. Nusantara ini diciptakan untuk bersatu menghadapi ancaman dari negara lain yg lebih besar pada saat itu. Kita sudah punya alat pemersatu bangsa layaknya pada jaman Majapahit dulu yg menggunakan bendera gula kelapa, kita punya Sang Saka Merah Putih, Burung Garuda Pancasila, dan Undang-undang Dasar 1945. Hayati semua makna dibalik lambang itu maka di hatimu akan tumbuh rasa kagum, haru dan bangga akan Bangsa Indonesia.

Review Rambo IV

2008-01-30T18:01:00.000-08:00

Tokoh John Rambo dalam sequel film Rambo 1 - 3 pernah dijadikan simbol dari perjuangan prajurit/ tentara America oleh presidennya pada tahun 80-an. Memang benar, John Rambo adalah prajurit sejati dengan kemampuan komplit berkat pengalamannya terjun di berbagai medan perang dan menghadapi berbagai macam pengalaman sulit didalamnya.
Berbekal pengalaman tokoh ini, kembali muncul sequel film Rambo IV yg tetap dibintangi oleh Silvester Stallone, sang aktor dengan perawakan kekar dengan muka yg dibuat seram. Setting film ini adalah daerah perbatasan negara Burma yg penuh dengan kekejian perang antara militer, pemberontak pro pembebasan rakyat, dan rakyat di daerah perbatasan itu sendiri yg menjadi korban kebiadaban tentara. Demi menekan kaum pemberontak yg berjuang secara gerilya dan sembunyi-sembunyi, tentara mengganggu rakyat, menganiaya mereka dengan kejam, agar kaum pemberontak menjadi takut dan tunduk pada tentara di kawasan itu.
John Rambo datang sebagai penyelamat bersama sekelompok tentara bayaran. Hatinya terpanggil melihat sesuatu yg tidak benar, apa lagi ada warga AS yg menjadi korban didalamnya.
Dia adalah seorang tentara yg sudah kenyang dengan peperangan. Didalam dirinya terdapat insting membunuh yg kini muncul kembali, bukan karena membunuh demi negara, tetapi membunuh untuk sesuatu yg benar yg diyakininya.
Jadilah tonontonan ini menegangkan dengan pertempuran yg sengit antara tentara musuh dengan John Rambo dan teman-teman tentara bayaran tadi. Parahnya penggambaran di film ini, penumpasan terhadap tentara musuh tak kalah sadisnya sama dengan perlakuan mereka terhadap pembantaian warga tak berdosa.
Overall film ini menunjukkan kepada kita sebuah kisah heroik apik, diselipi pesan moral bahwa mati demi memperjuangkan sesuatu yg diyakini benar dan bermafaat bagi orang lain, lebih baik daripada hidup tapi sia-sia. Seandainya saja film ini nantinya akan diputar di televisi di tanah air, bisa jadi durasinya tinggal 30 menit, karena semua adegan perang penuh kesadisan dipotong lembaga sensor film..hahaha..