Fast inserting a file into a table

Submitted by st on Sat, 01/12/2012 - 11:15

How to insert fast a file (i.e. binary one) from disk to a SQL Server table?

Certainly the SQL Server account should have a corresponding permissions to access the file on the disk. For example I take the file C:\WINDOWS\system32\oembios.bin having the size about 12 MB. For really big files you'd better switch to the FILESTREAM approach.

Programming

SQL Server

SQL: generate date/time series

Submitted by st on Fri, 27/04/2012 - 20:31

There are many examples of user defined table functions generating the series of dates(times) between two specified values. Usually, in OLAP you can see a table filled by the date/time series, too. Such table avoid to generate the values "on the fly".

However, in some cases you may be unable to use an UDF or a table. Fortunately, "pure SQL" method still works.

Suppose, the start and finish dates are specified as entry parameters. The transact SQL code below generates the series "moth by month".

Programming

SQL: delete duplicated rows

Submitted by st on Fri, 09/03/2012 - 16:17

How to delete the rows having the duplicated values of one or more columns and considered as duplicates? The example in Transact SQL is below.

Programming

SQL Server

Transact SQL: exporting table or view in CSV

Submitted by st on Mon, 20/02/2012 - 12:38

It's pretty easy on SQL Server but pay attention to the database/server rights of used connection. Usually, the preparing like sp_configure requires a system administrator privileges.

Programming

SQL Server

DateTime in C++

Submitted by st on Sat, 04/12/2010 - 13:48

Many years ago we had a need to manipulate the data of types "date" and "time" (DateTime in C# and Delphi/C++Builder). I had developed the corresponding class for these purposes.

Here is the interface with comments:

Programming

Indexing columns of bit type

Submitted by st on Wed, 24/11/2010 - 20:06

Clearly, an index build on only one column of the bit type is useless because the selectivity is poor (50% of a table in average). However, when a table has several columns of the bit type, the composite index may be efficient.

For the test example, we'll create a table and fill it with random data.

Programming

SQL Server

Dynamic result ordering in SQL

Submitted by st on Tue, 18/05/2010 - 17:19

There are several methods to implement the dynamic ordering of result returned by SQL query. The examples of stored procedures below use the Microsoft's demo database AdventureWorks.