Why do async_network_io wait types occur?

ASYNC_NETWORK_IO somehow indicates that the client application isn't processing results as fast as SQL Server feeds them. This could be caused by an issue with the client application or with the network connection between the server and the client application.

Please refer to a post by Thomas LaRock

  The ASYNC_NETWORK_IO wait indicates that one of two scenarios are
  happening. The first scenario is that the session (i.e., SPID) is
  waiting for the client application to process the result set and send
  a signal back to SQL Server that it is ready to process more data. The
  second is that there may be a network performance issue.

or this post by Joe Sack

  As you may already be aware, the ASYNC_NETWORK_IO (seen in SQL 2005)
  and NETWORKIO (seen in SQL 2000) wait types are associated with either
  a calling application that is not processing results quickly enough
  from SQL Server or is associated with a network performance issue.

Since you are using entity framework this post by Brent Ozar  may be useful too

  Looking at the wait stats for these queries, I saw there was a lot of
  ASYNC_NETWORK_IO — often 1000+ milliseconds. That didn’t make any
  sense either! How can a query with so little CPU time and so few reads
  take so long to complete? It’s not like the application was asking for
  millions of rows and couldn’t consume the results fast enough.


Unexpected behaviour for to_date() with week number and week day

This behavior is documented:

  In to_timestamp and to_date, weekday names or numbers (DAY, D, and related field types) are accepted but are ignored for purposes of computing the result. The same is true for quarter (Q) fields.


Thanks for the help.  I found out it was a HIPS (Host Intrusion Protection System) blocking the registry entries.  Once I disabled it, the installation completed successfully.  Thanks again.


The decision to use a table spool or not depends on the query optimizer's cost assessments. These in turn are influenced by the specific values of parameters when the query plan is compiled (or recompiled).

So, this is just a regular case of parameter sensitivity. The chosen (and cached) plan depends on the parameter values sniffed when a compilation or recompilation occurs. With one set of parameter values, the optimizer decides it is worthwhile using a table spool; with other parameters, the cost estimations come out differently, and a spool is not deemed beneficial. Once the plan is cached, it will be reused for other parameter values until another recompilation occurs.

The usual range of solutions are available, please see my article, "Parameter Sniffing, Embedding, and the RECOMPILE options" for details.


Tracing 'Invalid Number Error' from millions of rows using a large, complicated query

Your suspect #2 cannot be true because null values do not cause ORA-01722.

Q1: You might try

Decode (Translate (AlphanumericColumn, 'x0123456789', 'x'),
  Null, To_Number (AlphanumericColumn),
  SomeIdentifyingNumericReplacementValueForNonNumericContent
)

on all the alphanumeric columns whereever any one is used as a numeric value and later scan for the identifying numeric replacement value, which might even be null if still identifying, in the output to spot the offending rows.

Q2: It might be that the offending conversion occurs outside the error handling block, particularly in one of the initializers in the declare part before the begin or in another exception handler immediately before your When Others.
If true, you should wrap the existing code into another inner declare-begin-end block while keeping your exception handler at the outermost level.


Failover Cluster Manager > Role > SQL Server (Node1 is owner) > Resources (SQL Server & SQL Agent)

My question is, what goes in the connection string for the client
application, the ip for the SQLCluster or the ip for the SQL resource?

You should always refer to the Client Access Name or the IP Addresses you see on the SQL Server role properties.

Also, when I manually failover from Node1 to node2, using the the
roles pane, the owner node changes and the services and shared disks
move over to the node2. But, at the Cluster level, the current Host
Server is still node1.

That happens because one is the management for the Cluster and the other is the management for the Role. A role is related to the service that runs on one node of the cluster and it is somehow independent from the management of the Cluster itself. Therefore, sometimes you can see the owner for the Role and the owner for the Cluster not being the same node at some point.
Sometimes you're gonna see the Quorum disk owned by the node that's not currently the owner of the other disks.


Can you separate schemas and objects with spaces and line feeds in T-SQL?

Yes, this is valid T-SQL:

SELECT
*
FROM
sys
.
databases
d
WHERE
d
.
name
=
'tempdb';

As is this:

SELECT * FROM sys . databases d WHERE d . name = 'tempdb';
GO

And they both work fine.


Can I connect two mysql database located in different server by php?

You can actually use as many connections as needed (within system resources limitations).

Using mySQLi:

<?php
$servername_1 = "server_1";
$username_1 = "username_1";
$password_1 = "password:1";
$db_1 = "db_1" ;

$servername_2 = "server_2";
$username_2 = "username_2";
$password_2 = "password_2";
$db_2 = "db_2" ;

// Create connections, and check
$conn_1 = new mysqli($servername_1, $username_1, $password_1, $db_1);
if ($conn_1->connect_error) {
    die("Connection 1 failed: " . $conn_1->connect_error);
}

$conn_2 = new mysqli($servername_2, $username_2, $password_2, $db_2);
if ($conn_2->connect_error) {
    die("Connection 2 failed: " . $conn_2->connect_error);
}

echo "Connected successfully";
?>

Using PDO

<?php
$servername_1 = "server_1";
$username_1 = "username_1";
$password_1 = "password:1";
$db_1 = "db_1" ;

$servername_2 = "server_2";
$username_2 = "username_2";
$password_2 = "password_2";
$db_2 = "db_2 ;

try
{
    $conn_1 = new PDO("mysql:host=$servername_1;dbname=$db_1", $username_1, $password_1);
    // set the PDO error mode to exception
    $conn_1->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "Connected successfully 1";
}
catch(PDOException $e)
{
    echo "Connection failed: " . $e->getMessage();
}

try
{
    $conn_2 = new PDO("mysql:host=$servername_2;dbname=$db_2", $username_2, $password_2);
    // set the PDO error mode to exception
    $conn_2->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "Connected successfully 2";
}
catch(PDOException $e)
{
    echo "Connection failed: " . $e->getMessage();
}
?>

More parameters can be passed both to the mysqli function (such as port number) or the PDO connection strings.

For more info:

PHP Connect to MySQL  (the above examples are basically a variation (x 2) of theirs)
MySQL Improved Extension
MySQL Functions (PDO_MYSQL)


SSMS 2016 takes forever to bring up context menu for an index

I fixed this by changing my authentication method from "Windows Authentication" to "SQL Server Authentication".

I have no idea why.

Before this change i had to wait up to 1 minute for context menu's to appear.

After change context menus are instant.


I would suggest a single trigger, with a single pass through inserted and/or the base table, and just updating all four values for any row that is affected. I have proven before that avoiding updating columns in a row where other column values have changed is not worth the effort, and that updating the same row twice from two different triggers will be slower.

IF UPDATE(DATE) OR UPDATE(DATE1)
BEGIN
  UPDATE t
    SET DateYearID   = ...,
        DateQuarterID  = ...,
        Date1YearID    = ...,
        Date1QuarterID = ...
  FROM dbo._test AS t
  INNER JOIN inserted AS i
  ON t.ID = i.ID;
END

(Also, just as an aside, note that UPDATE() tells you the column was referenced in an update statement, but it doesn't guarantee that the value has changed.)

And yes, I agree with Gianluca's comment: depending on how these four columns are calculated, you may not need a trigger at all. You may not even need a computed column - simple calculations (like DATEPART(YEAR,Date1)) are often best done at query time and only when you need them. Storing redundant data is overrated - I/O and memory are much more important than CPU in almost all systems, even with today's SSD and PCIe flash drives...


I suggest that you pipe the results of your query to a temporary table and work from there. You query is already complex enough and to achieve the results you are after you need to perform multiple acceses to the base data.

When your temp table is ready, you can query it like this:

CREATE TABLE #mytable(
   engineer VARCHAR(6) NOT NULL
  ,qty      INT  NOT NULL
  ,postcode VARCHAR(4) NOT NULL
);
INSERT INTO #mytable(engineer,qty,postcode) VALUES ('JWH',1,'AB25');
INSERT INTO #mytable(engineer,qty,postcode) VALUES ('JWH',1,'AB31');
INSERT INTO #mytable(engineer,qty,postcode) VALUES ('JBR',6,'EH51');
INSERT INTO #mytable(engineer,qty,postcode) VALUES ('RSC',1,'EH51');
INSERT INTO #mytable(engineer,qty,postcode) VALUES ('CPA',1,'FK1');
INSERT INTO #mytable(engineer,qty,postcode) VALUES ('RYM',3,'FK10');
INSERT INTO #mytable(engineer,qty,postcode) VALUES ('CARPET',1,'FK2');
INSERT INTO #mytable(engineer,qty,postcode) VALUES ('RDU',1,'FK2');
INSERT INTO #mytable(engineer,qty,postcode) VALUES ('RSC',2,'FK2');
INSERT INTO #mytable(engineer,qty,postcode) VALUES ('SWO',5,'FK2');
INSERT INTO #mytable(engineer,qty,postcode) VALUES ('BJON',4,'FK3');
INSERT INTO #mytable(engineer,qty,postcode) VALUES ('CPA',4,'FK3');

WITH engineers AS (
    SELECT DISTINCT engineer
    FROM #mytable
),
preaggregation as (
    SELECT engineer, postcode, sum(qty) as qty
    FROM #mytable
    GROUP BY engineer, postcode
)
SELECT *, STUFF((
    SELECT ',' + CAST(qty AS varchar(10)) + ' x postcode'
    FROM preaggregation
    WHERE engineer = engineers.engineer
    FOR XML PATH(''), TYPE
).value('.','varchar(max)'),1,1,SPACE(0))
FROM engineers

Some comments about your code:

NOLOCK does not mean "go faster": it means "no consistency". Get rid of it, you'll thank me later.
Scalar functions are evil, well known performance killers. Avoid them like the plague. In your case, I suppose that the dbo.dateonly function does what's on the tin, so it converts a datetime to a date. You can acheive the same thing with CAST as date (which is still non-SARGable, but doesn't trigger procedural code for each and every row in the input set).

Hope this helps

EDIT: to make the above code compatible with SQL Server 2000, you need another temprary table and (UGH!) a cursor:

IF OBJECT_ID('tempdb..#mytable') IS NOT NULL DROP TABLE #mytable;
IF OBJECT_ID('tempdb..#results') IS NOT NULL DROP TABLE #results;

CREATE TABLE #mytable(
   engineer VARCHAR(6) NOT NULL
  ,qty      INT  NOT NULL
  ,postcode VARCHAR(4) NOT NULL
);
INSERT INTO #mytable(engineer,qty,postcode) VALUES ('JWH',1,'AB25');
INSERT INTO #mytable(engineer,qty,postcode) VALUES ('JWH',1,'AB31');
INSERT INTO #mytable(engineer,qty,postcode) VALUES ('JBR',6,'EH51');
INSERT INTO #mytable(engineer,qty,postcode) VALUES ('RSC',1,'EH51');
INSERT INTO #mytable(engineer,qty,postcode) VALUES ('CPA',1,'FK1');
INSERT INTO #mytable(engineer,qty,postcode) VALUES ('RYM',3,'FK10');
INSERT INTO #mytable(engineer,qty,postcode) VALUES ('CARPET',1,'FK2');
INSERT INTO #mytable(engineer,qty,postcode) VALUES ('RDU',1,'FK2');
INSERT INTO #mytable(engineer,qty,postcode) VALUES ('RSC',2,'FK2');
INSERT INTO #mytable(engineer,qty,postcode) VALUES ('SWO',5,'FK2');
INSERT INTO #mytable(engineer,qty,postcode) VALUES ('BJON',4,'FK3');
INSERT INTO #mytable(engineer,qty,postcode) VALUES ('CPA',4,'FK3');

