How can I improve the performance of a sorted join?

Perhaps this is crazy, but I like to try a bit of blue-sky-thinking every once in a while, so I'd consider adding the TrackerId column to the dbo.PacketTransmissions table to avoid the join completely.  Obviously, this means you need to modify the row-insert procedure for the table, which may or may not be feasible.

However, this change, combined with a simple index:

CREATE INDEX IX_PacketTransmissions ON dbo.PacketTransmissions
(
    TrackerId ASC
    , DateProcessed ASC
)
INCLUDE (Id); --not strictly required, since the primary key
              --is always included in every non-clustered index
              --I include them just to be explicit

creates a query plan using a run-of-the-mill index seek, combined with a key lookup for each row returned.  As in:

To test this, I created a minimally complete verifiable example:

USE tempdb;

IF OBJECT_ID(N'dbo.Sessions', N'U') IS NOT NULL
DROP TABLE dbo.[Sessions];
IF OBJECT_ID(N'dbo.PacketTransmissions', N'U') IS NOT NULL
DROP TABLE dbo.PacketTransmissions;
GO

CREATE TABLE [dbo].[Sessions]
(
      [Id] int
        CONSTRAINT PK_Sessions
        PRIMARY KEY CLUSTERED
    , [DateConnected] datetime NOT NULL
    , [Origin] nvarchar(max) NULL
    , [TrackerId] int NULL
    , [Imei] nvarchar(max) NULL
    , [Sim] nvarchar(max) NULL
    , [ProtocolVersion] tinyint NULL
)

CREATE TABLE [dbo].[PacketTransmissions]
(
      [Id] int
        CONSTRAINT PK_PacketTransmissions
        PRIMARY KEY CLUSTERED
    , [RequestId] int NULL
    , [SessionId] int NOT NULL
    , [DateProcessed] datetime NOT NULL
    , [Direction] int NOT NULL
    , [Sequence] int NOT NULL
    , [Acknowledgement] int NOT NULL
    , [DateRecorded] datetime NOT NULL
    , [Version] tinyint NOT NULL
    , [Command] tinyint NOT NULL
    , [Flags] tinyint NOT NULL
    , [Checksum] tinyint NOT NULL
    , [Data] varbinary(max) NULL
    , [TrackerId] int NULL
)
GO

INSERT INTO dbo.[Sessions] (Id, DateConnected, Origin, TrackerId, Imei, Sim, ProtocolVersion)
SELECT ROW_NUMBER() OVER (ORDER BY sc1.id)
    , DATEADD(DAY, CONVERT(int, CRYPT_GEN_RANDOM(1)), '2017-01-01 00:00:00')
    , CONVERT(nvarchar(max), CRYPT_GEN_RANDOM(128))
    , CONVERT(int, CRYPT_GEN_RANDOM(1))
    , CONVERT(nvarchar(40), CRYPT_GEN_RANDOM(38))
    , CONVERT(nvarchar(40), CRYPT_GEN_RANDOM(38))
    , CONVERT(tinyint, CRYPT_GEN_RANDOM(1))
FROM sys.syscolumns sc1
    CROSS JOIN sys.syscolumns sc2;

INSERT INTO dbo.PacketTransmissions (Id, RequestId, SessionId, DateProcessed, Direction, Sequence, Acknowledgement, DateRecorded, Version, Command, Flags, Checksum, Data, TrackerId)
SELECT ROW_NUMBER() OVER (ORDER BY s.Id)
    , CONVERT(int, CRYPT_GEN_RANDOM(1))
    , CONVERT(int, CRYPT_GEN_RANDOM(3))
    , DATEADD(DAY, CONVERT(int, CRYPT_GEN_RANDOM(1)), '2017-01-01 00:00:00')
    , CONVERT(int, CRYPT_GEN_RANDOM(1))
    , CONVERT(int, CRYPT_GEN_RANDOM(2))
    , CONVERT(int, CRYPT_GEN_RANDOM(1))
    , DATEADD(DAY, CONVERT(int, CRYPT_GEN_RANDOM(1)), '2017-01-01 00:00:00')
    , CONVERT(int, CRYPT_GEN_RANDOM(1))
    , CONVERT(int, CRYPT_GEN_RANDOM(1))
    , CONVERT(int, CRYPT_GEN_RANDOM(1))
    , CONVERT(int, CRYPT_GEN_RANDOM(1))
    , CRYPT_GEN_RANDOM(128)
    , s.TrackerId
FROM dbo.[Sessions] s
    CROSS JOIN (SELECT v.n
    FROM (VALUES (0), (1))v(n)) v;
GO

On my system, this creates around 700,000 session rows, and double that number of transmission rows.

The query then becomes:

DECLARE @TrackerId int = 100;
DECLARE @StartDate datetime = '2017-03-10';
DECLARE @EndDate   datetime = '2017-03-12';

SELECT [PacketTransmissions].*
FROM [PacketTransmissions]
WHERE [PacketTransmissions].[TrackerId] = @TrackerId
    AND [PacketTransmissions].[DateProcessed] > @StartDate
    AND [PacketTransmissions].[DateProcessed] < @EndDate
ORDER BY [PacketTransmissions].[DateProcessed] DESC;


Seperate value of a specific column with "," delimiter in Oracle using "Regular Expression"

Such an approach makes sure you don't get too many duplicates as your query does (so you removed them with a distinct in line #4; without it, you'd get 12 rows as a result):
SQL> with customer_info (cust_id, cust_name, cust_address) as
  2    (select 1, 'A', 'Tehran,Tehran' from dual union all
  3     select 2, 'B', 'Rasht,Tehran'  from dual union all
  4     select 3, 'C', 'Minsk,Tehran'  from dual
  5    )
  6  select --distinct      --> use DISTINCT to get result you want
  7         cust_id,
  8         cust_name,
  9         regexp_substr(cust_address, '[^,]+', 1, column_value) cust_address
 10  from customer_info cross join
 11    table(cast(multiset(select level from dual
 12                        connect by level <= regexp_count(cust_address, ',') + 1
 13                       ) as sys.odcinumberlist))
 14  order by cust_id;

   CUST_ID C CUST_ADDRESS
---------- - ----------------------------------------------------
         1 A Tehran
         1 A Tehran
         2 B Rasht
         2 B Tehran
         3 C Minsk
         3 C Tehran

6 rows selected.

SQL>

However, as you want to have only one 1-A-Tehran combination, distinct in line #6 has to be uncommented.


Any suggestion on how to do that?

The Easiest way to recover the data (in your scenario) using third party tool
which help you to read transaction log (backups or online),  and able to generate (T-SQL) script that can recover particular data effected by delete operation.

In case if the recovery model is simple, database has to be set OFFLINE or detached for making live database files connection to do the recovery, since the LOG Backup cannot be possible in simple recovery mode. However, Chances are less (in simple recovery) if the duration between recovery action and deleted operation is too long as the log truncation happens automatically.


Merge multiple table output but indicate which tables the records came from

You can use a full outer join between your tables and check against null values to figure out where the data comes from.

select isnull(A.ID, B.ID) as ID,
       isnull(A.Name, B.Name) as Name,
       case
         when A.ID is null then 'B'
         when B.ID is null then 'A'
         else 'A+B'
       end as Tables
from TableA as A
  full outer join TableB as B
    on A.ID = B.ID


How to count boolean values in PostgreSQL?

Use the filter() clause:

select count(*) filter (where "public") as public_count,
       count(*) filter (where not "public") as not_public_count
from  public."user";

Note that user is a reserved keyword, you have to use double quotes in order to use it as a table name.

The above assumes that the column public is of type boolean


Remote MySQL Dump not working with cnf file

Or you can put the credentials in a .cnf file in the correct section. Mysql commands will search that file and use those. This is typically how servers do local backups and maint scripts and such things. I.e.
In /etc/my.cnf
# bottom of my.cnf file
# include all files from the config directory
#
!includedir /etc/my.cnf.d

Then in some file such as /etc/my.cnf.d/credentials.cnf
#
[client]
user                  =  admin
password              =  ';\m^P[9MVJ'
#
[mysqldump]
user                  =  root
password              =  ';\m^P[9MVJ'
#

Make only root has rights to read them. I.e.
-rw-------. 1 root root  657 Dec 1 14:22 /etc/my.cnf.d/client.cnf


Does anyone know of resources for the Oracle 1Z0-072 exam objectives?

Relational Database Models

It is a basic DBA exam. I think they meant Relational Data Structures, which you would need anyway. Maybe this specific part: Introduction to Oracle Database. Nothing complicated, I have never ever encountered a modeling question on the exams from the DBA track.

Connecting to Oracle Databases

My bet is on Configuration and Administration of Oracle Net Services. Connection Manager is the odd one here, it is nice to know what it is, but I don't think the details are needed, it is a very rarely used feature.

Both of the above chapters cover other exam topics as well.


When normalizing a relational database, should multiple "type" tables be combined into one?

I would go for separate tables for each type.  With so few rows performance will not be a consideration either way.  Individual tables will only have a page or two each.  Similarly for the combined table.

Either way you will have to join a "type" table to complete your views.  Inserts are infrequent at this level of abstraction so locking is unlikely to be an issue either way.

For me, though, having different tables keeps logically distinct items separated, which is a cleaner design.

There will be many different tables which will have a relationship to a type table of some sort.  If there are many different type tables the foreign key relationships are self-documenting and enforcable through DRI.  With one combined table it is difficult to use DRI to say "Venue.TypeID comes from Type.TypeID but only if the Type.Category = 'V'", for example.  The complementary side of this relationship - "Type.TypeID with Type.Category = 'V' can only be used in the Venue table" - is also not supported in any RDBMS's DRI that I know of.


SQL Server 2012 Query: Invoices with Earliest Date in State for each Vendor

Looks like you don't have InvoiceState anywhere in your query.

Here it is with a little better formatting (PRE-COLUMN COMMA MASTERRACE!!)

SELECT VendorName
, InvoiceNumber
, InvoiceDate
, InvoiceTotal
FROM Vendors V
JOIN Invoices I
    ON V.VendorID=I.VendorID
WHERE InvoiceDate <= ( SELECT Min(InvoiceDate)
                       FROM Invoices
                       JOIN Vendors
                       ON V.VendorID=Vendors.VendorID )
GROUP BY VendorName
, InvoiceNumber
, InvoiceDate
, InvoiceTotal

Adding it (InvoiceState) to your SELECT and GROUP BY would add that level of detail.  I'd assume you need to add it as your first column (before VendorName).

Edit: looking at it further, seems like you'd also need it in your Subquery.


SQL Server created a new errorlog file but instance and agent were not restarted

I would assume it's more likely that the procedure sp_cycle_errorlog was called. There's a possibility someone accidentally right clicked on Error Log and then clicked Recycle instead of Configure which also calls this procedure.

What I would do is create an audit event on this procedure for the future so you can see how this occurs. If you do have some SQL Auditing in place, I'd search the history to see if that procedure was caught in the audit.

Jeremiah Peschka from Brent Ozar on the log recycling.


Cannot open database "dbname" requested by login.The login failed.Login failed for user 'DOMAIN\Windowsusername'

That login probably has the dropped database listed as his/her default database. You just need to assign a different default database.

In SSMS, connect to the server, then go to the Security section, then the Logins section. Find the login in question, right-click, then choose Properties. On the General tab, you'll find a "Default database" field. It probably has the name of the dropped database. Select a database that is still valid, and use the OK button to save your change.


Why does SUM() return 0 when no rows in table?

The result is correct, i.e. according to the standards, as far as I can understand it.

The related question: What is the correct result for this query? contains the paragraph that explains it:

  The following is an excerpt from the General Rules of 7.9 (the definition of the GROUP BY clause)

    1) If no <where clause> is specified, then let T be the result of the preceding <from clause>; otherwise, let T be the result of the preceding <where clause>.

    2) Case:

    a) If there are no grouping columns, then the result of
    the <group by clause> is the grouped table consisting of T as its only group.

So, when there is no GROUP BY - or when we have GROUP BY () - the result should be a single group. It doesn't matter if the table is empty or not. You'll get a single row* in the result.
If the table is empty (and the SUM() function is used), you get a single row with NULL as value - which you convert to 0 using ISNULL().

When you have GROUP BY t.id, you'll have one row for every group. But there are 0 groups in your case, so 0 rows in the result.

*: SQL-Server will however return 0 rows for the query with GROUP BY (), which is contradicting the standard.


You have two options.

Your current limit with smallfiles is 2G.  As you can see there the limit is 4194303 blocks, which is 4194303 x 512 byte block size = 2,147,483,136 bytes = 2G.

  Option 1:

Mount your file systems with the -largefiles option, which will allow you to create 32G and higher.

  Option 2:

You can only add 2G datafiles.

alter tablespace UNDOTBS1 add datafile 'path/filename' size 2G;

The -largefiles option has been the default for many years now, so there shouldn't be a problem for your unix admin to change this.


As per MongoDB BOL documented Here MongoDB provides two options for restoring secondary members of a replica set:

1) Manually copy the database files to each data directory.

2)  Allow initial sync to distribute data automatically.

  Note:- If your database is large, initial sync can take a long time to complete. For large databases, it might be preferable to copy
  the database files onto each host.


How to run Trigger only on certain updates

The trigger always "runs" -- but just because it runs doesn't mean it has to perform any action.  And a trigger that doesn't perform any action doesn't take very long, if you're concerned about efficiency.

In your case, all you need is logic to compare NEW.SellingPrice to OLD.SellingPrice and only perform the INSERT action if the two values are not the same.

Note that a common error at this step is to assume that != is the correct operator to use when comparing old and new values, but this will bite you if the column is nullable, because null values are neither equal to nor not equal to any other value, including null itself (since null is not a value, even though I myself carelessly used the phrase "null values" earlier in this same sentence).  Even if the column isn't nullable now, it is probably still a good practice to use the null-safe comparison ("spaceship") operator <=> and negate the result with NOT, because <=> expresses the actual relationship we're trying to evaluate:

DELIMITER $$

CREATE TRIGGER price_update
    AFTER UPDATE ON Product
    FOR EACH ROW
BEGIN
  IF NOT (NEW.SellingPrice <=> OLD.SellingPrice) THEN
    -- this update has changed the value of SellingPrice
    -- for the row that invoked this run of the trigger
    INSERT INTO PriceDetails
    values(
      NEW.ProductID,
      now(),
      NEW.SellingPrice);
  END IF;

END $$
DELIMITER ;


Query to get queue position for each group

This is a Gaps and Islands question. See here for more details on problems like this.

This should do what you need:

-- Generate test data
DECLARE @Companies TABLE
(
    ID INT
    ,Company NVARCHAR(100)
    ,Location NVARCHAR(100)
);

INSERT @Companies
SELECT *
FROM    (VALUES (1, 'acme', 'new york')
                ,(2, 'acme', 'philadelphia')
                ,(3, 'genco', 'st.louis')
                ,(4, 'genco', 'san diego')
                ,(5, 'genco', 'san francisco')
                ,(6, 'acme', 'miami')
        ) AS CompanyLocations(ID, Company, Location);

-- Find company positions
;WITH cte_Companies
AS
(
    SELECT ID
           ,Company
           ,CASE
              WHEN LAG(Company) OVER(ORDER BY ID) = Company
              THEN 1
              ELSE 0
            END AS CompanyPosition
    FROM @Companies
)

SELECT ID, Company
FROM cte_Companies
WHERE CompanyPosition = 0

UPDATE
Andriy noted that my solution was a SQL Servre 2012+ solution. The following code should work for versions down to 2005.

-- Generate test data
DECLARE @Companies TABLE
(
    ID INT
    ,Company NVARCHAR(100)
    ,Location NVARCHAR(100)
);

INSERT @Companies
SELECT *
FROM    (VALUES (1, 'acme', 'new york')
                ,(2, 'acme', 'philadelphia')
                ,(3, 'genco', 'st.louis')
                ,(4, 'genco', 'san diego')
                ,(5, 'genco', 'san francisco')
                ,(6, 'acme', 'miami')
                -- Further test data
                ,(7, 'genco', 'London')
                ,(8, 'genco', 'Portsmouth')
        ) AS CompanyLocations(ID, Company, Location);

-- Find company positions

SELECT ID, Company
FROM @Companies c1
WHERE NOT EXISTS    (
                        SELECT *
                        FROM @Companies c2
                        WHERE c1.Company = c2.Company
                        AND c1.ID - 1 = c2.ID
                    )


Postgres - autovacuum on several big tables

If only one database needs any attention, then upto autovacuum_max_workers will all get launched in that one database.  But it will take a few passages of autovacuum_naptime to get all of them launched.  Each one will take a different table to work on at a given time.

This might not have the effect you want, however, as the IO throttling threshold will get divided up between them.  That means that while multiple tables are vacuumed in parallel, increasing the number autovacuum_max_workers does not increase the overall speed of vacuuming when you have IO throttling turned on.  The purpose of using a higher value of autovacuum_max_workers is to ensure that small tables don't get entirely neglected if all the (would-be-lower max number) of workers are individually pinned down by large tables.

If you are worried autovac is not working fast enough, I would just go for the big guns and set autovacuum_vacuum_cost_delay = 0 in order to turn off IO throttling, and set log_autovacuum_min_duration = 0 so you can see the what is going on.  This should quickly give you a definitive answer.  As opposed to incrementally changing autovacuum_vacuum_cost_limit, which will slowly give you ambiguous answers.


Query to list all the databases that have a specific user

By using this inner join and the link is database_id = principal_id
select
dp.[name] as username,
dp.create_date,
dp.modify_date,
dp.[type_desc] as [type],
dp.authentication_type_desc as authentication_type,
dp.principal_id,
m.[name]
from sys.database_principals dp
INNER JOIN master.sys.databases m ON m.database_id = dp.principal_id
    AND m.name like '%PRD%'
where dp.[type] not in ('A', 'G', 'R', 'X')
and dp.[sid] is not null
and dp.[name] <> 'guest'
and dp.[name] = 'user_1'
order by username;

Also try to avoid != and replace it by <>.
Hope it helps,


PostgreSQL: Executing DDL on every schema

I would prefer the latter solution.  You can collect the schema names into a file (one schema per line) in psql:

\o change_schema.sql
\t on

SELECT n.nspname
FROM pg_catalog.pg_namespace n
WHERE n.nspname !~ '^pg_' AND n.nspname <> 'information_schema';

-- reset the output
\o
\t off

Then you can easily do the following:

Have a DDL changing script (for example, change_schema.sql), without reference to the including schema

SET search_path TO :schema;

BEGIN;
...
...
ALTER TABLE orders
ADD COLUMN last_modified timestamp;
...
...
COMMIT;

Then you can turn every line of the schema list into a line like

psql -h dbhost -d targetdb -f change_schema.sql -v schema=<schema_name>

with a simple sed command, for example - then you just have to run these commands.   Of course, you can turn it into a proper shell script if you like.


Do Postgres GIN indexes need to be rebuilt periodically?

Empirically, I've found GIN indexes need to be re-indexed after I've inserted a large number of rows into the table.

I had a table with 15 million rows that had a GIN index that let me do a keyword search in under 3 seconds. After I inserted another million rows, that same search took 30 seconds. I waited for a day after my inserts, but the search was still slow, so I manually ran REINDEX on my GIN index, which took a little over an hour, but afterwards my search was back to 3 seconds.


Update a JSON column with WHERE Clause in JSON Column Postgres

As noted in comments, this would be a much easier task if the data was properly normalised. However, in the interest of wasting a morning playing with postgres json functions, you could use the following query (fiddle)

with js as (
    select
        jsonb_array_elements((data->>'certified')::jsonb) as elems
    from test_table
    where clusterid = 1
), to_keep as (
    select elems
    from js
    where js.elems->>'id_no' != '216430036272'
), to_upd as (
    select
        jsonb_set(elems,'{Status}','"Active"') as elems
    from js
    where js.elems->>'id_no' = '216430036272'
), final_array as (
    select jsonb_agg(elems) as elems
    from (
        select elems from to_keep
        union all
        select elems from to_upd
    ) j
)
update test_table set
    data = jsonb_set(data,'{certified}',elems)
from final_array fa
where clusterid = 1;

Pretty clearly this is very specifically answering your narrow question, so you'll need to make your own modifications to generalise it. By way of a breakdown though...

  I want to ... update the data column and change the "Status" to "Active" where "cluster_id" is 1 and "id_no" is "216430036272"

js

select
    jsonb_array_elements((data->>'certified')::jsonb) as elems
from test_table
where clusterid = 1

The first common table expression (CTE) filters down to clusterid = 1 out of all the rows in Test_Table

The element certified in your json document is itself an array of objects - as indicated by the [{},{}] notation. For this reason, you need to unwrap the array. jsonb_array_elements turns a single row into many rows. Thus the cell...

+---------+
| array   |
+---------+
| [{},{}] |
+---------+

...becomes...

+---------+
| objects |
+---------+
| {}      |
| {}      |
+---------+

to_keep

Now that you've unwrapped the objects in the array, you need to preserve them. This CTE isolates those objects that you do not want to modify (js.elems->>'id_no' != '216430036272') so that they can be re-aggregated unchanged into the array later.

to_upd

This is the object you've stated you want to update (specified by js.elems->>'id_no' = '216430036272'). jsonb_set(elems,'{Status}','"Active"') modifies the Status element as desired (case sensitive).

final_array

select jsonb_agg(elems) as elems
from (
    select elems from to_keep
    union all
    select elems from to_upd
) j

You need to turn many rows back into one row to re-create the array-of-objects you decomposed to perform the update in the first place. UNION ALL re-connects the rowsets from to_keep and to_upd and then jsonb_agg causes

+---------+
| objects |
+---------+
| {}      |
| {}      |
+---------+

...to turn back into...

+---------+
| array   |
+---------+
| [{},{}] |
+---------+

...which allows us to use it in...

The actual UPDATE statement

update test_table set
    data = jsonb_set(data,'{certified}',elems)
from final_array fa
where clusterid = 1;

You must re-specify clusterid = 1 in order to target the correct row for modification. jsonb_set is seen here again, this time supplanting the entire certified node with the reconstructed data from final_array.elems


From the SQL Server query optimizer's point of view, there is not much to choose between the parallel and serial execution plans in this case.

In general, the optimizer's cost model reduces the CPU cost (not the I/O cost) of operators in a parallel plan in proportion to the estimated degree of parallelism available. This CPU adjustment explains why the optimizer ever chooses a parallel plan (which will generally consume more resources) over a serial plan.

Unfortunately, the cost model does not apply this CPU reduction to the inner side of a nested loops join. It makes no sense to me (because the inner side still uses parallelism efficiently), but I didn't design the cost model.

Anyway, because the majority of the CPU cost in this execution plan is associated with the Clustered Index Scan (for which CPU reduction does not apply), the choice between serial and parallel is a close one. In broad terms, to be selected, a parallel plan must save enough using the local/global Stream Aggregate to compensate for the extra exchanges (Distribute and Gather Streams). The costs involved in that decision depend sensitively on the distribution of row values, as well as the number of rows. With relatively few rows and low-CPU operators, the trade-off can easily go either way.

In short, this query suffers from a debatable design choice applied to the costing of parallel nested loops joins. You can force the selection of a parallel plan using a plan guide, or by using the undocumented trace flag 8649. In SQL Server 2016 SP1 CU2 onward, you can also use the undocumented ENABLE_PARALLEL_PLAN_PREFERENCE hint.


This setting will only let you 'respect' the encrypted database setting.  It will not generate scripts for you to run to enable encryption on the target deployment server.  If you uncheck that, it will then disable encryption on your source database scripts that you are generating for the DACPAC.

Since TDE requires several keys to fully function, you might need to configure that outside of the DACPAC in order to set encryption as on/off per database/environment.  You could utilize this as part of your greater rollout effort (using orchestration like Jenkins/Octopus/maybe TFS), doing it as part of a powershell script, and so fourth.

From:
https://social.msdn.microsoft.com/Forums/en-US/df7d6f7f-f912-48ae-b553-0cede0eedce1/ssdt-and-tde?forum=ssdt

"When this checkbox is checked, no encryption changes are made to the target database*. That is, databases are neither encrypted or decrypted. I don't see that it performs any function at all, but perhaps it will be utilized in a future version.

When this checkbox is unchecked, encrypted database are decrypted, and no changes are made to unencrypted databases. No warning or status message is output to the messages window when running the script. This is the coe that is generated:

IF EXISTS (SELECT 1
           FROM   [master].[dbo].[sysdatabases]
           WHERE  [name] = N'$(DatabaseName)')
    BEGIN
        ALTER DATABASE [$(DatabaseName)]
            SET ENCRYPTION OFF
            WITH ROLLBACK IMMEDIATE;
    END
"


Does mongodb automatically move small non-partitoned databases to spread read load?

Left to its own devices, no, MongoDB will not move those unsharded databases to a different primary shard - the automatic balancing only applies to chunks from sharded collections.

It will round robin through your shards as the databases are created to spread them out across all the shards from that perspective.  If you had one shard originally and expanded to many, the databases may have been concentrated on that shard - the round robin aspect only applies when you create the database, not the collections inside it.

Once the databases are created, and assuming you can predict what will be used and when, you can then move them to whatever shard you wish using the movePrimary command and distribute load accordingly:

http://www.mongodb.org/display/DOCS/movePrimary+Command

Naturally, this will be a quicker process if there is no data in the databases, but should still be possible after the fact.


You are missing the reference to the special table inserted which holds just the rows that were updated. Currently your trigger is updating all the rows of the tracking table, every time.

ALTER TRIGGER [dbo].[GET_DURATION] ON [dbo].[INBOUND_CALL_xxx]
AFTER UPDATE AS
BEGIN

    IF UPDATE(END_TIME) OR UPDATE(START_TIME)
    BEGIN

               UPDATE C
               SET DURATION = convert(time(0),(I.END_TIME - I.START_TIME))
               FROM
                inserted AS I
                INNER JOIN INBOUND_CALL_xxx AS C ON I.KeyColumn = C.KeyColumn

    END

END

You can also modify the trigger to update the DURATION only if the relevant columns were updated (as of this example).

Another option would be using a computed column (maybe persisted) with an expression of CONVERT(time(0), END_TIME - START_TIME), so you don't need a trigger to keep the values updated.

ALTER TABLE INBOUND_CALL_xxx DROP COLUMN DURATION

ALTER TABLE INBOUND_CALL_xxx ADD DURATION AS CONVERT(TIME(0), END_TIME - START_TIME) -- PERSISTED (if needed)


tuning postgresql for large amounts of ram

There are many tweakable constants, initialised via postgres.conf. The most important ones are:

max_connections: the number of concurrent sessions
work_mem : the maximal amount of memory to be used for intermediate results such as hash tables, and for sorting
shared_buffers the amount of memory dedicated to 'pinned' buffer space.
effective_cache_size the amount of memory assumed to be used by the OS's LRU buffers.
random_page_cost : an estimate for the relative cost of disk seeks.

max_connections should not be set higher than needed, connections cost resources even when idle; in most cases a connection would spend more time waiting inside than waiting outside. (at the price of concurrency) A nice rule-of-thumb formula is "number of spindles+number of processors+X"

work_mem is tricky: is can be applied to every subquery, so a query with 5 HASHJOINS might cost 5*work_mem. And for worst-case scenarios, you should also think of multiple sessions consuming this amount (again a reason to keep max_connections low).

shared_buffers is (IMHO) overrated. Normally it is advised to set it to about 1/4...1/2 of all available "free" memory, but I tend to keep it low, and set effective_cache_size to all available "free" memory.

random_page_cost is the cost for a seek+read on the disk. It is relative to the sequential_disk_cost, which is 1. The default (4) for random_page_cost is set too high for modern machines and network storage, normally it can be lowered to between 2 and 1.x. For SSD disks yould even set it to 1.0, since seeking is almost for free on SSDs.


If the vendor is providing their own database you could make their account apart of the owner role at the database level and all you need to do at the system level is grant the account login permissions to their database. This will allow them to have full control over their database and will prevent them from using the account to access other databases you have. If they tell you they need the sysadmin database role for tempdb access that is not true as every user has all the access they need to tempdb by default no other steps needed unless you made changes to the defaults for tempdb. Setting up their account this way will also prevent them from enabling other extended features in sql like mail and xp_cmd_shell which would give them access to the underlying OS.

There is other hardening you can do at the sql server service level to ensure that any sysadmin accounts in sql don't have admin rights on the OS. These include running the sql service and sql agent service as just a user on the box.  Then adding the windows group mssql$....users to the Disk Maintenance and Log on as a batch policy. This will give the two accounts access to run as a service and will also allow sql server to do instant file initialization when it needs to grow the underlying data/log files.
You will also need to grant full control at the NTFS level of the folders for the same group where the data and log files.  Use the SQL Server configuration tool to change the accounts as it sets most of the needed permissions for you.

Here is a basic guide from MSDN while I try to find a more step by step on of the basics that I've used in the past


Which datatype to store XML data in: VARCHAR(MAX) or XML

what factors should I be considering when trying to decide between storing XML in an xml column vs. a varchar(MAX) column

The factors are:

The XML type is queryable / parseable through XQuery expressions, including being able to use FLWOR Statement and Iteration

Data in XML variables and columns can be modified inline using XQuery expressions via XML DML.

XML data is stored as UTF-16 LE (Little Endian), so VARCHAR(MAX) would be a poor choice as it could result in data loss. Hence, the true decision should be between XML and NVARCHAR(MAX), given that NCHAR / NVARCHAR is also UTF-16 LE.

XML data can be validated against an XSD / XML SCHEMA COLLECTION. No validation (outside of ensuring well-formedness) is done if no XML Schema Collection is specified, but this option is not available when using NVARCHAR(MAX).

