For me, it is useful, to enable compression for al tables in a database.
EXEC sp_MSforeachtable
@command1="PRINT 'Starting compressing ' + CONVERT(VARCHAR(MAX), GETDATE(), 120) + ' ?'"
,@command2="PRINT 'Compressing table....' ALTER TABLE ? REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)"
,@command3="PRINT 'Finished compressing ' + CONVERT(VARCHAR(MAX), GETDATE(), 120) + ' ?'"
Unsere Trauung wird mitten in Worms stattfinden. Direkt neben dem Standesamt und dem Marktplatz befindet sich die Dreifaltigkeitskirche zu Worms.
“ Hotel Kriemhilde ”
Url: http://www.hotel-kriemhilde.de/
Telefon: 06241 / 91150
Adresse :
Hofgasse 2 - 4
67547 Worms
“ Familienweinkellerei Tadeus Ras ”
Url: http://www.tadeus-ras.de/
Telefon: 06241 / 3111
Adresse :
Obere Hauptstraße 118-122
67551 Worms/Horchheim
“Landhotel Hopp”
Url: http://www.landhotel-hopp.de/
Telefon: 06233 / 37780
Adresse :
Großniedesheimer Str.44
67258 Heßheim
“Frankenthaler Hof”
Url: http://www.frankenthalerhof.de
Telefon: 06233 / 62625
Adresse :
“Fröhliche Pfalz”
Url: http://www.froehliche-pfalz.de/
Telefon: 06239 / 926466
Adresse :
Entfernung zum Veranstaltungsort: ca. 2 Km
“Weinhaus Weis”
Url: http://www.weinhausweis.de
Telefon: 06241 / 23500
Adresse :
Entfernung zum Veranstaltungsort: ca. 8 Km
Sobald ich neue Infos über geeignete Übernachtungsmöglichkeiten in Worms und Bobenheim/Roxheim habe, werden ich sie hier veröffentlichen.
Am Veranstaltungsort, wo wir feiern werden gibt es Übernachtungsmöglichkeiten.
Url: http://www.seehotelbader.de
Telefon: 06239/4031
Sometimes is it necessary to calculate the first or the last day of month.
SQL code:
DECLARE
@ANY_DATE DATE = '2010-02-10'
,@FIRST_DAY_OF_MONTH DATE
,@LAST_DAY_OF_MONTH DATE
/* subtract n-1 days from current date */
SET @FIRST_DAY_OF_MONTH = DATEADD(DD, (-1) * (DAY(@ANY_DATE) - 1), @ANY_DATE)
/*
- add 1 month to first day of month
- subtract 1 day to get last day of month
*/
SET @LAST_DAY_OF_MONTH = DATEADD(DD, -1, DATEADD(MM, 1, @FIRST_DAY_OF_MONTH))
SELECT
@ANY_DATE ANY_DATE
, @FIRST_DAY_OF_MONTH FIRST_DAY_OF_MONTH
, @LAST_DAY_OF_MONTH LAST_DAY_OF_MONTH
Um allen Hochzeitsgästen eine kleine Orientierungsmöglichkeit zu geben, wo die Feier zu unserer Hochzeit stattfindet, haben wir mit Goggle Maps den Punkt markiert.
To increase the pleasant anticipation of our wedding party guests, we marked the place of the party with the help of google maps.
Sometimes it is necessary to get knowledge of the usage of your table columns. This little stored procedure is looking in all tables, views, functions and stored procedures for your column. But remember only user created objects will be scanned. The stored procedure for this functionality is stored in the master database, so it can be used for all databases in your instance.
The procedure takes two parameters:
USE master;
GO
IF OBJECT_ID('dbo.usp_SearchColumnUsage', 'P') IS NOT NULL
DROP PROCEDURE dbo.usp_SearchColumnUsage
GO
CREATE PROCEDURE dbo.usp_SearchColumnUsage
(
@SEARCHSTRING NVARCHAR(100) = NULL
,@DB_NAME NVARCHAR(100) = NULL
)
AS
BEGIN
DECLARE @cmd NVARCHAR(4000) =
'USE [' + LTRIM(RTRIM(@DB_NAME)) + '];
/* Searchs für Stored Procs und Functions*/
select
[SCHEMA] = sm.name
,[NAME] = so.name
,[OBJECTTYPE] = CASE so.TYPE
WHEN ''FN'' THEN ''UserDefinedFunction''
WHEN ''P'' THEN ''StoredProcedure''
ELSE ''UNKNOWN''
END
from sys.all_sql_modules s_all
JOIN sys.objects so ON s_all.object_id = so.object_id
JOIN sys.schemas sm ON sm.schema_id = so.schema_id
WHERE s_all.definition LIKE ''%' + @SearchString + '%''
UNION
/* query in Tables */
select
[SCHEMA] = t.TABLE_SCHEMA
,[NAME] = t.TABLE_NAME
,[OBJECTTYPE] = ''Table''
from INFORMATION_SCHEMA.COLUMNS c
JOIN INFORMATION_SCHEMA.TABLES t ON
c.TABLE_NAME = t.TABLE_NAME AND
c.TABLE_SCHEMA = t.TABLE_SCHEMA
WHERE c.COLUMN_NAME LIKE ''%' + @SearchString + '%''
UNION
/* query in Views */
select
[SCHEMA] = t.TABLE_SCHEMA
,[NAME] = t.TABLE_NAME
,[OBJECTTYPE] = ''View''
from INFORMATION_SCHEMA.COLUMNS c
JOIN INFORMATION_SCHEMA.VIEWS t ON
c.TABLE_NAME = t.TABLE_NAME AND
c.TABLE_SCHEMA = t.TABLE_SCHEMA
WHERE c.COLUMN_NAME LIKE ''%' + @SearchString + '%''
ORDER BY [SCHEMA], [NAME], [OBJECTTYPE]
'
EXECUTE sp_executesql @cmd
END
GO
EXECUTE master.dbo.usp_SearchColumnUsage @SEARCHSTRING = 'TABLE_SCHEMA', @DB_NAME = 'master'
For a regular review it is useful to identify unused indexes. With the following query you can show unused indexes in your database. So don’t forget to select your database.
If the columns “reads” = 0 or very low, then you can assume that this index isn’t used for querying.
SELECT
objectname=OBJECT_NAME(s.OBJECT_ID)
, indexname=i.name
, i.index_id
, reads=user_seeks + user_scans + user_lookups
, writes = user_updates
, p.rows
FROM sys.dm_db_index_usage_stats s
JOIN sys.indexes i ON
i.index_id = s.index_id AND
s.OBJECT_ID = i.OBJECT_ID
JOIN sys.partitions p ON
p.index_id = s.index_id AND
s.OBJECT_ID = p.OBJECT_ID
WHERE
OBJECTPROPERTY(s.OBJECT_ID,'IsUserTable') = 1
AND s.database_id = DB_ID()
AND i.type_desc = 'nonclustered'
AND i.is_primary_key = 0
AND i.is_unique_constraint = 0
AND p.rows > 10000
ORDER BY reads, rows DESC
This query shows some index hints, which might be useful for your database instance. Please review carefully these ddl statements before you apply them to your database.
SELECT
migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure,
'CREATE INDEX [missing_index_' + CONVERT (varchar, mig.index_group_handle) + '_' + CONVERT (varchar, mid.index_handle)
+ '_' + LEFT (PARSENAME(mid.statement, 1), 32) + ']'
+ ' ON ' + mid.statement
+ ' (' + ISNULL (mid.equality_columns, '')
+ CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE ' ' END
+ ISNULL (mid.inequality_columns, '')
+ ')'
+ ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement,
migs.*,
mid.database_id, mid.[object_id]
FROM sys.dm_db_missing_index_groups mig
JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE
migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) > 10
ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC
