trying to get more rows from the table than there actually exist

declare @t table(StudentID int, Month int, Year int, Present char(1));
    insert into @t
    select 1, 1, 2016, 'P' union all
    select 1, 12, 2016, 'P' union all
    select 1, 4, 2017, 'P';

    select y.StudentID,
           y.y, m.m,
           isnull(t.Present, 'A') as P
    from
    (
    select StudentID, year as y
    from @t
    group by StudentID, year
    ) y
    cross join

    (
    select number as m
    from master..spt_values
    where type = 'p' and number between 1 and 12
    )m
    left join @t t
       on t.StudentID = y.StudentID and t.year = y.y  and t.month = m.m;

Explanation: we need as much rows as the number of students per number of years per 12 month, so I use cross join of distinct pairs student, year and 12 months.

As we have SQL Server 2000, we cannot use Row_number() to generate 12 values, niether VALUES, so we can join to a number table (I used spt_values even if I personally have a number table until 10000000) or we could generate in other ways, for example like this :

select 1 as m union all select 2...union all select 12

And finally join the presence list to a generated 'calendar' for every student


IF condition on temporary table takes long time for query execution

It is improper to "group by col1" but fetch col2 unless they col2 is either directly derivable from col1 or is an "aggregate".

The ORDER BY in the derived table will be ignored (once the two syntax errors are fixed).  Why do you have it?

table2, as the query stands, needs INDEX(col1, col2).

Break the query up into two in order to avoid the IF clauses.  Something like

SELECT ... JOIN ...
UNION
SELECT ... LEFT JOIN WHERE temp.col1 IS NULL AND ...

That will need parens around it before doing the outer GROUP BY.


Is there a psql equivalent of bash's reverse-search-history?

A conversation about this on GitHub led to an interim "quick fix" solution:

  open ~/.editrc and add:

  bind "^R" em-inc-search-prev

Apparently the bigger issue is that PostgresApp uses libedit instead of libreadline. And supposedly libedit lacks some command-line features of libreadline, so until PostgresApp is compiled against libreadline, just use the above quick fix.


Will my query continue to execute after I shut off my computer

Probably not.

The server will detect that the TCP connection has been closed sooner or later and then try to cancel the query.

You may have success with suspend instead of shutdown, but it's not guaranteed to work.

Your best option is to connect to the server (or to some other always-on server) and run the query there using the psql command-line in a  tmux, or screen session etc.

PgAgent may also be worth considering.


Why is the ORDER BY clause in a view ignored as soon as it's called with a WHERE claue?

The query you posted is not valid for creating a view; running CREATE VIEW xy AS for this query will result in an error. Are you using a TOP clause?

A view, being a table expression (a set), can't have the order defined, since that would be against the principles of a relational model (there is no order for rows in a relational table - a set is an unordered collection of tuples). Same goes for other table expressions - derived tables, CTEs etc.

From BOL article about the ORDER BY clause:

  The ORDER BY clause is not valid in views, inline functions, derived
  tables, and subqueries, unless either the TOP or OFFSET and FETCH
  clauses are also specified. When ORDER BY is used in these objects,
  the clause is used only to determine the rows returned by the TOP
  clause or OFFSET and FETCH clauses. The ORDER BY clause does not
  guarantee ordered results when these constructs are queried, unless
  ORDER BY is also specified in the query itself.

Long story short: Use the ORDER BY clause in the outer query that references the view. Do not use it in a view. Even using it with TOP(100) PERCENT (or on SQL Server 2012, the OFFSET-FETCH equivalent) does not guarantee presentation order, it just means you'll get the top 100% of the rows, in any order.


Adding foreign keys constraint on databases used

OK - following my comment, here is what I would do.

Before shutting down the server or individual database.

Create backup table scripts for each table you need to enforce foreign keys on and their parent(s). Call them my_table_bak (or similar).
Run these CREATE TABLE my_table_bak(1..n) scripts on the database (don't definitively delete anything).
(removed during edit)
Prepare an SQL script file (salvage.sql? :-))

Now, the question is are you removing orphan records (i.e. child records that have no corresponding record in the parent table)? Or are you deleting barren parents (i.e. parents with no child records)? Below will do both.

INSERT INTO parent_bak
SELECT * FROM parent
WHERE parent.primary_key NOT IN
(
    SELECT child.foreign_key
    FROM child
); -- backup barren parents

INSERT INTO child_bak
SELECT * FROM child
WHERE child.foreign_key
NOT IN
(
    SELECT parent.primary_key
    FROM parent
); -- backup orphan records

DELETE FROM parent
WHERE parent.primary_key
NOT IN
(
    SELECT child.foreign_key
    FROM child
); -- remove invalid records from your original tables.

DELETE FROM child
WHERE child.foreign_key NOT IN
(
    SELECT parent.primary_key
    FROM parent
); -- remove invalid records from your original tables.

Add your FOREIGN KEY constraints.
Stop all clients connecting to the server or database (so updates can't occur while running the copy into _bak tables (invalid data) and the deletes.
Run:

mysql -h host -u username -ppassword yourdb < salvage.sql

...for each of your servers. For your 20 databases, you could write a script for all 20 and run that.

The time this will take will depend on the size of your databases and the number of tables. Warning - there may be typos in this post - I didn't run the scripts on a server, but you should get the idea.

Test on at least one database before running on your live server.

[EDIT] Forgot - add your FOREIGN KEY constraints at the end of salvage.sql also.


I'm not sure I understand your question right, but:

If I understand right, this is a tree that has children ordered. Also the creation of children is monotone, that is no children are deleted.
In these cases, in relational databases I used to have:

Id
parent's Id
child order (first child is 1, second 2, etc.)
child count (count of my children, only needed if you want "last child")
acceptor, ...

Key's are ( Id ) & ( parent's ID ; child order ).

In this case:

Previous sibling is ( parent's Id ; child order-1 )
Next sibling is ( parent's Id ; child order+1 )
First child is ( Id ; 1 )
Last child is ( Id ; child count )
Parent id ( parent's Id )

I hope I helped.


I think you have your column data-type on non numeric format. if your data type is CHAR (or VARCHAR) type it would select 9 as your max number.

If you change your datatype for the column to INT, your problem should be resolved.


If using xp_cmdshell and Powershell is an option (rather than BCP), you could achieve your goal as follows:

--Demo setup to create and load a table with some data
SET NOCOUNT ON
DROP TABLE IF EXISTS [dbo].[CUSTOMER];
CREATE TABLE [dbo].[CUSTOMER](
    [CustomerID] [int] NOT NULL,
    [FirstName] [varchar](20) NOT NULL,
    [LastName] [varchar](20) NOT NULL,
    [Email] [varchar](30) NOT NULL,
    [PhoneNo] [int] NOT NULL,
    [StreetAddress] [varchar](40) NULL,
    [City] [varchar](20) NULL,
    [OrderID] [int] NOT NULL,
PRIMARY KEY CLUSTERED
(
    [CustomerID] 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
INSERT [dbo].[CUSTOMER] ([CustomerID], [FirstName], [LastName], [Email], [PhoneNo], [StreetAddress], [City], [OrderID]) VALUES (13579246, N'David', N'Casey', N'dcasey@gmail.com', 12826363, N'34 Bray Rd', N'Dublin', 94724274)
GO
INSERT [dbo].[CUSTOMER] ([CustomerID], [FirstName], [LastName], [Email], [PhoneNo], [StreetAddress], [City], [OrderID]) VALUES (16579946, N'Donal', N'Smith', N'dsmith@gmail.com', 13826563, N'78 Ballybrack Rd', N'Dublin', 14724274)
GO
INSERT [dbo].[CUSTOMER] ([CustomerID], [FirstName], [LastName], [Email], [PhoneNo], [StreetAddress], [City], [OrderID]) VALUES (17579946, N'Steve', N'Reidy', N'sreidy@gmail.com', 13827563, N'22 Conna Rd', N'Cork', 64724274)
GO
INSERT [dbo].[CUSTOMER] ([CustomerID], [FirstName], [LastName], [Email], [PhoneNo], [StreetAddress], [City], [OrderID]) VALUES (18579946, N'Michael', N'Yule', N'myule@gmail.com', 14827563, N'44 Blasket Rd', N'Kilkenny', 44424274)
GO

A possible downside to this approach is because ConvertTo-CSV automatically surrounds output columns with double quotes. The follow script handles the elimination of ALL double quotes.  This could be problematic if your data actually contains double quotes.

--The solution
--The output goes to C:\Test\test.txt
--Make sure that the parameter value for -ServerInstance below is equal to the Server Name
--of the SQL Server instance you want to access.  The example below is using a single period
--which indicates the default instance on the computer where the Powershell script is actually running.

DECLARE @cmd VARCHAR(4000)
set @cmd = 'powershell.exe "Invoke-Sqlcmd -ServerInstance . -Query ''Select * from Test.dbo.customer'' | ConvertTo-Csv -NoTypeInformation -Delimiter "`t"  | % {$_ -replace ''""'',''''} | Out-File (''C:\Test\test.txt'') "'
print @cmd
exec xp_cmdshell @cmd

Note that the ConvertTo-Csv cmdlet allows you to specify a Delimiter.  If you don't provide that parameter, the default is a comma (as represented by the output below.

Results in C:\Test\test.txt:

CustomerID,FirstName,LastName,Email,PhoneNo,StreetAddress,City,OrderID
13579246,David,Casey,dcasey@gmail.com,12826363,34 Bray Rd,Dublin,94724274
16579946,Donal,Smith,dsmith@gmail.com,13826563,78 Ballybrack Rd,Dublin,14724274
17579946,Steve,Reidy,sreidy@gmail.com,13827563,22 Conna Rd,Cork,64724274
18579946,Michael,Yule,myule@gmail.com,14827563,44 Blasket Rd,Kilkenny,44424274

Here is an example where I'm using backtick t (`t) to export a tab delimited file.

DECLARE @cmd VARCHAR(4000)
set @cmd = 'powershell.exe "Invoke-Sqlcmd -ServerInstance . -Query ''Select * from Test.dbo.customer'' | ConvertTo-Csv -NoTypeInformation -Delimiter "`t"  | % {$_ -replace ''""'',''''} | Out-File (''C:\Test\test.txt'') "'
print @cmd
exec xp_cmdshell @cmd


How to allow connections to SQL Server with 2 differents ip

This is the type of setup we use at the place I work for.

I'll assume for the benefit of giving a better example, that your Aliases (CNAMEs) are configured like this:

SQLINSTANCE.my.network -> 192.168.1.30
SQLINSTANCE.mgt.my.network -> 10.0.0.1

You then start the SQL Server Configuration Manager, navigate to the SQL Server Network Configuration branch and open up to the Protocols for SQLINSTANCE (my picture shows MSSQLSERVER as the instance name) and then you open the TCP/IP properties with a right-click and you will get a screen similar to the below screen shot:

In the dialog you ensure the TCP/IP properties are set Enabled : Yes and that the Listen All : No option is set, so that it looks likes the following screen shot:

You then switch to the IP Addresses register and find your first IP address. In this example the settings for the first IP (192.168.0.30) are found at the IP2 section. It will look like this:

Ensure that the option Enabled : Yes is set and the IP address is correct and that the TCP Port : 1550 is set to you reserved port.

Do the same for the other IP address. Locate the IP section (e.g. IP4 in this screen shot) and set accordingly:

And last but not least we have to ensure that Dynamic Ports are disabled. To accomplish this, scroll down to the bottom of the IP Addresses register and set the following settings:

It is essential that both the TCP Dynamic Ports and the TCP Port settings are empty!

When done click OK and restart your SQL Server Instance SQLINSTANCE. Your SQL Server instance SQLINSTANCE will now pick up on both IP addresses.

Good luck!

I wrote a Q & A style article What are valid connection strings for SSMS login box? which explains some of the settings you might encounter when connecting to SQL Servers and their instances. It also briefly explains that the SQL Server Browser service is only required if you don't know the actual port you are connecting to. Added security anybody?


I've added a few more rows just to check it.

create table employee_details (id int, name text, desig text, age text);

insert into employee_details values
(1, 'John',    'Supervisor',   'M/28'),
(2, 'Deborah', 'Manager',      'F/44'),
(3, 'Joan',    'Area Manager', 'M/52'),
(4, 'Anna',    'Manager',      'F/35'),
(5, 'Rose',    'Area Manager', 'M/55');

Age group can be computed in this way:

(substring_index(age, '/', -1) DIV 10) * 10 as age_group

Then you can GROUP BY desig and age group:

SELECT   desig,
         (substring_index(age, '/', -1) DIV 10) * 10 as age_group,
         count(*) as count_emp
FROM     employee_details
GROUP BY (substring_index(age, '/', -1) DIV 10) * 10, desig
ORDER BY (substring_index(age, '/', -1) DIV 10) * 10, desig;

desig        | age_group | count_emp
:----------- | --------: | --------:
Supervisor   |        20 |         1
Manager      |        30 |         1
Manager      |        40 |         1
Area Manager |        50 |         2

dbfiddle here

For a custom age groups I'd suggest a tally table:

create table age_group (id int, name text, low int, high int);

insert into age_group values
(1, '< 25',   0,  25),
(2, '25-35', 25,  35),
(3, '35-40', 35,  40),
(4, '40-50', 40,  50),
(5, '>= 50', 50, 150);

Then you can CROSS JOIN this table with all possible designations and use an scalar subquery to get count of employees.

SELECT ed.desig,
       ag.name,
       (SELECT COUNT(*)
        FROM   employee_details
        WHERE  substring_index(age, '/', -1) >= ag.low
        AND    substring_index(age, '/', -1) < ag.high
        AND    desig = ed.desig) count_emp
FROM   age_group ag
CROSS JOIN (SELECT DISTINCT desig
            FROM   employee_details) ed;

desig        | name  | count_emp
:----------- | :---- | --------:
Supervisor   | < 25  |         0
Supervisor   | 25-35 |         1
Supervisor   | 35-40 |         0
Supervisor   | 40-50 |         0
Supervisor   | >= 50 |         0
Manager      | < 25  |         0
Manager      | 25-35 |         0
Manager      | 35-40 |         1
Manager      | 40-50 |         1
Manager      | >= 50 |         0
Area Manager | < 25  |         0
Area Manager | 25-35 |         0
Area Manager | 35-40 |         0
Area Manager | 40-50 |         0
Area Manager | >= 50 |         2

dbfiddle here


Setting a variable from a statement that starts with EXEC

Try something like the following:

DECLARE @DbServerName sysname;

EXEC [TMR_DM_LS].[master].sys.sp_executesql
   N'SELECT @DbServerName_tmp = @@SERVERNAME;',
   N'@DbServerName_tmp sysname OUTPUT',
   @DbServerName_tmp = @DbServerName OUTPUT;

SELECT @DbServerName AS [RemoteName];

HOWEVER, the above is how to return info in general. IF you are truly only wanting the server name, then I think you already have it on your local server. The Linked Server definition, depending on how it was configured, might very well have the name you are looking for:

DECLARE @DbServerName sysname;

SELECT @DbServerName = [data_source]
FROM   sys.servers
WHERE  [name] = N'TMR_DM_LS';

SELECT @DbServerName AS [RemoteName];

See if that works before doing the actual remote execution since it would be much more efficient to get the value locally.

UPDATE
O.P. replied that the desired value was indeed in the sys.servers system catalog view.


Creating joined rows from multiple tables on date field

Let's start by considering the downpayments and payments together.

WITH AllPayments AS (
    SELECT * FROM TableB
    UNION ALL
    SELECT * FROM TableC
)

Now get the total and latest date.

, YearlyDetails AS (
    SELECT TableAID, YEAR([Date]) AS TheYear,
        SUM(Amount), MAX([Date]) AS LastDate
    FROM AllPayments
    GROUP BY TableAID, YEAR([Date])
)

Now you can easily join this to TableA...

So putting all of it together:

WITH AllPayments AS (
    SELECT * FROM TableB
    UNION ALL
    SELECT * FROM TableC
), YearlyDetails AS (
    SELECT TableAID, YEAR([Date]) AS TheYear,
        SUM(Amount) AS Amount, MAX([Date]) AS LastDate
    FROM AllPayments
    GROUP BY TableAID, YEAR([Date])
)
SELECT a.ID, a.Name, y.Amount, y.LastDate as [Date]
FROM TableA AS a
JOIN YearlyDetails AS y
ON y.TableAID = a.ID;


MySQL slow log logs every query instead of just slow queries

Maybe your mysqld got it's long_query_time value from another source. What happens if you run:

select @@long_query_time;

Note that I would consider setting the long_query_time to 0 to be good practice, particularly on Innodb databases. Consider the case where query A takes 1 second and runs a thousand times a day vs query B taking 20 seconds and run once a day; you'll get more benefit from tuning query A.


A query to find patients with any 4 visits within a 365 day period

Here is a possibility for you to look at.  It uses the Lag function with the offset option set.  It doesn't show what all the visits were or when they occurred, but it does only show the most recent visit date for each patient and then when the fourth prior visit occurred within the year.

Test Data

IF OBJECT_ID('tempdb..#TestData') IS NOT NULL
BEGIN
    DROP TABLE #TestData;
END

CREATE TABLE #TestData
    (
    TestDataID INT IDENTITY(1,1) PRIMARY KEY NOT NULL
    , PatientName VARCHAR(40) NOT NULL
    , VisitDate DATETIME NOT NULL
    );

INSERT INTO #TestData
(PatientName, VisitDate)
VALUES ('Smith', '11/30/2017')
    , ('Smith', '1/1/2018')
    , ('Smith', '12/5/2017')
    , ('Smith', '2/1/2015')
    , ('Smith', '9/5/2017')
    , ('Doe', '9/8/2017')
    , ('Smith', '9/12/2017');

Query

/** CTE_Visitor
    Limits data to only where all visits happened in 2017
    Determines if most recent (MostRecentDesc) will be 1 for most recent.
    Determines the fourth prior visit ID by using LAG with an offset.
    **/
;WITH CTE_Visitor AS
    (
    SELECT S.TestDataID
        , S.PatientName
        , S.VisitDate
        , FourVisitsAgo = LAG(S.TestDataID, 3) OVER (PARTITION BY S.PatientName ORDER BY S.VisitDate)
        , MostRecentDesc = ROW_NUMBER() OVER (PARTITION BY S.PatientName ORDER BY S.VisitDate DESC)
    FROM #TestData AS S
    WHERE YEAR(S.VisitDate) >= 2017
    )
/** Joins test data back on to the FourVistsAgo to get the date.
    - Only displays for the most recent visit and the fourth prior visit.
    - Must have had at least four visits in the past
    - Fourth prior visit must be within a year of most recent visit
    **/
SELECT S.TestDataID
    , S.PatientName
    , S.VisitDate
    , Prev.VisitDate AS DateFourVisitsAgo
    , DiffDays = DATEDIFF(DAY, Prev.VisitDate, S.VisitDate)
FROM CTE_Visitor AS S
    INNER JOIN #TestData AS Prev ON Prev.TestDataID = S.FourVisitsAgo
WHERE MostRecentDesc = 1
    AND FourVisitsAgo IS NOT NULL
    AND DATEDIFF(DAY, Prev.VisitDate, S.VisitDate) <= 365;


An archive of old packages (3.3.2 and later) for Debian/Ubuntu-based systems is available here:
http://atalia.postgresql.org/morgue/r/repmgr/


How to find which query is causing tempdb growing?

--Lists the TempDB usage per each active session.
--It helps identifying the sessions that use the tempdb heavily with internal objects.

;WITH task_space_usage AS (
    -- SUM alloc/delloc pages
    SELECT session_id,
           request_id,
           SUM(internal_objects_alloc_page_count) AS alloc_pages,
           SUM(internal_objects_dealloc_page_count) AS dealloc_pages
    FROM sys.dm_db_task_space_usage WITH (NOLOCK)
    WHERE session_id <> @@SPID
    GROUP BY session_id, request_id
)
SELECT TSU.session_id,
       TSU.alloc_pages * 1.0 / 128 AS [internal object MB space],
       TSU.dealloc_pages * 1.0 / 128 AS [internal object dealloc MB space],
       EST.text,
       -- Extract statement from sql text
       ISNULL(
           NULLIF(
               SUBSTRING(
                   EST.text,
                   ERQ.statement_start_offset / 2,
                   CASE WHEN ERQ.statement_end_offset < ERQ.statement_start_offset THEN 0 ELSE( ERQ.statement_end_offset - ERQ.statement_start_offset ) / 2 END
               ), ''
           ), EST.text
       ) AS [statement text],
       EQP.query_plan
FROM task_space_usage AS TSU
INNER JOIN sys.dm_exec_requests ERQ WITH (NOLOCK)
    ON  TSU.session_id = ERQ.session_id
    AND TSU.request_id = ERQ.request_id
OUTER APPLY sys.dm_exec_sql_text(ERQ.sql_handle) AS EST
OUTER APPLY sys.dm_exec_query_plan(ERQ.plan_handle) AS EQP
WHERE EST.text IS NOT NULL OR EQP.query_plan IS NOT NULL
ORDER BY 3 DESC, 5 DESC

On a TEST SERVER you can start this query

--USE TempDB;
--GO
--SELECT TOP 1000000000
        IDENTITY(INT,1,1) AS RowNum
   INTO #StressTempDB
   FROM Master.sys.All_Columns ac1,
        Master.sys.All_Columns ac2,
        Master.sys.All_Columns ac3;
GO

and then start the above script to see which sessions are causing tempDB growth.


MySQL Fast Database Data Validation Master/Slave

The Master is now in the US?  But will later be in Australia?

Rather than simply have Master (in US) + Slave, also set up the binlog (etc) so that it is Master-Master.  However, continue to write only to the US Master.

Once you have spent days on validation, you should be ready to point the clients at the Australia Master.  At this point, you have to trust that the days of replication makes across the big pond correctly.  (You could spend more days re-validating, but I expect it will say OK.)

I don't know for a fact, but I would expect pt-table-checksum to work almost as fast in a live system as an idle one.


How to lock a MSSQL (2017 Linux) account after N unsuccessful login attempts

I think you need to use a server / login trigger. Aaron Bertrand wrote a cool little article about combining login triggers and extended events here - https://www.mssqltips.com/sqlservertip/3582/prevent-and-log-certain-sql-server-login-attempts/


Altering nullability of column in SQL Server

The relevant documentation is here

  ALTER COLUMN  Specifies that the named column is to be changed or
  altered.

  The modified column cannot be any one of the following:

  Used in an index, unless the column is a varchar, nvarchar, or
  varbinary data type, the data type is not changed, the new size is
  equal to or larger than the old size, and the index is not the result
  of a PRIMARY KEY constraint.
  Used in statistics generated by the CREATE STATISTICS statement unless
  the column is a varchar, nvarchar, or varbinary data type, the data
  type is not changed, and the new size is equal to or greater than the
  old size, or if the column is changed from not null to null. First,
  remove the statistics using the DROP STATISTICS statement. Statistics
  that are automatically generated by the query optimizer are
  automatically dropped by ALTER COLUMN.

In practice it seems that SQL Server does allow some additional cases beyond that mentioned in the documentation however.

As you show in your question ALTER TABLE test ALTER COLUMN field1 int null does in fact work so the restrictions on changes to columns used in indexes appear to be the same as those for user created statistics.

Moreover the caveat mentioned about Primary Keys seems to be untrue also. The following works fine.

CREATE TABLE test2(pk varchar(10) primary key);

ALTER TABLE test2 ALTER COLUMN pk varchar(100) NOT NULL


These are the hints:

The dimension consists only of natural key (e.g. invoice's or order's number) and contains no other attributes (because they've been placed in dimensions of their own).
The dimension table grows proportionately with the fact table, e.g. every time you insert new order lines into the fact table, you have to insert new OrderNumber dimension rows.
You never use the dimension to filter data when populating a report and it makes no sense to use its values as a row header.

Read Design Tip #46: Another Look At Degenerate Dimensions from the Kimball Group


How can I recover a dropped table which is not in the recycle bin?

You can clone your production database to a point in time before the table was dropped, then export the table you need and import into your prod database.
You can also try a table space point in time recovery. I tested it out a few years back, but I haven't needed to do it since.

Performing RMAN Tablespace Point-in-Time Recovery (TSPITR)


Triggers on Insert or Update SQL Server (Spatial)

Isn't this what you're looking for ?

CREATE TRIGGER [triggername] ON [table]
AFTER INSERT, UPDATE
AS
BEGIN
    UPDATE [table]
    SET [table].Updated = Getdate()
    WHERE ID IN (SELECT ID FROM INSERTED)
END
GO


How to copy one table data to another on different server?

You could restrict on table level. This can be done by ignoring some tables, or by only specifying the databases/tables that you want.

http://dev.mysql.com/doc/refman/5.0/en/replication-options-slave.html#option_mysqld_replicate-do-table

Example: --replicate-wild-do-table=foo%.bar% replicates only updates that use a table where the database name starts with foo and the table name starts with bar.

Example: --replicate-wild-ignore-table=foo%.bar% does not replicate updates that use a table where the database name starts with foo and the table name starts with bar.


PostgreSQL: What is the best way to cast from from text to point[]?

Not any better than what you already got, but:

select string_to_array(replace('(180,90),(-180,-90)','),(',');('), ';')::point[];

If the separator token in the array would have been something else (say ";"), it would have been sufficient with:

select string_to_array('(180,90);(-180,-90)', ';')::point[];

EDIT:

If I get it right you have a foreign table like:

create table mytbl (p point[]);
insert into mytbl (p)
values ( '{"(180, 90)", "(-180,-90)"}'::point[] );

which you access through a dblink. Would the following work ( I cant use DBLink at the moment )?

select string_to_array(array_to_string(p,';'),';') from mytbl;


I'm not aware of such tool, but you can easily create one your self (foreign-keys-in-a-sql-server-database):

SELECT C.TABLE_CATALOG [PKTABLE_QUALIFIER],
       C.TABLE_SCHEMA [PKTABLE_OWNER],
       C.TABLE_NAME [PKTABLE_NAME],
       KCU.COLUMN_NAME [PKCOLUMN_NAME],
       C2.TABLE_CATALOG [FKTABLE_QUALIFIER],
       C2.TABLE_SCHEMA [FKTABLE_OWNER],
       C2.TABLE_NAME [FKTABLE_NAME],
       KCU2.COLUMN_NAME [FKCOLUMN_NAME],
       RC.UPDATE_RULE,
       RC.DELETE_RULE,
       C.CONSTRAINT_NAME [FK_NAME],
       C2.CONSTRAINT_NAME [PK_NAME],
       CAST(7 AS SMALLINT) [DEFERRABILITY]
FROM   INFORMATION_SCHEMA.TABLE_CONSTRAINTS C
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU
     ON C.CONSTRAINT_SCHEMA = KCU.CONSTRAINT_SCHEMA
    AND C.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME
JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC
     ON C.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA
    AND C.CONSTRAINT_NAME = RC.CONSTRAINT_NAME
JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C2
     ON RC.UNIQUE_CONSTRAINT_SCHEMA = C2.CONSTRAINT_SCHEMA
    AND RC.UNIQUE_CONSTRAINT_NAME = C2.CONSTRAINT_NAME
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU2
     ON C2.CONSTRAINT_SCHEMA = KCU2.CONSTRAINT_SCHEMA
    AND C2.CONSTRAINT_NAME = KCU2.CONSTRAINT_NAME
    AND KCU.ORDINAL_POSITION = KCU2.ORDINAL_POSITION
WHERE  C.CONSTRAINT_TYPE = 'FOREIGN KEY'
  AND C.TABLE_SCHEMA = ?
  AND C.TABLE_NAME = ?

will give you all the children of the root table. You can create a recursive CTE that computes all offspring to this table.

As for the number of rows affected, you can construct it like:

SELECT COUNT(1)
FROM (
    SELECT DISTINCT T.*
    FROM T
    WHERE EXISTS (
         SELECT 1
         FROM <JOIN ALL TABLES FROM PARENT OF T TO ROOT>
         WHERE ROOT.PREDICATE
    )
) AS X


How can I modify this trigger with loops and concat?

How about expanding this a little and do a proper log table, logging every update, insert or delete. That way you can keep a proper history.
For this, in MySQL, you will need three triggers :

CREATE TABLE EMP ( ID INT,
                   LASTNAME varchar(100),
                   firstname varchar(100),
                   gender varchar(1),
                   dob date,
                   marital varchar(1),
                   ssn varchar(20)
                 );

CREATE TABLE EMPLOG ( empid INT,
                   operation varchar(1),
                   operationdate date,
                   operated_by varchar(200),
                   LASTNAME varchar(100),
                   firstname varchar(100),
                   gender varchar(1),
                   dob date,
                   marital varchar(1),
                   ssn varchar(20)
                 );

CREATE TRIGGER emplog_insert AFTER INSERT ON emp
FOR EACH ROW
INSERT INTO emplog VALUES
(NEW.id,
 'I',
 Now(),
 USER(),
 NEW.lastname,
 NEW.firstname,
 NEW.gender,
 NEW.dob,
 NEW.marital,
 NEW.SSN
);

CREATE TRIGGER emplog_update AFTER UPDATE ON emp
FOR EACH ROW
INSERT INTO emplog VALUES
(NEW.id,
 'U',
 Now(),
 USER(),
 NEW.lastname,
 NEW.firstname,
 NEW.gender,
 NEW.dob,
 NEW.marital,
 NEW.SSN
);

CREATE TRIGGER emplog_delete AFTER DELETE ON emp
FOR EACH ROW
INSERT INTO emplog VALUES
(OLD.id,
 'D',
 Now(),
 USER(),
 OLD.lastname,
 OLD.firstname,
 OLD.gender,
 OLD.dob,
 OLD.marital,
 OLD.SSN
);

If you then wish to check out the history of what happened for a specific employee, you simply run this query :

select *
from Emplog
where empid = ?
order by operationdate


Strange colored sqlcl results on Windows

It's a bug, and it's fixed for the next release.

We're planning on regular, not quite monthly, updates.


I had this problem after upgrading to SQL Server 2016, and removing all replication was not an option.

For me, the solution was adding SP1 to my SQL Server 2016 instances.

The error had come when the trigger called sp_MStran_ddlrepl (in its last step, above). That stored procedure wasn't expecting "5" as the second parameter on a couple of upgraded servers. Running SP1 altered that stored procedure making "5" an acceptable parameter.


Total logical reads per database on SQL Server

You could use a query like this, with the caveat that it will also track backup activity as reads. I'm also not aware of a DMV that tracks logical reads in the way you want.
To account for the backups caveat, you may want to stick it in an Agent Job and log it to a table at the beginning and end of working periods, or some other cadence that makes sense.
If you have Query Store enabled on all of your databases, you could take a daily snapshot of runtime_stats to get closer to the truth. But that's a big if, and depending on your settings it may not capture all query activity.
SELECT
    run_date =
        GETDATE(),
    days_up =
        DATEDIFF
        (
            DAY,
            DATEADD
            (
                MILLISECOND,
                -vfs.sample_ms,
                GETDATE()
            ),
            GETDATE()
        ),
    database_name =
        DB_NAME(vfs.database_id),
    drive =
        LEFT
        (
            mf.physical_name,
            1
        ),
    mf.physical_name,
    size_on_disk_mb =
        vfs.size_on_disk_bytes / 1024 / 1024.,
    read_latency =
        (vfs.io_stall_read_ms
            / NULLIF(vfs.num_of_reads, 0)),
    mb_read =
        vfs.num_of_bytes_read
            / 1000000.,
    avg_mb_per_read =
        (vfs.num_of_bytes_read
            / NULLIF(vfs.num_of_reads, 0)) / 1000000.,
    avg_read_stall =
        vfs.num_of_reads
            / NULLIF(vfs.io_stall_read_ms, 0),
    write_latency =
        (vfs.io_stall_write_ms
            / NULLIF(vfs.num_of_writes, 0)),
    mb_written =
        vfs.num_of_bytes_written / 1000000.,
   avg_mb_per_write =
       (vfs.num_of_bytes_written
           / NULLIF(vfs.num_of_writes, 0)) / 1000000.,
    avg_write_stall =
        vfs.num_of_writes
            / NULLIF(vfs.io_stall_write_ms, 0)
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs
JOIN sys.master_files AS mf
    ON  vfs.database_id = mf.database_id
    AND vfs.file_id = mf.file_id
ORDER BY size_on_disk_mb DESC;


Hard limitation for merge replication article count?

I have now Merge replication setup with 666 Table Articles in production, and I didn't get any warning or errors regarding articles limit.

Update:
Starting from SQL Server 2016, Articles limit is now: 2048

Source


The most common cause of this is virus scanning, as virus scanners tend to lock files.

Try excluding the directory from scanning.


MySQL Replication after a set period of time

I suggest it is not a real problem.  I have worked with a Master that had about 20 slaves, each pulling upwards of a million queries per day.  The polling and network issues were minor.

But if you must...  Have a job that does this forever:

repeat forever
    START SLAVE;
    sleep 0.1 sec
    STOP SLAVE;
    sleep 10 sec
end-loop

To make it more robust, adjust the sleeps if Seconds_behind_master gets "too high".


You can do the aggregate in a subsequent cte, and update from there.

;WITH CTE AS
(Select EC, CN, CID, EntryDate
        FROM OPENQUERY(192.55.108.214,'select
                              count(es.estimatenumber) As EC
                              ,cm.an CN
                              ,cm.am CID
                              ,es.entrydate EntryDate
                              from etalac es
                              INNER JOIN bacalac cm
                              ON es.CID = cm.am
                              GROUP BY cm.an, cm.am, es.entrydate'))
, uCTE as (
  select cid, sumEC=sum(EC)
    from cte
    where CAST(entrydate As Date) BETWEEN '01/01/2016' AND '01/22/2016'
    group by cid
)
UPDATE d2016
  SET TotalNumber = sumEC
FROM uCTE cte
  INNER JOIN datafor16 d2016
    ON cte.CID = d2016.CID


Time dimension or timestamp in fact table?

I would recommend including both on the fact table. The dimension should be used for filtering and grouping, while the time stamp value can be used in detailed reports/queries.

Unless you care about whether an event occurred at 8 seconds or 42 seconds past the minute, create your time dimension at the grain of 1 minute.

As you did not tag your RDBMS, I thought it prudent to mention that later versions of SQL Server do not allow date + time data type operations; i.e. recreating the time stamp from the date & time dimension business keys.
A work around is convert(datetime, [date column]) + convert(datetime, [time column]), or similar.


The superior implementation is to save the default choice with the row in the parent table. Then a new default replaces the old one automatically. No trigger required, no chance to break referential integrity
Data model based on fragments in the question:
CREATE TABLE house (
   house_id         serial PRIMARY KEY
 , default_phone_id int
 -- , other columns
);

CREATE TABLE phoneline (
   phone_id serial PRIMARY KEY
 , house_id int  NOT NULL REFERENCES house
 , number   text NOT NULL
 , UNIQUE (house_id, phone_id)  -- seems redundant, but needed for FK below
);

ALTER TABLE house ADD CONSTRAINT house_default_phone_id_fkey
FOREIGN KEY (house_id, default_phone_id) REFERENCES phoneline(house_id, phone_id);

The circular FK references may seem like a problem. But, really, they are not.
I made the FK constraint house_default_phone_id_fkey include house_id, so that setting the default to a phone number of a different house is impossible. The FK requires a UNIQUE (house_id, phone_id) in phoneline, which seems a bit redundant since phone_id is the PK. But you typically need an index with house_id as leading column for performance anyway.
Since the default_phone_id can be NULL, you can choose the default later, after inserting one or more phone numbers. But it even works with the column set to NOT NULL; just use data-modifying CTEs to insert & update in a single statement. Reference implementation:

How to deal with mutually recursive inserts

Related:

Constraint to enforce "at least one" or "exactly one" in a database
Enforcing constraints "two tables away"


Measuring MongoDB I/O and CPU performance

There are several tools that measure system performance like newrelic,MMS,cacti, ganglia...

Regarding the size you need to share a sample record from Oracle and a sample document from Mongo.

The obvious reasons are:

long field names: A collection in Mongo with N documents stores the field names N times. If the field names are too long you are wasting space.
powerof2 allocation strategy (only for MMAP engine): Is now the default record allocation strategy for MMAPv1. With the power of 2 sizes allocation strategy, each record has a size in bytes that is a power of 2 (e.g. 32, 64, 128, 256, 512 ... 2MB). If your document size is 33 then mongo will allocate 64 and you are wasting half your storage.
Compression (only for MMAP engine): Oracle is using compression while MMAP engine not


Foreign key without referenced columns specification

The behaviour is not explicitly mentioned in any of the official SQL Server documentation I am familiar with, but the 1992 Draft SQL Standard (section 11.8.2.b) does say:

  If the <referenced table and columns> does not specify a <reference column list>, then the table descriptor of the referenced table shall include a unique constraint that specifies PRIMARY KEY. Let referenced columns be the column or columns identified by the unique columns in that unique constraint and let referenced column be one such column. The <referenced table and columns> shall be considered to implicitly specify a <reference column list> that is identical to that <unique column list>.

Translated, this means an implicit foreign key does reference the primary key of the referenced table. As others have mentioned in comments to the question, it is probably best to be explicit about the relationship though.


Stored Procedure produce data for a range of dates

Having this parameters start_range=2015-02-01 and end_range=2016-08-01, we can generate a list of dates, recursively (see ;WITH cteDates).
This part could be replaced with a calendar table , if you have it.

DECLARE @dStart_Range AS DATE = '2015-02-01',
        @dEnd_Range AS DATE = '2016-08-01'

DECLARE @tPRJ AS TABLE
    (
        PRJ_ID CHAR(4) NOT NULL,
        START DATE NOT NULL,
        FINISH DATE NOT NULL,
        PRIMARY KEY CLUSTERED (PRJ_ID)
    );
INSERT INTO @tPRJ(PRJ_ID,START,FINISH)
VALUES('xxxx','2015-01-01','2016-01-01');

-- generate the list of dates
-- could be replaced by a calendar table if exists
;WITH cteDates
AS(
    SELECT @dStart_Range AS aDates
    UNION ALL
    SELECT DATEADD(MONTH,1,aDates)
    FROM cteDates
    WHERE
        aDates < @dEnd_Range
)
--SELECT * FROM cteDates

SELECT
    P.PRJ_ID AS [PRJ_ID],
    D.aDates AS [DATE],
    CASE WHEN P.START <= D.aDates AND P.FINISH>= D.aDates THEN 'ACTIVE'
         ELSE 'NOT ACTIVE' END AS [STATUS]
FROM
    cteDates AS D
    CROSS JOIN  @tPRJ AS P

And the result is:

PRJ_ID DATE       STATUS
xxxx   2015-02-01 ACTIVE
xxxx   2015-03-01 ACTIVE
xxxx   2015-04-01 ACTIVE
xxxx   2015-05-01 ACTIVE
xxxx   2015-06-01 ACTIVE
xxxx   2015-07-01 ACTIVE
xxxx   2015-08-01 ACTIVE
xxxx   2015-09-01 ACTIVE
xxxx   2015-10-01 ACTIVE
xxxx   2015-11-01 ACTIVE
xxxx   2015-12-01 ACTIVE
xxxx   2016-01-01 ACTIVE
xxxx   2016-02-01 NOT ACTIVE
xxxx   2016-03-01 NOT ACTIVE
xxxx   2016-04-01 NOT ACTIVE
xxxx   2016-05-01 NOT ACTIVE
xxxx   2016-06-01 NOT ACTIVE
xxxx   2016-07-01 NOT ACTIVE
xxxx   2016-08-01 NOT ACTIVE


Does SQL Server 2017 Express install on Dual CPU server?

Will it install?

Yes.

Will it work?

Yes.

Will it use all of the horsepower the server has available?

No. The limitation of the Express Edition -- "single physical CPU or four cores" -- means that SQL will detect more than one CPU and will set the others to offline. You'll see that in the results of select * from sys.dm_os_schedulers both as "VISIBLE OFFLINE" in the status column and as "0" in the is_online column.

So you and your customer can do this, but you'll be wasting some money. The recommended approach all depends on what the price difference is between the dual-CPU system and a similar single-CPU system. If it is a small cost difference, and there is a strong possibility that they will upgrade to Standard Edition or Enterprise Edition soon (after all, you do mention that Express Edition is enough "for the time being"), you might want to advise them to start with the dual-CPU system to enable making that future edition upgrade without also having to deal with a server upgrade. If the price difference is not insignificant, go with the single-CPU system so you don't waste the cost of the second CPU.


PostgreSQL bad plan: the frequency of a tag depends on the project!

You are correct that there is no way to do this, and that CREATE STATISTICS only looks at arrays in their entirety, not their individual components.

Perhaps CREATE STATISTICS can be expanded in the future to do that, but someone would have to volunteer to implement it, and I am not aware of anyone working on such a thing.

Note that even if "tag" were a scalar, CREATE STATISTICS wouldn't help you in versions 10 or 11, as the mcv type of statistics were not added until v12, and that is what you need. So presumably what you would need is a new type of statistics, call it arrray_mcv.


Grant all the privileges to a user over all other user's objects in Oracle

Which privileges?  Of course they are granted by using the appropriate GRANT command, but I suspect that is not what you are asking.  You could use the ANY option (like GRANT SELECT ANY TABLE ...) but I strongly recommend against it as it violates the principle of granting least privilege necessary.  I suspect what you really need is something like this:

set echo off feedback off header off pagesize 0
spool doit.sql
select 'grant select on table '||table_name||' to someuser;'
from dba_tables
where <whatever condition to filter the list of tables>
;
spool off

Then do a sanity check on the spooled file 'doit.sql', edit as necessary, then execute it.  Some do it all in one pop with a PL/SQL loop, but I'd rather capture the commands first and be able to do the sanity check on it.


xlog flush request is not satisfied in log files

Yes that looks like corruption, perhaps caused by storage hardware problems.

Perform a pg_dumpall of the cluster and restore it to a newly created cluster, that should get rid of the problem.

Check your hardware.


MyISAM! Shame.  Switch to InnoDB.

Don't worry about that PRIMARY KEY; it is too much hassle to split it up, etc.  But do make sure it is the 'appropriate' character set:

VARCHAR(21) CHARACTER SET ascii

If it is case sensitive, then add on COLLATE ascii_bin.

Yes, there is a memory and disk penalty.  But there are tradeoffs with speed, simplicity, etc.

(If it were 100 characters long, I might give a different answer.)

Numbers for MyISAM

In MyISAM, assuming the strings are half length on average:
PRIMARY KEY(decimal_14_0, varchar_7) needs 2*(7+(1+3.5)) bytes.
PRIMARY KEY(varchar_21) needs 2*(1+(13.5+3.5))

2* because PK fields stored with data and with index;
7 bytes for DECIMAL(14,0);
1=length field for VARCHAR;
3.5(?)=avg bytes for up-to-7-char suffix;
13.5(?) for avg # digits in prefix.

After cancelling out things, the diff is 2*(13.5-7)=13 bytes smaller per row (data+pk) for DECIMAL+VARCHAR. 300M*13=4GB.

(InnoDB computation is in Comments.)


How do I enforce a write-once then read only database table in SQL?

I see at least two ways of accomplishing this. The first approach is to not grant DELETE and UPDATE privileges on these write-once tables, or, for that matter, any privileges apart from INSERT and SELECT, thus only allowing users to insert into or select from them. This option has no performance overhead, as the privilege check is a part of any statement processing, but it can be overridden by users with elevated privileges, like root.
Another option is to define BEFORE UPDATE and BEFORE DELETE triggers on these tables and use the SIGNAL statement in the trigger body to raise an exception, which would prevent updates and deletes respectively. There is slight performance penalty that you will pay for this, but also some added security, as even privileged users won't be able to get past the error without disabling or dropping the triggers.


How bad is query plan pollution (too many equivalent plans in cache)?

"How bad is it?" depends on the degree to which you are suffering now or could suffer with increased workload in the future.

One major point of suffering with plan cache pollution could be too many single use plans bloating your plan cache leading to inefficient cache usage.

Another point of suffering could be high compilations/second - so in an environment with a heavy workload and a lot of activity, there is a cost associated with compiling over and over.

You can see the impact of compilations/sec in perfmon (SQL Server Statistics:Compilations/sec). This can look like CPU pressure. To your performance/applications, this can look like increased query duration waiting for needless compiles each time it runs.

You can see the impact to the plan cache from the memory bloat by this query borrowed from Glenn Berry's Diagnostic scripts. How big is your SQLCP plan cache?

SELECT TOP(10) [type] AS [Memory Clerk Type],
       SUM(pages_kb)/1024 AS [Memory Usage (MB)]
FROM sys.dm_os_memory_clerks WITH (NOLOCK)
GROUP BY [type]
ORDER BY SUM(pages_kb) DESC OPTION (RECOMPILE);

Also the query that was used in the question to identify the number of plans helps as well.

Is This Ever a Good Thing?

There are some cases where this could be good, but the situation is rare. Basically if you were suffering from parameter sniffing gone bad (nutshell: if the data can vary widely from execution to execution based on parameters, one compilation for one set of parameters ideal may yield an excellent query plan for that one query but poor for others.). My guess is that you likely wouldn't be dealing with that as bad as the implications from poor plan reuse.

What Can You Do About It?

Optimize For Ad Hoc Workloads can certainly help with the memory implications since only a stub of the plan is stored in cache at first execution, and the full plan isn't stored until it is executed a second time with the same plan.
Forced Parameterization could help here also. It can sometimes force parameterization to happen and help solve both the issue of cache bloat and the cost of having to recompile.
Fix The Queries Ideally, you shouldn't have to resort to these options, but instead can be more strict in your database development, encourage plan reuse, consider stored procedures for all of their benefits, and attempt to head off the problem that way. The ways to help fix this through forced parameterization or optimize for ad hoc are good to help, but the best solution is always aimed at the root cause.

There is an excellent resource here that talks about some of the dangers of plan cache pollution and some things you can do. I'd recommend a read here. It is written for SQL Server 2012, but the concepts and solutions apply.


Assuming you are dealing with a single time zone at a time (e.g. British Time), and can identify all rows that are in British Time (maybe that's everyone so far), you could create a calendar table that has the DST time change dates for Summer Time. You subtract an hour for all times that fall between, and don't touch the ones that don't. Sample data:

CREATE TABLE dbo.SampleData
(
  LocalDT DATETIME,
  IsBritishTime BIT,
  UTC DATETIME
);

INSERT dbo.SampleData(LocalDT,IsBritishTime) VALUES
 ('20000101 00:00',1), -- should NOT be changed
 ('20000326 00:59',1), -- should NOT be changed
 ('20000326 01:01',1), -- should go back
 ('20001029 01:59',1), -- should go back
 ('20001029 02:01',1), -- should NOT be changed
 ('20001231 23:59',1), -- should NOT be changed
 ('20000401 00:00',0); -- should NOT be changed (different time zone)

Now you can identify the ranges using some rather tricky T-SQL. We grab some rows from system tables based on the years your data needs to cover, then for each year we calculate the beginning and end of BST. We can then use those outputs to update the main table:

SET DATEFIRST 7;

;WITH y AS
(
  -- all the years from 2000 through 50 years after the current year:
  SELECT TOP (YEAR(GETDATE())-2000+51)
    y = DATEADD(DAY,-1,DATEADD(YEAR,number,'20000101'))
      FROM [master].dbo.spt_values
      WHERE [type] = N'P' ORDER BY number
),
s AS
(
  SELECT
    -- BST starts last Sunday in March @ 1:00 AM UTC:
    BSTStart = DATEADD(HOUR, 1, DATEADD(DAY,(1-DATEPART(
      WEEKDAY,DATEADD(MONTH,3,y))) % 7,DATEADD(MONTH,3,y))),
    -- and ends last Sunday in October @ 2:00 AM UTC:
    BSTEnd = DATEADD(HOUR,2,DATEADD(DAY,(1-DATEPART(
      WEEKDAY,DATEADD(MONTH,10,y))) % 7,DATEADD(MONTH,10,y)))
  FROM y
)
-- UPDATE d SET
SELECT *,
  UTC = DATEADD(HOUR,
  CASE WHEN s.BSTStart IS NULL THEN 0 ELSE -1 END, d.LocalDT)
FROM dbo.SampleData AS d
LEFT OUTER JOIN s
ON d.LocalDT >= s.BSTStart
AND d.LocalDT < s.BSTEnd
WHERE d.IsBritishTime = 1
-- or more likely WHERE EXISTS
-- (SELECT 1 FROM dbo.Users
--    WHERE UserID = d.UserID
--    AND TimeZone = 'BritishTime');

As written, this just shows you the join and identifies the rows that will be updated with a different UTC value (they will have non-null BSTStart/BSTEnd values). If you uncomment -- UPDATE d SET and comment SELECT *,, then select the data from dbo.SampleData, you should see this:

LocalDT          IsBritishTime UTC
---------------- ------------- ----------------
2000-01-01 00:00 1             2000-01-01 00:00
2000-03-26 00:59 1             2000-03-26 00:59
2000-03-26 01:01 1             2000-03-26 00:01 -- changed
2000-10-29 01:59 1             2000-10-29 00:59 -- changed
2000-10-29 02:01 1             2000-10-29 02:01
2000-12-31 23:59 1             2000-12-31 23:59
2000-04-01 00:00 0             NULL

(And actually, the March 26 1:01 local time shouldn't be possible - at 1:00 AM, the clocks rolled forward to 2 AM, so how could it ever have been 1:01 AM locally? So, you may want to special case that hour and deal with those cases individually. This is why every system I design stores UTC from the start - there is no plausible reason to store local time, since I can always get that from UTC, but I can't always get back.)

Of course, this will affect all rows that match the WHERE clause, it's just that some of the rows won't have a different value for UTC. I highly recommend putting this into a different column first, rather than just changing the start time, so that you maintain the original data in case something goes wrong, you need to troubleshoot, you do end up needing that data later, etc. You can change the column names if you want to use the UTC value in existing queries instead of changing those one-by-one.

This will work best if you have supporting indexes on the underlying table. If you are performing this update against multiple time zones (and have a column that can identify rows by local time zone), then creating a filtered index for each operation might be something worth trying. If you are identifying by some other indirection (such as UserID), maybe you could do a user at a time, or the set of users from each time zone. Chunking will work best in terms of blocking and log impact, but having no other details about the structure and how you could partition the data, that's the best I can do.

I provided British Time above, since it was the example in the question, but you'll obviously need different calculations for different timezones that have different offsets and different start/end dates (and those may differ from year to year, for example in 2007 the US dates changed).

This is based on the fact that the UK (well, London specifically) moves forward to 2 AM BST on the last Sunday of March at 1 AM local (or UTC), and back to 1 AM GMT the last Sunday of October at 2 AM local (1 AM UTC). Corroborate here; link courtesy @JackDouglas.


Why should an application not use the sa account

It depends on your business, but the main thing in most cases is to make sure it is not seen as an IT issue. It is a security issue and while the two overlap massively business peopel are more likely to listen if you say "security" than if you are just "moaning about general IT stuff".

Do you work with any clients that have security requirements? That is a good place to start. If we ran an app with sa level access, or just app that didn't properly secure its credentials even if not using priveleged access (we strongly prefer Windows Integrated rather than stored user/pass where possible), and we were subject to a security audit, that audit would fail and we would risk losing customers and/or having to give money back as to our groups's clients (banking organisations in the case of the product I work on mostly, other parts of the group deal with the police and health authorities and so forth) security is part of our offering being fit for purpose. Business people will understand the severity of that potential threat even if they usually pay no more than lip service to IT recommendations.

Even ignoring client imposed requirements, if you strive to meet various industry standard security standards then again this sort of application authentication is going to fail you in the face of an audit as it is so far from best practise as to be something generally considered to be on the "simply shouldn't be done" list. Make clear to your business descision makers that security is an important part of the application, and the fact that this vendor does not seem to be aware of (or at least appropriately concerned about) makes you have doubts about what else they might not capable of dealing with: knowing about DB security best practise is (well, should be) part of their job and imlpementing it is not difficult.

Also point out that you (the purchaser) should be dictating reasonable security requirements to the vendor, not the other way around. It is your data, so they are not qualified to state what is considered secure enough.


In the where clause, trade the Ic alias with IFNULL(c.new_ic,c.old_ic).

 SELECT
     `c`.`fullname` AS `fullname`,
     IFNULL(`c`.`new_ic`,`c`.`old_ic`) AS `Ic`,
     `bla`.`loan_no` AS `Loan_No`,
     `bla`.`total_loan_amt` AS `Total_Loan_Amt`,
     `bla`.`monthly_installment` AS `Month_Inst_Amt`,
     `bla`.`disb_date` AS `disb_date` FROM (`customers` `c`
 JOIN `basic_loan_application` `bla`
    ON ((`bla`.`customer_uid` = `c`.`customer_uid`)))
 WHERE IFNULL(`c`.`new_ic`,`c`.`old_ic`) LIKE '%800%' ORDER BY `bla`.`Basic_Loan_Application_Id` DESC LIMIT 0, 1

This happen because the query execution order. In MySQL queries are parsed in this order:

1- FROM clause
2- WHERE clause
3- GROUP BY clause
4- HAVING clause
5- SELECT clause
6- ORDER BY clause

Which means that queries is not executed the way we write them. So, the Ic alias is not defined in the moment of WHERE clause is executed.


You are best to start with a transactional database that records the data in as much detail as you can get from the source.  You can always summarize and report from detailed data, but you can't go backwards from summary information to detailed information.

If you are getting data in the form:

  transaction_date  datetime
, pallets_taken_in  int
, pallets_sent_out  int
, total_work_effort decimal(19,2)

Then create a table with these columns.  If you get one record per day then make the transaction_date a unique index (or even primary key).

Calculating the values for your report is easy from these columns:

Pallets handled = pallets_taken_in + pallets_sent_out for a given transaction_date
Pallets/hour = (pallets_taken_in + pallets_sent_out) / total_work_effort for a given transaction_date
Pallets on hand = SUM(pallets_taken_in) - SUM(pallets_sent_out) over all records in your table. (i.e. over all dates)

Note that depending on how you key your table, you can also include physical inventory adjustment records to make allowance for correcting your balance on hand.

Depending on how much data you have and how much (and what kind of) reporting you need, it might make sense to pre-calculate some of these values and store them in a second table in a data warehouse type of scheme, but don't pre-calculate data unless you're pretty sure you need to.  Having two copies of the same data (raw transactions and data warehouse) means that you have potential discrepancies to manage.


Can I run UPDATEs on a replicated MySQL slave, safely?

Yes with many caveats- I have literally this setup to expose Wikipedia database in public, and I would not recommend it unless you do not have an alternative options. We use triggers that update/blank fields that are private, replication filters to avoid full tables/databases, and views to limit certain per-row accesses.

Problems we faced:

Statement based replication is very flexible, but if you run certain writes based on existing data (e.g. INSERT...SELECT, updates based on modified columns), it will either break replication or create a data drift. You should limit application writes by inserts and updates by fields you are not going to modify-but it is not possible in most cases to control all possibilities except on simple models (insert-only writes, for example). The breakage can be very subtle but it may happen over time if the application is minimally complex.
Row based replication is more accurate (because you get the final state), but it does not trigger the triggers. We started using MariaDB 10.1 to have replica-only triggers to be able to do that (thus solving data drift), but we still have issues due to row based replication have limitation in terms of supporting different schema on master and replica.
Triggers and filters make things difficult to manage and more room for errors
If possible, I would suggest not having real time replication, but using dumps so you can filter on query time (in our cases it is not possible due to the size of the database and requiring real time from some usages).
Because you have different data, running pt-table-checksum and other comparing tools is not really possible (to check for data discrepancies)

When we have the resources, we will likely move to a custom filtering automation that analyzes the row based binary protocol in pseudo real-time and performs more complex filtering on application layer.


How to safely populate pg_notify payload?

Rather than worrying about how to escape it. Just use JSON as a transport layer. Here we use row_to_json

CREATE FUNCTION on_sign_up() RETURNS trigger as $$
  DECLARE
  BEGIN
    PERFORM pg_notify('sign_ups', row_to_json(NEW)::text );
    RETURN NEW;
  END;
$$ LANGUAGE plpgsql;


A WAL archive can be used for disaster recovery, PITR (point in time recovery), or as part of a replication set up.  If you don't know why you set it up in the first place, then maybe you don't need it.  The consequences of turning it off is that you can't use that type of disaster recovery, can't do PITR for after the time it was turned off, and can't use that kind of replication anymore.

Also, note that WAL often compresses extremely well, often 25 fold or more, with something like "xz" or "lzip". So if you mostly concerned with storage, you can implement compression.


Advice on diagnosing a "sometimes" slow query

I really don't think using the OPTION (RECOMPILE) is an effective way to eliminate the possibility of parameter sniffing.

Parameter sniffing happens when SQL is confused about a particular query and thinks its new because it sees new parameters.  It's slow because it's taking extra time to generate a new execution plan.

All that option does is force SQL to produce a new plan every time which is pretty much the same thing. Instead, you might want to consider adding default parameters using this hint:

OPTION(OPTIMIZE FOR(@LocationIds='xx',@StatusType='xx'))

When choosing parameters for the default make sure to use a statistically representative set.
That will force the same plan to be used every time and eliminate the possibility of parameter sniffing.  Once you do that, and determine it didn't help, then its probably safe to dismiss parameter sniffing as a possibility.


NTLM vs Kerberos authentication - questions

how can this be that while "Write ServicePrincipalName" privilege is turned off for the SQL service account, domain users are connecting using Kerberos auth

That privilege is not required for Kerberos to work, it only enables the service account to register its own Service Principal Names (SPNs).

See Register a Service Principal Name for Kerberos Connections


This is just concatenating a semicolon between two fields. Think of it like selecting LNAME + ', ' + FNAME or something similar.

It's generally fine to have presentation layer items like this in the select list, but it's important to avoid them as predicates (anything in a join or where clause), because in SQL Server, they're not SARGable.

SQL Server uses +, which is different from other platforms, that may use || to concatenate things, which is an issue I've written about in the past.


The short answer is maybe. You can read the SQL Server transaction log (that is your .LDF file) in an online database. Using the fn_dblog function. This post describes that process.

There is a slightly longer answer, though:

Why/how do you have your .mdf and .ldf from that day? If you had a crash consistent copy of them - in other words, a Volume Snapshot Service (VSS) orchestrated snapshot of the files with a tool that is SQL Server aware - then you are in good luck. If the snapshot was just taken randomly with some other tool that doesn't instruct SQL Server to freeze/thaw the IO before and after the snapshot there is a chance that your log file and data file are not 100% consistent (as in from the exact same moment in time) and it won't be of any use.

The steps you could go down are:

Attach that MDF and LDF (note since you want to read the log file, you can't do an attach rebuild log - you need to attach both so see the note above). Attach them as a new Database, lest you overwrite the existing database entirely and then someone comes after you with a question that says "how do I find out who overwrote the production database while trying to find out who dropped a table" it could get messy then.
Look to the link I shared above and start seeing what you can see in the log file.

You can also do things like - figure out who has permissions. Honestly, as a DBA, when an object gets dropped there is a shared responsibility there. The person who did the drop, and the person who let the person who did the drop do the drop. When I'm on dad duty, and one of my kids does something a little silly that mom would never allow when on mom duty, we're both in trouble when mom comes home. They shouldn't have done that, but I shouldn't have been answering questions on DBA.SE and I should have been controlling their access to whatever mess they caused. It's sort of like that. So this is a good reminder about least privilege.


SQL Server 2016 AlwaysOn VDI_CLIENT_WORKER command

After some more research I discovered that when I create an AG with autoseeding SQL Server creates a new worker (that executes the VDI_CLIENT_WORKER command) for each scheduler.
To remove those workers I need remove every 'autoseeded' AG and restart the SQL Server service.

If I then create the AG with full backup/restore none of these workers are created.


Restore a copy of the encrypted database

I have encrypted the backup using the method followed by the file server.

Not sure what that means.

  Unfortunately, I did not keep a copy of the certificate of the process of encryption and unfortunately also the server was completely destroyed and is impossible to run again and I have to restore a copy of the database.

That's going to be impossible. If you don't have the encryption cert, you can't restore.

  I have a copy of the rule but it is encrypted.

Rule? Not sure what a rule has to do with this.

  Is there a solution to this problem?

Tell the people that you can't restore the database and it is lost forever.


Do you really need compressed? A multi-terabyte datasets may happily live on 100G-RAM machine. What is your data like? Text? Blobs? Lots of rows? Care to share the CREATE TABLEs?

I don't have any details on Cloud SQL.  But...

Compression is usually not the first place to look when thinking of Scaling.  It has overhead (usually CPU, sometimes complexity).

InnoDB's compression gives you about 2x.
Outside compression (zip, etc) give about 3x for text. That is not very significant for "scaling".
TokuDB and InfiniDB claim to get 10x, and I have heard supporting evidence for such.

"Scaling" usually involves Sharding or some other "distributed" technique.

RAM is not usually the limiting factor in Scaling; it is disk space. Or it could be any of several metrics - IOPs, network bandwidth, etc.

Scaling...

Read scaling is 'easily' handled via Replication.
Write scaling is not handled by any normal technique.
Galera Cluster gives some write scaling.
Sharding give a lot of write scaling, but at noticeable complexity in the application.
MySQL's NDB Cluster gives some write scaling, but it is usually limited to niche applications, and not for general use.  (And the setup is quite complex.)
I am skeptical of any off-the-shelf database "scaling" -- beware of marketing hype.  (Web servers are easy to scale; databases are not.)


How do I get postgres to log the stored procedures it is running?

You can see a count of all user-defined functions being called in the system view pg_stat_user_functions.

There is no facility to write all function calls to the log.  That would be quite bulky.

If you're feeling adventurous, you can play with the settings debug_print_parse and debug_print_plan to get more detailed information about what is being called.  Again, this will be bulky.


Server version error where restoring backup with SQL Server Management Studio

The version of SSMS is wholly irrelevant here - the error is referring to SQL Server itself.

In this case you will need to upgrade your SQL Server from 2012 to 2014.

How can I upgrade SQL Server from 2012 to 2014?

As this is Express edition and likely not a production server, I would recommend an inplace upgrade. You will need to download the installation media for SQL 2014 Express here.

Once downloaded, run it and choose the option to Upgrade from a previous version of SQL Server and follow the Wizard from there.

I would advise to take full backups of any databases you can't lose before doing this.

Are there any other ways to do this with less downtime?

You could also perform a side-by-side install of 2014 with 2012, and then migrate the databases over by backing them up then restoring them.

Please note this could impact how people connect to this instance - you can't create the new instance with the same name as the old instance, so if this is how users are connecting you may need to set up new connection strings.

As an aside the version of SQL 2014 you are running has several known issues and security problems - it's 2014 SP1. There have been 2 SP's released since then - I would recommend you speak to whoever is in charge of this box and look to get SQL server upgraded to the latest SP if at all possible.


Powershell dbatools combining result of multiple Get-dbaxxx commands to generate a report

TL;DR
Create a PSCustomObject with the properties of your variables.

PowerShells output formatter uses the first object in the output set as a template for displaying subsequent objects in table format.
If the first object has properties A, B, and C those will be the table headers.
If the second object has only properties D, E, and F then a blank line will be shown.
You can see it for yourself with this example:
$TestResults = @()

$Test1 = [PSCustomObject]@{
    Name = 'Test1'
    Type = 'PSCustomObject'
}
$Test2 = [PSCustomObject]@{
    Name = 'Test2'
    Type = 'PSCustomObject'
}
$Test3 = [PSCustomObject]@{
    Label = 'Test3' # <-- This is "Label", not "Name".
    Type = 'PSCustomObject'
}

$TestResults += $Test1
$TestResults += $Test2
$TestResults += $Test3

# Show output:
$TestResults

Option 1 - Calculated Properties (Wouldn't recommend in your scenario):
So, you could rename the properties in your variables to have the same name using calculated properties?
$Test3 | Select-Object @{ Name = 'Name'; Expression = { $_.Label }}, Type

Then when you add this to your array, all the values should show:
$TestResult_02 = @()

$TestResult_02 += $Test1
$TestResult_02 += $Test2
$TestResult_02 += $Test3 | Select-Object @{ Name = 'Name'; Expression = { $_.Label }}, Type

$TestResult_02

Why not this method for your scenario?
All your properties mean separate things.
The Architecture property of $OSInfo doesn't match up with any property of $DiskSpace for example.
Using calculated properties here would likely end up confusing everyone in time.
Option 2 - Creating a PSCustomObject (Would recommend)
It's easy to build up a PSCustomObject type, especially if you know the output of commands or variables in advance as you do in your scenario.
$ResultObject = [PSCustomObject]@{
    Test1_Name = $Test1.Name
    Test1_Type = $Test1.Type
    Test2_Name = $Test2.Name
    Test2_Type = $Test2.Type
    Test3_Name = $Test3.Label
    Test3_Type = $Test3.Type
}

$ResultObject

Using your examples with the information on my machine, we can create the following PSCustomObject
$FullResults = [PSCustomObject]@{
    ComputerName = $OSInfo.ComputerName
    OSVersion = $OSInfo.OSVersion
    Version = $OSInfo.Version
    Architecture = $OSInfo.Architecture
    InstanceName = $SQLInstanceInfo.DbaInstanceName
    Edition = $SQLInstanceInfo.Edition
    NetPort = $SQLInstanceInfo.NetPort
    IsClustered = $SQLInstanceInfo.IsClustered
    Processors = $SQLInstanceInfo.Processors
    ProductLevel = $SQLInstanceInfo.ProductLevel
    ServiceName = $SQLInstanceInfo.ServiceName
    Disks = $DiskSPace
}

$FullResults


Invoke SQLCMD doesn't work when used with xp_cmdshell

There are several problems here:

The main issue is that the CMD shell executes each line as it comes in via a return; it does not wait for an "end of command" indicator such as ;, nor does it attempt to figure it out via parsing (like SQL does upon a batch being submitted) since there is no way to know if a return ends a "command" or not. Typically you can use ^ for line continuation, but that does not seem to work within an input paramter (at least not one with an unclosed quote).

So, for this you need to reduce everything to a single line. That is simple enough as you can just replace Carriage Return (character 13) with an empty string and Line Feed/Newline (character 10) with a space. But, that alone will not work since PowerShell statements need to be separated by either a newline or a semicolon. Currently newlines are being used, but if we replace those with spaces, then semicolons will be required.

Hence, Step 1 is to append a semicolon to each actual PowerShell command. This is probably good-form in general, same as with T-SQL.
Step 2 would be doing the two REPLACE calls against the @ps variable to transform this into a single-line script.
You have embedded double-quotes, used for creating the INSERT statement. Those need to be escaped with a backslash ( \ ).
Get-WmiObject does not seem to work as you are attempting to use it here. I had to add -ComputerName to pass in the name of a system. If I left it blank then it returned the local system info, but if you want to return info for remote servers then you will probably need to use the -ComputerName switch and pass in one server at a time. That will most likely require an additional outer loop to handle multiple server names (and it would be there that you pass in @ServerNames to get split for iterating through the list).
Are "capacity" and "freespace" really stored as strings in the DB? If not, you might want to remove the single-quotes around those "values" in the INSERT statement.

The following at least executes. You can debug from there. I was getting errors on it not finding the "Invoke-SQLcmd" module or something.

DECLARE @ServerNames NVARCHAR(4000);
SET @ServerNames = N'ALBRIGHT';

DECLARE @ps NVARCHAR(4000);
SET @ps = N'powershell.exe -noexit -c "

$computers=Get-WmiObject -Class Win32_Volume -ComputerName ' + @ServerNames + N';
foreach($computer in $computers)
{
$pscomputername=$computer.pscomputername;
$name=$computer.name;
$capacity=$computer.capacity;
$freespace=$computer.freespace;
$Label=$computer.Label;

$insertquery=\";
INSERT INTO [dbo].[temp_disksdata]
           (
           [servername] ,
           [DiskName] ,
           [Capacity(GB)] ,
           [FreeSpace(GB)],
           [label]
           )
     VALUES
           (''$pscomputername'' ,
           ''$name'' ,
           ''$capacity'',
           ''$freespace'',
           ''$Label''
           )
GO
\";

Invoke-SQLcmd  -query $insertquery -ServerInstance ''someserver'' -Database dbname;

}

"';

SET @ps = REPLACE(REPLACE(@ps, NCHAR(13), N''), NCHAR(10), N' ')
PRINT @ps;

EXEC xp_cmdshell @ps;


Tablespace error SQLCODE=-289, SQLSTATE=57011

This probably means you ran out of space within the DMS tablespace. DMS tablespaces pre-allocate their storage. They can be set to auto-grow when full but perhaps this one isn't, or it's hit the maximum size specified.

For more information on the particular error you can use the "db2 ?" facility. In this case, the error page is particularly verbose (there are many sub-categories) so here's a sample of the output:

$ db2 ? sql0289                                                                                [109/109]

SQL0289N  Unable to allocate new pages in table space
      "<tablespace-name>".

Explanation:

One of the following conditions is true on one or more database
partitions:

1

         One of the containers assigned to this SMS table space has
         reached the maximum file size. This is the likely cause of the
         error.

2

         All the containers assigned to this DMS table space are full.
         This is the likely cause of the error.

[... lots more error codes ...]

User response:

Perform the action corresponding to the cause of the error:

1

         Re-create as an AUTOMATIC STORAGE table space or an SMS table
         space with more directories (PATHs) such that: (number of
         directories) >= (max tablesize / maxfilesize) or re-create.
         Note that maximum file size is operating system dependent.
         Re-creating the table space as AUTOMATIC STORAGE allows you use
         storage groups.

2

         Add new container(s) to the DMS table space and try the
         operation again, after the rebalancer has made the new pages
         available for use.

[... lots more responses ...]

sqlcode: -289

sqlstate: 57011

   Related information:
   ALTER TABLESPACE statement

As it states, the most likely cause is the containers for the tablespace are full, so add some new containers (or extend the existing ones) with ALTER TABLESPACE (there's several examples at the bottom). You will likely need to ask your DBA to do this (as the manual page states, this requires SYSADM or SYSCTRL privilege).


Given the following info:

  Previous count = 0, current count = 1.

the implication is that sp_refreshview is opening an explicit transaction but encountering an unhandled exception such that it doesn't do its own ROLLBACK. The "Previous count = 0" means that your code is not creating the Transaction.

This is not something that you can fix in your code because a subprocess must exit with the same @@TRANCOUNT that it started with. I doubt you will be able to add a ROLLBACK, or even a BEGIN TRAN and then ROLLBACK to help this.

At the very least, you need to figure out which View is causing the problem. You can do that by printing the view name being given to that system stored procedure before executing sp_refreshview, especially if the error is one of the few that cannot be trapped by the TRY...CATCH construct. Just add the following statement just after the BEGIN TRY:

RAISERROR(N'View: %s', 10, 1, @ViewName) WITH NOWAIT;

Also, it would be helpful to see the exact error message, so replace the PRINT in the CATCH block with the following query:

SELECT @Viewname AS [ViewName],
       ERROR_NUMBER() AS [ErrorNumber],
       ERROR_MESSAGE() AS [ErrorMessage];

You can also add the following to the CATCH block, but I doubt it will solve this issue:

IF (@@TRANCOUNT > 0)
BEGIN
  ROLLBACK;
END;

P.S. You should be using sysname (all lower-case) for variable and columns that hold SQL Server object names since that is the datatype used in the system tables for object names. sysname is an alias for NVARCHAR(128).


Mongodb index keeps growing in size eventhough the collection has a TTL index

Compact the collection in your staging environment. If index size decreases, space is taken up by overhead.
Compare the size of compacted collection & index to the size of both pre compaction, identify the overhead amount/percentage.


What could be the possible reasons for cardinality estimates being very inaccurate in an execution plan?

Giving a complete overview of statistics and how they work feels a bit out of scope for a question here. You can find a ton of information about this by searching with your favorite search engine. This white paper is the first result for me.

However, I can say a little bit about your query, reproduced here:

SELECT COUNT(*)
FROM reservations
WHERE length(office_number)=2
and length(regexp_substr(text, '\d*')) = 3
and indicator_flag is null;

When trying to figure out how Oracle arrived at a cardinality estimate I often find it helpful to break the query into smaller pieces and to get cardinality estimates on those smaller pieces. Consider this piece:

SELECT COUNT(*)
FROM reservations
WHERE indicator_flag is null;

Statistics (if they exist) for the indicator_flag column store how many NULL values are in that column. So Oracle can use the statistics object directly to derive an estimate. This estimate can be off if the statistics object isn't present, if statistics are outdated, or if statistics were gathered with the wrong sample rate. For your query I'm going to guess that around 24% of the rows in the table have a NULL value for indicator_flag, so the estimate here should be around 220k rows.

Consider this piece:

SELECT COUNT(*)
FROM reservations
WHERE length(office_number)=2;

Here a function is applied to the column. That can make things more complicated because Oracle won't store statistics on the length of the office_number column directly. In this situation RDBMs need to make a guess. On the version of Oracle that I'm using the guess appears to be pretty simple: 1% of the rows of the table for integer values of length. Comparisons to NULL or NOT NULL use the statistics on the column. This is very likely to be inaccurate because Oracle is making a guess that isn't based on your data. The estimate as calculated by Oracle should be around 9100 rows.

Consider this piece:

SELECT COUNT(*)
FROM reservations
WHERE
length(regexp_substr(text, '\d*')) = 3;

The analysis above applies to this part as well, so the estimate as calculated by Oracle should be around 9100 rows.

How does Oracle combine the estimates to get a cardinality estimate for the full query? In my experience it tends to assume the predicates are independent unless it has better information. So your final estimate should be 910087 * 0.01 * 0.01 * 0.24 = 22 rows.

If you need the estimate to be better to improve the performance of queries you have a few options depending on where the issue is. You can create virtual columns based on the length, you can create multi column statistics to make the relationship between columns more obvious to Oracle, you can add a dynamic sampling hint to the query, and so on.


Trans Log fills during delete, conceptual question

As Dan Guzman originally mentioned in a comment, your understanding of why the active portion of the transaction log couldn't be cleared is correct (it was being used for the large rollback).

There is one thing of interest that I think is worth mentioning related to this:

  The query appeared to keep running even when the trans log got full. I then cancelled the query, and it showed cancelling.

Whenever log records are written to the transaction log, additional space is reserved to accommodate a rollback in case an error is encountered (or an explicit rollback is requested).  This is documented in the MS Docs article SQL Server Transaction Log Architecture and Management Guide, specifically in the section on "Transaction Log Logical Architecture."

When the transaction log looked full, the query execution encountered and error and automatically started rolling back.  Had you waited for this to complete, you would have seen this error message:

  Msg 9002, Level 17, State 4, Line 1
  The transaction log for database 'YourDatabaseName' is full due to 'ACTIVE_TRANSACTION'.


sp_Blitz does not warn "No Alerts for Corruption" if one of 3 is set

Correct. The finding is "No Alerts for Corruption". If you've set up alerts for any of those, we assume you know what you're doing. (That's not always the case, of course, heh.)

If you'd like to change that, here's the guide on contributing to sp_Blitz.

(Disclaimer: I'm Brent Ozar, the script author.)


DB comparer field by field for all tables

There are many free tools - native and opensource available for comparing schema between 2 databases:

Open DBDiff ==> This is on Codeplex and works great.
SQL Admin Studio ==> This is now a free tool.
Hidden Gem from SQL 2005 and up :  tablediff.exe (you can find this in the COM directory of your SQL Server install folder) Compare schemas: Regular or Strict
Powershell - You have to write your own code or build up on existing ones
Since you are using SQL Server 2012, SSDT is also an option.

From my experience, its worth to invest in Redgate's Schema and data compare tools as they have commandline options and they integrate well with Powershell as well.


This is not a clean problem.  The following should work assuming that there will not be multiple street addresses with different suite numbers.  Also the data will have to be formatted with the comma and space before the Suite.  I added a duplicate record (1 Burton Hills...) for testing the group by logic.

Create Table Addresses (Address Varchar(8000))

Insert Addresses (Address) Values ('1 Burton Hills Blvd, Suite 250')
Insert Addresses (Address) Values ('1 Burton Hills Blvd, Suite 250')
Insert Addresses (Address) Values ('1 Burton Hills Blvd')
Insert Addresses (Address) Values ('1 Burton Hills Blvd')
Insert Addresses (Address) Values ('1 Burton Hills Blvd')
Insert Addresses (Address) Values ('1 Burton Hills Blvd')
Insert Addresses (Address) Values ('1 Burton Hills Blvd')
Insert Addresses (Address) Values ('1 Ford Pl')
Insert Addresses (Address) Values ('1 Ford Pl, Suite 5B')
Insert Addresses (Address) Values ('1 Kish Dr')
Insert Addresses (Address) Values ('1 Park Center Dr, Suite 11')
Insert Addresses (Address) Values ('1 Park Center Dr')

;
With cteWithSuite As
    (
    Select Substring(Address, 1, CharIndex(', Suite', Address)-1) AddressRoot, Address
        From Addresses
        Where CharIndex(', Suite', Address) > 0
        Group By Substring(Address, 1, CharIndex(', Suite', Address)-1), Address
    )
Update B Set B.Address = A.Address
    From cteWithSuite A
    Join (Select Address
            From Addresses
            Where CharIndex(', Suite', Address) = 0
        ) B On B.Address = A.AddressRoot


SSIS - executing package from SQL Server by stored procedure

If you are using SSISDB (which I think you are?), you can Execute a set of standard stored procedures that are in the SSISDB. These are outlined in the MSDN Article here.

This article also states the permissions that are required to run these procedures. It sounds like you need to set up a SQL Server login which has these permissions. How to describe this is located here. You will need to do this with your existing Windows Authenticated user if it has permission to allocate permissions (ALTER ANY LOGIN or ALTER LOGIN permissions).

You may also need to create a user who is mapped to this login as described here.

Your question is not very specific so without further information about a particular problem you are having, it is difficult to give a clear answer.


What causes "attribute key cannot be found when processing" errors?

In my case, I had 2 tables: Sales and Company.

I had one record 'ABC' in Sales, but it was supposed to be in Company. Because of that, when I processed the package I got the same error.

To fix it, I just created a row with '0' Id  in Company, and kept all the columns in that particular row as unknowns. After that, I processed the package and it worked.


DB disk space requirements comparision between MySQL versions

One thing must be noted that you probably never realized: The setting of the option innodb_file_per_table will determine how much space is inside each database folder.

First, take a look at the InnoDB Architecture

Note the system tablespace (which was know is named ibdata1 in the OS).

In MySQL 5.0, the default value for innodb_file_per_table is 0 or OFF
In MySQL 5.6, the default value for innodb_file_per_table is 1 or ON
With innodb_file_per_table disabled (default in 5.0), the data and index pages for all InnoDB tables are stored inside ibdata1. When you create the table mydb.mytable, you get the following:

/var/lib/mysql/mydb/mytables.frm
Data and index pages for mydb.mytable are inside ibdata1

With innodb_file_per_table enabled (default in 5.6), the data and index pages for all InnoDB tables are outside ibdata1.  When you create the table mydb.mytable, you get the following:

/var/lib/mysql/mydb/mytables.frm
Data and index pages for mydb.mytable are stored in /var/lib/mysql/mydb/mytables.ibd

SUMMARY

Without setting innodb_file_per_table and just storing data

Databases under MySQL 5.0 will have nothing but .frm files for its InnoDB tables. Thus, you only have kilobytes of files in each database.
Databases under MySQL 5.6 will have .frm and .ibd files for its InnoDB tables. Thus, you will have megabytes or gigabytes of files in each database.


Alternative tools to export Oracle database to SQL Server?


Cannot grant execute to stored procedure

Try putting a GO on a newline between your procedure DDL and your grant statement and after the grant.


What happens when you create the primary key of a table on a different filegroup?

It's all about clustered vs non-clustered indexes actually.  Primary key has nothing to do with it.  A clustered index IS the data. The actual table data is contained in the data pages of the clustered index.

If you put a clustered index on a table then the data is stored where you tell the clustered index to be.  If you changed your code above to be a non-clustered primary key then the data would be in [common_data] (and it would be a heap as it happens) and the non-clustered index for the primary key would be in [common_index].  If you then created a clustered index and pointed it to [uncommon_data] then the table data would be moved to the new filegroup and nothing would remain on [common_data].

Oh, and the reason you can do specify two separate filegroups is because you are working with two different parts of the syntax.  The ON [common_data] (FG for the table) tells SQL where to put the table.  The ON [common_index] (the FG for the index) tells SQL where to put the index.  In this case because it's a clustered index it forces the data to go with it.


Dimension for Product Properties based on Product Type - Sparse Dimension?

I would not create a separate foreign key for each product type. This is known as a centipede fact table.

http://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/centipede-fact-table/

I would consider creating separate fact tables for products which can logically be grouped. ie FactTechnologySales, FactBookSales. And then you would have DimTechnologyProducts and DimBooks dimensions. This would minimise NULL fields in your dimension table. (note that I would actually populate these NULL fields with N/A rather than leave them NULL).

If you want to only have one fact table, then create a product dimension with attributes like Product Name, Product Description, Product Category, Product Type. Where Product Description is Model + OS + Size for the smartphones and Author + Title for books. Use the Product Category and Type fields to create a hierarchy for users to drill down. For any other attributes that don't fit into the description, then put them in a Junk dimension.


You can use generate_series() to generate a series of dates, and extract() to get day of week.

Then simply filter those dates where day of week are not 0=Sunday, 6=Saturday.

with days as
(
    select dd, extract(DOW from dd) dw
    from generate_series('2017-04-28'::date, '2017-05-02'::date, '1 day'::interval) dd
)
select *
from   days
where  dw not in (6,0);

dd                     | dw
:--------------------- | :-
2017-04-28 00:00:00+01 | 5
2017-05-01 00:00:00+01 | 1
2017-05-02 00:00:00+01 | 2

dbfiddle here

If you have to exclude public holidays and other non-business days, you can build a business_day table.  Just insert the output from above and then remove all days that have to be excluded (in certain countries, like Hungary, there might be additional replacement days (typically Saturdays) which have to be added, too).  Of course, this has to be maintained (for example, you can prepare the next year every December), but as there is no built-in functionality that knows about those days, you have no better option.

Using a calendar table

Let me create a sample calendar table and insert some values:

create table calendar
(
    id serial primary key,
    cal_day date not null,
    bussines_day bool not null
);

insert into calendar (cal_day, bussines_day) values
('20180101', false), ('20180102', true),
('20180103', false), ('20180104', true),
('20180105', false), ('20180106', true),
('20180107', false), ('20180108', true),
('20180109', false), ('20180110', true),
('20180111', false), ('20180112', true);

Now you can use a function to obtain the next Nth business day in this way:

create or replace function add_business_day(from_date date, num_days int)
returns date
as $fbd$

    select max(cal_day) as the_day
    from (select   cal_day
          from     calendar
          where    cal_day > $1
          and      business_day = true
          order by cal_day
          limit    $2) bd;

$fbd$ language sql;

or

create or replace function add_business_day2(from_date date, num_days int)
returns date
as $fbd$

    select cal_day
    from   (select cal_day,
                   row_number() over (order by cal_day) rn
            from   calendar
            where  cal_day > $1
            and    business_day = true
            limit  $2) bd
    where  rn = $2;

$fbd$ language sql;

Both return same result:

select add_business_day('20180103', 4);

| add_business_day |
| :--------------- |
| 2018-01-10       |

select add_business_day2('20180103', 4)

| add_business_day2 |
| :---------------- |
| 2018-01-10        |

db<>fiddle here


Working out Duplicated Data in a Customer Data Base

Here are your duplicates, then everything else is your non-duplicate.

with c as (
    select *,
    row_number() over (partition by PostSur, Name  order by ID) as rowID
    from Aron_Reporting.dbo.Customer_NewSort
)
select c.*
    from c
    inner join c c2 on c.PostSur = c2.PostSur AND c.rowID > 1 AND c2.rowID = 1 as rowID
     order by PostSur

This code partitions the row numbers by PostSur but also name, that way every person with a different name, or PostSur starts the numbering over. I joined the results back to the anchor, but you really don't need that necessarily.

I think I understood your requirements, hopefully this at least gets you close.


How can I insert procedure results from server 1 into table in server 2?

One option is to create a "linked server". This allows you to access another server tables. To do this you use the server name as a prefix.
Before addressing this option verifies with the DBAif  is an option allowed in the organization. Also check permissions issues.

This link from Microsoft has more information. The following example is taken from the same link:

How to create the link?

USE [master]
GO
EXEC master.dbo.sp_addlinkedserver
    @server = N'SRVR002\ACCTG',
    @srvproduct=N'SQL Server' ;
GO

How can reference a table from another server?

SELECT local.name AS LocalLogins, linked.name AS LinkedLogins
FROM master.sys.server_principals AS local
LEFT JOIN [SRVR002\ACCTG].master.sys.server_principals AS linked
    ON local.name = linked.name ;
GO


SARG cardinality estimate, why not full-scan?

When comparing values of different datatypes SQL Server follow the Data Type Precedence rules. Since nvarchar has higher precedence than varchar SQL Server has to convert the column data to nvarchar before comparing values. That means applying a function on the column and that would make the query non-sargable.

SQL Server does however do it's best to protect you from your mistakes so it uses a technique described by Paul White in the blog post Dynamic Seeks and Hidden Implicit Conversions to do a seek for a range of values and then doing the final comparison, with the conversion of the column value to nvarchar, in a residual predicate to filter out any false positives.

As you have noted, this does however not work when the collation of the column is a SQL collation. The reason for that, I believe, can be found in the article Comparing SQL collations to Windows collations

Basically, a Windows collation uses the same algorithm for varchar and nvarchar where a SQL collation uses a different algorithm for varchar data and the same algorithm as a Windows collation for nvarchar data.

So going from varchar to nvarchar under a Windows collation will use the same algorithm and SQL Server can produce a range of values from, in your case, a nvarchar literal to get rows from the varchar SQL collation column index. However, when the collation of the varchar column is a SQL Collation that is not possible because of the different algorithm used.

Update:

A demonstration of the different sort orders for varchar columns using windows and sql collation.

SQL Fiddle

MS SQL Server 2014 Schema Setup:

create table T(C varchar(10));

insert into T values('a-b'),('aa'),('ac');

Query 1:

select C
from T
order by C collate SQL_Latin1_General_CP1_CI_AS;

Results:

|   C |
|-----|
| a-b |
|  aa |
|  ac |

Query 2:

select C
from T
order by C collate Latin1_General_100_CI_AS;

Results:

|   C |
|-----|
|  aa |
| a-b |
|  ac |


What's the point in SQL Server Central Management Server?

The main difference between Registered Servers and Central Management Servers is where the server list is stored, and who it can be used by. There's no functional difference in multi-server queries -- at that point, the query window is connected to servers, regardless of which of the two features you used to get there.

It's unclear why that text is a warning in the documentation; I think it's just an advisory that you need to set up a server list before following any of the instructions (or they'd have to fork the instructions for Registered Servers vs. CMS). I think it should include language that indicates using Registered Servers (aka local server groups) is also a possibility, but I can see why they did what they did to simplify the instructions.

If you're interested in learning more about this topic, I have a two-part video series on my blog that you may like: Part 1, Part 2


Mysql 8 accidentally lost ROOT user all privileges in localhost wampserver 3.2

Thank god,Finally it worked out.
first I created a file named as mysql-init.txt in c drive.The file contains

update mysql.user
set Select_priv='Y',
    Insert_priv='Y',
    Update_priv='Y',
    Delete_priv='Y',
    Create_priv='Y',
    Drop_priv='Y',
    Reload_priv='Y',
    Shutdown_priv='Y',
    Process_priv='Y',
    File_priv='Y',
    Grant_priv='Y',
    References_priv='Y',
    Index_priv='Y',
    Alter_priv='Y',
    Show_db_priv='Y',
    Super_priv='Y',
    Create_tmp_table_priv='Y',
    Lock_tables_priv='Y',
    Execute_priv='Y',
    Repl_slave_priv='Y',
    Repl_client_priv='Y',
    Create_view_priv='Y',
    Show_view_priv='Y',
    Create_routine_priv='Y',
    Alter_routine_priv='Y',
    Create_user_priv='Y',
    Event_priv='Y',
    Trigger_priv='Y'
where User='root';

Then I run this command.

D:\wamp3\bin\mysql\mysql8.0.18\bin>mysqld --init-file=c:\\mysql-init.txt --conso
le

Then login as root user.Now all database showing up.


Is there a timeout for a query over database link?

Since you're using dbms_scheduler, you can set the max_run_duration attribute of the job to some limit, and then have the scheduler email you if that event is raised.  Behind the scenes Oracle uses queueing tables (which can enable you to create jobs that fire when an event is raised, if you wanted to take the extra steps to do more automation around your response).  But basically any job that runs over the max_run_duration you setup will raise event type: JOB_OVER_MAX_DUR

The email piece is built in 11gr2, see here for good writeup.

Hope that helps.


Why would recompile query hint result in different plan for same adhoc statement after freeproccache?

The query you posted contains variables.

SQL Server doesn't do variable sniffing so without OPTION (RECOMPILE) it will compile a general plan as it would for OPTIMIZE FOR UNKNOWN.

I don't really follow your question though. At one point you seem to be saying that the version without the hint is "much much faster" and  then later you say the version with the hint is "much better". So which one is it?

Both are explicable however. If you find the version with the hint is better than this is because SQL Server can use statistics to estimate the number of rows that will be matched by the date predicate and choose an appropriate plan for that case.

If the version without the hint is better the statistics themselves may need updating. Perhaps when they were last updated there were few or no rows meeting that predicate and so SQL Server massively underestimates the number of rows that will be returned. See Statistics, row estimations and the ascending date column for more about this potential issue.


I guess it has something to do with the fact, that db2 will return an error code of 1 when no records were found.


Why different datetimes freezes a query based on indexes?

We have had the similar issue when dealing with big (billions of records) tables. As Martin Smith suggested in comment - you would probably need to periodically rebuild statistic data on that table.
This worked for us:

UPDATE STATISTICS <TABLE NAME> WITH FULLSCAN

This solution is kind of brute-force and being too simple but still it may help.