One major benefit of the XML type is that it is stored in a highly optimized format (not VARBINARY(MAX) as stated in @Oleg's answer) that does not store the exact string representation that you see, but instead has a dictionary of Element and Attribute names and refers to them by their ID. It also removes whitespace. Try the following:
  DECLARE @Test1 XML = N'<Test><TagName>1</TagName><TagName>2</TagName></Test>';

 DECLARE @String1 NVARCHAR(MAX) = CONVERT(NVARCHAR(MAX), @Test1);

 SELECT DATALENGTH(@Test1) AS [XmlBytes],
        LEN(@String1) AS [StringCharacters],
        DATALENGTH(@String1) AS [StringBytes];

 SET @Test1 = N'<Test><TagName>1</TagName><TagName>2</TagName><TagName>3</TagName>
 <TagName>4</TagName><TagName>5</TagName><TagName>6</TagName></Test>';

 SET @String1 = CONVERT(NVARCHAR(MAX), @Test1);

 SELECT DATALENGTH(@Test1) AS [XmlBytes],
        LEN(@String1) AS [StringCharacters],
        DATALENGTH(@String1) AS [StringBytes];

Returns:
<!-- language: lang-none -->

    XmlBytes   StringCharacters   StringBytes
    56         53                 106

    XmlBytes   StringCharacters   StringBytes
    84         133                266

As you can see in the example output above, adding four elements (#s 3, 4, 5, and 6) added 80 characters (hence 80 bytes if using VARCHAR) and 160 bytes to the NVARCHAR variable. Yet, it only added 28 bytes to the XML variable, which is less than it added for VARCHAR (just in case someone was going to argue in favor of VARCHAR over XML because XML is UTF-16 which is [mostly] double-byte). This optimization can save tons of space, and is reason enough by itself to use the XML datatype.


How to check if there is partitions in use in fact tables?

The below script will give you a list of tables, and count of partitions, where there are tables with multiple partitions:

select schema_name(schema_id), name, COUNT(name) from sys.tables t
inner join sys.partitions p on t.object_id = p.object_id
group by schema_name(schema_id), name
having COUNT(name) > 1


If I manually failover my AlwaysOn AG primary replica to the secondary and take the primary database offline will it auto-sync when back online?

This is not possible since database is added to AOAG
You cannot take the db offline unless you remove it from AG

You can see and verify this yourself simply by adding some "TestDB" to your AG and try to take if offline

  Do I have to wait for the sync to finish before manually failing back over to Database A from Database B?

Yes you have to wait for sync to complete in any scenario, if you do not want to lose any data


Performing efficient searches over two columns

An index in 2 columns in the order (MACAddress, TimeStamp) will benefit any reasonable query construct for both

for a specific MAC address
for a MAC address within a range

Why?

The first query uses "equality" (= MacAddress)
The second uses "equality" then "inequality" (= MacAddress AND TimeStamp BETWEEN X and Y)

An RDBMS should traverse the index to find the MACAddress quickly, then looks with the subset for the time range.

It should be sublinear performance

Some reading to explain more (SQL Server specific)

https://www.toadworld.com/platforms/sql-server/w/wiki/9635.index-selectivity-and-column-order
https://stackoverflow.com/questions/797830/how-to-order-fields-on-index-creation-sql-server-2005


database and table design for billions of rows of data

To improve the query:

SELECT h.ticker_id, h.record_date , d.broker_id, d.broker_id, d.amount
FROM DETAIL
INNER JOIN header h
    ON h.id = d.header_id
WHERE
    h.ticker_id = 'A00001'

(ignoring that ticket_id is an INT in the table);

The aspect of the query that will limit the results most is the value of h.ticket_id. To find this quickly there need to be an index on this in table header.

 CREATE INDEX ticket ON header (TICKER_ID)

The database then need to find the HEADER_ID quickly in the detail table:

 CREATE INDEX header ON detail (HEADER_ID)

What is unclear in the question here is why detail has an auto_increment primary key. If this isn't used in any queries, and if HEADER_ID,BROKER_ID is unique, then perhaps those are a better primary key for the table and as an alternate to CREATE INDEX header above:

ALTER TABLE detail
DROP ID,
DROP PRIMARY KEY,
ADD PRIMARY KEY (HEADER_ID, BROKER_ID);

This will also speed up the query as primary keys are quicker to search than secondary keys (where non-index values like d.amount are also needed).


Delete data from huge InnoDB table from MySQL Master and Slaves

Here are some things you should consider:
CONSIDERATION #1
Using DELETE FROM tbl ... LIMIT 5000 is not replication-safe because the  5000 rows being deleted may not be the same order or the same set of 5000 rows on a slave. See MySQL Documentation (Replication and LIMIT). Should a DELETE on a Master get interrupted, it has to rollback. In that event, it could be remotely possible that the number of times the DELETE FROM tbl ... LIMIT 5000 is called may not be the same. Extra calls of the DELETE on a Slave is not critical. Too few calls could be bad. This should not happen.
SUGGESTION : Make sure the row count for RTesAll is identical on the Master and all Slaves.
CONSIDERATION #2
Since you are running a Stored Procedure on the Master, the DELETE command will not begin its execution on a Slave until the completion of the DELETE on the Master and the DELETE command is posted in the Master's Binary Logs. With one Master and 4 Slaves (5 DB Servers), there are a certain of calls to DELETE multiplied by 5.
SUGGESTION #1 : You should add set sql_log_bin = 0; to the Stored Procedure
DROP PROCEDURE IF EXISTS PurgeOlderData;
DELIMITER $$
CREATE PROCEDURE `PurgeOlderData`(In StartDate DATETIME ,In EndDate DATETIME,In NoOfRecordsToDelete BIGINT,In TableName CHAR(50))
BEGIN
    SET sql_log_bin             = 0;
    SET @delete_counter             = 0;
    SET @table_name             = TableName;
    SET @number_of_records_to_delete    = NoOfRecordsToDelete;
    SET @start_date             = StartDate;
    SET @end_date               = EndDate;

    WHILE @delete_counter < @number_of_records_to_delete DO
        SET @varSQL = CONCAT('DELETE FROM ', @table_name,' WHERE recordDate BETWEEN \'',@start_date ,'\' AND \'', @end_date ,'\' LIMIT 5000;');
        PREPARE stmt FROM @varSQL;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        /*SELECT @varSQL;*/
        SET @delete_counter = @delete_counter + 5000;
    END WHILE;
    SET sql_log_bin             = 1;
END $$
DELIMITER ;

to prevent all DELETEs from replicating. If you are deleting 611992998 rows, deleting every 5000 rows requires executing DELETE ... LIMIT 5000 122399 times (That's 611992998 / 5000 rounded up to next integer).
This will prevent having to write to the binlogs 122399 times.
SUGGESTION #2 : Run the Stored Procedure on the Master and Slaves at the same time.

This prevents replication lag
This gets rid of all the rows at the same time
This is best done during maintenance cycles or slow read times

CONSIDERATION #3
Make sure autocommit is enabled on Master and Slaves. Otherwise, all the deletes will pile up in the undo logs inside ibdata1 before either doing all the deletes at once as a single transaction or a huge rollback.
Give it a Try !!!


Are there any known problems caused by having NLS_LENGTH_SEMANTICS set to CHAR on an Oracle database?

According to the documentation:

  Oracle strongly recommends that you do NOT set the NLS_LENGTH_SEMANTICS parameter to CHAR in the instance or server parameter file. This may cause many existing installation scripts to unexpectedly create columns with character length semantics, resulting in runtime errors, including buffer overflows.


get all the count of sub category from the product table in single query with mysql optimization

Seems you want a simple GROUP BY:

SELECT subcat_id, COUNT(*) AS number_of_products
FROM some_table
WHERE status = 1
  AND publish = 1
  AND subcat_id IN (1,2,3,4,33,47)
GROUP BY subcat_id ;

An index on (status, publish, subcat_id) would improve efficiency of the query.


SQL How to show information from others tables in select requet

Instead of the (implicit) inner joins you have to use left joins if you want all rows from the left table regardless of the existence of a matching row from the right table.

SELECT ac.m_art_code,
       ac.m_shop_code,
       sh.shops_name,
       ac.m_user_code,
       us.user_name
       FROM action ac
            LEFT JOIN shops sh
                      ON sh.shop_code = ac.m_shop_code
            LEFT JOIN user us
                      ON us.user_code = ac.m_user_code;


Connection to named instances is handled by SQL Browser Service:

  When an instance of SQL Server starts, if the TCP/IP or VIA protocols
  are enabled for SQL Server, the server is assigned a TCP/IP port. If
  the named pipes protocol is enabled, SQL Server listens on a specific
  named pipe. This port, or "pipe," is used by that specific instance to
  exchange data with client applications. During installation, TCP port
  1433 and pipe \sql\query are assigned to the default instance, but
  those can be changed later by the server administrator using SQL
  Server Configuration Manager. Because only one instance of SQL Server
  can use a port or pipe, different port numbers and pipe names are
  assigned for named instances, including SQL Server Express. By
  default, when enabled, both named instances and SQL Server Express are
  configured to use dynamic ports, that is, an available port is
  assigned when SQL Server starts. If you want, a specific port can be
  assigned to an instance of SQL Server. When connecting, clients can
  specify a specific port; but if the port is dynamically assigned, the
  port number can change anytime SQL Server is restarted, so the correct
  port number is unknown to the client.

  Upon startup, SQL Server Browser starts and claims UDP port 1434. SQL
  Server Browser reads the registry, identifies all instances of SQL
  Server on the computer, and notes the ports and named pipes that they
  use. When a server has two or more network cards, SQL Server Browser
  returns the first enabled port it encounters for SQL Server. SQL
  Server Browser support ipv6 and ipv4.

  When SQL Server clients request SQL Server resources, the client
  network library sends a UDP message to the server using port 1434. SQL
  Server Browser responds with the TCP/IP port or named pipe of the
  requested instance. The network library on the client application then
  completes the connection by sending a request to the server using the
  port or named pipe of the desired instance.


Trouble With Database Permissions for sp_send_mail

You are running into the dreaded 'sandboxed' mode of the EXECUTE AS context, as described in Extending Database Impersonation by Using EXECUTE AS. In short code running under EXECUTE AS USER ... is trusted only inside the database context, not at the instance context.

There are three ways out:

the easy way: mark the current database as TRUSTWORTHY ALTER DATABASE [...] SET TRUSTWORTHY ON;
the correct way out: use code signing
the cheat: use EXECUTE AS LOGIN

If in your environment the dbo of the current database is trusted then you can go with TRUSTWORTHY. It will work, but if this property is set then any db_owner in the current DB can elevate himself to server admin.

If you want a 'correct' solution then:

move this code in an stored proc
sign the stored proc with a certificate
drop the private key (so that it can never be used again to sign anything)
export the public key, import it in [msdb]
create user in [msdb] derived from this certificate
grant necessary permissions (AUTHENTICATE, EXECUTE on sp_send_mail) to the certificate derived user

Trivial, heh? BTW, every time you modify the signed stored proc the signature is lost and the procedure has to be repeated. See Call a procedure in another database from an activated procedure for an example.

I totally don't recommend using EXECUTE AS LOGIN instead.


This can be much simpler, yet, with DISTINCT ON:

SELECT DISTINCT ON (product_id)
       product_id
     , CASE WHEN stock = 0 THEN NULL ELSE warehouse_id END AS warehouse_id
     , stock
     , CASE WHEN stock = 0 THEN NULL ELSE price END AS price
FROM   product_stock
ORDER  BY product_id, (stock = 0), price;

Assuming stock to be NOT NULL.

SQL Fiddle.

About DISTINCT ON:

Select first row in each GROUP BY group?

Postgres has a proper boolean type and one can ORDER BY any boolean expression. FALSE sorts before TRUE sorts before NULL. So rows with (stock = 0) sort behind rows with any other value for stock - except NULL, which would sort last.


JOIN/Union Two tables, removing NULL where possible (similar to pandas concatenate)

I've tried a UNION, FULL JOIN, and SELF JOIN but none of those seem to
  produce the desired result

FULL JOIN works fine. Maybe you were joining on the wrong predicate or didn't coalesce the results from the ID and Date columns (done with ISNULL below)?

db <> fiddle

SELECT ISNULL(T1.[ID], T2.[ID]) AS [ID],
       ISNULL(T1.Date, T2.Date) AS Date,
       [FloorRating],
       [CeilingRating],
       [WindowRating],
       [FanRating]
FROM   Table1 T1
       FULL JOIN Table2 T2
              ON T1.[ID] = T2.[ID]
                 AND T1.[Date] = T2.[Date]
ORDER  BY [ID],
          Date


Why does server name need to be specified when accessing local instances?

Quite simply, client tools need to know explicitly which machine you're trying to connect to. This is like trying to access http://localhost/test_site by typing test_site into your browser.

For SQL Server you can indicate the local machine in a variety of ways:

machine_name\instance
(local)\instance
127.0.0.1\instance
.\instance


The select count(*) from foo inside the returning clause is evaluated before the insert, and then treated as a constant in the returning clause, as explain clearly shows:
> explain insert into foo (a) values (1) returning (select count(*) from foo);
┌───────────────────────────────────────────────────────────────────────────┐
│                                QUERY PLAN                                 │
├───────────────────────────────────────────────────────────────────────────┤
│ Insert on foo  (cost=41.88..41.89 rows=1 width=4)                         │
│   InitPlan 1 (returns $0)                                                 │
│     ->  Aggregate  (cost=41.88..41.88 rows=1 width=8)                     │
│           ->  Seq Scan on foo foo_1  (cost=0.00..35.50 rows=2550 width=0) │
│   ->  Result  (cost=0.00..0.01 rows=1 width=4)                            │
└───────────────────────────────────────────────────────────────────────────┘
(5 rows)

This is confirmed by the documentation where it says that the returning clause may contain column names or value expressions (using those columns) to be returned (saving a query after the insert):
> select count(*) from foo;
┌───────┐
│ count │
├───────┤
│     1 │
└───────┘
(1 row)

> insert into foo (a) values (1) returning 'a', a, (select count(*) from foo);
┌──────────┬───┬───────┐
│ ?column? │ a │ count │
├──────────┼───┼───────┤
│ a        │ 1 │     1 │
└──────────┴───┴───────┘
(1 row)

INSERT 0 1


Is there a oracle SQL query show me how many time I had Downtime in my Database through a year?

Example for Oracle 11.2

Prepare

sqlplus / as sysdba
CREATE VIEW X$_DBGALERTEXT AS SELECT * FROM X$DBGALERTEXT;
GRANT SELECT ON X$_DBGALERTEXT TO <USERNAME>;
CREATE SYNONYM <USERNAME>.X$DBGALERTEXT FOR SYS.X$_DBGALERTEXT;

select startup and shutdown time database.

select  to_char(originating_timestamp, 'YYYY-MM-DD HH24:MI' ), message_text
from X$DBGALERTEXT
where  message_text like '%Starting ORACLE instance%' or
       message_text like '%Instance shutdown%'

Example output

2018-07-31 10:45    Starting ORACLE instance (normal)

2018-07-31 11:12    Instance shutdown complete

2018-07-31 11:13    Starting ORACLE instance (normal)

2019-01-25 10:16    Instance shutdown complete

2019-01-25 14:15    Starting ORACLE instance (normal)


Looks like somebody deleted the log file while it was used by the mongodb process. Deleting log files is such a manner is not a good idea as the process will keep it locked and that means the file will keep occupying space on the disk. Such deleted files entry in the lsof will disappear once the process that is locking it is restarted.

The best way to handle logs files is by using proper log rotation by utilities like logrotate which is available on almost all linux systems.

If you ever face a situation where the log file has grown too big and you want to free up space without restarting the service, the best way would be to truncate the file using - cat /dev/null > /var/log/my_big_log_file.log or just echo "" > /var/log/my_big_log_file.log. Just remember that, this is not a proper solution for handling log files and should be used as a temporary fix.


This has a scent of a parameter sniffing problem.  See what I did there?  Punny, right?

Anyway, Paul White, wrote up a great article on the various approaches available to resolve this issue, so I'm not going to go into detail when you should just read his article instead.  What you can try quickly though, because this is an ad-hoc statement, is to force a recompile using the OPTION (RECOMPILE) hint.  If this fixes your issue, you definitely are running into what I suspect.  However the best way to properly resolve this is to convert your statement into a Stored Procedure and then execute the Stored Procedure passing in the date parameters.  A parameterized Stored Procedure won't fall prey to the current issue and should be reusable as well.


This should be straightforward, probably something like this:

SELECT u.UserID, o.OrderID, p.PassengerID
FROM Users u
  JOIN Orders o ON u.UserID=o.UserID
  JOIN Passengers p ON o.PassengerID=p.PassengerID
WHERE ...

The WHERE condition, if you want the WHERE clause at all, could be e.g. u.UserID=5 to retrieve only the results for a certain user, or p.PassengerID=4 for a certain passenger.

A slightly shorter way of writing the same query:

SELECT UserID, o.OrderID, PassengerID
FROM Users u
  JOIN Orders o USING(UserID)
  JOIN Passengers p USING(PassengerID)
WHERE ...

Note that here the aliases u and p are not needed in the SELECT clause. USING can only be used because the columns have identical names in the two tables being joined.


Relational Databases many records stored in one

What you're describing is called a "many-to-many" relationship. Each receipt can have many menu items; each menu item can be on many receipts.

This is normally handled by creating a "bridge" table.

Let's assume you already have the following tables: menu_item, customer, and receipt. Further, let's assume each one has a primary key column (uniquely identifies a given row), <tablename>_id.

So, each row in receipt would be connected to a customer, and would need to be able to have several menu_items associated with it.

First, you'd add customer_id as a column in the receipt table, to tie the receipt to a customer.

However, you have an indeterminate number of menu items to attach as well. One option would be to have 5, 10, maybe 15 columns, each of which could be tied to one menu item. However, this is messy - you leave a lot of columns empty (even NULL columns consume some space), and you still run the risk of eventually having a customer who orders more than that number of items.

It's much cleaner to add a new table, receipt_item. This table would have its own primary key (receipt_itme_id), and would tie to one receipt (so it has a receipt_id column), and one menu_item (so, add the menu_item_id column). In the case of a restaurant, you might have other columns as well: instructions, to note that the steak should be medium rare, for example. That's just a thought from your example; all a bridge table must have is the ID values from the two tables it connects.

So, each receipt could have anywhere from one to dozens of receipt_item rows connected to it, and each of those ties to a menu_item.


Question on Design for User + User Groups + User Types + Subscriptions Model

I think you overdid normalization:

I dont see the purpose of the user_names table, i would store the names in the user
table
Same for basic_user table, i would store the email and password in the user table as well
A subscription can have multiple statuses? if not i would put id_status in the subscription table


The files are stored in a blob in the sharepoint database and as TDE encrypts all the pages in the database all the files will be encrypted there within.

It's important to notice that while the database is mounted on the server the database server will serve the files unencrypted to the Sharepoint application server and it's clients.

The Sharepoint binary cache will store them unencrypted as well as all the clients. You are only encrypting the data at rest on the SQL Server when using TDE.

You can add to the security by using encrypted connections to the database server and HTTPS to connect to the Sharepoint application but after the files leave the database storage they will be unencrypted.


SSIS data transfers between two remote servers

Yes there will be traffic to Server A during stage 2. This is because the data that has been staged will need to come back to the place that is doing the work (Server A) so that the package can send the data to the target (Server C).

I think you may be doing this the wrong way around to be honest. If Server A has the production database on it then you want to put as little extra load on it as possible. Because of this I would suggest placing your SSIS package on Server B which then only has to pull data from Server A - meaning that there will be no additional processing load on Server A other than satisfying the query that is asked of it.

Then, once the staging has been complete stage 2 can begin which will only consume resources on Server B and Server C. This is a much better approach - I don't see any need to have multiple packages unless you want to split the logic of each stage into their own modules so that they can be maintained separate from one another.

The other benefit to this approach is that you can make alterations to the package and the staging database without having to change anything on your production server - this decoupling allows better separation of duties and will make future maintenance easier for you.

I hope this helps you.


Validate database backup For Innodb tables

Would this work for you?

SELECT database_name, table_name, last_update, n_rows
FROM mysql.innodb_table_stats;

immediately after DR?  To allow verification and avoid the long COUNT(*).


using regexp substitution part in postgres string functions


SQL Server 2012 Data Warehousing Overall Flow

Not 100% what you are getting at, whether you're talking about the order of developing a data warehouse or how the data flows into the warehouse and on to other things.

There is a camp that models all the data into the data warehouse (using dimensional modeling techniques, and only generally informed by the users roles and domain expertise) and then the end users build their analytics on it.

There is another camp that gathers end-user requirements and then builds the warehouse to meet that, identifying the data in the source system.

I tend to be more in the first camp, because users only know what they want once they play with the data and see what's available.


Update Field Added From Alter Table Statement

If you need to do this in a stored procedure, your update has to be dynamic.

Here's a demo in case you get lost on the way.

Create a simple table:

USE tempdb;

CREATE TABLE dbo.whatever
(
    Id INT
);

INSERT dbo.whatever ( Id )
VALUES ( 1 );

GO

This is about what your stored procedure will have to look like:

CREATE PROC dbo.dosomething
(
    @i INT
)

AS
    BEGIN

        DECLARE @sql NVARCHAR(MAX) = N'';

        IF NOT EXISTS (   SELECT *
                          FROM   sys.columns AS c
                          WHERE  OBJECT_NAME(c.object_id) = 'whatever'
                                 AND c.name = 'something' )
            BEGIN
                ALTER TABLE dbo.whatever
                ADD something FLOAT;
            END;

        IF NOT EXISTS (   SELECT *
                          FROM   sys.columns AS c
                          WHERE  OBJECT_NAME(c.object_id) = 'whatever'
                                 AND c.name = 'soforth' )
            BEGIN
                ALTER TABLE dbo.whatever
                ADD soforth FLOAT;
            END;

        SELECT @sql = N'
        UPDATE dbo.whatever
        SET something = @s_i,
            soforth = @s_i
        WHERE Id = @s_i
        ';

        EXEC sys.sp_executesql @sql, N'@s_i INT', @s_i = @i;

    END;

There won't be any errors because the dynamic SQL isn't compiled at this stage.

Then you can run it:

EXEC dbo.dosomething @i = 1;

And validate results:

SELECT *
FROM   dbo.whatever AS w;

+----+-----------+---------+
| Id | something | soforth |
+----+-----------+---------+
|  1 |         1 |       1 |
+----+-----------+---------+

Hope this helps!


intermittent TNS-12533: TNS:illegal ADDRESS parameters

Try any one or both

Try using IP for HOST
Also remove (SERVER = DEDICATED) row from tnsname.ora file.


Getting ORA-00947: not enough values error while inserting date into table

You are only inserting one column value, not both. The second parameter in to_date should be the format used by the first parameter, not another date. Your insert should look like this:

insert into times (departure, arrival) values
  (to_date('2018-05-02 12:40','YYYY-MM-DD HH24:MI'),
   to_date('2018-05-02 15:08','YYYY-MM-DD HH24:MI'));


Overwhelmed with Sensor Data - Excel to SQL Server fails

To help those who follow... I ended up packing the data into a comma separated string for insert into a varchar(max) column. Not what I wanted to do but it works and I'm on my way. Another gotcha with this method is that although varchar(max) can store 2GB the table read from an external connection in excel only returns the first 32765 bytes of the string. To get round that I needs to run a vba script to get the full string using GetChunk() method.

Best.


Dump PostgreSQL schema with foreign keys and referenced data

I am not aware of any tools to do exactly what you are asking right now.  I want to suggest a few options you might have overlooked however that may meet your needs.

Database Cloning

If your database is not being used 24/7 you can clone it for test cases.  This would be done using during times when the db is not in use:

createdb -U postgres new_dbname -T old_dbname

This does a file-level copy of the database, and so it preserves all data and is much faster than a dump and reload.

I very frequently use this technique for cases where I want to run test cases on one db where I can't guarantee that everything will roll back.

Base Backup and Restore

A second thing you can do if you want to bring all data onto another server is to set it up for replication as a master with at least one wal_sender, and use pg_base_backup to clone your db cluster over the network.  Note this is a file-based clone, and so it will be a very network-intensive process (more than a dump) but restore will be a lot faster.

Writing your own Tool

One of the big difficulties in having a generally available tool is that cross-schema dependencies could be quite complex and handling all possible complexity is a bit of a daunting task.  However for specific purposes this isn't too hard.  I would recommend three steps, using pg_dump to get you most of the way there, and then having a script written in one language or another to add what we can hope are modest missing pieces.  I think you could even do it with an sql script run through psql.

Dump schemas only of tables you need.  Create new file.  Something like: pg_dump -t mytable1 -t mytable2 -t mytable3 > my_partial_dump.sql
Export selected data to csv, and wrap using COPY statements.  This could be done using psql along with echo and COPY TO STDOUT.  This is appended to the partial dump.
Dump the schema as you are, and append this to the partial dump.

The advantage of a purpose-built tool if this is what you need is that you can manually manage the complex part of it and thus have a smaller, simpler tool than you might otherwise require.


Unable to retrieve query plan of running query because of blocking

Try flipping this predicate from

where object_name ( s2.objectid, s2.dbid) = 'sp_name'

to something like

where object_id = object_id('sp_name')

You might get a simpler plan that aviods whatever resource is blocked in TempDb.


SSIS - sp logs counters in debug mode but not from agent job

What this appeared to boil down to was the TargetServerVersion property for the project. What I hadn't realised was, where SSDT had been upgraded, the TargetServerVersion defaults to 'SQL Server 2017', whereas my target instance is 2016.

I originally tried to "downgrade" the target server version in the original project, but was still getting the same problem. I was also getting some informational messages about the script task in the child package, saying that something hadn't gone as planned when "downgrading". I eventually recreated the entire project with the TargetServerVersion set to 2016 from the beginning. This has done the trick, as all is working as it should.

The only other thing I changed was that I deployed the project using SQL Server 2016 Deployment Wizard, instead of 2017 as before. However, I'm not too sure this would have been the problem.

Moral of the story, always check your TargetServerVersion.

I have provided this as the answer, in case other people experience such an issue. If, in future, any evidence presents itself to indicate that the above was not the cause of the issue, I will gladly change the answer.


It's actually surprisingly easy using DAY(), as 1-7 will be 1, 8-14 will be 2, 15-21 will be 3, 22-28 will be 4 and 29-31 will be 5. Doesn't matter what the name of the day is, that will always hold true.

select case when DAY(yourdate) <=7  then 1
            when DAY(yourdate) <=14 then 2
            when DAY(yourdate) <=21 then 3
            when DAY(yourdate) <=28 then 4
            else 5
        end as occurance
from yourtable;

(case short-circuits)


How to find the price based on the given range

I don't get the same output but I think there is an error in your sample with the last row (=1500) and/or a price for QTY>30 is missing.

You can look at this SQL Fiddle and SQL Server query:

With list as (
    Select n = ROW_NUMBER() over(order by (select 1)) From (
        Select n = 1 From (values (1), (1), (1), (1), (1), (1), (1), (1), (1), (1)) as x1(n)
        Cross Join (values (1), (1), (1), (1), (1), (1), (1), (1), (1), (1)) as x2(n)
    ) as x
)
Select pr.PROD, pr.[date], QTY = count(l.n), Price = sum(pi.price) From list as l
Inner Join Price pi on l.n >= pi.srange and l.n <= pi.erange
Inner Join (
    Select p1.PROD, p1.date, previous = coalesce(SUM(p2.QTY), 0), p1.QTY
    From Product as p1
    Left Join Product  as p2 on p1.PROD = p2.PROD and p2.date < p1.date
    Group By p1.PROD, p1.date, p1.QTY
) as pr on pr.PROD = pi.PROD and l.n > pr.previous and l.n <= (pr.previous+pr.QTY)
Group By pr.PROD, pr.date

Output:

PROD | date         | QTY   | Price
A    | 2015-01-01   | 5     | 520
A    | 2015-02-01   | 10    | 1100
A    | 2015-03-01   | 5     | 600
A    | 2015-04-01   | 10    | 1200

With 1 more row in Price such as ('A', 31, 50, 150), it gives the output you want:

PROD | date         | QTY   | Price
A    | 2015-01-01   | 5     | 520
A    | 2015-02-01   | 10    | 1100
A    | 2015-03-01   | 5     | 600
A    | 2015-04-01   | 10    | 1500

SQL Fiddle with Price > 30.


Why didn't this statistics object on a filtered index auto update?

Fatal flaw in filtered indexes/statistics: churn is measured by % of row count for the table, not the index/statistic. Paraphrased from my blog post here:

  Filtered indexes don't benefit from auto-updating of stats based on a percentage change of the subset of the table that is identified by the filter predicate; it is based (like all non-filtered indexes) on churn against the whole table. This means that, depending on what percentage of the table is in the filtered index, the number of rows in the index could quadruple or halve and the statistics won't update unless you do so manually. Kimberly Tripp has given some great information about this (and Gail Shaw cites an example where it took 257,000 updates before statistics were updated for a filtered index that contained only 10,000 rows):http://www.sqlskills.com/blogs/kimberly/filtered-indexes-and-filtered-stats-might-become-seriously-out-of-date/Also, Joe Sack has filed a Connect item that suggests correcting this behavior for both filtered indexes and filtered statistics:http://connect.microsoft.com/SQLServer/feedback/details/509638

You shouldn't rely on auto-update statistics for filtered objects. If the cardinality is important and the subset changes a lot more than the whole table, you will need to manage these manually.


Perhaps you have Management Studio 2008 and your localhost is a newer version. If this is the case, you should always use the most recent version of Management Studio even if you sometimes manage lower versions. You can determine the version of the engine you're managing by looking at the top-level node in Management Studio's Object Explorer - 2008 is going to show 10.0.xxxx, 2008 R2 is going to show 10.50.xxxx, and 2012 is going to show 11.0.xxxx. You can tell what version of the client tools you have by going to Help > About.

Another possibility is that you are still using the RTM version of Management Studio 2008. If this is the case, you should apply SP3 (if you are in fact on 2008) or apply SP2 (if you are on 2008 R2, which a lot of people confuse with 2008).

Your best bet, IMHO, is going to be to uninstall the 2008 client tools entirely and install Management Studio 2012 (in SP1, the client tools are now free and fully functional). Not only does it have far fewer bugs like this, and plenty more features, but you won't have to do this later when you have to manage your first 2012 instance (which the 2008 tools aren't going to be very good at).

In the meantime, instead of using the UI as a crutch to create a database, you could learn the DDL and perform this in a query window, which won't have any problems:

CREATE DATABASE MyNewDatabaseName;

You can learn more about the CREATE DATABASE syntax in Books Online.


Log Shipping- Can I alter the schema of a table that is being used in the secondary instance?

Can I alter the schema (add columns, remove columns etc.) of the table in the Primary instance/db while the table is being used in the Secondary instance/db?

Yes you can. You can run alter command on primary database and at same time a select query can run on secondary database accessing same table this is perfectly fine and allowed. But when the changes you made on primary are replayed on secondary then the users might be disconnected and unless restore operation completes you wont be able to run query. This is subject to condition that you have selected check box Disconnect users in database while restoring


How to AUTOINCREMENT against another column rather than the whole table in sqlite?

I'd just create a view on top of the table.

The view uses the analytic function row_number() to provide what you needed.  You'll need a column to sort on.  That could be your AUTOINCREMENT column.

https://www.sqlite.org/windowfunctions.html

select channel_id, text
  ,row_number() over (partition by channel_id order by identity_column) as id
from unnamed_table


Recursive update for tree structure in PostgreSQL

First, lets try to generate the chnum column using a recursive query (without using the generated, of course):

WITH RECURSIVE r AS (
        SELECT c.id, c.parentid, ARRAY[row_number() OVER(ORDER BY c.id)] AS chnum
        FROM chapters c
        WHERE c.parentid IS NULL
        UNION ALL
        SELECT c.id, c.parentid, array_append(r.chnum, row_number() OVER(PARTITION BY c.parentid ORDER BY c.id)) AS chnum
        FROM chapters c JOIN r ON c.parentid = r.id
)
SELECT r.id, array_to_string(r.chnum, '.') AS chnum, parentid FROM r
ORDER BY r.chnum;

Now, we just need to take this result and update every id that doesn't match the chnum:

WITH RECURSIVE r AS (
        SELECT c.id, c.parentid, ARRAY[row_number() OVER(ORDER BY c.id)] AS chnum
        FROM chapters c
        WHERE c.parentid IS NULL
        UNION ALL
        SELECT c.id, c.parentid, array_append(r.chnum, row_number() OVER(PARTITION BY c.parentid ORDER BY c.id)) AS chnum
        FROM chapters c JOIN r ON c.parentid = r.id
)
UPDATE chapters c
SET chnum = array_to_string(r.chnum, '.')
FROM r
WHERE c.chnum IS DISTINCT FROM array_to_string(r.chnum, '.') AND c.id = r.id;

See this SQL Fiddle that I used to test it.

I'm making some assumptions here:

The id column is primary key.
The order is defined by the id column, probably a pos column or after/before thing would be better.

Also, unless it is a really huge table, I wouldn't store the chnum result, instead I'd create a view with the first query and just use it on the code.


In postgresql binary replication, indexes are replicated or not? And if i dont like it?

The postgres replication works at the WAL (Write-Ahead-Log) level, which basically contains a copy of each disk block that is modified. The slave just streams the WAL records and play them back. So, everything that modifies the disk, which includes indexes, are replicated.

This also means that the slave will use exactly same amount of disk space (for the database) as that of the master, and will use exactly the same query plan as that of the master etc.

It is not possible to change that behavior as long as you are using postgres in-built replication.

When the slave is promoted as master, there is no need to run reindex as index is already available.


I know this is really late, but you really need to capture the output of SHOW ENGINE INNODB STATUS; during that query to see why it's waiting.

If it happens a lot during a specific time, it would be easy to just grab that output every x seconds and hope you capture it (or perhaps artificially generate the load).


Referring system views and functions in production functions

There should be no real performance problems related to using the system objects to build your T-SQL.

Consider also that building your dynamic T-SQL is probably a much lighter load on your server than actually running the T-SQL statements that you generate.

There are also INFORMATION_SCHEMA views, but these only partially cover the system objects.  I mention this only to warn you that, although once recommended, it has many limitations and I do not use them any more.

Likely the biggest issue for your code is that system objects do change over time.  Probably most changes would not affect your code generation, but you should plan in advance before upgrading to a new version of SQL Server.

SQL Server documentation usually details out the changes for the next upgrade.

For example, here is a link for SQL Server 2014's breaking changes.
https://msdn.microsoft.com/en-us/library/ms143179.aspx#SQL14


What is the recommended way to backup a MySQL/Amazon RDS database to S3?

The recommended way to back up RDS is with automatic backups and DB snapshots.  DB snapshots are basically the same as EBS snapshots, which are stored in S3 behind the scenes, but are only available within the same region.

If you need cross-region fault tolerance (good plan!), there is no way to restore your data  in another region without doing it "the hard way" from a mysqldump.  Your alternatives are to back up using mysqldump (slow and terrible for any reasonable sized dataset), or set up your own EC2-based slave in another region and back that up using any available method (xtrabackup, EBS snapshots, etc).  However, then you are back to managing your own MySQL instances, so you might as well abandon RDS entirely.

For my money, RDS provides absolutely no benefits in really any way and a whole lot of disadvantages in performance, flexibility, and reliability.  I would ask yourself what value RDS provides to you.


How to restore 11gR2 fullbackup to 12c database in rman

This is not going to work. Oracle binaries of a certain version correlate to the Oracle Database dictionary of the same version.

RMAN is a backup and recovery tool. It is NOT an upgrade tool.

You need to restore and recover you database on 11g binaries. From there you can upgrade your database to 12c.

You can have many versions of Oracle Database installed on the same host. The different binaries go into different ORACLE_HOME.


Its not a good idea to run the backup against the Primary. The extra load you adding on the Primary might cause heartbeat failures and trigger an election. Now in your case, overloading the primary might caused an election, you can see that info on your logs, but the other secondary didn't become new primary which expected to. I see some cases why didn't. 1) Replication lag was big 2) you run the replica set on the same server or storage so all nodes get affected but here logs are your best friend on what really happened.


