Selecting from random amount of tables with identical structure I am programming along the lines of an unfavorable design. I have no influence on the schema because it belongs to a readymade standard software.

For simplicity let's say this software stores address books of several users. Each table is assigned with the name of the corresponding user.

The tables are created like this:

CREATE TABLE
    NAME_OF_USER
    (
        SURNAME VARCHAR(100),
        TOWN VARCHAR(100)
    )

So all tables have these columns:

surname
town

Two tables equalling two address books exist:

donaldduck
daisyduck

The address books have been filled like this:

INSERT
INTO
    DONALDDUCK
    (
        SURNAME,
        TOWN
    )
    VALUES
    (
        'Dagobert Duck',
        'Entenhausen'
    )

In order to retrieve a comprehensive list of addresses I could run

Select * from donaldduck
Select * from daisyduck

But what if someone adds an address book for GOOFY ? I would have to change my query for every newly created address book, in this case adding a SELECT * FROM GOOFY as the third row.

To make this even more complicated there are other tables with a different setup (they are storing other information which is totally irrelevant for my task of listing all entries from the given address books). So I can not simply iterate over all existing tables because this would lead to processing tables which I do not need in my query.

Is there a way to say "select * from all tables which contain a column named town" ? This should be solved by using plain SQL or PL/SQL because I want to avoid having to code this in a high level language like I think I would need to be doing by following advice from jsapkota.

I am not proud of publicly writing something like this.

Sample + data:

CREATE TABLE donaldduck ( SURNAME VARCHAR(100), TOWN VARCHAR(100) );
CREATE TABLE daisyduck  ( SURNAME VARCHAR(100), TOWN VARCHAR(100) );
CREATE TABLE goofy      ( something number(1), SURNAME VARCHAR(100), TOWN VARCHAR(100) );

INSERT INTO DONALDDUCK ( SURNAME, TOWN ) VALUES ( 'Dagobert Duck', 'Entenhausen' );
INSERT INTO daisyduck ( SURNAME, TOWN ) VALUES ( 'Daisy Duck', 'Entenhausen' );
INSERT INTO goofy ( SOMETHING, SURNAME, TOWN ) VALUES (   1, 'Goofy Dog', 'Entenhausen' );
commit;

Types and PL/SQL using a pipelined function:

create type t_NAME_OF_USER as object
(
    SURNAME VARCHAR(100),
    TOWN VARCHAR(100)
);
/

create type t_name_of_user_tab IS TABLE OF t_NAME_OF_USER;
/

CREATE OR REPLACE FUNCTION get_surname_town RETURN t_name_of_user_tab PIPELINED AS
  rc sys_refcursor;
  query clob;
  l_surname varchar2(100);
  l_town varchar2(100);
begin
  for t in (
    select table_name from user_tables ut
    where
      'SURNAME' in (select column_name from user_tab_columns utc where utc.table_name = ut.table_name) and
      'TOWN'  in (select column_name from user_tab_columns utc where utc.table_name = ut.table_name)
    )
  loop
    open rc for 'select surname, town from ' || t.table_name ;
    loop
      fetch rc into l_surname, l_town;
      exit when rc%notfound;
      pipe row(t_NAME_OF_USER(l_surname, l_town));
    end loop;
  end loop;
end;
/

SELECT:

select * from table(get_surname_town);

SURNAME                        TOWN
------------------------------ --------------------
Daisy Duck                     Entenhausen
Dagobert Duck                  Entenhausen
Goofy Dog                      Entenhausen


is_not_trusted = 1 after Error-Free WITH CHECK CHECK CONSTRAINT I'd like SQL Server 2005 SP4 to trust a certain foreign key constraint. Executing an ALTER TABLE statement that enables + checks the constraint returns Command(s) completed successfully yet sys.foreign_keys is_not_trusted stays set at 1.

Any ideas why this could be? Does ALTER TABLE sometimes silently swallow constraint errors?

ALTER TABLE {table} WITH CHECK CHECK CONSTRAINT {constraint_name}
-- returns: Command(s) completed successfully.

SELECT is_not_trusted, is_disabled
FROM sys.foreign_keys
WHERE name = {constraint_name}
-- returns 1, 0

Dropping and re-creating the constraint also completes successfully but the re-created constraint also isn't trusted.

ALTER TABLE {table} DROP CONSTRAINT {constraint_name};
-- returns: Command(s) completed successfully.

ALTER TABLE {table} ADD CONSTRAINT {constraint_name}
FOREIGN KEY ({column_name})
REFERENCES {referenced_table_name} ({referenced_column})
ON DELETE CASCADE
ON UPDATE NO ACTION
NOT FOR REPLICATION;
-- returns: Command(s) completed successfully.

SELECT is_not_trusted, is_disabled
FROM sys.foreign_keys
WHERE name = {constraint_name}
-- returns 1, 0

For reference, the key column is SMALLINT NOT NULL.

Thanks to a How to make foreign key constraints trusted on StackOverflow, I figured out that the constraint was untrusted because it was marked NOT FOR REPLICATION. After re-creating the constraint without NOT FOR REPLICATION, all were trusted!

This behavior makes sense. If the constraint isn't replicated, other replication participants won't be enforcing it. When changes made by those other participants make their way back to the database I'm working with, there's no guarantee that the foreign key constraint's condition is satisfied—so NOT FOR REPLICATION forces the constraint to be marked is_not_trusted.


Call Powershell script from another Powershell script I have a problem.
I develop some powershell scripts to perform actions on my Databases.
In theory this PS should execute when them are called by the main powershell like the calls in .bat files.

My idea is the team support have in one folder all PS1 scripts and they only run the main PS1 and provide the credentials and other values requires to run the scripts.

I tried using the Invoke-expression or Invoke-command to call the others PS1 but the main script find the files but don't execute any action.
I would like to know if you know what cause the others scripts don't run

I have the main PS1 named CREATE_CNFMGR_SCHEMA.ps1 within this PS1 I call another PS1 named for example testing.ps1 or FileStreamAccess_Validation.ps1
The script code find the PS1 files but not perform anything.

This is the code to call the others PS1 in my main PS1:

param(
[String] $pathfolder,
[Parameter(Position=0, Mandatory=$true)]
[string] $svrinstance,
[Parameter(Position=1, Mandatory=$true)]
[string] $dbuser,
[Parameter(Position=2, Mandatory=$true)]
[string] $dbusupassw,
[Parameter(Position=3, Mandatory=$true)]
[string] $dbname
)

$sqlinstance    = $svrinstance
$userdb         = $dbuser
$passdb         = $dbusupassw
$namedb         = $dbname

try
{
 Write-Output "Baseline Script to Set Up Confirmation Manager Database"
    $pathfolder = Split-Path -Parent $MyInvocation.MyCommand.Definition

if($pathfolder -ne $null)
{
    Write-Host $pathfolder
    [string]$psfile = "c:\temp\log\FileStreamAccess_Validation.ps1"
    Write-Host $psfile
    Invoke-Command -filepath $psfile
    Invoke-Expression $psfile
 }

}
catch
{

}

I solve my case using $PSScriptRoot, I hope my answer can help somebody more

The final code ends:

param(
[String] $pathfolder, #variable used to get the actual location of the folder from where is execute the PS1 file.
[Parameter(Position=0, Mandatory=$true)]
[string] $svrinstance,
[Parameter(Position=1, Mandatory=$true)]
[string] $dbuser,
[Parameter(Position=2, Mandatory=$true)]
[string] $dbusupassw,
[Parameter(Position=3, Mandatory=$true)]
[string] $dbname
)

$sqlinstance    = $svrinstance
$userdb         = $dbuser
$passdb         = $dbusupassw
$namedb         = $dbname

try
{

 Write-Output "Baseline Script to Set Up Confirmation Manager Database"
 $pathfolder = Split-Path -Parent $MyInvocation.MyCommand.Definition

 if($pathfolder -ne $null)
 {
    Write-Host $pathfolder
    $psfile = $PSScriptRoot + ".\FileStreamAccess_Validation.ps1"
    .$psfile $sqlinstance $userdb $passdb

 }

}
catch
{

}


Join 2 tables with 3rd table vertically I have 3 tables with id as common field. In 3rd table I have Lineindex,line columns which has multiple value like following :

Table A:

id   name
----------
1    name1

Table B:

id  value Aid
-------------
 1  val1  1

Table C:

id lineindex   line   Aid
--------------------------
1   1          lin1     1
2   2          lin2     1

Now, I want to take record by joining these table in a single row like'

a.id   name   b.id   value   line1[line+lineindex]   line2[line+lineindex]
-------------------------------------------------------------------------
 1     name1  1      val1    lin1                    lin2

Like, the line values in table- C need to get in single row.
How can we achieve this?

I achieved this by doing the following:

SELECT Id,Name,Num,TypeId,
[1] Line1,[2] Line2,[3] Line3,[4] Line4,[5] Line5,[6] Line6,[7] Line7,[8] Line8,[9] Line9,[10] Line10
,Location
from
(
SELECT ast.Id,ast.Name,ast.Num,ast.TypeId,
,loc.Location
FROM MyTable ast
LEFT JOIN MyLine astline ON ast.Id = astline.Id
LEFT JOIN aim.Location loc ON ast.LocationId = loc.LocationId
where ast.ProjectId = @ProjectId
) as maintab1
pivot
( MIN(maintab1.Line)
   for maintab1.LineIndex in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10])
) as pivotT1


Can OFFSET 0 ROWS FETCH NEXT 25 ROWS ONLY cause to create totally different execution plan and also cause query to run slow? We have a SELECT query which is taking time (around 90 secs) to execute. It has OFFSET 0 ROWS FETCH NEXT 25 ROWS ONLY in it.
When we remove OFFSET 0 ROWS FETCH NEXT 25 ROWS ONLY, it completes in 4-5 secs.
On observing execution plan for both the queries, it is totally different than one another. Without OFFSET 0 ROWS FETCH NEXT 25 ROWS ONLY it returns 154 rows.
I am puzzled on how using OFFSET 0 ROWS FETCH NEXT 25 ROWS ONLY can degrade performance and cause different execution plan? Since it limits the records that should be returned, I thought it will improve the performance.
Does anyone has any inputs on how to proceed further?

Using OFFSET...FETCH will likely set a "row goal" in the execution plan.  Row goals are often a good thing, as SQL Server can choose a different plan based on the knowledge that it only needs a few rows.  See Paul White's series on row goals for lots of additional detail on that subject, which starts here: Setting and Identifying Row Goals in Execution Plans
Unfortunately, when there isn't good indexing in place, or the data being requested just doesn't match many of the rows in the tables involved, the introduction of a row goal can wreak havoc on performance.  This is because all, or most, of the table might need to be read just to get those 25 rows.
There's a great example of this on Erik Darling's blog: When Data Isn’t There
Your situation sounds like it matches this one closely.  If there are only 154 matches in the entire table (or set of tables), then without the row goal the optimizer might choose a parallel scan of the tables, using hash joins to bring the result set together.  With the row goal, the optimizer is likelier to choose a serial plan, with nested loops joins, which generally won't perform as well if a lot of data needs to be read.


adding an order by to this query returns faster than without, why? Any ideas on why adding a sort to this query returns considerably faster than without the order by? I would expect the opposite so what could make this happen?

SELECT TOP (500) r.cID,r.aField,a.Description
       FROM dbo.tblR r
            inner join dbo.tblA a on r.aID = a.ID
            left join dbo.tblX x on x.cID = r.cID
       WHERE (ISNULL(x.anotherField,'') <> r.anotherField or x.field3 is null)
            and (r.ID=(select max(ID) from tblR where cID = r.cID and
            ISNULL(aField,'') <> ''))
            and r.cID in (select ID from tblC)
       ORDER BY r.cID ASC -- when I comment this line out it runs much slower

The execution plans are not helping out much.

The most obvious cause would be that (select max(ID) from tblR where cID = c.cID and ISNULL(aField,'') <> '') sub-query in the WHERE clause in combination with the TOP 500 is making the order make a difference.

In either case it will probably be running that sub-query individually for every row it might otherwise return until it has found 500 that match the whole WHERE (actually, it will be bright enough to check the other parts of the WHERE first, as they are much less expensive, but still it will be running the sub-query for a portion of the rows it meets). At a guess, more rows near the top when sorted by c.cID match currently so it has to run the sub-query less often before finding 500 matches than it does when running down the results in the order the query planner chooses by default.

(I'm assuming that the cs should be xs or vice-versa, otherwise the query would not run as the alias c is not defined anywhere)

It is worth running queries like that in the Management Studio with the "actual query plan" view turned on - then you might see from the diagram which parts are causing the differeing performance.


Remove collation from all database columns I have a SQL Server database where the collation is set on it. However I am running into problems as the collation on some columns in the database varies from the database collation.

I would like to remove collation from the columns and use the collation set on the database.

I am working with SQL Server 2012.

How can I do this? Can a script do this?

With this script here, you can find all columns that don't match the database default:

DECLARE @DatabaseCollation VARCHAR(100)

SELECT
    @DatabaseCollation = collation_name
FROM
    sys.databases
WHERE
    database_id = DB_ID()

SELECT
    @DatabaseCollation 'Default database collation'

SELECT
    t.Name 'Table Name',
    c.name 'Col Name',
    ty.name 'Type Name',
    c.max_length,
    c.collation_name,
    c.is_nullable
FROM
    sys.columns c
INNER JOIN
    sys.tables t ON c.object_id = t.object_id
INNER JOIN
    sys.types ty ON c.system_type_id = ty.system_type_id
WHERE
    t.is_ms_shipped = 0
    AND
    c.collation_name <> @DatabaseCollation

For each of those columns, you can then execute this simple command to change their collation to be the database default:

ALTER TABLE dbo.YourTable
ALTER COLUMN YourColumn VARCHAR(50) COLLATE DATABASE_DEFAULT

If you really really want, you could probably also combine the two - use a cursor on the SELECT statement from the first script and then iterate over the results and execute the second statement for each column found - I typically prefer to do this manually, to be more aware of what I'm doing (and how much I'm changing):


Data synchronization across multiple databases I have a Django application which uses PostgreSQL as a database.
The application stores hierarchical data. It is like a warehouse management system with multiple branches and hierarchy.

The current version is deployed on one main server and every other warehouse communicates directly with that server.

We want to add additional warehouses, but they are not well connected to internet (bad signal, bad channels, slow) so we are going to deploy application locally and synchronize the data with the main server in the background using database tools.

We don't want to rewrite the whole application and tables with additional "is_synchronized" columns.

Main
- Branch A (connects to Main directly via Web UI)
--- Branch A.1 (fine)
--- Branch A.2 (fine)
...
- Branch B (slow connection to Main) deploy locally
--- Branch B.1 (slow -> B) deploy locally
--- Branch B.2 (slow -> B) deploy locally

B.1 should synchronize the data to B's server and B should see all the data from their remote B.1, B.2 branches eventually. The same should happen with the Main server, it should see all the data from A and B branches eventually and same with their child branches.

In summary every parent should see every child's data (in case of a slow connection, eventually is fine)

How can we achieve this? What should we read? (we are total beginners in DB technologies, only web guys)

UPDATE: The child branches don't get data from parent. The child stores its own data and sends data to parent, parent stores all child data and its own. Main stores everything from everywhere

From your description, it sounds like you need something like Bucardo to try and merge data from multiple sources into one.

Though you'll probably have to write some custom conflict handlers to deal with data that conflicts across B, B1, and B2. There are examples out there like this for custom conflict resolution:

http://blog.endpoint.com/2014/07/postgresql-conflict-handling-with.html

Londiste also lets you merge data from multiple tables into one:

http://skytools.projects.pgfoundry.org/skytools-3.0/doc/howto/londiste3_merge_howto.html

If you can just replicate many child tables from different databases to a single database, and have your application look across all of the tables for the consistent view of your data, then you might be able to get away with something as simple as Mimeo on github from OmniTI Labs.

Without further detail on your requirements, I can only make broad suggestions.

Hope that helps.


pg_resetxlog command not found I can't find pg_resetxlog under /usr/bin.

postgres (PostgreSQL) 10.7 (Ubuntu 10.7-1.pgdg16.04+1)

cd /usr/bin
postgres@node-1:/usr/bin$ ./pg_resetxlog
-su: ./pg_resetxlog: No such file or directory

That's because from version 10 it has been renamed to pg_resetwal. And the binary is located in /usr/lib/postgresql/<version>/bin.
The pg_xlog directory was renamed to pg_wal in version 10 and several functions, tools and options as well, to reflect that change. See Postgres 10 release notes:

Rename write-ahead log directory pg_xlog to pg_wal, and rename transaction status directory pg_clog to pg_xact (Michael Paquier)
Users have occasionally thought that these directories contained only inessential log files, and proceeded to remove write-ahead log files or transaction status files manually, causing irrecoverable data loss. These name changes are intended to discourage such errors in future.

Rename SQL functions, tools, and options that reference xlog to wal (Robert Haas)
For example, pg_switch_xlog() becomes pg_switch_wal(), pg_receivexlog becomes pg_receivewal, and --xlogdir becomes --waldir. This is for consistency with the change of the pg_xlog directory name; in general, the xlog terminology is no longer used in any user-facing places.


Filtering previous month data with case statement These are my available attributes:

DT_CURRENT_DT Month Year
11-OCT-18     10    2018
12-OCT-18     10    2018
13-OCT-18     10    2018
14-OCT-18     10    2018
15-OCT-18     10    2018
16-OCT-18     10    2018
17-OCT-18     10    2018

I need a logic that will pull last month data based on sysdate.

where DT_CURRENT_DT >=  trunc (sysdate, 'MM') - interval '1' month
and DT_CURRENT_DT <   trunc (sysdate, 'MM')

However when sysdate = November 2018 then data must be pulled from October 15th till end of October. For other months it must behave as code above.

UPDATE (NOV 11) - CASE code version:

WHERE  DT_CURRENT_DT >= CASE
                       WHEN extract ( month from trunc (sysdate, 'MM')) = 11 and extract ( year from trunc (sysdate, 'MM')) = 2018
                       THEN to_date('15-OCT-2018')
                       ELSE  trunc(sysdate, 'MM') - interval '1' month
                       END
AND DT_CURRENT_DT <   trunc (sysdate, 'MM')

Try to change the where clause to the following:

where DT_CURRENT_DT >= trunc (sysdate, 'MM') - interval '1' month
  and DT_CURRENT_DT < trunc (sysdate, 'MM')
  and (
       trunc(sysdate, 'MM') != to_date('01-NOV-2018')
       or extract(day from DT_CURRENT_DT) >= 15
      );


How to maintain production database with less data and report database with all data We have a large database. We want to remove previous months data from Prod DB and keep only 3 months data. We also want a Report DB with all data.

For Example: we have restored complete Prod DB backup on Report DB on 01-JAN-2018 00:00:00 on a separate server. Now on 1st of each month we want to restore previous month data on Report DB from Prod DB. From 01-Apr-2018, we will start removing oldest month data from Prod DB.

We are planning it do with MySql Queries and PHP. We will get backup from  Prod DB using table date fields and execute SQL queries on Report DB for each month. But is it the right way to do it?

Does MySQL provide any facility to achieve this with efficient way.

EDIT:

I want to know that, is there any MySQL mechanism similar to master/salve replication that run database structure/data changes queries on 'Report DB' at a specific time(daily or monthly) but does not DROP/DELETE from 'Report DB' when we DROP/DELETE from 'Prod DB'. So all 'Prod DB' queries(INSERT, UPDATE, ALTER) executes on 'Report DB' but DROP/DELETE queries are restricted on 'Report DB'.

I would keep both, but do it slightly differently...

Keep the large table with all the data, but purge it after 3 months.  (More in a minute)

Build and maintain one or more Summary tables for reporting.  These will be much smaller than the main table and much faster for building Reports.  These are kept forever.

For purging, I would use PARTITION BY RANGE(TO_DAYS(...)) to break it into weekly partitions, then DROP PARTITION and REORGANIZE PARTITION every week.  Details here.  DROP PARTITION is orders of magnitude faster than DELETEing rows.

For Summary tables, see this.  I often see 10x speedup in report generation.

I have sacrificed saving "all" the data.  You may be unconfortable with that.  See the partition link, above, for an alternative to DROP, namely "transportable tablespaces".  With this, you turn the partition into a table (and remove it from the main table, having the effect of DROP).  Now this "table" (with one week's data) can be moved to some other place for safe keeping.  Or move it into a non-replicated database.  Or copy it to the server with a big disk that keeps it "forever".  Optionally, use "transportable tablespaces" again to hook it into a partitioned table at the target.

(This could be done by the "month" instead of by the "week", if you prefer.)


Prepared statement too slow have an issue with MariaDB 10.2.12 running in aws rds (single master, no slave)

when I run prepared statement using a variable substitution

PREPARE stmt1 FROM
"select ...quite complex select from tenth of tables..
 and `subscribers`.`id` = ?
 group by `subscribers`.`id`
 limit 1";
SET @a = 77586744;
EXECUTE stmt1 USING @a;
DEALLOCATE PREPARE stmt1;

it takes 13 minutes to process (those tables have millions rows)

however, if I run the very same query without variableS:

SET @sql = CONCAT("select ...quite complex select from tenth of tables..
 and `subscribers`.`id` = ",77586744,"
 group by `subscribers`.`id`
 limit 1");
PREPARE stmt1 FROM @sql;
EXECUTE stmt1 ;
DEALLOCATE PREPARE stmt1;

it only needs 0.05s to run. The same time it takes if I not use PREPARE at all.

According to profiler output the long run involves several "converting HEAP to Aria" with reindexing and sorting it, which do not take place if no variable involved.

Why do this converting happen? it seems there is no lack in tmp/heap since the query runs in 0.05s, thus it's  "USING variable" changes something I don't know.

btw, we've faced the issue running application on PHP 7.1.20 with Laravel Framework 5.6.39,
so I cannot just rewrite a query to get rid of "USING variable" as it something that happens under the hood of PDO.

UPD:
EXPLAIN for slow query

EXPLAIN for fast one

OK, explain differs, but it's kind of expected from profilier output.
The thing I still  don't get is why it differs while the query stays the same.

Is subscriber_tag a many:many mapping table?  Ditto for segmentation_tag?  If so, there may be several ways to speed up the use of such on both versions.  See here.


What's the best way to translate a website I have a website in Django using MySQL, and I have more than 4.000 entries that I want to translate.

What's the best way to do it? Create a new database for every language? Create other table for every language? Or create more entries?

I want 6 or 8 languages so I thought it might be bad to have a table with many records.

You want to use a TRANSLATIONS table, that looks something like:

CREATE TABLE TRANSLATIONS
(
  translation_id    INTEGER,
  language_id       INTEGER,
  translation       VARCHAR(255),
  PRIMARY KEY (translation_id, language_id)
);

Then edit your current schema and/or code to reference the TRANSLATIONS table, rather than any hard-coded values (using translation_id,language_id to lookup). For example, rather than having a VARCHAR column in a table, you'd instead have a NUMBER column that would "link" to the TRANSLATIONS table.

Probably also best to have a LANGUAGES table that lists what each language_id represents.

For example:

CREATE TABLE LANGUAGES
(
  language_id   NUMBER PRIMARY KEY,
  iso_code      CHAR(4), -- eg "en"
  language_name VARCHAR(32) -- eg "English"
);


How long time does it take to TRUNCATE 50 million rows? I want to TRUNCATE (or remove all rows in) a table with 50 million rows.

The database is in use in production.

How long time will the TRUNCATE operation take?

In normal circumstances this should take very little time since it is a metadata change. Whereas a regular delete will delete all data row by row, a truncate operation will deallocate all data pages instead.

Be aware though that a truncate table requires a Sch-M lock, so on a busy environment it could take a while before it can get hold of its lock and truncate the data. If your statement keeps running for a long time, it's probably locked by (an)other transaction(s).

If you want to be sure, try it on a copy.


Executing a table type parameter stored procedure or function within a select clause Just a quick question. I have a stored procedure that takes two parameters, a varchar (table name) and a tabletype (range of primary key ID values), and uses them to return all FK connections to and from the table for the provided ID's.

The usage is tricky though, since you actually need to declare the tabletype, select the ID's into it, and then execute the procedure like so:

DECLARE @IDVALS SearchObjRef_Type

INSERT INTO @IDVALS
SELECT TOP 10 RAND_ID FROM RANDTABLE

EXEC usp_SearchObjRef @TABLENAME = 'RANDTABLE', @IDRANGE = @IDVALS

This works just fine, but I'd like to find a more comfortable, fast and easy way to use the procedure in standard selects. What would be the best way, assuming it's even possible, to do all that in one command?

For a logical example that obviously doesn't work, what I'd like to do is a form of the following:

EXEC usp_SearchObjRef @TABLENAME = 'RANDTABLE',
    @IDRANGE = (SELECT TOP 10 RAND_ID FROM RANDTABLE)

The way you're doing it is about the fastest, easiest and most comfortable way you're going to find to pass a TVP to a stored procedure, sorry. You can't pass expressions to stored procedure parameters, even simple things like:

EXEC sp_who2 N'act'+'ive';

Fails with:

  Msg 102, Level 15, State 1, Line 1
  Incorrect syntax near '+'.

In order to use an expression, it needs to be:

DECLARE @expression SYSNAME = N'act'+'ive';
EXEC sp_who2 @expression;


SQL enforce value of a column if other column is NOT NULL I'm having a hard time to explain this so let me illustrate it with an exemple.

I have a table for comments and it looks like so:

CREATE TABLE comments (
    id INT UNSIGNED AUTO_INCREMENT,
    parent_id INT UNSIGNED,
    article_id INT UNSIGNED NOT NULL,
    user_id INT UNSIGNED NOT NULL,
    ip VARCHAR(255) NOT NULL,
    submitted_at TIMESTAMP DEFAULT NOW(),
    comment TEXT,
    PRIMARY KEY (id),
    CONSTRAINT fk_com_parent
        FOREIGN KEY (parent_id)
        REFERENCES comments (id)
    CONSTRAINT fk_com_article
        FOREIGN KEY (article_id)
        REFERENCES articles (id)
    CONSTRAINT fk_com_user
        FOREIGN KEY (user_id)
        REFERENCES users (id)
);

Now it makes sense that if parent_id is set, child and parent should share the same article_id.

Answers to a comment must be on the same article. So is there a way to contraint this?

Answers to a comment must be on the same article. So is there a way to contraint this?

If so the logic must be that the record have arcticle_id IS NULL when parent_id IS NOT NULL. And the article ID must be obtained from its parent for that record.

SELECT ... ,
       CASE WHEN t1.parent_id IS NULL
            THEN t1.article_id
            ELSE t2.article_id
       END article_id,
       ...
FROM table t1
LEFT JOIN table t2
    ON t2.user_id = t1.parent_id
...

So if parent_id is set, the article_id is taken from parent's record, and the value from current record is simply ignored.


Convert MyISAM to InnoDB What are the considerations needed when converting myisam to innodb.

I have a table around 80 GB with engine myisam. I would like to convert into innodb. What are the considerations needed to convert.

I'm using percona  MySQL. I checked the table indexes and there is no full text searching indexes for the above table.

I plan to use percona online scheme change.

What are the considerations/pre-requisite to keep in mind to do this activity?

InnoDB tables tend to occupy more space than MyISAM tables do.
While converting, new physical files are being created, so you have to have free space on the disk (Hard to estimate, but to be safe I would say 120G)
If you use Percona tools to make the change online, all modifications on the original table will be stored in a temporary table using triggers. So depending on the DML operations,i.e. inserts/updates/deletes, you may need more space. Be careful that on high traffic tables, triggers may affect the performance.
It could be that some InnoDB related configuration should be tuned.

If in your setup you have two or more servers, like master-master, or master-slave(s), I think it is better to do it offline, one server at a time.


UnknownError: error while getting last oplog entry for begin timestamp: find command not yet implemented I have 2 mongoDBs. One running as PRIMARY, its version is 3.0.13. The other works as SECONDARY in the replica set, its version is 3.0.11. Replication works perfectly at this point.

I want to add a 3rd mongoDB, empty data folder, and its version is 3.4.5.

Using mongo client I can access to PRIMARY and SECONDARY, and from PRIMARY I can access to this 3rd mongoDB, and from SECONDARY I can access to this 3rd mongoDB too.

In the primary, I execute rs.add("server3:27017"), and the command rs.status() displays this 3rd server in status STARTUP2. But after a while I get this error

2017-07-25T09:24:52.159+0000 I REPL     [replication-0] Initial sync attempt finishing up.
2017-07-25T09:24:52.159+0000 I REPL     [replication-0] Initial Sync Attempt Statistics: { failedInitialSyncAttempts: 0, maxFailedInitialSyncAttempts: 10, initialSyncStart: new Date(1500974688699), initialSyncAttempts: [] }
2017-07-25T09:24:52.159+0000 E REPL     [replication-0] Initial sync attempt failed -- attempts left: 9 cause: UnknownError: error while getting last oplog entry for begin timestamp: find command not yet implemented

and the daemon dies when the attempts left reaches 0.

2017-07-25T09:25:03.887+0000 F REPL     [replication-0] The maximum number of retries have been exhausted for initial sync.
2017-07-25T09:25:03.899+0000 E REPL     [replication-1] Initial sync failed, shutting down now. Restart the server to attempt a new initial sync.
2017-07-25T09:25:03.899+0000 I -        [replication-1] Fatal assertion 40088 UnknownError: error while getting last oplog entry for begin timestamp: find command not yet implemented at src/mongo/db/repl/replication_coordinator_impl.cpp 635
2017-07-25T09:25:03.899+0000 I -        [replication-1]

***aborting after fassert() failure

what does it mean this find command not yet implemented? any workaround for having this 3rd server joined in the replica set?

You cannot add a MongoDB 3.4.x member to a MongoDB 3.0.x replica set.

Replication between mixed versions is only supported for adjacent MongoDB major releases (for example, 3.0 and 3.2 or 3.2 and 3.4), and even then generally only intended to be used transiently for the purposes of a rolling upgrade.

  what does it mean this find command not yet implemented?

The find command was introduced in the MongoDB 3.2 production release series, and MongoDB 3.4 replication expects this command to be available.

  any workaround for having this 3rd server joined in the replica set?

Install MongoDB 3.0 or 3.2 for compatibility with your existing MongoDB 3.0 replica set members. If you add a new member with MongoDB 3.2, I would recommend upgrading your other replica set members to the same release version.

For more information see:

Upgrade a replica set to MongoDB 3.2
Upgrade a replica set to MongoDB 3.4


On-Demand Fuzzy Lookup We've got a customers table (Who doesn't?), containing many records that are, from a business perspective, duplicates. I've been able to create an SSIS package to perform fuzzy grouping, and report on potential duplicates.

Now, suppose I want to do this kind of analysis just as somebody is entering a new customer. The idea would be to perform a fuzzy lookup on customer name (and possibly some other basic info like postal code), and show potential duplicates prior to proceeding to the customer creation form.

The obvious problem here is that the fuzzy grouping and lookup components are part of SSIS. If I wanted to run those on-demand, I'd have to do something insane like putting the search terms in a staging table, running the SSIS package, waiting for it to complete, and fetching the results from an output table. It would be slow, painful, and have severe concurrency problems.

So, the other idea was to use full-text indexing. In experimenting with it, it looks like it won't be suitable. It can't catch subtle misspellings of customer names, or names that differ in "Company" vs. "Corporation" vs. "Co.", or "Anderson" vs. "Andersen", and other such variations.

Is there something that will allow for the flexibility of fuzzy grouping/matching from T-SQL? I can tell a fuzzy lookup to save the tokens, but it looks like I would still have to reimplement most of the matching algorithm to make use of them.

In the past I built a "fuzzy-search" in a .Net CLR function. This function gets called the same way a user-defined function gets called.

For example,

select id, name
from customers
where dbo.CompareStrings("newCustomerName", customers.name) > .8

would only return customers with a name that was 80% similar to the input name.

The % match is based on the number of changes needed to convert one value to another, not the number of characters that are different. We use it to compare addresses, and found this was more effective because of the numerous street abbreviations that are used.

Here's the code I used to compare strings. I did this so long ago that I can't remember how to deploy it, although a quick search will show you many articles on how to create SQL CLR functions

' Checks two strings against each other and returns a decimal between 0 (doesn't match at all) and 1 (100% match)
<Microsoft.SqlServer.Server.SqlFunction()> _
Public Shared Function CompareStrings(ByVal input1 As SqlChars, ByVal input2 As SqlChars) _
As <SqlFacet(Precision:=10, Scale:=4)> SqlDecimal

    If IsNothing(input1) And IsNothing(input2) Then
        Return New SqlDecimal(1.0)
    ElseIf IsNothing(input1) Or IsNothing(input2) Then
        Return New SqlDecimal(0.0)
    End If

    Dim s1 As String = New String(input1.Value)
    Dim s2 As String = New String(input2.Value)

    If s1.Length = 0 Or s2.Length = 0 Then
        Return New SqlDecimal(1.0)
    Else
        Dim re As New Regex("[^A-Za-z0-9 ]", RegexOptions.IgnorePatternWhitespace Xor RegexOptions.Singleline)
        s1 = re.Replace(s1, "( )\1*", "$1")
        s2 = re.Replace(s2, "( )\1*", "$1")
        s1 = UCase(re.Replace(s1, ""))
        s2 = UCase(re.Replace(s2.ToString, ""))

        Dim dif As Integer = GetStringSimilarity(s1, s2)
        Dim max As Integer = s1.Length
        If s2.Length > max Then max = s2.Length

        Return New SqlDecimal(1.0 - (dif / max))
    End If
End Function

' Compares two strings using the relationship in patterns of letters
Private Shared Function GetStringSimilarity(ByVal s1 As String, ByVal s2 As String) As Integer
    Dim n As Integer = s1.Length
    Dim m As Integer = s2.Length
    Dim distance(n + 1, m + 1) As Integer

    Dim cost As Integer = 0
    If n = 0 Then Return m
    If m = 0 Then Return n

    For i As Integer = 0 To n
        distance(i, 0) = i
    Next
    For j As Integer = 0 To m
        distance(0, j) = j
    Next

    For i As Integer = 1 To n
        For j As Integer = 1 To m
            If Mid(s2, j, 1) = Mid(s1, i, 1) Then cost = 0 Else cost = 1
            distance(i, j) = Min3(distance(i - 1, j) + 1, distance(i, j - 1) + 1, distance(i - 1, j - 1) + cost)
        Next
    Next
    Return distance(n, m)
End Function

' Returns the min of 3 values
Private Shared Function Min3(ByVal x As Integer, ByVal y As Integer, ByVal z As Integer) As Integer
    Dim min As Integer = x
    If y < min Then min = y
    If z < min Then min = z
    Return min
End Function


SSDT - Unresolved Reference to temporary table in linked server Here's the scenario.

I am extracting data from a table in an Oracle database via a linked server as part of an ETL process. I am developing the solution in Visual Studio 2013 with SQL Server Data Tools (SSDT).

The ETL procedure uses a temporary table which is dynamically created in the Oracle database via EXECUTE AT syntax. This is so that I can insert a small number of records from my SQL Server database into the temporary table in order to perform a join to a much larger source table (~20m rows) in the Oracle database. The insert statement uses four-part naming as follows:

INSERT INTO [linked-server]..[owner-name].[table-name]
SELECT a, b FROM [stage].[table-name];

However, because the table is temporary, my project contains a warning about an unresolved reference:

  SQL71562: Procedure: [schema].[procedure-name] has an unresolved
  reference to object [linked-server].[].[owner-name].[table-name]

Also, when I publish the database project, the publish operation fails because the temporary table does not exist. The error message reads:

  The OLE DB provider "OraOLEDB.Oracle" for linked server
  "[linked-server]" does not contain the table
  ""[owner-name]"."[table-name]"". The table either does not exist or
  the current user does not have permissions on that table.

I can publish the project by creating the temporary table first via SQL Management Studio then dropping it after the project has been published. But this seems to go against how database projects should work.

I've seen suggestions about suppressing T-SQL warnings for unresolved references, but that doesn't enable me to publish the project.

Notes:

The Oracle database belongs to a 3rd party application so I cannot
create permanent tables in the database
I have created the linked server object in the SSDT project

I know this is an old question, but I want to throw this out there in case anyone else happens upon this post.  Create a "scripts" folder in your SQL Database Project, and add a Pre-Deploy script that will create the temporary table in Oracle; right click on the script and set the Build Action to PreDeploy.  I know it's not the most elegant solution, but it's quick and dirty, and will get the job done.

You'll have to "Build" the project before you publish it, which may still generate the unresolved object error; in that case you'll have to create the table before you build, and then drop it after, but it should work.


Security implications of enabling CLR on shared instance My database is currently isolated in its own instance and VM.  My customer is retiring that server and I need to migrate the DB to a new environment.  My DB uses CLR, so the new environment needs to have that enabled.

The customer proposed using an existing server with an existing SQLServer 2014 Standard instance hosting DBs from other software vendors on it.  I'm concerned about incurring a sort of "you break it, you bought it" liability, since I can't vouch for the security strategy of those other DBs or their front-ends.  I don't want to do something that would introduce a significant risk.

I run into similar scenarios with other customers from time to time.  I'm a database developer.  I occasionally provide DBA services pertaining to my own product for my small to medium business customers, but I am by no means a skilled DBA.  Each customer has different budgets, priorities, IT staff, and security policies, but generally none of them have IT/DBAs that can understand and/or make competent risk assessments at this level of detail.

How would enabling CLR on a shared instance affect the security of the other DBs?  (I am not asking about database-level permission_set levels, just enabling CLR itself).  I need to understand the risk (if there is any) well enough to be able to advise a 'best practice' and a customer-specific 'secure enough' alternative.

Given that we are dealing with a shared instance, it should be safe to assume that none of the logins for the "customer" DBs has sysadmin privileges or has been granted the CONTROL SERVER permission.

Under such conditions, there should not be any security risk to having SQLCLR enabled, regardless of what many people seem to claim. Having the instance-level "CLR enabled" option enabled merely allows for a login that has the appropriate permission to load assemblies, and for loaded assemblies to be used by anyone granted access to the objects in those assemblies.

If users cannot load assemblies, then there can be no risk of having unknown code in assemblies, because there won't be any assemblies. Then, if one user (such as yourself) is granted the CREATE ASSEMBLY permission (which should be coupled with using Certificates to handle the assembly security), then only you can load assemblies.

Of course, for SQL Server 2005 - 2016, and for SQL Server 2017 and newer that has the "CLR strict security" instance-level configuration option disabled, the database owner (i.e. the dbo user) and any user in the db_owner fixed database role can create SAFE assemblies, but those aren't a security risk. Sure, they might be a performance risk, but the same is true for sloppy data modeling, T-SQL, triggers, etc. If the various vendors are not dbo for their databases, then this is a non-issue. If the vendors are dbo for their respective databases and the desire is to prevent them creating SAFE assemblies, it is easy enough to create an instance-wide DDL trigger for the CREATE ASSEMBLY statement and issue a ROLLBACK if the user is not you and/or it is not executed within your database.

If someone is a member of the sysadmin instance-level role, or granted the CONTROL SERVER instance-level permission, then they can also load assemblies. BUT, and here is what most "security" related recommendations miss: having "CLR enabled" disabled does not protect against someone who is a sysadmin or has CONTROL SERVER permission because they have permission to enable the "CLR enabled" option (and enable xp_cmdshell, etc and do whatever they want)!

There is no inherent risk in having this option enabled because having it enabled does not give anyone the permission to load assemblies that pose a security risk (in SQL Server 2005 - 2016, dbo and users in db_owner role can load SAFE assemblies). And, once loaded, assemblies marked as EXTERNAL_ACCESS or UNSAFE need additional security (i.e. Certificates / strong naming, not TRUSTWORTHY) in order for their code to be executed. And, starting in SQL Server 2017 all assemblies, by default, need that additional security just to be loaded in the first place. And, that additional security (for the operation of the assembly) requires sysadmin level permissions:

In the unfortunate case of someone setting the database to TRUSTWORTHY ON, the login that owns the DB needs to have either the EXTERNAL ACCESS ASSEMBLY or UNSAFE ASSEMBLY permission (for SQL Server 2017+ with "CLR strict security" enabled — the default — it must be UNSAFE ASSEMBLY), which can only be granted by a sysadmin (these permissions are obviously implied if the owning login is a sysadmin). Hopefully vendor DBs would never have TRUSTWORTHY enabled.
When assemblies are signed, a certificate or asymmetric key needs to be created in the [master] DB, then a login needs to be created from that, then that login needs to be granted either the EXTERNAL ACCESS ASSEMBLY or UNSAFE ASSEMBLY permission (for SQL Server 2017+ with "CLR strict security" enabled — the default — it must be UNSAFE ASSEMBLY).

For some examples of handling SQLCLR security, please see:

Stairway to SQLCLR Level 3: Security (General and SAFE Assemblies)
Stairway to SQLCLR Level 4: Security (EXTERNAL and UNSAFE Assemblies)

That being said, as David Browne noted in a comment on this answer:

  Another important point is about the trust that presumably exists among the stakeholders for the various databases. In a corporate environment, everyone is expected to be trustworthy and cooperative. in a 3rd party hosting environment, you have to assume that the owner of the adjacent workload is a completely untrusted and even malicious actor. Lots of not-perfectly-safe things are safe-enough to do behind a firewall and among friends.


How to enable implicit casts in PostgreSQL >=9.2? Coming from this topic on SO a user suggest me to enable implicit casts in my PostgreSQL and I don't know how to do that. I also don't know if this is a good practice or if it's the solution to the issues I'm having but I want to try this one since others solutions isn't working. So find information on Google I found this:

  For enable implicit casts you must therefore execute the following
  commands in your PostgreSQL console when connected to the template1
  database, so that any database created afterward will come with the
  required CASTs (if your database is already created, execute the
  commands in your database as well):

CREATE FUNCTION pg_catalog.text(integer) RETURNS text STRICT IMMUTABLE LANGUAGE SQL AS 'SELECT textin(int4out($1));';
CREATE CAST (integer AS text) WITH FUNCTION pg_catalog.text(integer) AS IMPLICIT;
COMMENT ON FUNCTION pg_catalog.text(integer) IS 'convert integer to text';

CREATE FUNCTION pg_catalog.text(bigint) RETURNS text STRICT IMMUTABLE LANGUAGE SQL AS 'SELECT textin(int8out($1));';
CREATE CAST (bigint AS text) WITH FUNCTION pg_catalog.text(bigint) AS IMPLICIT;
COMMENT ON FUNCTION pg_catalog.text(bigint) IS 'convert bigint to text';

So my questions around this:

How do I execute that on template1? Any example will be helpful since I'm not a DBA just a simple developer and most of the time my tool & friend is pgAdmin or any other GUI on Windows
Can this be done easily from pgAdmin or any other GUI for Postgres or I need to work from command line?
Is this a good practice? Will be any performance issues afterwards?
Are this the only one queries I need to run to get ride of my problem?

One can also just add ?compatible=7.1 to a jdbc connection url (before the 42 release)... people say 'Implicit casts to text were removed for a reason' but in 10 years I have never had an issue with them. (I have fixed other bugs is pgsql though)

https://www.postgresql.org/docs/current/static/sql-createcast.html

\dC
********* QUERY **********
SELECT pg_catalog.format_type(castsource, NULL) AS "Source type",
       pg_catalog.format_type(casttarget, NULL) AS "Target type",
       CASE WHEN castfunc = 0 THEN '(binary coercible)'
            ELSE p.proname
       END as "Function",
       CASE WHEN c.castcontext = 'e' THEN 'no'
            WHEN c.castcontext = 'a' THEN 'in assignment'
            ELSE 'yes'
       END as "Implicit?"FROM pg_catalog.pg_cast c LEFT JOIN pg_catalog.pg_proc p
     ON c.castfunc = p.oid
     LEFT JOIN pg_catalog.pg_type ts
     ON c.castsource = ts.oid
     LEFT JOIN pg_catalog.pg_namespace ns
     ON ns.oid = ts.typnamespace
     LEFT JOIN pg_catalog.pg_type tt
     ON c.casttarget = tt.oid
     LEFT JOIN pg_catalog.pg_namespace nt
     ON nt.oid = tt.typnamespace
WHERE ( (true  AND pg_catalog.pg_type_is_visible(ts.oid)
) OR (true  AND pg_catalog.pg_type_is_visible(tt.oid)
) )
ORDER BY 1, 2;
**************************
                                         List of casts
         Source type         |         Target type         |      Function      |   Implicit?
-----------------------------+-----------------------------+--------------------+---------------
 abstime                     | date                        | date               | in assignment
 abstime                     | integer                     | (binary coercible) | no
 abstime                     | timestamp without time zone | timestamp          | yes
...


ERD via Chen's notation: How do I organize multiple 'last done' entries? I'm having an issue creating a little practice ERD.  I'm trying to do some more difficult stuff, and I'm not sure what the standard way to organize it is.

Basically, I don't know which of these is most correct.  Also, I'm trying to add some data: "Last video chat; last voice chat; last im; last email".

Is has a better interaction word than friends?

Should I use:

user->gold_friends->user; user->silver_friends->user,
user->has->friend; friendship->is->gold (and how would these be connected, if so), or
user->has->gold_friend; goldfriend->is->user?

Similarly, which do you prefer and why:

user->emails->user; email->was at->date, the way silver and gold come out of type in the drawing?
user->has->email_interaction, or
user->has->interaction; interaction->was via->email

If I add a friend of friendID, should I be sure that the friendID is transferred as a derived key generated with the transaction?  Why or why not?  How does the process of friend adding work, programmatically?  Is the key connected to the added user, and then transferred to the adding user, or is the key connected to the adding user and then sent to the added user, or does each user have a permanent key that is added to an array for each user at the same time when they add?

First, 'Friend' is probably not a different entity but just a relationship from 'User' to 'User' but your case may be special, I don't know. So about "if I add a friendID, should I have it as a derived key from user", assuming a friend is just another user - yes.

Additional info like 'last voice chat' & 'last im' shouldn't be part of a composite attribute, each 'last ' is meaningful by itself and a composite attribute doesn't help. If you want to know when user AAA lastly called anybody? then such info should be attributes of 'User', but if you want to know when user AAA lastly called user BBB? then such info should be attributes of the relationship between users AAA & BBB.

The type of relationship probably should be a simple attribute (enumerated) but it depends. If a friendship could be both silver & gold then it should be a composite attribute of boolean isSilver, isGold etc. But if is only silver or gold (or maybe normal) but only one of them, it should be a simple enumerated attribute.

Lastly, you should consider the different ways of how to define friendships, you probably want to read more about it in questions like these:

https://stackoverflow.com/questions/3009190/mysql-friends-table
https://stackoverflow.com/questions/6618701/storing-friends-in-database-for-social-network
https://stackoverflow.com/questions/3120591/mysql-friends-table-structure-help

Good Luck!


Row size limits in Mysql w.r.t to TEXT and BLOB columns There is a well-known limitation in Mysql for row size which 65,535 bytes.
While going through the docs,

I have found that

  BLOB and TEXT columns only contribute 9 to 12 bytes toward the row
  size limit because their contents are stored separately from the rest
  of the row.

I did go through the MySQL documentation but unable to understand when a TEXT column will contribute 9 bytes and when it will contribute 12 bytes. Can anyone explain how to interpret the statement?

It is also stored the length of the data, that is why it is 9 - 12 , if you want it exactly.

https://github.com/jeremycole/mysql/blob/master/storage/innobase/include/btr0cur.h#L762

/** The reference in a field for which data is stored on a different page.
The reference is at the end of the 'locally' stored part of the field.
'Locally' means storage in the index record.
We store locally a long enough prefix of each column so that we can determine
the ordering parts of each index record without looking into the externally
stored part. */
/*-------------------------------------- @{ */
#define BTR_EXTERN_SPACE_ID     0   /*!< space id where stored */
#define BTR_EXTERN_PAGE_NO      4   /*!< page no where stored */
#define BTR_EXTERN_OFFSET       8   /*!< offset of BLOB header
                        on that page */
#define BTR_EXTERN_LEN          12  /*!< 8 bytes containing the
                        length of the externally
                        stored part of the BLOB.
                        The 2 highest bits are
                        reserved to the flags below. */
/*-------------------------------------- @} */


`CHANGE_TRACKING = AUTO` fails to populate full-text index I have created full-text indexes on tables with the following options (simplified):
CREATE FULLTEXT INDEX ON [schema].[table]
(
    ColumnA Language 'English',
    ColumnB Language 'English'
)
KEY INDEX pk_table ON database_ft_catalog
WITH
    CHANGE_TRACKING = AUTO,
    STOPLIST = OFF
GO

This works, and rows are searchable after the index builds.
Select Top 100 * from [schema].[table]
  where contains(*, '"searchTerm*"');

My problem is that rows are not automatically populated into the full-text index like they should be.
At one point, I saw in the logs that the full-text daemons died, so I restarted those using sp_fulltext_service 'restart_all_fdhosts'. (sys.fulltext_indexes showed that a full population was still underway.)
This worked for a while, but now new rows are once again not being indexed. This is my latest error message from the Full Text Log:

A full-text retry pass of Auto population started for table or indexed view
Error '0x80004005' occurred during full-text index population for table or indexed view
Informational: Full-text retry pass of Auto population completed for table or indexed view '[db].[schema].[table]'... Number of retry documents processed: 1. Number of documents failed: 1.

Any information on how to fix this would be greatly appreciated. The rows are failing four times, then are ignored after that, i.e., they are no longer tracked and will not make it into the full-text index until I rebuild it.
FYI, I did try to increase the ism_size as listed in this article.  However, that did not fix the issue. (I know that's an old version, but it was worth a shot.)
UPDATE:
I found some additional posts today that said to look in Event Viewer. I'm getting Event ID 30089:

The fulltext filter daemon host (FDHost) process has stopped abnormally. This can occur if an incorrectly configured or malfunctioning linguistic component, such as a wordbreaker, stemmer or filter has caused an irrecoverable error during full-text indexing or query processing. The process will be restarted automatically.

The Windows Service (SQL Full-text Filter Daemon Launcher - FDHost) is restarting anywhere between 1 and 30 seconds. The full text service is using the default SQL Server account NT Service\MSSQLFDLauncher, which I made sure has READ & EXECUTE access to the C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Binn folder and all child objects, which is where sp_help_fulltext_system_components 'wordbreaker' reported the wordbreaker packages were all located.
Still no change.
Update 2019-10-07
It turns out these are using EC2 in AWS, with Amazon's Windows 2016 image. We installed SQL Server Standard 2017 ourselves, though.  It looks like the AMI is Windows_Server-2016-English-Full-Base-2019.07.12
Update 2019-10-08
As I'm in a testing environment, I'm using SQL Server Edition. The database is at compatibility level 140 (2017) and the SQL Server instance and databases are all fresh installs, no upgrades.
The database file is set to unrestricted max size, logs file is set to max of 2TB, both grow by 64MB. The database file is currently at 1.288 GB and the logs are 776 MB. There is enough room on the configured disk for it to grow another 20-40 GB.
One tweak I could make for performance issues is put the full text indexes on a separate disk, but I'm currently the only one using this database. I just want the process to complete without errors and be able to add newly inserted or modified records to the index.
Note that my primary key is an identity column per performance recommendations, and all of my columns are built-in data types (varchar(255) seems to be the max), and that these tables have system versioning enabled, if that affects anything.

You said that the SQL Full-text Filter Daemon Launcher - FDHost is restarting. Do you have any error in the Event Viewer that would suggest it doesn't have the appropriate rights to run?

You could check the account and password and check that the permissions are sufficient on the computer it runs.

You should check that the named pipes are enabled.

For example, the documentation states that :

  If named pipes functionality has been disabled on the local computer, or if SQL Server has been configured to use a named pipe other than the default named pipe, the SQL Full-text Filter Daemon Launcher service might not start.

It also says that you can have problems if another instance of the same named pipe is already running.

If you can provide additional error logs, maybe we could help you futher.

I read a post here that says that this error got resolved once the SQL Service, Agent Service and Full Text Service all run on the same account. Maybe it's something worth trying.

You can try to query sys.dm_fts_fdhosts or another of the DMV views related to fulltext indexes. Maybe you can find additionnal information on what's going on there.


Any way to get database data back? So I've entered a couple hundred rows of data into a database manually and have begun implementing a Java frontend. However, I got an unexpected result and now when I go back into RazorSQL, the database is gone (I believe a DROP command was executed). Is all the data I entered gone forever?

Part of this I believe stems from me not fully understanding where databases are stored. I'm using RazorSQL on OS X and can't seem to find a path where the database is stored.

Undrop tool for InnoDB can scan you disk and retrieve records that are not overwritten yet.
I didn't test it on OSX though, only Linux-es are supported.


Are database query optimizers aware of storage performance differences? As I understand it, the query optimizer in SQL Server (or any other RDBMS, really) isn't aware of the performance of the storage underneath the database, and will make decisions as if all storage has equal cost. Is that accurate, or is there some knowledge of storage performance that's taken into account?

In a totally contrived example, let's say that my table rows are stored on an SSD drive in my SAN with instantaneous access times, where my indexes are stored on SAS drives that are extremely overloaded, resulting in disk saturation and constant disk queues. When the RDBMS generates the execution plan, is it any more likely to favor a table scan than an index operation (or possibly a skinny index and associated table lookups, as opposed to a covering index, because it's less IO on the SAS disks)?

I suspect the answer is a solid "not a chance is the optimizer that smart or even aware of disk performance", but I just wanted to see if anybody out there knows for sure. I'm using SQL Server, but I'm interested in any database system.

Sql server's query optimizer does not take variations in disk performance into consideration when compiling a query plan.  Paul White provides a great overview of Sql Server's cost based optimizer here:

https://sqlkiwi.blogspot.com/2010/09/inside-the-optimizer-plan-costing.html

Some key points are:

The optimizer isn't trying to calculate the exact cost of a plan. It's trying to pick the plan with the relative lowest cost between several alternatives.
It's a simplified view of reality.  It assumes a server can perform 320 io/sec and that cpu performance hasn't increased in over a decade.
Even though servers today have vastly different performance characteristics, the optimizer still does a really good job in the majority of cases.

So, why doesn't Microsoft add some additional intelligence to the optimizer?  In the future they might, however, what is more likely are small tweaks to the costs of individual iterators.  Currently the benefit isn't there to justify the effort.

You can use undocumented dbcc calls to change some of the query optimizers assumptions. DO NOT USE THESE ON A PRODUCTION SERVER

DBCC SETIOWEIGHT(<multiplier>)
DBCC SETCPUWEIGHT(<multiplier>)

Both have default values of 1.   Play with them and see if you can come up with different values that consistently produce better plans in a majority of cases.  You'll find that small changes won't change  the majority of plans and large changes will generate really bizarre plans.

One additional point is that while SQL doesn't consider io performance when compiling a plan, it does respond to io performance during the execution of the plan (limiting read-ahead reads if io is saturated, etc.)


SQL Server: trigger for insert update and delete caught when no data been changed? I've added a simple table to a db called: aaa_log with columns :(id ,name, op))

CREATE TABLE aaa_log (
   [id] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL,
   [name] [varchar](50) NOT NULL,
   [op] [varchar](50) NULL)

id column used just in manner to keep the order.

I've added a trigger (for insert, update, delete) to all other tables in a db ussing the following script:

declare @cmd varchar(max)
declare trigger_create cursor for
select 'Create trigger ['+TABLE_SCHEMA+'].[xxtr_'+TABLE_NAME+'_auto]
  on ['+TABLE_SCHEMA+'].['+TABLE_NAME+'] fro insert,update,delete as
  BEGIN
   declare @op varchar(20)
   if exists(SELECT * from inserted)  and exists(SELECT * from deleted)
   begin
     set @op = ''update''
   end
   if exists(SELECT * from inserted)  and not exists(SELECT * from deleted)
   begin
     set @op = ''insert''
   end
   if not exists(SELECT * from inserted)  and exists(SELECT * from deleted)
   begin
     set @op = ''delete''
   end
   insert into aaa_log([name],[op]) values('+TABLE_SCHEMA+'.'+TABLE_NAME+', @op)
  END
'
from information_schema.tables
where table_type='BASE TABLE' AND table_name <> 'aaa_log'

open trigger_create
fetch next from trigger_create into @sql
while @@FETCH_STATUS =0
BEGIN
  exec(@sql)
  fetch next from trigger_create into @sql
END
close trigger_create
deallocate trigger_create

Nobody else update the aaa_log table, just these triggers, but when I check the  aaa_log table I see some rows where op is NULL.

The only option I can think off is that both inserted and updated is null, so how the trigger had been activated?

Any explenation?

You are relying on the presence of rows in either inserted or deleted (or both). What happens when no rows are affected? The trigger still fires.

CREATE TABLE dbo.floob(a int);
INSERT dbo.floob(a) VALUES(1);
GO

CREATE TRIGGER dbo.TRfloob
ON dbo.floob
FOR INSERT, UPDATE, DELETE
AS
BEGIN
  IF    NOT EXISTS (SELECT 1 FROM inserted)
    AND NOT EXISTS (SELECT 1 FROM deleted)
  BEGIN
    PRINT 'Strange, unknown operation!';
  END
END
GO

UPDATE dbo.floob SET a = 2 WHERE a = 2;
GO

To avoid this kind of thing, typically people start their trigger with something like:

IF     NOT EXISTS (SELECT 1 FROM inserted)
   AND NOT EXISTS (SELECT 1 FROM deleted)
BEGIN
  RETURN;
END

Checking @@ROWCOUNT is popular too, but I find it far more brittle.


Starting SSIS Package through SSIS Catalog I've developed an SSIS package to load files but it is behaving very strangely. I can kick off the package running start_execution on the SSISDB only when I log directly onto the server. Kicking it off from my PC will throw a warning saying the For Each File enumerator is empty. My account and the SQL SQL service account both have access to the file share where the SSIS package is pointing. Anyone else encountered a similar issue?

UPDATE:

The SSIS package will execute successfully if executed remotely through a SQL agent job.

I would agree with the comment from Travis Page. Having just gone through this is sounds like either the Service Principal Names(SPN) are missing for the SQL Service or the file share. Ultimately you will need the "MSSQL" SPN for your SQL Server instances and the "HOST" SPN for host of the fileshare.

Some reading/blog articles to help understand troubleshoot any kerberos issues:

This blog describes the creation of SPN's using the SETSPN command line tool:
http://blogs.technet.com/b/tristank/archive/2006/05/08/spns-r-fn.aspx

There is a tool published from Microsoft which will help determine the SPN's require for SQL Server. This blog talks about the tool:
http://blogs.msdn.com/b/farukcelik/archive/2013/05/21/new-tool-quot-microsoft-kerberos-configuration-manager-for-sql-server-quot-is-ready-to-resolve-your-kerberos-connectivity-issues.aspx

Additionally there is this below article from Microsoft which will help troubleshoot any kerberos problems:
http://blogs.technet.com/b/askds/archive/2008/05/14/troubleshooting-kerberos-authentication-problems-name-resolution-issues.aspx


SQL Server: How to get the responsiveness of a trigger, without blocking the transaction that fires the trigger? On a thread today I posed a desire to get code to execute immediately when a record is inserted rather than resorting to polling.

Triggers are powerful tools, yet dangerous places to put real code, because if the code is slow or blocks, the transaction will block, and it can become a real problem.

For my purpose, polling is not feasible. I really need my code to execute immediately when the record shows up. A windows app is inserting data into the table. I do not have sources to the app and cannot change it. I have full access to the SQL Server.

But it is truly a bad practice to put serious code in the trigger.

So the question beccomes: What is the alternative to a trigger that is as responsive as a trigger? (Or almost as responsive... 1-5 seconds of delay would be OK, but a 30 second polling cycle is really unpleasant. The code to run takes from 0-3 seconds to complete.)

Constraints:

I only have access to the db, not the app
SQL Server 2005 and 2008
Feasible. We aren't looking to invent something crazy, add a lot of infrastructure, or big new systems. We have an app. It talks to SQL Server. Adding more layers prob is not poss.
Implementing yet another server app (like msg queue server or such) is not feasible for this project. This thing is only going to fire one to fifteen times a day.

Tools at hand (not comprehensive):
- .NET
- Python
- C#

Would like to avoid xp_cmdshell if at all possible.

You might be able to use the service broker, though that is probably overkill, as you state.

Alternately, if you don't want to install/manage an extra service for this one need, you could use xp_cmdshell or a CLR-based trigger to make an external call when needed, to a program that starts the desired process asynchronously (so, outside the transaction that called the trigger) and returns immediately. I would be more wary of this than simply putting some complex code in a trigger though – at least, with a trigger any good SQL Server DBA person would be able to support the code when you are not around rather than requiring some other specific programming experience.

When you say that polling simply isn't an option as an "up to 30 seconds" delay is unacceptable, is there a reason you can't poll more often? If the concern is the load the polling will impart on the server(s) then there are ways to keep the polling very light-weight. No matter what you are going to need the trigger (or something else) to decide if action is needed at the point rows are inserted - if you make this decision in a trigger and simply note it in a simple status table then you could poll every second-or-few to wait for that status marker being updated (a DB hit each second to perform a simple lookup like that once every few seconds (or even every second, or more often) is not going to impart significant load, unless you are using some cloud DB service that charges you by the query). Again, this is far from ideal, but it seems safer to me than the xp_cmdshell option.

Whatever you do, be careful to make sure failures in the process are flagged appropriately. As you are pulling the extra processing out of the main transaction, you can't rely on your existing exception handling to tell you that it failed for some reason – so, you might not know that you have a growing data consistency problem. The key advantage of the trigger here is that by staying in the same transaction you know your data is kept consistent, as one half won't be committed if the other half fails.


Transpose MySQL rows as column names - DYNAMIC My goal is to be able to calculate the time difference between the ticket timestamp being created and closed and if reopened Add time from being reopened to closed again etc..
I have an existing mySQL table called ost_ticket_event as follows:

I need to display this table like below where the state could be dynamic, ie: created, closed, assigned, transfer or reopened.
The state for one ticket_id could be closed/reopened more than once etc.:

ticket_id//////CREATED///////////////CLOSED//////////////REOPENED/////////////CLOSED2//
1-----------18-01/15 12:00 -------18-01/15 13:00------18-01/15 13:30------18-01/15 14:00
2-----------19-01/15 10:00--------20-01/15 09:00-------------NULL------------NULL

I tried LEFT JOIN Group CONCAT but cant seem to get it to produce the desired results.

This is my query:

SET group_concat_max_len=20000;

SET @a = 0;
SET @b = 0;
SET @num := 0;
SET @ticket_id := '';

SET @line1 =    CONCAT (
 'SELECT ',
 (
  SELECT CONCAT('ticket_id,', GROUP_CONCAT(' state',@a:=@a+1,', timestamp',@a))
  FROM ost_ticket_event as ts1
  WHERE ticket_id =
  (
   SELECT ticket_id
   FROM ost_ticket_event tm
   GROUP BY ticket_id
   LIMIT 1
  )
 ),
 ' FROM ost_ticket_event' ,
 (
  SELECT CONCAT(' ', REPLACE(REPLACE(GROUP_CONCAT(' LEFT JOIN ( SELECT ticket_id as         ticket_id',@b:=@b+1,'| state as state',@b,'| timestamp as timestamp',@b,' FROM ( SELECT *| @num := IF(@ticket_id = ticket_id| @num + 1| 1) AS row_number| @ticket_id := ticket_id AS dummy FROM ost_ticket_event ORDER BY ticket_id) AS x',@b,' WHERE x',@b,'.row_number = ',@b,') as t',@b,' ON  ticket_id',@b,' = ticket_id'),',',' '),'|',','))
  FROM ost_ticket_event as ts2
  WHERE ticket_id =
  (
  SELECT ticket_id
      FROM ost_ticket_event tm
   GROUP BY ticket_id
   LIMIT 1
  )
 ),
 ' GROUP BY ticket_id'
);

PREPARE my_query FROM @line1;
EXECUTE my_query;

This is the output:

I worked it out, in-case anyone would like to know the following Query did it:

SELECT CONCAT(
'SELECT `ost_ticket_event`.ticket_id', GROUP_CONCAT('
 ,    `t_', REPLACE(state, '`', '``'), '`.timestamp
     AS `', REPLACE(state, '`', '``'), '`'
 SEPARATOR ''),
' FROM `ost_ticket_event` ', GROUP_CONCAT('
 LEFT JOIN `ost_ticket_event`   AS `t_', REPLACE(state, '`', '``'), '`
        ON `ost_ticket_event`. ticket_id = `t_', REPLACE(state, '`', '``'), '`.ticket_id
       AND `t_', REPLACE(state, '`', '``'), '`.state = ', QUOTE(state)
 SEPARATOR ''),
' GROUP BY `ost_ticket_event`.ticket_id'
) INTO @qry FROM (SELECT DISTINCT state FROM `ost_ticket_event`) t;

PREPARE stmt FROM @qry;
EXECUTE stmt;


Filter predicates executing before Access predicates This is oracle 11g. When I try to do a select query on a table, in the explain plan option I see that the filter predicates of the query(predicates on column without indexes) are executed before the Index access predicate. I don't understand why the query optimizer is picking this plan?

I tried the --+ ordered_predicates option, but it ignored that.

Filter predicates are just as horrible as full table scans right?

Edit Query and the plan:

select column1, column2, column3 from table where column1 >= :cutoff and trunc(column2) = trunc(sysdate-1) and column3 = :column_value

The index is defined on column 1.

This is the query plan:

Plan hash value: 4098448107

-------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                       |  1619K|    49M|  4433K  (1)| 00:02:54 |
|*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TABLE_NAME            |  1619K|    49M|  4433K  (1)| 00:02:54 |
|*  2 |   INDEX RANGE SCAN                  | NAME_OF_THE_INDEX     |    87M|       |   648K  (1)| 00:00:26 |
-------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("TABLE_NAME"."column3"=:column_value AND TRUNC(INTERNAL_FUNCTION("TABLE_NAME"."column2"))=TRUNC(SYSDA
              TE@!-1))
   2 - access("TABLE_NAME"."column1">=TO_NUMBER(:CUTOFF) AND "TABLE_NAME"."column1" IS NOT NULL)

The order of steps displayed in an execution plan is not the order of steps in which they are executed.

The database first accesses the index called NAME_OF_THE_INDEX using these predicates:

2 - access("TABLE_NAME"."column1">=TO_NUMBER(:CUTOFF) AND "TABLE_NAME"."column1" IS NOT NULL)

Then the database accesses the table called TABLE_NAME using the rowid from index NAME_OF_THE_INDEX  using these predicates:

1 - filter("TABLE_NAME"."column3"=:column_value AND TRUNC(INTERNAL_FUNCTION("TABLE_NAME"."column2"))=TRUNC(SYSDATE@!-1))

So it works as expected, and uses the access predicates first, and the filter predicates after that.

Unfortunately I could not find a good example with explanation in the official documentation, but I can recommend something better. To have a better understanding of execution plans, read these series (there are 14 parts):

https://www.red-gate.com/simple-talk/sql/oracle/execution-plans-part-1-finding-plans/


Blocking incoming database links Let's say there is an Oracle database A and another Oracle database B. Our database is A.

Is there a way to block database links from B to A?

If you want to block all connections from whatever machine B is running on to A whether they are not they are using a database link, you can use the TCP.EXCLUDED_NODES parameter in your sqlnet.ora file.

If that is not what you want, you could potentially write a login trigger that tries to deduce whether the connection is via a database link presumably based on things like the machine and program from v$session or the IP address from sys_context('USERENV', 'IP_ADDRESS').  A login trigger can work reasonably well assuming your goal is mostly to keep honest users honest.  But since you are depending on information that is supplied by the client, a hostile attacker can often work around the issue-- often rather easily.


How to change PostgreSQL IP address for streaming replication I have Postgres replication (master & slave).

The slave server has 2 local IP addresses (192.168.1.17 & 192.168.2.12). As far as I know the streaming replication worked fine with slave using 192.168.2.12. But somehow the slave could not connect to master because it required another IP:

  2017-01-30 23:53:13.510 WIB >FATAL:  no pg_hba.conf entry for replication connection from host "192.168.1.17", user "user1", SSL off

I can put 192.168.1.17 in pg_hba.conf to work around this problem. I want to know if I did anything wrong to make the slave Postgres IP change? It was working just fine.

This is not the question about PostgreSQL - this is about routing. Just route from 192.168.1.17 became as better than from 192.168.2.12


Predict MySQL errors when turning on STRICT mode(s) I've got a database which hasn't been very well designed. For example, there are DATETIME fields (not null) which have values like 0000-00-00 00:00:00.

I want to turn on STRICT_TRANS_TABLES mode. The database functions but the apps often fail with broken queries (often INSERT/UDPATE)

Are there any tools to recommend what should be fixed by looking at the data in tables & structure?

Specifically looking for:

Built in warnings (if any?)
Asking the mysql server if it thinks there is anything wrong with the structure & table data

There are few, if any, tools are available.
You really must study the code enough to understand it, before thinking about automated fixes.
You can probably study each problem in depth and fix it before moving on.  For example, changing 0 dates to NULLs is probably an ALTER plus an UPDATE.  But then the code needs changing to test for NULL instead of 0.


Replicación Master / slave - MySql I have slave setup and looks like it is running fine.

But does not replicate.

My Master is an IP of my Hosting and my slave in my Remote Server.

My BD master:

Variable    Valor
File    mysqld-bin.000019
Position    120
Binlog_Do_DB    asistencias,caja,colegios,intranet,intranet2,papeletas,planillas,postulacion,resoluciones,visitas
Binlog_Ignore_DB    mysql,test,app_convoca,intranetugel,moodle_ugel04,postfix,root_cwp,roundcube

My slave:

Variable    Valor
Slave_IO_State  Waiting for master to send event
Master_Host xxx.xx.xx.xxx
Master_User esclavo_hosting
Master_Port 3306
Connect_Retry   60
Master_Log_File mysqld-bin.000019
Read_Master_Log_Pos 120
Relay_Log_File  DB-01-relay-bin.000003
Relay_Log_Pos   284
Relay_Master_Log_File   mysqld-bin.000019
Slave_IO_Running    Yes
Slave_SQL_Running   Yes
Replicate_Do_DB
Replicate_Ignore_DB
Replicate_Do_Table
Replicate_Ignore_Table
Replicate_Wild_Do_Table
Replicate_Wild_Ignore_Table
Last_Errno  0
Last_Error
Skip_Counter    0
Exec_Master_Log_Pos 120
Relay_Log_Space 621
Until_Condition None
Until_Log_File
Until_Log_Pos   0
Master_SSL_Allowed  No
Master_SSL_CA_File
Master_SSL_CA_Path
Master_SSL_Cert
Master_SSL_Cipher
Master_SSL_Key
Seconds_Behind_Master   0

Show connected slaves

Mostrar esclavos conectados
ID del servidor Servidor
1

But it does not replicate

Find the flowchart that explains what happens when you have a mixture of binlog_do_... and binlog_ignore_... -- it is not necessarily what you would expect.

Be aware that

USE db1;
INSERT INTO db2.tbl

uses db1, not db2 for determining whether to replicate.

Which binlog format are you using?

For further debugging, use mysqldumpbinlog to see if a particular row was inserted into the binlog file on the Master and/or whether it arrived in the relay-log file on the Slave.


Should not shrink database to release space in production? My understanding is that we should never shrink the database in production to free up space?   Rather, we should shrink the Transaction log.

Could someone explain why?
Thanks!

To elaborate on sp_BlitzErik's comment:

Generally, you should not be shrinking data or log files on a regular basis.

Shrinking the data file:

Impacts performance in the long run, as it can cause fragmentation (as it may have to move pages to create a contiguous block of free space at the end of the file);
Impacts performance while the shrink is happening;
is logged in the transaction log (and thus, can cause that to get bigger); and
will impact performance later, when more space is required and the file has to grow again.
also: risks failure if other files have used up the available disk space, and the file doesn't have room to grow again.

Shrinking the transaction log can be even more tempting, as it may normally show most of its space as unused. Most of the points above for the data file also apply to the log file (log file shrink won't be logged and cause the log file to grow), but it has some unique concerns:

The log file may require 30GB to support a job that runs every night at 2AM, but only require 500MB during the business day. So, shrinking it may be undone every day, making it pointless to do so.
The log file can suffer fragmentation (on SQL Server at least - search on "virtual log files").

Basically, the only time when shrinking either file makes sense is after unusual activity.

For the data file, if a large amount of data has been removed, and will not be repopulated over time, then it can make sense to shrink the file. If you have a database where data is remove and archived elsewhere once a year - it's being repopulated, and I wouldn't shrink the file. If you discovered a problem with that database, where one very large table was left out of the archive, and you cleaned it up and deleted 8 years of data - then, it might make sense to shrink the database.

Sometimes, you may decide to shrink a database after that annual archive, because there's hardly any free disk space on the server. This is a stopgap measure; I would make sure I had put getting more disk space in motion before I shrank the database, because you know you'll need it.

For log files, the circumstances when it makes sense to shrink them are even more limited. If your database is configured for point-in-time recovery (in SQL Server, the FULL recovery model), but you have not been taking transaction log backups, the transaction log could grow very large. Once you've got the transaction log process up and working, it probably makes sense to shrink the log file. Similarly, if you execute a single transaction that moves a lot of data, that could cause the log file to grow (it has to hold all the data to roll back the current transaction, regardless of the recovery model). If this is a process that occurs rarely (for instance, a twice-a-year load of millions of prospects into a sales database), it may make sense to shrink the log file afterwards. However, even here, allowing other files to grow into the space the transaction log required for that load may mean the load fails the next time you run it, because there's no longer enough free disk to grow the transaction log to the size it needs to be.

To summarize, shrinking data or log files does nothing to help your database run better, can make it run worse, and increases the likelihood that you'll run out of disk space down the road. If you're doing it to free space on your disk for other things, outside of the few specific cases where there's been known abnormal growth, you're only putting off the inevitable, and you better be working on getting more disk space somehow.


Is there any benefit of a primary key that comprises all columns of the table? I have a table with four columns that are all non-nullable, and the data is such that all four are needed to distinguish a unique record. This means that if I were to make a primary key, it would need to comprise all columns. Queries against the table will almost always be to pull back a single record, i.e. all columns will be filtered in the query.

Since every column will need to be searched, does having a primary key benefit me at all (besides enforcing uniqueness of records)?

In your case these fields are natural key.

  Surrogate Key:

  Surrogate keys are keys that have no
  “business” meaning and are solely used
  to identify a record in the table.
  Such keys are either database
  generated (example: Identity in SQL
  Server, Sequence in Oracle,
  Sequence/Identity in DB2 UDB etc.) or
  system generated values (like
  generated via a table in the schema).

  Natural Key:

  Keys are natural if the attribute it
  represents is used for identification
  independently of the database schema.
  What this basically means is that the
  keys are natural if people use them
  example: Invoice-Numbers, Tax-Ids, SSN
  etc.

Surrogate Keys vs Natural Keys for Primary Key

I prefer to add surrogate key to separate business and database model management.
Other question is using clustered and nonclustered index on primary key..
If you table changes (non static table, it has high intensive inserts or update), you will get problem with performance in case of using clustered index on non-monotonic increased key.


SQL Server - How to improve/optimize remote scan on a linked server I'm trying to figure it out how to optimize/improve the following thing, on a VM hosted in MS Azure and a database, also hosted in MS Azure. Besides the instance hosted in MS Azure, there is another instance (local) that has a dedicated database for an integration made between two software applications and there is a linked server between the two instances.

Lately, I noticed that the remote scan is taking up to 100% causing poor performance on some stored procedures that updates the database hosted in Azure.

Here's how the execution plan looks like:

Part of the stored procedure executed against the database from local instance that do some updates on the linked server, looks like this:

UPDATE docl
SET docl.udfvalue1=WMSC.QtyReceptioned,
    docl.udfvalue2=WMSC.QtySent,
    docl.Stringfield1=WMSC.WAREHOUSE
FROM  MyLinkedServer.DataBase.dbo.DocLines docl
INNER JOIN #WMS_Confirmed WMSC on docl.gid=WMSC.GID

UPDATE es
SET es.ADFLAG3 = 1
FROM MyLinkedServer.Database.dbo.DocHeader es
WHERE es.OrderDate>'2018-01-01' -- OrderDate is one of the indexes on `DocHeader` table
AND EXISTS (SELECT 1
              FROM #WMS_Confirmed WMSC
              WHERE es.gid = WMSC.fDocGID
             );

What I found online, like enabling Dynamic Parameters and NestedQueries on linked server provider or OPTION (RECOMPILE), didn't helped at all. Is there something else that I am missing? Thanks

Based on my personal experiences, you will get far better performance by transferring data between the two servers and performing the update on the target server.

After watching one of these at a more detailed level (i.e. amount of data transferred between the servers), I determined that at least 90% of the remote table is pulled over to the local server before comparisons are run. This may be improved by indexing your temp tables correctly, but no guarantees.

The reason is simple: The local server doesn't understand the distribution of the remote server. It doesn't know the file structure, indexing, or any such information, so it assumes that it will have to do a table scan to identify what needs to be changed (at least this is my assumption based on experience.) Manually transferring the data to the server to be updated, then running a process there to do the update is the only way I achieved reasonable increases in efficiency.

It is awkward, and I hope someone has a better answer for you, but there you have it - my personal success story for optimizing these types of cross-server queries between the cloud and a local server.


Check the data latency between two Always On Availability Group servers in ASYNC mode I have implemented Always on Availability Group in asynchronous mode between two servers primary and secondary.

When i tried to check the data latency between the servers in performance monitor using performance counters like MS SQL :availability replica,MS SQL:database replica and MS SQL:databases ,I'm not able to see any results in the graph.

But the graph is showing results for other performance counters.I've tried to get the data latency using DMV sys.dm_hadr_database_replica_states.

Can any one tell me what is the issue ?

How can I check the data latency between the two servers in performance monitor using graphs
as shown in the  msdn article ?

I used this script in a custom report once.

;WITH AG_Stats AS (
            SELECT AGS.name                       AS AGGroupName,
                   AR.replica_server_name         AS InstanceName,
                   HARS.role_desc,
                   Db_name(DRS.database_id)       AS DBName,
                   DRS.database_id,
                   AR.availability_mode_desc      AS SyncMode,
                   DRS.synchronization_state_desc AS SyncState,
                   DRS.last_hardened_lsn,
                   DRS.end_of_log_lsn,
                   DRS.last_redone_lsn,
                   DRS.last_hardened_time, -- On a secondary database, time of the log-block identifier for the last hardened LSN (last_hardened_lsn).
                   DRS.last_redone_time, -- Time when the last log record was redone on the secondary database.
                   DRS.log_send_queue_size,
                   DRS.redo_queue_size,
                    --Time corresponding to the last commit record.
                    --On the secondary database, this time is the same as on the primary database.
                    --On the primary replica, each secondary database row displays the time that the secondary replica that hosts that secondary database
                    --   has reported back to the primary replica. The difference in time between the primary-database row and a given secondary-database
                    --   row represents approximately the recovery time objective (RPO), assuming that the redo process is caught up and that the progress
                    --   has been reported back to the primary replica by the secondary replica.
                   DRS.last_commit_time
            FROM   sys.dm_hadr_database_replica_states DRS
            LEFT JOIN sys.availability_replicas AR
            ON DRS.replica_id = AR.replica_id
            LEFT JOIN sys.availability_groups AGS
            ON AR.group_id = AGS.group_id
            LEFT JOIN sys.dm_hadr_availability_replica_states HARS ON AR.group_id = HARS.group_id
            AND AR.replica_id = HARS.replica_id
            ),
    Pri_CommitTime AS
            (
            SELECT  DBName
                    , last_commit_time
            FROM    AG_Stats
            WHERE   role_desc = 'PRIMARY'
            ),
    Rpt_CommitTime AS
            (
            SELECT  DBName, last_commit_time
            FROM    AG_Stats
            WHERE   role_desc = 'SECONDARY' AND [InstanceName] = 'InstanceNameB-PrimaryDataCenter'
            ),
    FO_CommitTime AS
            (
            SELECT  DBName, last_commit_time
            FROM    AG_Stats
            WHERE   role_desc = 'SECONDARY' AND ([InstanceName] = 'InstanceNameC-SecondaryDataCenter' OR [InstanceName] = 'InstanceNameD-SecondaryDataCenter')
            )
SELECT p.[DBName] AS [DatabaseName], p.last_commit_time AS [Primary_Last_Commit_Time]
    , r.last_commit_time AS [Reporting_Last_Commit_Time]
    , DATEDIFF(ss,r.last_commit_time,p.last_commit_time) AS [Reporting_Sync_Lag_(secs)]
    , f.last_commit_time AS [FailOver_Last_Commit_Time]
    , DATEDIFF(ss,f.last_commit_time,p.last_commit_time) AS [FailOver_Sync_Lag_(secs)]
FROM Pri_CommitTime p
LEFT JOIN Rpt_CommitTime r ON [r].[DBName] = [p].[DBName]
LEFT JOIN FO_CommitTime f ON [f].[DBName] = [p].[DBName]


Disable TCP Connections in SQL Script We have a SQL Server Agent job for loading dumps in case we need to restore from our daily backups. The first step in this job is supposed to kill all connections to the database. However, we frequently encounter a problem where somebody on the network has left an application running which will try to reconnect to the database if disconnected, and so after this first step has completed, this application will reconnect. Therefore, when the restore step comes it fails because it was unable to gain exclusive access to the database.

Usually I would use ALTER DATABASE [db] SET SINGLE_USER WITH ROLL BACK IMMEDIATE to ensure exclusive access, but unfortunately in this case the application reconnects sooner than the restore begins, and so the restore script is unable to access the database at all.

In order to get around this, I thought that it might be possible to disable all TCP connections during the restore so that only the locally running restore script could access the database. So, my question is is there any way in T-SQL to disable/block TCP connections? Or perhaps is there a way to configure the SQL Server Agent to do this when it starts the job?

If you disable TCP connections to SQL Server, SQL Server Agent might also be disconnected.  However, that could be mitigated, and is by default, by enabling the Shared Memory Network Protocol for the server.

Having said that, disabling TCP connectivity is a bit like using a sledgehammer to kill a fly.

Normally, altering the database with SINGLE_USER WITH ROLLBACK IMMEDIATE will suffice; however occasionally you run into an application that is especially aggressive at reconnecting, and will reconnect to the database once it is in single user mode, but prior to the RESTORE DATABASE commencing.

One work-around I've used in the past for this is to put the database into RESTRICTED_USER mode, as in:

USE master;
ALTER DATABASE [xyz] SET PRIVILEGED_USER WITH ROLLBACK IMMEDIATE;
RESTORE DATABASE [zyx] FROM ...

I've seen this not work when the app connects as sa or some other sysadmin user.  In that case, I've done the following rather more aggressive action:

USE master;
ALTER DATABASE [xyz] SET OFFLINE WITH ROLLBACK IMMEDIATE;
ALTER DATABASE [xyz] SET ONLINE;
DROP DATABASE [xyz];
RESTORE DATATABSE [xyz] FROM ...

Having the above code in separate SQL Server Agent Job steps is problematic since each statement is executed in a different batch, which takes time to process.  Keep that code in a single job step to further reduce the ability for a connection to succeed in between statements.


Restoring SQL Server database backup file for read-only access - is it possible to skip the ldf file creation? I need to read some data from a database given by its backup file. So, I issue the RESTORE DATABASE command, which creates two files - the database MDF file and the log LDF file.

Is it possible to restore the database without creating the LDF file?

In brief: NO.

The transaction log file (the .ldf file) is a vital and absolutely necessary part of any SQL Server database. You cannot skip this, you cannot opt to not have it - just doesn't work. SQL Server must have a transaction log stored in a file - no way around that.


How can I connect SSIS to SQL Server 2000? I have to Virtual Machines, one with Windows XP and the other with Windows Server 2008. In my XP VM I have SQL Server 2000 with a database that is my datasource. In WS 2008 I have SQL Server 2008 R2 with an SSIS package to pull data from SQL Server 2000.

My problem: I can not connect to SQL Server 2000, I can not create a connection in my SSIS package. The error says "The connection faild because of an error in initializing provider. Login file for user sa" I am using sa user for the connection and of course this user has access to the database.

Things I have done:

-Both VM do ping between each other.

-I am using Native OLE DB\ Microsoft OLE DB Provider for SQL Server

-Did this configuration from this tutorial: https://msdn.microsoft.com/en-us/library/dd327979.aspx

I continue with the problem, I need to use Sql Server Authentication.
Please if any one has another suggestion, please help.

Regards, Anabel

Finally I had no problems to connect to SQL 2000 from SQL 2008 R2 is perfectly posible. My problem was a configuration problem between my two virtual machines. I tried to do the connection in a normal enviroment and it worked perfectly.

Thanks to all for the comments.

Anabel


Custom unaccent rules in heroku We have an application running in heroku and we want to add a search feature on a field that contains greek characters and we want to make it accent agnostic.

So the idea is to use postgresql's unaccent functionality. The problem is that by default greek rules are not included at heroku and we somehow need to add them (we already have a rules file).

Has anyone managed to add custom rules files at heroku, and if yes could you please share how?

I contacted Heroku after trying to find a solution for the exact same issue. Here is the response:

  Unfortunately, there is no way to upload that unaccent.rules file to
  the Postgres server that hosts your database. You can enable the
  unaccent extension, but not supply a custom rules files. If this is a
  requirement for your app to run, you will unfortunately have to find a
  workaround that doesn't rely on the unaccent.rules file.

So the shadow column might be the way to go for heroku after all.


Have to change schema in production I have a mean-stack website www.myweb.io. I need to upgrade the code and the database. In the previous database, I have a schema:

var UserSchema = new mongoose.Schema({
    username: {type: String, lowercse: true, unique: true},
    ...
})

I decided to change a lot UserSchema, especially to delete username.

I just uploaded the code from dev to prod. Adding the first user worked fine, whereas adding another user gave me:

events.js:182
      throw er; // Unhandled 'error' event
      ^
MongoError: insertDocument :: caused by :: 11000 E11000 duplicate key error index: news.users.$username_1  dup key: { : null }
    at Function.MongoError.create (/opt/funfun/node_modules/mongoose/node_modules/mongodb/node_modules/mongodb-core/lib/error.js:31:11)

I guess it is because both of the two new users don't have username. But why does the system still remember username is the key? In MongoDB, we don't have to change schema, right?

Additionally, I backed up the previous database of production, and copied to development, and restored it. Oddly, unlike production, creating two new users did not give error in development.

Does anyone know if I have to change schema in production? and how?

In MongoDB, we don't have to change schema, right?

MongoDB does not require you to predeclare schema, but there is still some onus for an administrator to remove unneeded indexes or data constraints. The Mongoose Node.js framework you are using includes a declarative client-side schema, but the MongoDB server is unaware of any changes to field or index usage.

By default Mongoose creates new indexes on startup (depending on the config.autoIndex option of your schema), but does not have any tracking to remove or alter index definitions once created. Since you had previously created a unique index on username, the index will need to be manually dropped if you no longer require that constraint.

The duplicate key error message indicates the index name and offending key value:

  E11000 duplicate key error index: news.users.$username_1  dup key: { : null }

Any documents missing the field in a unique index will have a value of null indexed. You cannot have two documents in the same collection missing a field in a unique index unless that index is configured as a  sparse or partial index.

You can manually drop the problematic index using the mongo shell or an admin tool. For example:
db.getSiblingDB('news').users.dropIndex({username:1})

  Additionally, I backed up the previous database of production, and copied to development, and restored it. Oddly, unlike production, creating two new users did not give error in development.

The difference in outcome will depend on whether the target database had the unique index previously created.