Fast inserting a file into a table

Submitted by Serguei_Tarassov on

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.

SQL: generate date/time series

Submitted by Serguei_Tarassov on

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".

Indexing columns of bit type

Submitted by Serguei_Tarassov on

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.

Dynamic filtering with SQL

Submitted by Serguei_Tarassov on

The one of the most frequent asked question in database development is "How to filter the data returned by a query according to some user selected criteria?". To complete the solution with dynamic ordering see my other blog post.

For every method template I will add icons indicating corresponding advantages and drawbacks.

Pages

Subscribe to Mechanics of software RSS