CREATE TABLE #results(
   engineer VARCHAR(6) NOT NULL
  ,result varchar(8000) NULL
);

INSERT INTO #results (engineer, result)
SELECT DISTINCT engineer, ''
FROM #mytable;

DECLARE eng CURSOR STATIC LOCAL FORWARD_ONLY READ_ONLY
FOR
SELECT engineer, ',' + CAST(qty AS varchar(10)) + ' x ' + postcode
FROM (
    SELECT engineer, postcode, sum(qty) as qty
    FROM #mytable
    GROUP BY engineer, postcode
) AS preaggregation;

DECLARE @eng varchar(6)
DECLARE @result varchar(8000)

OPEN eng
FETCH NEXT FROM eng INTO @eng, @result

WHILE @@FETCH_STATUS = 0
BEGIN

    UPDATE #results
    SET result = result + @result
    WHERE engineer = @eng;

    FETCH NEXT FROM eng INTO @eng, @result

END

CLOSE eng
DEALLOCATE eng

UPDATE #results
SET result = STUFF(result, 1, 1, SPACE(0))

SELECT *
FROM #results


You can use the COUNT() OVER window function and PARTITION by your ID_GROUP field (in a series of CTEs) to filter on in the final query.
WITH CTE_Group AS
(
    SELECT
        DENSE_RANK() OVER(ORDER BY TicketId) AS ID_GROUP,
        CIId
    FROM TicketCIs
),
CTE_Group_Distinct AS
(
    SELECT
        ID_Group,
        CIId,
        COUNT(CIId) OVER (PARTITION BY ID_GROUP) AS DupeCount
    FROM CTE_Group
)

SELECT ID_GROUP, CIId
FROM CTE_Group_Distinct
WHERE DupeCount = 1


Database design for varying amount of relations

What you are describing is called a subtype. A subtype is a common data structure used for things that are very similar but have some differences. For example, and object Named Clothing can be used to describe many types of personal coverings but T-Shirts and Shorts have very different properties. With a Subtype, you store all the common properties in one table and have a related table of the properties that are different. That main table will have a FK to a Type table that will denote which of the subtype tables this object is.

Entitty: Clothing
ClothingID
ClothingTypeID -> ClothingType
Name
Size
Color

Type Entitty: ClothingType
ClothingTypeID
Name

Now for the subtype tables. You will have one for each value in the Type table.

Entity: TShirt
ClothingID (Subtypes always have the same primary key value as the parent object)
SleeveLength
ColorType

Entity: Shorts
ClothingID
HasPockets
LegLength
IsSwimSuit


How does normalization fix the three types of update anomalies?

1NF is basically just "don't keep too much data in a single column", so I think that 2NF and 3NF are the primary fix for all 3 database anomalies, since both 2NF and 3NF involve breaking out items into their own tables:

Insertion anomaly: If you have one big enrollment table that includes both "class" and "student" data (neither of which exists elsewhere), then you can't enter a new (empty) course without at least one corresponding student (because the table is a record of your enrollments). So, apply 2NF and create separate tables for classes, students, and make your original enrollment table link to both by ClassID and StudentID. Now you can enter new classes with no students, and new students with no classes.
Deletion anomaly: Same as above, if each row of your original enrollment table contains the full details of the student and the full details of the class they are enrolled in, then removing the last enrolled student for a class removes the last bit of information about that class. The solution is the same, apply 2NF to make separate tables, so that students can be enrolled or unenrolled without losing class information.
Update anomaly: Same as above, using the single-table method, updating information (say, the room number) for a class with multiple students enrolled might lead to a situation where some rows have been new information and other rows have the old. Applying 2NF as above is again the solution, so that class data is changed in only one place (the classes table).

Note that 1NF still plays some role in your process, as it means you can't try to solve the problem by cramming an entire list of enrolled students into a single field, or add student1, student2, student3 fields or something like that.

We could come up with similar examples where the factor in play is 3NF instead of 2NF: If each "student" has a faculty advisor (and some advisors are assigned to multiple students), that might not be part of the key for our student table, but it is "dependent attribute", and could lead to some of the same problems as above. So faculty advisor could be broken out into its own table.

A few resources I found helpful:

Normalization in DBMS: 1NF, 2NF, 3NF and BCNF in Database
Fundamentals of Database Design: Anomalies


How does SQL Server generate a query execution plan that adds up to 6,000%?

This bug has been known for years, and won't ever be fixed. I first reported it 6 years ago but it has existed far longer than that. These are just estimated costs and don't really have any bearing on the optimizer itself and whether you can trust it. It's simply the showplan output that has some questionable math.

Connect #267530
Connect #370798
Connect #436184
Connect #621330

Solution: use SentryOne Plan Explorer, which tends to get the math right (or simply stop looking at estimated costs and look at more meaningful metrics). If you open this plan in Plan Explorer, the percentages will add up correctly.


