Audit Database Log On/Off SQL Server 2012 introduced a few new audit action groups.  Two that I'm interested in using are to audit log-ons/offs at the database level:

SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP
DATABASE_LOGOUT_GROUP

The problem is, I also have SQL Server 2008 R2 instances that I'd like to deploy this on.  Are there alternatives to achieve this using native SQL Server Audit?  Or do I have to do this by other means (eg. SQL Profiler Trace, Logon/logoff triggers)?

SQL Server 2012 introduced database-level logon/logoff audit tracking as part of the "contained databases" feature, which allows SQL Server logins to be defined at the database level.  In prior versions of SQL Server there is no such concept of login or logout at the database level.
From the Microsoft Docs:

SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP
Indicates that a principal successfully logged in to a contained database. Equivalent to the Audit Successful Database Authentication Event Class.
DATABASE_LOGOUT_GROUP
This event is raised when a contained database user logs out of a database. Equivalent to the Audit Database Logout Event Class.

Be aware that unless you are using contained databases, and connections are being made to those databases via contained logins, those audits will not reflect login/logout events.
SQL Server Audit on 2008 R2 includes the ability to audit successful and failed logins at the server level via:
SUCCESSFUL_LOGIN_GROUP
FAILED_LOGIN_GROUP

Alternatively, you can configure "Login Auditing" on the "Security" tab of the "Server Properties" dialog.
In SSMS, right-click the server name in the "Object Explorer" panel.  Then choose "Properties" from the list.  Select the "Security" page, then specify "Both failed and successful logins" from the "Login auditing" section.

Login audit entries are written to the SQL Server Error Log; you can view them with this command:
EXEC sys.xp_readerrorlog 0, 1, 'Login';


pg_upgrade and tablespaces (postgres 9.2 to 9.3) I have a 9.2 installed with tablespaces sitting at:

/var/lib/postgresql/9.2/main

and

/opt/postgres/9.2/second

And unfortunately they are on different filesystems.

This is a Debian package installation of 9.2. The 9.3 will be compiled from source. Since every example I have seen does not show how to deal with a second and a third table space, does anyone have the correct command line for this?  Does pg_upgrade take multiple -d and -D options?

The intent is for the data to stay on the same file systems and to use link-mode.

PostgreSQL creates subdirectories named for the PostgreSQL version within each tablespace.

So AFAIK pg_upgrade doesn't need to be told to take special action for the tablespaces. It will use the same tablespaces as the original database, but won't conflict because of the use of different subdirectories within the tablespace root.

The caveat is that if you have tablespaces inside your 9.2 data directory, this won't work. You shouldn't do that in the first place. Move the tablespaces to a location outside the 9.2 datadir before doing the upgrade.


Can sql server 2000 and 2012 coexist on the same server? I see that 2005/2008/2012 can all coexist but can't find anything on 2000 with 2012.

Yes, I have systems with 2000 and 2012 installed. I have only tried with installing 2000 first though; I haven't been brave enough to try installing 2000 after 2012 was in place.


Unable to wrap a simple CTE in plpgsql loop For a test database, I would like to create a bunch of test users (all with "default" values). There is a table called users, and another called user_properties. For each test user, I need to make corresponding entries in both these tables. I already have a nicely working CTE to do this, but I am unable to wrap this CTE in a loop.

CREATE OR REPLACE FUNCTION ins()
returns void as
$BODY$
BEGIN
--RETURN record;

  FOR Loopid  IN 0..10 LOOP

with
    user as (
    insert into user
    values(default)
    returning id
    )
    ,user_property as (
    insert into user_property (property_of)
    select id from user
    returning id
    )
select id from user_property;

END LOOP;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;

It creates the function, but on running it complains

ERROR:  query has no destination for result data
HINT:  If you want to discard the results of a SELECT, use PERFORM instead.

But it doesn't seem possible to replace the last select of the CTE with a perform instead - that's a syntax error. So how can I do this?

The table structures can be assumed to be very simple since most columns will take the default values. Table user has a single column id, and user_properties has two columns id and property_of - which is a foreign key to col id in the user table. Both the id columns are auto generated sequences.

The few questions (example) which have very similar titles have their answer that a loop/cte isn't really necessary, so they don't help me.

First: user is a reserved keyword, you need to use double quotes if you want to use it as a table name: "user" But I strongly suggest you find a different name.

To answer your immediate question: just remove the final select from the CTE - that's the one cause the error (as you need to store the result of that select somewhere).

CREATE OR REPLACE FUNCTION ins()
returns void as
$BODY$
declare
   loopid integer;
BEGIN
  FOR Loopid  IN 0..10 LOOP
    with new_user as (
      insert into "user"
      values(default)
      returning id
    )
    insert into user_property (property_of)
    select id
    from new_user;
  END LOOP;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;

Note that the language name is an identifier, you should not put it in single quotes.

However, you don't really need a function for that:

with new_users as as (
  insert into "user" (id)
  select i
  from generate_series(1,10) --<< this is the number of rows.
  returning id
)
insert into user_property (property_of)
select id
from new_users;


Estimate Log File Size Is there a way to estimate log file size? I have a scenario where there is a possibility of 40-45K rows per day.

For the above scenario,

What should I consider to estimate log file size?
How much initial size it will be?
On what basis, the log file growth will be there?

Finally, any approximate size for records 40-45K per day?

I think you'll find a good starting point here - http://blog.sqlxdetails.com/set-transaction-log-initial-size/

To summarize from the link:

