Using a foreign key on multiple fields in one table Simple question probably.

For two tables. A one to many relation is created from A -> B, TeamID -> Fk_Team, however there are multiple fields that need to reference this one relationship how does that work.

so

A                          B
TeamID                     EventID
teamName                   datetime
teamocation                homeTeam
etc                        awayTeam
                           Weather
                           Fk_team

So how do I define that homeTeam and awayTeam both utilise the Fk_Team relationship and that the homeTeam & awayTeam entry must exist in the TeamID.teamName field?

You can't implement this as a single constraint; you'll need to create two:

ALTER TABLE dbo.B ADD CONSTRAINT FK_HomeTeam
  FOREIGN KEY (homeTeam) REFERENCES dbo.A(teamName);

ALTER TABLE dbo.B ADD CONSTRAINT FK_AwayTeam
  FOREIGN KEY (awayTeam) REFERENCES dbo.A(teamName);

As I alluded to in my comment, it would be much more efficient to store TeamID in the two columns in dbo.B - this way you are repeating two INTs in every row, instead of bulky strings like "Billy Bob's Bait and Tackle Mudhens" or "South Cincinnati Rebel Rockers."

It also makes it much easier to change a team's name (unless the point is to record their historical name at the time the game was played, but even then I would store names over time in some kind of history table, not with every game).


Importing a Foxpro Database I have a Fox Pro database (one DBC and a bunch of DBF files) that I want to import into SQL via the Import and Export wizard.

I downloaded the FoxPro ODBC driver.  I managed to get to the screen that shows all table name.  I can even preview the data, except if I try to go next I will get

Column information for the source and the destination data could not be retrieved, or the data types of source columns were not mapped correctly to those available on the destination provider.

  "somecolumn" -> [dbo].[somecolumn]:

  Cannot load the column attributes from the schema using the current provider descriptor:

  The column attribute "ORDINAL_POSITION" is not valid.

  Please verify the ProviderDescriptors.xml file contains the data provider descriptor that matches the schema of the used data provider.

I understand I need to fix the ProviderDescriptors.xml file, which has this line

OrdinalPositionColumnName="ORDINAL_POSITION"

But what can I put in there?  I have guessed the following

POSITION
ORDINALPOSITION
ID
INDEX
OFFSET

All of the above doesn't work.

In FoxPro, under the Wizard menu, choose "upsizing". From there, choose an ODBC connection to the SQL Server database and step through it. Also, there is a much improved upsizing Visual FoxPro project that you can download here.

I have had trouble upsizing to SQL Server 2008 and above so if you have a SQL Server 2005 database around, you might have more success upsizing to it and migrating your tables to there final destination.


Index Pages (page type 2) I trying to understand page splits in SQL Server, reading WHAT IS A PAGE SPLIT? WHAT HAPPENS? WHY DOES IT HAPPEN? WHY WORRY? by Tony Rogerson

CREATE TABLE mytest
  (
     something_to_see_in_data CHAR(5) NOT NULL CONSTRAINT pk_mytest PRIMARY KEY CLUSTERED,
     filler                   VARCHAR(3000) NOT NULL
  )

go

insert mytest ( something_to_see_in_data, filler ) values( '00001', replicate( 'A', 3000 ) )
insert mytest ( something_to_see_in_data, filler ) values( '00002', replicate( 'B', 1000 ) )
insert mytest ( something_to_see_in_data, filler ) values( '00003', replicate( 'C', 3000 ) )
go

To check the pages of my table:

DBCC IND ( 0, 'mytest', 1);

+---------+---------+--------+--------+------------+---------+-----------------+-------------------+----------------+----------+------------+
| PageFID | PagePID | IAMFID | IAMPID |  ObjectID  | IndexID | PartitionNumber |    PartitionID    | iam_chain_type | PageType | IndexLevel |
+---------+---------+--------+--------+------------+---------+-----------------+-------------------+----------------+----------+------------+
|       1 |    3520 | NULL   | NULL   | 2065259704 |       1 |               1 | 72057595357560832 | In-row data    |       10 | NULL       |
|       1 |    3519 | 1      | 3520   | 2065259704 |       1 |               1 | 72057595357560832 | In-row data    |        1 | 0          |
+---------+---------+--------+--------+------------+---------+-----------------+-------------------+----------------+----------+------------+

To check the page details of data page:

dbcc traceon( 3604 )

go

DBCC page( 0, 1, 3519, 1 ) with tableresults

Offset:

Slot 0, Offset 0x60, Length 3016, DumpStyle BYTE
Slot 1, Offset 0xc28, Length 1016, DumpStyle BYTE
Slot 2, Offset 0x1020, Length 3016, DumpStyle BYTE

Updating one of the records, so that it will not fit into the current page, and page split will happen (i.e.) new page will be created?

update mytest
    set filler = replicate( 'B', 3000 )
where something_to_see_in_data = '00002'

Now checking the pages again:

DBCC IND ( 0, 'mytest', 1);

+---------+---------+--------+--------+------------+---------+-----------------+-------------------+----------------+----------+------------+
| PageFID | PagePID | IAMFID | IAMPID |  ObjectID  | IndexID | PartitionNumber |    PartitionID    | iam_chain_type | PageType | IndexLevel |
+---------+---------+--------+--------+------------+---------+-----------------+-------------------+----------------+----------+------------+
|       1 |    3520 | NULL   | NULL   | 2065259704 |       1 |               1 | 72057595357560832 | In-row data    |       10 | NULL       |
|       1 |    3519 | 1      | 3520   | 2065259704 |       1 |               1 | 72057595357560832 | In-row data    |        1 | 0          |
|       1 |    3521 | 1      | 3520   | 2065259704 |       1 |               1 | 72057595357560832 | In-row data    |        2 | 1          |
|       1 |    3522 | 1      | 3520   | 2065259704 |       1 |               1 | 72057595357560832 | In-row data    |        1 | 0          |
+---------+---------+--------+--------+------------+---------+-----------------+-------------------+----------------+----------+------------+

As we can see, two new pages got created:

3521 -- Index page
3522 -- Data page

I can understand the reason behind creation of new Data page(3522) because my data is more than 8kb so new page got created.

What is the use of the Index page and when will it will be created? I did lot of research in Google there is no proper documentation on Index page. Is it for maintaining B-Tree?

Index pages (type 2) hold the non-leaf level(s) of the clustered index b-tree. The leaf level of the clustered index is just the underlying object's data pages themselves.

In the special case where the whole object (table in your case) fits in a single page, SQL Server does not create a separate index page, because there is no need.

In your example, the first index page is created when the page split results in the table consisting of more than one data page for the first time.

Index pages are also used for all levels of nonclustered b-tree indexes.

Index pages can also be inspected with DBCC PAGE. Dump style 3 is the most informative, as it shows the child file and page pointers for index key ranges. This is the information needed to navigate down (potentially multiple levels of) the b-tree index.

As the number of rows in the index increases, the number of levels in the index will also increase.

See:

Clustered Index Structures
Nonclustered Index Structures
Heap Structures
Table and Index Organization

Comprehensive information about table and index internals can be found in the Microsoft SQL Server Internals books by Kalen Delaney et al.

A new index page will be created whenever an existing index page needs to split. If that page was the root page (top of the index tree), the index gains a new level as a result. Index pages can split if the index key is widened, or if another entry is needed on that page (e.g. to refer to a new child page) and there isn't enough room.


Not the prettiest query I've ever written, but it seems to get the job done without any recursion. (I've made the assumption that the output needs to be grouped by GroupID and Code.)

WITH starting AS ( --Find each "starting" entry
    SELECT
        d.GroupID,
        d.Code,
        d.StartSeq,
        d.EndSeq,
        d.StartDayNo,
        d.EndDayNo
    FROM SampleData d
        LEFT OUTER JOIN SampleData prev
            ON d.Code = prev.Code
            AND d.GroupID = prev.GroupID
            AND d.StartSeq = prev.EndSeq + 1
    WHERE prev.Code IS NULL
)
SELECT
    s.GroupID,
    s.Code,
    s.StartSeq,
    ISNULL(e.EndSeq, s.EndSeq) AS EndSeq,
    s.StartDayNo,
    ISNULL(e.EndDayNo, s.EndDayNo) AS EndDayNo
FROM starting s
    OUTER APPLY ( --Find the next highest "starting" entry
        SELECT TOP 1
            s2.*
        FROM starting s2
        WHERE s2.Code = s.Code
            AND s2.GroupID = s.GroupID
            AND s2.StartSeq > s.StartSeq
        ORDER BY s2.StartSeq ASC
    ) s2
    OUTER APPLY ( --Find the highest entry less than the next highest "starting" entry
        SELECT TOP 1 *
        FROM SampleData e
        WHERE e.Code = s.Code
            AND e.GroupID = s.GroupID
            AND (e.EndSeq < s2.StartSeq OR s2.Code IS NULL)
        ORDER BY e.EndSeq DESC
    ) e
ORDER BY s.Code, s.StartSeq


MongoDB update multiple sub documents inside array, each according to a condition I have a document like this:

{
    wid: 1,
    items: [
        {nid: 1, sent: 900},
        {nid: 2, sent: 12},
        {nid: 3, sent: 0},
        {nid: 4, sent: 1}
    ]
}

Now consider I want to update this document with an object I compute from somewhere else:

# All for wid:1, a mapping of nid:sent
# These values should be added to values already in the DB
{
    2: 37,
    3: 58,
    4: 172
}

so after the update, I want to have something like:

{
    wid: 1,
    items: [
        {nid: 1, sent: 900},
        {nid: 2, sent: 49},
        {nid: 3, sent: 58},
        {nid: 4, sent: 173}
    ]
}

Is there anyway to do this with only one update query?

What I know works->

db.test.update_one({wid: 1, 'items.nid': 2}, {$inc: {'items.$.sent': 37}})
db.test.update_one({wid: 1, 'items.nid': 3}, {$inc: {'items.$.sent': 37}})
db.test.update_one({wid: 1, 'items.nid': 4}, {$inc: {'items.$.sent': 172}})

But that's O(n) update queries issued and this is really contradicting all reasons I chose to stick to MongoDB.

Thanks!

Use the arrayFilters property:

db.getCollection("test").updateOne(
    {
        "wid": 1
    },
    {
        $inc: {
            "items.$[aa].sent": 37,
            "items.$[bb].sent": 58,
            "items.$[cc].sent": 172
        }
    },
    {
        upsert: false,
        arrayFilters: [
            { "aa.nid": 2 },
            { "bb.nid": 3 },
            { "cc.nid": 4 }
        ]
    }
);

For each named identifier you create (aa, bb, cc in the example), should be created a matching document in the arrayFilters array.


How to debug an hanging impdp importation I am currently trying to import a database dump in Oracle XE 11gR2 On windows 7. I have the following command.

 impdp 'APPLICATION/APPLICATION@XE' dumpfile=APPLICATION.dmp REMAP_TABLESPACE=APPLICATION:USERS logfile=APPLICATIONIMPORT.log

I tried running the command in the windows command prompt but... nothing happens. The process does not exit or do anything. It print a copyright and just... hang without using any CPU or anything. The log file is not created, and SQL Developper show no import task when I query with SELECT * FROM DBA_DATAPUMP_JOBS;

I there anyway to see what is going on? Any log, table, place where an error could be outputted? That would really help me resolve this problem.

Note: If that can help, I am an administrator on my machine but part of a Windows Domain. It screwed up the Oracle XE install the first time but I got around by running the installer with a local admin account.

I got it to work with a weird fix : I removed the 'APPLICATION/APPLICATION@XE' and entered the username/password manually with the interactive prompt.

I consulted this page: http://www.orafaq.com/wiki/Datapump and realized that I was not getting an equivalent of this line Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production, which tipped me it was a connection problem. By trying to remove the username/password from the command line it suddenly fixed the problem.

I am afraid I can't exactly say why this helped, but I hope this can be useful to someone.


Is it possible to create a dynamic join? I'm using PostgreSQL if it matters.

Assuming these two tables:

 id | run
----+-------
  1 | run_1
  2 | run_2
  3 | run_3

and

sample | elapsed |  run
-------+---------+-------
 samp1 | 1:09    | run_1
 samp2 | 0:32    | run_1
 samp1 | 0:58    | run_2
 samp2 | 0:28    | run_2
 samp1 | 0:55    | run_3
 samp2 | 0:28    | run_3

I want the following output:

sample | run_1 | run_2 | run_3
-------+-------+-------+-------
 samp1 | 1:09  | 0:58  | 0:55
 samp2 | 0:32  | 0:28  | 0:28

This is easy enough to do if I have a fixed number of run values, but is it possible to create a dynamic query which will output the right thing regardless of the number of runs?

Basically it's a crosstab query:

PostgreSQL Crosstab Query

The dynamic result type is a problem, though.

  ... is it possible to create a dynamic query which will output the right thing regardless of the number of runs?

No. Currently (including Postgres 9.6) not possible with a single SELECT statement. Not unless you know the return type at call time at the latest.

SQL is a strictly typed language. Postgres demands to know the row type that is going to be returned from the query. Either provided by user input in one or the other form, or from information in the system catalog (registered tables and types). Deducing the return type from involved data dynamically is strictly not possible.

Then why does SELECT * FROM tbl work?
Because there is a row type registered for every table (view, materialized view etc.) in the system catalogs, and Postgres knows which columns to return by looking that up.

Which ever way you try, you need two round trips to the database server for your query with dynamic result type:

Determine the number of columns in the result and build the SELECT statement accordingly.
Execute said statement returning the now well known row type.

Alternatives would be:

Return two arrays instead: One containing column names, the other containing values.
Return a document type (json, hstore, ...).
Return the maximum possible number of columns and fill dangling columns with NULL values.

I have answered similar questions on SO before:

Dynamic alternative to pivot with CASE and GROUP BY
Refactor a PL/pgSQL function to return the output of various SELECT queries


How can I find and clean up unused database files? How can I find and clean up unused (i.e. not used by any attached database) SQL Server database files (.mdf, .ldf, .ndf) that take up extra space on my server?

There are two PowerShell functions and a PowerShell script will help automate the process of finding unused SQL database files and alerting you to their presence so that you can clean them up.

First Invoke-SQL function:

Save the code for the Invoke-SQL function that I have posted as an answer to "How do you run a SQL Server query from PowerShell?" into a file named Invoke-SQL.ps1.

Second Get-SQLDatabaseFilesNotUsed function:

Save the following function into a file named Get-SQLDatabaseFilesNotUsed.ps1.