Sybase ASE uses blocking locks for transaction isolation, meaning that readers block writers and writers block readers (unlike other databases which use versioning so that writers don't block readers and vice versa).

So each SELECT is blocked by the exclusive lock taken out by the earlier UPDATE on the table it is selecting from, since that update was done by a different session (it wouldn't block if it was the same session). Since neither SELECT can proceed now, there is a deadlock.


What is the fastest way to (session) lock table?

To further expand on my comment, here is some sample code for you to look at.  I don't like the idea of introducing intentional locking on what appears to be a core table in your system.  It will effectively slow everyone down to a single-threaded access.

The ideal solution would remove the need to have the update from and update to logging actions in a specific sequence.  You can do this by adding a guid or some other identifier to the log table and use that to group the update from and update to actions together.

This example assumes that [Name] is a constant value and won't be changing.

/** Build up our table and triggers

    Note that I have consolidated the trigger logic into a single trigger
    and the additional column on T1_Log
    **/
CREATE TABLE dbo.T1
    (
    [Name] NVARCHAR(40) PRIMARY KEY NOT NULL
    , [Value] NVARCHAR(MAX) NOT NULL
    )

CREATE TABLE dbo.T1_Log
    (
    Log_ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY
    , Log_ActionGUID UNIQUEIDENTIFIER NOT NULL
    , Log_TS DATETIME DEFAULT GETDATE() NOT NULL
    , Log_Action VARCHAR(20) NOT NULL
    , Log_Session_ID INT DEFAULT @@SPID NOT NULL
    , [Name] NVARCHAR(40) NOT NULL
    , [Value] NVARCHAR(MAX) NOT NULL
    )

GO

CREATE OR ALTER TRIGGER trg_T1_Log ON dbo.T1
    AFTER INSERT, UPDATE, DELETE
AS
BEGIN

    DECLARE @Log_ActionGUID UNIQUEIDENTIFIER = NEWID()

    ;WITH CTE_Actions AS
        (
        SELECT Log_Action = CASE    WHEN D.[name] IS NULL THEN 'insert'
                                    WHEN I.[name] IS NULL THEN 'delete'
                                    ELSE 'update from'
                                    END
            , Log_Sort = 1
            , [Name] = COALESCE(D.[name], I.[name])
            , [Value] = COALESCE(D.[Value], I.[Value])
        FROM inserted AS I
            FULL OUTER JOIN deleted AS D ON D.[Name] = I.[Name]
        UNION ALL
        SELECT 'update to' AS Log_Action
            , Log_Sort = 2
            , I.[Name]
            , I.[Value]
        FROM inserted AS I
        WHERE EXISTS (SELECT TOP (1) 1 FROM deleted AS D WHERE D.[name] = I.[name])
        )
    INSERT INTO dbo.T1_Log
        (Log_ActionGUID, Log_Action, [Name], [Value])
    SELECT @Log_ActionGUID
        , Log_Action
        , [Name]
        , [Value]
    FROM CTE_Actions AS A
    ORDER BY Log_Sort

END

GO

/** Test Statements **/
INSERT INTO dbo.T1
    ([Name], [Value])
VALUES
    ('John Smith', 'Smith Value 1')

UPDATE dbo.T1
SET [Value] = 'New Value'
WHERE [Name] = 'John Smith'

DELETE FROM dbo.T1 WHERE [Name] = 'John Smith'

/** Show Log Data **/
SELECT * FROM dbo.T1_Log
ORDER BY Log_TS, Log_ActionGUID

/** Cleanup **/
DROP TABLE IF EXISTS dbo.T1_Log
DROP TABLE IF EXISTS dbo.T1


improve performance of insertions using MySQL for 10 million records in 5 tables

A date in a VARCHAR?  That will probably be a nuisance later.
No PRIMARY KEY?  Naughty.  If it is a "mapping" table, see my tips and justficications
What order are the rows in?  If the incoming data is roughly in the same order as some index, then that index will be more efficiently built.  (This won't explain 22 hours.)
What is the value of innodb_buffer_pool_size, and how much RAM is there?  For 4GB of RAM, I recommend innodb_buffer_pool_size 2G
key_buffer_size applies to MyISAM, not InnoDB.  Use 20M if not using MyISAM.

Adding the secondary key after the LOAD will probably make it run faster.  However, it won't explain 22 hours.  In InnoDB, any KEY other than PRIMARY KEY is a 'secondary key'.  Currently (mob_no, act_date) is a secondary key.  (but it may be come PRIMARY if this is a mapping table).


How to use except but excluding a column from the except operator comparison -psql?

except can be re-written as not exists query:

select t1.id, t1.amount, t1.email, t1.date
from t1
where not exists (select 1
                  from t2
                  where t2.amount = t1.amount
                    and t2.email = t1.email
                    and t2.date = t1.date);

But your statement seems to indicate you do not actually want except but something entirely different.


Not tested, but the idea is using same query twice with different LIMITs (depending on %HOURS passed) in a UNION.

(
SELECT * FROM villa_table v
 ORDER BY villa_order ASC, v.ID
 LIMIT %HOURS, 999999999999
) UNION ALL (
SELECT * FROM villa_table v
 ORDER BY villa_order ASC, v.ID
 LIMIT 0, %HOURS
)

You'll need to fill in %HOURS in your script language or stored procedure. Also once %HOURS is larger than the COUNT(*) of villa_table you'll need to restart it from 0.

Note how the parentheses are necessary.

Also note that the ORDER BY fields must uniquely identify rows (i.e. append the PRIMARY KEY!) to prevent possible ambiguous sorting.


postgresql: procedure to return the number of occurrences of given year and service

Assuming the tb_orders table has an id, would you not be able to do something like the following?
RETURN (SELECT COUNT(tbo.id)
          FROM tb_orders tbo
         WHERE YEAR(tbo.created_dt) = year AND tbo.order_code = order_code);

In your DECLARE you are overwriting the year value, and I'm not at all sure what n_service is supposed to do. If all you want is a count, then COUNT() is all you need. Mind you, using YEAR() in the WHERE clause is not great, but it's sufficient for the sake of this example.

Note: The syntax may be a bit off. I'm a bit rusty with stored procedures in PostgreSQL.


You got problem because sqllite and sql server datatype are different.

In the specific, you use a text datatype. This is bad, in sql server, for many reasons:

Text will be removed in future versions of sql server: https://msdn.microsoft.com/it-it/library/ms187993.aspx
You cannot compare with the equal operator
Always choose appropriate data type. Store a number in a text field will lead you to problems.

So, I suggest you to build your table with appropriate datatypes. In your case name must be a varchar and age an integer (if it is ok).

Also you must not learn starting on a book for a specific rdbms and use another one, or you will get a lot of problems like this.


Pressing Ctrl+C during UPDATE query in Postgres, was the query partially run?

A statement is always atomic. That is true for all DML statements and nearly all DDL statements.

So either everything succeeds or nothing. If you interrupt it halfway through, everything that was changed until then will be rolled back.


Why does changing the declared join column order introduce a sort?

Is this intentional?

It is by design, yes. The best public source for this assertion was unfortunately lost when Microsoft retired the Connect feedback site, obliterating many useful comments from developers on the SQL Server team.

Anyway, the current optimizer design does not actively seek to avoid unnecessary sorts per se. This is most often encountered with windowing functions and the like, but can also be seen with other operators that are sensitive to ordering, and in particular to preserved ordering between operators.

Nevertheless, the optimizer is quite good (in many cases) at avoiding unnecessary sorting, but this outcome normally occurs for reasons other than aggressively trying different ordering combinations. In that sense, it is not so much a question of 'search space' as it is of the complex interactions between orthogonal optimizer features that have been shown to increase general plan quality at acceptable cost.

For example, sorting can often be avoided simply by matching an ordering requirement (e.g. top-level ORDER BY) to an existing index. Trivially in your case that could mean adding ORDER BY l.a, l.b, l.c, l.d, l.e, l.f, l.g, l.h; but this is an over-simplification (and unacceptable because you do not want to change the query).

More generally, each memo group may be associated with required or desired properties, which may include input ordering. When there is no obvious reason to enforce a particular order (e.g. to satisfy an ORDER BY, or to ensure correct results from an order-sensitive physical operator), there is an element of 'luck' involved. I wrote more about the specifics of that as it pertains to merge join (in union or join mode) in Avoiding Sorts with Merge Join Concatenation. Much of that goes beyond the supported surface area of the product, so treat it as informational, and subject to change.

In your particular case, yes, you may adjust the indexing as jadarnel27 suggests to avoid the sorts; though there is little reason to actually prefer a merge join here. You could also hint a choice between hash or loop physical join with OPTION(HASH JOIN, LOOP JOIN) using a Plan Guide without changing the query, depending on your knowledge of the data, and the trade-off between best, worst, and average-case performance.

Finally, as a curiosity, note that the sorts can be avoided with a simple ORDER BY l.b, at the cost of a potentially less efficient many-to-many merge join on b alone, with a complex residual. I mention this mostly as an illustration of the interaction between optimizer features I mentioned previously, and the way top-level requirements can propagate.


How do you truncate a `date` (not `timestamptz`) to month?

Yes it's safe.

It's not exactly clear in the documentation but date_trunc can return either timestamp or timestamptz depending on what you pass it. If you pass a date, it will cast the input to timestamp and returns timestamp so there can be no tz issue when casting back to date.

If you pass a timestamptz it does the truncation the way you'd expect and returns a timestamptz:

select current_timestamp-'40d'::interval;
┌───────────────────────────────┐
│           ?column?            │
├───────────────────────────────┤
│ 2014-10-27 19:32:13.869909+00 │
└───────────────────────────────┘

select date_trunc('month',current_timestamp-'40d'::interval);
┌────────────────────────┐
│       date_trunc       │
├────────────────────────┤
│ 2014-10-01 00:00:00+01 │
└────────────────────────┘


Should I run a delete first before I execute index rebuild to a large table about 15 Million records

Really short answer: No.

Longer Answer:

A few thoughts to help you out here:

15 Million is a decent size - but it isn't impossibly huge.
I presume you are creating the index to better query it. So you care about the data inside of the table, right? So if you delete it you will have to just add the data again after the delete, now on a table with an additional index so that insert will be longer than it probably would have been.
Would you get rid of all of the data? If so, I wouldn't try and do a delete statement in one big fell swoop on a table of that size. It will all be logged and it will be slow and will impact your server's performance potentially worse than an index rebuild. If you are truly removing every row - a Truncate would be better IF you were to do a delete first.

So basically - my thought process is - no don't delete first.. There are always caveats, though.

Update

I answered like I thought you asked creating an index, not rebuilding. My answer doesn't change in fact it becomes simpler -  you still don't need to do this. In fact the point of an index rebuild is to reorganize the index and remove index fragmentation. If you remove all the rows in your table before hand, it sort of defeats the need for the rebuild in a sense, right? So you can make your index rebuild faster or reduce the impact.

If in Enterprise Edition you can do this as an online operation to reduce the impact to the users. Watch transaction log growth, though.

You can choose to Reorganize instead of Rebuild. There are caveats here though - you aren't fully taking care of all levels of fragmentation, it can actually take longer, but it can have less of an impact.

You can change the degree of parallelism for the actual rebuild operation to take advantage of parallelism.

You can use a script like this one from Ola Hallengren to actually see if you even need to rebuild first and then only rebuild if you need to.

But no.. You don't ever need to empty the table first - unless you plan to empty it afterwards anyway.


The subtraction (-) causes the error.  It is pessimistic.  You are stuck with using CAST(... AS SIGNED) to avoid the error.

SHOW VARIABLES LIKE 'sql_mode';

See if it includes NO_UNSIGNED_SUBTRACTION

For 'rationale', it may be best to quote some (of many) bug reports on the topic:

https://bugs.mysql.com/bug.php?id=61410 :

  This is intentional change in behavior in 5.5. For those who needs this kind of statements to work and produce meaningful (unlike in pre-5.5) results, there is NO_UNSIGNED_SUBTRACTION SQL mode (http://dev.mysql.com/doc/refman/5.5/en/server-sql-mode.html#sqlmode_no_unsigned_subtractio...).

https://bugs.mysql.com/bug.php?id=46799 :

  http://dev.mysql.com/doc/refman/5.1/en/numeric-types.html

  Subtraction between integer values, where one is of type UNSIGNED, produces an unsigned result by default. If the result would otherwise have been negative, it becomes the maximum integer value. If the NO_UNSIGNED_SUBTRACTION SQL mode is enabled, the result is negative.

https://bugs.mysql.com/bug.php?id=21070 :

  "Warning: When you use subtraction between integer values where one is of type UNSIGNED, the result is unsigned unless the NO_UNSIGNED_SUBTRACTION SQL mode is enabled. See Section 12.8, “Cast Functions and Operators”."


In such cases I am storing version numbers, and using referential integrity to make sure version numbers begin with 1 and have no gaps. Because I use constraints, I am sure my solution works in high concurrency situations. I have described this approach in this article


tuning huge delete operation on sql server table

Testing based on your comments

Tested on SQL Server 2014 SP3

  stg_table and stg_table_1 doesn't have indexes on service_date.

  both of these tables are loaded with million rows of data and delete
  operation is taking lot of time.

DDL

CREATE TABLE dbo.st_table_1( stg_table_1_ID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
                             service_date datetime2,
                            val  int)
CREATE TABLE dbo.stg_table (stg_table_ID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
                            service_date datetime2,
                            val  int)

PK's + Clustered indexes on identity fields.

DML

INSERT INTO dbo.stg_table WITH(TABLOCK)
(
service_date,val)
SELECT -- 1M
 DATEADD(S,rownum,GETDATE()),rownum
 FROM
 (SELECT TOP(1000000) ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) as rownum
FROM master.dbo.spt_values spt1
CROSS APPLY master.dbo.spt_values spt2) as sptvalues

INSERT INTO dbo.st_table_1 WITH(TABLOCK)
(
service_date,val)
SELECT -- 2.5M
 DATEADD(S,rownum,GETDATE()),rownum
 FROM
 (SELECT TOP(2500000) ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) as rownum
FROM master.dbo.spt_values spt1
CROSS APPLY master.dbo.spt_values spt2) as sptvalues

INSERT INTO dbo.stg_table WITH(TABLOCK)
(
service_date,val)
SELECT -- 4M
 DATEADD(S,rownum,GETDATE()),rownum
 FROM
 (SELECT TOP(4000000) ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) as rownum
FROM master.dbo.spt_values spt1
CROSS APPLY master.dbo.spt_values spt2) as sptvalues

2.5M rows in dbo.st_table_1 and 5M rows in dbo.stg_table
(Almost) all of these 2.5M rows will be deleted by the query Which is a more than 10 times less than yours.

Running your query

The actual execution plan for your base delete statement

As expected dbo.stg_table is accessed twice to get the max & min values with a stream aggregate.
The cpu time & elapsed / execution time:

  CPU time = 4906 ms,  elapsed time = 4919 ms.

A missing index hint is added to the execution plan:

CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[st_table_1] ([service_date])
INCLUDE ([stg_table_1_ID])

However, when we add the index, an extra sort appears to delete the rows from this newly added index:

The plan

And the cpu time / elapsed time increases:

   CPU time = 11156 ms,  elapsed time = 11332 ms.

YMMV, but from my example, based on your comments about data, it did not improve the query.

Creating an index on  [dbo].[stg_table]

CREATE NONCLUSTERED INDEX IX_service_date
ON [dbo].[stg_table] ([service_date]);

As a result the MAX() and MIN() can leverage the newly created index to return only one row instead of a full clustered index scan:

With the execution time improved:

 SQL Server Execution Times:
   CPU time = 2609 ms,  elapsed time = 4028 ms.

And the execution plan

But this is only based on indexing & my own example.
Proceed at your own risk.

Extra Notes

You should look into splitting that delete into separate batches so it won't fill up the log file & not having one big block of failed / succeeded delete .

You could also consider using (TABLOCK) so the entire table is locked from the very beginning.

SET STATISTICS IO, TIME ON;
delete dbo.st_table_1 WITH(TABLOCK)
where service_date between(select min(service_date) from stg_table)
                   and (select max(service_date) from stg_table);

Update: SELECT INTO + sp_rename

select * into db.temp_stg_table_1
from db.stg_table_1
where service_date not between( select min(service_date) from db.stg_table)
                             and (select max(service_date) from db.stg_table);

exec sp_rename 'stg_table_1' , 'stg_table_1_old'

exec sp_rename 'temp_stg_table_1' , 'test_table_1'

drop table stg_table_1_old

  how about if go with above logic to delete the millions of records.
  any advantages and disadvantages with that.

Apart from performance, sp_rename needs a Sch-M lock to complete, meaning that it has to wait for all other sessions to release their locks on the table before it can be modified. Any indexes/constraints on the original table will be gone and you will have to recreate them.

When I run the query on my own data:

select * into dbo.temp_stg_table_1
from dbo.st_table_1
where service_date not between( select min(service_date) from dbo.stg_table)
                             and (select max(service_date) from dbo.stg_table);

This does not represent your data, keep that in mind.

It is reading all rows to return 0 which is not optimal.

With a high execution time:

 SQL Server Execution Times:
   CPU time = 27717 ms,  elapsed time = 10657 ms.

But this is not really meaningful without more information regarding your data.
A query plan would be needed to give out more correct advice.


Temporal tables: history table population

I wanted to see if I could reproduce your observation of activity on the history table during inserts to the temporal table. To isolate the two tables I will place each on its own filegroup. I started with a new database in its own directory:

use master;
go

alter database DB219238 set single_user with rollback immediate;
drop database if exists DB219238;
go

CREATE DATABASE DB219238
ON PRIMARY
    -- Primary filegroup gets the default values
    ( NAME = N'DB219238',
      FILENAME = N'D:\Database\Data\DB219238\DB219238.mdf' ,
      SIZE = 3MB ,
      MAXSIZE = UNLIMITED,
      FILEGROWTH = 65536KB ),
FILEGROUP [DATA]  DEFAULT
    -- This will hold the temporal table
    ( NAME = N'Data',
      FILENAME = N'D:\Database\Data\DB219238\Data.ndf' ,
      SIZE = 1MB ,            -- deliberately small so I can observer growth events
      MAXSIZE = UNLIMITED,
      FILEGROWTH = 1MB ),     -- deliberately small
FILEGROUP [History]
    -- This will hold the history table
    ( NAME = N'History',
      FILENAME = N'D:\Database\Data\DB219238\History.ndf' ,
      SIZE = 1MB ,            -- deliberately small, as above
      MAXSIZE = UNLIMITED,
      FILEGROWTH = 1MB )      -- deliberately small
LOG ON
    -- Just the defaults
    ( NAME = N'DB219238_log',
      FILENAME = N'D:\Database\Log\DB219238_log.ldf' ,
      SIZE = 8192KB ,
      MAXSIZE = 2048GB ,
      FILEGROWTH = 65536KB );
GO

-- I don't want to be concerned with log growth
ALTER DATABASE DB219238 SET RECOVERY SIMPLE;
GO

Then I created a temporal table and its history table.

use DB219238;
go

create table dbo.Example_History
(
    ExampleId           int             not NULL,
    Information         char(4100)      not NULL,
    SysStartTimeUTC     datetime2(7)    not NULL,
    SysEndTimeUTC       datetime2(7)    not NULL,
) on History;    -- note the filegroup
GO
checkpoint;
go

create table dbo.Example
(
    ExampleId                           int IDENTITY(1,1)   not NULL,
    Information                         char(4100)          not NULL,

    SysStartTimeUTC                     datetime2(7) GENERATED ALWAYS AS ROW START  not NULL,
    SysEndTimeUTC                       datetime2(7) GENERATED ALWAYS AS ROW END    not NULL,

    constraint PK_Example primary key clustered
    (
        ExampleId ASC
    ),

    period for system_time (SysStartTimeUTC, SysEndTimeUTC)
) on Data    -- note the filegroup
with
(
    SYSTEM_VERSIONING = ON ( HISTORY_TABLE = dbo.Example_History )
);
GO
checkpoint;
go

The Information column is just over half a page long and fixed length so one row consumes a page and relatively few rows can cause a lot of page allocation. At this point the File Explorer shows the directory thus:

Now I populate the temporal table:

insert dbo.Example
(
    Information
)
select
    '';
GO 10000    -- arbitrary, but proved sufficient to demonstrate the case
checkpoint;
go

The only file growth has been on the temporal table. Or at least write activity on the History filegroup is less than the 1MB initially allocated to it.

As a further test I ran SysInternals Process Monitor (PM) while performing the inserts. The checkpoint statements throughout the code are to ensure activity is flushed to disk so PM can observe it.  I captured all events on the directory holding the DB's files. It registered 145 WriteFile operations, amongst others, against Data.ndf and no operations at all against History.ndf.

The execution plan for the INSERT is this:

It shows no activity on the history table, only the temporal table.

Building a clustered index on the history table again showed no activity on History.ndf in Process Monitor.

create clustered index IX_History on dbo.Example_History (ExampleId);
checkpoint;

I think this disproves your hypothesis, at least for my minimal setup. I'm not surprised to see file growth on a clustered index build. In SQL Server the clustered index leaf pages are the data. So to get the data in clustered sequence the storage engine copies the data from its current location to its new. Likely there would be a lot of free space left in that file after the clustered index had build, being the space where the data used to be.

I cannot guess why you saw the insert operators referencing the history table.


Well, at least it seems like you can get rid of the UNIONs. Use a CASE ... END to get the values for cashamount, creditnote, chequeamount and credit depending on paymenttypeid. Use IN (or multiple OR) to filter for paymenttypeid in the WHERE clause.

SELECT DISTINCT
       ih.invoiceno,
       c.name nameofcustomer,
       CASE ip.pamenttypeid
         WHEN 1
           THEN ip.amount
         ELSE
           0
       END cashamount,
       CASE ip.paymenttypeid
         WHEN 2
           THEN ip.amount
         ELSE
           0
       END creditnote,
       CASE ip.paymenttypeid
         WHEN 3
           THEN ip.amount
         ELSE
           0
       END chequeamount,
       CASE ip.paymenttypeid
         WHEN 4
           THEN ip.amount
         ELSE
           0
       END credit,
       ip.amount salesvalue
       FROM invoiceheader ih
            LEFT JOIN customer c
                      ON c.id = ih.customerid
            LEFT JOIN invoicedetail id
                      ON id.invoiceno = ih.invoiceno
            LEFT JOIN distributioncenter dc
                      ON dc.id = id.distributioncenterid
            LEFT JOIN invoicepayment ip
                      ON ip.invoiceno = ih.invoiceno
       WHERE dc.id = 1
             AND ip.paymenttypeid IN (1, 2, 3, 4)
             AND ip.invoicedate BETWEEN '2018/05/01'
                                        AND '2018/06/19'
             AND ih.deleted = 0;

Note, that UNION eliminates duplicates. So I added DISTINCT to have duplicates eliminated in this version too. Remove it, if this side effect of UNION wasn't actually wanted or if there cannot be any duplicates (I don't have the complete picture of your schema to judge that.).