Set your transaction log large enough so it will not grow during normal operation.  All update activity stops in the database when the logs grows, so you want to minimize log growths.
For in initial log size, you could roughly estimate the log size needed to rebuild your largest table in db (roughly, that is the size of that table) and set the initial log size to that value. That would be the “fit largest transaction” method. Imprecise, but is very simple.
Log file growth should be changed from a percentage (as is the default) to a fixed MB size and monitored.
The link references the usage of BULK RECOVERY model to limit the size of the transaction log due to 'minimally logged' transactions like BULK INSERT and INDEX REBUILDS.  You can only switch to BULK RECOVERY from FULL RECOVERY, but you should be aware that you cannot restore to a point of time that encompasses a 'minimally logged' transaction because individual log (insert/update/delete) records are not generated during that process.  While the actual log file will not be large for a 'minimally logged' transaction, the log backup will still be large due to backing up the changed data extents that were affected during the 'minimally logged' transaction.
In FULL RECOVERY, the transaction log will continue to grow until a log backup occurs (assuming there aren't any open transactions left)
In SIMPLE RECOVERY, the transaction log is 'basically' available for reuse after a transaction commits work.
Generally speaking, shrinking the log is discouraged.  At some point, there was activity that caused it to grow to that size (index rebuild, etc.).  There may be situations where an out-of-the-normal issue occurred and caused the transaction to grow large than normally needed and in those cases you may need to shrink the log down to a more realistic size


There are other ways to do this but the simplest is with a subquery:

select assetid
     , data_received_on
     , (select min(last_contacted_on)
        from customer_contacted_logs c
        where c.assetid=a.assetid and last_contacted_on>=data_received_on)
       last_contacted_on
from asset_data_historical a;

ASSETID | DATA_RECEIVED_ON  | LAST_CONTACTED_ON
------: | :---------------- | :----------------
  17085 | 23-AUG-2017 22:50 | 28-SEP-2017 21:57
  17085 | 25-AUG-2017 20:54 | 28-SEP-2017 21:57
  17085 | 25-AUG-2017 21:08 | 28-SEP-2017 21:57
  17085 | 31-AUG-2017 18:49 | 28-SEP-2017 21:57
  17085 | 29-SEP-2017 18:51 | 16-OCT-2017 18:51

If you need the ticket ID as well it becomes a more interesting problem — and the idiomatic Oracle way is with KEEP…FIRST:

select assetid,data_received_on,ticketid,last_contacted_on
from (select assetid
           , data_received_on
           , (select min(ticketid) keep (dense_rank first order by last_contacted_on)
              from customer_contacted_logs c
              where c.assetid=a.assetid and last_contacted_on>=data_received_on)
             ticketid
      from asset_data_historical a)
     left outer join customer_contacted_logs using(ticketid,assetid);

ASSETID | DATA_RECEIVED_ON  | TICKETID | LAST_CONTACTED_ON
------: | :---------------- | -------: | :----------------
  17085 | 31-AUG-2017 18:49 |  1785487 | 28-SEP-2017 21:57
  17085 | 25-AUG-2017 21:08 |  1785487 | 28-SEP-2017 21:57
  17085 | 25-AUG-2017 20:54 |  1785487 | 28-SEP-2017 21:57
  17085 | 23-AUG-2017 22:50 |  1785487 | 28-SEP-2017 21:57
  17085 | 29-SEP-2017 18:51 |  1785491 | 16-OCT-2017 18:51

db<>fiddle here


disable warning from `drop table if exists` When I DROP a table with the IF EXISTS clause (in PostgreSQL 11.x), it issues a warning as follows:

=> DROP TABLE IF EXISTS tt;

NOTICE:  table "tt" does not exist, skipping

Sometimes, I do expect that certain tables to not exist, and don't want to see many warnings like these in the output of a batch processing function/script.

Is there a way to disable this type of warning altogether?

The "warning" is in fact a "notice". You can disable the display of those notices trough the property client_min_messages  which controls the minimum level of messages returned to the client.

To only display warnings, but not notices use:

set client_min_messages = warning;

Note that this will disable all notices, including those sent through raise notice from e.g. a PL/pgSQL function.


A table that consists of multiple FK or creating separate tables for each FK I have tables called 'books', 'category', 'subcategory', and 'booksCategory'. Each book can have its own category and subcategories.
For example, a Sci-Fi book can belong in the 'category' table under sci-fi, but it also may be under the subcategory under 'Aliens'.

Table books -  a list of books
Table Category - a list of categories. e.g. Sci-Fi
Table Subcategory - a list of subcategories. e.g. Aliens
Table booksCategory - a list of books under categories and subcategories

I wasn't sure of the most optimal way of creating booksCategory.
booksCategory will have a FK referencing the ids of both Category and Subcategory tables.

CREATE TABLE booksCategory(
  booksCategory_id INT NOT NULL AUTO_INCREMENT primary key,
  category_id INT references category (category_id),
  subcategory_id INT references subcategory (subcategory_id)
)

so the results would be something like this

booksCategory_id | category_id | subcategory_id | books_id
----------------------------------------------------------
     1                 3            4                 1     // 3 = SCI-FI, 4 = Aliens
     2                 2            NULL              1    // 2 = Adventure, Null = does not belong to any subcategory

The problem occurs when a book falls under multiple categories. In the example above, book_id with 1 can be both SCI-FI and Adventure. Is this the best method because then I will have a NULL in subcategory?

My other option I can think of is to have separate tables for booksCategory, booksSubcategory, and if I have more subcategories, bookSubcategories2.  So basically a separate list of books that fall under Category, another list of books that fall under subcategory, and if I have more branching subcategories, a list of books that fall under that subcategory. I'm not really too fond of this method because it seems less organized/scattered.

Because you desire a categorization hierarchy of indeterminate depth, you will have to use a single category table structured along these lines:

CREATE TABLE Category (
    Category_ID           INT NOT NULL AUTO_INCREMENT primary key,
    Parent_Category_ID    INT NOT NULL REFERENCES Category(Category_ID),
    Description           varchar(255) not null
)

This type of structure is not as well supported by MySQL as some other RDBMS's, but these issues can all be worked through.

You will also need a revised BookCategory mapping table such as this:

CREATE TABLE BookCategory (
    Book_ID        INT NOT NULL references Book(Book_ID),
    Category_ID    INT NOT NULL references Category(Category_ID)
)

My apologies if the Foreign Key syntax above is not quite correct for MySQL; I usually work with SQL Server.

Update
With this INSERT:

insert Category(Parent_Category_ID,Description)
values (1,'Mystery')
      ,(2,'SciFi')
      ,(2,'Aliens')
      ,(3,'Humanoid')
      ,(3,'Reptilian')
      ,(3,'Other')
;

the following SELECT:

select
    root.Category_ID,
    cast(
      coalesce(grandparent.Description + '-',' ') +
      coalesce(parent.Description + '-',' ') +
            root.Description
    as varchar(255)) as Description
from Category                           root
left join Category                      parent
   on parent.Category_ID            = root.Parent_Category_ID
  and root.Parent_Category_ID      <> root.Category_ID
left join Category                      grandparent
   on grandparent.Category_ID       = parent.Parent_Category_ID
  and parent.Parent_Category_ID    <> parent.Category_ID

yields this:

Category_ID Description
-----------
1             Mystery
2             SciFi
3            SciFi-Aliens
4           SciFi-Aliens-Humanoid
5           SciFi-Aliens-Reptilian
6           SciFi-Aliens-Other

When Common Table Expressions are supported (not yet in MySQL I understand) the following will provide automatic nesting of the hierarchy levels:

with data as (
    select
        Category_ID,
        cast(Description as varchar(255)) as Description
    from Category
    where Parent_Category_ID = Category_ID

    union all

    select
        child.Category_ID,
        cast(parent.Description + '-' + child.Description as varchar(255)) as Description
    from data       parent
    join Category   child
       on child.Parent_Category_ID  = parent.Category_ID
    and child.Parent_Category_ID <> child.Category_ID
)
select
     Category_ID,
     Description
from data


Data Collection reports fail because login is from an untrusted domain I've set up a data warehouse and Data Collection according to BO. Reports are generated properly when running them in Management Studio running on the server but fail when they are run from Management Studio on a different machine (which is using VPN to connect to the network the SQL Server is on). The error message is:

  Failed to connect to server x.x.x.x. --> Login failed. The login is
  from an untrusted domain and cannot be used with Windows
  authentication.

The server uses mixed mode authentication. Data Collection is configured to use an SQL Login and the login that tries to generate the report is an SQL Login as well. They've got nothing to do with domains. Why do I get this error?

I have confirmed this is an issue with how the reports get executed on the server -- I reproduced the issue exactly by using two virtual machines, neither of which was in a domain.

Moreover, I created a test user (member of the Users built-in group and nothing more) on the SQL box, ran Management Studio locally as that user, logged in with sa, and attempted to run the report. As I expected, it failed, and returned the error Failed to connect to server MDWTestSQL. --> Login failed for user 'MDWTESTSQL\TestUser'.

This is clear indication that the reports are implemented to only use Windows authentication for some portion of it. Therefore, sufficient SQL-level permissions must be granted to the Windows principal running Management Studio, even if you authenticate to the server using a SQL login.

There are two workarounds:

Use pass-through authentication: create a matching (user name and password) local login on the workstation, and impersonate the local login to run Management Studio remotely. (I tested that this works.)
Use pass-through authentication by creating new matching local accounts on both boxes, and set up SQL permissions for the new account.

As I mentioned in chat, this problem is "fixed" in the 2012 Management Studio -- the reports in the menu are hidden if the Windows account doesn't have permissions to run the reports.


Multi Tenant Identity Is there any way to declare identity partitioned by tenant id?

For example I want to have this structure:

tenant_id | record_id (auto increment)
---------------------
      1   |   1
      1   |   2
      1   |   3
      1   |   4
      2   |   1
      2   |   2

No, there is no means of partitioning either an IDENTITY or SEQUENCE. In order to do this you would have to create a table to manage TenantID and RecordID for this table. Then you would need a function that accepts TenantID and will return RecordID. This means that you need to insert via a stored procedure that can grab that value and then do the INSERT, or maybe you can get away with an INSTEAD OF trigger, but I am not sure about that given that both TenantID and RecordID need to be part of the PK.

And if you want this pattern to apply to all tables then you would need to duplicate that custom sequence per each table. This is ok for maybe a few tables, but not more than that.

There are rare occasions when this type of behavior is desirable, but the current reason for requesting this seems a bit suspect (noted in a comment on the question):

  for capacity...1 mil record with id partitioned by 50 tenants will have highest id around 20k, while not partitioned will have highest id of 1 mil.

This is not (or at least should not be) a valid concern. The INT datatype has a capacity of just under 4.3 billion values. If you start the IDENTITY (i.e. the "seed") at 1, then you get half that range since this is a signed INT and not an unsigned INT. If you want all 4,294,967,296 values, then use IDENTITY(-2147483648, 1).


Mysql database in a symlink-ed directory gives ERROR 1005 (errorno: 13) I've the need to put my database outside the default datadir of mysql.

To achieve that I followed the symlink guidance in the mysql documentation.

However when I use the db if I try to create a table I'm greeted with:

  ERROR 1005 (HY000): Can't create table 'test' (errno: 13)

In my search for solutions I already read Can I use symlink or data directory for ARCHIVE MySQL tables? and this answer which didn't apply for my context.

At the moment my data dir looks like this with ls -Ll

server:/var/lib/mysql# ls -Ll
total 28800
-rw-r--r-- 1 root  root         0 Jan 26 00:37 debian-5.5.flag
-rw-rw---- 1 mysql mysql    25088 Feb  8  2005 ib_arch_log_0000000000
-rw-rw---- 1 mysql mysql 18874368 May 31 15:21 ibdata1
-rw-rw---- 1 mysql mysql  5242880 May 31 15:21 ib_logfile0
-rw-rw---- 1 mysql mysql  5242880 Feb  8  2005 ib_logfile1
drwx------ 2 mysql mysql     4096 Jan 26 00:37 mysql
drwxr-xr-x 1 root  root         0 May 29 00:01 so
drwx------ 2 mysql mysql     4096 May 28 22:48 so2
drwxr-xr-x 2 mysql mysql      4096 May 31 14:34 symlink
drwxr-xr-x 2 mysql mysql     4096 Apr 20  2013 test

The two symlinks are created with the command

ln -s /mnt/nas/se/data/mysql/ so

I chown the symlinks to mysql:mysql as well as in the directory it self. For the symlink directory that worked (that is on an ext3 fs) but for the so one (that is on a nas over CIFS/SMB) I'm still out of luck.

I managed to enable NFS (ver 3) on the NAS (running BusyBox v1.1.1) and mounted that as well. I synced the UID for the mysql account on the server and the client. Unfortunately the result is the same for ls -Ll

drwxrwxr-x 2 root  rene      4096 May 31 16:51 sonfs

and for ls -l

lrwxrwxrwx 1 root  root         8 Jun  1 00:26 sonfs -> /mnt/nfs

I did su mysql and tried to cp a file to those symlinked directories and that worked which made me assume on the OS level the permissions are OK.

Within mysql I granted root all permissions to the so and the symlink database.

I'm not sure if I the problem I face is something I misconfigured at the Debian OS level or within mysql itself.

Can someone point out to me what diagnosis I need to do and which steps need to be taken to be able to create tables that get stored in those symlinked directories?

I managed to solve this, basically by the advice given by Mat.

The first valueable tip was to use ls -Ll to show information  for the file the link references rather than for the link itself.

It is clear that the symlink should show the user (and group) mysql is using.

When mouting (in this case the cifs type) I first used

mount -t cifs //nas01/myshare /mnt/nas -o user=nasuser

where I should have used

mount -t cifs //nas01/myshare /mnt/nas -o user=nasuser,uid=mysql,gid=mysql

This resolved the errorno: 13 because ls -Ll now showed mysql as owner and group for the symlinked directory.


Most efficient way to update a column where Id exists in another table I have several tables which correspond to fiscal_years (2017, 2018, 2019, 2020, etc). Each of these tables contain a record_id and an owner_id - with the record_id being unique within a given year. Each of the fiscal_year tables contain 6-8 million rows, and the owners table contains 1 million rows. Each of these fiscal_year tables contain a process_date field meant to represent the date when the owner_id was added/found/moved.
I have created a dynamic sql function that can retrieve the owner_ids unique among all of these tables and put them into an owner table - this part works fine and takes appx. 5 min to complete.
However, I am struggling to update the source table's (fiscal_years) process_date field if its owner_id exists in the owner table.
I have tried this:
update fiscal_year y
set process_date = now()
where exists (select owner_id from owners o where o.owner_id = y.owner_id)
and process_date is null;

But predictably, this query ran for 2 hours before I stopped it.
I also tried to use an etl tool to update the fiscal_year table using owners.owner_id as a key. But this method never finished either - presumably because there are many owner_ids that don't exist in each fiscal_year table.
How can I update the fiscal_years table when the owner_id is present in the owners table?
Any help on this would be greatly appreciated.

I believe by doing an EXISTS on a subquery you may be using a sub-optimal way to accomplish this. Why not emulate an INNER JOIN directly like so:
update fiscal_year y
set y.process_date = now()
from owners o
where y.owner_id = o.owner_id
and y.process_date is null;

Having an index on (owner_id, process_date) for the fiscal_year table should help ensure the most optimal operations occur in this case too.
Your tables aren't big by any means, this process should be able to execute in minutes if not seconds, as long as everything is architected properly. I'd be curious what the above yields you (with the recommended index).


Setting query to run as a stored procedure and email the results I have the following query:

select COUNT(CASE WHEN New_accounttype = 1 THEN New_AccountType ELSE NULL END) as 'New Connections'
, COUNT(CASE WHEN New_accounttype = 2 THEN New_AccountType ELSE NULL END) as 'Domestic Metered'
, COUNT(CASE WHEN New_accounttype = 3 THEN New_AccountType ELSE NULL END) as 'Commercial Metered Low'
, COUNT(CASE WHEN New_accounttype = 4 THEN New_AccountType ELSE NULL END) as 'Commerical Metered High'
, COUNT(CASE WHEN New_accounttype = 5 THEN New_AccountType ELSE NULL END) as 'Domestic Keypad'
, COUNT(CASE WHEN New_accounttype = 6 THEN New_AccountType ELSE NULL END) as 'Generator'
, COUNT(CASE WHEN New_accounttype = 7 THEN New_AccountType ELSE NULL END) as 'Commercial Keypad'
, COUNT(*) as 'Total Live'
from AccountExtensionBase as a
INNER JOIN CustomerAddressBase as b ON a.AccountId = b.ParentId
where New_AccountStage = 7
and AddressTypeCode is null

I want to set this up to automatically email the values on a daily basis. How can I convert this into a stored procedure and use. I am using SQL server 2008

Configure Database Mail
Create a sql agent job to run below t-sql (you dont need a stored procedure for what you are asking, but below code can be easily converted into a stored procedure --> I will leave that up to you - hint: just look for Create procedure as ..)

---- first check if there are any rows returned ... as we dont want to send empty email ....
if exists (
        select 1
        from AccountExtensionBase as a
        inner join CustomerAddressBase as b on a.AccountId = b.ParentId
        where New_AccountStage = 7
            and AddressTypeCode is null
        )
begin
    if object_id('#final_results') is not null
        drop table dbo.#final_results;

    select COUNT(case
                when New_accounttype = 1
                    then New_AccountType
                else null
                end) as 'New Connections'
        ,COUNT(case
                when New_accounttype = 2
                    then New_AccountType
                else null
                end) as 'Domestic Metered'
        ,COUNT(case
                when New_accounttype = 3
                    then New_AccountType
                else null
                end) as 'Commercial Metered Low'
        ,COUNT(case
                when New_accounttype = 4
                    then New_AccountType
                else null
                end) as 'Commerical Metered High'
        ,COUNT(case
                when New_accounttype = 5
                    then New_AccountType
                else null
                end) as 'Domestic Keypad'
        ,COUNT(case
                when New_accounttype = 6
                    then New_AccountType
                else null
                end) as 'Generator'
        ,COUNT(case
                when New_accounttype = 7
                    then New_AccountType
                else null
                end) as 'Commercial Keypad'
        ,COUNT(*) as 'Total Live'
    into dbo.#final_results -----<--- put results into temp table for easy processing ...........
    from AccountExtensionBase as a
    inner join CustomerAddressBase as b on a.AccountId = b.ParentId
    where New_AccountStage = 7
        and AddressTypeCode is null

    --- prepare for sending email ... since we have something to send ....
    declare @tableHTML nvarchar(MAX)
        ,@tableHTML2 nvarchar(MAX)
        ,@tableConnErr nvarchar(MAX)
        ,@tableDBConnErr nvarchar(MAX);
    declare @Mailsubject nvarchar(255)
        ,@mail_recipients nvarchar(255)

    set @mail_recipients = 'dbagroup@somecompany.com' --<-- CHANGE HERE
    set @Mailsubject = 'Setting query to run as a stored procedure and email the results' --<-- CHANGE HERE
    set @tableHTML = N'
Below is the report as per [http://dba.stackexchange.com/q/104030/8783] :
'
    set @tableHTML = @tableHTML + N'
' + N'' + N'
New Connections	' + N'Domestic Metered	' + N'Commercial Metered Low	' + N'Commercial Metered High	' + N'Domestic Keypad	' + N'Generator	' + N'Commercial Keypad	' + N'Total Live	' + N'
' + ISNULL(CAST((
                    select td = [New Connections],''
                        ,td = [Domestic Metered],''
                        ,td = [Commercial Metered Low],''
                        ,td = [Commercial Metered High],''
                        ,td = [Domestic Keypad] ,''
                        ,td = [Generator],''
                        ,td = [Commercial Keypad],''
                        ,td = [Total Live],''
                    from (
                        select *
                        from dbo.#final_results
                        ) A
                    order by 1 asc
                    for xml PATH('tr')
                        ,TYPE
                    ) as nvarchar(MAX)), '')
    set @tableHTML = @tableHTML + N'' + N'
