From the documentation page:

  Tip: Plain VACUUM may not be satisfactory when a table contains large numbers of dead row versions as a result of massive update or delete activity. If you have such a table and you need to reclaim the excess disk space it occupies, you will need to use VACUUM FULL, or alternatively CLUSTER or one of the table-rewriting variants of ALTER TABLE. These commands rewrite an entire new copy of the table and build new indexes for it. All these options require exclusive lock. Note that they also temporarily use extra disk space approximately equal to the size of the table, since the old copies of the table and indexes can't be released until the new ones are complete.

If you can't get away with an exclusive lock on the table, you also might be fine with a simple ANALYZE <tablename>;, as that will only take a read lock and should update the statistics for the table and reduce ( if not entirely eliminate ) performance degradation for the other queries.  Then you can let the regular autovacuum daemon do its thing ( or whatever your current custom vacuum solution might be ) normally.

If your table doesn't even have a clustered index, you will first need to do an ALTER TABLE <tablename> CLUSTER ON <indexname>;.  You should probably have some kind of clustered index anyway: Having a massive heap table and poor performance against it often goes hand in hand.

As far as best practices go, I would be highly inclined to recommend the ANALYZE approach insofar as doing an automatic statistics update is by far preferable to an automatic index rebuild or virtually any form of VACUUM FULL being run without an admin actively watching it.


Importing CSV into multiple tables using PostgreSQL

I find the most reliable way to import CSV data into PostgreSQL is to create a table just for the import (you can delete it after). Often there are wonky values in some columns, so I make all the column types text and then convert them on insert into the "real" table.

The \copy command in psql (different than copy) makes it easy to copy files to a remote server, and insert into ... select makes it easy to copy to another table.


This is a classic situation where you use a "joining table".

You will need three tables.

Person
Skill
Person_Skill

(note that table names are singular - this is a preference of
 mine, not a rule).

Person
ID integer PRIMARY KEY
<other person details - name, address, phone &c.>
KEY name (and possibly phone?)

Skill
ID integer PRIMARY KEY
<other skill details - name, description, necessary qualification &c.>
KEY name (maybe not even necessary if there are only 50 skills?)

Person_Skill
Person_ID integer FOREIGN KEY REFERENCES Person(ID)
Skill_ID integer FOREIGN KEY REFERENCES Skill(ID)
Unique Index on (Person_ID, Skill_ID) (to avoid duplicates)
KEY Skill_ID

This way you don't store the skill name, description &c. for every person who has that skill, but just once and then refer to it in the Person_Skill table by its ID - this reduces space and memory requirements and is less error prone (you only store the Skill datum once - one of the core features of an RDBMS under Codd's rules).

That is the way I would do this.


If I need to restrict access to certain databases?

Generally you should start by running mysql_secure_installation on any mysql production database servers to ensure the minimum security measures are in place. You shouldn't need additional restrictions on general users after this.


Stopping N number of nodes of a Cassandra cluster running on a single machine using bash script

I figured it out:

kill $(ps auwx | grep '[c]assandra' | awk '{print $2}')


Let's suppose the institution does send me a listing of all the tables and their columns.  Even though I don't have the actual data, I can now attempt social engineering attacks to convince insiders into giving me whatever data I want.

The request would sound much more legitimate, because I could ask for very specific pieces of information using the actual table names. If I were to find someone with access to the database and convince them that I'm a higher-ranking employee (e.g. their manager or the CEO), I could make a legit-sounding request for sensitive information.

You would be surprised how often this happens in a corporate environment. Just having the metadata for a database exposed can be a big security risk.


How to show the related (suggested) keywords?

I am assuming that your tags are stored in the "Tag" table, but if not it should be an easy fix for the below SQL. I have not used SQLite3 before, but this should be generic SQL. I used the literal "John," but just replace with a variable for whatever the user enters. Also, if you wanted to limit the count to at least x number then put in a having clause.

select t2.keyword
from TagsTree tt
join Tag t on tt.TagID = t.TagID
join TagsTree tt2 on tt.ImageID = tt2.ImageID
join Tag t2 on tt2.TagID = t2.TagID and t.TagID <> t2.TagID
where t.keyword = 'John'
group by t2.keyword
order by count(*) desc, t2.keyword


I suggest the following:

At first login as SYSDBA and check the ACCOUNT_STATUS of your user (SYSTEM in the sample below).

> sqlplus / as sysdba
SQL> select username, account_status, lock_date from dba_users where username='SYSTEM';

If this statement doesn't return a record, then your account does not exist.

If the account is locked (maybe because you entered a wrong password several time) then you have to unlock the account:

SQL> alter user SYSTEM account unlock;

To reset the password you can use this statement:

SQL> alter user SYSTEM identified by NEW_SYSTEM_PWD;

If you are working with a development system, then you can ignore case sensitive logons and make sure passwords never expire again:

SQL> alter system set sec_case_sensitive_logon=false scope=both;
SQL> alter profile default limit password_life_time unlimited;

See Oracle documentation for further infos regarding DBA_USERS


When Creating a Foreign Key Relationship

What you need to check are the referential integrity constraints.

If you want to delete the parent key there are around four options on how you want to deal with the children keys:

NO ACTION which raises an error and does not delete the parent key (rolls back).
CASCADE which will carry on and delete all the child keys referred by that parent key,  leaving no orphans.
SET NULL which will set all the child keys to null values.
SET DEFAULT sets the child keys to the specified default value.


Partial Backup / Restore Sql Server Simple Recovery

In short, no, you can't backup the individual files that you want to restore. According to BOL, the only way to backup files on a database that uses simple recovery model is if they are all backed up up together: http://msdn.microsoft.com/en-us/library/ms179401.aspx .
Have you considered differential backups of the files? Doing this would give you smaller file size for backups (still requires a full at some point), but would also increase the number of steps required to restore the database. Differentials partial backups are described here: http://msdn.microsoft.com/en-us/library/ms190218.aspx.


data_precision and data_scale Missing from Oracle's all_tab_columns

I do not see NUMBER(12,6) anywhere in your output. That column is simply a NUMBER without precision specificed.

SQL> create table t1(c1 number);

Table created.

SQL> insert into t1 values (0.00025);

1 row created.

SQL> select * from t1;

        C1
----------
    .00025

SQL> select data_length, data_precision, data_scale from  all_tab_columns where table_name = 'T1' and column_name = 'C1';

DATA_LENGTH DATA_PRECISION DATA_SCALE
----------- -------------- ----------
         22

SQL>

Specifying NUMBER is not the same as NUMBER(22,0) or NUMBER(22):

SQL> create table t2(c1 number(22, 0));

Table created.

SQL> select data_length, data_precision, data_scale from  all_tab_columns where table_name = 'T2' and column_name = 'C1';

DATA_LENGTH DATA_PRECISION DATA_SCALE
----------- -------------- ----------
         22             22          0

SQL> insert into t2 values (0.00025);

1 row created.

SQL> select * from t2;

        C1
----------
         0

SQL>  create table t3(c1 number(22));

Table created.

SQL>  select data_length, data_precision, data_scale from  all_tab_columns where table_name = 'T3' and column_name = 'C1';

DATA_LENGTH DATA_PRECISION DATA_SCALE
----------- -------------- ----------
         22             22          0

SQL>


Database Maintenance Job and Backup Scheduling

According to the scheduled maintenance tasks and second backup strategy here is my suggestion which may suit your requirement to reduce transaction log backup.
As you said in the comment, “Log-Shipping is enabled for the databases. Log-Shipping feature support both FULL and BULK_LOGGED recovery model.

So

1st thing you can do if you want to continue with FULL recovery model.

You can add two steps in the schedule maintenance job:

Change recovery model of database to BULK_LOGGED
Execute Index maintenance script
And Change recovery model back to FULL

Pros:  It will reduce truncation log backup size.
Cons: Point-In-Time recovery will not be possible for the duration.

2nd thing you can do if it suits your SLA (RTO & RPO) as you are backing-up transaction log every 15 minutes, “change recovery model to BULK_LOGGED.

3rd action you can perform to find all unused indexes and remove them, it’ll help to reduce T-Log backup size and maintenance duration as well. You can use this script to find index details.

CREATE TABLE #tbl_index_info(
    [Table Name] [sysname] NOT NULL,
    [Index Name] [sysname] NULL,
    [Index Type] [nvarchar](60) NULL,
    [Index Columns] [sysname] NULL,
    [Row Count] [bigint] NULL,
    [Fill Factor] [tinyint] NOT NULL,
    [user_seeks] [bigint] NOT NULL,
    [user_scans] [bigint] NOT NULL,
    [user_lookups] [bigint] NOT NULL,
    [Index Read] [bigint] NULL,
    [Index Writes] [bigint] NOT NULL,
    [Last Used (Read)] [datetime] NULL,
    [Last Used (Write)] [datetime] NULL,
    [Last Index Re-Built/Re-Organize] [datetime] NULL,
    [index_id] [int] NOT NULL,
    [object_id] [int] NOT NULL
)
GO

insert into #tbl_index_info
select      t.name [Table Name]
            ,i.name [Index Name]
            ,i.type_desc [Index Type]
            ,c.name [Index Columns]
            ,p.rows [Row Count]
            ,i.fill_factor [Fill Factor]
            ,ius.user_seeks
            ,ius.user_scans
            ,ius.user_lookups
            ,(ius.user_seeks+ius.user_scans+ius.user_lookups) [Index Read]
            ,ius.user_updates [Index Writes]
            ,COALESCE(ius.last_user_seek,ius.last_user_scan,ius.last_user_lookup) [Last Used (Read)]
            ,ius.last_user_update [Last Used (Write)]
            ,STATS_DATE(s.object_id, s.stats_id) [Last Index Re-Built/Re-Organize]
            ,i.index_id
            ,t.object_id

from        sys.tables t
inner join  sys.columns c
on          t.object_id=c.object_id
inner join  sys.indexes i
on          t.object_id=i.object_id
inner join  sys.index_columns ic
on          t.object_id=ic.object_id and i.index_id=ic.index_id and c.column_id=ic.column_id
inner join  sys.dm_db_index_usage_stats ius
on          t.object_id=ius.object_id and i.index_id=ius.index_id
inner join  sys.stats s
on          t.object_id=s.object_id and i.index_id=s.stats_id
inner join  sys.partitions p
on          p.object_id=i.object_id and p.index_id=i.index_id
where i.name is not null and i.is_unique!=1
Order by ius.user_lookups desc
GO

Select      DISTINCT
            a.[Table Name],a.[Index Name],a.[Index Type],
            (SELECT SUBSTRING(
            (
                SELECT ',' + [Index Columns] FROM #tbl_index_info b
                WHERE a.[Table Name]=b.[Table Name] and a.[Index Name]=b.[Index Name]
                FOR XML PATH('')), 2,10000)
                )[Index Columns]
                ,a.[Row Count]
            ,ips.avg_fragmentation_in_percent,a.[Fill Factor],a.user_seeks,a.user_scans
            ,a.user_lookups,a.[Index Read],a.[Index Writes],a.[Last Used (Read)],a.[Last Used (Write)]
            ,[Last Index Re-Built/Re-Organize]
from
#tbl_index_info a
cross apply
            sys.dm_db_index_physical_stats(db_id(),a.object_id,a.index_id,0,DEFAULT) ips
ORDER BY [Index Read] ASC
GO

DROP TABLE #tbl_index_info
GO

4th you need to verify that you are using appropriate FILLFACTOR for indexes. Less FILLFACTOR means more number for pages. So chose FILLFACTOR according to usage of table.

Thanks!


T-SQL to find Database name and if Filestream is enabled for specific Database

Reference:

Enable and configure FILESTREAM
sys.master_files (Transact-SQL)

I would rephrase you question to find databases that are using FILESTREAM. Because you can only enable FILESTREAM at an instance level in order to use it in any user database.

To find all the files related to FILESTREAM or database name you can use this query.

SELECT db.NAME       AS DBName,
       type_desc     AS FileType,
       physical_name AS Location
FROM   sys.master_files mf
       INNER JOIN sys.databases db
               ON db.database_id = mf.database_id
WHERE  type_desc = 'FILESTREAM'


How to save Outlook attachment in a drive with a new name and overwrite if it already exists?

Public Sub saveAttachtoDisk(itm As Outlook.MailItem)
Dim obj As Outlook.Attachment
Dim saveFolder As String
saveFolder = "D:\EmailAttachments"
     For Each obj In itm.Attachments
          obj.SaveAsFile saveFolder & "\" & obj.DisplayName
          Set obj = Nothing
     Next
End Sub


Issue when connecting to open shift via mysql workbench on Mac

The error message tells you that the MySQL server is not accepting a connection for user adminM4B1hN at the given host. You need to allow such a connection for that user (e.g. by adding an anyhost user entry (adminM4B1hN@%) for that user.

Also, keep in mind how the MySQL server is seen from the remote end of your SSH tunnel. If the tunnel ends on the same machine where the MySQL runs then the MySQL server address would be localhost not what you have given. The address is always resolved relative to the remote end of the tunnel.


Convert Rows to columns using 'Pivot' in SQL Server when columns are string data type

Don't pivot if you always have 3 rows per empid. Just self join

SELECT
   t1.empid,
   t1.amount AS basic,
   t2.amount AS TA,
   t3.amount AS DA
FROM
   MyTable t1
   JOIN
   MyTable t2 ON t1.empid = t2.empid
   JOIN
   MyTable t3 ON t1.empid = t3.empid
WHERE
   t1.wagecode = 'basic'
   AND
   t2.wagecode = 'TA'
   AND
   t3.wagecode = 'DA'

Also, welcome to the pitfalls of EAV designs


Yes, you can upgrade directly to Service Pack 3. Service packs are cumulative; SP3 contains everything in SP2 and SP1.

This has always been true for every version of SQL Server I’ve worked with, and I'm old. But for those needing to read this from MS  documentation on their web site instead of peers answering questions here, here is one official source:

  Service packs are cumulative. Each new service pack contains all the fixes that are in previous service packs, together with any new fixes. You do not have to install a previous service pack before you install the latest service pack.

You will need to upgrade to SP3 before applying any SP3 Cumulative Updates. The name is a little confusing but a cumulative update is only cumulative from the relevant service pack. SP2 CU1 can only be applied to SP2; it won’t install on RTM, SP1, SP3, etc.

This acronym definition page may have useful info too.


To reduce recovery time (and save space): Is it possible to ignore some PostgreSQL 13 tables (or schemas) when backing up using Barman?

Barman will create online file system backups, and such backups can only be done of the complete cluster.
To backup parts of a database, you must use a logical backup with pg_dump, but then you cannot perform point-in-time recovery.
One way to work around that is to have the tables you don't want to back up be part of a different cluster and access them with postgres_fdw, or store them as “flat files” and use file_fdw to access them.
Be warned, though, that there are performance penalties when querying such foreign tables.


Here is a process that you need to go through for upgrading from SQL Server 2000 to SQL Server 2014.

http://sqlmag.com/sql-server-2014/sql-select-steps-migrate-sql-server-2000-sql-server-2014

You will see that you need to

Restore a SQL Server 2000 backup to SQL Server 2008 as one step, followed by
Restore of the (now upgraded) 2008 version to SQL Server 2014.

If you only have .mdf files (not a full backup of the database) then you will need to create .ldf (log) files for the instance. This is not a recommended method for restores, but may be what you need.  Perhaps use marc_s script:

CREATE DATABASE Test_Data
ON PRIMARY
(FILENAME = N'D:\Test_Data\Test_Data.mdf')
FOR ATTACH_REBUILD_LOG
GO

Or, there are several posts out there about attaching a database without a transaction log file.  One random choice:

http://www.mytechmantra.com/LearnSQLServer/How-to-attach-database-without-a-transaction-log-file-in-SQL-Server.html

If your SQL Server 2000 .mdf was from SQL Server 2000 SP4 then you may be able to attach it to a SQL Server 2008 server.  If the file has been corrupted, of course, then there is little to be done.

From this location you can apparently download the MSDE engine and SP 4.  The MSDE engine will only handle a 2GB database.

https://msdn.microsoft.com/en-us/sqlserver/bb895925

Microsoft SQL Server 2000 Desktop Engine (MSDE 2000) Release A
Microsoft SQL Server 2000 Service Pack 4

MSDE has many limitations, including only 2GB databases.  (And it has been so long since I used it that I do not remember all the problems and limitations.)  See:

https://technet.microsoft.com/en-us/library/ms165672(v=sql.90).aspx

If you need a full version of SQL Server 2000, then I would suggest that you talk to other SQL Server users in the area and find one who still has retained an original install disk.

There are websites that say they have the media, but I cannot recommend them.


How to get max field value length for each TEXT field in all tables in database

No need for PL/pgSQL.

You can use a variation of this solution to do everything in a single query.

select table_schema,
       table_name,
       column_name,
       (xpath('/row/max/text()',query_to_xml(format('select max(length(%I)) from %I.%I', column_name, table_schema, table_name), true, true, '')))[1]::text::int as max_length
from information_schema.columns
where table_schema = 'public'
  and data_type = 'text'
order by table_schema, table_name, column_name;

Evan's comment got me thinking if there is a way to do this with a single statement per table (although I doubt that this performance optimization is really important for a "get me some information about my database query")

With a bit of XML juggling, it is indeed possible:

with counts as (
  select table_schema,
         table_name,
         query_to_xml(format('select %s from %I.%I', string_agg(format('max(length(%I)) as %I', column_name, column_name), ', '), table_schema, table_name),
                      true, true, '') as result
  from information_schema.columns
  where table_schema = 'public'
    and data_type = 'text'
  group by table_schema, table_name
)
select table_schema,
       table_name,
       substring(x::text, 2, strpos(x::text,'>') - 2) as column_name,
       (xpath('./text()', x))[1]::text::int as max_length
from counts, unnest(xpath('//row/*', result)) as c(x);

For the pagila demo database this returns:

table_schema | table_name                 | column_name | max_length
-------------+----------------------------+-------------+-----------
public       | actor_info                 | film_info   |        830
public       | customer_list              | notes       |          6
public       | customer_list              | name        |         21
public       | film                       | description |        130
public       | film_list                  | actors      |        216
public       | film_list                  | description |        130
public       | nicer_but_slower_film_list | actors      |        201
public       | nicer_but_slower_film_list | description |        130
public       | sales_by_store             | manager     |         12
public       | sales_by_store             | store       |         19
public       | staff_list                 | name        |         12

The above first generates one statement per table containing one max(length(..)) for each column.

The result is then parsed and turned into rows again. It would be possible to aggregate that back into a single JSON.

To get one row per table, with the column information as a JSON value, the following can be used:

with counts as (
  select table_schema,
         table_name,
         query_to_xml(format('select to_jsonb(t) as cols from (select %s from %I.%I) t', string_agg(format('max(length(%I)) as %I', column_name, column_name), ', '), table_schema, table_name),
                      false, true, '') as result
  from information_schema.columns
  where table_schema = 'public'
    and data_type = 'text'
  group by table_schema, table_name
)
select table_schema,
       table_name,
       (xpath('//row/cols/text()', result))[1]::text::jsonb as column_sizes
from counts;

For the pagila demo database this returns:

table_schema | table_name                 | column_sizes
-------------+----------------------------+------------------------------------
public       | actor_info                 | {"film_info": 830}
public       | customer_list              | {"name": 21, "notes": 6}
public       | film                       | {"description": 130}
public       | film_list                  | {"actors": 216, "description": 130}
public       | nicer_but_slower_film_list | {"actors": 201, "description": 130}
public       | sales_by_store             | {"store": 19, "manager": 12}
public       | staff_list                 | {"name": 12}


Oracle: Can a user log in without a password?

Possible solutions:

Use a Secure External Password Store (Oracle Wallet): http://docs.oracle.com/database/121/DBSEG/authentication.htm#CHDHGAIJ

You can use the stored credentials without specifying a username or password. Connection string: /@orcl.

REMOTE_OS_AUTHENT: http://docs.oracle.com/cd/E18283_01/server.112/e17110/initparams208.htm

Not recommended, insecure, mentioned just for completeness, do not use it.

Kerberos Authentication: http://docs.oracle.com/database/121/DBSEG/asokerb.htm

You can use your directory credentials for logging in without specifying your username or password. Connection string: /@orcl.

SSL Authentication: http://docs.oracle.com/database/121/DBSEG/asossl.htm

You own a certificate and you will be authenticated by that, without specifying a username or password. Connection string: /@orcl.

Operating system authentication based on group membership. Works only locally, and for privileged users. Connection string: / as sysdba, / as sysoper, / as sysbackup, / as sysdg, / as syskb.


What exactly is a "thread" in thread_cache_size, Thread_connected and thread_created?

On *nix, a MySQL thread is a relatively heavy-weight "process".  You can see the "processes" in top or ps.

For Windows, thread_cache_size = 0 is OK.  This is because threading is done differently.

On *nix, thread_cache_size should be a modest number, say, 20.  It is the maximum number of unused processes to keep open.  Suppose you have a burst of activity and briefly peak at 30 threads running (Threads_connected), but then 7 disconnect.  That leaves 23 connected, plus the cache of 7 "processes" not yet terminated (Threads_cached).  Let's say another 18 disconnect.  You are down to 5 running and 20 (the cache size) held in reserve.

A "thread_pool" is something different.  Furthermore, Oracle and MariaDB implemented different things here.

The STATUS values of Threads_cached/connected/created/running give a peak into whether the VARIABLE thread_cache_size needs tuning.

Threads_created/Uptime is how fast a new process is being created because the cache is too small.  Recommend keeping this under 3/second.


datetime index skipped for same query different params

From the looks of the two EXPLAIN plans, you may need another index.
From the WHERE clause of
SELECT count(*) as total_students FROM students s,subjects b
WHERE s.subjectid = b.subjectid AND s.attended>'2012-05-22 00:00:00'
AND s.classid=47;

I suggest creating the following compound index
ALTER TABLE subjects ADD INDEX subjectid_attended_ndx (subjectid,attended);

Give it a Try !!!
CAVEAT
As suggested in the comment from @AaronBertrand, the cardinality of the index may be causing random results depending on different values for subjectid (47 vs 43 vs some other value).


Why the table gets deleted by the server in mysql?

Reading the log seems to make the problem fairly apparent:

2013-11-27T15:08:08.791992Z 9 Query drop table devicedata

That's not mysql dropping the table.  That's your application dropping the table.

There's another line in the log that is very suspicious:

 ,s.Plan_Hash_Value FROM V$sql s WHERE s.Sql_Text LIKE 'SELECT /*+ FIRST_ROWS XX */

V$sql is not a valid table name, at least not without backticks.  It sounds like the application may be trying to build the table name from a string and building the string incorrectly... when that query fails, it drops the table in some kind of misguided attempt at "recovery."

Search the code for the word "drop."  MySQL has no concept of dropping a table because it's too full.


skip columns while using LOAD command mysql

From the documentation:

  You can also discard an input value by assigning it to a user variable
  and not assigning the variable to a table column:

LOAD DATA INFILE 'file.txt'
  INTO TABLE t1
  (column1, @dummy, column2, @dummy, column3);


Create View For All Table Names Listed In Table

Untested... but something like this:

Declare @employee varchar(100), @sql varchar(4000), @location varchar(300)

select RTRIM(LTRIM(name)) As employee
INTO #TempTable1
from alphawhiskeycharlie
where name LIKE 'AL%'
AND (terminationdate IS NULL
OR CAST(terminationdate As Date) >= GetDate()-90)
ORDER BY RTRIM(LTRIM(name)) ASC

Declare c1 Cursor For

Select employee
FROM #TempTable1

Open c1

Fetch Next From c1 Into @employee

Set @location = (Select fulltablelocation from servername.databasename.dbo.tableinformation where employeename = @employee)

Set @sql = 'IF OBJECT_ID('TestView', 'V') IS NOT NULL '
                + 'DROP VIEW TestView '
                + 'Create View [TestView] As '
While @@FETCH_STATUS = 0
  Begin
    Set @sql = @sql
                +'Select '''+@employee+''' As ''employee'', '
                +'COUNT(employeeID) As [TotalEmployees] '
                +'From '+@location+' '
                +'where employeename is not null

                UNION '

    Fetch Next From c1 Into @employee

End

-- remove the last UNION
Set @sql = Substring(@sql,1,len(@sql)-10)

Print(@sql)
exec(@sql)


Oracle date not returning the right data

The to_date function creates a date with a time. If I remember well this is 12:00. If the date in your table has a different time then this is also taken into account. When you want to test dates then you better put the trunc() function around your date like:

select as_of_dt
from   budget
where  trunc(as_of_dt) <= to_date('2016-05-15', 'yyyy-MM-dd');

Now the time part is the same in both dates.


iSeries Linked-Server SQL 2012 performance issue

Community Wiki answer originally left as a comment by the question author

Resolution: I unchecked “Automatically set processor affinity mask for all processors” and checked the individual CPUs.  I had also already changed the MaxDOP =  0 (it was even faster than 1 or 3).

Results are blazing fast!  So the bug must be that the "Automatically set processor affinity mask for all processors" box does not actually use all the CPUs, it must only see one of them.  Manually selecting all the CPUs is required.  This was true for both the 2008 R2 and 2012 servers.


How to recover SA account password without restoring it?

There is no way you can retrieve the lost sa password without resetting it. Relevant SO thread here


Why am I NOT getting a mutating table error in trigger?

There is an exception. When you define a before insert, row-level trigger on a table and issue a single row INSERT statement, the table is mutating error will not be raised. But if you define the same kind of trigger and issue a multi-row INSERT statement, the error will be raised. Here is an example:

SQL> create table TB_TR_TEST(
  2    col1 number,
  3    col2 number
  4  )
  5  ;

Table created

SQL> create or replace trigger TR_TB_TR_TEST
  2  before insert on TB_TR_TEST
  3  for each row
  4  begin
  5    SELECT max(col1) + 1 INTO :NEW.col1
  6      FROM TB_TR_TEST;
  7    UPDATE TB_TR_TEST SET col2 = 5000;
  8  end;
  9  /

Trigger created

Here is a single-row insert statement, which won't raise mutating table error:

SQL> insert into TB_TR_TEST(col1, col2) values(1,2);

1 row inserted

SQL> insert into TB_TR_TEST(col1, col2) values(3,5);

1 row inserted

SQL> commit;

Commit complete

Here is a multi-row insert statement, which will raise mutating table error:

SQL> insert into TB_TR_TEST(col1, col2)
  2    select 1, 2
  3      from dual;

insert into TB_TR_TEST(col1, col2)
  select 1, 2
    from dual

ORA-04091: table HR.TB_TR_TEST is mutating, trigger/function may not see it
ORA-06512: at "HR.TR_TB_TR_TEST", line 2
ORA-04088: error during execution of trigger 'HR.TR_TB_TR_TEST'


Should running number be separated column from primary key in database?

Your Primary Key is a unique key and should be sufficient to find the records that you need.  However, if you need a sequential number for other identifying reasons, then YES you should make it a separate unique value.

I personally would skip the GUID key and just use the Integer, but that is a choice you will need to make.

Getting a sequential set of keys without gaps will require some code.  Are you using Microsoft SQL Server or some other version.  If MS SQL Server, look at:

http://msdn.microsoft.com/en-us/library/ff878058.aspx

This is a discussion of using Sequence Numbers, with some warnings about the limitations.


Schema and indexes are only one aspect of query plan and performance.  Your statement "but with different data" is likely the source of the difference. The number of rows and the distribution of data is essential to the query optimizer.  If you have significantly more rows in D2, or if the data is of entirely different characteristics (wider or narrower range of values), then you should expect to see different performance and execution plans.

For each set of statistics, SQL Server keeps a maximum of 200 samples. As the rows in the tables grow and the more irregular the distribution of values the more likely it is that SQL Server will not have enough information to generate optimal execution plans.  That's where the use of filtered indexes and statistics comes into play.

If this is a parameterized query you may also be running into a parameter sniffing problem.  Note that if you're using local variables the calculation changes also.


Not really an answer - it's not possible to do it.

2 work arounds that we considered:

Creating a Stored Proc/SQL Agent Job that could be executed by the user that did the backup
Convince the business that they can trust us to do their backups for them

We went with option #2, and scheduled a copy-only backup to run every week on payroll day, and also at the end of the financial year. We told them that we can also run the backup whenever they ask us to do it. We also demonstrated that we can restore any database up to the minute based on our RPO. It made some people in HR disappointed, but the manager signed off on it.

We also disabled their access to backup to prevent any accidents.


If your database is in the full recovery model, you should immediately start taking log backups along with the full backups. For simple backup recovery model, you will not need log backups. The frequency depends on your required SLA (RTO & RPO), more on it here.

Second, you should definitely be automating your backup tasks - both full and log backups (if you are taking log backups). There are hundreds of reasons that can justify automating the tasks, my favorite one is I don't have to run/repeat the task again in a well planned automation.

In order to automate your backups using SQL Server Agent jobs, you can either write your won scripts, test it and implement to suit your environment, or pick up one of the freely available scripts and modify to suit your need. My favorite is Ola Hallengren's free sets of scripts, which he publishes and updates on his website. These scripts have been around for a while, and are well tested in various SQL Server environments.

Restore

Assuming your database is in full recovery mode, and you have taken your full backups and log backups, following are a simplified steps for database recovery.

Perform the tail-of-the-log backup to backup with NORECOVERY
option. This is the log generated since the last log backup was
executed.
Restore the most recent full backup with NORECOVERY option.
Restore all the log backups since last full backup beginning the
first log backup done after the full backup to the most recent log
backup with NORECOVERY option.
Restore the tail-of-the-log backup with NORECOVERY option.
Recover the database with RECOVERY option.

Note: If differential backup is part of your backup plan, you may have to restore the most recent differential backup before you restore the log backups, if the most recent differential backup was taken after the most recent full backup.

The database recovery could be more complex depending on the physical layout of your database files, and your recovery objectives etc. You can learn more about backup and recovery in SQL Server book online. Aslo, Paul Randal has a series of blog post on backup and recovery on his Accidental DBA series.


MongoDB: queue vs. realtime write/update

There is no priority system currently for writes (or reads, though you can send reads to secondaries) - the closest thing you will get is yielding.  For long running operations and for operations that it predicts will page in data from disk, MongoDB will yield the lock and allow other operations through, essentially interleaving operations:

If you wanted to make sure that the less important writes are throttled somewhat you could rate limit them by ensuring they are replicated W=2, REPLICAS_SAFE, or similar writes (depending on your driver).  See here for the command behind such implementations on the MongoDB side - take a look at your driver docs for the relevant equivalent there.

http://www.mongodb.org/display/DOCS/getLastError+Command#getLastErrorCommand-%7B%7Bw%7D%7D

There would then be a slight delay as the write waits for replication out to the secondaries, allowing your other, more important writes their shot at the write lock.

http://www.mongodb.org/display/DOCS/How+does+concurrency+work

In terms of the future, with 2.2 due out shortly, you will get database level locking, so as long as your 2 different profiles/priorities are in different databases you should have no lock contention (IO/RAM contention may still exist, of course).

Finally, in terms of other things to look at, for the line by line type read, I would look at capped collections and tailable cursors - see if they fit your use case:

http://www.mongodb.org/display/DOCS/Tailable+Cursors


Can bad table-scan queries slow down nice index-covered queries at the same instance?

I can definitely say "Yes, your bad query can impact Ivan's query" due to resource competition at instance level.

I have multiple databases in one production sql server box, I have seen many times that a bad query (actually a SP) in one database that caused both CPU and I/O usage skyrocketed and impact all other user sessions connected to other databases.

Besides CPU and I/O usage, one particular case is the negative impact a bad query brings to TempDB performance, and when TempDB is heavily utilized, it is a direct hit to all other queries that need to use TempDB for their executions.


The UDT has lot of scans as per the query plan,please try to create UDT with clustered index (primary key)

https://stackoverflow.com/questions/4451052/how-to-add-an-index-or-primary-key-to-a-user-defined-table-type-in-sql-server


Different results from select and from materialized view

They give different results because they do different things.

One does the "distinct on" over only those rows which meet user_id = 2779, the other returns only those those rows where the result of the "distinct on" happens to have user_id = 2779.

So if one group of the values yielded by expression "p" has a row where user_id = 2779 but also other rows, and the row where user_id = 2779 is not the one selected to represent the group, then no row for that group is returned.  Since you did not specify an ORDER BY, it is indeterminate which row is selected to represent each group.

You can reproduce this without a materialized view, it is the difference between:

select * from (<query> where user_id=2779) foobar;

and

select * from (<query>) foobar where user_id=2779;


Is there any benefit to SCHEMABINDING a function beyond Halloween Protection?

Yes.
Failing to specify WITH SCHEMABINDING means SQL Server skips the detailed checks it normally makes on the function body. It simply marks the function as accessing data (as mentioned in the link given in the question).
This is a performance optimization. If it did not make this assumption, SQL Server would have to perform the detailed checks on every function invocation (since the unbound function could change at any time).
There are five important function properties:

Determinism
Precision
Data Access
System Data Access
System Verification

For example, take the following unbound scalar function:
CREATE FUNCTION dbo.F
(
    @i integer
)
RETURNS datetime
AS
BEGIN
    RETURN '19000101';
END;

We can look at the five properties using a metadata function:
SELECT
    IsDeterministic = OBJECTPROPERTYEX(Func.ID, 'IsDeterministic'),
    IsPrecise = OBJECTPROPERTYEX(Func.ID, 'IsPrecise'),
    IsSystemVerified = OBJECTPROPERTYEX(Func.ID, 'IsSystemVerified'),
    UserDataAccess = OBJECTPROPERTYEX(Func.ID, 'UserDataAccess'),
    SystemDataAccess = OBJECTPROPERTYEX(Func.ID, 'SystemDataAccess')
FROM (VALUES(OBJECT_ID(N'dbo.F', N'FN'))) AS Func (ID);

The two data access properties have been set true, and the other three are set false.
This has implications beyond those that might be expected (use in indexed views or indexed computed columns, for example).
Effects on the query optimizer
The Determinism property in particular affects the query optimizer. It has detailed rules concerning the types of rewrites and manipulations it is allowed to perform, and these are very much restricted for non-deterministic elements. The side-effects can be quite subtle.
For example, consider the following two tables:
CREATE TABLE dbo.T1
(
    SomeInteger integer PRIMARY KEY
);
GO
CREATE TABLE dbo.T2
(
    SomeDate datetime PRIMARY KEY
);

...and a query that uses the function (as defined previously):
SELECT *
FROM dbo.T1 AS T1
JOIN dbo.T2 AS T2
    ON T2.SomeDate = dbo.F(T1.SomeInteger);

The query plan is as expected, featuring a seek into table T2:

However, if the same logical query is written using a derived table or common table expression:
WITH CTE AS
(
    SELECT *, dt = dbo.F(T1.SomeInteger)
    FROM dbo.T1 AS T1
)
SELECT *
FROM CTE
JOIN dbo.T2 AS T2
    ON T2.SomeDate = CTE.dt;

-- Derived table
SELECT
    *
FROM
(
    SELECT *, dt = dbo.F(T1.SomeInteger)
    FROM dbo.T1 AS T1
) AS T1
JOIN dbo.T2 AS T2
    ON T2.SomeDate = T1.dt;

The execution plan now features a scan, with the predicate involving the function stuck in a Filter:

This also happens if the derived table or common table expression is replaced by a view or in-line function. A FORCESEEK hint (and other similar attempts) will not succeed:

The fundamental issue is that the query optimizer cannot reorder nondeterministic query elements as freely.
To produce a seek, the Filter predicate would need to be moved down the plan to the T2 data access. This movement is prevented when the function is non-deterministic.
Fix
The fix for this example involves two steps:

Add WITH SCHEMABINDING
Make the function deterministic

The first step is trivial. The second involves removing the non-deterministic implicit cast from string to datetime; replacing it with a deterministic CONVERT. Neither is sufficient on its own.
ALTER FUNCTION dbo.F
(
    @i integer
)
RETURNS datetime
WITH SCHEMABINDING
AS
BEGIN
    -- Convert with a deterministic style
    RETURN CONVERT(datetime, '19000101', 112);
END;

The function properties are now:

With the optimizer freed up, all examples now produce the desired seek plan.

Note that using a CAST to datetime in the function would not work, because it is not possible to specify a conversion style in that syntax:
ALTER FUNCTION dbo.F
(
    @i integer
)
RETURNS datetime
WITH SCHEMABINDING
AS
BEGIN
    -- Convert with a deterministic style
    RETURN CAST('19000101' AS datetime);
END;

This function definition produces the scan plan, and the properties show it remains non-deterministic:


SQL Performance - One counter to rule them all - identify CPU/MEM/DISK/NET pressure

No. Say you have a server with:

High PAGEIOLATCH waits
High IO stall times
Low Page Life Expectancy (say, consistently under 30)
128GB RAM

You could fix this scenario with:

Index tuning - because maybe the queries are scanning a table too large to fit in memory
Query tuning - because maybe they're using non-sargable predicates, or not caching
Configuration - because maybe some wacko set max server memory too low, or we're using the wrong Cardinality Estimator for our code
Adding memory - but now you have to check version & edition, because your Windows or SQL Server may not support more memory
Faster storage - but this is one heck of an expensive fix

There's no one metric that tells you which of those solutions is the right one. Welcome to performance tuning. Ain't no easy button here.


Multi user, allow user to read data and add their own data

There are many ways to model this, but I would choose a single ingredient table that contains all ingredients. Add a column user_id that is a foreign key to the table of users. Add a special "public" user (perhaps with id 0) that owns all the public ingredients.
Then you could create a unique constraint on ingredient(id, user_id) and reference that in the recipeingredient table. The only drawback is that you will need a second user_id column in recipeingredient that references recipe(id, user_id). Perhaps call one user ID column recipe_user_id and the other ingredient_user_id.
Then it is easy to add a check constraint on recipeingredient that makes sure that (recipe_user_id = ingredient_user_id OR ingredient_user_id = 0), so that nobody can use somebody else's private ingredients.


If you see the slow log and general log in /var/lib/mysql/mysql, it means that in the config files for the old server, these logs were specified without directories, and that in the old server, the datadir is actually configured to be /var/lib/mysql/mysql, and not the default /var/lib/mysql. That would explain the appearance of the logs there. Check the old servers .cnf file and make sure the datadir matches before attempting the restore again.
After you figure out where the correct datadir was on the old server, match that in the new server, and do the restore. Be sure to set ownership after restoring: "chown -R mysql:mysql /var/lib/mysql" (or wherever the datadir ended up)
Also, keep in mind that mysql_upgrade or update do not recreate much from scratch. They mostly modify system objects to match the new version, and leave others intact, so if something is outright missing (like an entire system schema) it probably won't work.


If create three shards(of 200 GB each) on single node, will it help in search performance ?

Short Answer: No.

Info from Comments:

16GB of RAM.

The important query is a "search", but it is rather unbounded.  Hence, we need to assume that the whole table (600GB) needs to be scanned.

So, why not have multiple instances of MySQL, each with a fraction of the data?  They could work in parallel, couldn't they?

Long Answer:

I/O is much more costly (10x?  100x?) than CPU or context switching or looking a cache.  Of the 16GB, you will allocate perhaps 11GB for  innodb_buffer_pool_size, which is the main "cache" in MySQL.  There is no way to fit 600GB into 11GB, so there will be I/O.  If you shard across ~60 separate machines, then you could avoid I/O.  Or if you had 700GB of RAM on the one machine.

So, on a single machine, the parallelism is defeated because of there being only one access to disk, and each parallel thread need to spend most of its time fetching data from disk.  You would see the CPU cores sitting there twiddling their thumbs waiting for I/O.

What if an index could be used?

Why bother with sharding?  With a usable index, the query is likely to be fast because it won't be touching much of the 600GB, hence doing very little I/O.

And if you have lots of different connections, MySQL (with the help of the OS) is happy to farm them out to different CPUs.

Hence, I/O is not a bottleneck; CPU is not a bottleneck?  The sharded system is complex, but for no benefit.

When is Sharding needed?

If you need "read scaling" -- that is, too many clients are accessing MySQL, and the system is overloaded -- use Replication.  Each Slave has a copy of the data.  You can add as many Slaves as needed to handle the load.  Note:  Each Slave needs to be on its own hardware.

If you need "write scaling" -- that is, the writes, not the reads, are the bottleneck -- then you might need multiple servers and do "sharding".

Even with either of those situations, I suggest that there may be ways to improve the performance of the queries / indexes / ETL / etc to tame it.


Error Code: 1728. Cannot load from mysql.db. The table is probably corrupted

I have seen this error many times when installing earlier versions of MySQL 5.7. I am stunned you are still getting this error.

Prior to MySQL 8.0, some of the tables in the mysql schema are MyISAM.

Unfortunately, MyISAMtables get corrupt very easily.

EXAMPLE: The header of the .MYD file holds the count of open file handles. If a MyISAM table is closed and has a nonzero value for open file handles, the MyISAM storage engine considers the table corrupt.

Solution: Just run the following command:

mysql> REPAIR TABLE mysql.db;

I suggested this  before : Mar 15, 2012 : Why do MySQL tables crash? How do I prevent it?


The docs say

  In some cases, MySQL cannot use indexes to resolve the ORDER BY,
  although it still uses indexes to find the rows that match the WHERE
  clause. These cases include the following:

and then go on to list many circumstances that prevent MySQL from using indexes. Among them are

  You use ORDER BY on different keys
  The key used to fetch the rows is not the same as the one used in the ORDER BY

and there are probably others.

To avoid a filesort, you have to find a way to get what you want without running into any of those (many) documented conditions.

You can also

  Increase the size of the sort_buffer_size variable.
  Increase the size of the read_rnd_buffer_size variable.
  Use less RAM per row by declaring columns only as large as they need to be to hold the values stored in them.
  Change tmpdir to point to a dedicated file system with large amounts of free space.

(Same link.)

Also see docs for Optimizing LIMIT Queries, which interacts with ORDER BY.


Suppressing non-ANSI warnings in SQLCMD script

I was able to reproduce the issue. Problem is step 1 is refering to steps that are not created yet. Following code will generate the same error you are getting.

USE [msdb]
GO

BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'test333',
        @enabled=1,
        @notify_level_eventlog=0,
        @notify_level_email=0,
        @notify_level_netsend=0,
        @notify_level_page=0,
        @delete_level=0,
        @description=N'No description available.',
        @category_name=N'[Uncategorized (Local)]',
        @owner_login_name=N'domain\account', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'GetDate',
        @step_id=1,
        @cmdexec_success_code=0,
        @on_success_action=4,
        @on_success_step_id=2,
        @on_fail_action=4,
        @on_fail_step_id=3,
        @retry_attempts=0,
        @retry_interval=0,
        @os_run_priority=0, @subsystem=N'TSQL',
        @command=N'select getdate()',
        @database_name=N'master',
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'success',
        @step_id=2,
        @cmdexec_success_code=0,
        @on_success_action=1,
        @on_success_step_id=0,
        @on_fail_action=2,
        @on_fail_step_id=0,
        @retry_attempts=0,
        @retry_interval=0,
        @os_run_priority=0, @subsystem=N'TSQL',
        @command=N'Print ''i am success''',
        @database_name=N'master',
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'failure',
        @step_id=3,
        @cmdexec_success_code=0,
        @on_success_action=2,
        @on_success_step_id=0,
        @on_fail_action=2,
        @on_fail_step_id=0,
        @retry_attempts=0,
        @retry_interval=0,
        @os_run_priority=0, @subsystem=N'TSQL',
        @command=N'print ''i am failure''',
        @database_name=N'master',
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

I commented out 4 lines on the 1st step. Added a section calling sp_update_jobstep. This code will not throw the error you are getting.  I am only pasting modified code here.

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'GetDate',
        @step_id=1,
        @cmdexec_success_code=0,
        --@on_success_action=4,
        --@on_success_step_id=2,
        --@on_fail_action=4,
        --@on_fail_step_id=3,
        ........
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

Add this at the end of creating last step and before running sp_add_jobserver.

EXEC msdb.dbo.sp_update_jobstep @job_id=@jobid, @step_id=1 ,
        @on_success_action=4,
        @on_success_step_id=2,
        @on_fail_action=4,
        @on_fail_step_id=3


Try like

SELECT MAX(A.count_supplier), A.id FROM
(
    SELECT supplier_id as id, COUNT(*) AS count_supplier
    FROM library.lms_book_details
    GROUP BY supplier_id
) A GROUP BY A.id;

Alias inner COUNT(*) as something different than COUNT as it is good practice to not use it as alias , As i have used count_supplier.


transactional replication, failing with communication link failure half way through bcp

If the link is bad, you must overrride -QueryTimeOut and -KeepAliveMessageInterval parameters. Check the SQL Server Agent job, identify the replication Job, then right-click Properties, then Steps, Edit and at the end of Commend just add -LoginTimeOut 120 -QueryTimeOut 7200 -KeepAliveMessageInterval 600


How to determine why connection creation lasts so long?

I answer myself - it was SSL set to on.  Setting ssl to off in postgresql.conf solved the problem.


Why does Inner Join introduce a hidden sort?

I don't think the join is producing the sort

The group by sum is producing the sort
See the order by in the query plan

This might be faster
It breaks if the purpose is multiple accounts with same name

SELECT ah.Id, ah.FirstName, ah.LastName
FROM
    AccountHolders AS ah
JOIN
    [dbo].[Accounts] AS a
ON
    ah.Id = a.AccountHolderId
GROUP BY
    ah.Id, ah.FirstName, ah.LastName
HAVING SUM(a.Balance) > @suppliedNumber

Or

SELECT ah.Id, ah.FirstName, ah.LastName
FROM AccountHolders AS ah
JOIN ( select AccountHolderId
       from [dbo].[Accounts] A
       GROUP BY Id
       HAVING SUM(Balance) > @suppliedNumber
     ) a
 on a.AccountHolderId = ah.ID


Oracle UPDATE followed by SELECT in LinqPad

Thank you all for your comments.  You deserve the credit for this answer.  To summarize:

The Oracle driver for LinqPad that I'm using (dotConnect Direct Mode based on OCI 8) wants the statement to be formatted like this (yes, wihout any semicolons):

UPDATE schedule_amounts
SET ANNUAL_RATE = 44000
WHERE SCHEDULE_ID = 'LCSD'
    and SCHEDULE_NO = 2014
    and SCHEDULE_LEVEL = 100
    and SCHEDULE_STEP = 17
GO
SELECT * FROM schedule_amounts


PgTap, unit test a foreign key is deferrable

Ok, it appears there are no methods to test such thing with PgTap for now so I ended up with

SELECT results_eq(
    $$select condeferrable, condeferred from pg_catalog.pg_constraint where conname = 'default_article_id_fk'$$,
    $$values ('t'::bool, 'f'::bool)$$,
    '"default_article_id_fk" is deferrable initially immediate.'
);

That does the job.


Oracle - How to issue desc command in a procedure within package?

DESC / DESCRIBE is a client command (eg SQL*Plus) and not available in PL/SQL.

You can try querying the user_arguments or all_arguments view. It is relatively simple as long as you don't have overloaded procedure / functions and nested data types or weird procedure / variable names.


Why are there differences in data when comparing table to itself? TSQL

The only thing that comes to my mind is that you are not using the same data type for the column productSales on both tables.

For example, if your data type on compareTempA is float and on compareTempB is decimal, that's exactly what should happen.

By the way, this happens even if both columns are float, due to the fact that float is not a precise datatype.

I suggest that you change your datatype to a precise one, like decimal.

"Approximate-number data types for use with floating point numeric data. Floating point data is approximate; therefore, not all values in the data type range can be represented exactly. The ISO synonym for real is float(24)."
Source: https://docs.microsoft.com/en-us/sql/t-sql/data-types/float-and-real-transact-sql?view=sql-server-2017

Similar problems:

Same problem here - https://stackoverflow.com/questions/16149966/comparing-2-float-values-in-sqlserver

Best Pratices Red Gate - https://documentation.red-gate.com/codeanalysis/best-practice-rules/bp023

Another question about floats and precise comparisions - https://stackoverflow.com/questions/10737004/should-we-use-float-as-primary-key-in-sql-server


Creating an entity-relationship diagram for a recipes business domain

It's difficult to say if the ER is correct not knowing what is acceptable entered data.  Based on your current design, including UserID in UserRecipes is unnecessary since you can get that information through RecipeID and joining it with the Recipes table and then using fkUserID.

But I think if Recipes table is supposed to be a dish like "Lasagna" and different users can submit their own lasagna recipes and you wish to capture that in UserRecipes, you need to probably move at least "Preptime", "Cooktime", "Ingredients", "Tools" and "Photos" to UserRecipes table.  Then establish a many-to-many relationship between Recipes and Users in UserRecipes.  Then remove fkUserID from Recipes and keep UserID in UserRecipes.

Just a side-note, I'd suggest keeping a more consistent naming convention for naming your foreign keys.  In some cases, you are using CatID as a foreign key for CategoryID and then you're using fkUserID as a foreign key for UserID.


How to search for a specific column name in all the tables in MySQL Workbench?

You can use the INFORMATION_SCHEMA database and the COLUMNS table in particular Example of use:

SELECT
    table_name,
    column_name,
    data_type,
    ordinal_position

FROM  INFORMATION_SCHEMA.COLUMNS

WHERE table_schema = 'myDatabase'     --- the database you want to search
  AND column_name = 'name' ;          --- or: column_name LIKE '%name%'


Using Dynamic Data Masking in SQL Server set Custom String

You cannot refer to data in some other column so if that 1 should come from the MemberId column, then you are out of luck. What you have to play with are the prefix and suffix, and those need to be from the same column as the masking is defined on.


Postgres role does not inherit permissions of superuser role via membership in that role

SUPERUSER doesn't inherit; it's an attribute of a role rather than a permission.

  The role attributes LOGIN, SUPERUSER, CREATEDB, and CREATEROLE can be thought of as special privileges, but they are never inherited as ordinary privileges on database objects are. You must actually SET ROLE to a specific role having one of these attributes in order to make use of the attribute.

See https://www.postgresql.org/docs/current/role-membership.html


Limit the number of records depends on the part of primary key

And I want to limit the number of referees on a single match to three,
  How can i do that?

One method is with a trigger:

CREATE TRIGGER tr_match_referee
ON dbo.match_referee
FOR INSERT, UPDATE AS
IF EXISTS(
    SELECT 1
    FROM inserted
    JOIN dbo.match_referee WITH (READCOMMITTEDLOCK) ON
        match_referee.id_match = inserted.id_match
    GROUP BY match_referee.id_match
    HAVING COUNT(*) > 3
    )
BEGIN
    RAISERROR('No more than 3 referees allowed per match', 16, 1);
END;

Another approach is to add a column with a unique referee number per match along with a unique and check constraint. You would need to assign the referee number when assigning a ref to a match.

ALTER TABLE dbo.match_referee
    ADD match_referee_number int NOT NULL
    CONSTRAINT CK_match_referee_match_referee_number
    CHECK (match_referee_number BETWEEN 1 AND 3);
ALTER TABLE dbo.match_referee
    ADD CONSTRAINT UN_match_referee_id_match_match_referee_number
    UNIQUE(id_match, match_referee_number);


As the error says, the database must be in the Rollforward pending state for you to use the rollforward command. However, while in this state the database is not accessible.

You don't explain what it is that you are trying to achieve so it's hard to provide a precise solution. If this is a one-time activity you will need to restore your DR database from a backup and run rollforward. If your intent is to keep the DR database up to date on an ongoing basis, consider setting up HADR, which automates the process of transferring and applying logged changes to the standby database.


Timeout expired error. Is it because of a query or server settings?

Timeout settings are a configuration of the client connection, so are either set by the client application, or via the connection string. Look at how your applications is instantiating the database connections, or perhaps in your application config files that control how those connections are created.

But increasing the timeout is just treating the symptom. Determining why the timeout occurred is a more difficult problem, and may require some detective work. Here are some avenues of investigation:

Is the error always throwing on one or two particular queries? You might need to do some query tuning.
Is the error always occurring at the same time each hour? Does that correspond to maintenance running overnight, perhaps in scheduled SQL agent jobs?
You might run SQL trace/profiler overnight, filtered by query duration, to see if you can better identify these long-running queries.


As long as the bottleneck is the amount of RAM you can keep scaling up. MyISAM has some bug related to large key buffer size but InnoDB handles massive buffer pool just fine (even in the TB range). I see you already have 4 buffer pool instances. By increasing the buffer pool it's good to increase this too. The only "issue" you may experience is if you also have larger innodb log files and more dirty pages in the buffer pool then stopping the database may take longer and in case of a crash innodb recovery will take longer.

For scaling out the most efficient simple measurement is the concurrently running threads: show global status like 'Threads_running'; You can take a benchmark and see where is the tipping point for TPS on your system. (Usually with increasing concurrency you will see transactions / second increase for a while until it hits different contention issues and then drop usually quite quickly, you can see a sample benchmark and what the results look like here: https://www.percona.com/blog/2014/01/23/percona-server-improve-scalability-percona-thread-pool/) You want to keep it always under that level. Measuring this will give you a good indication of when will you need new server and how many.


How do I tell what is filling up the transaction log?

Start by investigating what is holding up the log reuse. Read Factors That Can Delay Log Truncation, look at log_reuse_wait_desc value is sys.databases. Based on what you find there, there could be several potential actions.


Password of the oracle user in oracle linux?

The oracle-rdbms-server-12cR1-preinstall package includes a bash script that creates the oracle user, if not already existing, using the useradd command.

In this case in log file /var/log/oracle-rdbms-server-12cR1-preinstall/results/orakernel.log you should see this entry:

  Please set password for oracle user

You can run the following command as root to assign a password:

# passwd oracle


timeline 2 of the primary does not match recovery target timeline 1

The issue is covered in this detailed blog.

In short, the master and the standby servers must all have archive_mode = on and an archive_command, until 9.3 which removes this requirement.


If the game area is strictly static, then my preference would be to use the coordinates as the primary key.

On postgres, I'd probably do that using a composite type. I don't think there is a direct equivalent in SQL Server - hopefully someone will correct me if I'm wrong.

Whether this will simplify the problem or not depends on how exactly you have implemented your searching algorithm - can you post more details on that?


sqlite 3.4 updating table with data from different table - changed with 3.3 - newbie question

You need to gove sqllite the chance to know which column from which table you mean
UPDATE GULLfromLG
   SET issbn = (
   SELECT issbn
  FROM BIBinfo
 WHERE bibno = GULLfromLG.bibno)
[Example](http://sqlfiddle.com/#!5/c6687/1)


Error on Oracle calling external procedures

The following configuration works for me on Oracle 11.2.0.2:

C prog:

[oracle@node1 ~]$ cat exec_hmmftg.c
#include <stdio.h>

int exec_hmmftg ( char *p1 , char *p2, char *p3 )
{
  return 5;
}
[oracle@node1 ~]$

Compile it, and make sure it's placed in $ORACLE_HOME/bin or $ORACLE_HOME/lib (configuration can be changed to allow other directories) - compiler flags will differ for AIX too.

gcc -shared -o $ORACLE_HOME/lib/exec_hmmftg.so exec_hmmftg.c

Configure EXTPROC in the listener.ora file:

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.1.6)(PORT = 1521))
      )
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = PHIL112)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
      (PROGRAM = extproc)
      (SID_NAME = PHIL112)
    )
  )

Restart the listener with lsnrctl stop followed by lsnrctl start.

Create the reference to the external library & create the function to interface between PL/SQL and the C prog:

[oracle@node1 ~]$ sqlplus phil/phil

SQL*Plus: Release 11.2.0.2.0 Production on Tue Aug 14 17:51:21 2012

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> CREATE LIBRARY CPROGLIB UNTRUSTED AS '/u01/app/oracle/product/11.2.0/dbhome_1/lib/exec_hmmftg.so            ';
  2  /

Library created.

SQL> CREATE OR REPLACE FUNCTION CALL_C_PROGRAM(P1 IN VARCHAR2, P2 IN VARCHAR2, P3 IN VARCHAR2)
  2      RETURN BINARY_INTEGER
  3      IS EXTERNAL NAME "exec_hmmftg"
  4      LIBRARY CPROGLIB
  5      LANGUAGE C
  6      PARAMETERS(P1 STRING, P2 STRING, P3 STRING, RETURN INT);
  7  /

Function created.

SQL>

Test the function:

SQL> select call_c_program('a','b','c') from dual;

CALL_C_PROGRAM('A','B','C')
---------------------------
                          5

SQL>

Not quite sure what you're doing wrong without seeing the full C you've written. Try and setup this simple case I've made for you, then add functionality bit by bit from there.