Change recovery mode and enforce a transaction log backup?

Yes in general that is a good approach.
As Shanky commented if you take a manual backup then the chain within BackupExec will be broken.

  If you have the ability to kick all the users on the server and can wait for the transaction log to be backed up by BackupExec then you will find it easier to start the work without involving another team.  Otherwise you will want to get the backup team to take the log backups before and after you have shrunk the data.

Only Shrink the database if you really have to reclaim the disk space otherwise leave it as it is. Brent Ozar has a lovely rant on shrinking, especially when scheduling it here and below is an excerpt from Paul S. Randal blog. The long and short of it is that it takes processing power to grow and shrink a log, this can be an unnecessary burden on an SQL Server.

  Shrinking the log is necessary if your log has grown out of control, or as part of a process to remove excessive VLF fragmentation (see Kimberly’s excellent posts on this here and here). However, shrinking the log should be a rare operation and should not be part of any regular maintenance you perform.


How to remove triggers from the restored database in SQL Server

You have a database trigger that logs DDL event to a table ddl_DBEvents in the database DBMonitor.

You can either delete or disable the trigger from SQL Server Management Studio in the Object Explorer under YourDatabase - Programmability - Database Triggers.

You could also disable or delete the trigger from code:

DISABLE TRIGGER dbtrg_DBEventsmodel ON DATABASE;
DROP TRIGGER dbtrg_DBEventsmodel ON DATABASE;

