12/5/12

Just a sample script of refreshing indexes in the current database

-- To rebuild or reorganize just the "few" fragmented of all indexes in the current database...
-- And remember, this is just a sample of how you could do it.
-- See this as inspiration rather than the "best way"
DECLARE
iterator CURSOR FOR SELECT'ALTER INDEX [' + INDEX_NAME + '] ON [' + SCHEMA_NAME + '].[' + TABLE_NAME + '] ' +CASEWHEN avg_fragmentation_in_percent < 30 AND page_count > 200 THEN 'REORGANIZE'ELSE 'REBUILD'END stmtFROM (SELECTc.name SCHEMA_NAME, t
.name TABLE_NAME, i
.name INDEX_NAME, s
.avg_fragmentation_in_percent, s
.page_count,i.indid FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) sINNER JOIN sys.tables t ON s.object_id = t.object_idINNER JOIN sys.sysindexes i ON s.object_id = i.id AND s.index_id = i.indidINNER JOIN sys.schemas c ON t.schema_id = c.schema_idWHERE s.avg_fragmentation_in_percent >= 10 AND i.indid > 0) mORDER BY m.indid ASCDECLARE
@stmt varchar(max)OPEN iteratorFETCH NEXT FROM iterator INTO @stmtWHILE @@FETCH_STATUS = 0BEGIN
-- PRINT
'EXECUTING: ' + @stmt -- Just if you want some outputEXEC (@stmt)FETCH NEXT FROM iterator INTO @stmtEND
CLOSE
iteratorDEALLOCATE iterator

8/29/12

By request

DECLARE @start datetime, @numberofyears int
SET @start = '1900-01-01'
SET @numberofyears = 180
;WITH a1(col)
AS (
SELECT NULL
UNION ALL
SELECT NULL
), a2(col)
AS (
SELECT NULL FROM a1 x1 CROSS JOIN a1 x2
), a3(col)
AS (
SELECT NULL FROM a2 x1 CROSS JOIN a2 x2
), a4(col)
AS (
SELECT NULL FROM a3 x1 CROSS JOIN a3 x2
), a5(col)
AS (
SELECT NULL FROM a4 x1 CROSS JOIN a4 x2
), nums(num)
AS (
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1 FROM a5
), dates(datetime)
AS (
SELECT DATEADD(day, num, @start) FROM nums WHERE DATEADD(day, num, @start) < DATEADD(year, @numberofyears, @start)
)
SELECT YEAR(datetime) * 10000 + MONTH(datetime) * 100 + DAY(datetime) AS DATEKEY, datetime FROM dates

7/16/12

Shinking transactionlogs

Hey friends, I usually get questions and concerns like "how do I shrink this database", "the logfile is so big", "please help me". I got this splendid idea to write this topic when I got a question about shrinking a large database from a friend, so thanks Mostafa. This one is for you.

How do I shrink a database?
DBCC SHRINKDATABASE('databasename', 10)
-- 10 is the amount of percent free space in the database after the shrink operation

How do I shrink a transactionlog?
DBCC SHRINKFILE(2, 10)
-- 2 is the second file of the database, usually the transactionlog
-- 10 is the amount of percent free space in the file after the shrink operation

Ok, just a sample PIVOT

DECLARE @DatabaseObjects TABLE
  (
    DatabaseName sysname,
    ObjectType sysname,
    ObjectName sysname
  );
DECLARE
  @DatabaseName sysname,
  @SQLStatement varchar(max),
  @ExecuteStatement varchar(max),
  @Columns varchar(max);

SET NOCOUNT ON;
SET @SQLStatement = 'SELECT ''{DatabaseName}'' AS [DatabaseName], [type_desc] AS [ObjectType], [name] AS [ObjectName] FROM [{DatabaseName}].[sys].[objects];';
DECLARE
  DatabaseCrawler CURSOR
  FOR SELECT Name FROM sys.Databases ORDER BY Name ASC;
OPEN DatabaseCrawler;
FETCH NEXT FROM DatabaseCrawler INTO @DatabaseName;
WHILE @@FETCH_STATUS = 0
BEGIN
  SET @ExecuteStatement = REPLACE(@SQLStatement, '{DatabaseName}', @DatabaseName);
  INSERT INTO @DatabaseObjects
  EXEC (@ExecuteStatement);
  FETCH NEXT FROM DatabaseCrawler INTO @DatabaseName;
END;
CLOSE DatabaseCrawler;
DEALLOCATE DatabaseCrawler;
SET NOCOUNT OFF;

SELECT * INTO #DatabaseObjects FROM @DatabaseObjects

SELECT @Columns = ISNULL(@Columns, '') + '[' + [Column] + '],' FROM (SELECT DISTINCT ObjectType AS [Column] FROM @DatabaseObjects) Columns

SET @Columns = LEFT(@Columns, LEN(@Columns) - 1)

SET @SQLStatement = 'SELECT DatabaseName, {Columns} FROM #DatabaseObjects PIVOT (Count(ObjectName) FOR ObjectType IN ({Columns})) AS pvt'

SET @ExecuteStatement = REPLACE(@SQLStatement, '{Columns}', @Columns);

EXEC (@ExecuteStatement)
DROP TABLE #DatabaseObjects

5/25/12

Conferences to look for Autumn 2012

There are some nice SQL conferences upcoming:
SQLTuneIn 2012 in Zagreb, Croatia, 24-25 of September
http://www.sqltunein2012.com/

PASS SQLRally Nordic 2012 in Copenhagen, Denmark, 1-3 of October
http://www.sqlpass.org/sqlrally/2012/nordic/

And then it's:
Microsoft TechEd EMEA in Amsterdam, Netherlands, 26-29 of June
http://europe.msteched.com/

