Copyright (c) 2007 Mike Chirico (firstname.lastname@example.org) 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 <email@example.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 firstname.lastname@example.org> -- 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 */
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 email@example.com. All consulting work must include a donation to SourceForge.net.