This could depend a great deal on the storage engine.

For MyISAM, I think it would be a great idea because you can make data as contiguous as a you want. That would benefit queries involving bulk operations and large range scans. The toolset for compressing and repairing MyISAM to improve the table format can also make access a little better. IMHO concurrent INSERTs could thrive in such a disk environment.

InnoDB is a completely different story.

If you have innodb_file_per_table disabled, data can be scattered throughout ibdata1 and thus scattered amongst the multiple disks. You could run OPTIMIZE TABLE against an InnoDB table and make the data and index pages contiguous, but that could cause two problems:

Makes the ibdata1 grow
Deteriorate performance over time

Don't forget the four types of data that reside in ibdata1:

Table MetaData
Table Data Pages
Index Data Pages
MVCC Data

There is constant writing of MVCC Data to protect data integrity. There is also constant interaction with ib_logfile0 and ib_logfile1 (redo logs). There is constant data flushing every 10 seconds from the InnoDB Buffer Pool back to the . Overall, it is safe to say that the spread of data across multiple disks and the I/O performance can show marginal-to-modest performance improvement until there is enough fragmentation within ibdata1.

Now, if innodb_file_per_table is enabled, things could be a little messier. Here is why:

Once you cleanup ibdata1 and separate all InnoDB tables from ibdata1, you will have a smaller ibdata1. However, each access to an InnoDB table requires navigating its use through ibdata1 ALL THE TIME via Table Metadata. You literally cannot semantically separate the table metadata (ibdata1) from the table (.ibd). Wherever ibdata1 lives, disk I/O must always pass through ibdata1.