';
    set @tableHTML = @tableHTML + N'
Report By: @TheRockStarDBA ;Generated On: ' + convert(varchar, getdate()) + '
'

    --print @tableHTML
    exec msdb.dbo.sp_send_dbmail    @profile_name = 'YOUR MAIL PROFILE'----< CHANGE HERE !
                                   ,@recipients = @mail_recipients
                                    ,@subject = @Mailsubject
                                    ,@body = @tableHTML
                                    ,@body_format = 'HTML';
end

Add a schedule to the above job.


GZip existing varbinary(max) column in Sql Server 2008+ I have an existing legacy table that is ~180GB in size due to a client application storing PDF files as varbinary.  I'd like to be able to compress that column for all rows using GZIP to help save space while I create a new solution (I was hoping there was a way to do it in SQL and not have to write client code for this).  I see that the COMPRESS method is available for Sql Server 2016 but I need a solution that will work with 2008.  Any ideas would be appreciated.

You can use .NETs GZipStream Class with SQLCLR to do the compression since you are on SQL Server 2008.

Solomon Rutzky has a free SQLCLR version (Util_GZip and Util_GUnzip functions) that you can test and use.

See : Alternative way to compress NVARCHAR(MAX)?


Using Table as a Variable for a Stored Procedure I am working on adding some stored procedures to my database and am stuck on how to do the SQL for what I want to do.

For the procedure I am working on right now, it needs to have two parameters, a table name, and a column name.

The procedure needs to check if that column exists within the table, and if it does, return all of the data within that column.

Now I have been going through both this site, and Google trying to figure out how I'd go about programming this, but needless to say I am utterly confused and not sure what to do.

If I were to pseudocode the procedure it would look like so:

CREATE PROCEDURE GetTableColumn (
    @ColName VARCHAR(25),
    @TblName TABLE READONLY
) AS
BEGIN
    IF (EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TblName AND COLUMN_NAME = @ColName))
        SELECT @ColName
        FROM @TblName
END

I have tried the above code, and it doesn't work, basically I have no idea how to set up a variable that can be a table.

You would need to use dynamic sql.  You final select line should be:

declare @sql varchar(max)
set @sql = 'select ' + @colName + ' from ' + @TblName
exec(@sql)


mysql cluster with innodb utilizing high memory I have implemented MySQL cluster with two data, sql and management node respectively.
On one of the sql node I have ndb as well innodb engine for the huge tables which I can not able to convert into ndb-cluster engine. But after few days the sql node with innodb engine started eating large amount of physical memory it is increasing rapidly. Show processlist is not showing any running processes. I am clueless now where is MySQL consuming this RAM. I am worried if my server goes out of memory. One week ago I have uploaded 30GB of data but right now it is not used by any of the application. I have observed increase In ram usage goes 20% to 75% within a week after that data upload.

[mysqld]

ndbcluster
ndb-connectstring=192.168.0.105,192.168.0.109   # IP address of Management
ndb-cluster-connection-pool=2

[mysql_cluster]

ndb-connectstring=192.168.0.105,192.168.0.109   # IP address of Management Node

[mysqld]

ndbcluster
ndb-connectstring=192.168.0.105,192.168.0.109       # IP address for server management node
default_storage_engine=ndbcluster     # Define default Storage Engine used by MySQL

[mysql_cluster]

ndb-connectstring=192.168.0.105,192.168.0.109       # IP address for server management node
 #[mysqld_safe]
 #log-error=/var/log/mysqld.log
 #pid-file=/var/run/mysqld/mysqld.pid

 #log_bin=/var/log/mysql/mysql-bin.log
 #binlog_do_db=radius

 #port = 8881
 #innodb_force_recovery=6
 #innodb_purge_threads=0

The main memory usage in an SQL node is for InnoDB's buffer pool.  What is the value of innodb_buffer_pool_size?  (Use SHOW VARIABLES LIKE 'innodb_buffer_pool_size';)  How much RAM do you have?  Normally, it should be set to about 70% of RAM.  The memory usage will grow until it hits the setting.  After that it will fluctuate slightly as other allocations come and go.


Optimizing MySQL for Read-Only? We have 14 GB worth of CSV's which total 138 million rows.  I imported this into a MySQL table first with InnoDB, and then tried again with MyISAM.  In both cases, a simple SELECT on primary key (which is just an autoincremented int) took 6-7 seconds, though MyISAM sometimes was a little faster at 5-6 seconds.

We only need to write the data once, and I've been using mysqlimport.  With that in mind, how can I improve the query speed?

...it's worth nothing that we have 2 gigs of RAM and everything is one table (and it has to stay that way due to the nature of the queries).  Is that the best performance I can expect given the hardware?  Or is there something else I should try, like compression?  Or really, I need a lot more RAM?

MyISAM ROW FORMAT
If the table is a lot of VARCHAR fields, I would love to suggest the following
For every MyISAM table you load, do this beforehand
ALTER TABLE mytable ROW_FORMAT=Fixed;

This will increase query performance up to 15-30% without changing anything else. The side effect of doing this is having bigger tables and bigger indexes.
I have earlier posts on this:

May 10, 2011 : What is the performance impact of using CHAR vs VARCHAR on a fixed-size field? (TRADEOFF #2)
Aug 12, 2011 : Which DBMS is good for super-fast reads and a simple data structure? (Par 3)
Sep 20, 2011 : Best of MyISAM and InnoDB
Jan 03, 2012 : Optimized my.cnf for high-end and busy server (Under the heading Replication)

INCREASED RAM
If you have many indexes, you may want to increase RAM on the DB Server and then attempt to preload all MyISAM index pages into the key buffer.
First compute how big a key buffer you would like (in GigaBytes)
SELECT CEILIING(SUM(index_length)/POWER(1024,3)) SuggestedKeyBufferSize
FROM information_schema.tables
WHERE engine='MyISAM' AND table_schema NOT
IN ('information_schema','performance_schema','mysql');

If SuggestedKeyBufferSize can be set up for the DB Server, then do the following (example 4G)
STEP 01) Set this in /etc/my.cnf
[mysqld]
key_buffer_size=4G

STEP 02) preload all MyISAM indexes
See my post on how to do this : MySQL warm procedure


SSIS: Passing variable from child package to parent package I have an SSIS project consisting of many packages, including a Main_Flow package which executes the other packages in the desired order.

The first package to be called from Main_Flow is Extract_Archive with a ForEach container that looks for zipped files. If one or more zip file exists, it extracts them in a location and marks a variable ArchiveFileFound to TRUE.

Now back in the Main-Flow package, I would like the next step to be executed ONLY if the ArchiveFileFound from the Extract_Archive is set to TRUE. However that variable only exists in Extract_Archive. I've been looking for information on how to pass variables from one SSIS package to another but I cannot find a good practical and recommended example. Any thoughts?

This can be done using a Script Task in the child package to update a variable from the parent package based off the value of a variable in the child package, assuming that none of the variables that are accessed have the same name in both the parent and child packages.  While the variables from the parent package won't be displayed in the ReadWriteVariables field on the Script Task, if you enter them here then they can be accessed.  Updating the variables from the parent package would follow the same syntax as doing so for variables in the same package, and the C# example details this.

Dts.Variables["User::ParentVariable"].Value = Dts.Variables["User::ChildVariable"].Value;