References:

DISABLE TRIGGER (Transact-SQL) - Books Online
DROP TRIGGER (Transact-SQL) - Books Online


Can I edit queued MySQL binary logs for replication?

Plan A:

Can you avoid updating that table entirely?  If so, the Slave can be configured to have replicate-ignore-table on the Slave.  If it is an entire database (or could be moved to be such), then you can use the more efficient binlog-ignore-db on the Master.  Caveat -- If that table is needed on the Slave, well, it won't be there (or at least not up to date).

Plan B (close to what you are asking about):

SET SESSION sql_log_bin = 0;
UPDATE ...
SET SESSION sql_log_bin = 1;

Again, having the table inconsistent between Master and Slave leads to various Caveats.

Reference: https://dev.mysql.com/doc/refman/5.7/en/set-sql-log-bin.html


In SSMS settings, make sure the SET QUOTED IDENTIFIER box is checked. That should fix it.


Why my query does not return correct data (NORTHWIND)?

There are two orders with an orderdate=1997-09-05.  One for employee=4 and one for employee=7.  Now, for employee=4, there are 156 rows on the order table and for employee=7, there are 72 rows on the order table.  That totals to 228.  Your query is returning all orders where the employeeid matches those two id's.

Alter your query to include a check for o2.OrderDate=o.OrderDate

