Where to look for possible deadlock causes?

Seem to have resulved the issue adding a SELECT FOR UPDATE to pre-lock the affected rows, as follows:

CREATE OR REPLACE FUNCTION telemetria_data.insert_tabla_instantaneo(
    bigint,
    timestamp without time zone,
    integer,
    double precision[],
    integer,
    double precision)
  RETURNS boolean AS
$BODY$
DECLARE
    imei ALIAS FOR $1;
    utc ALIAS FOR $2;
    clase ALIAS FOR $3;
    valor ALIAS FOR $4;
    power ALIAS FOR $5;
    voltaje ALIAS FOR $6;
    num_rows int;
dummy INTEGER;
BEGIN

    SELECT 1 into dummy FROM telemetria_data.instantaneo a WHERE a.imei=$1 and a.clase=$3 FOR UPDATE;
    EXECUTE 'UPDATE telemetria_data.instantaneo SET utc = ''' || utc || ''', registro=now(), power='|| power ||', voltaje = '|| voltaje ||', valor =''{' || array_to_string(valor,',')  ||'}''::double precision [] WHERE imei='|| imei ||' and clase=' || clase || ';';

    GET DIAGNOSTICS num_rows = ROW_COUNT;

    IF num_rows > 0 THEN
    RETURN TRUE;
    ELSE

    EXECUTE 'insert into telemetria_data.enabled_units (imei,clase) values ('|| imei ||','|| clase ||');';
    EXECUTE 'insert into telemetria_data.instantaneo (imei,utc,clase,valor,registro,power,voltaje) values ('|| imei ||',''' || utc || ''','|| clase ||',''{' || array_to_string(valor,',')  ||'}''::double precision [],now(),'|| power ||','|| voltaje ||');';

    RETURN TRUE;
    END IF;

    EXCEPTION
    WHEN UNIQUE_VIOLATION THEN
        EXECUTE 'insert into telemetria_data.instantaneo (imei,utc,clase,valor,registro,power,voltaje) values ('|| imei ||',''' || utc || ''','|| clase ||',''{' || array_to_string(valor,',')  ||'}''::double precision [],now(),'|| power ||','|| voltaje ||');';
        RETURN TRUE;

    WHEN foreign_key_violation THEN
        RETURN FALSE;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

There are not that many deadlocks, but they are still appearing as you can see in the following graph:


Just add a WHERE condition.  And if you are only displaying one value, RUNNING, then there is no reason for a CASE. Otherwise you will want to evaluate each condition in the CASE including what should display in the event none of the conditions is met; a default value.

So here is the code to your original question:

SELECT 'RUNNING' AS A
from message_log
where  Message = 'BUILD'
    AND Log = 'Day Start'
order by 1;

And here is the code for a multi-condition CASE:

SELECT CASE
    WHEN (Log = 'Day Start') THEN 'RUNNING'
    WHEN (Log = 'Day End')   THEN 'NOT RUNNING'
    ELSE 'UNKNOWN' END AS A
from message_log
    where  Message = 'BUILD'
order by 1;


What is wrong with the following Python code -- trying to insert into a database?

ok this is my table:

mysql> desc name_test;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| fname | varchar(20) | NO   | PRI | NULL    |       |
| sname | varchar(20) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

and this is the code to insert the data:

    import mysql.connector

    db = mysql.connector.connect(host="127.0.0.1", # your host, usually localhost
             user="root", # your username
              passwd="", # your password
              db="music")# name of the data base
    cur=db.cursor()

    q = "insert into name_test(fname,sname) values(%s, %s)"
    v = { "bob", "smith" }

    cur.execute(q,v) #runs the query
    db.commit() #ensures that its saved
    cur.close() #closes the cursor
    db.close() #closes the connection

source: https://dev.mysql.com/doc/connector-python/en/connector-python-example-cursor-transaction.html

hope this helps :)


Storing arrays of integers in database (for efficient select)

RDBMs were built to handle these kinds of relations and operations. Plus, storing the users and items separately will help with detailed reporting (eg: number of items added today).

With optimized indexes, reading the many items per user is just one lookup away. The items table can be indexed to store the related items (per user) consecutively, thereby giving a array like reading scenario. Just make sure to use page padding to allow for the future updates/inserts/deletes.

RDBMs are pretty fast and can handle hundred-thousand operations per second, so this may not be an issue. When your items are on a different table, updates done per item will perform better than if the items were stored in a text column (as an array) in the same rows as the user. This will give you faster updates, lesser memory required for the operation, quicker locks and releases (and you may not have to lock your respective user), smaller read/writes, etc...


with
cte1 as
(
    select *, row_number() over(order by Ad) RowNumber
    from #Personel
),
cte2 as
(
    select *, row_number() over(order by newid()) RowNumber
    from #Masa
)
select cte1.Ad, cte2.MasaAdi
from cte1
    join cte2 on
        cte1.RowNumber = cte2.RowNumber


The normal forms don't really have anything to do with many-to-many relationships.  If you happen to lose some as a byproduct of the normalization process, that's fine, but you won't generally do so.  If we consider that we have two tables: Salesman and Product which each have ID fields as their primary key and we have a third table called Specializes which shows which Salesmen specialize in selling which products.  This Specializes table would represent the many-to-many relationship since each salesman can specialize in multiple products and each product can be specialized in by more than one salesmen.  It would probably look something like this (excuse the awkward formatting, we can't do real tables on StackExchange):

| SalesmanID | ProductID |
|------------|-----------|
|  1         |  1        |
|  1         |  2        |
|  2         |  1        |
|  2         |  2        |
|  2         |  3        |
|------------|-----------|

Obviously, the lack of nulls and repeated rows means that this table is in 1NF.  In this table, the only candidate key is {SalesmanID, ProductID} and as such, there are no non-prime attributes.  It also contains no non-trivial functional dependencies.  Thus, it is necessarily in 2NF, 3NF, and BCNF.  I'm also going to assert without proof that it's in 4NF, 5NF, 6NF, and DKNF (to avoid having to explain all of the details thereof).  So really, no normal form removes many-to-many relationships, nor are they meant to.  The purpose of normal forms is not to remove many-to-many relationships (and I am not actually clear on why you would want to) but rather to remove potential insert anomalies, update anomalies, and deletion anomalies.  The primary role of the normal forms is to ensure that each piece of information is represented in a database table precisely once.  Having the same information embedded in multiple places leads to problems.  But that has nothing to do with many-to-many relationships.

I think that you mean to be asking a slightly different question about something like situations where a many-to-many relationship is embedded in a table which also tries to contain other information, such as if the table above contained the product name in addition to the product number (where name is functionally determined by number).  A table like that would either violate 2NF (if the name did not also functionally determine the number) or Boyce Codd Normal Form (if the name did functionally determine the number).

You could also perhaps be thinking of a different situation: when we have two unrelated 1:M relationships in the same table, such as if we were to add a third column to identify which language or languages each salesman speaks.

| SalesmanID | ProductID | Language |
|------------|-----------|----------|
|  1         |  1        | English  |
|  1         |  2        | English  |
|  2         |  1        | Spanish  |
|  2         |  2        | Spanish  |
|  2         |  3        | Spanish  |
|  2         |  1        | French   |
|  2         |  2        | French   |
|  2         |  3        | French   |
|------------|-----------|----------|

As you can see, that table is quite problematic, since we need 6 entries to express that Salesman 2 specializes in 3 products and speaks 2 languages.  This is a fourth normal form violation.

Edit:

Upon clarification, it's clear that what he's asking about is a table like the Specializes table, but with extra information about the salesmen and the products, essentially, a table which contains two entity sets and their many-to-many relationship in a single table.  So to answer that question directly, yes, you can have lousy tables like that which are in 3NF.  The normal form which guarantees that that won't happen is Boyce-Codd Normal Form (BCNF).  Here's an example of a lousy table like that which is vulnerable to all kinds of anomalies (insert, update, and delete), but is in 2NF and 3NF.

| SalesmanName | SalesmanID | ProductID | ProductName |
|--------------|------------|-----------|-------------|
|   Alex       |  1         |  1        |  Thingy     |
|   Alex       |  1         |  2        |  Whatsit    |
|   Barb       |  2         |  1        |  Thingy     |
|   Barb       |  2         |  2        |  Whatsit    |
|   Barb       |  2         |  3        |  Whoosit    |
|--------------|------------|-----------|-------------|

So, looking at this table, it's obviously in 1NF.  Further, we can identify the non-trivial functional dependencies very straightforwardly.  SalesmanName -> SalesmanID.  SalesmanID -> SalesmanName.  ProductID -> ProductName.  ProductName -> ProductID.  Next we need to identify the candidate keys.  There are four: {SalesmanName,ProductID}, {SalesmanName,ProductName}, {SalesmanID, ProductID}, and {SalesmanID, ProductName}.  As such, we have no non-prime attributes.  Thus, we are necessarily in 2NF (no functional dependencies between non-prime attributes) and 3NF (no non-trivial functional dependencies where the left-hand-side is not a super key and the right hand side contains a non-prime attribute).  However, we are not in BCNF because there do exist non-trivial functional dependencies whose left-hand-side is not a superkey.

Any similar situation will also always not be in Boyce-Codd Normal form because there will be some non-trivial functional dependency whose left-hand-side is not a superkey.  Any table like this will essentially have two entity sets each of whom have some attributes.  Basically, it will have a left entity set and a right entity set.  The left entity set will have some attributes which uniquely identify each left entity and the right entity set will have some attributes which uniquely identify each right entity.  Those will be involved in functional dependencies.  However, they will each not be candidate keys because you'll have to combine them to get a candidate key for the whole table.  As such, they won't be superkeys and there will be a Boyce-Codd Normal Form violation.  So BCNF will stop it cold.  Anything less than that will only catch some cases.  Really, if you only remember one normal form, it should be BCNF.


Designing MySQL Database For Two Different User Types (Staff and Client)?

I think your problem is that you are focusing to much on performance. From the description it seems like the user table will be rather small (say, less than a million users).

Instead you should focus on the functionality you want from the system. There are questions you should ask and design for such as:

Can a user be both client and staff?
Can a user be user for several companies?
Can a user have several adresses, say a business adress and a home adress?
Do you need to have "time based changes", say that a client should be made active (or inactive) at a future date?
Do you need to store change information (who and when information was changed)?
Will you need to support several different login schemas (say through facebook or using some kind of one-time-password)?

My take on this is that I would have one table describing a login user and a second table describing a users role. When a user has successfylly logen in I would check if the users has several roles, and the user would be required to select role: staff or user for one company. This would allow the login to be for one physical person, over time it could evolve to include OTP or challenge response or what is deemed necessary. The user would at any time have zero, one or more selectable roles.

Edit (added): If the user has only one role, there is no need to show any selection. Even if a user has no role right now the system might allow changing adress, mail adress and so on. Role based might allow a user to be administrator for the users at one company. End edit.

In the role table I would have start and stop dates. If necessary from a performance view it would be possible to later add an "active" field but that would be an optimization which I would not add until necessary.


What is the best way to find out how many records do not have a proper phone number in SQL Server?

It is one time task.
Write a small application or just select script, use C#, VB.NET, T-SQL...and analyze all patterns manually.
Maybe you will find out common patterns by operators who inputed this data. Add an algorithm for every pattern. Apply your patterns to real data in a database. Remove all "wrong" records.

Luck.

ADDED:

As option, you can use database of telephone numbers to check person2number validity if it's possible.


MySQL: Foreign key constraint on composite key with the referenced table a function of a constituent column of the key

Maybe it's not exactly what you expected but you can try such table structure:

CREATE TABLE customFields (
    id INT UNSIGNED NOT NULL
    ,type VARCHAR(15) NOT NULL -- it would be better to use bit
    ,foreignIdMultipleChoice INT NULL
    ,foreignIdText INT NULL
    ,foreignId INT AS (IFNULL(foreignIdMultipleChoice, foreignIdText))
    ,CONSTRAINT FK_foreignIdMultipleChoice FOREIGN KEY (foreignIdMultipleChoice)
        REFERENCES customFieldsMultipleChoice(id)
    ,CONSTRAINT FK_foreignIdText FOREIGN KEY (foreignIdText)
        REFERENCES customFieldsText(id)
    )

You will need to insert foreignId value into one of these columns (foreignIdMultipleChoice or foreignIdText) depending on the field source. If you are on latest version of MySQL you can add check constraint to avoid issues when both of these columns are nulls or both of them are not null simultaneously. Something like this:

,CONSTRAINT `foreignId_check` CHECK ((foreignIdMultipleChoice > 0 AND foreignIdText IS NULL)
                                      OR (foreignIdMultipleChoice IS NULL AND foreignIdText > 0))


Use of Shrink after rebuilding Indexes in sql server 2005

Paul Randal answers your question here:

  Data file shrink should never be  part of regular maintenance, and you
  should NEVER, NEVER have auto-shrink enabled. I tried to have it
  removed from the product for SQL 2005 and SQL 2008 when I was in a
  position to do so - the only reason it's still there is for backwards
  compatibility. Don't fall into the trap of having a maintenance plan
  that rebuilds all indexes and then tries to reclaim the space required
  to rebuild the indexes by running a shrink - that's a zero-sum game
  where all you do is generate a log of transaction log for no actual
  gain in performance.

  So what if you do need to run a shrink? For instance, if you've
  deleted a large proportion of a very large database and the database
  isn't likely to grow, or you need to empty a file before removing it?

  The method I like to recommend is as follows:

  Create a new filegroup
  Move all affected tables and indexes into the new filegroup using the CREATE INDEX ... WITH (DROP_EXISTING) ON  syntax, to move the tables and remove fragmentation from
  them at the same time
  Drop the old filegroup that you were going to shrink anyway (or shrink it way down if its the primary filegroup)


The command rpm -qa shows what is installed. If it does not give what you look for then it is not installed with yum.


It seems that you would like to reference ZipCode in the OUTER query from the scalar subquery. You cannot reference the alias z since it has only just been defined in the same SELECT clause. The correct way to do this is just to use the base column, but to alias the table differently to prefix the column names with the aliases.

select a.ZipCode as z,
  (select AVG(b.Latitude)
     from tlkp_ZipCodes b
    where b.ZipCode like LEFT(a.ZipCode, 4) + '%' ) AvgLatitude
 from tlkp_ZipCodes a


Sorting a list of records alphabetically: is there any reason NOT to let the database do it

This is a way to broad question to give a detailed answer. However, there is one general rule that holds true in development:

  Don't reinvent the wheel

That is, unless you have a good reason. In the case of sorting, the developers of the database engine spend a lot more time writing an efficient sorting algorithm than you will be able to. So unless you need to sort by something like a field that is encrypted and cannot be decrypted on the database server, I would always sort on the database side.

However, if you have changing sorting requirements, like a display were the user selects which column to sort by, you are better of getting the data once and then sorting it in memory. The alternative of transferring the data again and again every time the sort order changes might be just too chatty on the network. Also a fully flexible query tends to be hard to optimize by the rdbms.

But then, if you are returning only the top few rows, sorting on the database site becomes a lot faster again, as the amount of data going over the network is greatly reduced.

That really brings us to the second general rule in database development:

  It depends.

It hope this was at least a little helpful. For a more detailed answer please give us more detail on your actual problem.


A common way to do this in SQL Server is to use a Recursive CTE:

Here's your test data as an insert:

CREATE TABLE #yourmom
(
    custid INT,
    custname VARCHAR(10),
    deptid VARCHAR(3),
    company VARCHAR(10),
    parentcustid INT,
    enrolled BIT
)

INSERT #yourmom ( custid, custname, deptid, company, parentcustid, enrolled )
SELECT x.custid, x.custname, x.deptid, x.company, x.parentcustid, x.enrolled
FROM   ( VALUES ( 7060, 'Sally   ', 'AB1', 'comp1 ', NULL, 1 ),
                ( 6953, 'Ajit    ', 'AB7', 'comp2 ', 7060, 1 ),
                ( 6957, 'Rahul   ', 'DE1', 'comp3 ', 7060, 1 ),
                ( 6958, 'uday    ', 'TG6', 'comp4 ', 7060, 1 ),
                ( 6959, 'john    ', 'HY7', 'comp5 ', 7060, 1 ),
                ( 6960, 'netaji  ', 'HY5', 'comp6 ', 7060, 1 ),
                ( 6961, 'prakriti', 'GT6', 'comp7 ', 7060, 1 ),
                ( 6962, 'sachin  ', 'KL7', 'comp8 ', 7060, 0 ),
                ( 6963, 'santosh ', 'KK5', 'comp9 ', 7060, 1 ),
                ( 6964, 'Ravi    ', 'PP0', 'comp10', 7060, 1 )
        ) AS x ( custid, custname, deptid, company, parentcustid, enrolled );

And here's how a Recursive CTE would look for it:

WITH yourdad
AS ( SELECT y.custid, y.custname, y.deptid, y.company, y.parentcustid, y.enrolled
     FROM   #yourmom AS y
     WHERE  y.parentcustid IS NULL
            AND y.enrolled = 1
     UNION ALL
     SELECT ym2.custid, ym2.custname, ym2.deptid, ym2.company, ym2.parentcustid, ym2.enrolled
     FROM   yourdad AS yd
     JOIN   #yourmom AS ym2
     ON ym2.parentcustid = yd.custid
        AND ym2.enrolled = 0 )
SELECT *
FROM   yourdad;

There are some Great Posts® written about them:

Adam Machanic
Jeff Moden #1 and #2
The late great Dwain Camps

Hope this helps!


I would say "is unique" is an operator that returns true if, and only if. the tested SQL relation does not contain any duplicate tuples.

For context, this is question 11 in this quiz, and Q54 in GATE 2014 Paper 2 (pdf).

Answer D is therefore incorrect, because it returns an empty (null) result if R contains any duplicate tuples.


To generate a script to drop or map the orphan users in a SQL Server Instance

The dbatools module has already solved this for you.

Get-DbaDbOrphanUser - reports all users in one or more databases with no corresponding login
Repair-DbaDbOrphanUser - finds orphan users and if there is a login with the same name, it will remap them.
Remove-DbaDbOrphanUser - locates orphaned users and drops them from the database (you can do this with Repair-DbaDbOrphanUser -RemoveNotExisting as well).


How do I track logins, connections, user accounts, queries, ...?

I would suggest you to run a server side trace - for your entire business life cycle. There might be cases where some reports are ran end of month or end of quarter.

So you will need below event in a server side trace 11,13,16,19,33,37,61,162 with these columns 1,6,8,10,11,12,14,27,35,40.

You can filter out stuff that you dont need using sp_trace_setevent (note that it is announced deprecated).

Alternatively you can use Extended events - Tracking SQL Server Database Usage.

Note: Currently in my environment, I am using Server side trace and its a very low impact. Also, I have a job that dumps the profiler info into a table for analysis.

You can have below table structure for loading trace data :

CREATE TABLE [dbo].[login_trace](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [ServerName] [varchar](300) NULL,
    [HostName] [varchar](300) NULL,
    [ApplicationName] [varchar](300) NULL,
    [DatabaseName] [varchar](200) NULL,
    [LoginName] [varchar](100) NULL,
    [StartTime] [datetime] NULL
) ON [PRIMARY]

You can adjust it as per your requirement.


To upgrade to MongoDB 3.0 all members of a replica set must be running the previous major release (MongoDB 2.6). See: Upgrade a replica set to 3.0.

A replica set configuration mixing 2.4 and 3.0 nodes is not supported or tested and will likely lead to unexpected errors. Since you have 1.7TB of data, I would definitely follow the recommended upgrade instructions in the MongoDB documentation and upgrade all members one major release series at a time (2.4 => 2.6, then 2.6 => 3.0).

Since it sounds like you are starting from a single node, it would be more straightforward to:

Review the driver & server upgrade notes and test driver compatibility for your target MongoDB server release. Drivers generally have a broader range of compaibility and should be upgraded first. There may be driver API changes required given the age of your current server version.
Take a backup of your MongoDB instance (perhaps using filesystem snapshots given your data size).
Upgrade your mongod as a standalone (2.4 => 2.6, 2.6 => 3.0) until you reach your destination release.
Convert your standalone to a replica set. When you add secondary members, this would be good timing to switch to the WiredTiger storage engine.

Ideally you should upgrade to a supported version of MongoDB (currently 3.4 or newer, although 3.4 will reach EOL in Jan 2020). Changes in successive releases have improved initial sync and 3.4 would be a much better starting point. MongoDB 3.4 also includes optional network compression for communication between mongod instances (which is enabled by default in 3.6+).


Unique identifier field with a conditional

You could use a key table to store the incrementing part of your second ID column.  This solution does not rely on any client-side code, and is automatically multi-year aware; when the @DateAdded parameter passes in a previously unused year, it will automatically begin using a new set of values for the ID2 column, based on that year.  If the proc is consequently used to insert rows from prior years, those rows will be inserted with "correct" values for the increment.  The GetNextID() proc is geared to handle possible deadlocks gracefully, only passing an error to the caller if 5 sequential deadlocks occur when trying to update the tblIDs table.

Create a table to store one row per year containing the currently-used ID value, along with a stored procedure to return the new value to use:

CREATE TABLE [dbo].[tblIDs]
(
    IDName nvarchar(255) NOT NULL,
    LastID int NULL,
    CONSTRAINT [PK_tblIDs] PRIMARY KEY CLUSTERED
    (
        [IDName] ASC
    ) WITH
    (
        PAD_INDEX = OFF
        , STATISTICS_NORECOMPUTE = OFF
        , IGNORE_DUP_KEY = OFF
        , ALLOW_ROW_LOCKS = ON
        , ALLOW_PAGE_LOCKS = ON
        , FILLFACTOR = 100
    )
);
GO

CREATE PROCEDURE [dbo].[GetNextID](
    @IDName nvarchar(255)
)
AS
BEGIN
    /*
        Description:    Increments and returns the LastID value from
                        tblIDs for a given IDName
        Author:         Max Vernon / Mike Defehr
        Date:           2012-07-19

    */
    SET NOCOUNT ON;

    DECLARE @Retry int;
    DECLARE @EN int, @ES int, @ET int;
    SET @Retry = 5;
    DECLARE @NewID int;
    WHILE @Retry > 0
    BEGIN
        SET @NewID = NULL;
        BEGIN TRY
            UPDATE dbo.tblIDs
            SET @NewID = LastID = LastID + 1
            WHERE IDName = @IDName;

            IF @NewID IS NULL
            BEGIN
                SET @NewID = 1;
                INSERT INTO tblIDs (IDName, LastID)
                VALUES (@IDName, @NewID);
            END
            SET @Retry = -2; /* no need to retry since the
                                  operation completed */
        END TRY
        BEGIN CATCH
            IF (ERROR_NUMBER() = 1205) /* DEADLOCK */
                SET @Retry = @Retry - 1;
            ELSE
                BEGIN
                SET @Retry = -1;
                SET @EN = ERROR_NUMBER();
                SET @ES = ERROR_SEVERITY();
                SET @ET = ERROR_STATE()
                RAISERROR (@EN,@ES,@ET);
                END
        END CATCH
    END
    IF @Retry = 0 /* must have deadlock'd 5 times. */
    BEGIN
        SET @EN = 1205;
        SET @ES = 13;
        SET @ET = 1
        RAISERROR (@EN,@ES,@ET);
    END
    ELSE
        SELECT @NewID AS NewID;
END
GO

Your table, along with a proc to insert rows into it:

CREATE TABLE dbo.Cond
(
    CondID INT NOT NULL
        CONSTRAINT PK_Cond
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , CondID2 VARCHAR(30) NOT NULL
    , Date_Added DATE NOT NULL
);

GO
CREATE PROCEDURE dbo.InsertCond
(
    @DateAdded DATE
)
AS
BEGIN
    DECLARE @NextID INT;
    DECLARE @Year INT;
    DECLARE @IDName NVARCHAR(255);
    SET @Year = DATEPART(YEAR, @DateAdded);
    DECLARE @Res TABLE
    (
        NextID INT NOT NULL
    );
    SET @IDName = 'Cond_' + CONVERT(VARCHAR(30), @Year, 0);
    INSERT INTO @Res (NextID)
    EXEC dbo.GetNextID @IDName;

    INSERT INTO dbo.Cond (CondID2, Date_Added)
    SELECT CONVERT(VARCHAR(30), NextID) + '/' +
        SUBSTRING(CONVERT(VARCHAR(30), @Year), 3, 2), @DateAdded
    FROM @Res;
END
GO

Insert some sample data:

EXEC dbo.InsertCond @DateAdded = '2015-12-30';
EXEC dbo.InsertCond @DateAdded = '2015-12-31';
EXEC dbo.InsertCond @DateAdded = '2016-01-01';
EXEC dbo.InsertCond @DateAdded = '2016-01-02';

Show both tables:

SELECT *
FROM dbo.Cond;

SELECT *
FROM dbo.tblIDs;

Results:

The key table and stored proc come from this question.


SQL To Fetch Partition Boundaries and Filegroups

This relationship isn't altogether intuitive but I think this query will get you what you need:

SELECT
  PScheme       = ps.name,
  PFunction     = pf.name,
  Boundary      = dds.destination_id,
  BoundaryValue = prv.value,
  [Filegroup]   = fg.name
FROM sys.partition_schemes AS ps
INNER JOIN sys.partition_functions AS pf
        ON ps.function_id = pf.function_id
INNER JOIN sys.destination_data_spaces AS dds
        ON ps.data_space_id = dds.partition_scheme_id
INNER JOIN sys.filegroups AS fg
        ON dds.data_space_id = fg.data_space_id
LEFT OUTER JOIN sys.partition_range_values AS prv
        ON dds.destination_id = pf.boundary_value_on_right + prv.boundary_id
       AND prv.function_id = pf.function_id;

And because LEFT/RIGHT is always a tedious mental exercise for me, it might be that you need to flip the boundary_value_on_right part on the second-last line:

ON dds.destination_id = prv.boundary_id + ABS(1-pf.boundary_value_on_right)

You don't need to have created any tables.


Multitenant application: several tables or several IDs?

The best approach is a mixture of allowing multiple databases and having a master identifier within the table.

This allows you to host multiple smaller customers in one database, but also to use multiple databases (so you can scale). Because there may be a point where you want to have more data than you can handle in one database.


Oracle 12.1 optimize_adative_features = FALSE ; using new statistics

No, it just means that the Oracle optimizer won't use

  adaptive features, including adaptive plan (adaptive join methods and bitmap pruning), automatic re-optimization, SQL plan directives, and adaptive distribution methods

(quoting from the URL @JSapkota mentioned in the comments)

Oracle will still use all the information gathered via dbms_stats.gather_schema_stats -- but unless you are experiencing problems with the default value of true -- why not take advantage of those additional features?  As a side note, Oracle will, with a default installation, automatically gather statistics daily on tables that have changed enough via inserts, updates, and deletes, where "enough" is roughly 20% of the data; so you'll seldom need (or want to) manually gather statistics for an entire schema.

You may want to gather statistics manually on a particular table or set of tables after a bulk load, etc., but then just on the affected tables, and if you don't want to wait for the daily statistics gathering to do its thing.

In short, any of the Oracle dbms_stats.gather_XXX functions will provide the needed information for the optimizer.  The only time you may want to gather statistics for an entire schema is perhaps after importing a schema into a database, after a mass update of many tables in the schema, etc.


Mysql - optimal indexing for query using IN operator

The best index in case above seems to be

ALTER TABLE table1 ADD INDEX IDX_bar_foo (bar,foo);

  Why is IDX_bar chosen but not IDX_foo or IDX_foo_bar?

What index will be used and will some index be used at all, is dependent by table statistic (and query text, of course).

Maybe in Your case the index IDX_bar is more selective than another 2 indexes.

  Does MySQL choose index differently depend on the data at the time?

That's right. Change data changes statistic and so index choose can change too.

  Should I only have IDX_bar and remove the other two?

For optimizing the query You show - yes (if data stat will not change radically). Or remove all 3 indexes and create index shown above.


MySQL DELETE becomes exceptionaly slow with a large number of rows

With InnoDB, deleted rows must be held in case there is a crash or other reason to rollback the action.  This is costly, and may get more costly with larger counts, as you are seeing.

Plan A:  Delete in smaller chunks and COMMIT after each chunk.  (Similarly, chunking should be applied to Updates.)

Plan B, C, D, ...  See more tips

If no WHERE

No WHERE?  It would be much better to create a new table, then use RENAME TABLE to atomically swap the new one into place.  And finish with DROP TABLE.  No down time; no costly delete.

Query rewrite

If you are running 5.7, there is a new feature there which lets you "rewrite" queries to accommodate various kinds of nasties.  More discussion.  You could probably turn a delete without a where into DROP and CREATE.  (This is not quite as good as the previous suggestion.)


Check and count if a column value is used in another column of same table

SELECT t1.id,
       MAX(CASE WHEN t1.`check` THEN t1.modified_date END) modified_date,
       COUNT(DISTINCT t2.id) children_count,
       1 `check`
FROM test t1
LEFT JOIN test t2 ON t1.id = t2.parent_id
GROUP BY t1.id
HAVING modified_date IS NOT NULL;

fidddle


db.serverStatus() got not authorized on admin to execute command

You need to grant the clusterMonitor role to the user running  the db.serverStatus command. You can grant this role to a user by calling the `grantRolestoUser.

Here's a list of other clusterMonitor roles

use admin

db.createUser(
    {
      user: "clustermonitor",
      pwd: "password",
      roles: [
         { role: "readWrite", db: "admin" }
      ]
    }
);

db.grantRolesToUser(
  "clusterMonitor",
   [
     { role: "clusterMonitor", db:"admin"}
   ]
);


Sending mail using DatabaseMail in SQL Server 2008

Depending on the method you use for sending mail: Yes

If you use sp_send_dbmail  (which is what I use), then yes, you can set the FROM address in the call to send mail.

See docs:
http://msdn.microsoft.com/en-us/library/ms190307.aspx

@from_address  is what you want, I believe

If you want to actually log in to the mail server as a diff user, then you need to mod the profile or create a new one. The auth to the mail server is configured in the profile.


Quorom Configuration for 3 Node Windows cluster

if i face similar scenario when the onsite 2 node will go down together in that case how can i keep the cluster operational on one node / Dr site..

With the current configuration you cannot assuming the 2 nodes simultaneous went down. To avoid this you have to add one more node from  the DR side into the WSFC configuration and set up a fileshare witness(preferred). The FS witness should reside on VM or machine which is accessible to both production and DR and should be kept at "third place" which is not affected when either of DC or DR is completely down. I would suggest cloud witness here. Cloud witness do have some lags so please test the scenario with various test cases. With 4 nodes and 1 FS witness even if 2 nodes at DC went  down there are still 3 votes from 2 nodes at DR and one FS witness to keep WSFC up and running.

The quorum for this configuration would be Node and Fileshare majority.

Replying to your question from comments

  So can i have 2 Node on site , 3rd Node DR and a file share at the DR site. In that case I can survive if both the onsite node goes down.

No WSFC would not survive because there are 4 voting members in WSFC and we need more than 2(More than 50%) to be voting for quorum and WSFC to be online.

  Or during the failover before the maintenance can i just remove the 2 onsite nodes from voting rights leaving the DR with 1 vote and thus the cluster will stay alive even the 2 Node onsite went down

Now this is something different from unexpected failover, yes you can remove votes from DR nodes or, if you are using Windows server 2012 r2 and above you can gracefully shutdown the 2 nodes at DC and still the DR node would be online, this is called as  last man standing. This is because of dyanamic quorum and dynamic witness functionality of Windows server 2012 r2 server. But please keep in mind, in this scenario you are doing shutdown in planned manner and that is why dynamic quorum and witness are working if the 2 nodes go down unexpectedly nothing would work and whole WSFC would come down.

For your current configuration if suppose due to unexpected DC shutdown your WSFC is down you can use forcequorum to bring the WSFC online at DR node. This method is not complex and if some downtime can be afforded this is useful method. A simple practice and it would hardly take 5-10 mins to bring WSFC online. This will save cost of additional nodes and additional infrastructure.


Deprecated functions / features work fine in your current SQL Server Version. It is just a hint for you that in future versions of the SQL Server these functions will be dissmissed or replaced by others.
It doesn't mean that these functions are removed in the very next version, but it is planned in long term to remove them.

A list of deprecated features is available here: Microsoft SQL Server 2016 Deprecated features


SSDT Schema Compare doesn't work while a BULK INSERT is in progress

The OBJECTPROPERTY call requires a schema stability (Sch-S) lock, which is only incompatible with a schema modification (Sch-M) lock.

The BULK INSERT will take a Sch-M lock in some circumstances. These are listed in the "Table Locking and Logging During Bulk Import" section of Guidelines for Optimizing Bulk Import in Books Online:

If the destination table is clustered, you may find enabling trace flag 610 helps. Please read the whole series of those posts and the Data Loading Performance Guide and test thoroughly if you decide to go this route.

I have no idea why SSDT checks the IsEncrypted property for tables. I cannot imagine a scenario where that makes sense, but that's a question for the SSDT people.


Why don't HOST comands appear in the spool?

Yeah spool only captures output from SQLPlus itself. Try this - Before invoking your OS command, turn spooling off. Then pipe the output to the end of the file. Then you can use spool append to start spooling from where you left off again:

spool test.log
select blah from blah;
spool off
!echo %COMPUTERNAME% >> test.log
spool test.log append
select * from argh;


-- Employee (aka operator) EMP exists.
--
employee {EMP}
      PK {EMP}

-- Customer CST exists.
--
customer {CST}
      PK {CST}

-- On date RFQ_DTE, request for quotation
-- number RFQ_NO was received from customer CST.
--
rfq_in {CST, RFQ_NO, RFQ_DTE}
    PK {CST, RFQ_NO}

FK {CST} REFERENCES customer {CST}

-- Project PRO was started on date PRO_DTE
-- in response to request for quotation
-- number RFQ_NO from customer CST.
--
project {PRO, CST, RFQ_NO, PRO_DTE}
     PK {PRO}
     AK {CST, RFQ_NO}

FK {CST, RFQ_NO} REFERENCES rfq_in {CST, RFQ_NO}

-- Offer revision REV for project PRO
-- was created on date OFR_DTE.
--
offer {PRO, REV, OFR_DTE}
   PK {PRO, REV}

FK1 {PRO} REFERENCES project {PRO}

-- On date OW_DTE employee EMP spent OW_HRS hours
-- working on offer revision REV for project PRO.
--
offer_work {PRO, REV, EMP, OW_DTE, OW_HRS}
        PK {PRO, REV, EMP, OW_DTE}

FK1 {PRO, REV} REFERENCES offer {PRO, REV}

FK2 {EMP} REFERENCES employee {EMP}

-- Offer revision REV for project PRO
-- was accepted and approved on date APR_DTE.
--
accepted {PRO, REV, APR_DTE}
      PK {PRO}

FK {PRO, REV} REFERENCES offer {PRO, REV}

-- On date PW_DTE employee EMP spent PW_HRS hours
-- working on project PRO.
--
project_work {PRO, EMP, PW_DTE, PW_HRS}
          PK {PRO, EMP, PW_DTE}

FK1 {PRO} REFERENCES accepted {PRO}
FK2 {EMP} REFERENCES employee {EMP}

Create a view to report work hours.

CREATE VIEW work_hours
AS
SELECT OW_DTE AS DTE
     , EMP
     , OW_HRS AS HRS
     , PRO
     , 'O'    AS WRK_TYPE
     , ('offer revision ' || REV) AS DETAIL
FROM offer_work
UNION
SELECT PW_DTE AS DTE
     , EMP
     , PW_HRS AS HRS
     , PRO
     , 'P'    AS WRK_TYPE
     , 'some project work' AS DETAIL
FROM project_work
;

Note:

All attributes (columns) NOT NULL

PK = Primary Key
AK = Alternate Key (Unique)
FK = Foreign Key


It is running a stored procedure named proc_DefragmentIndices in many (possibly all) of these databases at a time.
The stored procedure rebuilds every index in the database unconditionally.

I assume you don't have any control of this, but I'd be remiss if I didn't suggest that you make this stop happening as soon as possible   Use Ola Hallengren's script, which will at list rebuild or reorg only at specific fragmentation thresholds.  Or considering avoiding index rebuilds in favor of statistics updates.

How can INDEX rebuilds be going parallel when MAXDOP is set to 1?

They shouldn't be.  And they really shouldn't be because you're using Standard Edition - and parallel index operations are an Enterprise Edition feature.  I wonder if there is just something weird about the way this is showing up in Activity Monitor.

Activity Monitor shows the ALTER INDEX commands but sp_WhoIsActive does not. Does anyone know why?

That's another red flag that something is off here.  Index rebuilds should show up just fine in sp_WhoIsActive.  Here's me rebuilding the Posts table (from the StackOverflow2010 sample database) on my laptop:
USE StackOverflow2010;

ALTER INDEX PK_Posts__Id ON dbo.Posts REBUILD;

As a further point of investigation, you could query sys.dm_os_tasks directly when you see one of these ALTER INDEX sessions that appears to have gone parallel, to be extra sure whether it's serial as it should be, or parallel.


Is there anyway to know when a sql server instance was installed?

This query:

SELECT create_date
FROM sys.server_principals
WHERE sid = 0x010100000000000512000000


On pgAdmin 4, how to basically copy the content of one or two cells?

OK I've found.

You have to double click on the cell, then a popup pops up, with the cell content selected, and then you can use ctrl+c...


How is parallelism threshold (seconds) used if query CPU cost is actually unitless

The parallelism threshold is based on the estimated plan cost. If the estimated plan cost goes past the parallelism threshold, the optimizer considers parallel plan options too. There is no other measurement involved.

In the beginnings of the optimizer the plan cost was derived based on actual measurements on a specific computer. Since then throughout many versions a lot has changed with the optimizer, so attaching any time unit to the current "estimated cost" is not appropriate anymore. For example, not only estimated execution time influences that estimated cost in newer versions of SQL Server. The only thing it is telling you is that the optimizer assumes that a query plan with a higher estimated cost for the same query executed under identical circumstances will require more resources.

You cannot even compare the estimated cost of two different queries. It is fairly easy to come up with two queries where the estimated cost of the one is ten times higher, but the execution time of the other is actually significantly higher. So, you should never use the estimated cost of a query plan for performance tuning.

The quote in your question is a left over from those early days when it still made sense. It was at that time also mentioned in the section explaining the estimated query cost.


For future additions etc. it is better to have a course table and refer to it in your questions table. This gives you flexibility when modifying your structure.
Again, for future additions you had better to have a separate answer table.

Your design can be like this:

Since it is a one-to-many relationship, there will be a course_id field in the question table.


Upgrading to SQL Server 2017 from SQL Server 2012

Unless it's a CU or SP, I'd always stick to a side-by-side upgrade over an in-place one.  It allows you to rigorously test and UAT the new version and ensure that all your queries/processes still run efficiently and aren't plagued by new Execution Plans or query engine "features" that can turn out to be a serious hindrance.

If you can't spout the license, try upgrading to 2017 Developer Edition, which is still a full version.  As long as you don't use the Dev edition for production, you can do all the dev/testing/staging you want on it.

Here's my favorite post on the topic.
The e-book is available in the FRK. (I seem to remember this post being longer but I guess not).
https://www.brentozar.com/archive/2015/10/sql-server-migration-and-upgrade-checklist-video-and-ebook/


How data can be transferred from Sybase SQL Anywhere to SQL Server

Method 1:

You can create a Linked server at SQL server pointing to SYBASE and then create a SQL server agent job that does the task of refreshing the data from SYBASE to SQL with help of OPENQUERY. This is better explained in the article here.

Method 2:

If you are comfortable at creating SSIS package, then you can create one, in case method 1 cannot be used with security point in concern, to fetch the data from SYBASE to SQL server. You can follow the steps as explained in Extracting data from Sybase SQL Anywhere using SSIS through ODBC.

Method 3:

As you said, you have the access to client server, then you may give a try with option of extracting the data from sybase DB tables with help of writing few line codes using bcp utility into csv or text files on the sybase server or copy that onto SQL server. Once the data has been exported onto SQL server you may import with various option available to import the same text/csv file into SQL server.


How to create lots of SQL-Developer Connections quickly?

SQL Developer stores its connection information in (on Windows):

%HOMEPATH%\Appdata\Roaming\SQL Developer\system18.1.0.095.1630\o.jdeveloper.db.connection\connections.xml

The path obviously changes within different versions because of the version number.

I am not aware of any built-in solution for adding entries except the GUI, but it is not that difficult to copy an existing entry and modify the values. Passwords are stored in a hashed form, copying an existing hash value works as well.


Safest way to uninstall the previous SQL Server versions

I strongly suggest to read Working with Multiple instance of SQL Server. If it is not interrupting at all STOP the SQL Server services which is not required its more safer approach. Microsoft has history of behaving incorrectly when instance is removed from server having multiple instances present. I have seen it multiple times

IMHO If you have multiple instances its more safer to use command prompt to remove SQL Server instance as compared to GUI. Below is the dummy script. You have to open CMD as administrator and move to folder where Setp.exe file is present and then run below script.

Setup.exe /Action=Uninstall /FEATURES=SQL,AS,RS,IS,Tools /INSTANCENAME=MSSQLSERVER

You can read about uninstall parameters from Books Online. You can also have a look at how to uninstall existing instance of SQL Server

If you want to remove SQL Server 2005 This support article is very well written


To schedule a job to run once a month, in this case midnight on the first day:

USE [msdb]
GO
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Get Date',
    @enabled=1,
    @notify_level_eventlog=0,
    @notify_level_email=0,
    @notify_level_netsend=0,
    @notify_level_page=0,
    @delete_level=0,
    @category_name=N'[Uncategorized (Local)]',
    @owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Get today''s date',
    @step_id=1,
    @cmdexec_success_code=0,
    @on_success_action=1,
    @on_success_step_id=0,
    @on_fail_action=2,
    @on_fail_step_id=0,
    @retry_attempts=0,
    @retry_interval=0,
    @os_run_priority=0, @subsystem=N'TSQL',
    @command=N'Select getdate()',
    @database_name=N'master',
    @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'First day of month',
    @enabled=1,
    @freq_type=16,
    @freq_interval=1,
    @freq_subday_type=1,
    @freq_subday_interval=0,
    @freq_relative_interval=0,
    @freq_recurrence_factor=1,
    @active_start_date=20180312,
    @active_end_date=99991231,
    @active_start_time=0,
    @active_end_time=235959,
    @schedule_uid=N'7e73cf5c-8036-41a2-af0d-3b2151862684'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO


DECLARE @t TABLE (MainStationID int, SubStationID varchar(3), StartDateTime datetime, EndDateTime datetime);
INSERT INTO @t VALUES
(1, 'RH1', '20181219 17:24:08.440', '20181219 17:25:04.523'),
(1, 'RH1', '20181219 17:25:52.283', '20181219 17:26:05.280'),
(2, 'RH2', '20181219 18:36:51.530', '20181219 18:36:57.107'),
(2, 'RH2', '20181219 18:37:03.080', '20181219 18:37:05.090');

;WITH ct AS
(
    SELECT
        MainStationID,
        CONCAT(SUBSTRING(SubStationID, 1, 2), ' STN - ', MainStationID) as Station,
        COUNT(*) as [Count],
        SUM(DATEDIFF(SECOND, StartDateTime, EndDateTime)) as Duration
    FROM
        @t
    WHERE
        SubStationID LIKE 'RH%'
    GROUP BY
        MainStationID,
        SubStationID
)
SELECT
    MainStationID,
    Station,
    [Count],
    CONCAT(RIGHT('0' + CAST(Duration / 3600 AS varchar(3)), 2), ':',
           RIGHT('0' + CAST((Duration / 60) % 60 AS varchar(3)), 2), ':',
           RIGHT('0' + CAST(Duration % 60 AS varchar(3)), 2)) AS Duration
FROM
    ct
ORDER BY
    MainStationID,
    Station;

| MainStationID | Station    | Count | Duration |
|---------------|------------|-------|----------|
| 1             | RH STN - 1 | 2     | 00:01:09 |
| 2             | RH STN - 2 | 2     | 00:00:08 |


Understand why rank() over doesn't fit to don't select duplicates rows

RANK() is a deterministic function, meaning that duplicates will be labelled with the same rank value. The outputs of your queries suggests to me that there are multiple records with the same user_id and image_id that also have the same created_at value. These records will all return with the same RANK() value.

If you run your inner query, you will see these duplicates where all three attributes are the same. If the created_date is also the largest value for that combination of user_id and image_id they will all have the RANK() value of 1.

To get your desired output, you should instead be using ROW_NUMBER(). This is a non deterministic function when the parameters in the OVER clause do not uniquely determine the rows, which is true in this case. This will assign a unique result to every row, but as your RANK() query provided duplicates it will assign each of these rows a unique value at random.

Your second query using ROW_NUMBER():

SELECT user_id,
       image_id
FROM
  (SELECT user_id,
          image_id,
          ROW_NUMBER() OVER (PARTITION BY user_id, image_id
                       ORDER BY created_at DESC) AS i_ranked
   FROM active_downloads) AS i
WHERE i.i_ranked = 1;


Connected to MySQL database but cannot print the data

You definitely have to check your query execution response, as clearly suggested by the mysqli::query documentation, by adding the following code fragment just after the query call.

if (!$result) {
    printf("Error: [%s] %s<br/>", $conn->errno, $conn->error);
}

Hope it helps.


Creating very quickly an composite partial index on 2 new columns with null values

There is no built-in way to do this.  If you are willing to compile your own PostgreSQL binaries it might be relatively easy to implement this.  This part of the code gets whacked around a bit between versions to add new features and performance improvements, so you would have to target a specific version.


Database Design for updatable sequential records

If all you need is to store which step of your process comes after which previous step, then all you need is the following:

ProcessID | ParentProcessID | PreviousProcessID

Of course, you will need a FK constraint to make sure that (ParentProcessID | PreviousProcessID) points to a valid (ParentProcessID | ProcessID)

If I understood your requirements and this design is valid, then it is easy to insert/remove/move around steps in your process - you do not have to propagate any changes to your child tables, because they refer to your primary key on (ParentProcessID | ProcessID).

HIH


You can create a dynamic table of dates for the current month

SELECT date_field
FROM
(
    SELECT
        MAKEDATE(YEAR(NOW()),1) +
        INTERVAL (MONTH(NOW())-1) MONTH +
        INTERVAL daynum DAY date_field
    FROM
    (
        SELECT t*10+u daynum
        FROM
            (SELECT 0 t UNION SELECT 1 UNION SELECT 2 UNION SELECT 3) A,
            (SELECT 0 u UNION SELECT 1 UNION SELECT 2 UNION SELECT 3
            UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7
            UNION SELECT 8 UNION SELECT 9) B
        ORDER BY daynum
    ) AA
) AAA
WHERE MONTH(date_field) = MONTH(NOW());

NOTE: If you cut-and-paste the above query as is, it will generate the whole month for you

You then LEFT JOIN this to your original query

SELECT
    AAA.date_field,
    IFNULL(BBB.val,0) val
FROM
(
    SELECT date_field
    FROM
    (
        SELECT MAKEDATE(YEAR(NOW()),1) +
        INTERVAL (MONTH(NOW())-1) MONTH +
        INTERVAL daynum DAY date_field
        FROM
        (
            SELECT t*10+u daynum FROM
            (SELECT 0 t UNION SELECT 1 UNION SELECT 2 UNION SELECT 3) A,
            (SELECT 0 u UNION SELECT 1 UNION SELECT 2 UNION SELECT 3
            UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7
            UNION SELECT 8 UNION SELECT 9) B ORDER BY daynum
        ) AA
    ) AA WHERE MONTH(date_field) = MONTH(NOW())
) AAA LEFT JOIN (SELECT date_field,val FROM MY_TABLE) BBB
USING (date_field);

Give it a Try !!!


Is heap a table without indexes at all or without a clustered index?

You need to be very careful what you read on the interwebs ;-) (of course, that also goes for this answer or pretty much anything anywhere, but still). Just as there is a lot of good information out there, there is also a lot of misinformation (and sadly, this is not confined to technical info). And people copy and paste / repost / share both. So, it is good to ask the question :-).

Eventhough BOL does have some mistakes, in this case (and most cases) it is correct: a Heap is specifically a table without a Clustered index. It has nothing to do with non-Clustered indexes. Of course, this could be a simple case of misunderstanding what is being said in these "online posts" since "a table without any kind of indexes" is a Heap, but only because having no indexes means that there is no Clustered index. However, if these posts claim that a table with only non-Clustered indexes is not a Heap, then they would definitely be incorrect.

If you look at sys.indexes you will see that index_id of 1 is a Clustered Index and 0 is a Heap. A Table will have one or the other. It is not possible to have both. Non-clustered indexes start at index_id of 2 and go up from there. All tables will have an index_id of either 0 or 1 and optionally one or more of index_id >= 2.

You can even test this by using the following query:

SELECT COUNT(*)
FROM   sys.indexes si
WHERE  si.index_id IN (0, 1)
GROUP BY si.[object_id]
HAVING   COUNT(*) > 1;

It should never return a row.

Here is a second test which is even more obvious and does not allow for someone to speculate that the condition can exist but just doesn't, which I suppose is possible with the test above:

-- DROP TABLE #tmp;
CREATE TABLE #tmp (Col1 INT, Col2 INT);
SELECT * FROM tempdb.sys.indexes si WHERE si.[object_id] = OBJECT_ID(N'tempdb.dbo.#tmp')
-- 1 row; index_id = 0 and type_desc = HEAP

CREATE NONCLUSTERED INDEX [IX_#tmp] ON #tmp (Col2 ASC);
SELECT * FROM tempdb.sys.indexes si WHERE si.[object_id] = OBJECT_ID(N'tempdb.dbo.#tmp')
-- 2 rows; index_id = 0 / HEAP and index_id = 2 / NONCLUSTERED

CREATE CLUSTERED INDEX [CIX_#tmp] ON #tmp (Col1 ASC);
SELECT * FROM tempdb.sys.indexes si WHERE si.[object_id] = OBJECT_ID(N'tempdb.dbo.#tmp')
-- still 2 rows (not 3!!); index_id = 1 / CLUSTERED and index_id = 2 / NONCLUSTERED

The point of this second test is that the authority on what is and is not true about SQL Server is always going to be SQL Server itself. So it is important to test to essentially ask SQL Server the question, and not a human. Even the experts are sometimes wrong, but SQL Server is always correct (with regards to questions about SQL Server, of course).

Also, while this is not "proof" in any definitive way, the title of the following MSDN page is rather telling:

Heaps (Tables without Clustered Indexes)


Oracle does not use transitive closure to add additional predicate


How to change name for administrator login on azure sql server

So this may be an old question, but the problem is still relevant today.
The only way that you can alter the administrator of an Azure Sql Server, is to create a new SQL Server. Once the administrator has been set, you can reset their password, but not the name.

I just ran into this same issue and had the same #facepalm moment.


Starting postgresql service and specifying data directory

There error is telling ... The problem is that you ran initdb as your local login, but I'm pretty that your login (username) doesn't have access to /var/run/postgresql. If you have a clean install, may I recommend that you remove the data directory and start all over, this time by su-ing into the postgres account:

username@jupiter:~$ sudo su - postgres

Then you can initdb

postgres@jupiter:~$ /usr/lib/postgresql/8.4/bin/initdb --encoding=UTF8 --pgdata=/mydata/pgdbdata/

Also, as Catcall mentioned, make sure you're using the correct location for your data.


Sql Server Mirroring Failover and Failback

Applications do their testing against the new principal which is ServerB but don't want to take those changes or sync to ServerA (Mirror).
Is there any way to do this without breaking the mirror?

No this is not possible to prevent changes to ServerA. You have to resetup mirroring from original primary ServerA to ServerB
What you can do is :

Failover ServerA to ServerB.
Ask Users to test (making changes to serverB).
When the test is going on, in the background make full backup and transfer it to serverB. Make sure you use compression and instant file initialization. Depending on the database size, this will take some time.
Once the testing is done, you can point your application to serverA.  In the background, restore the database on serverB WITH NORECOVERY. This is initializing the mirroring from backup.
Take transaction log backup on primary (serverA) and then restore it on mirror database (serverB).


How do I grant PostgreSQL superuser access to vacuum tables?

It sounds like you revoked superuser privileges from the cluster's founding user.  Don't do that.  Log in as your remaining superuser, and grant superuser privileges back to the founding user.  If you have no remaining superusers, then you need to shut down the server, start it up in single-user mode, and regrant superuser privs.

The founding user is the username specified to initdb's -U argument when the database was created, or if that argument was not provided, then it is the name of the OS user who ran the initdb.  This is most often postgres.


Stop MySQL generating repeated mysql-relay-bin-****.00000* files

SHOW VARIABLES LIKE '%log%';

It looks like the file name has a - that is really a "NON-BREAKING HYPHEN" instead of a regular minus sign.


does that mean no matter how good the CE is, the query has no chance of not spilling over? since the query max ram is hard capped at 38 GB

The overall memory grant for your query appears capped at 37GB given your current hardware and SQL Server configuration.

If the Sort cannot be performed within the Memory Fraction (0.860743 in that plan) of the query memory grant, it will spill to tempdb. Note also that this parallel Sort splits its fraction of the query memory grant equally across 12 threads, and this allocation cannot be rebalanced at runtime.

  does query optimizer not take max query ram into consideration when building the plan? (forcing a Hash Match aggregate would eliminate the sort step and significantly improve query performance, unfortunately, the actual query is coming from Cognos and we have no control of it)

Yes, it does, but only as an input to the general costing framework. The optimizer chooses the plan that looks cheapest according to its model. If the numbers are wrong, the plan choice is not likely to be optimal.

In your case, the actual number of rows produced by the Stream Aggregate is significantly less than estimated:

The optimizer favours Hash Aggregate when fewer, larger groups are expected (since each group occupies a slot in the hash table). The misinformation about the density leads to an incorrect choice of Sort + Stream Aggregate.

The best plan would likely be a hash join instead of the nested loops join, and a hash aggregate. This should be able to extend batch mode processing to the important aggregation step.

SQL Server 2012 was quite limited in its transitions between row and batch mode. The execution engine never returns to batch mode once row mode processing has begun (so row-batch-row is ok, but batch-row-batch is not).

  will increase the 25% cap to close to 100% be a sensible option here? (assuming that the said server access can be controlled to limit the number of concurrent query requests)

If you want to increase the amount of memory available for this query, you could certainly do so by changing your Resource Governor setup. Increase the limit by degrees to see if you can locate a good compromise. I would be wary of going too close to 100%.

If the query is suitable for a plan guide, try a HASH GROUP hint.

Longer term, upgrading to SQL Server 2016 will pay dividends since more operators can execute in batch mode (including Sort), dynamic memory grant increases are possible, and ... about a thousand other improvements in columnstore/batch mode processing in general.


What privileges do I need in order to grant execute on dbms_rls?

Which privileges does that user require in order to grant execute on dbms_rls?

The grating user should have the execute privilege on DBMS_RLS with GRANT OPTION.

I have two users namely usra and usrb. I have granted execute on DBMS_RLS to usra with admin option. Then usra is able to grant the privilege to usrb.

SQL> conn / as sysdba
SQL> grant execute on dbms_rls to usra with grant option;

Grant succeeded.

SQL> conn usra/usra
Connected.
SQL> grant execute on sys.dbms_rls to usrb;

Grant succeeded.


SQL Server how to get around the transaction log filling up when updating a column to an int

There's no way to tell SQL Server not to use the transaction log.

What you can do is set the recovery model of the database to SIMPLE, which will overwrite old log entries as space is needed.  You should not do this on your production server, however, because you won't be able to do certain types of restores, such as point-in-time restores.

Alternatively, you can set your transaction log file to be larger -- as an unscientific rule of thumb I'd make sure that either A) your transaction log has at least about 1.5x more free space than the size of your table or B) that your transaction log can auto-grow to a drive which has at least about this amount of disk space free.

You can free transaction log space by backing up the log.  If you don't care about the log contents, throw the file away.   A shortcut for this is BACKUP LOG <Your Database Name> TO DISK = 'NUL:'.  Again, don't do this on a production server unless you are absolutely sure you understand the implications.

Another thing to be careful of (though it's not entirely germane to your question) is to make sure the table you're expanding has a clustered index defined on it.  If it does not, the table could incur a very large amount of heap fragmentation, and potentially become needlessly large on a change like this.


Does UPDATE without WHERE clause lock a table in PostgreSQL?

An UPDATE without a WHERE clause will lock all rows in the table, but will not lock the table itself for DML.

The rows can not be deleted from a different transaction because they are locked.

But you can insert new rows without problems (assuming they do not violate any constraints).

Any row that is inserted after the UPDATE will not be seen by the UPDATE statement and thus they won't be changed.


Copied from a StackOverflow answer of mine  This answer assumed C# code, but the error message is directly applicable here as well.

CLI0108E simply means the connection was broken.  From the official IBM documentation of this error code:

  CLI0108E Communication link failure.

  Explanation The connection between the driver and the data source failed during execution of this function.

  User response Establish a new connection.

The SQLState (40003) indicates that the DB server can't tell whether or not the statement completed successfully.  (Documentation of SQLState messages and their meaning can be found here: http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=%2Fcom.ibm.db2.udb.msg.doc%2Fdoc%2Fr0sttmsg.htm

values in this range:

  40001 Deadlock or timeout with automatic rollback occurred.
  40003 The statement completion is unknown.
  40504 A system error has caused the unit of work to be rolled back.
  40506 The current transaction was rolled back because of an SQL error.
  40507 The current transaction was rolled back as a result of a failure creating an index.

CLI0108E could have any number of causes:  Issues in the network, the client or server closing the connection due to a timeout, someone kicking a cable in the computer room, you name it.  I've even seen is in our environment where a virtual server running on a particular VM host got this error because there was a problem with the virtual NIC card, and it was fixed by reinstalling the driver.  The list of possible causes is surprisingly large, but a good Network Admin should be able to help.  (Monitoring packets with a sniffer is a good way to track down the source when all else fails.)

If  you can, it might not hurt to refactor the code to upload smaller sets.  Say you have a 10,000 records, try uploading a thousand at once, ten times to see if that helps.

Another approach I used (once) in a situation where the server was completely unreliable was to just try inserting one record at a time.

pseudo-code: (Assuming a connection object named "connection" and a command object named "cmd" that uses that connection...)

connection.Open()

for each record
   try
      cmd.CommandText  ="whatever changes need to be made"
      cmd.Execute()
   catch(Exception ex)
      if( the exception is a communication link failure)
         connection.Open()
         cmd.Execute()
       else
          handle different errors accordingly
       end if
  end try

next

But that approach has performance issues, is messy, and still could fail of the attempt to reopen the connection fails.  Even though I've used it in a pinch, I don't recommend it long-term.  (I got rid of that approach once the original issue was fixed.)  You're far better off trying to track down the root cause of the broken connections.

In your case, it is possible that the underlying cause may just be that you're trying to upload too much data at once. I doubt it, but it's possible.  Refactoring the code to send the data in smaller chunks would definitely by my first choice were I in your shoes.


Error: 18456, Severity: 14, State: 11. Login-based server access validation failed with an infrastructure error

Check the new endpoint name on sys.endpoints table and grant connect permission for the user. The new endpoint name should be different from "TSQL Default TCP".

Use this script for windows login:

GRANT CONNECT ON ENDPOINT::"NEW_ENDPOINT_NAME" TO [DOMAIN\firstname.lastname]

Or use this for sql authentication login:

GRANT CONNECT ON ENDPOINT::"NEW_ENDPOINT_NAME" TO "username"


SQL query output to a file is failing with error code 9 when the data contains French and English characters with size > 32k

I removed this line and its working fine now. It is writing all kind of characters to the file.(English,French,Chinese)

WHENEVER OSERROR EXIT 9;


Mysql select rows with 7 continuous dates

MySQL 8

MySQL 8 provides window functions...

SELECT item_id
FROM (
  SELECT
    item_id,
    date,
    count(coalesce(diff, 1)=1 OR null) OVER (PARTITION BY item_id ORDER BY date) seq
  FROM (
    SELECT
      item_id,
      date,
      date - lag(date) OVER (PARTITION BY item_id ORDER BY date) AS diff
    FROM foo
  ) AS t
) AS t2
GROUP BY item_id
HAVING max(seq) > 7;

Explanation

This is what we're doing on the inner.

SELECT
  item_id,
  date,
  date - lag(date) OVER (PARTITION BY item_id ORDER BY date) AS diff
FROM foo

 item_id |    date    | diff
---------+------------+------
       1 | 2017-02-10 |
       1 | 2017-02-11 |    1
       1 | 2017-02-12 |    1
       1 | 2017-02-13 |    1
       1 | 2017-02-14 |    1
       2 | 2017-02-10 |
       2 | 2017-02-13 |    3
(7 rows)

Here we return the differences. What we need to now do is isolate the ones where the date difference is 1. We assume here if the result of the difference is null it is because there was no previous date to subtract, so we set it to 1. Then we if we don't have a 1, we set the value to null so count() skips it.

SELECT
  item_id,
  date,
  count(coalesce(diff, 1)=1 OR null) OVER (PARTITION BY item_id ORDER BY date) seq
FROM (
  SELECT
    item_id,
    date,
    date - lag(date) OVER (PARTITION BY item_id ORDER BY date) AS diff
  FROM foo
) AS t;

 item_id |    date    | seq
---------+------------+-----
       1 | 2017-02-10 |   1
       1 | 2017-02-11 |   2
       1 | 2017-02-12 |   3
       1 | 2017-02-13 |   4
       1 | 2017-02-14 |   5
       2 | 2017-02-10 |   1
       2 | 2017-02-13 |   1
(7 rows)

From this point, it's just a GROUP BY and HAVING.

This was tested in PostgreSQL because MySQL 8 wasn't out yet. If you haven't used PostgreSQL, download it for free and check it out. It's like MySQL but better in every single way.


Some ideas:

Inject some GO commands every thousand or few thousand lines. Then instead of one ginormous batch it is broken up into multiple batches.
 Change your individual INSERT statements to INSERT ... VALUES () with a thousand sets each.
Use transactions and commit and/or checkpoint gratuitously (again, every 1000 inserts or so is probably a good place to start, in combination with GO). Your watch and the log will thank you.
It's a silly trivial little thing, but make sure your script has SET NOCOUNT ON - otherwise the UI, SQL Server, and the network in between spend a lot of time sending 1 row(s) affected messages back and forth for every single insert.
Best is probably to use BULK INSERT, BCP or maybe even SSIS instead of Management Studio. Part of the problem is probably the memory overhead on your own machine just loading that huge file, never mind trying to parse/execute it. I'd be curious to see private bytes for ssms.exe while this is going on...
Based on offline discussion with the OP, adding another option: ask the vendor for a backup instead of a script. Then you can restore that to some test or dummy system, and generate inserts from that manually (or use import/export, or Red Gate's data compare tool). This may end up being net same amount of time, but won't kill the log with that huge script of millions of inserts...


Copy result of a query to a new PostgreSQL database

Some ideas:

You can use COPY (SELECT ...) TO ... to write the relevant parts of the tables to a file on the database server and use COPY ... FROM on the destination database to load the data.

If the databases are not on the same machine, you could use NFS or similar for a shared directory, or you can use COPY ... TO PROGRAM ... to pipe the result into scp.
You can use a postgres_fdw foreign table and just use

INSERT INTO foreign_tab
   SELECT ... FROM localtab;

to transfer the data.


How fast does SQL Server start moving data if you add another Rows Data File?

SQL Server uses a proportional fill algorithm when determining where to write data.

If you have one file that is 90% full, and add a similarly sized new, empty file to the same filegroup, the new file will get approximately 90% of the writes (because it is nearly empty, and the existing file is 90% full).

Set the new file to the same size as the existing file (naturally, this would be on a different drive, if the existing drive is out of space).  Preallocating a good amount of file space is required for the proportional fill algorithm to actually write most of it's data to the new file.

Once you have the filegroups configured the way you want them, you can re-balance the data between the files by rebuilding tables and indexes, ensuring you specify the correct filegroup in the ON .... clause for each table and index being rebuilt.

As an aside, get in the habit of always specifying the maximum file size, and managing that purposefully. Setup alerts in SQL Server Agent so you get alerted as files fill up.  When you're thinking about maximum file sizes, evaluate a sensible file growth increment; one that balances time required to grow the file against the desire to not be continuously growing files.  Currently, if I think a filegroup needs 10 GB of disk, and I decide to split that into two files of 5GB each, I'd probably set the initial file size at 3GB for each file, with filegrowth increment set to 250MB or 500MB, and I'd set the maximum file size for each file at, say, 6GB just to give a bit of leeway.   Then I'd setup an event to warn when any file goes over 80% full, to give you time to provision more disk space.  My answer here shows an example of setting up an alert to monitor log file growth.


As long as you have the dates in a date column, you can use a simple CASE to choose, which meal is selected.
The result if the query, depends when you look for the example, it will choose pasta before 9 in the morning a Minute later and you would get  gnocchi, you only have to enter the menus ahead of time
In an event you could delete the old menus, but that is not necessary, as you will not have millions of rows.

CREATe TABLe "OrDERS" ("orderdate" date, "food" varchar(40))

INSERT INTO "OrDERS" VALUES (CURRENT_DATE  ,'Pasta'), (CURRENT_DATE  + INTERVAL '1 day','Gnocchi')

SELECT to_char("orderdate",'Dy, Mon DD'), "food" FROM "OrDERS"
WHERE "orderdate" =
       CASE WHEN NOW() < now()::date + interval '9h' THEN now()::date
            ELSE now()::date + INTERVAL '1 day'
       END

to_char     | food
:---------- | :-----
Tue, Dec 22 | Gnocci

db<>fiddle here


media family on device is incorrectly formed. SQL Server cannot process this media family

If you are attempting to restore newer database to older SQL version you can always simply manually create the database in the older SQL version and utilize the task - generate scripts and include the data.

Save to File --> Advanced--> Types of data to script --> Schema and data.

Also make sure to Script for Server Version.

This might be the only supported option if you're trying to go from newer SQL Server to older version.

Related: Restore SQL Server 2012 backup to a SQL Server 2008 database?


Creating two sub queries using WITH clause

There are two problems with your query:

You are missing a comma before trip_mode.travel_mode.
If you want to reference the CTEs in the main query, you have to add them to the FROM clause.


Why PostreSQL 9.2 does not use index scan if it can?

PostgreSQL only supports index-only scans since 9.2 and only if the visibility map allows that.

If there is but a single concurrent transaction which has updated a record in a page (and hence there exists a record not visible to all transaction), the visibility map record for this page is invalidated and its records need to be checked for visibility (requiring a heap fetch).

A heap fetch is quite an expensive procedure and it's impossible to tell now many of them would be required on the optimization stage.

So optimizer just tries to play safe and chooses the more predictable path.


Galera Cluster not syncing from a standalone Mysql Server

The question comment guess by jerichorivera was absolutely right:

  log-slaves-updates on node1 enabled?

..but you seem to have misunderstood.

log_bin=/var/log/mysql/mysql-bin

(or whatever file basename) to enable writing the binary logs a slave can read from. However, this alone lets the node write empty binary logs (a size of a few hundred bytes) if it is slave on its own. To act as intermediate master, also set

log_slave_updates

in the my.cnf.


How do I do an outer join between all nested subqueries? (getting an error of 'OUTER' is not a valid input for this position)

You need to specify whether you want an left, right or full outer join. A left outer join is the same as left join. Assuming this is what you want:

[...]
FROM (SELECT subfactor.score, dateID, EmployeeID
      FROM employeescore
      LEFT JOIN subfactor
          ON employeescore.SubFactorID = subfactor.SubFactorID
      LEFT JOIN factor
          ON subfactor.FactorID = factor.FactorID
      WHERE DateID='Jan2015'
        AND employeescore.EmployeeID='1'
        AND subfactor.FactorID='A1'
     ) a
LEFT JOIN (SELECT subfactor.score, DateID, EmployeeID
           FROM employeescore
           LEFT JOIN subfactor
               ON employeescore.SubFactorID = subfactor.SubFactorID
           LEFT JOIN factor
               ON subfactor.FactorID = factor.FactorID
           WHERE DateID='Feb2015'
             AND employeescore.EmployeeID='1'
             AND subfactor.FactorID='A1'
      )b
          ON a.EmployeeID = b.EmployeeID
[...]


Which version is the right one to begin with Oracle

Start here: 11G 2 Day DBA

Best of luck!


Consecutive Rows to fetch from SQL server table

Without knowing what you mean by dynamic days interval, the following is an example of how I might attempt this.

USE tempdb;
GO
CREATE TABLE dbo.ShiftStatii
(
    ShiftStatusID INT NOT NULL
        CONSTRAINT PK_ShiftStatii
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , StatusName VARCHAR(255) NOT NULL
);

INSERT INTO dbo.ShiftStatii (StatusName)
VALUES ('Present'), ('Absent'), ('Holiday');

CREATE TABLE dbo.Employees
(
    EmpID INT NOT NULL
        CONSTRAINT PK_Employees
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , EmployeeName VARCHAR(255)
);

INSERT INTO dbo.Employees (EmployeeName)
VALUES ('John'), ('Jane'), ('Joe');

CREATE TABLE dbo.Shifts
(
    ShiftID INT NOT NULL
        CONSTRAINT PK_Shifts
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , EmpID INT NOT NULL
        CONSTRAINT FK_Shifts_EmpID
        FOREIGN KEY REFERENCES dbo.Employees(EmpID)
        ON UPDATE NO ACTION ON DELETE NO ACTION
    , InDate DATE
    , ShiftStatusID INT NOT NULL
        CONSTRAINT FK_Shifts_ShiftStatusID
        FOREIGN KEY REFERENCES dbo.ShiftStatii(ShiftStatusID)
        ON UPDATE NO ACTION ON DELETE NO ACTION
);

INSERT INTO dbo.Shifts (EmpID, InDate, ShiftStatusID)
VALUES (1, '20130201', 1)
    , (2, '20130201', 2)
    , (3, '20130201', 1)
    , (1, '20130202', 1)
    , (2, '20130202', 2)
    , (3, '20130202', 1)
    , (1, '20130203', 1)
    , (2, '20130203', 1)
    , (3, '20130203', 2)
    , (1, '20130205', 1)
    , (2, '20130205', 3)
    , (3, '20130205', 2);

SELECT E.EmployeeName, S.InDate, SS.StatusName
FROM dbo.Shifts S
    INNER JOIN dbo.Employees E ON S.EmpID = E.EmpID
    INNER JOIN dbo.ShiftStatii SS ON S.ShiftStatusID = SS.ShiftStatusID
ORDER BY E.EmployeeName, S.InDate

The data for my test, therefore, is:

SELECT E.EmployeeName, S1.InDate, S2.InDate
FROM (
        SELECT ROW_NUMBER()
            OVER (PARTITION BY S.EmpID ORDER BY S.InDate) AS EmpRowNum, *
        FROM dbo.Shifts S) S1
    INNER JOIN (
        SELECT ROW_NUMBER()
            OVER (PARTITION BY S.EmpID ORDER BY S.InDate) AS EmpRowNum, *
        FROM dbo.Shifts S
        ) S2 ON S1.EmpID = S2.EmpID AND S1.EmpRowNum = S2.EmpRowNum - 1
INNER JOIN dbo.Employees E ON S1.EmpID = E.EmpID
WHERE S1.ShiftStatusID = 2 AND S2.ShiftStatusID = 2

The results of this are:


Table column references partial key of table B

One way is to create a domain table for the text keys:

CREATE TABLE TEXT_KEYS
( TEXT_KEY INT NOT NULL PRIMARY KEY );

CREATE TABLE Texts
( TEXT_KEY INT NOT NULL REFERENCES TEXT_KEYS (TEXT_KEY)
, [language] [nchar](5) NOT NULL
, [text] [nvarchar](max) NULL
,   CONSTRAINT [PK_Texts] PRIMARY KEY CLUSTERED
    ( TEXT_KEY ASC, [language] ASC
[...]

Now you can use TEXT_KEY from TEXT_KEYS as a foreign key for your other tables.


MySQL - Manipulating MyISAM files directly brings down entire database

You had setup MySQL Replication, you could achieve the following:
SLAVE
You could do the following on the Slave in Session #1
mysql> STOP SLAVE SQL_THREAD;
mysql> FLUSH TABLES WITH READ LOCK;
mysql> DO SLEEP(86400);

NOTE: Doing STOP SLAVE SQL_THREAD stop SQL processing but continues collecting binary log events from the Master.
Open another session (Session #2) and copy the .frm, .MYD, and .MYI files to your heart's content as you stated in the question.
When you are done copying the files from the Slave to the target server, simply kill the sleep session in Session #1 and mysql session. Log back into mysql and run
mysql> START SLAVE;

Doing it this way assures that all the MyISAM tables are from the same point-in-time.
MASTER
The Master can keep on going with all the reads and writes.


mysql - import only schema from full dump?

A mysqldump is only inserts, not updates. So grep -v "^INSERT" dump.sql should be sufficient. Take a look before using


What effect does serializable isolation level have on DDL-statements?

I know what it does to dml-statements, but I'm not sure what it means for ddl.

Generally speaking, not much.

DDL encompasses a huge range of operations (many of which have their own special behaviours), but most DDL will take a schema modification (Sch-M) lock on the underlying object, either for the duration of the command, or at a critical stage (e.g. at the end of most online index builds). This is the most restrictive type of lock, preventing any sort of concurrent access, at any isolation level.

Even where Sch-M is not taken, the engine is careful to take the right locks at the appropriate times to ensure the end result is correct. For example, it is not possible to build an index that does not exactly reflect the underlying data.

All of the above applies regardless of any session isolation level specified by the user.

As far as changes to metadata (i.e. the system structures underlying views like sys.tables) are concerned, while SQL Server ensures correct changes automatically, user access is only fully supported and guaranteed to be honoured at the default locking READ COMMITTED isolation level.

Quoting from Metadata Access, Isolation Levels, and Lock Hints (emphasis added):

  SQL Server does not guarantee that lock hints will be honored in queries that access metadata through catalog views, compatibility views, information schema views, metadata-emitting built-in functions.

  Internally, the SQL Server Database Engine only honors the READ COMMITTED isolation level for metadata access. If a transaction has an isolation level that is, for example, SERIALIZABLE and within the transaction, an attempt is made to access metadata by using catalog views or metadata-emitting built-in functions, those queries will run until they are completed as READ COMMITTED. However, under snapshot isolation, access to metadata might fail because of concurrent DDL operations. This is because metadata is not versioned. Therefore, accessing the following under snapshot isolation might fail:

  Catalog views
  Compatibility views
  Information Schema Views
  Metadata-emitting built-in functions
  sp_help group of stored procedures
  SQL Server Native Client catalog procedures
  Dynamic management views and functions

From the same page:

  The following table provides a summary of metadata access under various isolation levels.

Despite not being guaranteed or supported, it can be important to access metadata (e.g. catalog views, DMVs) under READ UNCOMMITTED isolation to avoid blocking issues. Most tools and diagnostic scripts make heavy use of READ UNCOMMITTED isolation (often using the synonymous but misleadingly-named NOLOCK hint). For an example, see Glenn Berry's Diagnostic Queries.

As an example of READ UNCOMMITTED access not being honoured, see Metadata functions should follow same isolation semantics as metadata queries on Connect and Bad habits : Using (certain) metadata "helper" functions by Aaron Bertrand.

Accessing metadata at an isolation higher than read committed is not even honoured - the information will be read with READ COMMITTED isolation semantics.

If I had to guess, I would say the comparison tool in the question sets isolation to SERIALIZABLE to cover any DML in the generated script.


How to find number of days since beginning of fiscal month?

This will do it:

DECLARE @d DATE = '20140502', @dm TINYINT = 26;
DECLARE @Start_Of_Fiscal_Month DATE

SET @Start_Of_Fiscal_Month = DATEADD(DAY, @dm-1, DATEADD(MONTH, -1 + DATEPART(dd, @d)/26, DATEADD(DAY, 1-DAY(@d), @d)));
SELECT @Start_Of_Fiscal_Month
SELECT DATEDIFF(dd, @Start_Of_Fiscal_Month, @d) + 1


SSIS: Passing variable from child package to parent package

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

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


Is there a way to avoid duplicating the definition of a foreign key?

There is no way to do that. And, frankly, I don't see the need.
Remember that referenced and referencing column don't have to share the same data type. They just have to have an = operator defined between them.

If your aim is to shorten the syntax, you could omit the column (or column list) of the referenced column(s) if it's the PK:

CREATE TABLE weather (
    city      text references cities,
    temp_lo   int,
    ...

Data type in the example is compatible but not the same (textvs. varchar(80)), referenced column is assumed to be the primary key of the table cities.

I quote the manual on CREATE TABLE:

  If refcolumn is omitted, the primary key of the reftable is used


Update column value based on other column with manage duplicate value MySQL query

Assuming the suburb_id is autoincremental primary key:

WITH cte AS ( SELECT suburb_id,
                     ROW_NUMBER() OVER ( PARTITION BY LOWER(locality)
                                         ORDER BY suburb_id ASC ) rn
              FROM tableA )
UPDATE tableA, cte
SET tableA.slug = CASE WHEN cte.rn = 1
                       THEN LOWER(tableA.locality)
                       ELSE CONCAT( LOWER(tableA.locality), '_', cte.rn-1 )
                       END
WHERE tableA.suburb_id = cte.suburb_id

  > are you sure that WITH ... UPDATE ... has been implemented in MariaDB? – ypercubeᵀᴹ

If not, then simply convert CTE to subquery:

UPDATE tableA,
       ( SELECT suburb_id,
         ROW_NUMBER() OVER ( PARTITION BY LOWER(locality)
                             ORDER BY suburb_id ASC ) rn
         FROM tableA ) cte
SET tableA.slug = CASE WHEN cte.rn = 1
                       THEN LOWER(tableA.locality)
                       ELSE CONCAT( LOWER(tableA.locality), '_', cte.rn-1 )
                       END
WHERE tableA.suburb_id = cte.suburb_id


How to efficiently swap a portion of a large table's data?

After a little bit more digging on the MySQL website, I found out about Exchanging Partitions and Subpartitions with Tables, which seems to be a new MySQL 5.6 feature:

ALTER TABLE x EXCHANGE PARTITION y WITH TABLE z;

It seems to be exactly what I was looking for.

After running some benchmarks on my server, I found out that exchanging a partition with a table is not instant, but still very fast: about 1s per 1,700,000 records in the table being integrated in the partitioned table (the number of records in the partition being taken out does not seem to have any influence on the speed), which is very close to the sub-second swapping I was hoping to get for a table with a few million records.

This is to be compared against inserting the rows directly in the partitioned table, where I get about 27,000 inserts / second.

Hence swapping an already filled up table with a partition is roughly 60x faster (for me, YMMV) than inserting directly into the partitioned table.

Sweet!


Embedding vs foreign table in one-to-one relations?

Option 1 is close...

However:

If a student can own zero to many cars:

you'd have ownerstudentid in the car table
car is a child of student

If a car can exist without a student, I'd consider a many-many table carstudent with a constraint on carid to allow zero or one owning student

Note:

This is where tools like NORMA come in useful to capture these relationships and constraints in plain English. See Object Role Modelling


Creating a copy of primary database and attaching the copy database in the same sql instance

IF the server is NOT a PRODUCTION server, then you can backup the databases you need for Data manipulation and restore them with databaseName_test.

Keep in mind that you will still be competing for resources on the server by creating another set of databases.

Why cant you install another instance of sql server ? Even the enterprise edition is free for 30 days (for evaluation purpose ONLY) or just buy a Developer edition which is much cheaper. If you have MSDN subscription, you will be able to get one. Windows Azure is also another option.

Below will give you the correct version and some more info :

     SELECT
   SERVERPROPERTY ('MachineName') AS PhysicalMachineName,
   SERVERPROPERTY ('ServerName') AS SQLServerName,
   SERVERPROPERTY ('Edition') AS ServerEdition,
   CAST(SUBSTRING(@@Version,charindex('SQL',@@version, 1),15) AS VARCHAR(255)) +  ' + '
       + CAST (SERVERPROPERTY ('productlevel')AS VARCHAR (50)) + ' + (Build'
       + CAST (SERVERPROPERTY ('ProductVersion') AS VARCHAR (50)) +')' AS ProductVersion,
    RIGHT(@@version, (Len(@@Version)-charindex('Windows',@@version, 1))+1) AS [O.S.],
 SERVERPROPERTY ('Collation') AS Collation


The following is kind of a shot in the dark in lack of clarification of the question, but as I had built it already earlier I'll post it in absense of any other answers.

How about implementing the message-deletion-per-recipient so that you only keep track of those messages that have been tagged as deleted-by-recipient? That would translate into getting rid of the recipient table as you had it and instead having a recipient table plus a cross-ref table for deleted messages:

recipient table

| recID              |
| recipientID --FK   |

deletedmessages table

| msgID --FK         |
| userID --FK   |

When fetching messages to be displayed to a given recipient, it would be enough to either join the two tables on msgID or to do something like

... WHERE NOT EXISTS (SELECT 1 FROM deletedmessages dm WHERE dm.recipientID = <current_user_ID> AND dm.msgID = m.msgID)

to filter out the deleted ones. Having deletedmessages indexed by both columns (probably mandatory to get the foreign keys references properly set up) would allow the DB to execute this very efficiently.

This way deletedmessages would only grow when a user actually deletes a message and not grow that much.

There's a SQLfiddle with the above solution.


SQL Server Full txt Search - Thesaurus Entries

You should really consider upgrading to a more recent version, especially if you want to utilize some of the best parts of Full Text Search.

To your question, the short answer is yes.

From the documentation for 2005, you have two main features to customize in a thesaurus, Expansion Sets and Replacement Sets:

  An expansion set contains a group of synonyms. These synonyms are
  identified in code by "substitution" tags ( and ). Queries
  that contain matches in one substitution are expanded to include all
  other substitutions in the expansion set.

and

  A replacement set contains a text pattern to be replaced by a
  substitution set.

So if you place all three of your key words (IE, IE7, Internet Explorer) into a Substitution Set, then they will all trigger matches for each other. You can put as many substitutions in the set as you'd like - it's not a one-way definition for the substitutions.


Connecting to SQL Server from R which is Running on Remote UNIX Machine

I changed delimiter which between server and port from : to , . It fixed my problem.

dbhandle <- odbcDriverConnect('driver=ODBC Driver 11 for SQL Server;server=10.XXX.XX.XX,1663;')


SQL Merge Replication Issue - problem where backup and maintenance job which is running till morning and most of the server jobs are stuck

I came to know the Anti virus was updated last week and past weekend all servers were rebooted. To test on one server we disabled some of the install anti virus features, and my job ran successfully in a reasonable time (4-5 hrs). Hope this work globally too. Fingers crossed!


DD/mm/yyyy in varchar(max) needs to be converted to yyyymmdd format

That's very complex. It's easier than that. Just run,

ALTER TABLE foo
    ALTER COLUMN dt datetime2 NOT NULL;

Where dt is the name of your varchar date field. Now you have it in a date field, and you can simply,

SELECT convert(varchar, mydate, 112)
FROM foo;


Stored proc getting ANSI_NULLS and ANSI_WARNINGS error when executed by application

The issue is specific to ANSI_WARNINGS as the value of ANSI_NULLS is stored with the definition of the Stored Procedure when it is created. Just run:

SELECT * FROM sys.sql_modules;

To see the difference between ANSI_NULLS and ANSI_WARNINGS, run the following:

-- Check current values (SSMS defaults to ON for both unless changed in
-- Tools -> Options -> Query Execution -> SQL Server -> ANSI
SELECT SESSIONPROPERTY('ANSI_NULLS') AS [AnsiNulls],
       SESSIONPROPERTY('ANSI_WARNINGS') AS [AnsiWarnings];
-- returns: 1   1

SET ANSI_NULLS OFF;
SET ANSI_WARNINGS OFF;

SELECT SESSIONPROPERTY(N'ANSI_NULLS') AS [AnsiNulls],
       SESSIONPROPERTY(N'ANSI_WARNINGS') AS [AnsiWarnings];
-- returns: 0   0

EXEC('
CREATE PROCEDURE ##SessionSettings
AS
SELECT SESSIONPROPERTY(N''ANSI_NULLS'') AS [AnsiNulls],
       SESSIONPROPERTY(N''ANSI_WARNINGS'') AS [AnsiWarnings];
');

EXEC ##SessionSettings;
-- returns: 0   0   (as expected)

SET ANSI_NULLS ON;

SELECT SESSIONPROPERTY(N'ANSI_NULLS') AS [AnsiNulls],
       SESSIONPROPERTY(N'ANSI_WARNINGS') AS [AnsiWarnings];
-- returns: 1   0   (ANSI_NULLS is back ON for the session)

EXEC ##SessionSettings;
-- returns: 0   0   (ANSI_NULLS is still OFF due to being saved with the proc definition)

SET ANSI_WARNINGS ON;

SELECT SESSIONPROPERTY(N'ANSI_NULLS') AS [AnsiNulls],
       SESSIONPROPERTY(N'ANSI_WARNINGS') AS [AnsiWarnings];
-- returns: 1   1   (ANSI_WARNINGS is back ON for the session)

EXEC ##SessionSettings;
-- returns: 0   1   (ANSI_WARNINGS is now ON due to NOT being saved with the proc definition)

Putting the SET command inside of the stored procedure is too late; it needs to be set before the EXEC of the proc. So you were on the right track with wanting to do:

SET ANSI_WARNINGS ON;exec sp_storedproc 0,0,'A','0001'

However, you cannot change the app code and how it calls the proc. BUT, you can change the proc itself. Hence, you can rename the current proc to sp_storedprocReal and create a new stored procedure named sp_storedproc that is just a wrapper that does the following:

CREATE PROCEDURE sp_storedproc
(
  @Param1 INT,
  @Param2 INT,
  @Param3 VARCHAR(50),
  @Param4 VARCHAR(50)
)
AS
SET ANSI_WARNINGS ON;

EXEC sp_storedprocReal @Param1, @Param2, @Param3, @Param4;

I have tested the above wrapper proc after reproducing the stated error and it does resolve the issue.


DBEAVER: SQL error or missing database (near ",": syntax error)

SQLite allows to insert multiple rows with one statment, but only in version 3.7.11 or later.

Apparently, your JDBC SQLite driver is several years out of date.