But, it's only a shorter query text. Depending on the existing indexes, your first approach might execute faster. Check the execution plans, when in doubt.


Formatting is meta-data.  If you include formatting in a column, you are mixing meta-data with data, which is clearly not a great idea.  I'm not a theory-guru, but I'd bet that is listed somewhere as a no-no.

For your specific example, which I know is only an example, you may actually want to store the format in a separate table.  Below is an example for SQL Server.  I must stress, this is only an example of how you might approach the problem by abstracting the format away from the data.  Please note, I'm not saying this is the best way, or even an appropriate way for your scenario.  Simply that it MIGHT work, as in the output shown below.

IF OBJECT_ID('dbo.PhoneNumbers') IS NOT NULL
DROP TABLE dbo.PhoneNumbers;

IF OBJECT_ID('dbo.PhoneFormats') IS NOT NULL
DROP TABLE dbo.PhoneFormats;

CREATE TABLE dbo.PhoneFormats
(
    PhoneFormatID int NOT NULL IDENTITY(1,1)
        CONSTRAINT PK_PhoneFormats
        PRIMARY KEY CLUSTERED
    , FormatDetails varchar(30) NOT NULL
);

INSERT INTO dbo.PhoneFormats (FormatDetails)
VALUES ('(###) ###-####')
    , ('#### ###-###');

CREATE TABLE dbo.PhoneNumbers
(
    ClientID int NOT NULL
    , PhoneNumber bigint NOT NULL
    , PhoneFormatID int NOT NULL
        CONSTRAINT FK_PhoneNumber_Format
        FOREIGN KEY
        REFERENCES dbo.PhoneFormats(PhoneFormatID)
);

INSERT INTO dbo.PhoneNumbers (ClientID, PhoneNumber, PhoneFormatID)
VALUES (1, 2125551212, 1)
    , (2, 2300123456, 2);

SELECT pn.ClientID
    , FormattedNumber = FORMAT(pn.PhoneNumber, pf.FormatDetails)
FROM dbo.PhoneNumbers pn
    INNER JOIN dbo.PhoneFormats pf ON pn.PhoneFormatID = pf.PhoneFormatID;

the output:

+----------+-----------------+
| ClientID | FormattedNumber |
+----------+-----------------+
|        1 | (212) 555-1212  |
|        2 | 2300 123-456    |
+----------+-----------------+

This can be made to support currency symbols and thousands-separators exactly the same way.


How do Availability Groups work with cheduled jobs and SSIS packages?

Yes, you will need to create the jobs on any other replicas that you would want those specific jobs to run if they were the primary replica.

You will need to create your own logic for if/when each SQL Server Agent job will run.  For instance, do you want to run a job only if the current instance is the primary replica of a particular Availability Group?  You will need to put that into your job.  It can't be blanketed automatically, because that would take away the flexibility of AlwaysOn AG.  Whether you want them disabled on the secondary replica(s) is completely up to you, what those jobs do, and how/when/if you want them to run.

Remember, the secondary replica server isn't just a stand-by server waiting for failover.  It could be a fully functional, accessible server.  Because of this, having every job sitting by idle would be a huge disability.

So, yes, you will need to push our your jobs to other replicas and use some logic as to if the job should continue execution when it kicks off.

For instance, backup jobs can take advantage of the sys.fn_hadr_backup_is_preferred_replica function by determining whether the current replica is the preferred one for a particular database.  This will derive how you have your Availability Group setup for backup preferences.


Specify a frequency range for a SQL agent schedule

How about  mutiple, recurring Monthly Schedules where you have selected the first Monday of every 1 month on Schedule 1, the first Tuesday of every 1 month on Schedule 2, ... the second Friday of every 1 month?  See the following pic to get the idea of what I'm thinking:


Auditing database administrator actions with SQL Server 2014 Standard

If you only need to track database access, maybe collecting schema stability locks with extended events is enough.

I blogged this idea last week for a totally different reason  (tracking unused objects) but the script can be adapted to fit your needs. You can find it here.

Basically, it reads an extended events session using the streaming api and then saves summary information in a table at regular intervals. In your case you need the individual events and probably the sql text as well. It really depends what you're trying to achieve. Maybe a simple and plain file target is enough.


Can you switch recovery mode to simple from full in an Always ON cluster setup?

The DBA is correct - if the database is part of an Availability Group (AG), it has to be in the FULL recovery model.  This is because of the way AGs work - they ship transaction log blocks from the primary server to the secondary server(s).  So all the detailed log records provided by the FULL recovery model are needed.
You could remove the database from the AG during your initial data load.
ALTER AVAILABILITY GROUP [YourAG]
REMOVE DATABASE [YourDatabaseName];