SELECT *
FROM orders o
WHERE EXISTS (
        SELECT 1
        FROM orders o2
        WHERE o2.EmployeeID = o.EmployeeID
            AND YEAR(o2.OrderDate) = 1997
            AND MONTH(o2.OrderDate) = 9
            AND DAY(o2.OrderDate) = 5
            AND o2.OrderDate = o.OrderDate
        )

Based on your example, you would get the same results by simplifying the query to:

select * from orders where orderdate = '1997-09-05'


In my case this turned out to be caused by not enough memory

I concluded this after running some of the commands found in this article:
http://www.cyberciti.biz/faq/linux-check-memory-usage/

egrep --color 'Mem|Cache|Swap' /proc/meminfo will output how much memory is available. Don't forget that Mongo itself will require memory, so even if you have got a bit of free mem, it may not be enough.

I restarted my server, which killed a few of the memory sucking processes, and this for now has solved my problem.


Mysql Master-Master with additional slaves

What you want is server1 to binlog not only its own updates, but also those updates he received from server2, so that slave1 will get all updates.

You just need to tell server1 to do this, which can be accomplished by setting log-slave-updates to yes.

You might want to set this option on server2 too, if the server topology should change someday (as Rolando kindly remarked in the comments below).

This does not loop endlessly as the default setting for replicate-same-server-id is 0 (and cannot be changed as long as log-slave-updates is on). This setting set to 0 means the slave will skip statements he receives with his own server-id.

Your infrastructure decision in general seems fine to me.


How could I connect to MySQL database running on WAMP server from other computers in same LAN?

WAMPServer is primarily designed for use on a single machine i.e. a developer writing and testing code locally on that PC.

So to protect the beginner from his/her self the Apache & MySQL configuration by default is setup so no access can be made to the Apache or MySQL server form anywhere but the PC running Apache and MySQL.

When changing this configuration great care should be taken to only allow access that you intend and not global access. So be VERY CAREFUL with any help/tutorial that suggest adding Allow from all or Require all granted as they either dont really know what they are doing or the help is based on giving access to the site to the whole internet.

To allow access to phpMyAdmin you need to change the Alias configuration for phpMyAdmin, so edit wamp\alias\phpmyadmin.conf and add an instruction to allow access from any ip address on your internal network like so :-

Alias /phpmyadmin "d:/wamp/apps/phpmyadmin4.1.14/"

<Directory "d:/wamp/apps/phpmyadmin4.1.14/">
   Options Indexes FollowSymLinks MultiViews
   AllowOverride all
  <IfDefine APACHE24>
    Require local
-->     Require ip 192.168.0
  </IfDefine>
  <IfDefine !APACHE24>
    Order Deny,Allow
      Deny from all
      Allow from localhost ::1 127.0.0.1
-->     Allow from 192.168.0
    </IfDefine>
  php_admin_value upload_max_filesize 128M
  php_admin_value post_max_size 128M
  php_admin_value max_execution_time 360
  php_admin_value max_input_time 360
</Directory>

If you actually want to access MySQL via the mysql.exe processor and other tools like it from another IP address you have basically the same problem.

MySQL user account are made up of 2 parts, a UserId and a Host. The Host part tells MySQL where this userid is allowed to connect from. If you look using phpMyAdmin at the user accounts that are pre-created in MySQL you will see that the Host part of all the account is either 127.0.0.1 or localhost or ::1 all of which equate to The PC running MySQL. Again for the security of the beginner no access allowed from outside the PC running MySQL.

So to use mysql.exe etc, I suggest you create a new MySQL User Account, lets say MAKhan with a Host of 192.168.0 AND OF COURSE A PASSWORD. That account will them be allowed to login to MySQL from any ip on your local network. You will of course have to give rights to this new account so that it can access some or all of the databases on the MySQL server.


How to use GENERATED from SELECT with JOIN?

According to the MySQL documentation, GENERATED columns cannot have subqueries:

Generated column expressions must adhere to the following rules. An error occurs if an expression contains disallowed constructs.

Literals, deterministic built-in functions, and operators are permitted. A function is deterministic if, given the same data in tables, multiple invocations produce the same result, independently of the connected user. Examples of functions that fail this definition: CONNECTION_ID(), CURRENT_USER(), NOW().

Subqueries, parameters, variables, stored functions, and user-defined functions are not permitted.

...

What you could do, is use a view:
CREATE VIEW DietDB.Servings_All        -- pick an appropriate name
AS
SELECT s.*,
       CAST((fi.kcal * s.units) / 100 AS UNSIGNED INT) AS kcal
FROM DietDB.Servings AS s
  LEFT JOIN DietDB.FoodItems AS fi
    ON s.FoodItem_id = fi.id ;


Postgres : Executing SELECT within a transaction does not return most recent rows