MCT Summit Europe in Warsaw, Poland, 6-8 of September
http://www.mctsummit.eu/

SEF 2012 in Stockholm, Sweden, 22-23 of October
http://www.seforum.se

And don't miss
Microsoft Sommarkollo 2012, Sweden, Summer of 2012
http://www.microsoft.com/sv-se/sommarkollo/default.aspx

I'll probably be on most of these as a speaker or other official role, if you're plan to visit one or more of these conferences. Check out this blog to see if I'm there aswell.

3/8/12

SQL Server 2012 goes RTM

A few weeks ago the last golden bits of SQL Server 2012 were finally signed ready. Yesterday, or today, depending on where on earth you are the launch of the newest, finest version of SQL Server ever.

Download from here: Try SQL Server 2012 RTM
Experience the SQL Server Virtual Launch here: Party

Back from my first ever MVP Summit

I'd like to direct a huge thank you to the SQL Server Product Group, my MVP Lead Willian Jansen, the MVP Awards team, and the community for giving me the opportunity of being a MVP on SQL Server: Architecture. The summit rocked, and I will be back if you want me too.

1/9/12

SQL Server 2012, new functions: DATE & TIME FROMPARTS

We have a series of new function in SQL Server 2012. Within the Date and Time area we got both the EOMONTH and a whole bunch of date and/or time from parts functions.

SMALLDATETIMEFROMPARTS( year, month, day, hour, minute )
DATETIMEFROMPARTS( year, month, day, hour, minute, seconds, milliseconds )
DATETIME2FROMPARTS( year, month, day, hour, minute, seconds, fractions, precision )
DATETIMEOFFSETFROMPARTS( year, month, day, hour, minute, seconds, fractions, hour_offset, minute_offset, precision )
DATEFROMPARTS( year, month, day )
TIMEFROMPARTS( hour, minute, seconds, fractions, precision )

When is this needed?

If you store date and time with integers, one column per part, take a look at the example below.

DECLARE @table TABLE (
iYear int,
iMonth int,
iDay int,
iHour int,
iMinute int,
iSecond int,
iMilliseconds int,
iFractions int );
DECLARE @timestamp datetime2(7);
SELECT @timestamp = sysdatetime();
INSERT INTO @table(iYear, iMonth, iDay, iHour, iMinute, iSecond, iMilliseconds, iFractions)
SELECT
DATEPART(yyyy,@timestamp), -- Years
DATEPART(mm,@timestamp), -- Months
DATEPART(dd,@timestamp), -- Days
DATEPART(hh,@timestamp), -- Hours
DATEPART(mi,@timestamp), -- Minutes
DATEPART(ss,@timestamp), -- Seconds
DATEPART(ms,@timestamp), -- Milliseconds
DATEPART(ns,@timestamp)/100;
-- The fraction, amount of nanoseconds divided by 100
-- Test the functions
SELECT SMALLDATETIMEFROMPARTS(iYear, iMonth, iDay, iHour, iMinute) FROM @table -- SMALLDATETIME
SELECT DATETIMEFROMPARTS(iYear, iMonth, iDay, iHour, iMinute, iSecond, iMilliseconds) FROM @table -- DATETIME
SELECT DATETIME2FROMPARTS(iYear, iMonth, iDay, iHour, iMinute, iSecond, iFractions, 7) FROM @table -- DATETIME2
SELECT DATETIMEOFFSETFROMPARTS(iYear, iMonth, iDay, iHour, iMinute, iSecond, iFractions, 1, 0, 7) FROM @table -- DATETIMEOFFSET
SELECT DATEFROMPARTS(iYear, iMonth, iDay) FROM @table -- DATE
SELECT TIMEFROMPARTS(iHour, iMinute, iSecond, iFractions, 7) FROM @table -- TIME

SQL Server 2012, new functions: EOMONTH

Have you missed the Oracle function LAST_DAY? Have you solved it in Microsoft SQL Server in various ways?

What we could do before the upcoming release of SQL Server is to start out with the current date, add one month, a8nd subtract the number of days of the current month. Or we could get the current date, add one month, convert that to characters, cut out the year and month part and concatenate that with the string '801', and convert that to date, and then subract one day.

Look no further, now we have the EOMONTH-function instead.

EOMONTH ( start_date [, month_to_add ] ) * start_date is the current date, or the date you want the highest date for * month_to_add is the optional increment of months

-- Prepare a variable with the current date
DECLARE @date date;
SELECT @date = getdate();

-- Method 1
SELECT DATEADD(day, (0 - datepart(day, @date)), DATEADD(month, 1, @date));

-- Method 2
SELECT dateadd(day, -1, CAST(CONVERT(varchar(8), dateadd(month, 1, @date), 120) + '01' AS date));

-- New function
SELECT EOMONTH(@date);
SELECT EOMONTH(@date, 2);
SELECT EOMONTH(@date, -4);

New and changed functions in SQL Server 2012

Microsoft SQL Server 2012 introduces 14 new built-in functions. These functions ease the path of migration for information workers by emulating functionality that is found in the expression languages of many desktop applications. However these functions will also be useful to experienced users of SQL Server.

The new functions are:


Conversion functions
* PARSE
* TRY_CONVERT
* TRY_PARSE

Date and time functions
* EOMONTH
* SMALLDATETIMEFROMPARTS
* DATETIMEFROMPARTS
* DATETIME2FROMPARTS
* DATETIMEOFFSETFROMPARTS
* DATEFROMPARTS
* TIMEFROMPARTS

Logical functions
* CHOOSE
* IIF

String functions
* CONCAT
* FORMAT

In addition to the 14 new functions, one existing function has been changed. The existing LOG function now has an optional second base parameter.

I will try to cover them over the next upcoming blogposts. I start right away with Date and time functions in the next two posts.