Java -Xmx

It's hard to believe that I can't find this out from google. Put it here for quick reference.

-Xmxn
Specify the maximum size, in bytes, of the memory allocation pool. This value must a multiple of 1024 greater than 2MB. Append the letter k or K to indicate kilobytes, or m or M to indicate megabytes. The default value is 64MB. The upper limit for this value will be approximately 4000m on Solaris 7 and Solaris 8 SPARC platforms and 2000m on Solaris 2.6 and x86 platforms, minus overhead amounts. Examples:

-Xmx83886080
-Xmx81920k
-Xmx80m

Sample of XML date type

as long as the xml can be converted into table, thing becomes familiar and simpler

--1.orange values like xxxxxx into table
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE xmlTest
@msg xml
AS
BEGIN
declare @tmpTable table(col1 int,col2 varchar(20))
insert into @tmpTable(col1,col2)
select tbl.col.value('./abc[1]','int'),tbl.col.value('./d[1]','varchar(20)')
from @msg.nodes('/test') as tbl(col)
select * from @tmpTable
END
GO

exec xmlTest '12345testing88888test888'

--2.orange values like into table
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE xmlTest
@msg xml
AS
BEGIN
declare @tmpTable table(col1 int,col2 varchar(20))
insert into @tmpTable(col1,col2)
select tbl.col.value('@abc','int'),tbl.col.value('@d','varchar(20)')
from @msg.nodes('/test') as tbl(col)
select * from @tmpTable
END
GO

exec xmlTest exec xmlTest ''

--accept string instead
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE xmlTest
@msgStr varchar(MAX)
AS
BEGIN
declare @msg xml
set @msg=@msgStr
CREATE PROCEDURE xmlTest
@msgStr varchar(MAX)
AS
BEGIN
declare @msg xml
set @msg=@msgStr
declare @tmpTable table(col1 int,col2 varchar(20))
insert into @tmpTable(col1,col2)
select tbl.col.value('@abc','int'),tbl.col.value('@d','varchar(20)')
from @msg.nodes('/test') as tbl(col)
select * from @tmpTable
END
GO

Drop columns with default constraint in SQL Server 2000

here's a sample I used to drop all the columns with default constraint in a table. it can be further changed to work better.

declare dfName cursor for
SELECT o2.name,c.name
FROM syscolumns c
JOIN sysobjects o ON c.id = o.id
JOIN sysobjects o2 ON c.cdefault = o2.id
WHERE o.name = 'table name'

open dfName
declare @dn varchar(100),@cn varchar(50)
declare @tSQL varchar(300)
fetch next from dfName into @dn,@cn
print @@fetch_status
print @dn+':'+@cn
while (@@fetch_status<>-1)
begin
set @tSQL='alter table tablename drop constraint '+@dn+' alter table tablename drop column '+@cn
exec(@tSQL)
fetch next from dfName into @dn,@cn
print @@fetch_status
print @dn+':'+@cn
end
close dfName
deallocate dfName

SQL Server maintenance

sp_who
kill

data warehouse - fact and dimenssion

From WIKI.

The centralized table in a star schema is called a fact table. A fact table typically has two types of columns: those that contain facts and those that are foreign keys to dimension tables. The primary key of a fact table is usually a composite key that is made up of all of its foreign keys. Fact tables contain the content of the data warehouse and store different types of measures like additive, non additive, and semi additive measures.

In a data warehouse, a dimension is a data element that categorizes each item in a data set into non-overlapping regions. A data warehouse dimension provides the means to "slice and dice" data in a data warehouse. Dimensions provide structured labeling information to otherwise unordered numeric measures. For example, "Customer", "Date", and "Product" are all dimensions that could be applied meaningfully to a sales receipt. A dimensional data element is similar to a categorical variable in statistics.

The primary function of dimensions is threefold: to provide filtering, grouping and labeling. For example, in a data warehouse where each person is categorized as having a gender of male, female or unknown, a user of the data warehouse would then be able to filter or categorize each presentation or report by either filtering based on the gender dimension or displaying results broken out by the gender.

Each dimension in a data warehouse may have one or more hierarchies applied to it. For the "Date" dimension, there are several possible hierarchies: "Day > Month > Year", "Day > Week > Year", "Day > Month > Quarter > Year", etc

T-SQL to learn

xml data type,
xml variable.nodes
xml element.value
select from xmlv.nodes

set showplan_xml on

for xml path(), type
severity level
alter table switch
create index where
outer apply
permission set on assembly

with xmltable
generate xml
openxml
raiseerror with no wait
select fast 50
dm_db_missing_index_details
msdb.sysmail_faileditems

Passing parameter to sql script via isql

An example:
ECHO OFF
isql /S dbserver /U user /P password /d db /o output.txt /s , /w8000 /Q "EXIT(exec sp1 '%1','%2')"

T-SQL Group Sets

This is generic way to provide union of the aggregates based on the columns specified in each set in the Grouping set. you can write corresponding statements to replace the specific CUBE and ROLLUP funtion.

You can use Group Sets to understand how CUBE and ROLLUP work.

CUBE Equivalent:

SELECT c1, c2,avg(c3)
from tbl
Group BY
CUBE (c1, c2)

Equals to:
SELECT c1, c2, avg(c3)
from tbl
GROUPING SETS
(
(c1, c2),
(c1),
(c2) ,
()
)

ROLLUP Equivalent:
SELECT c1, c2, avg(c3)
from tbl
Group BY
ROLLUP (c1, c2)

Equals to:
SELECT c1,c2, avg(c3)
from tbl
Group BY
Grouping Sets
(
(C1, C2),
(c1),
()
)

Comparison on Text column

In T-SQL, "Comparison operators test whether two expressions are the same. Comparison operators can be used on all expressions except expressions of the text, ntext, or image data types."

Using "Like" on the text column instead.