Overall effect: Table fragmentation is limited to .ibd files and can be reduced and reorganized with OPTIMIZE TABLE, but no additional I/O performance enhancement is really achieved.

What about putting all .ibd files into a different set of disks apart from ibdata1 and using symlinks? According to Percona, that creates more mayhem.

CONCLUSION

When it comes to using multiple drives over a single drive, I highly favor MyISAM over InnoDB.


Mysql: Insert into JSON column type with GROUP_CONCAT

Use JSON_ARRAYAGG() instead of GROUP_CONCAT(). - akina

It is available only in 5.7.22+

Does it have an implicit DISTINCT? I don't know...if not, subquery with DISTINCT will help.


How to insert default values to a table with not null columns?

Put defaults on the columns, so that your DEFAULT VALUES works.

In your table definitions just put DEFAULT 0 after each column definition - should be easy.


Check constraint for Canadian postal code?

Canadian postcodes are

  in the format A1A 1A1, where A is a letter and 1 is a digit, with a
  space separating the third and fourth characters ... Postal codes do
  not include the letters D, F, I, O, Q or U, and the first position
  also does not make use of the letters W or Z.

So the following should do it.

CHECK (PostCode LIKE REPLACE(REPLACE('Alpha1[0-9]Alpha2 [0-9]Alpha2[0-9]',
                                     'Alpha1',
                                     '[ABCEGHJKLMNPRSTVXY]'),
                             'Alpha2',
                             '[ABCEGHJKLMNPRSTVWXYZ]') COLLATE Latin1_General_Bin) )

Also you should probably use CHAR(7) if you are only allowing values exactly 7 characters long (especially if this column is mandatory).

As the space appears predictably between the third and fourth characters arguably storing this is redundant and it should be added at display time instead. If you decide to go that route then use CHAR(6) and remove the space in the middle of 'Alpha1[0-9]Alpha2 [0-9]Alpha2[0-9]'


How to store Alternate Names for Places or City in Table

The normalised way would be create a master table for town and then create another table for alternate names with a 1 to many relationship. This allows you to have 0..N alternate names. To fully normalise it you would pull all the names out from the town table and put them into the town names table and have a flag which denotes the default or primary name and locality for that town or you could go even further and have a name type column allowing you to further define the name type in a meaningful way.

This happens a lot with names and addresses for people. People have a primary (or legal name) and they also have one or more aliases or other known-as names and the technique of separating the name from the actual person and categorising their name via a type is commonly used - the same for their addresses


So you want to make sure that uuid_generate_v4() is only called once.

Your solution is essentially doing that. Other solutions only vary in the syntax being used.

You could use a common table expression to somehow express that in your query:

with data (id) as (
   select uuid_generate_v4()
)
SELECT *
FROM fruits
  cross join data;

As the CTE only returns a single row, the cross join won't increase the number of rows in the complete result. Alternatively you could use an "implicit" cross join to make it shorter:

with data (id) as (
   select uuid_generate_v4()
)
SELECT *
FROM fruits, data;

Or you could remove the CTE and use the function call in a derived table:

SELECT *
FROM fruits, (select uuid_generate_v4() as id);


How to find the counts of word occurrences in a column?

You need to first split each string into multiple words and count the number of times each word occurs inside the string. Splitting can be done using regexp_split_to_table() which yields one row per word:
select w.word, w.num_occurrences
from the_table t
  cross join lateral (
     select word, count(*) as num_occurrences
     from regexp_split_to_table(lower(t.text_col), '[\s[:punct:]]+') as x(word)
     where word <> ''
     group by word
  ) w

This returns the following given your sample data:
word | num_occurrences
-----+----------------
test |               1
a    |               1
is   |               1
this |               1
test |               2
a    |               2
is   |               1
test |               1
how  |               1
to   |               1
test |               1
more |               1
one  |               1

This can be grouped by num_occurences to get the result you want:
select count(*), num_occurrences
from (
  select w.word, w.num_occurrences
  from the_table t
    cross join lateral (
       select word, count(*) as num_occurrences
       from regexp_split_to_table(lower(t.text_col), '[\s[:punct:]]+') as x(word)
       where word <> ''
       group by word
    ) w
  where w.word = 'test'
) t
group by num_occurrences
order by 1 desc

And the result of that is:
count | num_occurrences
------+----------------
    3 |               1
    1 |               2


You can do

SELECT col1, col2,
       CONCAT(LEAST(col1, col2), '-',
              GREATEST(col1, col2)) concat_result
  FROM table1
 ORDER BY concat_result

Output:

| COL1 | COL2 | CONCAT_RESULT |
|------|------|---------------|
|   A1 |   A2 |         A1-A2 |
|   A2 |   A1 |         A1-A2 |
|   B1 |   A1 |         A1-B1 |
|   A1 |   B1 |         A1-B1 |
|   A1 |   B2 |         A1-B2 |
|   B2 |   A1 |         A1-B2 |
|   A2 |   B1 |         A2-B1 |
|   B1 |   A2 |         A2-B1 |
|   A2 |   B2 |         A2-B2 |
|   B2 |   A2 |         A2-B2 |
|   B1 |   B2 |         B1-B2 |
|   B2 |   B1 |         B1-B2 |