However, the (potentially major) downside to this is that the secondary will need to be "re-initialized" once the data load is done.  That means either using "automatic seeding" to replicate the entire primary database to the secondary server(s) (Enable automatic seeding on an existing availability group), or taking a backup of the primary, restoring it on each of the secondaries, and then re-adding the database to the AG (Prepare a secondary database for an Always On availability group).
Depending on how big the database is after the data load, this might be "no big deal," or it could be a real hassle.  But yeah, that's the other option available to the DBA basically.

By the way, regarding this:

we are seeing within the activity monitor a pile up of queries in a wait state

You didn't mention the actual wait, but unless it's HADR_SYNC_COMMIT or WRITELOG, removing from the AG / switching to SIMPLE recovery might not help.
Even if these are your waits, be aware that you are treating a symptom.  If the app ever needs to dump in data like this once it's live, you probably won't be able to monkey with the AG or recovery models.
Maybe your app could reduce the amount of log data generated by grouping some of its transactions into larger batches.
Or maybe you need to investigate the network speed between the primary and secondary servers (keeping in mind how to properly measure AG throughput).
Those are just some possible remedies.  All of this is just to say that you're better off, in the long run, figuring out the root cause of this "wait state leading to application errors" business now rather than later.

The waits you've showed are lock waits, indicative of a classic blocking chain.
Notice the "Blocked By" column is 131 for all of the rows in the screenshot.  That's a "Session ID" (first column of Activity Monitor).  Keep following that chain until you find the lead blocker ("Blocked By" will be blank), and look in the "Wait Type" column to see what it is waiting on.  That's where you should focus your efforts to unravel the root cause.

Regarding this update:

Apparently the default for doing a backup of the logging directory was set to a large value. When that kicked off to clear out the logging (incremental log thing??), it makes the whole system stutter and caused the issue. That's my current theory. Now he has changed it to 5 minutes and so far, all we see is a stutter and recovery on our side. I have my engineer gather more real data (the actual block if he can catch it).

It sounds like increased resource usage when transaction log backups occur is interfering with your workload.  One solution to this would be to run transaction logs more frequently, as the DBA did.  You could even run the backups every minute if they finish fast enough.
All that said, the original solution of switching to SIMPLE recovery is a viable option if it's feasible to re-initialize the secondary after the initial load is done.


Query to retrieve index details for a particular column in all tables

To check the column properties you can run the query

SELECT * FROM information_schema.columns WHERE table_schema = 'your_schema_name'
AND COLUMN_NAME = 'created_at';

This will list all the tables in your schema with the columns created_at. Here you can check some of the column properties including the COLUMN_KEY with values PRI, UNI, MUL.

For a detailed view of the table index run the below query against the tables returned by the first query.
SHOW CREATE TABLE table_name;


Never on C:\ if it can be avoided, though with smallish SSDs and VMs on laptops it's hard to have anything but C:.

So where else? Depends on the config. I've done like you for simple workstation installs: data, logs, tempdb all on D:. But then you get into more advanced implementations where you have a SAN and many spindles to choose from - there I've had groups of data files on M:\ and N:\, log files on L:\, tempdb on T:\, etc. Making deployment easier isn't the only goal you should consider... it's also reducing the need for re-deployment and/or moving of data/log files later. :-)


Figured this out, it's easy, you just add :paramName in your query and it automatically asks your for the parameters when you run the query.


AD groups and ms SQL security: is this default behavior?

Why do we still get the highest permissions, even when AD group is
  removed from logins, and disabled in DB users?

This is because testuser is a member of both AD\testgroup1 and AD\testgroup2 and both those groups exist in the database with object permissions, resulting in cumulative object permissions for the testuser role member.

There is no requirement that a login exists for a Windows group database user; one can add a Windows group database user and grant permissions to that user regardless of whether a login for the Windows group exists. Permissions granted to groups will be honored for role members even if a corresponding login doesn't exist, as you've observed.

Without an individual login for testuser, the user must be a member of at least one Windows group with a login to connect to the server. This was AD\testgroup1 in your example after you dropped the AD\testgroup2 login. But the user still had object permisssions granted to AD\testgroup2 until you dropped the user.


WITH number_of_tickets AS (
  SELECT user_id, COUNT(user_id) AS number_of_tickets_per_user
  FROM tickets
  GROUP BY user_id
),
total_tickets AS (
  SELECT SUM(number_of_tickets_per_user) total_tickets
  FROM number_of_tickets
)
SELECT
  user_id,
  number_of_tickets_per_user,
  ROUND(number_of_tickets_per_user / total_tickets,3) percent
FROM number_of_tickets, total_tickets;


Yes you will get a performance boost if you do this entirely on the server as the IO will be contained entirely inside the database server.  However, at the same time you will introduce a performance hit on the server potentially.

It's a trade-off but I'd say it's worth it if you have spare capacity.


pg_basebackup giving "could not receive data from client: Connection reset by peer"

If you have errors in your log along the lines of SSL Renegotiation Failure or SSL Error, then you likely ran into an issue with a version of OpenSSL and SSL renegotiation during the streaming basebackup.

Setting ssl_renegotiation_limit = 0 should help address your problem.

There are and have been many discussions now on disabling ssl renegotiation by default on the PostgreSQL lists. Example here: http://www.postgresql.org/message-id/E1ZKB9u-00050W-7V@gemulon.postgresql.org

Hope that helps. =)


Error in the code shown below - Why is it

Twenty Fifteen is a valid year.

SQL> select to_char(sysdate, 'Year') as year from dual;

YEAR
------------------------------------------
Twenty Fifteen

But what you tried works only one way, you can not parse text like that (with suffixes) into a date type.

Datetime Format Element Suffixes

  Notes on date format element suffixes:

  When you add one of these suffixes to a datetime format element, the return value is always in English.
  Datetime suffixes are valid only to format output. You cannot use them to insert a date into the database.


Shrink data MDF file on server running on low disk space

Your sp_spaceused provides 2 sets of the data:
- DB Size + Unallocated space - These numbers include BOTH: Data and log file;
- Total statistics of RESERVED space for all objects within the database;

I bet your 36 GB of free space are in the Log file.

For real numbers use following query:

SELECT
    f.file_id as "File Id",
    f.physical_name as "Physical Name",
    CAST(CAST(ROUND(f.Size/128.,3) as DECIMAL(16,3)) as VARCHAR(16)) AS SizeMB,
    CAST(CAST(ROUND(FILEPROPERTY(f.name, 'SpaceUsed')/128.,3) as DECIMAL(16,3)) as VARCHAR(16)) AS UsedSpaceMB,
    RIGHT('  ' + CAST(CAST((1 - FILEPROPERTY(f.name, 'SpaceUsed') * 1./ f.size) * 100 as DECIMAL(5,2)) as VARCHAR(6)),6) AS FreeSpacePrc
    ,  CASE f.file_id WHEN 2 THEN 'Log File' ELSE IsNull(g.name,'N/A') END as [File Group]
FROM sys.database_files as f with (nolock)
LEFT JOIN sys.filegroups as g with (nolock) ON f.data_space_id = g.data_space_id

Might happen you still have some opened transactions, which hold you from shrinking individual files. See who hols them, close them. Reboot server if necessary. Shrink individual files while nobody accessing the database. switch it temporarily to Single User if necessary:

ALTER DATABASE <Your database> SET SINGLE_USER


SQL Server Named Instance is not listening on Port 1434

Some additional investigation is needed here. First, it is possible that you are filtering out too much of the netstat output in your findstr filter. Instead of "processID", check for the port number using ":1434". Also, using the -q switch instead of -a might be better as it will show bound, nonlistening ports. For example:

netstat -qof | findstr ":1434"

OR, if you start the command prompt as an administrator, you can add the -b switch to print out the name of program associated with each process (saves the step of needing to map the Process ID to the program). When doing this, the output of each entry is on 2 lines, so you won't see the additional info if you use findstr as those lines will never have the matching string. Instead, just pipe to more as follows:

netstat -qofb | more

Now, using netstat is handy to research to see if a port is in use, but for this issue it is more direct to see what SQL Server thinks is going on, and fortunately there are some views that have the info so you don't need to scan / parse the error log. You should check the following:

sys.tcp_endpoints
sys.dm_tcp_listener_states

So just execute the following:

SELECT * FROM sys.tcp_endpoints;
SELECT * FROM sys.dm_tcp_listener_states;

If the first view, sys.tcp_endpoints, shows that "Dedicated Admin Connection" has a state_desc other than "STARTED", then there is an issue.

Now, in the second view, sys.dm_tcp_listener_states, if you do have "::1" (IP v 6) and/or "127.0.0.1" (IP v 4) for ip_address then it is only listening locally (i.e. remote administrative access is OFF). If you instead see "::" (IP v 6) and/or "0.0.0.0" (IP v 4), then remote admin access is ON.

By default remote administrative access is OFF. You can enable it by executing the following (this is not an "advanced" option):

EXEC sp_configure N'remote admin connections', 1; RECONFIGURE;


Should I restart the instance after adjusting Memory Allocation and MAXDOP?

Both of those server configuration options do not require a restart of the database engine.  Running RECONFIGURE will be sufficient in this case.

exec sp_configure 'show advanced options', 1
go
reconfigure
go

exec sp_configure 'max server memory', 12288
go
exec sp_configure 'max degree of parallelism', 4
go

reconfigure
go

-- the new configuration has taken effect

Please see BOL for reference.


Database Schema for Resume/CV and Separate Sections

You are on the right track here.  Somethings to consider:

Users

Likely a User can have multiple resumes.  Job candidates often tailor a resume to a job opening and want to keep all of those.  This means you will need a User table.  Having a user table also gives  you a place to store attributes of the user such as their name.

Sections

A resume has lots of kinds of sections, such as experience, job history, education, and so on.  This means you will want to make you resume sections table a Section Type table.

Thing Types vs. Things

This brings up a common pattern in logical database schema design - the differentiation between kinds of things and the things themselves.  I would recommend you add a Section table related to Resume that holds the actual section of an instance of a resume for a user.

Model Example

Oracle provides a free tool called Oracle Data Modeler which you can use to create a visual model of the table, called an Entity Relationship Diagram (ERD), and then generate your DDL from it.  Here is an example starter model of your database design:

Creating an ERD also helps you start to clarify the requirements.  For example, I made the key to the section table the resume and a sequence number.  This means you could have 2 or more sections on a resume with the same type.  Does this make sense?  Or does it make sense to say there can only be one section of each type per resume?  Second, you want to think about what uniquely identifies a row in each table.  For example, I have used a Resume Number for the key to the resume table.  Wouldn't it make sense to say that each row would be uniquely identified by the User Name and the resume name?  This would prevent the storage of duplicate resumes.

DDL Generation

Once you nail down all the business rules, you can use Oracle Data Modeler to generate your DDL.  While it doesn't support mySQL, you can still generate it and get the base structure and then correct for mySQL specific syntax.  Here is a sample of what I quickly generated from this model:

-- Generated by Oracle SQL Developer Data Modeler 4.1.0.873
--   at:        2015-08-19 09:15:38 EDT
--   site:      Oracle Database 12c
--   type:      Oracle Database 12c

