Mike's notes page is souptonuts. For
open source consulting needs, please send an email to
mchirico@gmail.com. All consulting work must include a donation to
SourceForge.net.
Copyright (c) 2007 Mike Chirico (mchirico@gmail.com)
Permission is granted to copy, distribute and/or modify this document
under the terms of the GNU Free Documentation License, Version 1.2 or
any later version published by the Free Software Foundation.
Last Updated: Fri Dec 21 12:37:14 EST 2007
TIP 1:
Need to know how much fragmentation?
DBCC SHOWCONTIG (tbl_transaction)
There is also a more advanced option
DBCC SHOWCONTIG (tbl_transaction) WITH TABLERESULTS, ALL_LEVELS
There is also a defrag command
DBCC INDEXDEFRAG(Northwind,Customers,1)
TIP 2:
This is a cool feature:
IF object_id('OutputTest') IS NOT NULL
BEGIN
drop table OutputTest
END
CREATE TABLE OutputTest
(
ID int NOT NULL,
Description varchar(max) NOT NULL,
)
INSERT INTO OutputTest (ID, Description) VALUES (1, 'row 1')
INSERT INTO OutputTest (ID, Description) VALUES (2, 'row 2')
INSERT INTO OutputTest (ID, Description) VALUES (3, 'row 3')
DECLARE @DeleteLog AS TABLE (LogID INT, LogEntry VARCHAR(MAX))
DELETE OutputTest
OUTPUT DELETED.ID, DELETED.Description INTO @DeleteLog
WHERE ID = 1
SELECT *into DeleteLog FROM @DeleteLog
select * from DeleteLog
TIP 3:
Standard table create statement, with getdate() for automatically
recording the time when a record was added.
CREATE TABLE [junk23](
[JunkID] [smallint] IDENTITY(1,1) NOT NULL,
[Name] varchar(20) NOT NULL,
[ModifiedDate] [datetime] NOT NULL DEFAULT (getdate()))
TIP 4:
create PROCEDURE Insert_Top as
/*
CREATE TABLE [dbo].[top](
[SPID] [float] NULL,
[Status] [nvarchar](255) NULL,
[Login] [nvarchar](255) NULL,
[HostName] [nvarchar](255) NULL,
[BlkBy] [nvarchar](255) NULL,
[DBName] [nvarchar](255) NULL,
[Command] [nvarchar](255) NULL,
[CPUTime] [float] NULL,
[DiskIO] [float] NULL,
[LastBatch] [nvarchar](255) NULL,
[ProgramName] [nvarchar](255) NULL,
[SPID1] [float] NULL,
[REQUESTID] [float] NULL,
[ModifiedDate] [datetime] NULL CONSTRAINT [DF_top_ModifiedDate] DEFAULT (getdate())
) ON [PRIMARY]
*/
INSERT INTO [DPart_P008_Dev].[dbo].[top]
(
[SPID],
[Status],
[Login],
[HostName],
[BlkBy],
[DBName],
[Command],
[CPUTime],
[DiskIO],
[LastBatch],
[ProgramName],
[SPID1],
[REQUESTID])
EXEC sp_who2
Now, create the loop procedure:
create PROCEDURE Loop_Insert_Top as
WHILE (1=1) BEGIN
EXEC insert_top
WAITFOR DELAY '00:01:00';
END
To view the records:
select * from [top]
TIP 5:
sp_help
sp_helptext Insert_Page7_into_Combined_sproc
"All that really happens when you create a procedure is that its
syntax is checked and its source code is inserted into the
syscomments system table"
TIP 6:
Getting a directory listing of the c:\ drive.
-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO
EXEC master..xp_cmdshell "dir c:\*"
TIP 7:
select
'Name' = o.name,
'Owner' = user_name(ObjectProperty( object_id, 'ownerid')),
'Object_type' = substring(v.name,5,31)
--into t_sp_help
from sys.all_objects o, master.dbo.spt_values v
where o.type = substring(v.name,1,2) collate database_default and v.type = 'O9T'
and substring(v.name,5,31) ='stored procedure'
and user_name(ObjectProperty( object_id, 'ownerid'))='dbo'
order by [Owner] asc, Object_type desc, Name asc
TIP 8:
You may need indexes. Try running the following query to see if the
value is above 5000.
SELECT *
FROM
(SELECT user_seeks * avg_total_user_cost * (avg_user_impact * 0.01) AS index_advantage,
migs.* FROM sys.dm_db_missing_index_group_stats migs) AS migs_adv
INNER JOIN sys.dm_db_missing_index_groups AS mig ON migs_adv.group_handle =
mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid ON mig.index_handle = mid.index_handle
ORDER BY migs_adv.index_advantage
TIP 9:
Need to see what users are doing?
select * from sys.dm_exec_sessions
TIP 10:
Need the last day of the month?
Select
dateadd(mm,1,convert(datetime,convert(char(10),getdate(),110)) -
day(getdate())+1)-1
TIP 11:
Put last day of month into a user defined function.
create FUNCTION dbo.GetLastDayOfMonth()
RETURNS datetime
AS
BEGIN
DECLARE @OutDate datetime
set @OutDate = (Select dateadd(mm,1,convert(datetime,convert(char(10),getdate(),110))
- day(getdate())+1)-1)
RETURN(@OutDate)
END
Now call it:
select dbo.GetLastDayOfMonth()
TIP 12:
Need a function that gets the date a year ago at the beginning of
the month?
create FUNCTION dbo.Get1YearAgo()
RETURNS datetime
AS
BEGIN
DECLARE @OutDate datetime
set @OutDate = (Select dateadd(mm,-12,convert(datetime,convert(char(10),
getdate(),110)) - day(getdate())+1)
)
RETURN(@OutDate)
END
select dbo.Get1YearAgo()
TIP 13:
Need to get a list of all stored procedures?
SELECT name
FROM dbo.sysobjects
WHERE type = 'P' AND category = 0
TIP 14:
Need to get the median? Also see TIP 15 below and TIP 19.
SELECT Median=AVG(c1) FROM (
SELECT MIN(c1) AS c1 FROM (
SELECT TOP 50 PERCENT col AS c1 FROM table
ORDER BY c1 DESC) t
UNION ALL
SELECT MAX(c1) FROM (
SELECT TOP 50 PERCENT col AS c1 FROM table
ORDER BY c1) t
) M
(Also reference: http://www.sqlmag.com/Article/ArticleID/49827/49827.html)
But note, you can probably use the sp_executesql statement as well.
Here's a way to get the 75%.
WITH T AS
(
SELECT [Sls Rep],[Prod No],[UOM],[Unit Sales $],
ROW_NUMBER() OVER(PARTITION BY [Sls Rep],[Prod No],[UOM]
ORDER BY [Unit Sales $]) AS RowNum,
COUNT(*) OVER(PARTITION BY [Sls Rep],[Prod No],[UOM]) AS Cnt
FROM Pg7_all_trans
)
SELECT [Sls Rep],[Prod No],[UOM],Avg([Unit Sales $]) [Median Unit Sales $],
Max(RowNum) [RowNum], Max(Cnt) [Cnt] into Pg7_75
FROM T
WHERE RowNum >= 0.75*(Cnt+1) and RowNum <= 0.75*(Cnt+2)
GROUP BY [Sls Rep],[Prod No],[UOM]
TIP 15:
sp_executesql -- Executing the contents of a string as a SQL statement.
Declare @S nvarchar(500);
set @S = 'select * from' + ' junk'
execute sp_executesql @S
But wait! You can do this in a sproc:
create proc select_table(@a as nvarchar(50))
as
Declare @t as nvarchar(500);
set @t = 'select * from ' + @a;
execute sp_executesql @t
Now just run this as follows:
select_table 'junk';
Here's another example with parameters:
EXECUTE sp_executesql
N'SELECT * FROM AdventureWorks.HumanResources.Employee
WHERE ManagerID = @level',
N'@level tinyint',
@level = 109;
Here's an example with quotes:
Declare @S nvarchar(max);
set @S = '
IF object_id(''junk'') IS NOT NULL
BEGIN
DROP TABLE junk
END
create table
junk (a int,
b varchar(20));
insert into junk (a,b) values (1,''one'');
insert into junk (a,b) values (2,''two'');
select * from junk;
'
-- print @S
execute sp_executesql @S
(Also reference: http://msdn2.microsoft.com/en-us/library/ms188001.aspx)
TIP 16:
More sp_executesql --
DECLARE @sql AS NVARCHAR(MAX), @i AS INT;
SET @sql = N'';
SET @i = 1;
WHILE @i <= 100
BEGIN
SET @sql = @sql + N'PRINT ' + CAST(@i AS NVARCHAR(10))
+ NCHAR(13) + NCHAR(10);
SET @i = @i + 1;
END
EXEC sp_executesql @sql;
TIP 17:
Simple example of a union statement:
select * into t1_t2_combined from
(select * from t1
union
select * from t2) as a
TIP 18:
Need to find SQL version?
SELECT @@VERSION
or
SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'),
SERVERPROPERTY ('edition')
Reference:
http://support.microsoft.com/kb/321185
TIP 19:
Example calculating Median on the table Median_table
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
alter FUNCTION [dbo].[calc_median_product_uom] (@product as nvarchar(30),
@uom as nvarchar(4)
)
-- Created: Mike Chirico <mchirico@gmail.com>
-- Updated: Thu Sep 27 16:30:20 EDT 2007
--
-- Example Usage:
--
-- select product,uom,DPart_P008.dbo.calc_median_product_uom(product,uom) Median from Median_table
--
--
RETURNS money
AS
BEGIN
DECLARE @Median as money
SELECT @Median=AVG(c1) FROM (
SELECT MIN(c1) AS c1
FROM (SELECT TOP 50 PERCENT AvqPrice AS c1 FROM Median_table
where product=@product and uom=@uom ORDER BY c1 DESC
) as t
UNION ALL
select max(c1) as c1 from (
SELECT TOP 50 PERCENT AvqPrice AS c1 FROM
Median_table where product=@product and uom=@uom ORDER BY c1
) as t
) M
RETURN(@Median)
END
TIP 20:
Example listing all the indexes on table mytable.
select a.name table_name,
b.name index_name,
d.name column_name,
c.index_column_id
from sys.tables a,
sys.indexes b,
sys.index_columns c,
sys.columns d
where a.object_id = b.object_id
and b.object_id = c.object_id
and b.index_id = c.index_id
and c.object_id = d.object_id
and c.column_id = d.column_id
and a.name = 'mytable'
TIP 21:
Need a comparison between Oracle 10g and SQL 2005? Take a look at
the following:
http://www.wisdomforce.com/dweb/resources/docs/MSSQL2005_ORACLE10g_compare.pdf
TIP 22:
Need to free up space? The following query shows how much space
can be freed.
SELECT name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB
FROM sys.database_files;
To free up space from the database 'mydatabase' use the following
command:
DBCC SHRINKDATABASE('mydatabase',0);
To free up sapce from the logfile 'mylogfile' use the following command:
DBCC SHRINKFILE('mylogfile',0);
TIP 23:
Getting the Sum, Max across rows in a table.
Assume a simple table defined as follows:
create table junk
(a int,
b int,
c int);
insert into junk (a,b,c) values (1,4,3);
insert into junk (a,b,c) values (2,5,9);
insert into junk (a,b,c) values (3,8,2);
select *, (select sum(my_columns)
from (select a as my_columns
union all
select b
union all
select c) as t) as sum_column
from junk
Tip 24:
Delete all tables with the name delete in them.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Mike Chirico mchirico@gmail.com>
-- Create date: <Thu Nov 1 08:34:41 EDT 2007>
-- Description: <This proc will go through and delete
-- all tables with the name delete in them.>
-- =============================================
alter PROCEDURE free_space
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE cursor_value CURSOR
FOR
select name from sys.tables where name like '%delete%'
FOR READ ONLY
DECLARE @cursor_row_value varchar(120)
DECLARE @S nvarchar(500);
OPEN cursor_value
FETCH cursor_value INTO @cursor_row_value
WHILE (@@FETCH_STATUS=0) BEGIN
set @S = 'drop table ['+@cursor_row_value+']'
execute sp_executesql @S
FETCH cursor_value INTO @cursor_row_value
END
CLOSE cursor_value
DEALLOCATE cursor_value
END
GO
TIP 25:
What is the difference between UNION and UNION ALL? UNION only returns the distinct
number of rows and is slower than UNION ALL.
TIP 26:
What is the difference between NVARCHAR, VARCHAR and CHAR?
NVARCHAR takes two bytes, VARCHAR takes 1 byte, and CHAR is fixed
length.
TIP 27:
Order can make a difference when doing updates
IF object_id('tempdb..#t1') IS NOT NULL
BEGIN
DROP TABLE #t1
END
IF object_id('tempdb..#t2') IS NOT NULL
BEGIN
DROP TABLE #t2
END
IF object_id('tempdb..#t3') IS NOT NULL
BEGIN
DROP TABLE #t3
END
create table #t1 (a int, b int);
insert into #t1 (a,b) values (1,2);
insert into #t1 (a,b) values (1,5);
create table #t2 (a int ,b int, c int, d int, [order] int);
insert into #t2 (a,b,c,d,[order]) values (1,2,10,10,0);
insert into #t2 (a,b,c,d,[order]) values (1,2,50,60,1);
-- Change order here:
--select * into #t3 from #t2 order by [order] desc
select * into #t3 from #t2 order by [order] asc
update #t1
set #t1.a=#t3.c, #t1.b=#t3.d
from #t1 ,#t3
where #t1.a=#t3.a and #t1.b=#t3.b
select * from #t1
TIP 28:
Need a listing of all stored procedures on the system that you
created?
SELECT * -- Stored Procs
FROM sys.objects
WHERE objectproperty( object_id, N'IsMSShipped' ) = 0
AND objectproperty( object_id, N'IsProcedure' ) = 1
Below are some other common methods. But take a look at the
following below and note the condition where N'IsMSShipped' is
false, or equal to zero.
SELECT * -- Functions
FROM sys.objects
WHERE objectproperty( object_id, N'IsMSShipped' ) = 0
AND ( objectproperty( object_id, N'IsTableFunction' ) = 1
OR objectproperty( object_id, N'IsScalarFunction' ) = 1 )
SELECT * -- User tables
FROM sys.objects
WHERE objectproperty( object_id, N'IsMSShipped' ) = 0
AND objectproperty( object_id, N'IsTable' ) = 1
SELECT * -- Views
FROM sys.objects
WHERE objectproperty( object_id, N'IsMSShipped' ) = 0
AND objectproperty( object_id, N'IsView' ) = 1
Reference:
http://msdn2.microsoft.com/en-us/library/ms176105.aspx
http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/06/29/850.aspx
TIP 29:
Combining two tables into 1 pivot table
create table p1 (a int, b int)
create table p2 (a int, b int)
delete from p1;
delete from p2;
insert into p1 (a,b) values (1,34);
insert into p1 (a,b) values (2,12);
insert into p1 (a,b) values (3,22);
insert into p2 (a,b) values (1,500);
insert into p2 (a,b) values (2,512);
insert into p2 (a,b) values (4,600);
select g.a,g.b,g.c
from
(
select p1.a, p1.b,p2.b as c from p1, p2
where p1.a=p2.a
union all
select p1.a,p1.b,null as c from p1 left outer join
p2 on p1.a=p2.a
where p2.a is null
union all
select p2.a,null as b,p2.b as c from p2 left outer join
p1 on p2.a=p1.a
where p1.a is null
) as g
/* Results:
a b c
1 34 500
2 12 512
3 22 NULL
4 NULL 600
*/
ADDITIONAL TUTORIALS
Linux Quota Tutorial This tutorial walks you through implementing disk quotas for both users and groups on Linux, using a virtual filesystem, which is a filesystem created from a disk file. Since quotas work on a per-filesystem basis, this is a way to implement quotas on a sub-section, or even multiple subsections of your drive, without reformatting. This tutorial also covers quotactl, or quota's C interface, by way of an example program that can store disk usage in a SQLite database for monitoring data usage over time.Gmail on Home Linux Box using Postfix and Fetchmail If you have a Google Gmail account, you can relay mail from your home linux system. It's a good exercise in configuring Postfix with TLS and SASL. Plus, you will learn how to bring down the mail safely, using fetchmail with the "sslcertck" option.
Breaking Firewalls with OpenSSH and PuTTY If the system administrator deliberately filters out all traffic except port 22 (ssh), to a single server, it is very likely that you can still gain access other computers behind the firewall. This article shows how remote Linux and Windows users can gain access to firewalled samba, mail, and http servers. In essence, it shows how openSSH and Putty can be used as a VPN solution for your home or workplace.
Create your own custom Live Linux CD These steps will show you how to create a functioning Linux system, with the latest 2.6 kernel compiled from source, and how to integrate the BusyBox utilities including the installation of DHCP. Plus, how to compile in the OpenSSH package on this CD based system. On system boot-up a filesystem will be created and the contents from the CD will be uncompressed and completely loaded into RAM -- the CD could be removed at this point for boot-up on a second computer. The remaining functioning system will have full ssh capabilities. You can take over any PC assuming, of course, you have configured the kernel with the appropriate drivers and the PC can boot from a CD.
SQLite Tutorial This article explores the power and simplicity of sqlite3, first by starting with common commands and triggers, then the attach statement with the union operation is introduced in a way that allows multiple tables, in separate databases, to be combined as one virtual table, without the overhead of copying or moving data. Next, the simple sign function and the amazingly powerful trick of using this function in SQL select statements to solve complex queries with a single pass through the data is demonstrated, after making a brief mathematical case for how the sign function defines the absolute value and IF conditions.
Lemon Parser Tutorial Lemon is a compact, thread safe, well-tested parser generator written by D. Richard Hipp. Using a parser generator, along with a scanner like flex, can be advantageous because there is less code to write. You just write the grammar for the parser. This article is an introduction to the Lemon Parser, complete with examples.
Mike Chirico, a father of triplets (all girls) lives outside of
Philadelphia, PA, USA. He has worked with Linux since 1996, has a Masters
in Computer Science and Mathematics from Villanova University, and has
worked in computer-related jobs from Wall Street to the University of
Pennsylvania. His hero is Paul Erdos, a brilliant number theorist who was
known for his open collaboration with others.
Mike's notes page is souptonuts. For
open source consulting needs, please send an email to
mchirico@gmail.com. All consulting work must include a donation to
SourceForge.net.