function Get-SQLDatabaseFilesNotUsed {
    param (
        [parameter(Mandatory = $true)][string]$SQLServerName,
        [switch]$IncludeEqual
    )

        $SQLCommand = @"
set nocount on
SELECT DB_NAME([database_id])AS [Database Name],
        [file_id], name, physical_name, type_desc, state_desc,
        CONVERT( bigint, size/128.0) AS [Total Size in MB]
FROM sys.master_files WITH (NOLOCK)
ORDER BY DB_NAME([database_id]) OPTION (RECOMPILE);
"@

    $Results = Invoke-Sql -datasource $SQLServerName -database master -SQLCommand $SQLCommand
    $SQLDatabaseFilesInUse = $Results | select @{Name="fullname";Expression={$_.physical_Name -replace "\\\\","\" <#SQL server allows \\ in paths and just uses it like \#> }}

    $SQLDatabaseAndLogFilesOnDisk = invoke-command -ComputerName $SQLServerName -ScriptBlock {
        $Filesystems = get-psdrive -PSProvider FileSystem
        Foreach ($FileSystem in $Filesystems) {
            Get-ChildItem $FileSystem.Root -Recurse -include *.mdf,*.ldf,*.ndf -File -ErrorAction SilentlyContinue | select fullname
        }
    }

    $SQLDatabaseAndLogFilesOnDisk = $SQLDatabaseAndLogFilesOnDisk |
    Where fullname -notlike "C:\windows\winsxs*" |
    Where fullname -notlike "C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn\*" |
    select fullname

    Compare-Object -ReferenceObject $SQLDatabaseFilesInUse -DifferenceObject $SQLDatabaseAndLogFilesOnDisk -Property FullName -IncludeEqual:$IncludeEqual | FT -AutoSize

}

One thing to note, if you have additional database files that you want to keep on disk and you want to exclude them from this check just add another Where fullname –notlike “<Path where you have files you don’t want checked>” | to the others you see above.

Third Test-SQLDatabaseFilesNotUsed script:

Save the following code into a Test-SQLDatabaseFilesNotUsed.ps1 script file.

. c:\scripts\Invoke-SQL.ps1
. c:\scripts\Get-SQLDatabaseFilesNotUsed.ps1

$OutputMessage = ""

$FromAddress = "scheduledtasks@YourDomain.com"
$ToAddress = "DBA@YourDomain.com"
$Subject = "SQL Server has data or log files that are not being used"
$SMTPServer = "MailServer.YourDomain.com"

$OutputMessage = Get-SQLDatabaseFilesNotUsed -SQLServerName <YourSQLServerNameGoesHere>

if ($OutputMessage)
{
    Send-MailMessage -From $FromAddress -to $ToAddress -subject $Subject -SmtpServer $SMTPServer -Body ($OutputMessage | FT -autosize | out-string -Width 200)
}

Be sure to substitute the name of your SQL server for  in the code above.

Now save all of these in a C:\scripts directory and create a scheduled task with whatever schedule you like (I have ours run once per week) that executes:

Powershell.exe -noprofile -file c:\scripts\Test-SQLDatabaseFilesNotUsed.ps1

This has saved me +100GB already and from this point forward you won’t have to worry about having space used up by database files from databases removed from SQL server long ago.


Sql Server - Multiple errors regarding to memory in error log MSSQL-Server 2017 Express Edition runs on my Linux server.
The server has 4GB RAM.
The total size of DB is 300MB.

My website and the sqlserver are on the same server, it's a small website with max of 400-300 visitors a day.
Memory usage in linux looks fine, mssql takes only 20% memory:

Some additional sql memory information:

Maximum server memory is set to 2147483647 which is the default I guess.

The errors I get:

After restarting the mssql-service there are no problems for something like 2 days and then it repeats it self.

I would happily get an answer that will solve this for me, but I would also like to get a resource where I can learn about memory allocation/cache/pages/ related to sql server because I am not a sql expert, I am a programmer.

You're showing some conflicting information. 20% memory utilisation on a 4 GB server would be around 820 MB, however, your screenshots indicate somewhere between 3.8 and 4 GB is consumed by SQL Server at the moment.

Those errors are clearly indicating that SQL Server has insufficient memory available to process a request. While you could, and should, set the max server memory setting to something other than 2147483647, this won't change the fact that SQL Server wants additional memory.

You need to look at optimising the server and workload, apply best practises to the instance to ensure it is running optimally, tune your SQL code to make sure it is not using excessive memory, and you will possibly need to allocate additional memory to the server.

  After restarting the mssql-service there are no problems for something
  like 2 days and then it repeats it self.

This is because SQL Server flushes all of its caches upon restart, and it takes a while to fill that up again, given the low utilisation you've indicated. Setting a max server memory setting between 2 and 3 GB should help alleviate some of these errors because SQL Server will manage memory differently than if the setting is configured to simply consume as much memory as possible, however, it is likely that you will eventually hit these errors again as 4 GB for a Web & SQL Server is quite low.

Here is a link to the SQL Server Memory Management Architecture Guide by Microsoft.
Here is a link to SQL on Linux Performance Best Practises by Microsoft.
Here is a collection of best practise tips for SQL Server that you might find useful.


Display all parent records but only one record of child records of a parent record I have encountered an issue while writing a stored procedure for displaying some records of a table. The following code is to illustrate the issue.

declare @t table(
    Id int primary key identity(1,1),
    ParentId int null,
    Item varchar(50)
)
-----------------------------------------------------------
insert into @t(ParentId,Item)
select null,'A' union all
select null,'A' union all
select 1,'B' union all
select 1,'B' union all
select null,'C' union all
select 2,'D' union all
select 2,'D'

The data after insertion in the table @t looks like this.

Id | ParentId | Item
---------------------
1     NULL       A
2     NULL       A
3      1         B
4      1         B
5     NULL       C
6      2         D
7      2         D

I want to select all records whose ParentId is null but select only the first record of those whose ParenId is not null.

Desired Result:

Id | ParentId | Item
---------------------
1     NULL       A
2     NULL       A
3      1         B
5     NULL       C
6      2         D

Any idea on how to get the desired result?

Assuming "only the first record of those" means ordered by Id you can use rank() partitioned by ParentId and a case statement in the order by clause.

select T.Id,
       T.ParentId,
       T.Item
from (
     select T.Id,
            T.ParentId,
            T.Item,
            rank() over(partition by T.ParentId
                        order by case when T.ParentID is not null
                                   then T.Id
                                   else null
                                 end ) as rn
     from @t as T
     ) as T
where T.rn = 1;


Deleting all records on a table except the latest 10 for each user I have an activities table that has a foreign key reference to a users table. A user can have many activities, and I have a job that will populate their activities on a daily basis. After that job runs, I want to run a SQL query that will delete the oldest records per user and keep the latest 10. This is the schema:
users
id name
1  Jimmy
2  Johnny

user_activities
id user_id activity date
1  1       foo      2020-08-07 00:00:00
2  1       bar      2020-08-08 00:00:00
3  1       baz      2020-08-09 00:00:00
4  2       foo      2020-08-07 00:00:00
5  2       bar      2020-08-08 00:00:00
6  2       baz      2020-08-09 00:00:00

How can I write a query to truncate records up to the latest 10 for each user?


sp_BlitzCache and cursors I'm running sp_BlitzCache (Brent Ozar) to get some information about database performance. The program that "attacks" this database uses cursors everywhere, so sp_BlitzCache gives me several lines where Query Text is something like 'FETCH API_CURSOR000000000045E513'. Is there any way to know what the select of that cursor is?
   Thanks in advance.

Sure, Joe Sack writes that you can track it down for live sessions with the help of sys.dm_exec_cursors, like this:

SELECT c.session_id, c.properties, c.creation_time, c.is_open, t.text
FROM sys.dm_exec_cursors (53) c
CROSS APPLY sys.dm_exec_sql_text (c.sql_handle) t

With 53 being a current live session holding a cursor open. You can also use 0 to get all current open sessions.

Granted, this is different than sp_BlitzCache - it's only for currently running queries.


Size of Partitions in Partitioned Tables According to the following link, it is advisable to use hash partitioning when, "The sizes of range partitions would differ quite substantially or would be difficult to balance manually." This implies that when creating partitioned tables, the partitions should be relatively similar in size. Is this true?

Well, yes it's true.  One important reason for partitioning is to reduce the I/O load for queries.  That is, only one partition needs to be read for a given query.

If you make a bunch of assumptions, such as:

An even distribution of rows over queries over time (relative to the partitioning key)
Record sizes independent of the partitioning key
Equal access for the partitions

Then you will discover that for "n" partitions the most efficient solution is when they are equal sized.

That said, there are definitely special cases.  If the partitioning key is referenced through ranges (between), then hash partitioning is not optimal.  If some data is accessed much more frequently (such as the most recent data), then equal-sized partitions may not be suitable.  If you are using partitions for fast deletes (by dropping the partition), then hash-based partitions are not suitable.


Postgresql - sequences bigger than bigint PostgreSQL sequences (CREATE SEQUENCE) are limited to bigint (which is usually big enough). But in my case I need a bigger sequence. The table will not have this much records, it will be cleaned over time, but the numbers must never be reused. As I am planning this to be used for at least 20 years, there is the need to go bigger than bigint.

I am using NUMERIC(35).

How can I achieve this with less locking as possible?

Is creating two sequences and summing them a good idea?

As pointed out by @a_horse_with_no_name, bigint is good enought. I think 29 million years is reasonable (hehe).

The math checks. Should have done it before.


Invoke-SQLCMD with special characters I'm currently working on a project integrating AD with SQL Server data, which I am
handling mainly via powershell (Invoke-SQLCMD in particular). The issue I'm running into is if someone has a special character in a field in AD, it's throwing an error when I'm trying to run my insert.

$OuListing = 'OU=FirstOU,DC=example,DC=com','OU=SecondOU,DC=example,DC=com'
$UserList = $OuListing | ForEach { get-aduser -filter * -SearchBase $_ -properties * -SearchScope 2 | where {$_.Enabled -eq $True} | Select CN,Surname }

$database = 'DB_Name'
$server = 'Server_Name'
$table = 'dbo.table_Name'

Invoke-SQLCMD -Database $Database -ServerInstance $Server -Query "Truncate Table $Table"

ForEach ($User in $Userlist)
{
    Invoke-SQLCMD -Database $Database -ServerInstance $Server -Query "Insert into $Table (CN,Surname) Values ('$($User.CN)','$($User.Surname)')"
}

This code works fine 99% of the time, but like mentioned, if an AD users last name is "Mc'Laggen", the apostrophe in it throws things off. Is there any good way to pass through the values like this to make it work, or should I be parsing through each attribute and add in escape characters where there's anything special? In the actual script, there are approximately 20 attributes so I was wondering if there was a simpler method of parsing through.

A parametrized query will help you here. It's a well-established way to create queries that protect against  SQL injections and often improve performance too. The advantage in your case is that parametrization handles string escaping, so you don't need to worry about passing an apostrophe.

As a side note, depending on your database, trying to escape strings might be fool's errand, as PHP/MySql's notorious mysql_real_escape_string() proves with attack opportunities. Writing secure code is hard, and any database is vulnerable to attacks via poor development practices.

Using parameters requires one to build a .Net SqlCommand and add parameters into its Parameters collection.

The bad news is that configuring a SqlCommand requires working proficiency in .Net, and the documentation is huge. Relevant subsections are about commands and parameters.
Static typing for parameters is possible, though implicit conversion works in many cases. For static types, see .Add() method's numerous overloads. Further develpoment questions and script improvement would be better on-topic at Stack Overflow.

In case of link rot, a sample from the web that seems to work fine. Note that it relies on implicit conversion.

function exec-query( $sql,$parameters=@{},$conn,$timeout=30,[switch]$help){
 if ($help){
 $msg = @"
Execute a sql statement.  Parameters are allowed.
Input parameters should be a dictionary of parameter names and values.
Return value will usually be a list of datarows.
"@
 Write-Host $msg
 return
 }
 $cmd=new-object system.Data.SqlClient.SqlCommand($sql,$conn)
 $cmd.CommandTimeout=$timeout
 foreach($p in $parameters.Keys){
    [Void] $cmd.Parameters.AddWithValue("@$p",$parameters[$p])
 }

 $ds=New-Object system.Data.DataSet
 $da=New-Object system.Data.SqlClient.SqlDataAdapter($cmd)
 $da.fill($ds) | Out-Null

 return $ds
}

$conn=new-object data.sqlclient.sqlconnection "Server=<MyServer>;initial catalog=<SomeDatabase>;Integrated Security=True"
$conn.open()
(exec-query "select column from dbo.mytable where column=@type" -parameter @{type=1} -conn $conn).tables
$conn.dispose()


AlwaysOn Availability Group Automatic Failover does not work Playing with AG setup I have the WSFC up and configured with two nodes in one availability group called DevClusterOnline. Both nodes (DEV-AWEB5 primary, DEV-AWEB6 secondary) are running Windows Server 2008 R2.

If I check the health of my AG I get this:

Running the query below will return this result set:

select
    ar.replica_server_name,
    availability_group_name = ag.name,
    ar.availability_mode_desc,
    ar.failover_mode_desc
from sys.availability_replicas ar
inner join sys.availability_groups ag
on ar.group_id = ag.group_id
order by availability_group_name, replica_server_name;

If I disconnect DEV-AWEB5, I cannot connect to the Group Listener (DevListener), but I can ping it and it will respond to my ping. The replica - DEV-AWEB6 goes into a RESOLVING state and my DB is unaccessable. I can however, manually go into Management Studio and set Failover to DEV-AWEB6 and then I´m up and running again and DevListener will once again accept connections.

Considering that these facts confirm that the failover actually works, that I have synchronized commits and automatic failover configured I have no idea what if malfunctioning in my setup.

When I disconnect DEV-AWEB5 I expect that my replica will retain connection and thus, DevListener too. I expect that the automatic failover will allow me to connect to the AG Listener transparently. From an end-user perspective, using a Web system it should not be noticable that one of the DB servers go down.

I am stuck here, can anyone please enlighten me on what I am doing wrong?

If I disconnect DEV-AWEB5

Define "disconnect", if you will.  My guess is you kept the box up but took SQL Server down.

  I cannot connect to the Group Listener (DevListener), but I can ping it and it will respond to my ping

That's because the listener is just a virtual network name (VNN) within the WSFC cluster resource group for the represented availability group.  Your DEV_AWEB5 node still owns the cluster resource group, but it's just the AG cluster resource most likely that is in a failed state.  The VNN must still be online (expected behavior).  It's simply pointing to whatever node is owning that resource group (in this case, DEV-AWEB5).  In fact, if you had PowerShell remoting enabled, and you ran the following:

Invoke-Command -ComputerName "YourListenerName" -ScriptBlock { $env:computername }

Likewise, if you can RDP into DEV-AWEB5 (provided you have the capability and accessibility, etc.) then you'd be able to RDP using the listener name (mstsc /v:YourListenerName).  It's just a VNN.

The return of that would be the computer name of your owning node.

By all of your symptoms, I'd be willing to bet that you've reached your failover threshold.  The failover threshold determines how many times the cluster will attempt to failover your resource group in a specified time period.  The default of these values max failovers n - 1 (where n is the count of nodes) in a period of 6 hours.  You can see that through the following WSFC PowerShell command:

Get-ClusterGroup -Name "YourAgName" |
    Select-Object Name, FailoverThreshold, FailoverPeriod

That just gives you the settings (which you can modify if you so choose, of course).

The best way to prove that this is the case for you, you would need to generate the cluster log (the system event logs only go into detail as far as " has failed", or something like that).

Get-ClusterLog -Node "YourClusterNode" -TimeSpan <amount_of_minutes_since_failure>

That'll by default get put into the "C:\Windows\Cluster\Reports" folder, and the file is called "Cluster.log".

If you were to open up that cluster log, you should be able to find the following string in there, indicating exactly what happened and why it happened:

  Not failing over group [YourClusterGroupName], failoverCount [# of failovers], failover threshold [failover threshold value], nodeAvailCount [node available count].

The above message is simply WSFC telling you that it will not failover your group because it's happened too much (you hit the threshold).

Why does this happen?  Simply to prevent the Ping-Pong effect of cluster resources going back and forth too frequently between nodes.

Whereas this would be common to hit these thresholds in failover testing, in production it would typically point to a problem that should be investigated.


Which MongoDB administration UIs offer a table view (OS X)? The only admin UI we found that offers a table view is MongoVue, which is only available on Windows. We're on Macs with OS X. Do any exist?

MongoDB has a good list of administration tools and interfaces here.  Of these, I've played with a couple mostly on Windows, but some options for you that should work on OS X (because they are Java based) are:

Robomongo
Umongo

Of the two, I prefered Robomongo.  There is also an OS X specific tool called MongoHub that you may want to try out.


How to create column group in WiredTiger engine in mongodb 2.8? MongoDB 2.8 supports WiredTiger strorage engine, and WiredTiger supports column grouping.

How we can create a collection in mongodb 2.8 with grouped columns?

Although WiredTiger itself does support a column stores, this is not supported with MongoDB.  WiredTiger as implemented in MongoDB only supports LSM trees and the default row-store.


SQL Server AlwaysOn database stuck in Not Synchronizing / In Recovery mode after upgrading. Error: Cannot open database '...' version 782 While testing an upgrade from SQL Server 2014 SP1 (12.0.4422.0) to SQL Server 2016 CTP 3.2 (13.0.900.73) I was following the recommended update process and ran into an issue where the database would not start on the old primary after failing over to the updated secondary. Our setup is a primary replica and a single secondary replica, and the steps I completed were:

Remove automatic failover on synchronous-commit secondary replica
Upgrade secondary server instances to new version
Manually fail over to the secondary replica
Verify databases are online on new primary replica
Upgrade previous-primary replica to new version

The upgrade of the secondary and failover to make it the primary worked exactly as expected. But after upgrading the previously-primary replica I noticed that the databases on it were listed in SSMS as Not Synchronizing / In Recovery. Also trying to access them would generate an error message:

  The database ... is not accessible. (ObjectExplorer)

Checking thru the SQL Server Logs I saw

  Cannot open database '...' version 782. Upgrade the database to the latest version.

Querying the master..sysdatabases table showed that it was indeed an older version and had not been updated during the upgrade:

Unfortunately the logs did not indicate why it wasn't updated, and the Availability Groups Dashboard only gave a generic warning indicating the Data synchronization state of some availability database is not healthy with no reason why.

I tried using TSQL to detach the databases or set them offline to "kick" it into updating, but since they are part of the SQL AG those commands don't work.

How can I upgrade the database to the latest version when it is part of a SQL AG?

After poking around in SSMS for a while I noticed that on the secondary replica there was a pause icon next to the Availability Databases. The primary had shown both were "green", but there was an option on the secondary to Resume Data Movement. I resumed the first database, and immediately the In Recovery status message was removed. A minute later it changed from Not Synchronizing to Synchronized, and everything worked as expected.

Here is a screenshot of the AG Databases after I fixed "Patch", but before fixing the test database:

Note you can also use TSQL on the secondary to resume replication on multiple database at the same time:

ALTER DATABASE [Patch] SET HADR RESUME;
ALTER DATABASE [test] SET HADR RESUME;
GO


How to swap array values in PostgreSQL.? How to swap array values in PostgreSQL. For example i have a table that has a column whose data type is bigint[]. It has following value in it.

value: {111, 222, 333}
My desired output is {333, 111, 222}

i can not find any built in function to achieve other than array_replace.

You can access the values by index, as follows:

postgres=# create table intarray ( aaa int8[] ) ;
CREATE TABLE
postgres=# insert into intarray values ( ARRAY [ 111, 222, 333 ]  ) ;
INSERT 0 1
postgres=# select * from intarray;
      aaa
---------------
 {111,222,333}
(1 row)

postgres=# select aaa[3],aaa[2],aaa[1] from intarray;
 aaa | aaa | aaa
-----+-----+-----
 333 | 222 | 111
(1 row)

postgres=# select ARRAY[aaa[3],aaa[1],aaa[2]] from intarray;
     array
---------------
 {333,111,222}
(1 row)

postgres=#

Reading the documentation may help you.


Can I alter a view being referenced with schemabinding if the change fulfills the binding requirement? In SQL Server 2008, I have views WITH SCHEMABINDING and I need to alter one.

I am changing a column while not changing its alias so consumers will be unaffected.

If I were to drop all my other views that depend on this I would be able to recreate them again, so SCHEMABINDING is still valid.

Can I temporarily disable the schema lock on this view in order to ALTER it?

Or must I drop the dependent views and recreate them once it's changed?

You don't need to drop the dependent views, but you do need to alter them to (temporarily) not be schemabinding. If any of them are indexed, this means you will need to re-create the indexes.

SQL Server doesn't have some weighting system for schemabinding: you can't change the object. Period.

ALTER VIEW dbo.view_name
--Remove this WITH SCHEMABINDING
AS
  SELECT ...


mysql update based on select result Here are two SQL statements. One is an update statement and the other is a select statement. I want to combine them.

Update

update customer_entity
set website_id = 4
where website_id = 3

Select

SELECT count(*) c, email
FROM `customer_entity`
where website_id in (3,4)
group by email
having c <= 1) e

I tried:

update customer_entity
set website_id = 4
where website_id = 3
where email in
(
    SELECT count(*) c, email
    FROM `customer_entity`
    where website_id in (3,4)
    group by email
    having c <= 1
) e)

I want to update the website_id according to the select statement. I tried to implement it but not successful, does anyone know how to do that?

update customer_entity
set website_id = 4
where website_id = 3
AND email in
(
    select tt.email
    from
    (
        SELECT email
        FROM `customer_entity`
        where website_id in (3,4)
        group by email
        having count(email) <= 1
    ) as tt
)


Is this Dedicated Server good enough to Run MySQL on Linux Ubuntu? I have a web application that runs simple logic. Most of the SQL procedures that I have
only do simple selects, some simple join table and some simple inserts. I want to get
a dedicated server for My Database, I found one but I didn't get it yet. I want to get
some of your opinion on this machine before paying for it. I know I can benchmark it
but only after I own it. So any idea if this one is enough for 5000 users per day doing
simple reads and simple insertion into MySQL with InnoDB Engine ?

Core i5 Quad Core i5-2400 3.10 GHz
16 GB DDR3-1333 UDIMM
250 GB SATA HDD (7200 RPM)
100 Mbps Uplink Port Speed
Ubuntu 10.04 - 64 Bit

It's hard to know / to figure out:

Even some simple reads, could be IO hungry.
And the amount of users doesn't tell us how many request ( read / write).

Without more data, it hard to tell, but that seem to be a good server. I believe that it'll be good enough, but that's just a opinion.


Transposing/Wrangling Database Table Firstly, transposing is too specific a word for the kind of transform I want to do. I have a software that allows a technician to design custom forms for end users. From the form, I can see every entry on it (checkbox, textbox) is stored in a row. I have been asked to report on these forms, and the only way I can think of to make the data more usable is to wrangle it into a new table where each row represents one form entry.

Before:

Entry    FormElement    Value
1        Checkbox1      True
1        Textbox1       Joe Smith
2        Checkbox1      False
2        Textbox1       Jane Johnson

After:

Entry    Name          isMale
1        Joe Smith     True
2        Jane Johnson  False

Is there any other way to report on the data than what I am considering?
Should I use a stored procedure?

The only way I see with such a design is to self join the table several times. This means a JOIN for each column required in the output.

Notes:

The sample used is available toward the end and in this SQL Fiddle.
You will have to figure out what the value of FormElement means when writing the query
An age value has been added to it (bigger sample).
Expect bad performances with such a design on a large data set
An index on FormElement and Entry including Value or filtered indexes on FormElement may help.
It uses 1 subquery for each object because it helps see the separation between self joins.
I suppose there is always a row for each object from the form (hence the INNER JOIN)
I replace isMale by a CASE statement and Male or Female. It may help you.
Views may help writing shorter queries if some combination are often used.

People between 30 and 40

For example, if you want people between 30 and 40:

SELECT name.Entry
    , Name = name.[Value]
    , Sex = CASE WHEN sex.[Value] = 'True' THEN 'Male' ELSE 'Female' END
    , Age = age.[Value]
FROM (
    SELECT [Entry], [Value] FROM data WHERE [FormElement] = 'Textbox1'
) name
INNER JOIN (
    SELECT [Entry], [Value] FROM data WHERE [FormElement] = 'Checkbox1'
) sex
    ON name.Entry = sex.Entry
INNER JOIN (
    SELECT [Entry], [Value] FROM data WHERE [FormElement] = 'Textbox2'
) age
    ON name.Entry = age.Entry
WHERE age.[Value] > 30 AND age.[Value] < 40
;

Output:

Entry   Name        Sex     Age
1       Joe Smith   Male    32
3       Lucy Smith  Female  35

Name starting with a J

For people with a name with a J:

SELECT name.Entry
    , Name = name.[Value]
    , Sex = CASE WHEN sex.[Value] = 'True' THEN 'Male' ELSE 'Female' END
    , Age = age.[Value]
FROM (
    SELECT [Entry], [Value] FROM data WHERE [FormElement] = 'Textbox1'
) name
INNER JOIN (
    SELECT [Entry], [Value] FROM data WHERE [FormElement] = 'Checkbox1'
) sex
    ON name.Entry = sex.Entry
INNER JOIN (
    SELECT [Entry], [Value] FROM data WHERE [FormElement] = 'Textbox2'
) age
    ON name.Entry = age.Entry
WHERE name.[Value] LIKE 'j%'
;

Output:

Entry   Name            Sex     Age
1       Joe Smith       Male    32
2       Jane Johnson    Female  25

Sample data used

CREATE TABLE data
    ([Entry] int, [FormElement] varchar(9), [Value] varchar(12))
;

INSERT INTO data
    ([Entry], [FormElement], [Value])
VALUES
    (1, 'Checkbox1', 'True'),
    (1, 'Textbox1', 'Joe Smith'),
    (1, 'Textbox2', '32'),
    (2, 'Checkbox1', 'False'),
    (2, 'Textbox1', 'Jane Johnson'),
    (2, 'Textbox2', '25'),
    (3, 'Checkbox1', 'False'),
    (3, 'Textbox1', 'Lucy Smith'),
    (3, 'Textbox2', '35')
;


Secondary Clustered DTC will not work when Primary goes offline - SQL Server Issues I've been working on setting up clustered environment with a two new DTC roles, DTC1 and DTC2. I set up DTC1 which allows both clustered VM's and SQL Server Instances to function correctly. However, when I disable DTC1, DTC2 will remain online but no DTC transactions will work. I'm actually going through this issue with a Microsoft tech right now (without much luck). Both DTC services are set up with similar configurations. Anyone here have an idea as to what might be going on?

Here's my setup:

One clustered environment
Two DTC Roles
Two Separate ISCSI disks, one for each role
Two VM's (same configuration)
Two SQL Server Instances, on on each VM

Networking configuration is allowed, everything looks to be set up properly.

I don't believe what you want to do is possible through Windows or SQL Server configuration. Per Microsoft's guidance here, SQL Server will choose an MSDTC instance when it starts and, if that MSDTC instance fails, distributed transactions will fail:

What happens if the MSDTC Resource that was picked at SQL Service startup fails?
If the MSDTC instance that is being used by a specific SQL Server Group fails, SQL Server does not automatically attempt to use the next available MSDTC Resource such as the default cluster instance or the local machine instance of MSDTC.
You would need to completely remove the failed instance of MSDTC from the SQL Server group to use another instance of MSDTC.
Likewise, if you create a mapping for SQL Server and the mapped instance of MSDTC fails, your distributed transactions will also fail. If you want  SQL Server to use a different instance of MSDTC, you must either add an instance of MSDTC  to the local cluster group of the SQL Server or delete the mapping.

This matches the behavior you're seeing; SQL Server successfully opens DTC transactions through DTC1 but doesn't automatically switch to using DTC2 if DTC1 fails.
In some cursory tests I found:

DTC transactions resumed working when the failed MSDTC instance recovered.

SQL Server was indeed able to automatically switch to using a different MSDTC resource without a restart after the MSDTC resource it was using was deleted (as opposed to just failed).

The tests I ran weren't comprehensive and there might be scenarios where these findings aren't true.
The more important point in the document is that you may not need to cluster MSDTC at all:

What will SQL do if I do not Cluster MSDTC in Windows 2008 and above?
If there is no MSDTC resource in the cluster then SQL Server will use the MSDTC service that is running locally on the node.
What is the Simple recommendation for MSDTC with Windows 2008 and later?
In Windows 2008 and later you either Create a clustered instance of the MSDTC resource for EVERY SQL Server instance /Group that requires its functionality or DO NOT CLUSTER MSDTC at all.

Based on this, you should be able to achieve pretty good high availability by relying on local MSDTC resources (in which case you might need to configure them to restart automatically on failure) or through adding a clustered MSDTC resource to each SQL Server cluster group if you prefer for some reason. Windows can handle restarting the service on failure, and SQL Server appears to successfully resume creating DTC transactions once the MSDTC service recovers.
If you're trying to protect against scenarios not covered here (e.g., what happens if the DTC resource fails and can't restart automatically?) you might be forced to deal with that manually or at the application layer.
It will be interesting to see if Microsoft provides you with different advice.


Fastest way to change a 600GB table indexed key datatype from INT to BIGINT I need to change a datatype from INT to BIGINT in a 600GB MySQL table. The column has a unique index. I might be good with unsigned INT, but I assume changing to that or BIGINT will be pretty much same pain.  The table's engine is InnoDB. What would be easier:

ALTER TABLE
Copying structure and INSERT INTO (SELECT *)
Dumping table and changing dump file table definitions
Anything else?

UPDATE:
As requested, MySQL ver 5.5.15, no foreign  keys and create table:

 CREATE TABLE `tbl` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `user_id` int(11) NOT NULL,
    `created_at` datetime NOT NULL,
    `tid` bigint(20) NOT NULL,
    `t` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
    `f` tinyint(1) NOT NULL,
    `i_id` bigint(20) NOT NULL,
    `ir_id` int(11) NOT NULL,
    `r_c` int(11) NOT NULL,
    `r` tinyint(1) NOT NULL,
    `e` text CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
    `t` varchar(5) NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `user` (`user_id`,`tid`)
) ENGINE=InnoDB AUTO_INCREMENT=1657146169 DEFAULT CHARSET=utf8

Assuming your table does not have any triggers on it, you should consider using pt-online-schema-change since it will allow you to ALTER the table without locking it.

It will still take a fair amount of time given the size of the table.

Also, with this method or with ALTER TABLE you will need to make sure you have 600 GB of extra disk space to support two copies of the table while it is being rebuilt.


query generate the information one column in two columns in one query I have a question related with a query in sql.  I have the next table:

id        name
1         name1
2         country1
3         name2
4         country2

I need to call the results so:

id         name      id   country
1          name1      2    country1
3          name2      4    country2

Thanks for your help in advance.

To answer the question here is the query

SELECT A.*,B.*
FROM mytable A INNER JOIN mytable B
ON A.id = B.id -1 WHERE MOD(A.id,2)=1;

I made this query up based on staring at the output

It would have been nice to have a table called name_country perhaps like this:

CREATE TABLE name_country
SELECT A.id name_id,B.id country_id
FROM mytable A INNER JOIN mytable B
ON A.id = B.id -1 WHERE MOD(A.id,2)=1;
ALTER TABLE name_country ADD PRIMARY KEY (name_id,country_id);
ALTER TABLE name_country ADD UNIQUE INDEX (country_id,name_id);

That way going forward, you can maintain the name_country table in such a way that name and country do not have to be 1 id apart in the original table. You could freely enter names in bulk. Then later on, attach the country values.

Once you can create a name_country table, you could then do joins like this:

SELECT B.id,B.name,C.id,C.name
FROM name_country A
INNER JOIN mytable B ON A.name_id = B.id
INNER JOIN mytable C ON A.country_id = C.id;

UPDATE 2012-08-15 15:04 EDT

The query I first suggested

SELECT A.*,B.*
FROM mytable A INNER JOIN mytable B
ON A.id = B.id -1 WHERE MOD(A.id,2)=1;

is the answer

First here is your sample data

DROP DATABASE IF EXISTS cabita;
CREATE DATABASE cabita;
USE cabita
CREATE TABLE mytable
(id int not null auto_increment,
name varchar(20),primary key (id));
insert into mytable (name) values ('name1'),('country1');
insert into mytable (name) values ('name2'),('country2');
select * from mytable;

Here is your sample data loaded

mysql> DROP DATABASE IF EXISTS cabita;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE DATABASE cabita;
Query OK, 1 row affected (0.00 sec)

mysql> USE cabita
Database changed
mysql> CREATE TABLE mytable
    -> (id int not null auto_increment,
    -> name varchar(20),primary key (id));
Query OK, 0 rows affected (0.14 sec)

mysql> insert into mytable (name) values ('name1'),('country1');
Query OK, 2 rows affected (0.08 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into mytable (name) values ('name2'),('country2');
Query OK, 2 rows affected (0.07 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from mytable;
+----+----------+
| id | name     |
+----+----------+
|  1 | name1    |
|  2 | country1 |
|  3 | name2    |
|  4 | country2 |
+----+----------+
4 rows in set (0.01 sec)

Here is my original query executed

mysql>     SELECT A.*,B.*
    ->     FROM mytable A INNER JOIN mytable B
    ->     ON A.id = B.id -1 WHERE MOD(A.id,2)=1;
+----+-------+----+----------+
| id | name  | id | name     |
+----+-------+----+----------+
|  1 | name1 |  2 | country1 |
|  3 | name2 |  4 | country2 |
+----+-------+----+----------+
2 rows in set (0.03 sec)

mysql>

While the query works for your sample data, you have to pay close attention to how name relates to country. You would have to make sure of three things in order for my query work

the id of name is an odd number
the id of country is an even number
the id of name is one less id of country

That's one relationship I was able to take advantage of.

There is another relationship visible in your sample data: The number at the end of the each field. If you can enter names in bulk and then later load country in bulk, you would then have to rely on the number at the end of each name field. Such a query would look like this:

SELECT A.*,B.*
FROM mytable A INNER JOIN mytable B
ON SUBSTR(A.name,-1)=SUBSTR(B.name,-1)
WHERE A.name > B.name;

and would execute as follows

mysql>     SELECT A.*,B.*
    ->     FROM mytable A INNER JOIN mytable B
    ->     ON SUBSTR(A.name,-1)=SUBSTR(B.name,-1)
    ->     WHERE A.name > B.name;
+----+-------+----+----------+
| id | name  | id | name     |
+----+-------+----+----------+
|  1 | name1 |  2 | country1 |
|  3 | name2 |  4 | country2 |
+----+-------+----+----------+
2 rows in set (0.00 sec)

mysql>

or this query

SELECT A.*,B.*
FROM mytable A INNER JOIN mytable B
ON SUBSTR(A.name,-1)=SUBSTR(B.name,-1)
WHERE LEFT(A.name,4)='name'
AND LEFT(B.name,7)='country';

which would execute as follows

mysql>     SELECT A.*,B.*
    ->     FROM mytable A INNER JOIN mytable B
    ->     ON SUBSTR(A.name,-1)=SUBSTR(B.name,-1)
    ->     WHERE LEFT(A.name,4)='name'
    ->     AND LEFT(B.name,7)='country';
+----+-------+----+----------+
| id | name  | id | name     |
+----+-------+----+----------+
|  1 | name1 |  2 | country1 |
|  3 | name2 |  4 | country2 |
+----+-------+----+----------+
2 rows in set (0.00 sec)

mysql>

All these queries make assumptions on the content of the data. Such queries would produce very poor performance results. This is why I suggested creating an external table to perform the JOIN that would couple the id of name and its country together.

UPDATE 2012-08-16 17:19 EDT

In answer to the last question from the comment

SELECT A.*,B.*,
FROM mytable A
INNER JOIN mytable B ON SUBSTR(A.name,-1)=SUBSTR(B.name,-1)
INNER JOIN mytable C ON SUBSTR(A.name,-1)=SUBSTR(C.name,-1)
WHERE LEFT(A.name,4)='name'
AND LEFT(B.name,7)='country';
AND LEFT(C.name,11)='description';

If there is a different table involved for the descriptions, then perhaps this:

SELECT A.*,B.*,
FROM mytable A
INNER JOIN mytable B ON SUBSTR(A.name,-1)=SUBSTR(B.name,-1)
INNER JOIN difftable C ON SUBSTR(A.name,-1)=SUBSTR(C.name,-1)
WHERE LEFT(A.name,4)='name'
AND LEFT(B.name,7)='country';
AND LEFT(C.name,11)='description';


How to exclude non-distinct rows in a query I have a table that contains references to clients impacted in a problem. The parent table contains the problem info, specifically the ProblemID (PbMID). Since one problem can affect multiple clients, we store the client impacted data in a child table. The child table contains an ID field for housekeeping, a PbMID field which foreign keys back to the parent table, and a Company field containing the text name of the client.
I have a requirement to pull all the problems were a SINGLE client was impacted. If I use DISTINCT, I get all single client rows, but I also get the FIRST row of a multi-client problem, which is not what I'm being asked for.
Here's the client impacted table example
ID  | PbMID | Company    |
1   | 1     | Company 1  | Valid
2   | 4     | Company 2  | Valid
3   | 6     | Company 3  | Valid
4   | 22    | Company 1  | Invalid
5   | 22    | Company 4  | Invalid
6   | 23    | Company 5  | Valid
7   | 24    | Company 6  | Valid
8   | 25    | Company 1  | Invalid
9   | 25    | Company 8  | Invalid
10  | 25    | Company 10 | Invalid
11  | 26    | Company 2  | Valid
12  | 27    | Company 4  | Valid

The rows marked INVALID would not be included, since they reflect multi-client problems.
So, ideally, the return would be:
ID  | PbMID | Company    |
1   | 1     | Company 1  | Valid
2   | 4     | Company 2  | Valid
3   | 6     | Company 3  | Valid
6   | 23    | Company 5  | Valid
7   | 24    | Company 6  | Valid
11  | 26    | Company 2  | Valid
12  | 27    | Company 4  | Valid

Any help would be greatly appreciated. SQL isn't my forte, so I've been trying to wrap my head around this with no luck.

You could use GROUP BY, HAVING and a Common Table Expression (CTE)  to obtain the data.
The GROUP BY and HAVING provides all those PbMIDs that only impacted a single company. If you need PbMIDs that impacted n companies you could change the HAVING to HAVING =n  where n is the required number of companies.
SELECT
    PbMID
FROM
    ChildTable
GROUP BY PbMId
HAVING COUNT(Company) =1;

This can then be combined with a CTE to produce the final query below.
WITH CTE_SingleInstance (PbMIDSinglInstance)
AS
(
    SELECT
        PbMID
    FROM
        ChildTable
    GROUP BY PbMId
    HAVING COUNT(Company) =1
)
SELECT
    ChildTable.ID,
    ChildTable.PbMId ,
    ChildTable.Company
FROM
    ChildTable CT
JOIN
    CTE_SingleInstance  CTES
ON
    CT.PbMId  = CTES.PbMIDSinglInstance;


How do I select 1st row of each table and insert that data to a new table i have 5 tables (tab1, tab2, tab3, tab4 and tab5) and all the 5 tables has same table
definition. I want to select 1st row of each table and insert those 5 rows in
new table(newtab). I was only able to write query that select 1 row from one table and
insert that into newtab. How can i select 1st 5 rows from all the table and insert them
into newtab in one query.

In this case i have to deal with only 5 tables so its easy using union but when i want to select 1st rows from 100 tables then the query will become but lengthy using UNION.

This is not a complete answer (I'm sorry), but maybe a starting point for further thinking.

For a low number of tables UNION is the best approach IMHO. In special if the list of tables are not changing.

For a bigger number of tables you might can think of using some T-SQL and iterate through a list of tables and building up your query with dynamic SQL -- with this you can select your line(s) into a temporary table maybe.

As I'm not sure where you will get your list of tables from, you could get it from some cursor out of system tables or from some hard coded list maybe.


Tiny table causes extreme performance degradation, fixed by forced VACUUM. Why? I use PostgreSQL 9.6.

I have a query that joins 17 tables, 9 of those having several million rows. The query was running fine but its performance degraded rapidly this week. EXPLAIN's output didn't help (all scans are index scans except for the very small tables) and I had to try and remove tables from the query to isolate one that caused the degradation.

It turns out that an unremarkable table containing 40 rows broke the query: 800 ms without the table vs 30 s with it. I ran VACUUM FULL on the table, which ran in about a second and now the performance is back to normal.

My questions:

What can explain that a <10kb table breaks performance like this?
How to avoid the same problem in the future?

In the process of debugging, I made a base backup to another server, so I have two filesystem-level copies of the DB, one of them where I didn't run VACUUM FULL. When I logged on to the unvacuumed copy with pgAdmin, I got the following message:

  The estimated rowcount on the table "public.clients" deviates significantly from the actual rowcount. You should run VACUUM ANALYZE on this table.

The unvacuumed table has 40 rows counted and 0 estimated. Here are the rest of its statistics in a screenshot.

The table may be small, but as long as Postgres expects roughly 0 rows, chances are it is going to chose a different query plan than for roughly 40 rows - for which the same query plan is not as efficient.

Since joins multiply result rows rather than just adding to them, the 40 rows in the tiny table can have a massive effect when joined to big tables with several million rows like in your example. The difference can easily explain a factor 30 in execution time.
Or as the manual puts it:

  It is important to have reasonably accurate statistics, otherwise poor
  choices of plans might degrade database performance.

Default autovacuum settings are ok for most installations. Consider:

Are regular VACUUM ANALYZE still recommended under 9.1?

But for a database holding multiple tables with several millions rows, I would consider tuning per-table settings for selected tables and a manual ANALYZE on the whole DB from time to time.

The remaining questions

Q1. Why did autovacuum not launch ANALYZE automatically?
Q2. Why did VACUUM FULL fix the problem?

Q2 is simple: While other important statistics are only updated by ANALYZE, the basic count estimate in pg_class.reltuples is updated more often. The manual:

  Number of rows in the table. This is only an estimate used by the
  planner. It is updated by VACUUM, ANALYZE, and a few DDL commands such
  as CREATE INDEX.

Q1 is more sophisticated.

The manual again:

  The daemon schedules ANALYZE strictly as a function of the number of
  rows inserted or updated; it has no knowledge of whether that will
  lead to meaningful statistical changes.

Relevant settings (among others):

  autovacuum_analyze_threshold (integer)

  Specifies the minimum number of inserted, updated or deleted tuples
  needed to trigger an ANALYZE in any one table. The default is 50
  tuples. This parameter can only be set in the postgresql.conf file or
  on the server command line; but the setting can be overridden for
  individual tables by changing table storage parameters.

  autovacuum_analyze_scale_factor (floating point)

  Specifies a fraction of the table size to add to
  autovacuum_analyze_threshold when deciding whether to trigger an
  ANALYZE. The default is 0.1 (10% of table size). This parameter can
  only be set in the postgresql.conf file or on the server command line;
  but the setting can be overridden for individual tables by changing
  table storage parameters.

Bold emphasis mine.

Demo

Be sure the test DB is mostly idle to avoid testing artifacts and you are running with default settings:

SELECT * FROM pg_settings WHERE name ~ '^autovacuum|track_counts';

Most importantly:

autovacuum_analyze_scale_factor = 0.1
autovacuum_analyze_threshold = 50
autovacuum_naptime = 60
track_counts = on

Basically, autovacuum checks once every minute whether any table has last_estimate / 100 + 50 rows changed and launches ANALYZE for those.

To understand what happened in your case:

CREATE TABLE t50 (id int primary key, foo text);
INSERT INTO t50 SELECT g, 'txt' || g FROM generate_series(1,50) g;
SELECT reltuples FROM pg_class WHERE oid = 't50'::regclass;

pg_class.reltuples is the estimated row count of the table. More here:

Fast way to discover the row count of a table in PostgreSQL

You'll get 0. Wait for 2 minutes to make sure we cross the 1 minute delay. Check again. Still 0. Now insert one more row and check again:

INSERT INTO t50 VALUES (51, 'txt51 triggers analyze');
SELECT reltuples FROM pg_class WHERE oid = 't50'::regclass;

Still 0. Wait another 2 minutes, check again. Tada! We see the updated count of 51. Autovacuum did not kick in until 51 rows were inserted (or updated or deleted).

To see more details (including the timestamp of the last_autoanalyze):

SELECT * FROM pg_stat_all_tables WHERE relid = 't50'::regclass;

Related:

When is Postgres autovacuum executed

Solution

Run ANALYZE on public.clients manually once (or on the whole DB, it's cheap) and use more aggressive per-table autovacuum settings for this important table. Like:

ALTER TABLE public.clients SET (autovacuum_analyze_scale_factor = 0.01
                              , autovacuum_analyze_threshold = 10);

You might also want to audit the settings for some of your big tables for other reasons. Compare:

Debug query on big table that is sometimes slow

Also important

You are joining 17 tables, which is well beyond the default setting for join_collapse_limit which is 8. You may want to use explicit join syntax (maybe you already do) and rewrite your query to place the most selective tables (or the ones with the most selective predicates) first in the SELECT list. Related:

Complex view becomes slow when adding ORDER BY in outer query with small LIMIT
Can we execute an optimal plan instead of a generic one in the first execution of a PL/pgSQL function?

P.S.:
I think I found a minor documentation bug while running the test above. The manual on autovacuum_analyze_threshold reads:

  Specifies the minimum number of inserted, updated or deleted tuples
  needed to trigger an ANALYZE in any one table

This would indicate that 50 inserts trigger ANALYZE, not 51 like I observed. Similar in pg_settings.short_desc:

  Minimum number of tuple inserts, updates, or deletes prior to analyze.

In fact, the explanation for autovacuum in the manual here matches my observation:

  Otherwise, if the number of tuples obsoleted since the last VACUUM exceeds the "vacuum threshold", the table is vacuumed.

The first two sentences seem slightly incorrect.
I filed a bug report.


Which is more efficient: contradict a unique constraint or check if row exists? If I'm regularly going to to attempt to add a duplicate row in a table and duplicates aren't allowed, should I attempt the insert and catch an exception or manually check for the row's existence and only try the insert if the row isn't found?

In both cases, the unique constraint exists.

You need the unique index (or constraint, I prefer indexes because of includes and filters) anyway to guarantee data integrity.

So the question is one of

do check-as-part-of-insert? OR
try insert-and-catch?

Note, checking before insert is not reliable, for at least these reasons

if someone does it manually
assumes you have a consistent code path to insert
2 concurrent IF checks can potentially both see that the row does not exist and try to insert the same value

Using INSERT...WHERE NOT EXISTS (As mentioned in comments) will use the index, so will be efficient anyway.

There is no benefit in dealing with exceptions unless you have a real need to.


Resource cost of a blocked query I'm currently reviewing a case where an SSRS server is experiencing frequent blocking and deadlocking.
The reports are not business-critical, and they are frequently killed to alleviate the blocking.

However, this same SQL Server has some critical databases on it (it's sharing a sql server with a large sharepoint farm), that have been showing declining performance.
The plan from my predecessor is as follows:

Migrate 4TB worth of databases from A to B
Migrate Reporting server (originally A) from B to C
Migrate Reporting server (originally C) from C to B

The reasoning being that we will have better hardware (B), be able to get rid of an old server (A), and remove the reporting server (ends up at C) from the critical database server (now B).

However, I'm not entirely convinced that this will impact performance much (save for the newer hardware).
I've been monitoring the blocks for the better part of a week now, and it's consistently the WriteLockSession blocking a ReadChunkSegment. Which as far as I can tell from documentation (google) is perfectly fine.

How could I determine that/if a blocked process is causing slowdown on a different database?
If I could show that the performance issues are unrelated (reporting and other databases) other than the shared hardware, I would like to set up shared datasets. As most of the reports, and all of the long-running reports, are querying the same small subset of data.

A request that is being blocked does no work.  Here's an example:

Create a blocking query

use AdventureWorks2012;
go

begin tran;
    update HumanResources.Department
    set Name = 'testing 1 2 3'
    where DepartmentID = 7;
--rollback tran;

Run a query that will be blocked by the above session

use AdventureWorks2012;
go

select *
from HumanResources.Department;

Now look at any "work" this blocked request could be doing (query sys.dm_exec_requests for this blocked session, and then wait some time and query again to compare/contrast):

select
    start_time,
    status,
    total_elapsed_time_sec =
        total_elapsed_time / 1000,
    cpu_time,
    reads,
    logical_reads,
    writes
from sys.dm_exec_requests
where session_id = 52;  -- my blocked session's spid

waitfor delay '00:00:10';

select
    start_time,
    status,
    total_elapsed_time_sec =
        total_elapsed_time / 1000,
    cpu_time,
    reads,
    logical_reads,
    writes
from sys.dm_exec_requests
where session_id = 52;  -- my blocked session's spid

What you see here is a request that is doing no work, as it is not in the running state.  When it is suspended then it is not on a processor doing any work.


Database design for an exam I am designing database for an exam, and I am stuck. I do not know how to do it.

Here is the relevant information:

Student answers 10 questions.
Each question has 3 options (answers) and student chooses one.
Only one answer is correct, other 2 are wrong.
 Students can take this exam only once. They can not try again.
There will be no other exams, this is the only one.

I need help with database design.

What I have tried so far on my own:

Table Students

ID bigint (primary key, identity)
Name nvarchar(MAX)

Table Questions

ID bigint (primary key, identity)
TextOfTheQuestion nvarchar(MAX)

Table Answers

ID bigint (primary key, identity)
TextOfTheAnswer nvarchar(MAX)
QuestionID bigint (foreign key to Questions.ID)
isCorrectAnswer bit

Table StudentChoices

StudentID bigint (primary key, foreign key to Students.ID)
AnswerID bigint (primary key, foreign key to Answers.ID)

This is my personal design for learning purposes. I am trying to learn Entity framework + C# on my own.

Easy on the bigint and nvarchar(MAX)

Table Student:

ID int (primary key, identity)
Name nvarchar(800)

Table Question:

ID smallint (primary key, identity)
TextOfTheQuestion nvarchar(800)
CorrectAns tinyint FK (to AnswerNum.Num)
tested and can make a composite FK to Answer  QuestionID, AnsNum
I was not sure could do this but it would mean you have to create the question
Then populate Answer
Then come back and edit question for CorrectAns so may not be the way you want to go

Table Answer:

QuestionID smalling PK FK (to Question.ID)
AnswerNum tinyint   PK FK (to AnswerNum.Num)
TextOfTheAnswer nvarchar(800)

Table StudentChoice:

StudentID int       (primary key, foreign key to Student.ID)
QuestionID smallint (primary key)
AnswerNum tinyint
(QuestionID, AnswerNum) REFERENCES Answer (QuestionID, AnswerNum)

Table AnswerNum:

Num tinyint PK
-- just values 1, 2, 3
-- this way you can change number of questions in the future

This is how you score
When the primary query comes out clean it is sign of a good db design

  select Student.Name, count(*) as score
  from student
  left join StudentChoice
         on Student.ID = StudentChoice.StudentID
  left join Question
         on Question.QuestionID = StudentChoice.QuestionID
        and Question.CorrectAns = StudentChoice.AnswerNum
            -- CorrectAns in Question makes this a whole lot easier
  group by Student.Name


How to match a version of SQL Server 2008? It is needed to install a new instance of SQL to the existing Windows SQL Server 2008. I have no idea what was used to originally install the existing 4 instances. Is it R5 SP32? Thanks to MS wonderful versioning system nobody knows just by looking at Add/Remove.

The version in Add/Remove is listed as 10.1.2531.0 for all the SQL components (funnily enough, all except the main "MS SQL Server 2008 (64-bit)". When I click "modify" under there it asks where is the original installation dir? I would be happy to know too.

How to find "just the right" installer, in order to avoid version hell, upgrading the existing instances (this is production) and other mess?

Version 10.1.2531.0 is for SQL Server 2008 Service Pack 1.
The following table lists the major releases of SQL Server 2008.

Release                         Product version

SQL Server 2008 Service Pack 3  10.00.5500.00
SQL Server 2008 Service Pack 2  10.00.4000.00
SQL Server 2008 Service Pack 1  10.00.2531.00
SQL Server 2008 RTM 10.00.1600.22

Check more details on SQL Server version

EDIT:

You can query database to get these info from database itself like

SELECT
 SERVERPROPERTY('ProductVersion') AS ProductVersion,
 SERVERPROPERTY('ProductLevel') AS ProductLevel,
 SERVERPROPERTY('Edition') AS Edition,
 SERVERPROPERTY('EngineEdition') AS EngineEdition;

More details on SERVERPROPERTY


How can I identify correlated subqueries that can not be rewritten as JOINs without using DISTINCT? I am fairly new to SQL query tuning. I have been trying to understand how to write equivalent queries. While going through Prof. J. Widom's Stanford online video lectures, she makes mention that some subqueries that cannot be written by JOINs without the use of DISTINCT. For example, see this:

4:45 / 20:13 - GPA example

SELECT GPA
FROM Student
WHERE sID in (select sID from Apply where major = 'CS');

6:39 / 20:13 - students applied to CS but not to EE

SELECT sID, sName
FROM Student
WHERE sID IN (select sID from Apply where major = 'CS')
  AND sID NOT IN (select sID from Apply where major = 'EE');

My question is how can one know if an SQL statement written using  subqueries will have an equivalent one written using joins. I am quite comfortable, if in the answers, someone likes to use relational algebra notation.

I searched quite a bit on the Net and could not find a suitable answer.

Sample Data

Schema and table creation (for PostgreSQL) are as follows,

CREATE TEMP TABLE college AS
SELECT cname::text, state::text, enrollment::int
FROM ( VALUES
  ('Stanford', 'CA', 15000),
  ('Berkeley', 'CA', 36000),
  ('MIT', 'MA', 10000),
  ('Cornell', 'NY', 21000)
) AS College(cname, state, enrollment);

CREATE TEMP TABLE student AS
SELECT sid::int, sname::text, gpa::real, sizehs::int
FROM ( VALUES
  (123, 'Amy', 3.9, 1000),
  (234, 'Bob', 3.6, 1500),
  (345, 'Craig', 3.5, 500),
  (456, 'Doris', 3.9, 1000),
  (567, 'Edward', 2.9, 2000),
  (678, 'Fay', 3.8, 200),
  (789, 'Gary', 3.4, 800),
  (987, 'Helen', 3.7, 800),
  (876, 'Irene', 3.9, 400),
  (765, 'Jay', 2.9, 1500),
  (654, 'Amy', 3.9, 1000),
  (543, 'Craig', 3.4, 2000)
) AS Student(sid, sname, gpa, sizehs);

CREATE TEMP TABLE apply AS
SELECT sid::int, cname::text, major::text, decision::text
FROM ( VALUES
  (123, 'Stanford', 'CS', 'Y'),
  (123, 'Stanford', 'EE', 'N'),
  (123, 'Berkeley', 'CS', 'Y'),
  (123, 'Cornell', 'EE', 'Y'),
  (234, 'Berkeley', 'biology', 'N'),
  (345, 'MIT', 'bioengineering', 'Y'),
  (345, 'Cornell', 'bioengineering', 'N'),
  (345, 'Cornell', 'CS', 'Y'),
  (345, 'Cornell', 'EE', 'N'),
  (678, 'Stanford', 'history', 'Y'),
  (987, 'Stanford', 'CS', 'Y'),
  (987, 'Berkeley', 'CS', 'Y'),
  (876, 'Stanford', 'CS', 'N'),
  (876, 'MIT', 'biology', 'Y'),
  (876, 'MIT', 'marine biology', 'N'),
  (765, 'Stanford', 'history', 'Y'),
  (765, 'Cornell', 'history', 'N'),
  (765, 'Cornell', 'psychology', 'Y'),
  (543, 'MIT', 'CS', 'N')
) AS apply(sid, cname, major, decision);

My question is how can one know if an SQL statement written using subqueries will have an equivalent one written using joins. I am quite comfortable, if in the answers, someone likes to use relational algebra notation.

Good question.

First Example Given

So let's look at the first query she gives..

SELECT GPA
FROM Student
WHERE sID in (SELECT sID from Apply where major = 'CS');

And, then let's look at a similar query with joins,

SELECT *
FROM student
JOIN apply ON (student.sid = apply.sid AND major = 'CS');

I've changed the columns in  in the first and second intentionally. "GPA" here, is specifically student.GPA.

If the following, then the correlated subquery can not be rewritten as a simple join,

the SELECT statement has aggregates WHERE gpa in (SELECT max(gpa)..) (if the join requires aggregation or grouping, you'll have to use a derived or virtual table: JOIN ( SELECT.. ) AS t.
if the JOIN condition is NOT UNIQUE in the above, we assume there is one student with a matching row having both a common sid and major=CS. The resulset can grow or shrink of this assumption fails.

You can easily test that..

SELECT sid, count(*)
FROM apply
WHERE major='CS'
GROUP BY sid
HAVING count(*) > 1;

This returns two rows, both sids would get duped in the join.

 sid | count
-----+-------
 123 |     2
 987 |     2

As an exercise in clarity, you could push the DISTINCT down yourself, and this can again be rewritten using a JOIN.

SELECT GPA
FROM student
JOIN (
  SELECT DISTINCT sid, major
  FROM apply
) AS apply
  ON (student.sid = apply.sid AND major = 'CS');

Or, even..

SELECT GPA
FROM student
JOIN (
  SELECT DISTINCT sid, major
  FROM apply
  WHERE major = 'CS'
) AS apply
  USING (sid);

Second Example Given

The second example,

SELECT sID, sName
FROM Student
WHERE sID IN (select sID from Apply where major = 'CS')
  AND sID NOT IN (select sID from Apply where major = 'EE');

Falls victim to the first example exactly. However, the NOT IN,

Doesn't matter for UNIQUE-ness because we're excluding results.
Moreover, rewriting the NOT IN as an anti-join (LEFT OUTER JOIN ... ON NULL) is likely faster.
It will handle any condition where apply.sid IS NULL more effectively, including the case where there are no major = 'EE'

Here is an example,

SELECT student.sID, sName
FROM Student
LEFT OUTER JOIN apply ON (student.sid = apply.sid AND major = 'EE')
WHERE student.sID IN (select sID from Apply where major = 'CS')
  AND apply.sid IS NULL;

In summary

Joins (including INNER JOINS) can increase or decrease the amount of rows in the result set.

If the JOIN condition is never true, the result on an INNER JOIN is pruned.
If the result is true more than once for the same row, the result is that the amount of rows returned increases.

But, joins join two tables. On t1 JOIN t2,

if the t1, and t2 have no duplicate rows then SELECT t1.*, t2.* should have no dupes. Something should be different.
If either t1 or t2 have duplicate rows then a JOIN that includes those row will result in duplicate output rows.
If you SELECT a subset of t1, or a subset of t2 (as you did above when you ONLY selected GPA) then it's possible to have something that appears to be a dupe, though it's not. Had you have selected student.GPA, apply.* you would have seen the reason for the duping.


Which databases are backed up by "mysqldump --all-databases"? Which databases are included in the dump generated by mysqldump --all-databases?

By my experience, that should be all the user-created databases, plus the mysql database. The database information_schema is backed up only if explicitly mentioned, and performance_schema is never backed up. Is this correct?

I just did mysqldump --all-databases in MySQL 5.6.22 for Windows.
Neither performance_schema nor information_schema are in the dump.
It says so the the MySQL Documentation

Restrictions
mysqldump does not dump the INFORMATION_SCHEMA or performance_schema database by default. To dump either of these, name it explicitly on the command line. You can also name it with the --databases option. Also, use the --skip-lock-tables option.

That's a good thing. Why ?
PERFORMANCE_SCHEMA
The instrumentation tables within performance schema are transient within the DB Server. The metrics from one time period to another can be different or the same (thus, trying to use PERFORMANCE_SCHEMA idempotently would never reveal the same results). Such results would make absolute no sense loaded onto a different server or even back into the source from another time period. Therefore, performance_schema is not dumped by default.
If you simply need to recreate the performance_schema database from backup because you dropped it accidentally, that would be OK. Then, upon using the instrumentation, stuff would get rewritten anyway.
If you wanted to snapshot the instrumentation results to view later on in a text editor or an app, that would OK as well.
INFORMATION_SCHEMA
This is also transient. Furthermore, all information_schema tables are stored in memory and simple hold metadata about the tables in the instance since MySQL started. (See my old post How is INFORMATION_SCHEMA implemented in MySQL?) Dumping it and reloading it into another MySQL Instance is completely useless because mysqld would immediately deny its load (or at the very least, mysqld would start reloading its metadata by re-reading the mysql schema and all table files and tablespaces again if it had to).
If you wanted to capture the state of all tables and their files at a moment in time without touching the tables, then dumping INFORMATION_SCHEMA and viewing the dumps from an editor or some app would be OK.
EPILOGUE
Think of it this way: If you had a slow log from one MySQL instance and copied it to another, how does that benefit you as a Developer or DBA with queries that did not run on the new server ?
In like fashion, having performance metrics and table metadata from one server would not serve any useful purpose being dumped then reloaded back to the original server or another server. mysqld needs to have a clear picture of the data files it currently manages, not what mysqld managed on another server or at some time in the past.
CAVEAT
You should refrain from backing up the mysql schema unless you restore it to the same machine or at least to another machine with the same major release of MySQL.
Please see my old post Backup and restore "mysql" database as to why you should not.


How can prevent writes to a Postgres schema for a period of time? I am moving a schema from one server to another using pg_dump + pg_restore + drop schema cascade. I need to know that no applications are writing to it (or at least that the writes will fail) during this process to avoid losing data.

At the moment I am renaming the schema before starting the dump. Is that sufficient to prevent writes? What if a large batch insert is busy when I rename the schema? Will that still complete?

You can REVOKE access from that schema, but I am affraid the application will have some unwanted effects. PostgreSQL has fine tuned permission administration. And later you can GRANT access to that schema.


create foreign keys for pivot tables I'm trying to optimise and speed up a mysql 5.6 datatabase.

I have two tables with a many to many relationship:

Reservation and Resource. Each Reservation can have many Resources and each Resource can have many Reservations. I have a pivot table between the two tables which simply joins the two via their respective Id's

Should I be creating foreign keys within the pivot table for each of the ID's? Will this speed up queries or does this make little difference with a pivot table.

My initial thoughts are that I should but don't want to create a mess!

Foreign key are constraints for data consistency, and they should be added always as a security measure. In order for the foreign keys to be fast, MySQL also creates its corresponding indexes unless the columns are already indexed. That is independently of the performance question: create the foreign keys to avoid storing incorrect data.

In most cases, you may want to index the pivot table because the most typical queries that you will be doing are along these lines:

SELECT *
FROM Reservation RV
JOIN pivot p ON p.reservation_id = RV.id
JOIN Resource RS ON p.resource_id = RS.id
WHERE filter(column_on_Reservation_or_Resource)

In that case, you will traverse the pivot table in Reservation->Resource direction or viceversa. For that, the best index would be pivot(reservation_id, resource_id) (and/or viceversa if you are using it in the other direction), as that way you can get advantage of the covering index optimization. One of the directions can be the primary key, if the pairs happen to be unique (relationships cannot be multiple among the same pair of records from reservation and resource).


how much to keep table_open cache and what settings is available in mysql to release unwanted memory cache and table cache? how much to keep table_open cache and what settings is available in mysql to release unwanted memory cache and table cache?
MY server has 672GB RAM windows environment server 2012
Innodb_buffer_pool_size = 450GB
Can you please suggest me what are the things need to check ?

Each unit in table_open_cache is one table (not one byte).  Since you have a huge amount of RAM, the 'right' answer is probably to make it a little more than the number of tables in all the databases.

It is limited to open_files_limit, which, in turn, is limited by the OS.  Some OSs have an artificially low 1024, but it can be changed.

Suggest table_open_cache_instances = 16 if your version includes that relatively new feature.

If you would like a lengthier check, please provide SHOW VARIABLES; and SHOW GLOBAL STATUS; after mysqld has been running for more than a day, and preferably with a realistic load.

A common mistake is setting query_cache_size bigger than about 50M.  The code in the QC was not designed to scale.  Anyway, most production servers run better with the QC turned off.

The buffer_pool is the main cache in RAM.  There are a number of lesser ones.  Is this what you mean by "memory cache"?

Will this server be running anything other than MySQL?  If so, you should subtract that from the 672G when posing the question.


SQL Server 2000 : Upgrade from Personal Edition to Enterprise Edition I've never tried this before but this is what I'm attempting to do :

Backup all the databases
Copy current master.mdf and master.ldf to another location (for later purpose)
Uninstall SQL Server 2000 Personal Edition
Install SQL Server 2000 Enterprise Edition (SP4)
Stop the newly installed server, and replace the master.mdf and master.ldf with those old two files above
Restart server

I've done this before, but only with Enterprise Edition, after reinstalling SQL Server I'll just have to replace the master file and everything is fine.

Will it work in this case ?

I would like to post this as answer what is point in upgrading to SQL server 2000 which is outdated completely without any support. Even it is not good for testing purpose. Its like using typewriter in era or super computers.

Dont uninstall personal edition first. Install enterprise move databses by backup restore method. If you restore master msdb databases you would not need to move logins and jobs.

Please refer to below link

https://www.barracudaware.com/files/docs/9.00/help/restoring_microsoft_sql_server_2000_master_database.html


how to downgrade an unknown (2005) interface name from SSIS 12 to 05 The namespace Microsoft.SqlServer.Dts.Runtime.Wrapper was research, but a suitable SSIS 2005 counterpart to a SSIS 2012 interface named IDTSConnectionManager100 was not found.
Any help for the concrete old interface name would be appreciated.
Or do you know a kind of mapping overview / website for this kind of issue?

In SQL Server 2005, there is an IDTSConnectionManager90.  It comes with the note "this interface supports the SQL Server 2005 infrastructure and is not intended to be used directly from your code."  A very similar note is provided for IDTSConnectionManager100.

These two are not 100 percent identical, of course.

However, these are SQL Server version specific components and are intended to be used through the ConnectionManager class.  For 2005 see:

http://technet.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.connectionmanager(v=sql.90).aspx

http://technet.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.connectionmanager_members(v=sql.90).aspx


That's a tricky job for MySQL (PostgreSQL and other DBs) can handle it much easier.

For the sake of clarity, I've changed your tag.name to tag.tag and user.name to user.user_name, so that there's less risk of confusing the names. Note also that your data contains a user 4. I've called him James.

Let's do it one step at a time.

Step 1

First, let's get your expected output minus the tags:

SELECT
     u.id,
     u.user_name,
     coalesce(sum(r.score), 0) as score,
     coalesce(sum(r.reputation), 0) as reputation
FROM
    users u
    LEFT JOIN reputations r
        ON r.user_id = u.id
           AND r.date_time > 1500584821 /* unix_timestamp(DATE_SUB(now(), INTERVAL 1 WEEK)) */
GROUP BY
    u.id, u.user_name
ORDER BY
    reputation DESC, score DESC ;

id | user_name | score | reputation
-: | :-------- | ----: | ---------:
 1 | Jack      |     0 |         18
 4 | James     |     1 |          5
 2 | Peter     |     0 |          0
 3 | Ali       |     0 |          0

NOTE: the condition r.date_time > 1500584821 must be part of your JOIN ON condition. If it is in the where clause, your LEFT JOIN becomes just an INNER JOIN, and you will lose users with no reputation.

Step 2

Now we make another query that returns, for every user_id, the list of tags and its corresponding reputation:

SELECT
    u.id, u.user_name, t.tag, sum(r.reputation) AS tag_reputation
FROM
    users u
    LEFT JOIN reputations r
        ON r.user_id = u.id
            AND r.date_time > 1500584821 /* unix_timestamp(DATE_SUB(now(), INTERVAL 1 WEEK)) */
    JOIN post_tag pt ON pt.post_id = r.post_id
    JOIN tags t ON t.id = pt.tag_id
GROUP BY
    u.id, u.user_name, t.tag
ORDER BY
    u.id, tag_reputation DESC;

id | user_name | tag   | tag_reputation
-: | :-------- | :---- | -------------:
 1 | Jack      | css   |             15
 1 | Jack      | php   |              5
 1 | Jack      | mysql |              5
 1 | Jack      | html  |             -2
 4 | James     | html  |              5

Step 3

We LEFT JOIN the two together (after a smallish simplification of the second, to ignore user names there), ON user_id and GROUP BY user_id, user_name, score, reputation

SELECT
    q1.user_id, q1.user_name, q1.score, q1.reputation,
    substring_index(group_concat(q2.tag  ORDER BY q2.tag_reputation DESC SEPARATOR ','), ',', 2) AS top_two_tags
FROM
    (SELECT
        u.id AS user_Id,
        u.user_name,
        coalesce(sum(r.score), 0) as score,
        coalesce(sum(r.reputation), 0) as reputation
    FROM
        users u
        LEFT JOIN reputations r
            ON    r.user_id = u.id
              AND r.date_time > 1500584821 /* unix_timestamp(DATE_SUB(now(), INTERVAL 1 WEEK)) */
    GROUP BY
        u.id, u.user_name
    ) AS q1
    LEFT JOIN
    (
    SELECT
        r.user_id AS user_id, t.tag, sum(r.reputation) AS tag_reputation
    FROM
        reputations r
        JOIN post_tag pt ON pt.post_id = r.post_id
        JOIN tags t ON t.id = pt.tag_id
    WHERE
        r.date_time > 1500584821 /* unix_timestamp(DATE_SUB(now(), INTERVAL 1 WEEK)) */
    GROUP BY
        user_id, t.tag
    ) AS q2
    ON q2.user_id = q1.user_id
GROUP BY
    q1.user_id, q1.user_name, q1.score, q1.reputation
ORDER BY
    q1.reputation DESC, q1.score DESC ;

user_id | user_name | score | reputation | top_two_tags
------: | :-------- | ----: | ---------: | :-----------
      1 | Jack      |     0 |         18 | css,mysql
      4 | James     |     1 |          5 | html
      2 | Peter     |     0 |          0 | null
      3 | Ali       |     0 |          0 | null

Note that group_concat(q2.tag  ORDER BY q2.tag_reputation DESC SEPARATOR ',') does return all tags associated with the user. We cut to two by means of substring_index(string,separator,number).

You can find all the settings, and queries at dbfiddle here

Side note (ties when ranking): for user Jack, the php and mysql tags have the same tag_reputation. I.e.: there's a tie between second and third. In that case, and without a second criterion for sorting the tags, both css,mysql and css,php are acceptable answers for the "top two" tags.


How to insert Error_Message using EXECUTE sp_executesql Using MS SQL Server I have the below code - it is being executed via a EXECUTE sp_executesql command:

Declare @Cmd nvarchar(max)
;with DistinctTables as
(
select distinct [DestTable], [SourceFile] from [tbl_IN_Ctrl_Dtl]
),
InsertCommands as
(
-- columns from Destination table
select *,
'BEGIN TRY insert into ' + [DestTable] + '(' +
STUFF((
        SELECT ',' + [DestCol]
        FROM [tbl_IN_Ctrl_Dtl] t1
        where t1.DestTable = drt.DestTable
        GROUP BY DestCol,ORDINAL_POSITION
        ORDER BY ORDINAL_POSITION
        FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'),1,1,'')
+ ')' +
' select ' +
-- columns from source table
STUFF((
        SELECT ',' + [SourceCol]
        FROM [tbl_IN_Ctrl_Dtl] t1
        where t1.DestTable = drt.DestTable
        GROUP BY SourceCol,ORDINAL_POSITION
        ORDER BY ORDINAL_POSITION
        FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'),1,1,'')
        + ' from ' + [SourceFile]
        + ';
        update tbl_IN_Ctrl_Header set ErrMsg = @@ERROR where SourceFile_INSERT = 0 and SourceFile = ''' + [SourceFile]+ ''''
        +';
        update tbl_IN_Ctrl_Header set SourceFile_INSERT = 1 where SourceFile_INSERT = 0 and SourceFile = ''' + [SourceFile]+ ''''
        +'; END TRY BEGIN CATCH'
        as InsertCommand
 from DistinctTables drt
)
select @cmd =
STUFF((
        SELECT ';' + char(10) + [InsertCommand]
        FROM InsertCommands
        FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'),1,1,'')
+';' from InsertCommands ic

--print @cmd

EXECUTE sp_executesql @cmd

I can retrieve the error number using line:

update tbl_IN_Ctrl_Header set ErrMsg = @@ERROR where SourceFile_INSERT = 0 and SourceFile = ''' + [SourceFile]+ ''''

However, how do I retrieve the actual text error message?  If I use ERROR_MESSAGE I just get Null.  How do I modify the above to update 'ErrMsg' to the actual text error message?  I've put a BEGIN TRY at the beginning of the Insert and END TRY at the end of the UPDATE but I now receive the error Incorrect syntax near ';'.

Let's start simpler. To retrieve an ERROR_MESSAGE() derived during execution of dynamic SQL, outside of the dynamic SQL, one way is to use an output parameter.

SET NOCOUNT ON;

DECLARE @msg nvarchar(max);

DECLARE @sql nvarchar(max) = N'BEGIN TRY
  SELECT 1/0;
END TRY
BEGIN CATCH
  SET @msg = N''Error: '' + ERROR_MESSAGE();
END CATCH;';

EXEC sys.sp_executesql @sql, N'@msg nvarchar(max) OUTPUT', @msg OUTPUT;
PRINT @msg;

Result:

Error: Divide by zero error encountered.

I'm having a hard time reverse engineering all your code to determine exactly what you are trying to do with the error message, and whether you want to do that for every row that fails, inside the dynamic SQL, or what. But I do feel like you should start simpler. Get it working for one thing, then try doing all this stuff/concatenation business.

To just update a row in a table (again, in a much simpler scenario than you have in the question):

CREATE TABLE #x(msg nvarchar(max));

INSERT #x(msg) VALUES(NULL);

DECLARE @sql nvarchar(max) = N'BEGIN TRY
  SELECT 1/0;
END TRY
BEGIN CATCH
  UPDATE #x SET msg = ERROR_MESSAGE()
  -- WHERE... ;
END CATCH;';

EXEC sys.sp_executesql @sql;

Result:

SELECT msg FROM #x;

---------------------------------
Divide by zero error encountered.

I think your main problem is missing parens, missing underscore, or using @@ERROR instead of ERROR_MESSAGE().


Geographic Hierarchies in a OLAP Cube / Data Warehouse Are the following geographic hierarchies correct?
Do any US/Canadian area codes cross state/province lines?
Do any US/Canadian counties cross state/province lines?
Are there countries with counties but no principal country divisions (states/provinces)?

Zip / Postal Codes

US Style

Continent > Country > State/Province > Postal/Zip

UK Style

Continent > Country > Postal Code

Towns / Cities

US Style

Continent > Country > State/Province > City

UK Style

Continent > Country > City

Area Codes

US Style

Continent > Country > State/Province > Area Code

UK Style

Continent > Country > Area Code

Counties

US Style

Continent > Country > State/Province > County

UK Style

Continent > Country > County

To answer at least some of this:

Area codes do cross state boundaries in rare cases. There is a tiny bit of the Oregon 541 area code that is shared with California:

https://en.wikipedia.org/wiki/Area_codes_541_and_458

Zip codes do very rarely cross state boundaries, or have really odd zip code assignments compared to their geographical assignments. One example being 65733 in Arkansas and Missouri, where a few remote places are served by the 65733 Missouri Zip code:

https://en.wikipedia.org/wiki/ZIP_code#Secondary_regional_prefixes_.28123xx.29_and_local_ZIP_codes_.2812345.29

New Zealand used to have only counties, when they abolished provinces in the 1800's, and now a lot of them have merged into districts. Denmark also changed from counties to regions as well, without any provinces. Norway and Hungary are two more examples of a hop from country to county. There's a lot of fascinating pages on Wikipedia regarding counties and other administrative divisions:

https://en.wikipedia.org/wiki/County

There are certainly odd cases that would be hard to fit into the hierarchies you've outlined above. China, for example, has prefectural level cities that are below a province and above a county in their administrative division hierarchy. I'm not sure how much effort you would want to go through to make these generic by country, though.

https://en.wikipedia.org/wiki/Administrative_divisions_of_China

https://en.wikipedia.org/wiki/Table_of_administrative_divisions_by_country

https://en.wikipedia.org/wiki/Administrative_division

Hope that helps answer your question. =)