CREATE TABLE "Resume"
  (
    "Resume_Number" NUMBER (10) NOT NULL ,
    "User_Id"       NUMBER (10) NOT NULL ,
    "Name"          VARCHAR2 (100) NOT NULL ,
    "Description"   VARCHAR2 (1000) NOT NULL
  ) ;
ALTER TABLE "Resume" ADD CONSTRAINT "Resume_PK" PRIMARY KEY ( "Resume_Number" )
;

CREATE TABLE "Section"
  (
    "Resume_Number"           NUMBER (10) NOT NULL ,
    "Section_Sequence_Number" NUMBER (5) NOT NULL ,
    "Section_Type_Id"         NUMBER (5) NOT NULL ,
    "Content"                 VARCHAR2 (4000) NOT NULL
  ) ;
ALTER TABLE "Section" ADD CONSTRAINT "Section_PK"
PRIMARY KEY ( "Resume_Number", "Section_Sequence_Number" ) ;

CREATE TABLE "Section_Type"
  (
    "Section_Type_Id" NUMBER (5) NOT NULL ,
    "Name"            VARCHAR2 (100) NOT NULL ,
    "Description"     VARCHAR2 (1000) NOT NULL ,
    "Order_Number"    NUMBER (5) NOT NULL
  ) ;
ALTER TABLE "Section_Type" ADD CONSTRAINT "Section_Type_PK"
PRIMARY KEY ( "Section_Type_Id" ) ;

CREATE TABLE "User"
  (
    "User_Id" NUMBER (10) NOT NULL ,
    "Name"    VARCHAR2 (100) NOT NULL
  ) ;
ALTER TABLE "User" ADD CONSTRAINT "User_PK"
PRIMARY KEY ( "User_Id" ) ;

ALTER TABLE "Resume" ADD CONSTRAINT "Resume_User_FK"
FOREIGN KEY ( "User_Id" )
REFERENCES "User" ( "User_Id" ) ;

ALTER TABLE "Section" ADD CONSTRAINT "Section_Resume_FK"
FOREIGN KEY ( "Resume_Number" )
REFERENCES "Resume" ( "Resume_Number" ) ;

ALTER TABLE "Section" ADD CONSTRAINT "Section_Section_Type_FK"
FOREIGN KEY ( "Section_Type_Id" )
REFERENCES "Section_Type" ( "Section_Type_Id" ) ;

This gives you a good deal of DDL to start with.  A good reference on data modeling is David Hay's Enterprise Model Patterns.  I hope this answer helps you move forward with completing your design!


Default value as expression from other columns

DEFAULT has a lot of restrictions.
you can use here a daily EVENTs which runs every day and set premium accourdind to the expiration date
DROP EVENT IF EXISTS daily_event;
CREATE EVENT daily_event
ON SCHEDULE EVERY '1' DAY
STARTS '2020-12-12 00:00:01' -- mustbe in the future
DO
    UPDATE users u1
    INNER JOIN premium_subscription ps ON u1.user_id = ps.user_id
    SET premium = IF(NOW() > ps.expiration , 1, 0);

To enable the event scheduler see manual


Generate a series of months within a period

Evan explains why the generate_series() cannot do by itself what you want. But it can be done with a simple UNION:

select generate_series('2016-06-25'::timestamp, '2016-12-15', '1 month')
         as g_date
union
select '2016-12-15'
-- order by g_date
;

or UNION ALL:

select generate_series('2016-06-25'::timestamp,
                       '2016-12-15' - interval '1 day',
                       '1 month') as g_date
union all
select '2016-12-15' ;

Also note that generate_series() output is of timestamp without
timezone type. if you want a date type, you need to cast:

select generate_series('2016-06-25'::timestamp,
                       '2016-12-15' - interval '1 day',
                       '1 month')::date as g_date
union all
select '2016-12-15'::date ;


Open source & cheap 'data at rest' encryption solutions

Data-at-rest
There may be a terminology problem here. Data-at-rest encryption usually means

Storage-encryption
Not peer-to-peer nor any other form of data-at-use encryption.

On the forms of encryption suggested, I would advise staying away from those RDBMS-specific solutions as they're less tested than the other options which PostgreSQL suggests

Storage encryption can be performed at the file system level or the block level. Linux file system encryption options include eCryptfs and EncFS, while FreeBSD uses PEFS. Block level or full disk encryption options include dm-crypt + LUKS on Linux and GEOM modules geli and gbde on FreeBSD. Many other operating systems support this functionality, including Windows.
This mechanism prevents unencrypted data from being read from the drives if the drives or the entire computer is stolen. This does not protect against attacks while the file system is mounted, because when mounted, the operating system provides an unencrypted view of the data. However, to mount the file system, you need some way for the encryption key to be passed to the operating system, and sometimes the key is stored somewhere on the host that mounts the disk.

Essentially, different operating systems and file systems abstraction layers provide a better better-tested method of handling data-at-rest encryption.
Yes, that means you have a key. Yes, that means if the key is compromised the data can be read. But if your database is compromised and not the key, the data is secure. And, that's why it's data-at-rest.
So you normally store the key owned by root. Have root mount the secured location, and let the postgres user access that. Obviously PostgreSQL needs access to the data and has to know how to decrypt it.
Now, if other users are on the machine they can't access the data unless they're they postgres user. Moreover, they can't access the key. And if they do manage to compromise the data or even steal the physical encrypted back up they can't access it without the key.


IF else condition on DATE to determine String in other column

i didn't understood what you are meaning with "NEW = 'CHANGE', OLD = 'NO CHANGE' like you will ..." so i wrote 2 cases

Assuming that DataColumn and StrnigColumn are the columns names and myTable is the table name

you have to use a CASE with the DATEADD function

UPDATE myTable
SET StringColumn = CASE WHEN DATEADD(DAY,GETDATE(), -3) > DateColumn
                        THEN 'NO CHANGE'
                        ELSE 'CHANGE'
                   END

if you mean by "NEW = 'CHANGE', OLD = 'NO CHANGE' like you will ..." that you have also to check the value so your query will look like

UPDATE myTable
SET StringColumn = CASE WHEN DATEADD(DAY,GETDATE(), -3) > DateColumn AND StringColumn ='OLD'
                                            THEN 'NO CHANGE'
                        WHEN DATEADD(DAY,GETDATE(), -3) <= DateColumn AND StringColumn ='NEW'
                                            THEN 'CHANGE'
                        ELSE [StringColumn]
                   END


Create multiple field indexes with different types on postgres

This is going to be impossible for us to assist in with the information provided. However, rest assured 18M is not much. Baseline questions include,

Ignoring the LIKE condition, what is the selectivity that can be provided on start_time and product?
Do you ever select on start_time, and name, without product?
When you have a btree on (start_time, product), and a gin/gist tgrm index on name, how much times does the query take?
When you create the index described, how much time does the query take?

Take a look at the plans required by both. I would do these tests on a snap shot of the table so it's fair (or two snapshots with only the indexes mentioned)

CREATE TEMP TABLE foo
AS
  SELECT * FROM marketing


List all used data types of a table or whole database in Oracle DB

Here is an example to find the number and type of columns in a schema:

select data_type, count(*) from dba_tab_cols
where owner = '...' group by data_type order by data_type

DATA_TYPE              COUNT(*)
-------------------- ----------
ANYDATA                       6
BLOB                         32
CHAR                      12335
CLOB                        259
DATE                      16923
LONG                          2
NUMBER                    39618
NVARCHAR2                     9
RAW                          53
ROWID                       270
TIMESTAMP(3)                 37
TIMESTAMP(6)                109
VARCHAR2                 132291
XMLTYPE                       2