To get distinct values from concatenated result

SELECT DISTINCT
       CONCAT(LEAST(col1, col2), '-',
              GREATEST(col1, col2)) concat_result
  FROM table1
 ORDER BY concat_result

Output:

| CONCAT_RESULT |
|---------------|
|         A1-A2 |
|         A1-B1 |
|         A1-B2 |
|         A2-B1 |
|         A2-B2 |
|         B1-B2 |

Here is SQLFiddle demo


MySQL - Many UPDATEs to run after INSERT

Let us suppose that you have built a reference data set that has two (or more) fields.  For the sake of argument lets call the table geography

Area_Code
City

The MySQL update syntax would be

UPDATE data_table, geography
SET data_table.field2 = geography.City
WHERE data_table.field1 = geography.Area_Code

Potential pitfalls to watch out for are the data types, size and collation discrepancies between data_table.field1 and geography.Area_Code.

If geography.Area_Code is unique and must always be present make sure you stick a primary key on it.

It is probably worth  adding an index to data_table.field1.  Again, if it is mandatory and unique make it a primary key.


msdb.dbo.sp_start_job got stuck in stored procedure

It turns out to be some kind of SQL Server bug (Maybe the SQL Server 2005 is quite old). The stored procedure just ended unexpectedly and does not return any code.

So the problem is solved by moving all the logic before this msdb.dbo.sp_start_job procedure into a separate stored procedure.

Hope it can help anyone who got this same issue.


Encryption/Decryption with stored procedure / view

Note that encryption of modules in SQL Server is not really encryption; it is more like weak obfuscation. Anyone with a search engine and a few minutes to kill can reverse engineer your objects, so encrypting them is almost completely pointless. DENY VIEW DEFINITION is far more effective here (as well as not giving your developers sysadmin and other privilege escalation).

As for the data, SQL Server 2016 will have Always Encrypted (read about it here), it may make a lot of your work easier if you can wait for that. One of the big plusses is that the app can send the data through the provider already encrypted, so it can't be sniffed out by trace or other man-in-the-middle attacks. Be aware of limitations, too, though:

Always Encrypted Limitations
Performance Impact of Always Encrypted
Always Encrypted Performance : A Follow-Up


MSSQL 2016 - Restore SSISDB from MSSQL 2012

I've ran into the same problem after restoring the SSISDB database from a lower SQL version to a higher SQL version; I've ran into:

SSIDDB Upgrade not working or erroring out on the "sql version doesn't match"
Validating packages erroring on customized_logging_levels not existing
all other errors one can encounter...

I noticed the newer SSISDB has more tables, views and different stored procedures. However there's a fairly easy solution, although unconventional.

restore the SSISDB on the "new" SQL (already Integration Services Catalog Enabled)
sp_changedbowner 'sa'
open master key decryption by password = 'yourkeyinserthere'
Alter Master Key Add encryption by Service Master Key
EXEC sp_change_users_login 'update_one', '##MS_SSISServerCleanupJobUser##','##MS_SSISServerCleanupJobLogin##'
ALTER DATABASE [SSISDB] SET COMPATIBILITY_LEVEL = 130 (or the one you need)
Install a CU higher then your current one

Installing the CU does the SSISDB upgrade and you can check that by looking at the table internal.catalog_properties. It will have a line "upgrade from".
Also, what if you are already on the latest CU? Well, uninstall your last CU and reinstall it. That works too.


How to protect MySQL database from sql-injection?

you better use prepared statement from your programming source code, e.g. for PHP use PDO's prepare statement!

Check this - https://stackoverflow.com/questions/60174/best-way-to-prevent-sql-injection-in-php


Can Set-DbaAgReplica filter any SqlInstances out

The command works at the Availability Group level - not database level.   Therefore ALL databases that have been added to that Availability Group will failover at the same time.  You cannot have some of them on the Primary and some of them on the Secondary.

If you need to have different databases failover at different times then put them in different Availability Groups.

So NO, it doesn't set all the databases to auto-failover, it sets the Availability Group to auto-failover.


Trying to create a sequence in Oracle that starts with the max value of primary key of a specific table. Why does this not work?

You cannot mix DDL with DML in this way.

Your best bet is an anonymous PL/SQL block:

BEGIN
DECLARE
seqval NUMBER;
  BEGIN
  SELECT MAX(nvl(reservation_id),0)
  INTO seqval
  FROM reservation;

  execute immediate('CREATE SEQUENCE reservation_sequence MINVALUE 0 START WITH '||seqval||' INCREMENT BY 1 CACHE 20');
  END;
END;
/

Plus, are you sure you want max() and not max()+1?


Instead of restoring a daily full backup, you could:

Take weekly full backups of production, restoring them to the D-1 server the next day.
Take daily differential backups of production, restoring them to the D-1 server the following day.

The daily restores from the differential backup will be much faster than restoring the entire database each day.

I'd advise against using replication for this; as @Mark Storey-Smith said, you will now have two problems to manage instead of one.


Ola backups happening on both Primary and Secondary AG

Validate that:

Backup Priority is higher on the Secondary instances
None of the secondaries are excluded
You are looking at Synchronized (in the AG) databases
You do not have OverrideBackupPreference set in the Ola cmd line

Check your DBName. If I call sys.fn_hadr_backup_is_preferred_replica with a @DatabaseName that does not exist on the instance it returns a 1. If you have validated all this and it still returns a 1 on both replicas then the issue is not the OLA scripts, the issue is your AG configuration.

Community Wiki answer created from comments by IanG


You can use the Modulo operator, something along these lines:

UPDATE YourTable
SET NewBalance = Balance - (Balance % 100);

HTH


If you don't want to use xsi:nil you have to decide what value you want to have in the XML when the value in the table is null. For strings it might be appropriate with an empty string. For integers you might want to use 0 or -1 or perhaps convert the column data to a string first and use an empty string for integers as well. You have to decide what value you want and use isnull and cast to get the output you want.

Example:

declare @T table
(
  MenuID int,
  MenuOrder int,
  MenuName varchar(10)
)

insert into @T default values

select -- 0 instead of null
       isnull(MenuID, 0) as MenuID,
       -- empty string instead of null
       isnull(cast(MenuOrder as varchar(11)), '') as MenuOrder,
       -- The text value NULL instead of null
       isnull(MenuName, 'NULL') as MenuName
from @T as T
for xml path('')

Result:

<MenuID>0</MenuID>
<MenuOrder></MenuOrder>
<MenuName>NULL</MenuName>

If you want to do this dynamically where you have the table name as parameter and you decide that empty string is the way to go regardless of data type you can do something like this.

-- Table parameter
declare @TableName varchar(128) = 'Menus';
-- Column to use in where clause
declare @ColumnName varchar(128) = 'MenuID';
-- Value to filter by
declare @ColumnValue int = 100;

declare @S nvarchar(max);

