Database

Get Number of Days in a Month Function

This article shows the ways to get the number of days in a month in both sqlserver and mysql.

Getting the number of days in a month is quite easy because it is going to be either 30 or 31 days, with the exception of February, which can either have 28 or 29 days depending if it is a leap year or not.

  • January,March,May,July,August,October,December — 31 Days
  • April,June,September,November – 30 Days
  • February – 28 Days (Non Leap Year), 29 (Leap Year)

Leap year should be divisible by 4, should not be divisible by 100 or should be divisible by 400.

(continue reading…)


A few maximum limitations for Sqlserver and MySql

Often people in newsgroups ask about maximum size of various objects defined in databases.

The following tables shows the maximum sizes and numbers of various objects defined in MYSql, SQL Server.


MySql Maximums
Char 255 Bytes
Varchar 65,535 Bytes
Binary 255 Bytes
Varbinary 65,535 Bytes
TinyBlob 255 Bytes
TinyText 255 Bytes
Blob 65,535 Bytes (2^16 -1)
Text 65,535 Bytes (2^16 -1)
Medium Blob 16,777,215 Bytes (2^24 -1)
Medium Text 16,777,215 Bytes (2^24 -1)
LongBlog 4,294,967,295 Bytes (2^32 -1)
LongText 4,294,967,295 Bytes (2^32 -1)
Bit Range from 1 to 64
Tinyint The signed range is -128 to 127. The unsigned range is 0 to 255.
BOOL, BOOLEAN true or false
SmallInt The signed range is -32768 to 32767. The unsigned range is 0 to 65535.
MediumInt The signed range is -8388608 to 8388607. The unsigned range is 0 to 16777215.
Int/Integer The signed range is -2147483648 to 2147483647. The unsigned range is 0 to 4294967295.
BigInt The signed range is -9223372036854775808 to 9223372036854775807. The unsigned range
is 0 to 18446744073709551615.
Float Allowable values are -3.402823466E+38 to -1.175494351E-38, 0, and 1.175494351E-38
to 3.402823466E+38.
Double Allowable values are -1.7976931348623157E+308 to -2.2250738585072014E-308, 0, and
2.2250738585072014E-308 to 1.7976931348623157E+308.

(continue reading…)


How to Read Statistics Profile

(2006-09-01 added a paragraph on parallel query plans)

In SQL Server, “Statistics Profile” is a mode in which a query is run where you can see the number of invocations of each physical plan operator in the tree. Instead of running a query and just printing the output rows, this mode also collects and returns per-operator row counts. Statistics Profile is used by the SQL Query Optimizer Team to identify issues with a plan which can cause the plan to perform poorly. For example, it can help identify a poor index choice or poor join order in a plan. Oftentimes, it can help identify the needed solution, such as updating statistics (as in the histograms and other statistical information used during plan generation) or perhaps adding a plan hint. This document describes how to read the statistics profile information from a query plan so that you can also debug plan issues.

A simple example query demonstrates how to retrieve the statistics profile output from a query:

use nwind

set statistics profile on

select * from customers c inner join orders o on c.customerid = o.customerid;

(continue reading...)


UPDATE STATISTICS undocumented options

If you read the Books Online page describing the UPDATE STATISTICS command, you will see that there are some undocumented options.

UPDATE STATISTICS table | view [ { { index | statistics_name } | ( { index |statistics_name } [ ,...n ] ) } ] [    WITH [ [ FULLSCAN ] | SAMPLE number { PERCENT | ROWS } ] | RESAMPLE | <update_stats_stream_option> [ ,...n ] ] [ [ , ] [ ALL | COLUMNS | INDEX ] [ [ , ] NORECOMPUTE ] ] ; <update_stats_stream_option> ::= [ STATS_STREAM = stats_stream ] [ ROWCOUNT = numeric_constant ] [ PAGECOUNT = numeric contant ]

<update_stats_stream_option>

This syntax is for internal use only and is not supported. Microsoft reserves the right to change this syntax at any time.

There is a very good reason why these options are undocumented. They are meant for testing and debugging purposes, and should never ever be used on production systems.

(continue reading...)

Hints for DML queries

Not everyone knows that query level hints (like loop join) will impact the entirety of a DML query plan. This includes foreign key validation and indexed view maintenance.

Let us look at an example with two tables involved in a foreign key constraint.

use tempdb

go

create table department(deptid int primary key clustered, deptname varchar(10))

go

create table employee(empid int primary key clustered, empname varchar(10), deptid int references department(deptid))

go

insert department values(1, 'Optimizer')

go

(continue reading...)


  •   
  • Copyright © 1996-2010 BlogmyQuery - BMQ. All rights reserved.
    iDream theme by Templates Next | Powered by WordPress