How do I read ERD Notation (crow's feet) to convert to Natural Language?

I'd like to answer this more definitively now using the following image (from https://www.lucidchart.com/pages/ER-diagram-symbols-and-meaning)

Using the definitions of these symbols you can see that the following things are true about my originally posted diagram.

A company has 1 to many addresses. (shipping, billing, etc).
A company has 0 to many connections
A connection has one (and only one) company
An address has one company - this should probably contain the double
line connector to show that it is one and only one.

My final question was:

  Do you have a logical method with steps you could provide for
  examining each relationship and converting to natural language?

Place a finger on the entity you are starting with.  For example,
place a finger on Company.
Now say, "Company has..." and choose the related entity you want to
talk about. For example, choose connection.
Look at the relation symbol that touches the Connection entity (it is the empty circle with three lines (legs)) which indicates zero to many.
Continue saying "...zero to many connections".

The final sentence would be,

  "Company has zero to many Connections."

That is how you transform symbols on Entity Relationship Diagrams into natural language.


Best tool/approach to report on table contents over time

You can create triggers that are fired upon insert/delete/update in the table to write the data you need in a separate history table. You can then later query this table to create your reports. No need to change the application that is now responsible for maintaining the data.


( RDS ) MySQL to MySQL migration within aws

If your instances are accessible over the net than you can simply dump the prod instance on the dev instance via the host having required utilities and access to both instances:

mysqldump --host 10.20.30.40 \
          -u myaccount1 \
          -pmypassword1 \
          --all-databases \
          --events \
          --routines  \
| mysql --host 20.30.40.50 \
        -u myaccount2 \
        -pmypassword2


Efficient method of inserting a column into a table based on another table

No, you cannot alter the table structure and insert the data into new column in one statement.

But you can combine last 2 updates:

UPDATE main
SET main.color = CASE WHEN main.state IS NULL
                      THEN 'Red'
                      ELSE ColorTable.color
                      END
FROM main
LEFT JOIN ColorTable ON main.state = ColorTable.state


MySQL 8.0, MacOS, create database issue?

Run this in the mysql client (or similar):

SHOW VARIABLES LIKE 'datadir';

Then look at the ownership and permissions for that directory. It should be owned by the mysql user,  have the mysql group, and the mysql user should have read, write and execute (rwx) permissions, and I think similar for the group.

Which process is running the mysql daemon? It should be the mysql user. In Linux you can get a list of processes which includes the user who is running each. I assume you can do something similar on MacOSX (if that is what you're using).

Assuming this is a new installation, please see this documentation page about Initializing the data directory.


Are database query optimizers aware of storage performance differences?

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.)


Running sp_AskBrent with @ExpertMode = 1 in PowerShell

Brent here. Correct, @ExpertMode = 1 turns on multiple result sets - diagnostics, wait stats, file stats, and perfmon counters.

If you only want one result set, don't turn on @ExpertMode.

If you want multiple result sets, but your application (in this case, PoSH) can't consume them, you'll need to log them to tables. That's where these parameters come in:

@OutputDatabaseName - the name of the database you want to write into. Must already exist.
@OutputSchemaName - the schema where your tables will live, like dbo. Must already exist.
@OutputTableName - the table name for the top result set (diagnostics). This parameter also requires the above two. If the table doesn't already exist, it will be created.
@OutputTableNameFileStats, @OutputTableNamePerfmonStats, @OutputTableNameWaitStats - the rest of the result sets will be written here. If each table doesn't already exist, it will be created. Each of these is totally optional - you can pass in none, some, or all of them.

If you want it to work differently, describe what you need, and I'll see if I can come up with a way to get it. Hope that helps!

From a comment:

  I was actually thinking about writing a health check script which I would run in case of incident or for monitoring and which would parse the output of sp_AskBrent.

OK, cool. Just a few things to keep in mind - the tables don't clean themselves out, and they can get big if you use the @SkipChecksQueries = 0 parameter, for example. The sp_AskBrent® licensing also prohibits distribution, so just make sure you're not bundling the script, or installing it on servers you don't own. Otherwise, go for it! If there's anything I can do to help make it easier, holler.

There's a date field in all of the output tables to help you maintain history tables.


using view (necessary) with where conditions (dynamically)

Try a newer version of MySQL. The view implementation in MySQL-5.5 was very simplistic. I've seen later versions take the view expression into account when doing query planning.


Very slow query on table with Blob column

You need an index starting with ActLogId.

The indexes with lots of columns are likely to be not very useful.  Recommend getting rid of them, and redesign the indexes.

"Prefix" indexing (PhotoFile(20)) is rarely useful.

Read this cookbook for tips on creating the optimal index for a query.

where PhotoID > 0 AND ActLogId = 20992 needs a composite INDEX(ActLogId, PhotoID), in that order (= first).


Feedback on an entity-relationship diagram for a movie theater business domain

The following are examples used to convey the point, there are multiple instances of each of these examples within your ERD.

Naming

Consistency is key. Some table names are pluralised (Seats) some are not (Ticket). Your table contains data about many tickets so I normally prefer pluralised table names.

You're prefixing every column with the name of the table. This makes for column names like staff_staff_id, which isn't instantly understandable to readers. If I named the primary key of Staff ID then you store staff.id and you might access tickets.staff_id, which makes everything more understandable.

Relations

There's a couple of relations that don't exist, for example you've got seat_id in Ticket (makes sense if you have preassigned seating) but no relation to Seats.seats_id? Why not? If your cinema/theatre doesn't have preassigned seating then you don't need this data.

There are some duplicates within tables. Why does Ticket contain both staff_id and staff_staff_id?

Data duplication

There are some unnecessary (unenforced) relations that are allowing for data inconsistencies. If a MovieTime must happen within a Hall an a ticket must be at a MovieTime then there's no need for Ticket to contain any information about the hall in which the movie is going to be shown.

It's unclear what the difference between the time_of_screening and screening_time is. Maybe one of them is the number of minutes the screening runs for? This information is already within Movie and so is another duplication.

Data

There's no need to persist the age as you've already got the date of birth. I've written about this on Stack Overflow a few times.

It's quite an assumption that all staff and movie names will be 45 characters or less.

I find the inclusion of sex in the staff table a little off-putting. What's the purpose of storing this information? Are the chromosomes of the person selling you a movie ticket that important in this cinema/theatre. I also don't understand why it's 10 characters long.

How to start

Think about exactly what information is required to go within each table. If you're not sure it's required then don't add it in. If you need to add something later then so be it, but you haven't included a lot of unnecessary data. I'd set-up some test tables with dummy data to see if what you're creating will actually work.

Every relation to another table should be enforced. If you've got some unenforced relations then either add a foreign key constraint or remove the column.

Your cinema probably has tickets, so removing the data will prevent you selling tickets.


How to update records of one table with the values of another one (PostgreSQL)

update documentation

UPDATE t1 AS a SET
  account_id = b.account_id
FROM users b
WHERE a.user_id = b.id;

The key is that you need to make sure you have a link between the table you're updating (aliased as "a" in my example) and the table you're using .. which is done via the WHERE clause... a.user_id = b.id


Oracle DBMS Scheduler Email Notifications Dynamically Send Server and Instance Name

SYS_CONTEXT can be used to extract the host name of the database server and the instance name.

You simply call dbms_scheduler.add_job_email_notification in order to add the email notification like the following:

BEGIN
DBMS_SCHEDULER.add_job_email_notification (
  job_name => <JOBNAME>,
  recipients => <EMAIL>,
  subject =>
    SYS_CONTEXT('USERENV','SERVER_HOST') || ' ' ||
    SYS_CONTEXT('USERENV','INSTANCE_NAME') ||
    ' - Scheduler Job Notification-%job_owner%.%job_name%-%event_type%',
  events => 'job_failed');
END;

The 12c documention for all of the SYS_CONTEXT parameters can be found here.


Help writing the following query to relational algebra

This line represents a cartesian product of 3 tables:

FROM student a, enrolled i, class d

After creating a cartesian product you filter out rows that match the predicate.

WHERE a.id= i.student_id
AND i.disc_id = d.id

If you look closer at this query it is equivalent to:

  SELECT a.name, d.name. d.grade
    FROM student a
      JOIN enrolled i ON a.id= i.student_id
      JOIN class d ON i.disc_id = d.id

Maybe you should analyze the second query.


insert multiple authors only if it exists in MySQL

As this is a m:n relationship you'd need an intermediate table that links authors to papers, e.g.:

CREATE TABLE paper_author (
  paper_id INT,
  author_id INT,
  PRIMARY KEY(paper_id, author_id)
);

Then you can check for all authors of a paper with:

SELECT paper.name, author_name
  FROM paper
  JOIN paper_author ON paper.id = paper_author.author_id
  JOIN author       ON paper_authors.author_id = author.id
 WHERE paper.name = ...;

Same for all papers by a specific author with

 WHERE author.name = ...;

Or if you you want a list of authors for each paper on a single row you can use GROUP BY and the GROUP_CONCAT() aggregate function:

SELECT paper.name, GROUP_CONCAT(author_name)
  FROM paper
  JOIN paper_author ON paper.id = paper_author.author_id
  JOIN author       ON paper_authors.author_id = author.id
 GROUP BY paper.name;


Safe online dumping of an InnoDB database in the presence of DDL changes

You could isolate the dump by placing it on a replication slave and stopping the replication before the dump.

As table modification isn't transactional, changing the isolation level won't help.

If you keep the binary logs (and use --master-data) with mysqldump, you could revert to the previous backup and replay the binary logs. This would be immune to the impacts to an single inconsistent dump.


Does blocking always mean open transaction?

No.

If you try the following from two different connections then the second one will be blocked by the first (visible in sys.dm_os_waiting_tasks) but neither will result in any transaction log activity and running DBCC OPENTRAN will report "No active open transactions" (assuming no other activity).

SELECT COUNT_BIG(*)
FROM master..spt_values v1 WITH (TABLOCKX),
     master..spt_values v2 WITH (TABLOCKX),
     master..spt_values v3 WITH (TABLOCKX),
     master..spt_values v4 WITH (TABLOCKX)


You can indicate what language a message body is in, assuming that you know what language it is because the user told you so or because you have a process that reads the message and guesses the language.  Therefore your message table could look something like this:

CREATE TABLE `msg_messages` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `thread_id` int(11) NOT NULL,
  `body` text NOT NULL,
  `priority` int(2) NOT NULL DEFAULT '0',
  `sender_id` int(11) NOT NULL,
  `cdate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `language_id` char(2) NOT NULL,
  FOREIGN KEY (`language_id`) REFERENCES `language` (`language_id`),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

NOTE: Unless you plan on charging a different price in different languages, you don't want to keep the price in the product_translation table.  If you want to differentiate the price based on markets then you are better to split price into its own intersection table between product and whatever is suitable, like country or region or currency.


The procedure will not report if the email is not received. It will be upon you and your support team to troubleshoot why the email was not received. SQL Server just cares about being able to execute the call. Thomas Stringer pointed out sysmail_allitems in a comment and that will show failed status:

USE msdb;
select recipients, sent_status
FROM sysmail_allitems
GO

If it actually failed the sent_status field will show failed

Edit: Found some more stuff. sp_send_dbmail does not provide a function for "feedback" on the success or failure of the email. You can check the status using the sysmail_xxx tables and in the database mail log. The best I have been able to do on an AD exchange server relaying through anonymous SMTP is a failed status in sysmail_allitems and an error msg in the Database mail log stating unknown user

Links Jack Corbett


Could I use a copy of the database directory instead of using initdb?

You can do so - a given initdb will produce a datadir compatible with builds of the same postgres.

But.

If initdb fails, the rest of postgres probably will too. So you won't really be solving anything. You should instead bundle the MSVC redist in your installer.

BTW, if you install PostgreSQL bundled in an app, please use a non-default port, do NOT use the default port 5432. Thanks!


How to get a list of locked accounts / check that account is locked?

As Dba's answer already shows, account status information is accessible via the dba_users view. Connected with a user having the appropriate grants, this can also be used to identify "inactive users":

SELECT username, account_status, created, lock_date, expiry_date
  FROM dba_users
 WHERE account_status != 'OPEN';

tells you for each locked/expired account when it was created, which state it is in (locked, expired, expired and locked, expired(grace)), and what time it was locked or expired. Useful for a "cleanup"; but you should consider that some accounts might just be "data holders" which are never connected to for security reasons, but required either by Oracle itself or your application(s). A good example for those is the system account OUTLN. So take care to only "clean out" accounts you know are not used/needed :)


Copy data between two databases periodically

Assuming you have got 2 Tables with identical Structure in databases

e.g. test and test1 on the same Server

By example:

CREATE TABLE [dbo].[AA](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [varchar](50) NULL,
 CONSTRAINT [PK_AA] PRIMARY KEY CLUSTERED
(
    [ID] ASC
)
) ON [PRIMARY]

GO

You could use a trigger like that on database test1

CREATE TRIGGER [dbo].[tr_AA]
   ON  [dbo].[AA]
   AFTER  INSERT,DELETE,UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    Delete test2.dbo.AA from deleted where test2.dbo.AA.ID=deleted.ID

    Set identity_insert test2.dbo.AA ON
    insert into test2.dbo.AA (ID,NAME) select inserted.ID,Inserted.Name from
    inserted
    left join test2.dbo.AA a on a.ID=inserted.ID
    where a.ID is null
    Set identity_insert test2.dbo.AA OFF

    if exists(select * from deleted)
    Update test2.dbo.AA  Set Name=inserted.Name
    from inserted
    where test2.dbo.AA.ID=inserted.ID

END

Another possibility would be running a job in intervals from the task scheduler using "sqlcmd", for example calling a procedure which was created by you for synchronizing data.

http://www.databasejournal.com/features/mssql/article.php/3654176/SQL-Server-2005-Command-Line-Tool-147SQLCMD148-150-Part-I.htm

If you are using a version with  "SQL Server Agent" (not contained in express versions) you could plan task here as simple query or as dts(x) jobs.


Why are you reinventing the wheel .. as there is a cost to reinvent it !

You are also using deprecated stuff e.g. sysdatabases .. instead use sys.databases.

You should use -

Ola's backup solution
Minionware's backup solution

Both  are free and are well tested by the community.


Database Design: Linking 'vague' entries to associations?

One possibility would be to build a list of the values you currently have, and make users pick from that list, or enter it separately to get it on the list.

I do this all the time in various web interfaces - when I need to pay a bill through my online bank, for example, I first have to go create a payee. I curse this when I am only paying a one-time bill, but I love that I don't have to enter my account number and other details every time I make a car payment, mortgage payment or pay an electric bill - only the first time.

This will also reduce the noise from "almost" duplicates, for example if one person spells it Weber's and another spells it Webbers hopefully the second user will just use the first even if they don't think it's correct (and if they're both wrong you still only have to correct one row).

If you make users see the long-term benefit there should be no screaming about the up-front pain.


SSRS access for AD-trust *groups* not working

I don't have enough reputation to post comments because I'm new here, but this is resolved for me.

I think the root problem is groups are scoped as "domain local" in the foreign AD but that's probably off topic for here.

I created a new group in the developer.local domain and added users from the customer.local domain as members.  This allows the users to access SSRS as expected.


Expand a range columns into a single column

You could use some kind of row generation technique, for example:

select rownum - 1 as q from dual connect by level <= (select max(controlnoto - controlnofrom) + 1 from booklet)

This generates as many rows as the largest range requires, with data 0, 1, 2, ..., starting with 0, so no correction needed, when we add these values to controlnofrom. Now simply join this to your original table and limit the number of rows by controlnoto - controlnofrom, finally add the row number to controlnofrom:

with booklet as
(
  select '20150105' as id, 1 as controlnofrom, 100 as controlnoto from dual union all
  select '20150106', 101, 150 from dual union all
  select '20150107', 151, 225 from dual
)
select
  b.id as id, b.controlnofrom + r.q as controlno
from
  booklet b
  join (select rownum - 1 as q from dual connect by level <= (select max(controlnoto - controlnofrom) + 1 from booklet)) r on (r.q <= b.controlnoto - b.controlnofrom )
order by
  id, controlno
;


issues with encoding and pg_dump/restore between windows and linux

I suppose the dump file you have is encoded in WIN1252 and the target database on Linux has LATIN1 as the server encoding.  That's not going to work, as the error message says.

I suggest you reinitialize the target database using UTF8.

Alternatively, create the dump in UTF8 using the pg_dump -E option.


I am not entirely sure I understood you, but maybe something like this:

select t.id,
       sum(p.dist) as trip_distance,
       array_agg(p.dist) as dist_values,
       array_agg(p.id) as points
from gpspoints p
  join trip t on p.id between t.start_point and t.end_point
group by t.id;

For the following sample data:

create table gpspoints  (id int primary key, dist numeric);
create table trip (id integer primary key, start_point int, end_point int);

insert into gpspoints
values
  (1, NULL),
  (2,  2.3),
  (3,  4.4),
  (4,  1.7),
  (5,  1.2),
  (6,  2.7);

insert into trip
values
  (1, 1, 4),
  (2, 2, 3),
  (3, 3, 5);

The query returns:

id | trip_distance | dist_values        | points
---+---------------+--------------------+----------
 1 |           8.4 | {NULL,2.3,4.4,1.7} | {1,2,3,4}
 2 |           6.7 | {2.3,4.4}          | {2,3}
 3 |           7.3 | {4.4,1.7,1.2}      | {3,4,5}

The columns dist_values and points are just there for debugging purposes to validate that the correct points were included.


DR option for SharePoint databases? ideas and experiences

In terms of wanting to get input from other people about their experiences, I can share about our use of Log Shipping in our Production environment.  We use native Log Shipping and perform DR tests once a quarter for our critical systems.  This involves failing over to the Secondary, pointing the application(s) at it, and testing that everything works as expected.  We then fail back to the Primary and ensure things are ready for our end users again.

By repeatedly testing our DR process we've been able to make some hard-earned improvements.  The solution we use for helping perform the failover and failback procedures is here.


How to use "regular expression" to separate specific strings in Oracle

Simple method:

col field1 format a6
col field2 format a6
col lvl format a3
variable B1 varchar2(32);
exec :B1 := '1:30,2:4,52:0,8:1';
select
  regexp_substr(regexp_substr(:B1, '[^,]+', 1, level), '[^:]+', 1) field1,
  regexp_substr(regexp_substr(:B1, '[^,]+', 1, level), '[^:]+', 2) field2,
  level lvl from dual
connect by regexp_substr(:B1, '[^,]+', 1, level) is not null;

FIELD1 FIELD2 LVL
------ ------ ---
1      30       1
2      4        2
52     2        3
8      1        4

Or with fewer regexp_substr calls:

col field1 format a6
col field2 format a6
col lvl format a3
variable B1 varchar2(32);
exec :B1 := '1:30,2:4,52:0,8:1';
select
  regexp_substr(:B1, '[^:,]+', 1, level*2 - 1 ) field1,
  regexp_substr(:B1, '[^:,]+', 1, level*2) field2,
  level lvl from dual
connect by regexp_substr(:B1, '[^,]+', 1, level) is not null;

FIELD1 FIELD2 LVL
------ ------ ---
1      30       1
2      4        2
52     0        3
8      1        4


Finding exact row in Clustered index leaf page

The specifics of how SQL Server does it is not public.

But I recommend you read the vast research material on the subject which is public. Modern B-Tree Techniques is a good start. Many more papers from Goetz Graefe talk about this area, he is well known in the industry.

Interpolation search is a well known technique in B-Trees and there is plenty of material detailing possible approaches (bear in mind that none of the papers describe explicitly how SQL Server does it and there are many ways to skin a cat).


Dropping a constraint (index) on a column

You can use sys.indexes view to get indexes. You can join this table to sys.tables, sys.columns and sys.index_column to get informations to create a dynamic drop index. You can use this simple select to generate your drop index. You can use the where clause to filter the tables and the columns. If you want to change Table1.Column1 you must use those name to filter the select to get the right delete statement

use [testdb]
go
declare @sqlDropIndex NVARCHAR(1000)

select @sqlDropIndex = 'DROP INDEX ' + idx.name + ' ON ' + tbl.name
from sys.indexes idx inner join
        sys.tables tbl on idx.object_id = tbl.object_id inner join
        sys.index_columns idxCol on idx.index_id = idxCol.index_id inner join
        sys.columns col on idxCol.column_id = col.column_id
where idx.type <> 0 and
        tbl.name = 'MyTableName' and
        col.name = 'MyColumnName'
group by idx.name, tbl.name
order by idx.name desc

print @sqlDropIndex
--exec sp_executeSql @sqlDropIndex
go

Hope this will help you


sqlcmd returning NUL instead of NULL for tinyint column

sqlcmd only displays as many characters in a column as the width of the column data or the width of the column name, whichever is longer. You can expect the same behavior with the following data types:

bit
tinyint
decimal with precision < 2
varchar with length < 4

To work around the issue you can either cast the column to a longer data type or rename the column to a longer name.


CREATE Windows User in database for not existing domain

No, it isn't possible. SQL needs to contact the DC to collect the user/group SID and needs to save it inside the sys.server_principals table in master database.


You need to enable sharding on the database test first:

sh.enableSharding("test");

Then you need to shard the collection and pick a shard key.  Based on what you were trying to split on, that would be:

sh.shardCollection("test.zp", {"city" : 1});

A couple of notes:

"city" is probably a poor shard key unless you are expecting an even distribution of records per city and you are going to have a lot of them.  Take a look at the docs on picking a key
If you have data in the collection already, you will need to create an index on the "city" field before you can use it as a shard key.  If the collection is empty, MongoDB will do it for you automatically when you use it as a shard key.


MySQL Query Cache - what type of queue is used?

According to the manual, the query cache removes the Least Recently Used (LRU) Query from the Query Cache when the Query Cache is full. Read the general chapter on the MySQL Query Cache for a more detailed description of the logic and architecture of the Query Cache.


Monitor SQL Server stored procedure latency

Stored procedure execution statistics are surfaced via the sys.dm_exec_procedure_stats dynamic management view.

This query will show stats for stored procedures in the current database:

SELECT ObjectName = s.name + N'.' + o.name
    , sps.last_execution_time
    , sps.execution_count
    , sps.last_elapsed_time
    , sps.total_elapsed_time
    , avg_elapsed_time = sps.total_elapsed_time / sps.execution_count
FROM sys.dm_exec_procedure_stats sps
    INNER JOIN sys.objects o ON sps.object_id = o.object_id
    INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE sps.database_id = DB_ID()
ORDER BY s.name
    , o.name;

The output looks like:

╔══════════════════════════════════╦═════════════════════════╦═════════════════╦═══════════════════╦════════════════════╦══════════════════╗
║            ObjectName            ║   last_execution_time   ║ execution_count ║ last_elapsed_time ║ total_elapsed_time ║ avg_elapsed_time ║
╠══════════════════════════════════╬═════════════════════════╬═════════════════╬═══════════════════╬════════════════════╬══════════════════╣
║ dbo.procAccessUniqueID           ║ 2018-09-07 13:21:04.403 ║          173346 ║               614 ║          223886470 ║             1291 ║
║ dbo.procAddBatchClassFilter      ║ 2018-09-04 15:25:22.150 ║              43 ║              5124 ║             171329 ║             3984 ║
║ dbo.procAddBatchFilter           ║ 2018-09-06 15:04:34.817 ║              17 ║               251 ║              18767 ║             1103 ║
║ dbo.procAddLock2                 ║ 2018-09-07 13:21:05.047 ║          373086 ║               912 ║          542911271 ║             1455 ║
║ dbo.procAddTempUser_V10          ║ 2018-09-07 10:50:13.973 ║               4 ║               292 ║               1223 ║              305 ║
║ dbo.procAddUserGroupRelationship ║ 2018-09-07 13:14:49.533 ║            1124 ║                70 ║             151824 ║              135 ║
╚══════════════════════════════════╩═════════════════════════╩═════════════════╩═══════════════════╩════════════════════╩══════════════════╝

The avg_elapsed_time, measured in microseconds, shows the average elapsed time for completed executions of each stored procedure listed.


Database structure for users, events and event lists

Based on your comment above, it sounds like when you said everything was stored in separate "databases", you actually meant to say "tables". I'm going to work with that assumption...

I'd suggest a simple table that stores who's been invited to which event, and what their response was.

invitations
-----------
  id (primary key for the table)
  user_ID (foreign key to users.ID)
  event_ID (foreign key to events.ID)
  response (could be a simple string, or an ID to a table of possible responses, such as "attending", "not attending", "unsure"...)
  date_sent (a date showing when the invitation was sent)
  respond_by_date (date that a user must response by)
  mode_sent (was the invitation sent by email, text message, other electronic message, regular mail, semaphore flags, etc...)

The above structure will keep track of which user was invited to which event. It will also keep track of the user's response. Other fields such as date_sent aren't necessary, but could be useful for other purposes. Data for this might look like:

invitations
-----------
id   |  user_id  | event_id   | response  |  date_sent  | respond_by_date  | mode_sent
--------------------------------------------------------------------------------------
1    | 123       | 5          | attending |  2014-12-01 | 2014-12-05       | email
2    | 456       | 5          | not attnd |  2014-12-01 | 2014-12-05       | email
3    | 456       | 7          | attend+1  |  2015-01-01 | 2014-12-02       | semaphore flags

(This assumes that the user and event tables already exist)

Would this work for your purposes?