|
Monday, 19 December 2011 01:02 |
In ADO.NET 2.0, the DataView Object has a new method called ToTable, which allows you to create a new table based on data in the DataView.
Here is an example that we used above that creates a new DataTable listing Brazilian Contact Names only:
//Filtered records
DataView dv = new DataView(GetTable(),"Region = 'SP' and Country = 'Brazil'", "ContactName", DataViewRowState.CurrentRows);
//Create new table based on filtered records
DataTable newTable = dv.ToTable("BrazilianContactNames", true, new string[] { "ContactName" });
//Bind grid with only filtered records
dataGridView1.DataSource = newTable.DefaultView;
 Read more: |
|
Thursday, 15 December 2011 00:31 |
DBCC CHECKIDENT can reset the identity value of the table.
For example, Your Table has 25 rows with 25 as last identity. If we want next record to have identity as 35, we need to run the following TSQL script in Query Analyzer.
DBCC CHECKIDENT (yourtable, reseed, 34)
If table has to start with an identity of 1 with the next insert, then table should be reseeded with the identity to 0.
If identity seed is set below values that currently are in table, it will violate the uniqueness constraint as soon as the values start to duplicate and will generate error.
 Read more: |
|
|
Saturday, 22 October 2011 01:48 |
// create to_days(first_day), to_days(second_day) syntax that can be directly used while creating partitions based on month.
Create a table in the test database with the name "date_helper"
mysql> CREATE TABLE test.date_helper (
id INT NOT NULL,
to_timestamp datetime
);
Run the shell script and feed the output to mysql
unix> sh mydate.sh | mysql -uUser -pPassWd test
_____
The altered table will look something like this:
CREATE TABLE `date_helper` (
`id` int(11) NOT NULL,
`to_timestamp` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY LIST (to_days(to_timestamp))
(PARTITION January2010 VALUES IN (734138,734139,734140, ...) ENGINE = InnoDB,
PARTITION February2010 VALUES IN (734169,734170,734171,... ) ENGINE = InnoDB,
...
...
PARTITION November2011 VALUES IN (734807,734808,734809,...) ENGINE = InnoDB,
PARTITION December2011 VALUES IN (734837,734838,734839,...) ENGINE = InnoDB) */
_____
You can change the number of years as well as the starting month while running the script. If you want 3 years (365*3) partition starting from Jan 1990, use the following syntax:
sh mydate.sh 1990-01-01 1096
#!/bin/sh
# change the table name and partition column name below
tblname='date_helper'
colname='to_timestamp'
# if no start date is specified, then set the default date to 1 Jan 2010
startdate=${1:-'2010-01-01'}
# 24 partitions for 2 years viz 2010 and 2011 will be created by default
totaldate=${2:-'730'}
# change the days above from 730 for 2 years to 365 for 1 year
# run the script and save the output to a file > torun.sql
# open the file torun.sql and remove the last comma , before executing the query
# mysql test < torun.sql
# CREATE TABLE employees (id INT NOT NULL, store_date date)
# sample table
mysql -e"create table if not exists test.date_helper (id int, to_timestamp datetime);"
mysql -e"drop table if exists test.mycalendar;"
mysql -e"create table test.mycalendar (id int not null auto_increment, dateval date, primary key (id));"
echo "ALTER TABLE $tblname PARTITION BY LIST(to_days($colname)) ("
for (( i = 0 ; i < $totaldate ; i++ ))
do
mysql -e"insert into test.mycalendar (dateval) select '$startdate' + interval $i day;"
done
mysql -Bse"select concat(' PARTITION ', concat(monthname(dateval), extract(year from dateval)), ' VALUES IN ( ') as '', group_concat(concat('to_days(', '\'',dateval,'\')') order by dateval) as '', '), ' as '' from test.mycalendar group by extract(year_month from dateval);" | sed '$s/,//31'
echo ');'
exit
# use the following syntax to use
# sh /root/calendar.sh '2002-01-01'
## Use the output to create monthly partitions with date
CREATE TABLE employees (
id INT NOT NULL,
store_date date
)
PARTITION BY LIST(to_days(store_date)) (
PARTITION Jan02 VALUES IN (
to_days('2002-01-01'),to_days('2002-01-02'),...
),
PARTITION Feb02 VALUES IN (
to_days('2002-02-01'),to_days('2002-02-02'),...
)
);
# monthly partitions less than 40 years are recommended, in other words not more than 480 partitions
 Read more: |
|
Saturday, 22 October 2011 01:45 |
// Find the list of all the tables being used from general log and compare them with the table names in Information Schema to know the tables those are not being accessed by any application and hence can be removed.
// Be careful it is not perfect, helps to get an idea about important tables.
unix>> grep "from " /var/log/mysql/mysqld.log | awk -Ffrom '{print $2}' | awk '{print $1}' | sort | uniq | cat > /var/lib/mysql/test/tablelist.txt
mysql> create table test.tableslog (tname varchar(250));
mysql> load data infile 'tablelist.txt' into table tableslog;
mysql> select t1.tname, t2.TABLE_NAME from test.tableslog as t1 LEFT join information_schema.TABLES as t2 on t1.tname = t2.TABLE_NAME group by t1.tname having t2.TABLE_NAME IS NULL
 Read more: |
|