Convert full datetime to start of the day in T-SQL

Nice trick to convert full datetime to just year-month-day portion, resetting hours, minutes, seconds. Works in T-SQL, SQL Server

This statement:
SELECT GETDATE() AS FullDate, DATEADD(day, DATEDIFF(day, '19000101', GETDATE()), '19000101') AS StartOfTheDay

Produces this result:
FullDate StartOfTheDay

2012-04-11 13:51:15.570 2012-04-11 00:00:00.000


Note1: you can put any datetime instead of GETDATE()

Note2: DATEADD(day, DATEDIFF(day, '19000101', GETDATE()), '19000101') is the functional equivalent of DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)

Alternatives to SQLite or SQL Server Compact

Wanted to find suitable local table storage for some quick data transformation engine. Didn't want to use tempdb or create\drop tables in my SQL Server DB.

SQLite.org:
Pros: just one dll, no install needed
Cons:
No Foreign Keys. Write locks the whole DB

SQL Server Compact Edition (CE) 4.0:
http://weblogs.asp.net/scottgu/archive/2011/01/11/vs-2010-sp1-and-sql-ce.aspx
http://www.microsoft.com/download/en/details.aspx?id=17876
Toolbox: http://sqlcetoolbox.codeplex.com/

works in a multi-threaded environment.  Previous releases of SQL CE only worked in client-apps and could corrupt/crash in server environments.  SQL CE 4 specifically supports server scenarios.
in-process; transactions; no XQuery or XML support; no SPs; DB size - .sdf file up to 4Gb
Management UI: VS2010 IDE, SQL Server Management Studio
Needs Visual C++ 2008 Runtime Libraries - installed with .Net 3.5 SP1
need to install tools for VS2010SP1 : http://go.microsoft.com/fwlink/?LinkId=212219

Other alternatives (to be continued):

http://www.firebirdsql.org/

How to query the table on remote sql server which has xml column(s)

Problem: you need to run a query on a table on remote ("linked") server. The table has one or more xml columns. Your query doesn't depend on any xml columns, however it still fails with an error Xml data type is not supported in distributed queries. Remote object 'SERVERNAME.DBNAME.dbo.tTable' has xml column(s).


Failing Query Example: SELECT TOP 10 SomeID FROM [SERVERNAME].[DBNAME].[dbo].tTable


Solution: Use OPENQUERY: SELECT * FROM OPENQUERY([SERVERNAME], 'SELECT TOP 10 SomeID FROM [DBNAME].[dbo].tTable') AS a


Thanks to: the bug (sorry, "limitation") in Sql Server 2005 and 2008

SQL Server, how to return the size and rows count for all tables in a database

The script that will return the size and number of rows for all user tables in a database.

DECLARE @Table VARCHAR(255)    
CREATE TABLE #Results
(
    [TableName] varchar(100),
    [RowsCount] varchar(100),
    [SizeReserved] varchar(50),
    [SizeData] varchar(50),
    [SizeIndexes] varchar(50),
    [SizeUnused] varchar(50)
)
 
-- Get all user tables
DECLARE cursorTableList CURSOR
FOR SELECT [name]
 FROM dbo.[sysobjects] 
 WHERE  OBJECTPROPERTY(id, N'IsUserTable') = 1
FOR READ ONLY
 
OPEN cursorTableList
FETCH NEXT FROM cursorTableList INTO @Table
WHILE (@@Fetch_Status >= 0)
BEGIN
    INSERT  #Results EXEC sp_spaceused @Table
    FETCH NEXT FROM cursorTableList INTO @Table
END
CLOSE cursorTableList
DEALLOCATE cursorTableList
-- RETURN RESULTS
SELECT * FROM #Results ORDER BY CAST([RowsCount] AS INT) DESC
DROP TABLE #Results
GO

SQL Server, GO xx will repeat the query xx times

INSERT INTO MyTable (SomeColumn) VALUES('baaa')
GO 44


The above will repeat the insert query 44 times. Very handy when you need to generate some dummy test data and your table supports that (i.e. has autonumber or identity column)

Howto remap the Windows Account to a SQL Server login

/*

Remaps the Windows Account to a database user. Works with SQL Server 2005, 2008, 2008 R2.

Expects the following variables to come from sqlcmd.exe:

$(DbName) - database name, i.e. AdventureWorks

$(ServerLogin) - the login to use, should be Windows Account, either local or domain. DON'T USE localhost! USE computer name or domain name!!

$(DbUserName) - the user within the database

Usage:

SQLCMD -S"MyServer" -E -i"RemapLogin.sql" -v DbName="AdventureWorks" -v ServerLogin="%COMPUTERNAME%\SomeUser" -v DbUserName="some_user_in_db" -b

*/

USE master

GO

IF EXISTS (SELECT sid FROM master..syslogins WHERE [name]=N'$(ServerLogin)')

BEGIN

PRINT 'Dropping the $(ServerLogin) login';

DROP LOGIN [$(ServerLogin)];

END

GO

PRINT 'Creating the $(ServerLogin) login from Windows Account';

GO

CREATE LOGIN [$(ServerLogin)] FROM WINDOWS

GO

-- Grant server role bulkadmin that's required for BCP.

EXEC master..sp_addsrvrolemember [$(ServerLogin)], 'bulkadmin'

GO

USE [$(DbName)]

GO

PRINT 'Mapping the $(DbUserName) user in DB to newly created login';

GO

/*

Need to detect SQL Server version because LOGIN clause was added only in SQL 2005 SP2

*/

IF EXISTS (SELECT ProductVersion FROM

(SELECT SERVERPROPERTY('ProductVersion') AS ProductVersion, SERVERPROPERTY('ProductLevel') AS ProductLevel) Vrsn

WHERE CAST(ProductVersion AS varchar(100)) LIKE '9.00.%' AND CAST(ProductLevel AS varchar(100)) IN ('RTM','SP1'))

BEGIN

PRINT 'SQL Server 2005 pre-SP2 detected';

ALTER USER $(DbUserName) WITH NAME = $(DbUserName);

END

ELSE

BEGIN

PRINT 'SQL Server 2005 SP2 or later or 2008 detected';

EXEC sp_executesql N'ALTER USER $(DbUserName) WITH NAME = $(DbUserName), LOGIN = [$(ServerLogin)]';

END

GO

PRINT 'FINISHED!';

GO

VMWare vFabric SQLFire vs memcached vs AppFabric vs ???

VMware vFabric SQLFire is an in-memory distributed SQL-based cache which can work with a traditional database to persist data to disk. Distributed, in-memory shared-nothing fault-tolerant SQL data management system. It offers a JDBC or ADO.NET interface for querying the datastore in a pure SQL manner, but the keys and indexes are stored in memory to provide high scalability, availability and better performance. Leverages the "time-tested", production GemFire technology but adds a SQL interface. SQLFire is being offered as part of vFabric Advanced for $1,800 per VM, but it will also be offered as a standalone product


http://communities.vmware.com/community/vmtn/appplatform/vfabric_sqlfire