Partial SQL Server database synchronization I have 2 SQL Server databases and they have somewhat different schemas.
But there is a table with person attributes.
Name, address, city, email, etc...
And an account key.  on both tables.
Can SQL Server be set up to sync just these attributes.  On this table?
And not the entire database?
And, is there a way to log when data is overwritten from db1 to db2.  or db2 to db1?

If you want a real-time sync you can use replication and select only that table as an article. If you don't need real-time sync and let's say you only need to sync the table once a day, you can create a job scheduled as you need and this job will execute an SSIS package that will move the data from one table to the other.


Mysql Server always throwing unknown error Sorry for the bad formatting since its the first time

When i try to delete a row which has a foreign constraint, i'm expecting the Mysql server to give me the constraint key and the table to which it's related to but all i get is only this message "Unknown error 1451".

mysql server version : 5.7.26 (linux)

CREATE TABLE `detailmenu` (
  `detailmenu_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `menu_id` int(10) unsigned NOT NULL DEFAULT '0',
  `detailsubprg_id` varchar(20) DEFAULT NULL,
  `description` varchar(100) DEFAULT NULL,
  `link` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`detailsubprg_int_id`),
  KEY `FK_detailsubprgmtb_1` (`subprg_int_id`),
  CONSTRAINT `FK_detailmenu_1` FOREIGN KEY (`menu_id`) REFERENCES `menu` (`menu_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=81 DEFAULT CHARSET=latin1

When i try to delete a row from this table which has a constraint i expect the Mysql server to atleast give me the constraint key which is 'FK_detailmenu_1' or the child table reference but all i get is "Unknown error 1451" without any additional information. Is there any configuration issue? This issue does not come in windows machine.

I found the answer through this post MySQL 5.7 No Longer Returns Meaningful Error Messages.
On analyzing the mysql log in the path /var/log/mysqld.log. the error was decribed as [ERROR] Can't find error-message file '/usr/share/mysql/errmsg.sys'. Check error-message file location and 'lc-messages-dir' configuration directive.


Using AD groups vs roles in SQL Server What is best practice for sql server security? Using AD groups that are given rights on schema objects or should I add the AD groups to a database role which is given rights on schema objects ? If the later one is best practice, what is really the benefit of using database roles ?

Generally speaking, adding AD groups to database roles and granting the permissions to the roles gives you some separation of purpose. In that case you would use them as:

AD Group = a set of logins that share the same database access needs.
Role = a set of permissions that define a set of rights to be shared by logins.

The advantage is that the role can have more that one AD group added to it.  Likewise, if the AD Group is removed, the permission sets remain in place.  That way the original AD Group, a new AD Group or groups, could be added without needing to redefine the rights.

And, of course, more than one AD Group can use a role and an AD Group could use several roles.  It all depends on how you want to use and reuse permissions.

If the rights are granted directly to an AD Group, then dropping the group also drops the rights that the group had.  Which would mean recreating those rights if you need them elsewhere.


There is no copy database wizard I'm trying to copy data from one table to another table in a different database. After right clicking I have got an option Tasks, under it there are only the two options:

Export Data...
Import Data...

... but no option for Copy Data....

How can I solve this issue?

Thanks in advance.

You should use Export data and choose data source as SQL server native client (Version) as source and select your source SQL server instance and You should select the same for the destination with your Destination server name. On the next window select table, you want to copy and Run the wizard. This will copy all the data from your source table to destination table.

Also the copy database wizard is used to create a copy of your database not for copying data from table to table. Also if you can't see this option most probably you are using express edition here

  The Copy Database Wizard is not available in the Express edition.


Cannot retrieve resultset data I created a folder in "MySQL Server 5.7\Data" And copied frm,myd,myi files there from another database folder ( mysql 5.1 )
However the data is encrpted and there is a program that sends queries, decrypts and displays the result. ( written in Delphi 7, db component Zeoslib )
Whenever i try to search a big table, i get "Cannot retrieve resultset data" error. I am a total noob and dont know how to solve this.Any suggestions ?

edit: I am not particularly concerned about efficiency and i didnt write the decryptor program, i just downloaded it along with mysql 5.1 data files.Because the data is encrypted i am stuck with using this program for queries.I know several people managed to get this to work so it should be possible.I want to change mysql settings such that the program wont complain when searching big tables ( 50 million entries )

The main issue you are seeing is based on the nature of the encryption.  The Delphi application appears to not be using any native MySQL encryption/decryption mechanisms for the data.  This will result in certain disadvantages when querying for data.  Although the decrypted data is visible to the application while in use, native to MySQL, it has no what of indexing any encrypted data fields in any optimized way as the values would be unknown.  This would result in long query times as most likely all queries would be scanning the whole table to deliver any results.

If you can, use the Delphi application, or similarly developed application to export the data unencrypted into a file, easily parsed by MySQL (csv, SQL statements, etc.) that can be imported into your MySQL 5.7 database instance.

This will allow you to create indexes on the unencrypted data to run your queries as necessary.

If you need to reencrypt the data, you can use the native functions of MySQL:

https://dev.mysql.com/doc/refman/5.7/en/encryption-functions.html

However, you will still be unable to index those columns in any workable way to query on those columns.  I would recommend if possible that any queries be done on any columns that can remain unencrypted and therefore be indexed accordingly to optimize the execution plans.


timelines and history files PostgreSQL 9.1 What is the purpose of timelines, history files, and the setting 'recovery_target_timeline' in recovery.conf?

The vague understanding I have from the PostgreSQL 9.1 documentation is when the slave completes recovery it switches to a new timeline to prevent overwriting of the WALs of a previous timeline.

I am not clear how this is used in a recovery scenario, and the purpose of the .history file and the 'recovery_target_timeline' setting to 'latest'.

When the slave is promoted to a new master it creates a new timeline to avoid WAL names overlapping. The .history file contains information about database timeline branches. Recovery process uses this information to determine the timeline it is working with. By default it uses the same timeline that was when base backup was made. So, for example, if you promote one of your slaves to a new master other slaves will not be able to continue recovery from the new master as they use previous timeline.

To make them able to use current timeline, in easy words to switch them to the new master, you need them to know about the new history. Just delete everything in the pg_xlog directory on slaves and copy the history file from the new master. And then set recovery_target_timeline to 'latest' to force the database using the latest found timeline.

Note that the slave pretending to be a new master must be the most catch up one. Otherwise it might lead to data corruption on other slaves after you switch them to the new master.


Row_Num Partition in WHERE clause I've successfully added a ROW_NUMBER() function to my SELECT statement, but I get an "invalid column name" error when I try to reference the column I generated with the ROW_NUMBER() function in my WHERE clause by referencing the [RANK] alias.

Query

SELECT DISTINCT
      ROW_NUMBER () OVER (PARTITION BY T0.DocNum ORDER BY T0.DocNum) [RANK],
      T0.DocNum,
      T3.[PickIdNo],
      T0.[NumAtCard],
      T0.[U_STS_SalesProg],
      T0.[DocDate],
      T0.[DocDueDate],
      T0.[TaxDate],
      T0.[CardCode],
      T0.[CardName],
      T6.[SlpName],
      T8.[firstName][CSS],
      T4.[U_BOTYPE],
      T1.LineTotal BackOrderTotal,
      T2. OrderTotal
FROM ORDR T0
INNER JOIN
(SELECT
      T0.DocEntry
      , ROUND(SUM(T0.[OpenQty]*T0.[Price]),2) LineTotal
FROM RDR1 T0 where  LineStatus ='O'
GROUP BY T0.DocEntry) T1 ON T0.DocEntry = T1.DocEntry
INNER JOIN
(SELECT
      T0.DocEntry
      , ROUND(SUM(T0.[Quantity]*T0.[Price]),2) OrderTotal
FROM RDR1 T0
GROUP BY T0.DocEntry) T2 ON T0.DocEntry = T2.DocEntry

INNER JOIN RDR1 T3 ON T0.[DocEntry] = T3.[DocEntry]
INNER JOIN OCRD T4 ON T0.[CardCode] = T4.[CardCode]
INNER JOIN OTER T5 ON T4.[Territory] = T5.[territryID]
INNER JOIN OSLP T6 ON T4.[SlpCode] = T6.[SlpCode]
INNER JOIN OITM T7 ON T3.[ItemCode] = T7.[ItemCode]
INNER JOIN OHEM T8 ON T4.[DfTcnician] = T8.[empID]
INNER JOIN OITW T9 ON T7.[ItemCode] = T9.[ItemCode]

WHERE Rank = '1' -- <<== This is where it fails
ORDER BY T8.[firstName], T0.[CardName], t0.DocNum

From Itzik Ben-Gan's Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions (Developer Reference):

Logical Query Processing Order:

FROM
WHERE
GROUP BY
HAVING
SELECT

Evaluate Expressions
Remove Duplicates

ORDER BY
OFFSET-FETCH/TOP

Window Functions operate in steps 5.1. and 6.

Obviously then, you cannot access the results of a Window Function in the WHERE or HAVING clauses. You can always use a CTE or a subquery.

See also:
- Logical Processing Order of the SELECT statement (SQL Server 2014 - 2017)
- Logical Query Processing: What It Is And What It Means to You (Itzik Ben Gavin - ITProToday)


MySQL 5.1 InnoDB Configuration / 24GB RAM - bi-xeon high load i'm running a facebook app which currently has 300 - 600 concurrent users (and growing). To get the hardware ready for growing i changed my i7 / 12gb ram / 2x 80gb intel x25 ssd's (debian 5.0 / mysql 5.0 / 64bit) into a bi-xeon / 24gb ram / 2x 120gb intel 320 ssd (ubuntu 10.10 / mysql 5.1 / 64bit).

now i'm facing the problem that the performance is worse than on the "smaller box". On both servers i've been using nginx / php fcgi to serve the content.

i'm using innodb only, having Reads / Writes about 65% / 35%. Around 800 - 1000 qps but all Queries are simple and never join more than 1 additional table. All indexes are set and no individual query is logged in the slow log (> 2s).
At the moment i have around 400mb of data (around 1gb with indexes) expecting it to double every month.

I'd adore everyone who could give me a hint what to change to make it run smoother.

The old configuration on the i7 box was like this (mixed myisam / innodb), performed pretty good up to 800+ users.

old my.cnf

   key_buffer              = 3000M
   max_allowed_packet      = 128M
   thread_stack            = 192K
   thread_cache_size       = 8
   max_connections        = 400
   table_cache            = 8000
   thread_concurrency     = 16
   query_cache_limit       = 8M
   query_cache_size        = 128M
   wait_timeout            = 10
   interactive_timeout     = 10
   connect_timeout         = 600
   low_priority_updates    = 1
   join_buffer_size        = 8M
   read_buffer_size        = 2M
   sort_buffer_size        = 3M
   myisam_sort_buffer_size = 32M
   read_rnd_buffer_size    = 4M
   innodb_buffer_pool_size = 3G
   innodb_log_buffer_size  = 8M

The new configuration on the bi-xeon box is like this (pure innodb), causing high load with 300+ users. Around 30 mysql processes sitting on the top of the process list.

Disk I/O:

    avg-cpu:  %user   %nice %system %iowait  %steal   %idle
              36.28    0.00    1.60    0.17    0.00   61.95

my.cnf

    key_buffer              = 64M
    max_allowed_packet      = 1M
    thread_stack            = 192K
    thread_cache_size       = 128
    max_connections        = 500
    table_cache            = 512
    #thread_concurrency     = 10
    sort_buffer_size        = 256K
    read_buffer_size        = 256K
    read_rnd_buffer_size    = 256K
    tmp_table_size          = 32M
    max_heap_table_size     = 32M
    query_cache_limit       = 1M
    query_cache_size        = 128M
    query_cache_type        = 1

    innodb_file_per_table = 1
    innodb_data_file_path = ibdata1:1000M:autoextend
    innodb_buffer_pool_size = 16384M
    innodb_additional_mem_pool_size = 8M
    innodb_flush_log_at_trx_commit = 1
    innodb_support_xa = 0
    innodb_lock_wait_timeout = 50
    innodb_flush_method=O_DIRECT
    innodb_log_files_in_group = 2
    innodb_log_file_size = 128M
    innodb_log_buffer_size = 8M
    innodb_thread_concurrency = 12

I have written some posts in the StackExchnage

Tuning MySQL for InnoDB and MyISAM
How to keep InnoDB Diskspace under control
Another Viewpoint on MySQL Diskspace Managament
Viewpoint on InnoDB Optimization
InnoDB Fine Tuning

Please read these for the guidance you need.
Now, for more pressing issues: You mentioned that you have 400MB of data, 1GB with indexes. That kind of scares me that your indexes are 50% bigger than the data. However, since all your data is InnoDB and you are satisfied with the current query performance, your settings are more than adequate, espscially the 16384MB of innodb_buffer_pool_size. That's 16GB. You are all set there. But wait !!! Your innodb_log_file_size is 128M ? Way too small given the 16GB buffer pool. You should resize the ib_logfile files (set the innodb_log_file_size to 2047M).
You might be experiencing load on a per-thread basis. Try setting your connection buffers (join_buffer_size ,sort_buffer_size, read_buffer_size, read_rnd_buffer_size)
From Me : Why does MySQL say I'm out of memory?
From @DTest : How do you calculate mysql max_connections variable?
Give it a Try !!!


Concatenating only desirable values with LISTAGG In the Oracle DB there is a table with data "YEARS", see a table/image below
+-----+-----------+------------------+
| FID |    I0     |       BJA        |
+-----+-----------+------------------+
|   1 |     0     |       1949       |
|   2 |     0     |       1996       |
|   3 |     0     |       1970       |
|   4 |     1     |       1871       |
|   4 |     0     |       1975       |
|   5 |     0     |       1967       |
|   6 |     0     |       1968       |
|   7 |     0     |       1926       |
|   7 |     1     |       2009       |
|   7 |     2     |       2012       |
|   7 |     3     |       2018       |
|   8 |     0     |       1956       |
|   9 |     0     |       1990       |
|  10 |     0     |       1953       |
|  10 |     1     |       1904       |
| ... |    ...    |       ...        |
+-----+-----------+------------------+

I tried to create a fiddle (here), but it did not work ...
CREATE TABLE YEARS (
    "FID" NUMBER(10,0) NOT NULL,
    "I0" NUMBER(10,0) NOT NULL,
    "BJA" NUMBER(10,0)
)
INSERT INTO YEARS (
    "FID","I0","BJA"
)
VALUES (
    (1,0,1949),
    (2,0,1996),
    (3,0,1970),
    (4,1,1871),
    (4,0,1975),
    (5,0,1967),
    (6,0,1968),
    (7,0,1926),
    (7,1,2009),
    (7,2,2012),
    (7,3,2018),
    (8,0,1956),
    (9,0,1990),
    (10,0,1953),
    (10,1,1904)
);

I am trying to execute the following query:
SELECT YEARS.FID,
       MIN(YEARS.BJA) AS "CONSTRYEAR",
       LISTAGG(YEARS.BJA, ', ') WITHIN GROUP (ORDER BY YEARS.BJA ASC) AS "RECONSTRYEAR"
FROM DB.YEARS YEARS
GROUP BY YEARS.FID;

And the output of the above query:

However, it is not the result that I want ... Yes, I am reading documentation source 1, source 2, and source 3; and also I have seen related threads How to use LISTAGG and WHERE together and How to use Oracle's LISTAGG function with a unique filter?.
How can I get the result looking like this:
+-----+-----------+------------------+
| FID | CONSTRUCT |   RECONSTRYEAR   |
+-----+-----------+------------------+
|   1 |      1949 |                  |
|   2 |      1996 |                  |
|   3 |      1970 |                  |
|   4 |      1871 |             1975 |
|   5 |      1967 |                  |
|   6 |      1968 |                  |
|   7 |      1926 | 2009, 2012, 2018 |
|   8 |      1956 |                  |
|   9 |      1990 |                  |
|  10 |      1904 |             1953 |
+-----+-----------+------------------+

Where you see that values from "CONSTRUCT" column were excluded from "RECONSTRYEAR". I do not understand where do I need to put a WHERE YEARS.IO != 0 clause in LISTAGG, so the lowest year won't be included.

Regexp_replace goes a long way:
 Regexp_replace( LISTAGG(YEARS.BJA, ', ') WITHIN GROUP (ORDER BY YEARS.BJA ASC),
                   '^[0-9]*(, )?','')

See db fiddle


Indexing for an ascending date value Let's say you have a table of invoices

CREATE TABLE Invoice
(  InvoiceID int IDENTITY(1,1) PRIMARY KEY,
   InvoiceDate DateTime CONSTRAINT [df_invoicedate] DEFAULT GETDATE(),
   ....
)

The invoice table has an InvoiceDate which is always the current date/time when the record is inserted.

Let's say you index this date

CREATE INDEX idx_InvoiceDate ON Invoice(InvoiceDate)

Note this is a non-clustered index (held in a separate file).

Let's say we know the RDBMS uses B+tree format for index files.

Bearing in mind that all inserts will hit the same page of the index file (because they are always at the end of the sort order), and that inserts into a single location are worst-case for B+tree insert, what special considerations should be observed?

Will this index end up lopsided?  Should it be reindexed regularly?  Should I use a low, normal, or high fill factor?

Should we try to find something else to put into the index prior to the date, e.g. an invoice type, to get the tree to balance out more evenly?

Will there be issues with hotspots or index page contention, because all inserts hits the same part of the index?  What can I do to mitigate that?

I understand that for an ascending surrogate key, a reverse key index might be better.  But this is a date and I may want to perform scans of a date range.  Is there anything I can do that will alleviate any of the issues that reverse key indices are meant to address?

Or is all of this a total non-issue, and indices of this kind are fine and I should stop worrying about it?

Will this index end up lopsided?
No. The way the BTree algorithm works keeps it balanced. (One interpretation of the "B" in BTree is for "balanced".) As leaf pages fill they cause parent pages to split, with half the rows going to each new parent. Further rows cause further splits, which cascade up the tree until the root has to split, at which point a new root page is created. At every stage all paths from root to leaf have the same length and hence "balanced".

Should it be reindexed regularly?
Yes. Each DBMS has its own idiosynchracies but likely regular re-build of indexes will be recommended. If for no other reason, the statistics will get out of date eventually which will result in less efficient query plans.

Should I use a low, normal, or high fill factor?
As all writes will be on the right-most pages leaving free space anywhere else will be a waste. There will never be any writes to use up that free space. Use a fill factor of 100%.

Should we try to find something else to put into the index
Indexes aren't (generally speaking) created for entertainment purposes. They're there to improve query performance or support constraint enforcement. If your workload would benefit from a <something> + date index then create that. If not, don't. Indexes require extra work during writes and consume space and maintenance time. Create the ones you need and no more. And no less. Keep in mind filtered indexes and included columns. Bear in mind that optimiser math may mean your indexes aren't used anyway.

Will there be issues with hotspots or index page contention .. What can I do to mitigate that?
Well, yes, in theory. First test and prove you'll have a problem meeting your expectied transaction-per-second count. If you can't, are you sure it's this index that's the limiting factor? If it is, in-memory tables and snapshot isolation may implement concurrency mechanisms different to the "base" system's, depending on DBMS. There may be alternative storage engines whose characteristics differ. Try those to see if the pain receeds. You can always post a follow-up question here with quantatative observations!

I understand that for an ascending surrogate key..
Surrogate keys are made-up values that replace human-intelligible natural keys. They're used for reasons releated to DBMS implementation peculiarities and are not inherent to the relational model. Your date is not a surrogate key - it is business data.

a reverse key index might be better.
Hmmm .. OK, so instead of writing all today's rows to key value 20161021 you'd write them to .. 12016102? How does that help? Hashing won't help either: to use the hashed values for lookup each date would have to produce the same hash so there's still a hotspot.

Or is all of this a total non-issue, and indices of this kind are fine and I should stop worrying about it?
Probably. The vast majority of indexes on most systems are like this. By-and-large they work just fine and the DBMS is able to process significant workloads on modest hardware. Indexing is one of those things you can (and should) tweak ad nauseam after go-live. Concentrate on delivering a normalised, feature-rich, debugged system. Tune it afterwards.


Why would mysql "show global status" query be taking 15 minutes? I'm reviewing the slow log, and on one of my slaves the average time for SHOW GLOBAL STATUS is 914s.

Any idea how to determine the cause of this?

That sounds very unusual, but not surprising. All it takes to run SHOW GLOBAL STATUS; is the USAGE privilege, which is just general connectivity as stated in the last sentence of the first paragraph of the MySQL Documentation on SHOW STATUS:

This statement does not require any privilege. It requires only the ability to connect to the server.

Given these facts, there can only be two possible causes:
POSSIBLE CAUSE #1
In MySQL 5.1 and MySQL 5.5, values for global status are accessible via the INFORMATION-SCHEMA database

INFORMATION_SCHEMA.GLOBAL_VARIABLES in MySQL 5.5
INFORMATION_SCHEMA.GLOBAL_VARIABLES in MySQL 5.1

Here is the table layout:
mysql> show create table information_schema.global_status\G
*************************** 1. row ***************************
       Table: GLOBAL_STATUS
Create Table: CREATE TEMPORARY TABLE `GLOBAL_STATUS` (
  `VARIABLE_NAME` varchar(64) NOT NULL DEFAULT '',
  `VARIABLE_VALUE` varchar(1024) DEFAULT NULL
) ENGINE=MEMORY DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

Notice it is a MEMORY table. Like MyISAM, all INSERTs, UPDATEs, and DELETEs into a MEMORY table require a full table lock (See Locking Granularity for MEMORY Storage Engine). There has to be some intermittent lock to populate values into this table. Since this table is local to the DB Connection, only the DB Connection is affected.
POSSIBLE CAUSE #2
User authentication may be blocked for a time due to host caching and DNS. Note paragraph 2 the MySQL Documentation on DNS Lookup Optimization and the Host Cache:

For each new client connection, the server uses the client IP address to check whether the client host name is in the host cache. If not, the server attempts to resolve the host name. First, it resolves the IP address to a host name and resolves that host name back to an IP address. Then it compares the result to the original IP address to ensure that they are the same. The server stores information about the result of this operation in the host cache.

If you do not have skip-host-cache and skip-name-resolve configured, please add these to my.cnf:
[mysqld]
skip-host-cache
skip-name-resolve

and restart mysql ASAP.
EPILOGUE
Cause #2 is a bit of a stretch since authentication should not trigger an entry into the slow query log.
Cause #1 is a lot more likely because if the global status is being loaded into a table, it is an SQL query with discernible passage of time.Thus, if the query's running time (in seconds) exceeds long_query_time, it will get recorded into the slow query log.


Store wait_stats automatically and query it whenever required I'm learning wait_stats and came up with thought. Is it possible to store wait stats in a table or create a stored procedure or query which will return wait_stats % when we execute query or stored procedure? if yes I would like to have query and stored procedure.

You could get a monitoring tool like Idera SQL Diagnostic Manager or Dell Spotlight to do all of that for you. If you want to go the manual route and get the wait stats yourself you can check out sp_BlitzFirst from Brent Ozar. You can use that to store wait stats in a table. If you click through the link they give you directions on how to set it up to log to a table.


SQL concatenate VARCHAR + VARBINARY with VARBINARY as hex As the title suggests, is possible to concatenate the 2, with the result as VARCHAR and the VARBINARY data as hex?

DECLARE @test VARCHAR(50) = 'Hello World!'
DECLARE @testvb VARBINARY(256)

SET @testvb = CAST(@test AS VARBINARY(256));

SELECT @test + @testvb --I don't want to cast this back to var char

The result should be: Hello World!48656C6C6F20576F726C6421

this is what i wanted to do

DECLARE @test VARCHAR(50) = 'Hello World!'
DECLARE @testvb VARBINARY(256)

SET @testvb = CAST(@test AS VARBINARY(256));

SELECT @test + CONVERT(VARCHAR(MAX),@testvb,2);
GO

|(No column name)                    |
|:-----------------------------------|
|Hello World!48656C6C6F20576F726C6421|

dbfiddle here

From this Aaron Bertrand blog post, I found out that CAST converts it back to original value whereas CONVERT has an option to keep the varbinary value:

SELECT @test + CAST(@testvb AS VARCHAR(MAX)) --converts back to Hello World!
SELECT @test + CONVERT(VARCHAR(MAX),@testvb,2) --keeps varbinary value


Is making joins between different table spaces slower? In MySQL and PostgreSQL, is making joins between different tablespaces inherently slower, compared to joining tables in the same tablespace?

This is only for Postgres:

If the tablespaces reside all on the same harddisk, then there is no performance difference at all.

If the tablespaces are created on different harddisks then yes, this can make a difference. In that case the necessary I/O is spread over multiple disks, thus improving the overall throughput (assuming all harddisks have the same performance).


Best database replication technique for a very unstable (power wise) network I am part of a small network (500 users) who are all connect via WiMax to each other. The people connected to this network live in about a 70 km radius. This network allows for a transfer speed of about 1.5 MB/s. We are using a website which uses a Mysql database. We are all living in South Africa which is currently experiencing a phenomenon call Load Shedding. This is where the municipalities turn of the power to specific regions to save power as South Africa is currently experiencing a power shortage.

We want to set up a database replication grid for this website in question. In the case of load shedding, no warning is given for power failures so it can be assumed that the servers will shut down abruptly.

Can anyone recommend a database setup and replication type the will allow seamless synchronization after load shedding occurred? We would like for both servers to have write access for in the case that one server goes down, the user will still be able to connect to the online server.

So in a nutshell:
 - Seamless/Easy/Automatic synchronization in the case of inconsistencies in the database.
 - Fast enough to not delete the entire database, and re-download it form the acting master server.
 - Able to keep sync with a limit of 1.5MB/s bandwidth.
 - No data is lost. Both servers have to be in perfect sync.

Some other technical details of the database:
 - Handles average of 250 queries per minute.
 - Database size is 1 GB in size.

I have read up about Master-Master synchronization, but also read that it is not very reliable in abrupt shutdowns, nor for low bandwidth applications. Does anyone have an idea for a setup like this.

If MySQL isn't a hard requirement, consider FoundationDB Key-Value Store + SQL Layer.

FoundationDB is a distributed Key-Value database with ACID guarantees and strong fault-tolerance. You can setup a cluster spread across the different municipalities, and if a server in the cluster goes down, the rest of the cluster continues to operate and accept writes. Changes are automatically synced to the downed node once it comes back online.

Here's a video where you can see this in action: Dave is killing the power to different nodes in the cluster, and demonstrating the cluster remains up and accepts writes. This blog post walks through what happens to the database in the case of a network partition and adds more technical context.

SQL Layer is a process that maps SQL statements to the Key-Value Store. It which presents a full, ANSI-compliant SQL API to your application.

Full disclosure: I'm an engineer at FoundationDB


Misaligned Log IO Issues and Unmatched LUN sector sizes in AlwaysOn I'm running into a little bit of a snag on a high log send queue in a 2014 SP1 Asynchronous Mode AlwaysOn Environment with a secondary replica in Azure.

For one of our higher transaction volume DB, we are seeing log send queue sizes up to nearly 2GB for extended periods. Initially we saw a CPU bottleneck on the secondary due to the sizing of the Azure VM, which we corrected. However we were still left with some high log queue sizes and slow send rates, coupled with respective high re-dos.

We traced this back to an issue with IO using the following error log message:
"There have been 112313856 misaligned log IOs which required falling back to synchronous IO.  The current IO is on file..." More specifically this has to do with unmatched disk sector sizes between our SAN and Azure LUNs.

This knowledge base article seems to address exactly what my issue is:
https://support.microsoft.com/en-us/kb/2936603

However its a year old and applies to CU5, which is about a year old, and we are about 8 patches past CU5. Additionally I can't find any official documentation from MS on trace flag 1800. Does anybody have an similar experience with either this error message, or information on trace flag 1800? If so was your issue corrected with this flag, or were you forced to match your LUN disk sector sizes between both replicas?

Thanks!

Since you are running  SQL Server 2014 SP1 which is build 12.0.4100, it includes all the updates from the lower RTM branch including all the CUs. You still need to enable TF 1800 (as start up parameter with a restart of SQL Server service - this has to be done during maintenance window, since this is a downtime for your application.)

I guess the root of my question is, does flag 1800 have any affect at all after SQL 2014 CU5?

Yes, TF 1800 is required to be turned ON even if you are on SP1. This is because you have mis-aligned disk sector size.

The disks that store the log files of the primary and secondary replica in an AlwaysOn Availability Group (AG) have different sector sizes. For example:

The primary replica log file is located in a disk that has the sector size of 512 bytes. However, the secondary replica log file is located in a disk that has the sector size of four kilobytes (KB).
The primary replica log file is located in an on-premise local system that has the sector size of 512 bytes. However, the secondary replica is located in Windows Azure Storage that has the sector size of four kilobytes (KB).

If you want to understand more about the problem, refer to

Storage Spaces/VHDx and 4K Sector Size by Bob Dorr
This blog post shows how to address the problem by modifying the sector size - Message misaligned log IOs which required falling back to synchronous IO in SQL Server Error Log

Its always a best practice to have same hardware configuration on primary and secondary servers - Disk, CPU, RAM, etc.


SSIS processing only 5000 rows from csv file to sql table I am loading data from csv file to my sql table through SSIS. Is there any default limit specified on number of records to be read from csv file ?

While loading the csv file, my data flow component is processing only 5000 records, though it contains 5341 records,as shown in the image below.How can i fix this ?

The source file shows only 5000 coming through from the first step.

This means all of the data in the file got imported and there was a bad delimiter making some of the data get merged in the import or you have error notification / truncation notification turned off and the data was truncated due to destination or transformation constraints.

You could also have configured data rows to skip on accident or there are actually only 5000 rows of data. I'd start by examining the data, then looking into the logs. Make sure SSIS logging is enabled and look to the logs for more information. I would also add a count step between each of the steps and store them into a table for tracking so I can verify the amount of rows coming through each way.


How to disable data type check while pushing data to Linked Server I have two SQL serves one is SQL server 2005 and other SQL server 2008.I push data from SQL server 2005 to SQL server 2008 using Linked Server.I want to improve performance of data transfer. Few days back I cam across one article (I don't remember exact site name) which mentioned  while inserting data in remote server you can disable data type check which can improve performance.He had mentioned one Set Command. I don't remember that command .Guys can you please help me to find out this command.I check with MSDN site but no help.

Also if you have any other performance improvement tricks please let me know.

Thanks in advance...

I see you have asked the same question at: Performance improvement in linked server-Disable data type check

  I push data from SQL server 2005 to SQL server 2008 using Linked Server.I want to improve performance of data transfer.

Highly recommend to use SSIS. Linked servers are not meant to push data. Also, depending on the amount of data you are pushing or pulling (direction matters!!), the performance of linked server will always be slow + the n/w bandwidth plays an important role as well.

Refer to my answer for : Which one is more efficient: select from linked server or insert into linked server?

  Few days back I cam across one article (I don't remember exact site name) which mentioned while inserting data in remote server you can disable data type check which can improve performance.He had mentioned one Set Command.

AFAIK, there is no such command in SQL Server to disable data type check which improves performance !!


Memory usage of MongoDB I've got Django app with mongoengine, which executes some aggregation-queries often enough.

The main problem is that mongod takes all the RAM and then crashes, so no LRU working, mongod just trying to get more and more memory.

My mongodb version is 3.2 and default engine is MMAPv1. As docs says, for caching MMAPv1 needs dataSize+indexSize amount of RAM. In my case that's about 370Mb (~290 for dataSize and ~80 for indexSize).

Here is my db.stats():

{
    "db" : "random_db",
    "collections" : 14,
    "objects" : 155613,
    "avgObjSize" : 1859.8720672437394,
    "dataSize" : 289420272,
    "storageSize" : 338821120,
    "numExtents" : 32,
    "indexes" : 22,
    "indexSize" : 82512192,
    "fileSize" : 1006632960,
    "nsSizeMB" : 16,
    "extentFreeList" : {
        "num" : 25,
        "totalSize" : 258314240
    },
    "dataFileVersion" : {
        "major" : 4,
        "minor" : 22
    },
    "ok" : 1
}

Actually I've got 1GB RAM and made swap-file for another 1GB.
When my app executes first aggregation query (taking only 50 documents from collection), mongod RAM usage increasing to ~300Mb (when I start service it's about 50-100Mb), and till ~70 such queries been executed - all of my memory+swap ends and mongod crashes.

I've created all the indexes, that needs for aggregation's '$match', if needed, I can add some of them here.

So why ~4000 documents in cache taking more than 2GB of RAM, if my entire database takes only 370Mb and there is only 150k documents in db? I know I'm doing something wrong, the question is very simple, but still I can't find explanation at docs.

BTW, here is my db.serverStatus.mem:

{
    "bits" : 64,
    "resident" : 112,//this value growth when queries executed
    "virtual" : 2523,
    "supported" : true,
    "mapped" : 1136,
    "mappedWithJournal" : 2272
}

So, I guess, my mongod can't take more than 2523Mb for entire db in cache?

The problem is that you make several wrong assumptions. Below you will find some corrections

MongoDB is not optimized for small resources

MongoDB was specifically designed to take a lot of data (recording clickstreams were the first application, iirc). As far as I can read, you have your django app on the same server as MongoDB. The problem here is that a lot of users for your django app would translate in a lot of queries/aggregations/write operations done on the MongoDB side. So django and MongoDB would have a race for resources especially during high load times. Since django is the first in the stack, it will almost always "win", for example requesting RAM which MongoDB now can not request. So it might well happen that MongoDB refuses something because of the lack of resources, the request is cancelled and your system appears to do nothing while really the two parts of your application did their best to answer the request, but failed to do so for the lack of resources.

To be honest: Running MongoDB on an 1GB instance alone would imho be not reasonable. Let alone with a django application. Imho, with this setup, you should at the very least have 4GB of RAM, and it might work until you put real load on it. For comparison: I usually suggest between 32 and 128GB of RAM per node (depending on the data, indices and a few other factors) for machines using SSDs as storage technology. Mind you, that is for MongoDB only – at an according scale of data, of course.

"Working set" does not (only) mean cache

  Disclaimer: brutally simplified and terminology might be off

MMAPv1 uses memory mapped files. All the details put aside, this means that a file is treated as an addressable range of memory. So if MongoDB wants to read a certain doc, it uses a memory address and a range it wants to read. That memory address might either be already in RAM or it has to be read from disk. Or, and here is the misconception, from the OSes filesystem cache, which – you guessed it – resides in RAM, though just a different part. (Iirc, what happens in this situation is that the address a pointer refers to is changed). So, not only would MongoDB have the working set in RAM, but it would be the direct cause of quite some part of the filesystem cache. So, we have another part of MongoDB requiring even more RAM than the working set only.

The working set is not the only thing consuming memory

Actually, the way journaling works, it doubles the RAM required by MongoDB.
Each connection (and remember each driver basically opens a connection pool) gets 1MB of RAM allocated.
Operations need some memory. Lets take aggregations as an example. They are capped to 100MB memory consumption – that alone would be 10% of your RAM, 5% of your allocatable memory.

However since you use MMAPv1: Do NOT turn off journaling! It is vital for crash recovery in MMAPv1.

Conclusion

Your machine is vastly underprovisioned in terms of RAM. Even if you have a tight budget, I can not stress the need of putting more RAM into that machine enough. I'd at least put 4GB into that machine (physical, that is, not swap) and see how it goes.

Be aware though, that with this setup, you'll always have django and MongoDB compete for resources the most when you need it the least: when your application has comparatively many concurrent users.


MariaDB cluster uptime Is there a way to show the Cluster database's uptime?

Obviously I can see each individual node's database uptime with the likes of:

SHOW GLOBAL STATUS LIKE 'Uptime';
select TIME_FORMAT(SEC_TO_TIME(VARIABLE_VALUE ),"%Hh %im") as Uptime from information_schema.GLOBAL_STATUS where VARIABLE_NAME="Uptime";

But this is not the same as the time the cluster database has been operating without interruption...?

(and longest time of all the nodes would not necessarily give an approximation either as nodes can come and go, but the DB should still be up)
(there doesn't see to be a suitable status variable, including wsrep variables)

I didn't know a good answer to this myself, but thought it was a good question and that it would be a good feature to have. So I asked for ideas in Codership's Google group (here). You're right that there aren't any obvious status variables for this, but two methods were suggested:

Assuming you access the cluster through a DB proxy, then use a monitoring tool (Nagios, Zabbix, maybe PMM, maybe MONyog, ...) and log the availability of the cluster. Uptime is then the time since the last failure.
grep the error log on each of the cluster nodes to find the timestamp of the most recent bootstrap. This coincides with the timestamp for when the status variable wsrep_cluster_conf_id is equal to 1, and can be found in the error log with something like:

grep -e conf_id -e 'Quorum results' node1.err | grep '= 1,' -B 1

I suppose these two methods give a different perspective of 'uptime': The first method is more from the applications' point of view, and the second method is from the cluster's point of view. They may not always give the same answer since the cluster could be "up", but not necessarily always in a state that makes it accessible. Also, there could be network issues between the application and the DB proxy, and between the DB proxy and the cluster, which would make the cluster seem "down" from the applications' point of view, but in reality it is still "up".


Every "INSERT" query check a combination of column's records Unfortunately, my mind has stacked right now and i would like to design a really simple DB schema.

What i want?

In a simple table, for instance Table1, there are two different columns, Series and Orders.

I would like every new record for Orders be unique for Series.

  Series: A Orders: 1,2,3,4,5,6

  Series: B Orders: 1,2,3,4,5,6

If the user try to insert Series A and Orders 1 for second time i would like to have an error.

I think that the best way is using stored procedure? Am I right?

Thanks in advance.

It sounds like what you are looking for is a "unique" constraint:

ALTER TABLE Table1 ADD UNIQUE(Series, Orders)

That will cause an error to be raised when you try create a row (by inserting new or updating an existing one) that duplicates an existing combination of Series and Order.

Depending on the rest of the database design, these columns might be a good candidate for a compound primary key which would also enforce uniqueness.

This is fundamentally basic database stuff, I would suggest you grab an introductory book or find a good tutorial online. Keywords that you should understand: primary key, foreign key, check constraint, unique constraint, surrogate key


Merge two postgress databases with identical schemas I have two Postgresql databases with identical schemas, and I'd like to merge them (or merge them both into a third).

The problem is the tables all have id PKs that are auto increment primary keys, and use those id columns as FKs to other tables.

I know the data is logically distinct between my two databases, but they overlap on their PK's, for example:

Database A:

Table Building:

id    | name
1     | Smooth Hall
2     | Orchard Towers

Table Floor:

id    | building_id (FK building.id) | floor_number
1     | 1                            | 1
2     | 2                            | 1
3     | 2                            | 2

Database B:

Table Building:

id    | name
1     | Chancey Theater
2     | Allen Hall

Table Floor:

id    | building_id (FK building.id) | floor_number
1     | 1                            | 1
2     | 2                            | 1
3     | 2                            | 2

Is there a tool or approach you can suggest as to how to merge these databases such that the result would be include all the data, and all the FK relationships?

I don't care about the resulting order of rows, or the value in the id columns in the output, just so long as the data has the same logical structure and relationships, e.g.

Database C:

Table Building:

id    | name
a     | Smooth Hall
b     | Orchard Towers
c     | Chancey Theater
d     | Allen Hall

Table Floor:

id    | building_id (FK building.id) | floor_number
e     | a                            | 1
f     | b                            | 1
g     | b                            | 2
h     | c                            | 1
i     | d                            | 1
j     | d                            | 2

where a-j are any distinct numbers.

Unfortunately, there is no simple way to do this. Essentially you will need to write a script to go through one of the databases, extract the appropriate information, and then insert it into the other database, making sure to manage the new id numbers you get and updating the incoming information as needed. The complexity of your schema will determine how difficult this is to manage, but it is doable; pg_upgrade uses a version of this technique to handle re-mapping on-disk files inside the Postgres system catalogs.

On a side note, this is one of the arguments against using surrogate primary keys. Had you used entirely natural unique keys, you would be able to simply dump one database out and load it into the other.


Back up and restore to main system I have a software system running in my office with MS SQL Server in back end.I want to backup the database daily and delete all data from database so that only one-day data will be in the database at office. I want to append this daily data to my system at home so that  all data including old remains at home pc.

IS it possible to append daily data from office to my home pc?

It's possible, but not just with backup and restore.  You've got three separate problems to solve:

Problem 1: How do you delete all but one day at the office?

You can do this with delete statements that touch every table in the system, but if you've got complicated relationships or lots of tables, this can be painful.  You have to make sure you only delete tables with changing data, like sales, and not tables that need to be kept, like config tables and customer lists.

If you've got large quantities of data, this can also be painful for the long-running deletes.  Table partitioning can be a good fit for scenarios like this when you've got, say, >100GB of data involved.

Problem 2: How do you get the data from office to home?

You could solve this with backup/restore, but given that this is a solution you want to implement permanently, something like replication might make more sense.  Transactional replication lets you stream the logged transactions to the second machine, and then your data can look different between the primary machine and the secondary machine.  For example, the home machine might have different indexes depending on how much data you're trying to store.

Problem 3: How do you keep adding the historical data at home?

If you use the backup/restore approach, you'll be restoring a new database every day.  The historical database should be under a different name, and then write scripts to insert the data from the restored database into the backup database.  Again, more tables = more problems here.

If you use the transactional replication approach, this will be much easier.

All three problems are pretty large, so consider breaking this work up into more digestible steps.  Short story, though, this isn't a built-in feature.


GETDATE - Current date, Month Start and Month End in yyyymmdd I deal a lot in ISO dates (yyyymmdd) and need to generate the first and last day of the month very frequently. I spent a good deal of time trying to put these together and figured I would put it all together and share it. Here is what I have come up with.

I took some work from an existing article and added the CONVERT function to get the date format that I need.

--Get Current Date, Current Month Start, Current Month End, and Current Next Month start in ISO date format
SELECT
CONVERT (varchar(8),GETDATE(),112) Current_Day_ISO,
CONVERT (varchar(8),(DATEADD(MONTH,0,GETDATE())-DAY(GETDATE())+1),112) CurrentMonth_start_ISO,
CONVERT (varchar(8),(DATEADD(MONTH,1,GETDATE())-DAY(GETDATE())),112) CurrentMonth_End_ISO,
CONVERT (varchar(8),(DATEADD(MONTH,1,GETDATE())-DAY(GETDATE()-1)),112) NextMonth_start_ISO

Here is another version that allows you to do the same from a declared date

--Get Declared Date, Declared Month Start, Declared Month End, and Declared Next Month start in ISO date format
DECLARE @dtDate DATE
SET @dtDate = '08/22/1998'
SELECT
CONVERT (varchar(8),@dtDate,112) Declared_Day_ISO,
CONVERT (varchar(8),(DATEADD(MONTH,0,@dtDate)-DAY(@dtDate)+1),112) DeclaredMonth_start_ISO,
CONVERT (varchar(8),(DATEADD(MONTH,1,@dtDate)-DAY(@dtDate)),112) DeclaredMonth_End_ISO,
CONVERT (varchar(8),(DATEADD(MONTH,1,@dtDate)-DAY(@dtDate-1)),112) DeclaredMonthNextMonth_start_ISO

Credit goes to:
Pinal Dave in the main article and Param in the comments section of this blog post
http://blog.sqlauthority.com/2007/08/18/sql-server-find-last-day-of-any-month-current-previous-next/

Feel free to review my code and put any suggestions you might have in the comments!

From SQL Server 2012 you can use EOMONTH (Transact-SQL)

SELECT DATEADD(DAY, 1-DAY(GETDATE()), GETDATE()) AS CurrentMonth_start,
       EOMONTH(GETDATE()) AS CurrentMonth_End,
       DATEADD(DAY, 1, EOMONTH(GETDATE())) AS NextMonth_start,
       EOMONTH(GETDATE(), 1) AS NextMonth_End;


Can view adapt to implicitly reference user's default schema? A bit of background. I have a base application and most clients use it as standard. However some clients have small code and database customisations. Each of these clients has their own branch and maintenance can be tricky.

I want to consolidate all these into a single database structure (not a single database - we aren't doing multi-tenancy) to enable upgrades to be applied in a much more uniform fashion. I'm still at the proof of concept stage, but the route I was going down would be to have the standard objects stay in the schema they currently exist in (mostly dbo) and have the custom objects reside in a schema for each client.

For example, I could have dbo.users and client1.users which has some additional columns. If I set the default schema for the client to be "client1" then the following query

SELECT * FROM users

will return data from the client1 schema or the dbo schema depending on which login is connected. This is absolutely perfect for what I'm trying to achieve.

The problem I'm running into is with Views. I have many views which are in the dbo schema and refer to the Users table. No matter which user I connect to the database as, these views always select from dbo.users.

So I'm guessing the question I have is: Can I prefix the tables in the view with some variable like "DEFAULT"? e.g.

SELECT u.username, u.email, a.level
FROM DEFAULT.users u INNER JOIN DEFAULT.accessLevels a ON u.accessID = a.accessID

If this isn't possible and I'm totally barking up the wrong tree, do you have any suggestions as to how I can achieve what I'm setting out to do?

Many thanks.

Create multiple views and/or table-valued functions. You can't override the schema without specifying it explicitly unless you build dynamic SQL to do so for you, but you can't do that in a view or a function - only a stored procedure.

With that said, you could do that with something like this, however it will fall back to dbo (or some other schema you hard-code) if a match is not found or if you are using Windows groups for example (which prior to SQL Server 2012 do not support default schema):

CREATE PROCEDURE dbo.whatever_procedure
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @sql NVARCHAR(MAX), @s SYSNAME;

  SELECT @s = d.default_schema_name
    FROM sys.database_principals AS d
    LEFT OUTER JOIN sys.server_principals AS s
    ON d.[sid] = s.[sid]
    WHERE SUSER_SNAME() IN (d.name, s.name);

  SELECT @s = COALESCE(@s, N'dbo');

  SET @sql = N'SELECT x FROM ' + QUOTENAME(@s)
    + '.whatever_view_name;';

  EXEC sp_executesql @sql;
END
GO

You can test this:

USE tempdb;
GO
CREATE SCHEMA foo;
GO
CREATE LOGIN flubat WITH PASSWORD='flubat',CHECK_POLICY = OFF;
GO
CREATE USER flubat FROM LOGIN flubat WITH DEFAULT_SCHEMA = foo;
GO
GRANT SELECT ON SCHEMA::foo TO flubat;
GO
CREATE VIEW dbo.whatever_view_name AS SELECT x = 'dbo';
GO
CREATE VIEW foo.whatever_view_name AS SELECT x = 'foo';
GO
-- create the above procedure
GO
GRANT EXEC ON dbo.whatever_procedure TO flubat;
GO

And then execute it as yourself and also as flubat:

EXEC dbo.whatever_procedure;
GO
EXECUTE AS USER = N'flubat';
GO
EXEC dbo.whatever_procedure;
GO
REVERT;

Results:

x
----
dbo

x
----
foo

But like I said, this is potentially fragile, and I would lean toward either creating a view per schema, or changing the design altogether.


Execution plan percentages don't add up to 100% I am referring to SQL Server query execution plans to take query cost and then optimize the required things by looking at the plan.

But the total of individual query costs adds up to more than 100%.

This is my query:

DECLARE @date SMALLDATETIME

SELECT Reffd AS NAME
    ,(
        SELECT (
                (
                    SELECT count(*)
                    FROM [cal_reg].[dbo].[customer]
                    WHERE upper(Reffd) = upper(main.reffd)
                        AND cast(DATE AS SMALLDATETIME) = @date
                    ) + (
                    SELECT count(*)
                    FROM [cal_reg].[dbo].[rep]
                    WHERE upper(Reffd) = upper(main.Reffd)
                        AND cast(DATE AS SMALLDATETIME) = @date
                    )
                )
        ) AS Completed
    ,(
        SELECT (
                (
                    SELECT count(*)
                    FROM [cal_reg].[dbo].[customer]
                    WHERE upper([call Attnd]) = upper(main.[Reffd])
                        AND cast(DATE AS SMALLDATETIME) = @date
                    ) + (
                    SELECT count(*)
                    FROM [cal_reg].[dbo].[rep]
                    WHERE upper([Call Attnd]) = upper(main.reffd)
                        AND cast(DATE AS SMALLDATETIME) = @date
                    )
                )
        ) AS Attended
    ,(
        SELECT (
                (
                    SELECT (
                            (
                                SELECT count(*)
                                FROM [cal_reg].[dbo].[customer]
                                WHERE upper(Reffd) = upper(main.reffd)
                                    AND cast(DATE AS SMALLDATETIME) = @date
                                ) + (
                                SELECT count(*)
                                FROM [cal_reg].[dbo].[rep]
                                WHERE upper(Reffd) = upper(main.Reffd)
                                    AND cast(DATE AS SMALLDATETIME) = @date
                                )
                            )
                    )
                ) + (
                SELECT (
                        (
                            SELECT count(*)
                            FROM [cal_reg].[dbo].[customer]
                            WHERE upper([call Attnd]) = upper(main.[Reffd])
                                AND cast(DATE AS SMALLDATETIME) = @date
                            ) + (
                            SELECT count(*)
                            FROM [cal_reg].[dbo].[rep]
                            WHERE upper([Call Attnd]) = upper(main.reffd)
                                AND cast(DATE AS SMALLDATETIME) = @date
                            )
                        )
                )
        ) AS Total
    ,'' AS f6row
    ,'' AS f8row
FROM [cal_reg].[dbo].[customer] AS main
WHERE cast(DATE AS SMALLDATETIME) = @date
    AND upper(reffd) IN (
        SELECT upper(shname)
        FROM common.dbo.Password_table
        )
GROUP BY reffd

And for that I am getting total of 104% as shown below:

+

My question is it possible to solve that more than 100% cost error or are there any other ways from which I can tell my query is running efficiently ?

The sum of operator costs is more than 100% in execution plan is a known bug and is closed as by design!

Posted by Microsoft on 11/17/2010 at 5:28 PM
Thanks for taking the time to file this observation and simple repro. The strange cost percentage values that you observed are an aritifact of the specific structure of the query plan that are a bit confusing but ultimately do make sense. They will not adversely affect the running of the query in any way.
The concatenation operator has two children - the table scans. However the server expects that it will not be necessary to execute the second table scan. This is because it expects to find a row from the first table scan which would satisfy the "exists" part of the query. Therefore it does not include the cost of the second table scan when computing the total cost of the subtree rooted at the concatenation. However it still estimates and reports the cost of the second table scan, which the management tool the computes as a percentage of the total query cost as if it will be executed.
In this case of course, it would be necessary to run the second table scan since the first is empty. However the engine operates on the conservative assumption that there will always be at least one row (which may only have been added after the query was compiled).
Campbell
SQL Development

Aaron Bertrand filed a similar bug - SSMS : Execution plan sometimes exceeds 100%

Posted by Microsoft on 5/28/2008 at 11:58 AM
Thanks again for bringing this to our attention. This anomaly arise in the presence of "row goals" and the concatenation operator. Row goals is the term for when a subtree (perhaps the entire query) is not required to return all possible rows. This most commonly happens when the query has a "top" clause although there are other causes. The anomaly arises because we do not expect to execute the second child of the Concatenation (because we expect the first child to provide all the required rows). Those additional children are costed to assume they return a single row. The only alternative would be to give them 0 cost which would fix the anomaly but probably create other confusions and would lead the optimizer to not care about the plans - we don't want to do that just in case they are executed.
Campbell Fraser, SQL Development.

If you want to understand how plan costing works, Paul White explains it at his best here.
From the query processor team - What’s this cost?
General guidelines for better performance -

Have proper indexes & up-to-date stats.
Fetch data that is required ONLY along with relevant columns.
Have proper schema.table with proper indexes & datatypes
Use statistics time, IO before and after you make changes to measure the gain.
Get help / guidance from experts at this site :-)

That's why I use SQL Sentry's FREE Plan Explorer


AWS RDS: How do I diagnose this failure? I have a simple RDS setup: one database, no replication. It has been running for around 2 years without any problems. CPU usage is generally less than 5%, sometimes boosting to around 10%.

Today, without any apparent reason or warning, my application lost connection with the DB. Looking at the log files, I could see the message "Recovery of the DB instance started..." and a few minutes later "Recovery of the DB instance complete". At that point my application was able to reconnect and work fine.

How do I go about diagnosing this further? The log file has about 30 lines in it, starting with "Giving 2 client threads a chance to die gracefully", then "Shutting down slave threads". After that, the service goes through a restart procedure.

Is it normal operation for RDS to 'recover the instance' after a failure like this? Presumably I could lose a few minutes of data?

Update:

The logs are no longer available, so I cannot post them.  Also, I notice that the freeable memory jumps up sharply from the time of the incident, which would seem to be a good thing.

I have had this happen, even to production RDSs, though it is rare.

How to go about diagnosing

Check there was no advance notice of maintenance work:
Did you get any email notifications from AWS to say that your instance needed mandatory system upgrades or maintenance?  Was this in your instance's permitted maintenance window?

Raise a ticket with AWS Support:
If you have AWS support, raise a ticket with them to ask them what happened.  On the occasions that this has happened and I have raised a ticket they were not able to give me a good reason for the DB going away, but have generally shrugged and named local networking issues on the instance's HyperV.

Did I loose data?
It's unlikely though that you lost data if you are using innoDb and it was a shutdown issued by AWS. The log lines that you cite:
"Giving 2 client threads a chance to die gracefully", then
"Shutting down slave threads"
Are lines from a command initiated shutdown, rather than a crash.  It looks like AWS issued a shutdown for you.

Other Notes
Occasionally AWS sees fit to move your instance to another Hypervisor without prior notice, sometimes because they want to clear the Hypervisor that your instance is on, it may have developed hardware problems, for example.
There might be networking issues within AWS, or you might be subject to 'noisy neighbours'.  There are many possible reasons and unfortunately contractually AWS don't have to tell you why.