set @S = '
select '+
  stuff((
        select ', isnull(cast('+quotename(C.name)+' as nvarchar(max)), '''') as '+quotename(C.name)
        from sys.columns as C
        where C.object_id = object_id(@TableName)
        for xml path('')
        ), 1, 2, '')+'
from '+quotename(@TableName)+'
where '+quotename(@ColumnName)+' = @ColumnValue
for xml path('''')';

exec sp_executesql @S, N'@ColumnValue int', @ColumnValue;

You obviously need to parameterize what column and value you want to use as well if the table name is a parameter.

The dynamically generated query:

select isnull(cast([MenuID] as nvarchar(max)), '') as [MenuID], isnull(cast([MenuOrder] as nvarchar(max)), '') as [MenuOrder]
from [Menus]
where [MenuID] = @ColumnValue
for xml path('')


How to count multiple values of a column as one group?

Use the aggregate FILTER clause in Postgres 9.4+:

SELECT count(*) FILTER (WHERE rank = ANY ('{a,b,v}')) AS myvalues
     , count(*) FILTER (WHERE rank = ANY ('{c,d,j}')) AS yourvalues
     , count(*) FILTER (WHERE rank = 'e')             AS extravalues
FROM main;

Details and alternatives for older versions:

Return counts for multiple ranges in a single SELECT statement


Instead of the PIVOT you can simply use the CASE WHEN .... By using GROUP BY then use MAX(CASE will return your expected result:

Sample execution with the given sample data:

DECLARE @OriginalDataTable TABLE (
    [Year] INT, DRC DECIMAL(18,2), DISP DECIMAL(18,2), Id INT);

INSERT INTO @OriginalDataTable ([Year], DRC, DISP, Id) VALUES
(0, 140.21, 0.00, 5808),
(1, 112.37, 1.00, 5808),
(0, 140.21, 0.00, 5824),
(1, 112.37, 0.00, 5824);

SELECT [Id],
       MAX(CASE WHEN [Year] = 0 THEN DRC END)  AS [DRC Year 0],
       MAX(CASE WHEN [Year] = 0 THEN DISP END) AS [DISP Year 0],
       MAX(CASE WHEN [Year] = 1 THEN DRC END)  AS [DRC Year 1],
       MAX(CASE WHEN [Year] = 1 THEN DISP END) AS [DISP Year 1]
FROM @OriginalDataTable
GROUP BY [Id]

Output:

Id      DRC Year 0   DISP Year 0   DRC Year 1   DISP Year 1
5808    140.21       0.00          112.37       1.00
5824    140.21       0.00          112.37       0.00

More explanation:

SELECT [Id],
       CASE WHEN [Year] = 0 THEN DRC END   AS [DRC Year 0],
       CASE WHEN [Year] = 0 THEN DISP END  AS [DISP Year 0],
       CASE WHEN [Year] = 1 THEN DRC END   AS [DRC Year 1],
       CASE WHEN [Year] = 1 THEN DISP END  AS [DISP Year 1]
FROM @OriginalDataTable

The above query returns the result below:

Id      DRC Year 0  DISP Year 0  DRC Year 1  DISP Year 1
5808    140.21      0.00         NULL        NULL
5808    NULL        NULL         112.37      1.00
5824    140.21      0.00         NULL        NULL
5824    NULL        NULL         112.37      0.00

To avoid the NULL values and the Id wise result, I used GROUP BY Id then MAX() of the each column, so in the final result we can get the not NULL values as result.


Best solution for High Availability and SSRS on SQL Server 2008 R2?

If you can convince the reporting users that they don't really need real-time reports, you can take advantage of the ability to create a snapshot of the mirror database. See Database Mirroring and Database Snapshots on Technet.

  Unlike the mirror database itself, a database snapshot is accessible
  to clients. As long as the mirror server is communicating with the
  principal server, you can direct reporting clients to connect to a
  snapshot. Note that because a database snapshot is static, new data is
  not available. To make relatively recent data available to your users,
  you must create a new database snapshot periodically and have
  applications direct incoming client connections to the newest
  snapshot.

Mirror/snapshot combinations can also be beneficial if you want to run ETL over an OLTP database to load into a data warehouse. Example architecture in this article on SSC.

The advantage in both cases is removing the overhead of running a replication feed, if you can convince your users that an hourly lag on reporting data is acceptable. In the vast majority of cases it is perfectly reasonable but the perception can be tough to change.


mysql innodb db full text search FTS_DOC_ID column

Reading the manual it does not seem to be anything bad. MySQL just informs you about an additional action it had to do.

  InnoDB uses a unique document identifier referred to as a Document ID
  (DOC_ID) to map words in the full-text index to document records where
  the word appears. The mapping requires an FTS_DOC_ID column on the
  indexed table. If an FTS_DOC_ID column is not defined, InnoDB
  automatically adds a hidden FTS_DOC_ID column when the full-text index
  is created.

If you preferred to not get the warning, then you would have to manage the column yourself which looks like a lot of work which is currently automated:

  If you choose to define the FTS_DOC_ID column yourself, you are
  responsible for managing the column to avoid empty or duplicate
  values. FTS_DOC_ID values cannot be reused, which means FTS_DOC_ID
  values must be ever increasing.

One side effect of the actions producing this warning is that the table has to be rebuild - that might take some time for tables already containing many records. But the same will be true if you added the column yourself, so it does not make a difference for you.


Will creating an index improve performance on a full scan

Currently your data is stored as either a heap or a clustered index, with all of it that structure.

If your data is in a heap and you create a clustered index, then you will reorganise all that data into a tree structure, and you will leverage some of the nicer aspects of clustered indexes, such as ordered data, and (unlike heaps) data without forwarding records (which can slow down scans). The data will be about the same size though.

If you create a non-clustered index, then this is a COPY of some of the data. This can help if you're getting only a subset of the columns or rows. If your non-clustered index doesn't include all the columns that you need for your query, it probably won't get used. But if you're only wanting three columns in your query, and your index has all three, you can save quite a bit of effort. You might get 100 times as many rows onto each page on disk, and scanning this index should therefore take just 1% of the effort.

Having indexes is just like in paper-based systems. If all you need is a list of names, then you can put all that on a single page instead of using the filing cabinet that has a dossier on each person. You'll have to maintain that list, but it might be worthwhile with the savings in other areas.


I feel terrible for forgetting about this question!!!

I have located and fixed the problem as follows.

When the DNS names were added to DNS, the corresponding reverse lookup was not created.

This resulted in MySQL not being able to perform the reverse lookup from the IP address to the correct DNS name, and therefore rejecting the connection.

So, we added a set of reverse lookups from IP address to DNS names, ran FLUSH HOSTS; on all of the MySQL boxes, and everything started working.

We require the use of DNS names for all connections, due to our disaster recovery solution being in a separate data centre, and a virtually identical VM farm, but with the IP addresses of all the machines modified only slightly. If / When we have a complete fail-over to the other data centre, all the software and communications will just "work", as the DNS resolution will always give the correct address based on the data centre.

You just need to be careful not to have TWO (or more) names reverse looking up from the same IP address, as there is no guarantee which of the two names will be returned - sometimes your connection will work, and other times it won't.

Hope this helps someone with the same problem!

Regards,
Dave


query optimization for update nested query IN(SELECT

How about:

UPDATE
(select * from TABLE_NOTE
where COL_NOTE_BIBID = $projectID
join TABLE_PINENOTEREL on $COL_PILENOTEREL_NOTE_ID = $COL_NOTE_ID
WHERE $COL_PILENOTEREL_PILE_ID = $pileId)
SET COL_NOTE_DISPLAY='BLOCK'


What would cause Mysql database entries to be truncated to 253 characters?

If not configured (by adding strict_trans_tables to your sql_mode settings) to throw errors when presented with invalid data, such as a character value that is longer than the definition of a VARCHAR column allows, MySQL truncates it to fit, and only throws a warning.

My guess is that you've got a VARCHAR(255) column and at some point in the process, there's an inconsistency in character set configuration, and you have some UTF-8 characters that at some point in your system are being treated as latin1, causing the system to misinterpret the number of characters and truncate the value.

Wait, what?  How is the number of "characters" different than the number of "bytes?"

In UTF-8 encoding, character values from 0x7F (decimal 127) downward are still encoded as their 1-byte ASCII equivalent.  Larger values are encoded as 2 or 3 bytes.  Common symbols like © (ASCII 169 0xA9) and ® (ASCII 174 0xAE) are encoded as two bytes, 0xC2 0xA9 and 0xC2 0xAE respectively.

When fully configured for UTF-8, MySQL understands this; using "®" as an example:

mysql> SELECT LENGTH('®'), CHAR_LENGTH('®'), HEX('®');
+--------------+-------------------+-----------+
| LENGTH('®')  | CHAR_LENGTH('®')  | HEX('®')  |
+--------------+-------------------+-----------+
|            2 |                 1 | C2AE      |
+--------------+-------------------+-----------+

The LENGTH() function returns the number of bytes, while CHAR_LENGTH() returns the number of characters.

A VARCHAR column restricts not the number of storable bytes but the number of characters you can store in it, regardless of the byte-width of each character... so a VARCHAR(2) column could actually end up containing 6 bytes of data (plus requiring 1 byte of storage overhead) if the two characters happened to be both 3-byte-wide characters.

However... 0xC2 and 0xAE are both codes for valid characters in the default latin1 character set, so if you were configured with latin1 but sent those characters in their 2-byte unicode encoding, the server would interpret that single unicode character as if it were two characters -- both valid -- thereby reducing the number of printable characters that could be stored in the VARCHAR(255) column by one character for each 2-byte encoding, or reducing it by two characters for each 3-byte encoding.

In the example above, all of my character_set_* values (except character_set_filesystem, which is set to BINARY) were set as utf8, and the CHAR_LENGTH() of a 2-byte character was correctly reported as being "1."

Now, I'll change these settings, to illustrate:

mysql> set character_set_client = latin1, character_set_results = latin1;
Query OK, 0 rows affected (0.00 sec)

mysql> select CHAR_LENGTH('®');
+-------------------+
| CHAR_LENGTH('®')  |
+-------------------+
|                 2 |
+-------------------+

Note that I didn't actually change anything about my actual client's behavor -- each time the "®" symbol appears, the character was UTF-8 encoded and there were two bytes being sent and received over the wire.  All that changed is that I only told MySQL that what I was sending and what I expected to receive should be interpreted as being sent to the server (character_set_client) and returned to me (character_set_results) as latin1.

http://dev.mysql.com/doc/refman/5.1/en/charset.html


Shedding database server load from a web application: how to measure?

In my view the challenge will be to define what is loaded.  Unless you have a baseline tied to business hour (with full cycle, day/night, weekday/weekend etc) there is no way to determine if you are overloaded.  I noticed you mentioned:

  most overloads in our environment involve high cpu utilization

If you want to base you alert on one data point (high cpu) you can use this query to see what percent of cpu is being consumed at this moment.  You can change the top(10) value and get back to 256 minutes.

I copied this query from here.

DECLARE @ts_now BIGINT = (SELECT cpu_ticks / ( cpu_ticks / ms_ticks )
   FROM   sys.dm_os_sys_info);

SELECT TOP(10) sqlprocessutilization AS [SQL Server Process CPU Utilization],
              systemidle             AS [System Idle Process],
              100 - systemidle - sqlprocessutilization AS [Other Process CPU Utilization],
              Dateadd(ms, -1 * ( @ts_now - [timestamp] ), Getdate()) AS [Event Time]
FROM   (SELECT record.value('(./Record/@id)[1]', 'int')
                      AS record_id,
            record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS [SystemIdle],
            record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS [SQLProcessUtilization],
            [timestamp]
 FROM   (SELECT [timestamp],
                CONVERT(XML, record) AS [record]
         FROM   sys.dm_os_ring_buffers
         WHERE  ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
                AND record LIKE '%<SystemHealth>%') AS x) AS y
ORDER  BY record_id DESC;

With this approach you have no way to check if SQL Server (beside kernel process) is consuming all of the cpu.  Hopefully in your case you do not have other applications installed in the same host.

Adding few PowerShell commands.

Get-WmiObject win32_processor | Measure-Object -property LoadPercentage -Average | Select Average

Get-Counter '\Processor(_Total)\% Processor Time'

I can make this answer to a question how to detect high cpu consuming sessions and try to fix those but assuming that is not what you are looking for.  I hope you already know what is causing high cpu and at this moment you cannot do anything about that.

I want to point out high cpu is not a bad thing unless your ‘runnable’ tasks are waiting for scheduler to start working.  CPU is expensive and we pay lot of money to license those, why not let our money work to the full capacity?


Does ANSI SQL standard inlcude FORALL predicate?

Searching through a copy (of a draft) of the 2011 SQL Standard, there is no mention at all of a FORALL keyword.

Neither in the main document ("Part 2: Foundation (SQL/Foundation)") which describes the core SQL language nor in the extensions document ("Part 4: Persistent Stored Modules (SQL/PSM)") which describes persistent routines and server modules. There are several control and repeat structures and keywords (DO, ITERATE, FOR, LOOP, WHILE, REPEAT) but not FORALL.

Postgres docs also have an extensive list of SQL Key Words (taken from the 92, 2008 and 2011 versions of the standard). No mention of FORALL there either.

FORALL seems to be a keyword only in Oracle's procedural language extension to SQL.


Postgres performance testing, caching and flushing

PostgreSQL relies heavily on the OS cache as well as its own cache.  So you would have to clear the PostgreSQL cache (restart the postgres service) and clear the OS cache (either restart the machine, or follow the method in the comments to clear without restarting if you are Linux).

But, why do you want to do it this way?  If the query is parameterized, you could just change the parameters each time so they refer to a different part of the data.  If the query is not parameterized, then why is the data it needs getting driven out of the cache between runs on the production machine?

When the different parameterizations have different performance due to different result sizes, the most definitive solution is to write a driver program that repeats the query several times with random (but realistic) parameterizations, and hopes that the parameter-induced variation averages out between your tuning settings.  This is a pain, but when I have had to resort to it I generally came to wish I had to done it sooner.  This is better than doing each query from a cold cache, because in the real production situation you are unlikely to have a fully cold cache.  Certain parts of the data will be in common to all parameterizations, and that part will be "hot" enough to always be in the cache.


When the O7_DICTIONARY_ACCESSIBILITY parameter is set to false, users with SELECT ANY TABLE privilege can not access tables such as SYS.USER$, unless they have direct privileges to them.