I think your problem is simply having a BEFORE trigger - it fires before the row is inserted which cannot appear in the view yet. Change it to AFTER and (after considering Craig's suggestion) you are done.


Is there any relation between BSON storage for mongoDB and MySQL storage?

In each database, there is no one answer to what size something is when it is stored.  In MongoDB there are several factors to take into account, for example:

What types are being stored (BSON having more types than JSON, and with each getting different allocations)
Should you include the (16 byte) record header?
Is the collection you are storing the data in using powerOf2Sizes?
Are you using the default storage engine? (pluggable engines were just announced at MongoDB World 2014 and Toku has provided a fork with very different storage characteristics for some time)
Do you need to include index size in the storage calculation?
Does running a compact/repair alter the storage characteristics?

That note about storage engines is a good one.  I am not a MySQL expert, but I know there are multiple options there (MyISAM, ARCHIVE, MERGE and others) that will again have very different storage characteristics just within MySQL itself.

There's no easy answer on this one I'm afraid - you are going to have to tightly control the data being stored, look for equivalent/fair types across the two databases to store.  Then you will need to familiarize yourself with the intricacies of each in terms of how to measure storage and how different actions might impact that storage.


System.OutOfMemoryException SQL Server 2012 and 2008R2

The most likely problem is a memory limitation on the machine running Management studio. We have this problem some times when the client laptop only has 2GB-4GB of RAM. You could use the Export data function in Management Studio or the SQL Server "bcp Utility"

SQL Server Export Wizard: Right-Click on the Database > Tasks > Export Data

Bulk copy utility (bcp): Below is the sample syntax that we use and also links to the Microsoft documentation, note that xp_cmdshell needs to be enabled.
Sample syntax (all one line):
exec master..xp_cmdshell 'bcp mydatabase.dbo.mytable out C:\Myfolder\MyFile.csv -c -t, -T -S ServerName\InstanceName'

Microsoft Link: https://msdn.microsoft.com/en-us/library/ms162802.aspx


The GUI is showing the result of looking at the table design, which shows IDENTITY(1,1) for the column in question.

Run the following code, then check the definition of the identity value by using the GUI; you will see the GUI reflects the design of the table, which is independent from the actual next value of the IDENTITY column.

USE tempdb;

CREATE TABLE dbo.IdentTest
(
    ID BIGINT NOT NULL IDENTITY(1,1)
);

INSERT INTO dbo.IdentTest DEFAULT VALUES;
SELECT * FROM dbo.IdentTest;

DBCC CHECKIDENT('IdentTest', RESEED, 400000000);

INSERT INTO dbo.IdentTest DEFAULT VALUES;
SELECT * FROM dbo.IdentTest;

After running this, if you look at the GUI, you see:

If you "script" the design of the table; you get the following:

USE [tempdb]
GO

/****** Object:  Table [dbo].[IdentTest]    Script Date: 2015-10-27 4:54:15 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[IdentTest](
    [ID] [bigint] IDENTITY(1,1) NOT NULL
) ON [PRIMARY]

GO

Note the scripted table definition still shows IDENTITY(1,1), since that is the definition of the table, which is unchanged by using DBCC CHECKIDENT.  The MSDN page on DBCC CHECKIDENT says the following:

  Checks the current identity value for the specified table in SQL Server 2016 and, if it is needed, changes the identity value. You can also use DBCC CHECKIDENT to manually set a new current identity value for the identity column.

Note this does not mention anything about changing the definition of the table itself.


How to remotely run commands for a DB2 instance, preferably using ibm_db?

Use the SYSIBMADM.DBMCFG  administrative view for this.


SQL Server 2012: Generate Scripts from command line

Best is to use Powershell - if you are going to use it frequently. You can refer to Automated Script-generation with Powershell and SMO.

Also, SQL Server PowerShell Extensions (SQLPSX) are of great value when working with Powershell. All the modules are having help files e.g. Get-SqlScripter.

Get-SqlDatabase -dbname database1 -sqlserver server | Get-SqlTable | Get-SqlScripter | Set-Content -Path D:\scripts\script.sql
Get-SqlDatabase -dbname database1 -sqlserver server | Get-SqlStoredProcedure | Get-SqlScripter
Get-SqlDatabase -dbname database1 -sqlserver server | Get-SqlView | Get-SqlScripter

For third party tools, highly recommend to check out (there are many third party tools out there, but below ones I have used and they are great):

Redgate's SQL Compare and Data Compare
SSMS Tools Pack by Mladen Prajdić


MySQL: Get number of rows of table with specific name for all databases

You don't need to query all databases, and anyway you can't. You need to query only one database called information_schema, which contains all metadata about your databases (table structures, etc).

Here is the query you want:

SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_ROWS
    FROM information_schema.TABLES
    WHERE
          TABLE_NAME LIKE '%user%'
          OR TABLE_NAME LIKE '%member%';

Notes:

You don't need any permission on information_schema. But if there is some table you can't access (because you don't have permissions) you will not see it in the results.
The number of rows is MySQL estimation. It is never exact, sometimes it is even inaccurate. If this is not enough for you, you will need to run a query for each table to count the rows. There is no way to get the actual row count with 1 query.
I write the name of system databases, tables and columns with their actual case (information_schema is written lower case). For some reason, this is different than the convention used by the documentation and most technical articles (INFORMATION_SCHEMA). Just use the practice you prefer.


How to truncate an SSAS Tabular partition

In SQL Server Management Studio you can connect Object Explorer to SSAS, expand to the table, right click, choose Partitions, then click to process one or more partitions and do a Process Clear:


You can always check MySQL manual if a variable is dynamic: http://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_lower_case_table_names

Unfortunately lower_case_table_names is not. So you have to restart MySQL to make this variable picked up.


PostgreSQL/Psycopg2 upsert syntax to update columns

You can use the EXCLUDED keyword to access the values passed to INSERT. No need to pass them twice:

insert_sql = '''
   INSERT INTO {t} (id,col1, col2, col3)
        VALUES (%s, %s, NULLIF(%s, 'nan'), NULLIF(%s, 'nan'))
        ON CONFLICT (id)
        DO UPDATE SET
            (col1, col2, col3)
            = (EXCLUDED.col1, EXCLUDED.col2, EXCLUDED.col3) ;
'''

See examples of use in Postgres documentation about ON CONFLICT.


MySQL - huge Dictionary memory allocated

Sorry for the late answer, but I've been running into a similar situation--3.22 GB allocated for an Innodb data dictionary and eventually my server runs out of RAM and restarts MySQL.

From what I have read, there is no way to manage the size of the Innodb data dictionary in MySQL 5.5, but apparently there is a way in MySQL 5.6 and in Percona Server, so if you need to manage limits for the data dictionary, upgrade to MySQL 5.6 or newer.

According to the Percona guys and authors of High Performance MySQL, every time an innodb table is opened, an object for that table is loaded into the data dictionary. That object doesn't get removed, so the size of the dictionary increases over time. They also say that an overlarge Innodb data dictionary causes problems with database servers that have thousands of large tables, which it seems like you certainly have.

High Performance MySQL pg 356

Percona Server InnoDB Data dictionary

MySQL 5.6 InnoDB Data Dictionary


Saving a variable globally in SQL server it self and use it when required

Don't.

While is possible to use context_info, that is a really really really bad choice for a password. All users with VIEW SERVER STATE will see it in sys.dm_exec_sessions. SQL Profiler will not know what you're doing when you set it and will not obfuscate it in produced events. And more similar badness.

Instead use proper encryption hierarchy. Do not encrypt the data by passphrase, is never the right choice. Instead encrypt it with a symmetric key. Then open the key in the session using the password, see OPEN SYMMETRIC KEY. The key will become available in your session and the data can be automatically decrypted. This is the recommended way, don't try to outsmart it.


Using the correct database when calling a system stored procedure in SQL Server 2008

You need to mark your stored procedure as a system object to get the behaviour that you want (full example below tested as working on 2008 SP3)

USE master;
GO

CREATE PROCEDURE dbo.sp_mysp
AS
SELECT *
FROM INFORMATION_SCHEMA.TABLES

GO

USE tempdb;
EXEC dbo.sp_mysp /*Returns tables from master*/

GO

USE master;
EXEC sys.sp_MS_marksystemobject sp_mysp

GO

USE tempdb;
EXEC dbo.sp_mysp /*Returns tables from tempdb*/

This is an undocumented approach and may not work in future versions.


Ensure total order in table via constraint

This sounds like an occurrence of: can the database enforce constraints across rows?
(I assume that the X sign in the formula means "such that")

As a general rule, the answer is no, because when a row is being modified by a transaction, the state of the other rows is always uncertain due to possible modifications by not-yet-committed concurrent transactions.

An application may handle this correctly by implementing an adequate locking strategy preventing concurrent writes, or checking the constraint at the serializable isolation level, but that does not prevent a random session to just issue an UPDATE in the table without these safety measures, that would potentially result in quietly violating the constraint.

There are few kinds of table-level constraints that the SQL engine knows how to enforce, like unique constraints and exclusion constraints, but your formula doesn't look like an expression that could be used by such constraints.


Large Logfile full but very small tlog backup

You should check log_reuse_wait_desc of sys.databases after taking log backup to find out the reason.

Log backups just try to clear the log, but it can happen that it's impossible.

In this article sys.databases (Transact-SQL) you can find possible reasons WHY the log cannot be cleared, it can be open transaction, replication, mirroring, ecc.

If the log cannot clear it will grow. Once the cause of log retention is gone, log is cleared and can be reused.

One natural cause of this is full backup.
If your db is large enough and full backup takes some hours to complete, even if log backups can run concurrently and their size is small as usual, for the whole duration of full backup the log cannot be cleared.

This means that you log file should be large enough to accomodate all the data modifications for at least these some hours.

  Can somebody explain how a full log file of 10GB can generate such a
  small log backups?

It's not a log size to generate log records :)

It's your database writing activity.
If you run log backups every 15 minutes and they are about 700Kb it means that your activity generates 700Kb of log records every 15 minutes.

What you are asking about instead is what can it be to prevent my log to be cleared for such a long time that it accumulates 10Gb of data and still cannot be truncated ?

If the log was not truncated at the last log backup, it does not imply that the next log backup will grab more log records. The next log backup will start with the next record to the record where the previous backup was stopped. Log backups are not cumulative.

I will give you an example:

I opened transaction and went home without doing commit or rollback. Log backups run regularly every 15 minutes and every log backup grabs 1Mb of log records. I return to work after 1 week of illness. My transaction is still open! For one week the log could not be truncated. My log backups were still 1Mb, but my log is almost full: it accumulated 1Mb*4 *24*7 Mb=672Mb.

Now that I'm back, I finally issue a commit. The next log backup is still 1Mb but it could finally truncate the log.

  So in my case, it could be that something is blocking the log truncation => log file grows but for several weeks a log backup of 700KB can be taken?

Yes, exactly. You should find this "something" that blocks log truncation.


Perfomance of CREATE INDEX vs CREATE INDEX CONCURRENTLY in PostgreSQL

2ndQuadrant says:

  [CREATE INDEX CONCURRENTLY] must wait for all existing transactions to finish before starting the second phase on index build. This guarantees that no new broken HOT chains are created after the second phase begins.

I've seen CREATE INDEX CONCURRENTLY take over 90 seconds (before I aborted it) when creating the same index not CONCURRENTLY took 0.5 seconds. So it can be much slower.

There was no write activity on the table in the mean time, but other transactions were open on other tables. I think 2ndQuadrant mean that all transactions must end, not just those which have already written to the table in question.


It's not possible to scaffold Triggers, and it doesn't make much sense since they are a database specific type of object (not sure what they could map to in C#?). They don't directly describe the database model, as opposed to Tables, Columns, and Keys. What is your end goal that you wanted to scaffold the Triggers?
Please see Entity Framework Core - Reverse Engineering documentation for more information.
Particularly the How it works section details what is scaffolded from the database:

Reverse engineering starts by reading the database
schema. It reads information about tables, columns, constraints, and
indexes.
Next, it uses the schema information to create an EF Core model.
Tables are used to create entity types; columns are used to create
properties; and foreign keys are used to create relationships.
Finally, the model is used to generate code. The corresponding entity
type classes, Fluent API, and data annotations are scaffolded in order
to re-create the same model from your app.


This could be due to one of these issues:

A DNS problem: Your workstation does not ping the IP address associated with this host. resolution: Try to ping the LRSERVER. If it does not ping properly, add the LRSERVER ip address in your host file.
A firewall issue: the network traffic between your workstation and the SQL Server instance goes through a firewall which blocks the ports required by SQL Server. resolution: Ask the firewall administrator to open the required ports.


Stored procedure variable storage takes long time to execute

Don't use FIND_IN_SET ( SELECT GROUP_CONCAT ... ).  Instead JOIN the two tables together.


Suggestion for enforcing unique constraint in a table where soft delete is implemented

Why not somthing simple like this:

User enters position 'President' and it does not exist in the table.

Insert Into table

User deletes position 'President'.

Update table set isDeleted = 1

User changes mind and again enters a position 'President'.

Update table set isDeleted = 0