How to set Parent field according to another hierarchy field I need some help to set a parent field according to hierarchy field like this:

declare @Chart table([ChartId] int NOT NULL identity(1,1), [ParentId] int, [Number] varchar(5),
    [Serial] varchar(5), [AccountName] varchar(200), CurrencyId int, cType char(1),
    [TotalDebit] decimal(18,2), [TotalCredit] decimal(18,2));

insert into @Chart
select null,  '1    ', null, 'CAPITAL & LONG TERM LIABILITIES', 1, 'T', 0, 0 union all
select null, '10   ', null, 'CAPITAL & LONG TERM LIABILITIES', 1, 'T', 0, 0 union all
select null, '101  ', null, 'CAPITAL & LONG TERM LIABILITIES', 1, 'T', 0, 0 union all
select null, '1013 ', null, 'CAPITAL & LONG TERM LIABILITIES', 1, 'T', 0, 0 union all
select null, '1013 ', '00001', 'ShareHolder 1', 1, 'R', 0, 0 union all
select null, '1013 ', '00002', 'ShareHolder 2', 1, 'R', 0, 0 union all
select null, '1013 ', '00003', 'ShareHolder 3', 1, 'R', 0, 0 union all
select null, '4     ', null, 'SUPPLIERS', 1, 'T', 0, 0 union all
select null, '40   ', null, 'SUPPLIERS', 1, 'T', 0, 0 union all
select null, '401  ', null, 'SUPPLIERS', 1, 'T', 0, 0 union all
select null, '4011 ', null, 'SUPPLIERS', 1, 'T', 0, 0 union all
select null, '4011 ', '00101', 'Supplier 1', 1, 'R', 0, 0 union all
select null, '4011 ', '00102', 'Supplier 1', 2, 'R', 0, 0 union all
select null, '4011 ', '00103', 'Supplier 2', 1, 'R', 0, 0 union all
select null, '4011 ', '00104', 'Supplier 3', 2, 'R', 0, 0 union all
select null, '4011 ', '00105', 'Supplier 3', 1, 'R', 0, 0 union all
select null, '4011 ', '00105', 'Supplier 3', 3, 'R', 0, 0 union all
select null, '41   ', null, 'CUSTOMERS', 1, 'T', 0, 0 union all
select null, '411  ', null, 'CUSTOMERS', 1, 'T', 0, 0 union all
select null, '4111 ', null, 'CUSTOMERS', 1, 'T', 0, 0 union all
select null, '4111 ', '00101', 'Customer 1', 1, 'R', 0, 0 union all
select null, '4111 ', '00102', 'Customer 2', 1, 'R', 0, 0 union all
select null, '4111 ', '00103', 'Customer 2', 2, 'R', 0, 0 union all
select null, '4111 ', '00104', 'Customer 3', 2, 'R', 0, 0 union all
select null, '4111 ', '00105', 'Customer 4', 2, 'R', 0, 0 union all
select null, '4111 ', '00106', 'Customer 4', 3, 'R', 0, 0 union all
select null, '4111 ', '00107', 'Customer 5', 3, 'R', 0, 0;

Select * from @Chart;

What i wish is to update ParentId column as Parent and Id as keyfield and this according to Number Column like: left(number, 1) should be 1 and left(number, 2) should be linked to its parent which is 1... left(number, 3) should be related to its parent ...
the output should look like:

I would be highly appreciate any help.

Thank you

Direct update:

UPDATE c0
SET    ParentId = CASE WHEN c0.Serial IS NULL
                  THEN (SELECT TOP 1 c1.ChartId
                        FROM   @Chart c1
                        WHERE  c1.[Number] < c0.[Number]
                               AND c1.Serial IS NULL
                               ORDER BY c1.[Number] DESC)
                  ELSE (SELECT TOP 1 c2.ChartId
                        FROM   @Chart c2
                        WHERE  c2.[Number] = c0.[Number]
                        AND    c2.Serial IS NULL)
                  END
FROM   @Chart c0;

Using a CTE to calculate ParentId:

;WITH x AS
(
    SELECT
        c1.ChartId,
        CASE WHEN c1.Serial IS NULL
             THEN LAG(c1.ChartId) OVER (ORDER BY c1.[Number], c1.Serial)
             ELSE (FIRST_VALUE([ChartId]) OVER (PARTITION BY [Number] ORDER BY [Number], Serial))
             END AS ParentID
    FROM
        @Chart c1
)
UPDATE c1
SET    ParentId = x.ParentId
FROM   @Chart c1
JOIN   x
       ON x.ChartId = c1.ChartId;

ChartId | ParentId | Number | Serial | AccountName                     | CurrencyId | cType | TotalDebit | TotalCredit
------: | -------: | :----- | :----- | :------------------------------ | ---------: | :---- | :--------- | :----------
      1 |     null | 1      | null   | CAPITAL & LONG TERM LIABILITIES |          1 | T     | 0.00       | 0.00
      2 |        1 | 10     | null   | CAPITAL & LONG TERM LIABILITIES |          1 | T     | 0.00       | 0.00
      3 |        2 | 101    | null   | CAPITAL & LONG TERM LIABILITIES |          1 | T     | 0.00       | 0.00
      4 |        3 | 1013   | null   | CAPITAL & LONG TERM LIABILITIES |          1 | T     | 0.00       | 0.00
      5 |        4 | 1013   | 00001  | ShareHolder 1                   |          1 | R     | 0.00       | 0.00
      6 |        4 | 1013   | 00002  | ShareHolder 2                   |          1 | R     | 0.00       | 0.00
      7 |        4 | 1013   | 00003  | ShareHolder 3                   |          1 | R     | 0.00       | 0.00
      8 |        4 | 4      | null   | SUPPLIERS                       |          1 | T     | 0.00       | 0.00
      9 |        8 | 40     | null   | SUPPLIERS                       |          1 | T     | 0.00       | 0.00
     10 |        9 | 401    | null   | SUPPLIERS                       |          1 | T     | 0.00       | 0.00
     11 |       10 | 4011   | null   | SUPPLIERS                       |          1 | T     | 0.00       | 0.00
     12 |       11 | 4011   | 00101  | Supplier 1                      |          1 | R     | 0.00       | 0.00
     13 |       11 | 4011   | 00102  | Supplier 1                      |          2 | R     | 0.00       | 0.00
     14 |       11 | 4011   | 00103  | Supplier 2                      |          1 | R     | 0.00       | 0.00
     15 |       11 | 4011   | 00104  | Supplier 3                      |          2 | R     | 0.00       | 0.00
     16 |       11 | 4011   | 00105  | Supplier 3                      |          1 | R     | 0.00       | 0.00
     17 |       11 | 4011   | 00105  | Supplier 3                      |          3 | R     | 0.00       | 0.00
     18 |       11 | 41     | null   | CUSTOMERS                       |          1 | T     | 0.00       | 0.00
     19 |       18 | 411    | null   | CUSTOMERS                       |          1 | T     | 0.00       | 0.00
     20 |       19 | 4111   | null   | CUSTOMERS                       |          1 | T     | 0.00       | 0.00
     21 |       20 | 4111   | 00101  | Customer 1                      |          1 | R     | 0.00       | 0.00
     22 |       20 | 4111   | 00102  | Customer 2                      |          1 | R     | 0.00       | 0.00
     23 |       20 | 4111   | 00103  | Customer 2                      |          2 | R     | 0.00       | 0.00
     24 |       20 | 4111   | 00104  | Customer 3                      |          2 | R     | 0.00       | 0.00
     25 |       20 | 4111   | 00105  | Customer 4                      |          2 | R     | 0.00       | 0.00
     26 |       20 | 4111   | 00106  | Customer 4                      |          3 | R     | 0.00       | 0.00
     27 |       20 | 4111   | 00107  | Customer 5                      |          3 | R     | 0.00       | 0.00

db<>fiddle here


sql agent job timeout auto restart we have a DWH load job that sometimes hangs.  We have not been able to determine why this is happening it seems to be random and network/hardware related (which we have no control over).  We are ordering new hardware which will isolate our processes but this is weeks away.  Therefore when our job hangs, we would like to specify a timeout option if any of the steps reaches a certain amount of time and have it automatically restarted.  How can this be done?  Thanks!

Use another job to check periodically if your job is running, at which step it is running, and what the value for your timeout is. You can do this by running a stored procedure in your job. I wrote the stored procedure for you, if you have any question just comment :)

-- =============================================
-- Author:      <Wynants Stijn>
-- Create date: <30/03/2016>
-- Description: <GetHangingJob and Restart at step>
-- =============================================
CREATE PROCEDURE dbo.usp_GetHangingJob
    -- Add the parameters for the stored procedure here
    @JobName varchar(1000),
    @TimeOut int   ---In minutes
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @JobState varchar(20)
    DECLARE @JobStep int
    DECLARE @JobStepName varchar(1000)
    SET @JobState = (select top 1
        case when
        case when last_executed_step_date is null then a.run_requested_date else last_executed_step_date end > DATEADD(mi,-@TimeOut,getdate()) THEN 'OK' else 'NOT OK' end as JobState
    from
        msdb.dbo.sysjobs_view j
    inner join
        msdb.dbo.sysjobactivity a
    on
        j.job_id = a.job_id
    where
        a.run_requested_date is not null
    and
        a.stop_execution_date is null
    and
        j.name = @JobName)

    SET @JobStep = (select case when last_executed_step_id is null then 1 else last_executed_step_id + 1 end as CurrentJobStep
    FROM
            msdb.dbo.sysjobs_view j
    inner join
        msdb.dbo.sysjobactivity a
    on
        j.job_id = a.job_id
    where
        a.run_requested_date is not null
    and
        a.stop_execution_date is null
    and
        j.name = @JobName)

    SET @JobStepName = (select top 1 s.step_name from msdb.dbo.sysjobs j inner join msdb.dbo.sysjobsteps s on j.job_id = s.job_id and s.step_id = @JobStep and j.name = @JobName)
    IF @JobState = 'NOT OK'
    BEGIN
        exec msdb.dbo.sp_stop_job @JobName
        WAITFOR DELAY '00:00:02'
        exec msdb.dbo.sp_start_job @JobName,NULL,NULL,NULL,@JobStepName,NULL
    END

END
GO

Enjoy!


RMAN 11g/12c Client compatibility I'm having a Recovery catalog database 12c version. While I'm trying to register the target database (11g) with the catalog I get the following error:

Recovery Manager: Release 12.1.0.2.0 - Production on Fri Aug 14 13:03:44 2015

Copyright 1982, 2014, Oracle and/or its affiliates. All rights reserved.

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-06429: TARGET database is not compatible with this version of RMAN
RMAN-06618: RMAN client and database release mismatch; indicated database release is 11.2.0.4

I have checked the compatibility matrix, it says the RMAN client and the target database has to be the same version. But I'm using Catalog as 12c database therefore the client is 12 as well. Should I use RMAN 11g client to connect to the target? If yes, How should I do that?

You must use the RMAN executable that is in the classpath that is used to run (or how would you say that) your database. That is the one from 11g. Just do . oraenv <database> (note space between . and oraenv) to set the correct path and then execute rman.


Fix deleted transaction log file in SQL Server I have made the biggest mistake and deleted the transaction log file. Is there a way to fix this issue? I have the backup file, is there a way to restore it or reset everything again?

Backup contains the log file as well.

Just restore the database using tsql

Restore database db_name from disk = ' path to backup file.bak' with replace, stats=10

Based on your screenshot, it seems a connection issue .. you are not able to connect to SQL server with ssms.

Also, see this blog by Aaron https://sqlblog.org/2011/01/14/troubleshooting-error-18456


Understanding SQL Server plan cache I've been reading about the SQL Server plan cache and optimizing for adhoc workloads, etc. I'm fairly new to the topic and have a few general questions to help paint a better picture:

Suppose I have several databases running under one instance of SQL Server:

(1) Is there a different plan cache per database? More specifically, when I examine sys.dm_exec_cached_plans am I looking at database-specific information or instance-wide information?

(2) Is 'optimize for adhoc workloads' set per database or per instance?

Both the Plan Cache and 'optimize for adhoc workloads' are per instance.


Load balancing between 2 servers using MySQL Router I want to setup a simple master-slave MySQL replication. I also want to balance the load between the 2 servers. Can I use MySQL Router 2.1 for that purpose? As I read the official documentation, it seems that MySQL Router now only works with InnoDB cluster. I don't have a need for InnoDB cluster. I just want to load balance 2 servers. Is the Router a good way to go, or should I look elsewhere?

Yes! You can have a router for basic Master-Slave Replication. But use the routing strategy as "first-available".

Though this works, you should be aware that your slave will be in read-write mode (if you allowed it to be) and all write operations performed on slave should be manually synced with master.


When migrating all the way down, should I just run VACUUM (FULL) as the last down migration? I use node-pg-migrate and Amazon RDS Postgres on a development environment where I'm the process of building an app.

Sometimes I migrate all the way down so that the database is almost as it was when I created the instance.

I found recently that when I did several migrations up and down again, that I ran out of storage (5GB) after the last up migration. After I migrated fully down not the full 5GB is available. That may be because some persistent objects occupy some space.

Nevertheless, I noticed when I ran VACUUM FULL, that more storage was available.

Update. It turns out that more storage may have also become available due to autovacuum which may have coincided with my VACUUM FULL query within graph accuracy in my first test. See the graph below of the free storage space during a second test where I intentionally waited a bit before running VACUUM FULL.

Should I (still) create a down migration file that runs last and runs a VACUUM FULL query to free up storage space? I suspect no downsides since all my objects and data are gone. And autovacuum will do it anyway within minutes.

Other than taking time and resources to run vacuum full will do no harm.

The autovacuum process will eventually trim empty tables. but tables that are merely sparse will continue to consume disk space until a vacuum full.


How to find Stored Procedures With Recompile? In my databases I have some Stored procedure with recompile

The way I find them is:

SELECT OBJECT_NAME(ID)AS SP_NAME,*
FROM SYSCOMMENTS
WHERE TEXT LIKE '%WITH RECOMPILE%'

when I look at:

select * from sys.procedures

I find no indication of recompiles.

Is there a better or more elegant way to find the with recompile stored procedures?

Per database, you can do this:

SELECT          DISTINCT
                DB_NAME() AS DBName,
                SO.name AS SPName,
                SM.is_recompiled,
                ISR.SPECIFIC_SCHEMA
FROM            sys.sql_modules AS SM
LEFT OUTER JOIN master.sys.databases AS sDB
    ON SM.object_id = DB_ID()
LEFT OUTER JOIN sys.sysobjects AS SO
    ON  SM.object_id = SO.id
    AND SO.type = 'P'
LEFT OUTER JOIN INFORMATION_SCHEMA.ROUTINES AS ISR
    ON  ISR.ROUTINE_NAME = SO.name
    AND ISR.SPECIFIC_CATALOG = DB_NAME()
WHERE           SM.is_recompiled = 1;


indexes, foreign keys and optimization Not a DBA so pardon my ignorance...

I have a table with 20 fields of which 8 are foreign keys. Mysql has by default added an index for each foreign key. Table has about 100k rows.
When retrieving the data some of the foreign keys are used and some are not. Also, I would like to add indexes to two more fields that are used frequently while querying.

My questions are -

1) In the following code should we just index the field "criteria" or should col1, col2 be created as a non-clustered index?

SELECT col1, col2
FROM table1
WHERE criteria

My understanding - If only the field "criteria" was indexed then the query would use the index to resolve which rows to retrieve. Post that it would do a full table scan to retrieve the relevant rows

2) Can I remove the indexes on the foreign keys and still have the foreign key constraints work?

3) 10 indexes on a table with 20 fields. Would that cause a performance issue during inserts?

4) The most optimal storing scheme for the indexes - BTREE, RTREE and HASH. I assumed BTREE for queries that have > or < and hash for the = queries. Is that the right way to go?

Any suggestions would be greatly appreciated.

In the example you show, the expression would use the index on criteria to resolve which rows to retrieve, and the leaf nodes in the index contain references to the corresponding rows, either by physical location in the case of MyISAM or by primary key value in the case of InnoDB.  That way it avoids a full table scan.
Note that if the query optimizer thinks your query matches too much of the table, it will ignore the index, because it's just as fast to do the table-scan.  By analogy, this is why no one bothers to put the word "the" in the index at the back of a book.

No, MySQL requires that an index exists for the foreign key.  If an index already exists, InnoDB will use it, but it will create one if necessary.  I have found some corner cases where you can trick InnoDB by dropping the index after defining the foreign key constraint, but this would not be advisable, because the index is important for checking referential integrity quickly.

It's true that insert/update/delete needs to update indexes to reflect values added or removed.  But it's not as bad as you might fear.  Also, InnoDB implements a "change buffer" which records how your update affects secondary indexes, and reconciles it with the whole index later.

InnoDB and MyISAM support only BTREE indexes.  InnoDB also has an internal feature called the adaptive hash which does do a hash lookup for frequently-requested index entries.  But this is completely automatic and transparent; you don't have to define any index or tune any config parameters.


Intermittent failures to connect to 127.0.0.1 I have an instance of SQL Server 2016 (13.0.4451) which I can reliably connect to over shared memory (lpc:(local)\instance).  If instead I try to connect to 127.0.0.1,1433 I experience low (~1/1000) failure rates with the error message:

  Connection Timeout Expired.  The timeout period elapsed while attempting to consume the pre-login handshake acknowledgement.  This could be because the pre-login handshake failed or the server was unable to respond back in time.  The duration spent while attempting to connect to this server was - [Pre-Login] initialization=3; handshake=14995;

What would affect the loopback connection, but not the shared memory connection?

Related, but far more broad: Connection Timeout Expired without apparent network issue

Since this question is already quite old, I won't bother asking you to clarify the problem, and will instead try to focus on providing some things that might be worthwhile looking at, that some future visitor might find helpful.

Microsoft .Net Framework 4.5 and 4.5.1 have a known issue with connections to localhost
It's possible your server is suffering from thread-starvation.  Confirm the maximum number of expected simultaneous connections, including pooled connections that might not be actively performing tasks is below the maximum number of threads for your server.
Confirm there are no network-related filters in place that might be dropping connections.  Running netsh winsock show catalog will show the base service providers and any layered service providers that might be interfering with connectivity.  Particularly look for anti-virus or firewall apps.
Check network utilization, and if necessary tune networking parameters for performance.
If SQL Server is running in a virtual machine, check the host server to ensure a memory reservation has been set that prevents memory being swapped out (VMware, Hyper-V).  Ensure a CPU reservation has been set to prevent the virtual machine being starved of processing power.


How to add 100 to all strings starting with a digit in an attribute I have a table doc_properties
with columns

id (int)
doc_property_category (int)
prop_id (int)
value (string)

I want to increment all "number-value"-attributes (besides values starting with a number there are also values like "luxury-binding")in column value with 100.

For example in table doc_properties, the column value "0100s" should become "0200s", "1500s" should become "1600s", "1900s" should become "2000s" etc. The alphanumeric digit ends always with 's' and it starts always with 4 digits eg. 0100s, 0200s, 0300s, 1700s, 1800s, 2000s, 2100s.

What I'm looking for is a SQL that:

first check if the value starts with a digit, if so
remove trailing "s" from the string like in "1900s" => "1900"
convert into integer and add 100
convert back into string and add "s" so the result is "2000s".

Is this possible with MySQL only?

My attempt:

CREATE PROCEDURE ROWPERROW()
BEGIN
DECLARE n INT DEFAULT 0;
DECLARE i INT DEFAULT 0;
SELECT COUNT(*) FROM doc_properties INTO n;
SET i=0;
WHILE i<n DO
  UPDATE doc_properties SET value=value + 100 WHERE concat('',value * 1) = value;
  SET i = i + 1;
END WHILE;
End;
;;

I'm missing step 1 and the conversion to integer and back to string.

Can I do the conversion with:

SELECT field,CONVERT(SUBSTRING_INDEX(value,'s',-1),UNSIGNED INTEGER) AS num
FROM doc_properties;

If so how to integrate it into my loop?

It assumes that value field length is always 5 and s is located always at end of the string.

update doc_properties
set value = concat(lpad(cast(left(value, 4) as signed) + 100, 4, '0'), 's')
where substr(value, 1, 1) between '0' and '9';

✓

select * from doc_properties;

id | doc_property_category | prop_id | value
-: | --------------------: | ------: | :----
 1 |                     1 |       1 | 0300s
 2 |                     1 |       1 | 1300s
 3 |                     1 |       1 | 0600s
 4 |                     1 |       1 | 2000s

db<>fiddle here


SQL Server: Create New GUID Values for Existing Index I have an existing table with about 6 million rows. The relevant columns are:

ID int not null PK
Key uniqueidentifier not null

Read/write on this table by Key lookup is probably something like 100 to 1.

The existing rows were all created using newid() so they are not in sequence. An index exists for Key.

CREATE NONCLUSTERED INDEX [idx_Robert] ON [dbo].[Aleksander]
(
    [Key] ASC
) WITH (PAD_INDEX = OFF
    , STATISTICS_NORECOMPUTE = OFF
    , SORT_IN_TEMPDB = OFF
    , DROP_EXISTING = OFF
    , ONLINE = OFF
    , ALLOW_ROW_LOCKS = ON
    , ALLOW_PAGE_LOCKS = ON
    , FILLFACTOR = 80
    ) ON [PRIMARY]

Given that there are already millions of rows in the table, is there any benefit to changing the GUID generation to sequential using newsequentialid()?

Going forward it would generate better data, but since it's unknown where within the index the new sequential GUID series would land, would it just screw up the index faster? Would it be better to leave the GUID generation non-sequential so that the remaining page space in the index fills more evenly?

As far as the growth rate of the table, it represents about 3 years of data.

Year    NumRows
2012    3962660
2013    1661189
2014    711241

Where 2014 is halfway through the year as of this question. There were some code changes at some point that reduced the rate of inserts, so I believe 2013 would be a typical annual row count.

You may be able to reduce index rebuild/reorg times by moving to NEWSEQUENTIALID().

You are currently inserting around 4,500 rows into the table per day.  The table has around 6.3 million rows at present.  That's around 0.7% of the table per day.  Assuming every insert on the table results in an index-page split, this will result in 9,000 pages per day written to the storage system.  This will create some unnecessary I/O pressure both for inserts into the index, and for rebuild/reorgs.  Switching the Key field to sequential ID generation will vastly reduce this I/O pattern.

If you switch to NEWSEQUENTIALID(), the code that performs INSERTs on the table will need to be sensitive the possibility that it will attempt to insert a duplicate Key.  This is a remote possibility, however it is still a possibility, and is the same for both NEWID() and NEWSEQUENTIALID() - since nothing is truly random that is generated by a computer.  Queries against the index could gain some benefit by making the index unique, as in:

CREATE UNIQUE NONCLUSTERED INDEX [idx_Robert] ON [dbo].[Aleksander]
(
    [Key] ASC
) WITH (
    PAD_INDEX = OFF
    , STATISTICS_NORECOMPUTE = OFF
    , SORT_IN_TEMPDB = OFF
    , DROP_EXISTING = OFF
    , ONLINE = OFF
    , ALLOW_ROW_LOCKS = ON
    , ALLOW_PAGE_LOCKS = ON
    , FILLFACTOR = 80
    ) ON [PRIMARY];

This change would make index scans less likely.  Paul White has an excellent article about this at https://sqlkiwi.blogspot.com/2011/02/seeking-without-indexes.html

Since you haven't given any indication of the type of queries you run against this table, I will assume you are at some points scanning the entire index nothing about your situation.  If your data is truly stored on a single spinning disk, then having your index non-fragmented should reduce the time needed for queries to return information.

Having said that, you could likely get a whole lot more for your money (time is money) by improving the IO subsystem so index rebuild/reorgs are unnecessary.  See http://www.brentozar.com/archive/2012/08/sql-server-index-fragmentation/ .

If you are interested in measuring the effects of the index settings you are using for the Key column, you could monitor page free percentage (among other things) for pages used by the index by using the system dmv, sys.dm_exec_index_physical_stats.  For example:

SELECT o.name, ps.avg_page_space_used_in_percent, ps.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID('KeyTable','Table'), NULL, NULL, 'DETAILED') ps
    INNER JOIN sys.objects o ON ps.object_id = o.object_id;

(You may want to adjust the parameters passed to only show the index on the Key column.)

Once the avg_page_space_used_in_percent crosses some boundary value such as 95% (or more), you would then rebuild the index, which both defragments it, and with an appropriate FILL_FACTOR setting, leaves some space free on each page for new rows with randomly generated NEWID() values.

If you did decide to use NEWSEQUENTIALID() your index could use a FILL_FACTOR of 100% since index pages would not longer be split on a frequent basis.  This has the benefit of reducing the number of pages required to hold the index by 20% (against your current settings), thereby reducing the IO required to rebuild the index when that does become necessary.

Since you are only inserting new rows at a rate of about 0.07% of the total table per day, it may take a very long time before the system automatically triggers a histogram update on the statistics for the table involved.  You could use the STATS_DATE() function to ensure the statistics are never more than x days old.  Up-to-date statistics are vital for the query engine to produce optimal query plans.


Writing a stored procedure for deleting a table rows I face a problem while writing a stored procedure. I want to write a stored procedure so that it gets 2 input parameters (Date and TableName) and then delete table rows which Date column of the table is smaller than the given input date.

The table has 3 columns:

Date   Name    Id

Thank you

Create a stored procedure that builds a dynamic SQL statement based on your input parameters and then executes the command

First, let's create a test table with some data to play with.

 --Create a test demo table and insert a couple of rows
IF OBJECT_ID('dbo.TestDelete', 'U') IS NOT NULL
  DROP TABLE dbo.TestDelete;
 go
create table TestDelete (ID int, Name varchar(100), DateOnTable Date)
insert into TestDelete(ID, Name, DateOnTable) values (1,'NameOnTable','2017-01-01')
insert into TestDelete(ID, Name, DateOnTable) values (1,'NameOnTable','2017-03-01')
go

Now, let's create the 'delete' stored procedure.  My example takes in 3 parameters.  DeleteDate, SchemaName and TableName.

--Drop procedure if is already exists
IF EXISTS (
        SELECT *
        FROM dbo.sysobjects
        WHERE id = object_id(N'[dbo].[DeleteTableRowsLessThanSpecificDate]')
            AND OBJECTPROPERTY(id, N'IsProcedure') = 1
        )
    DROP PROCEDURE [dbo].[DeleteTableRowsLessThanSpecificDate]
GO

--Create the procedure
create PROCEDURE [dbo].[DeleteTableRowsLessThanSpecificDate] @DeleteDate DATE
    ,@SchemaName SYSNAME
    ,@TableName SYSNAME
AS
BEGIN
    SET NOCOUNT ON
    SET XACT_ABORT ON

    --Example error checking to prevent deleting rows with a date within the last month
    IF @DeleteDate >= Dateadd(MONTH, - 1, sysdatetime())
    BEGIN
        RAISERROR ('Delete Date Too Recent - Delete cancelled',16,1);

        RETURN
    END

    --Declare a variable to hold the dynamic SQL command
    DECLARE @Command NVARCHAR(4000)
    DECLARE @ParmDefinition NVARCHAR(4000) = '@SchemaName sysname, @TableName sysname, @DeleteDate date'

    --Build the command by concatenating the input TableName and input DeleteDate
    SET @Command = 'DELETE FROM ' + quotename(@SchemaName) + '.' + quotename(@TableName) + ' WHERE DateOnTable < ''' + convert(VARCHAR(20), @DeleteDate) + ''''

    --Printing just to verify the syntax
    PRINT @command

    --Dynamically execute the command you just built
    --(Using EXEC() instead of sp_executesql)
    --Check this blog post about why you should use sp_executesql instead of EXEC()
    --https://sqlblog.org/2011/09/17/bad-habits-to-kick-using-exec-instead-of-sp_executesql
    EXECUTE sp_executesql @Command
        ,@ParmDefinition
        ,@SchemaName = @SchemaName
        ,@TableName = @TableName
        ,@DeleteDate = @DeleteDate;
END
go

Now, let's test the stored procedure by passing in some different DeleteDates and checking the results.

--Now, we're ready to test the stored procedure by deleting all rows
--less than 2017-04-08.  That should leave only one row in our example (2017-06-01)

--Check rows before calling the stored procedure
select * from TestDelete

--Call the stored procedure
EXEC dbo.DeleteTableRowsLessThanSpecificDate @DeleteDate = '2017-02-01'
    ,@SchemaName = 'dbo'
    ,@TableName = 'TestDelete'

--Check rows after calling the stored procedure
select * from TestDelete

I also added some error checking logic in the stored procedure to show how you might prevent rogue deletes that are too aggressive due to the date being passed in.  My example stored procedure throws an error if you try to delete any rows within the past month. (I wrote this answer on 4/9/2017 and on that date, the following throws an error.

--Testing error logic by trying to delete rows within the last month
EXEC dbo.DeleteTableRowsLessThanSpecificDate @DeleteDate = '2017-04-01'
    ,@SchemaName = 'dbo'
    ,@TableName = 'TestDelete'

  Msg 50000, Level 16, State 1, Procedure
  DeleteTableRowsLessThanSpecificDate, Line 14 [Batch Start Line 72]
  Delete Date Too Recent - Delete cancelled


ORA-01034: ORACLE not available I tried to start the listener and do a dbstart, but it looks like it is still saying "Oracle Not available". Maybe the dbstart didn't work. Any suggestions would help me.

[user@fedora16 bin]$ echo $ORACLE_HOME
/home/user/app/user/product/11.2.0/dbhome_1
[user@fedora16 bin]$ echo $ORACLE_SID
orcl
[user@fedora16 bin]$ cat $ORACLE_HOME/network/admin/listener.ora
# listener.ora Network Configuration File: /home/user/app/user/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

orcl =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = /home/user/app/user/product/11.2.0

[user@fedora16 bin]$ cat $ORACLE_HOME/network/admin/sqlnet.ora
# sqlnet.ora Network Configuration File: /home/user/app/user/product/11.2.0/dbhome_1/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

ADR_BASE = /home/user/app/user/product/11.2.0

[user@fedora16 bin]$ dbstart $ORACLE_HOME
[user@fedora16 ~]$ lsnrctl start orcl

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 04-MAR-2012 09:04:19

Copyright (c) 1991, 2009, Oracle.  All rights reserved.

Starting /home/user/app/user/product/11.2.0/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.1.0 - Production
System parameter file is /home/user/app/user/product/11.2.0/dbhome_1/network/admin/listener.ora
Log messages written to /home/user/app/user/product/11.2.0/diag/tnslsnr/fedora16/orcl/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias                     orcl
Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date                04-MAR-2012 09:04:19
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /home/user/app/user/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File         /home/user/app/user/product/11.2.0/diag/tnslsnr/fedora16/orcl/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
The listener supports no services
The command completed successfully

[user@fedora16 bin]$ ps -ef | grep  '\(ora\|smon\|lsnr\)'
user  2405     1  0 08:46 ?        00:00:00 /home/user/app/user/product/11.2.0/dbhome_1/bin/tnslsnr orcl -inherit
user  2553  1611  0 08:49 pts/0    00:00:00 grep --color=auto \(ora\|smon\|lsnr\)

[user@fedora16 bin]$ sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Sun Mar 4 08:46:29 2012

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Enter user-name: scott
Enter password:
ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux-x86_64 Error: 2: No such file or directory
Process ID: 0
Session ID: 0 Serial number: 0

dbstart will only start databases that have the third parameter in the oratab file set to Y. Check the value in that file.

You also don't need to pass the ORACLE_HOME as an argument; not sure if you actually meant to pass the SID, but anyway, the home is taken from the oratab too.

The script itself says that it should only be executed as part of the system boot procedure, e.g. from a script run during boot from an /etc/rc*.d directory. The $ORACLE_HOME parameter that's passed in is only used to start the listener; the home for each database is taken from oratab.

According to the documentation, dbstart is deprecated in favour of Oracle Restart (in non-RAC environments) in 11g Release 2; I guess that means you should use crsctl start has to start the listener and database from then. That also uses the third oratab value to decide whether to start the database.


Getting an error "ORA-00093: pga_aggregate_target must be between 10M and 4096G-1" on Oracle DB startup My Oracle DB 12c instance was not starting up. While connected as an idle instance ( / as sysdba ) and executing startup; command, I was getting an error:

  ORA-00093: pga_aggregate_target must be between 10M and 4096G-1

Found an answer at : http://www.databaseskill.com/400704/

But the explanation is not very clear. To summarize :

This property 'pga_aggregate_target' is in a file called an spfile which is present at 'C:\app\dbhome\product\12.1.0\dbhome_1\database' for example.
I edited this value to one fitting the range which in my case I changed to 855638016
Further you require a init file at the same path. If one is not present at the above path you can retrieve a file from 'C:\app\dbhome\admin\orcl\pfile' and rename it to initorcl.ora
After doing the above steps connect to an idle instance by running sqlplus / as sysdba and try the startup; command

Answered my own question for future reference


How to UPDATE just one record in DB2? In DB2, I need to do a SELECT FROM UPDATE, to put an update + select in a single transaction.
But I need to make sure to update only one record per transaction.

Familiar with the LIMIT clause from MySQL's UPDATE option

  places a limit on the number of rows that can be updated

I looked for something similar in DB2's UPDATE reference but without success.

How can something similar be achieved in DB2?

Actually, on the link you have, it mentions that you can update only one record, you just have to use cursors to do so. It then points you to the DECLARE CURSOR page.

So first, you would have to declare a cursor from your table with the select statement that would give you what you want.

DECLARE C1 CURSOR
FOR (SELECT * FROM MYTABLE WHERE...);

Move to the value of the record in the cursor that you want.

Then run you update statement against the cursor using the link you mention

UPDATE MYTABLE
SET MYCOLUMN = ?
WHERE CURRENT OF C1;

I believe you need to issue a COMMIT (which closes the cursor unless you specify otherwise in the DECLARE CURSOR clause).

Check out the documentation in the link you have and in the one I have. You may end up having to use a stored procedure to run this.


Configured MySQL replication but its not working I have configured MySQL master and slave replication setup on 5.5.x.
But for some reason data is not getting replicated properly. What might be the reason?

At Master:

/etc/my.cnf

[mysqld]
log-bin=/var/lib/mysql/mysql-bin.log
server-id=1
innodb_flush_log_at_trx_commit=1
sync_binlog=1
binlog-do-db=cmdb
binlog-do-db=nagios

mysql> show master status;

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000005 |   168019 | cmdb,nagios  |                  |
+------------------+----------+--------------+------------------+

At Slave:

/etc/my.cnf

[mysqld]
server-id=2
log-bin=/var/lib/mysql/mysql-bin.log
innodb_flush_log_at_trx_commit=1
sync_binlog=1
report-host=10.10.0.22
replicate-do-db=cmdb
replicate-do-db=nagios

mysql>  show slave status\G;

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.10.0.21
                  Master_User: replicator
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000005
          Read_Master_Log_Pos: 198123
               Relay_Log_File: CentOS62-relay-bin.000012
                Relay_Log_Pos: 198269
        Relay_Master_Log_File: mysql-bin.000005
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: cmdb,nagios
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 198123
              Relay_Log_Space: 198574
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
1 row in set (0.00 sec)

What concerns me here is the use of both replicate-do-db on the Slave and binlog-do-db on the Master at the same time.

There is no need to use both. I would remove binlog-do-db from the Master. I would also look over any queries with fully qualified table names. Why ?

If you replicate any SQL use fully qualified table names (dbname.tblname) and you use replicate-do-db, MySQL Replication gets confused if you execute USE dbname.

Percona explained this in conjunction with binlog-do-db and replicate-do-db back in May 2009.

Percona also suggested reading the replication filtering rules thoroughly.


Multiple Instances and Availability Groups In my scenario, I have two servers in each of them two SQL Server instances:

Server 1: Instance 1 and Instance 2
Server 2: Instance 1 and Instance 2

I created availability groups between the instances like this:

Instance 1 from Server 1 with Instance 1 from Server 2: Availability Group 1
Instance 2 from Server 1 with Instance 2 from Server 2: Availability Group 2

And I have create a two listeners:

Listener 1, for Availability Group 1
Listener 2, for Availability Group 2

The problem is when I try to connect with the Listener 1, it connects me to the Availability Group 1, but when I try with Listener 2 it also connects me to Availability Group 1, not to Availability Group 2.

I don't understand what I'm doing wrong.

This can happen due to the TCP ports configuration of your instances and listeners

  If one of the instances of SQL Server is already listening on TCP port
  1433 via the instance listener and there are no other services
  (including additional instances of SQL Server) on the computer
  listening on port 1433, this will not cause a port conflict with the
  availability group listener. This is because the availability group
  listener can share the same TCP port inside the same service process.
  However multiple instances of SQL Server (side-by-side) should not be
  configured to listen on the same port.

Source

In short, you can have both your listeners listening on port 1433, as long as you change the tcp ports for the instances to different ports like 1533, 1633.

You could also use different port numbers than 1433 but then you need to specify these in the connection string(s).

In the case of "one instance, one listener" you could be using the same port for both.


MySQL returning only 1 row, multiple entries in the database I am creating a list of all publications for employees, and I am currently stuck on a query.  It runs with no syntactical errors, but it only returns one row.  The ERD for the relevant tables is as follows:

There is a many-to-many relationship between publications and employees, publications publication tags, and publications and publication types.  Each publication must have at least one tag and one type.

The Query

SELECT publicationsId, title, summary, image, publicationDate, citation, dateAdded, GROUP_CONCAT(DISTINCT tagName) AS tagName, GROUP_CONCAT(DISTINCT publicationType) AS publicationType, employee_employeeId
FROM publications
LEFT JOIN publications_have_publicationTags ON publications_have_publicationTags.publications_publicationsId = publications.publicationsId
LEFT JOIN publicationTags ON publications_have_publicationTags.publicationTags_publicationTagId = publicationTags.publicationTagId
LEFT JOIN publications_have_publicationTypes ON publications.publicationsId = publications_have_publicationTypes.publications_publicationsId
LEFT JOIN publicationTypes ON publications_have_publicationTypes.publicationTypes_publicationTypeId = publicationTypes.publicationTypeId
LEFT JOIN employee_has_publications ON publications.publicationsId = employee_has_publications.publications_publicationsId
ORDER BY dateAdded DESC

Extra Info

I was originally using INNER JOIN with the same effect.  I'm using PDO on the website, but I get the same result when running the query in phpmyadmin.

How can I get the publications information, and their related tags and types in one query?  Or should I just run separate queries to eliminate the complexity?

Your select statement includes group_concat().  This is an aggregation function.  Because there is no group by, this means that all the rows are treated as one group, and hence one row is returned.

Perhaps you mean:

SELECT publicationsId, title, summary, image, publicationDate, citation, dateAdded,
       GROUP_CONCAT(DISTINCT tagName) AS tagName,
       GROUP_CONCAT(DISTINCT publicationType) AS publicationType,
       employee_employeeId
FROM publications
LEFT JOIN publications_have_publicationTags ON publications_have_publicationTags.publications_publicationsId = publications.publicationsId
LEFT JOIN publicationTags ON publications_have_publicationTags.publicationTags_publicationTagId = publicationTags.publicationTagId
LEFT JOIN publications_have_publicationTypes ON publications.publicationsId = publications_have_publicationTypes.publications_publicationsId
LEFT JOIN publicationTypes ON publications_have_publicationTypes.publicationTypes_publicationTypeId = publicationTypes.publicationTypeId
LEFT JOIN employee_has_publications ON publications.publicationsId = employee_has_publications.publications_publicationsId
GROUP BY publicationsId, title, summary, image, publicationDate, citation, dateAdded, emplyee_employeeid
ORDER BY dateAdded DESC;


Same query sometimes uses a Table Spool I'm checking out a query which exhibits strangely inconsistent performance.

Using SolarWinds DPA, I can see that the query sometimes uses a Table Spool, sometimes it doesn't.

When it actually used a Table Spool, the query's performance seemed to tank (wait time increased significantly compared to when Table Spool is not used).

See below figure:

Plan #4681967514 has a Table Spool step
Plan #4038619871 does NOT have a Table Spool step

Can you provide a guide, a hint on why the exact same query produced wildly different execution plans like this case?

Note: I've pastebinned the query in Chop: http://chopapp.com/#mpc57cq7

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

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

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


Split Date and Time in sql query I have a table that looks like this:

Table:

CUSTTRIPID  TPTRIPID    CUSTOMERID  ENTRY_TOLLTXNID EXIT_TOLLTXNID  ENTRYTRIPDATETIME   EXITTRIPDATETIME    TRIPIDENTMETHOD TRIPCHARGETYPE  ENTRYLANEID ENTRYPLAZAID    EXITLANEID  EXITPLAZAID VEHICLENUMBER   VEHICLESTATE    VEHICLECLASS    VEHICLEID   TAGREFID    TOLLAMOUNT  FEEAMOUNTS  DISCOUNTSAMOUNT OUTSTANDINGAMOUNT   TRIPSTAGEID TRIPSTATUSID    TRIPSTATUSDATE  POSTEDDATE  PAYMENTSTATUSID CREATEDDATE CREATEDUSER UPDATEDDATE UPDATEDUSER REFLINKTRIPID   PLAZA
39038181    34040337    20272679    35126165    35126165    2017-05-04 08:15:54.000 2017-05-04 08:15:54.000 980 P   NULL    NULL    NULL    NULL    KA03D8435   KA  VC10    272614  34161FA8203289720249F4C0    30.00   0.00    0.00    30.00   3   1   2017-05-04 08:31:54.657 2017-05-04 08:31:54.657 456 2017-05-04 08:32:11.130 TRIPPOST    2017-05-04 08:32:11.130 TRIPPOST    0   BangaloreNelamangalaTollPlaza

I want to retrieve CUSTOMERID more than 2 records with time so I have written this query and got the result.

Main Query:

SELECT   TOP (10) [CUSTOMERID] , [ENTRYTRIPDATETIME]
FROM     [ISSUER].[TOLLPLUS].[TP_CUSTOMERTRIPS]
GROUP BY [CUSTOMERID],[ENTRYTRIPDATETIME]
HAVING   COUNT(*) > 2

Result:

CUSTOMERID  ENTRYTRIPDATETIME
20063381    2016-12-08 00:24:35.000
20010438    2016-12-16 22:16:55.000
20015278    2017-01-28 05:18:20.000
20108092    2017-02-10 00:37:09.000
20013275    2017-01-06 07:42:05.000
20018833    2017-01-27 04:53:32.000
20113994    2017-01-27 01:13:44.000
20199820    2017-01-27 05:34:24.000
20124122    2017-02-01 02:47:01.000
20240710    2017-03-21 13:02:18.000

But my requirement is to split the Date and Time in the abobe result, I mean there should be 3 Columns, so my question is where I will be able to write below query in my Main Query?

CONVERT(VARCHAR(10),[ENTRYTRIPDATETIME],101) as DatePart,
CONVERT(VARCHAR(10),[ENTRYTRIPDATETIME],108) as TimePart


Why is my database still fragmented after I reorganized and rebuilt it, and updated the statistics in SQL Server? I created a Maintenace Plan, first i reorganize the indexes, then rebuild, and final update the statistics every Saturday.
I ran this script for verification:
SELECT
    S.name as 'Schema',
    T.name as 'Table',
    I.name as 'Index',
    DDIPS.avg_fragmentation_in_percent,
    DDIPS.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS DDIPS
    INNER JOIN sys.tables T on T.object_id = DDIPS.object_id
    INNER JOIN sys.schemas S on T.schema_id = S.schema_id
    INNER JOIN sys.indexes I ON I.object_id = DDIPS.object_id
        AND DDIPS.index_id = I.index_id
WHERE DDIPS.database_id = DB_ID()
    AND I.name is not null
    AND DDIPS.avg_fragmentation_in_percent > 0
ORDER BY DDIPS.avg_fragmentation_in_percent DESC

But after the run, the fragmentation is still very high. Is that okay? What could be done?
Thank you for your help! :)

Bottom line: fragmentation is irrelevant for small indexes. Never bother about it for indexes less than some 1000 pages, or perhaps we should say 10000 nowadays. If you remember cassette tapes, we always have some noise in the background (my analogy, perhaps works better in Swedish).
Tip1: don't use single quotes for column names in SELECT list. It divert from ANSI SQL and it is "weird". :-)
Tip2: you don't have to join all those tables. Use meta-data functions instead.
Tip3: Heaps don't have fragmentation in the same way as indexes do. So either filter out the heaps or learn about forwarding pointers/forwarded records and handle them separately from your indexes. This might be useful, for instance: https://karaszi.com/rebuild-all-fragmented-heaps.
Tip4: Jumping back and forth on disk isn't as costly as it used to be. Assuming you are on faster storage than old-school spinning disks, that is. I.e., that type of fragmentation might not hurt you much. There's still the aspect of read-ahead, though. See for instance this: http://sqlblog.karaszi.com/fragmentation-the-final-installment/.
Tip5: Use something more reasonable to filter on the fragmentation level, like > 5% instead of > 0%.
Here's a query, simplified and with my recommendations above. But, again, perhaps you shouldn't worry about jumping back and forth in the first place?
SELECT
 OBJECT_SCHEMA_NAME(f.object_id) AS Schema_
,OBJECT_NAME(f.object_id) AS Table_
,i.name AS Index_
,f.avg_fragmentation_in_percent
,f.page_count
,i.type_desc
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS f
  INNER JOIN sys.indexes AS i On f.object_id = i.object_id
WHERE f.avg_fragmentation_in_percent > 5
 AND f.page_count > 1000
 AND i.type_desc NOT IN('HEAP', 'CLUSTERED COLUMNSTORE', 'NONCLUSTERED COLUMNSTORE')
ORDER BY avg_fragmentation_in_percent DESC


Find the functional dependencies that hold in a relation There is a question in my book that asks the following:

Suppose we have relation R ( A ,B ,C ,D ,E ) , with some set of FD’s, and we wish to project those FD’s onto relation S(A, B, C). Give the
    FD’s that hold in S if the FD’s for R are:

A->D
BD->E
AC->E
DE->B

In each case, it is sufficient to give a minimal basis for the full set of FD’s of S.

So I attempted to compute the closure of attributes for all of the subsets of (A,B,C).  I could not find any minimal basis of FD's for S.  I computer the closure of A, B, C, and AC, but I could not get all of the attributes.  A only implies AD, B only implies B, C only implies C and AC only implies ACE.  I could not find a functional dependency where there is a attribute on the left hand side that implies all of the attributes on the right hand side.

For convenience, define the (implied) numbering of your question:

A->D
BD->E
AC->E
DE->B

Then substituting 1 and 3 into 4 gives
(A)(AC) -> B
which reduces to just
 5. AC -> B.


How to migrate from MySql to HSQLDB? I have large amount of data in a MySQL database.

There are many databases schemas in the database.

How I can migrate from MySQL to HSQLDB?

Is there some tool for this?

I've exported the data manually.  How can I export only schema.sql?

Take a look at Comparison of database tools, there you find some tools supporting both HSQL
 and MySQL. Then it should be possible to export the MySQL DB and directly import to HSQL DB from the same GUI.


How can I create rows without an underlying table in IBM DB2 SQL? In T-SQL, I can execute the following:

SELECT      *
FROM        (SELECT 1 AS n UNION ALL SELECT 2 AS n) AS t;

In IBM DB2 SQL, this fails with:

  [IBM][System i Access ODBC Driver][DB2 for i5/OS]SQL0199 - Keyword
  UNION not expected. Valid tokens: , FROM INTO.

A SELECT statement seems to require a FROM clause. Indeed, a reductive SQL statement such as:

SELECT 1 AS n

Also errors with:

  [IBM][System i Access ODBC Driver][DB2 for i5/OS]SQL0104 - Token
   was not valid. Valid tokens: , FROM INTO.

There is an UNNEST function which can create rows from an array, but it seems the array must already be represented by something like a variable or parameter.

I understand I could work around this by doing a SELECT from an arbitrary table with an impossible criterion such as 1 = 0, but that seems to be a silly requirement.

So how can I construct rows without an underlying table?

(keywords: tally table; derived table)

Platform and version are important when asking about Db2...

In this case it appears you're running Db2 for IBM i (iSeries, AS/400)

What you are looking for is known in the SQL standards as table value constructor which looks like VALUES (<row1>),(<row2>),(...)

On Db2 for i (and LUW I believe) the following are a couple of ways...

-- Using a Common Table Expression (CTE)
WITH X(foo, bar, baz) AS (
VALUES (0, 1, 2), (3, 4, 5), (6, 7, 8)
) SELECT * FROM X;

-- Using a Nested Table Expression (NTE)
SELECT * FROM TABLE (
VALUES (0, 1, 2), (3, 4, 5), (6, 7, 8)
) X(foo, bar, baz);

However, you appear to be using an older version of Db2 for i (v5?)  I don't recall if that version supports table value constructors...


Oracle DB refresh on 11g using 10g Cold backup I am a sql dba, I am having only a little knowledge on Oracle. looking for help regarding oracle db restore.
I am having an Oracle 10g cold backup, i need to restore this on Oracle 11g setup, can you please help me understanding the process how to perform.

Datapump command like impdp can be used for importing the 10g DB contents into the 11g DB.

Update:

The above datapump command is an example of logical backup which can be done when a database is in running mode.
Below are the common steps followed during a logical backup and restore:

Export(Backup) of full DB is taken in old server:

expdp system/password@db10g full=Y directory=DIRECTORY_ON_SERVER dumpfile=DUMPFILE_NAME.dmp logfile=LOGFILE_NAME.log
Dumpfile moved to new server
Import(Restore) dumpfile in the new server:

impdp system/password@db11g full=Y directory=DIRECTORY_ON_SERVER dumpfile=DUMPFILE_NAME.dmp logfile=LOGFILE_NAME.log

  More details on this can be found in -->
  https://oracle-base.com/articles/10g/oracle-data-pump-10g (Better than
  Oracle docs)

Now coming back to the actual question, what you are asking is a restore of the cold backup in 11g DB server.

The restore of the cold backup can be done by using RMAN command(s) as below:

RMAN> restore database from TAG='COLD_BACKUP_FILE_NAME'

I assume that you have taken backup of all the necessary files during the cold backup process.

Hope this helps you !!
Please share your progress with regard to this :)


Excessive amount of data received from server We are having a problem with slow queries on a certain table. We tried some things with indexes but that didn't make any difference. Then we discovered that the amount of data being transferred was excessively high.

The table definition is as follows:

/****** Object:  Table [dbo].[survey_tbMultiLanguageText]    Script Date: 23-6-2015 17:45:53 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[survey_tbMultiLanguageText](
    [fMultiLanguageTextId] [int] IDENTITY(1,1) NOT NULL,
    [fProjectID] [int] NOT NULL,
    [fElementTypeID] [int] NOT NULL,
    [fElementCode] [varchar](50) NOT NULL,
    [fPropertyID] [int] NOT NULL,
    [fLanguageID] [int] NOT NULL,
    [fText] [nvarchar](max) NOT NULL,
 CONSTRAINT [PK_survey_tbMultiLanguageText] PRIMARY KEY CLUSTERED
(
    [fMultiLanguageTextId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[survey_tbMultiLanguageText]  WITH CHECK ADD  CONSTRAINT [FK_survey_tbMultiLanguageText_survey_tbLanguage] FOREIGN KEY([fLanguageID])
REFERENCES [dbo].[survey_tbLanguage] ([fLanguageID])
GO

ALTER TABLE [dbo].[survey_tbMultiLanguageText] CHECK CONSTRAINT [FK_survey_tbMultiLanguageText_survey_tbLanguage]
GO

ALTER TABLE [dbo].[survey_tbMultiLanguageText]  WITH CHECK ADD  CONSTRAINT [FK_survey_tbMultiLanguageText_survey_tbProject] FOREIGN KEY([fProjectID])
REFERENCES [dbo].[survey_tbProject] ([fProjectID])
GO

ALTER TABLE [dbo].[survey_tbMultiLanguageText] CHECK CONSTRAINT [FK_survey_tbMultiLanguageText_survey_tbProject]
GO

If I query this table like this:

select top 100 *
from survey_tbMultiLanguageText

This results in bytes received of 1.284202E+07 (12.25 MB)

Within these 100 records the max length of fText is 380, and if I convert to nvarchar(380) the number of bytes received is much lower (79339).

The database server I'm testing this on is (select @@VERSION):

  Microsoft SQL Server 2014 - 12.0.2000.8 (X64)     Feb 20 2014 20:04:26
    Copyright (c) Microsoft Corporation     Standard Edition (64-bit) on
  Windows NT 6.3  (Build 9600: ) (Hypervisor)

Oddly enough, when querying for a specific project/language, it gives different results on different servers when querying the same data (normal on staging vs excessively high on live). Yet the top 100 query also gives the same problem on staging.

Use of TOP without an ORDER BY means you cannot guarantee which rows are returned.  The result may be different across servers, or even on the same server at different times of day.


Max Memory settings on Multi-Instance SQL Server 2008 R2 Cluster SQL Server 2008 R2 multi-instance cluster (on VMware vSphere 5.1)
2 nodes, each running 2 instances (4 instances in total)
16GB RAM per node.

The only time the instances are on the same node is when I am patching the other node.

Brent Ozar's "maximum server memory" recommendation is to leave Windows 4GB or 10%, whichever is more.

Since this a cluster, how should I set the max memory on each node? Should I treat each as a standalone server? This would make sure memory on each node is not wasted. However, in a node failure, the max memory total for all 4 instances would exceed system memory of the single node. Will this cause any issues in the timeframe until we get the second node recovered? Do I need to lower the max memory setting on the 4 instances until the secondary node is recovered? Or is SQL Server smart enough to keep working (using page file if necessary).

You should absolutely make the most use of the hardware when you are in an optimal config, and adjust when you are in maintenance mode. And yes, you will have an issue while both (or all four?) instances are active on the same node. Since a failover induces a service start on the now-active node, you can adjust the max memory of each server in that event using a startup procedure. I blogged about this here, but for a different reason (failing over to a node with a different amount of memory):

https://sqlblog.org/2009/09/18/managing-multi-instance-cluster-failovers-with-different-hardware

Basically, you just need to check if both instances are on the same node (and this will require a linked server to be set up in both directions), and adjust accordingly. A very quick and completely untested example based on my blog post and assuming there is only one instance on each node at a time presently (the question is a bit ambiguous if you have 2 total instances or 4):

CREATE PROCEDURE dbo.OptimizeInstanceMemory
AS
BEGIN
   SET NOCOUNT ON;

   DECLARE
     @thisNode      NVARCHAR(255) = CONVERT(NVARCHAR(255),
                                  SERVERPROPERTY('ComputerNamePhysicalNetBIOS'),
     @otherNode     NVARCHAR(255),
     @optimalMemory INT = 12288, -- 12 GB
     @sql           NVARCHAR(MAX);

  SET @sql = N'SELECT @OtherNode = CONVERT(NVARCHAR(255),
                        SERVERPROPERTY(N''ComputerNamePhysicalNetBIOS''));';

  EXEC [SERVER\INSTANCE].master..sp_executesql @sql,
    N'@OtherNode NVARCHAR(255) OUTPUT', @OtherNode OUTPUT;

  IF @thisNode = @otherNode
  BEGIN -- we're on the same node, let's make everyone happy
    SET @optimalMemory = 6144;
  END

  SET @sql = N'EXEC sp_configure N''max server memory'', @om;
    RECONFIGURE WITH OVERRIDE;';

  EXEC                   master..sp_executesql @sql, N'@om INT', @optimalMemory;
  EXEC [SERVER\INSTANCE].master..sp_executesql @sql, N'@om INT', @optimalMemory;
END
GO

EXEC [master].dbo.sp_procoption
  N'dbo.OptimizeInstanceMemory', 'startup', 'true';

Of course create it again on the other instance, swapping the linked server name used.

This gets a little more complex if you have to adjust depending on whether you are sharing the current node with 1, 2 or 3 other instances.

Note that this will cause other side effects such as clearing the plan cache (in the event when one of the instances didn't just restart or fail over, in which case the plan cache would be empty anyway), but these are arguably better than leaving both instances to assume they still have 12 GB of memory to play with - there will be a lot of thrashing if they're both heavily used.

You may also want to consider other options such as global maxdop, NUMA/CPU affinity etc. depending on how sensitive the system is to the amount of resources available.


Accessing MySQL Database I am designing a database for an application, which will be accessed by different front end programming languages, such as php, asp.net, android applications and iphone applications.

I am confused, whether is it good to accessing different programming languages directly to a database? and I learned somewhere, I have to create a middle layer between database (Backend) and Programming languages (Frontend).

Is this middle layer indicating Stored Procedure?
If it is not stored procedure what is required to create this middle layer?
If my overall logic is wrong kindly suggest me any other method to solve this doubt.
Is there anything related to middle layer which I need to concentrate while designing?

Thanks in advance.

If you were only talking about accessing the database via PHP and ASP.net, then a stored procedure could serve as a common middle layer. Once you add the android and iphone applications into the equation, you're looking at having to design another middle layer. Reason for that it is most mobile devices don't have database drivers available to them.

In your case, you should build your middle layer in asp.net or PHP as a web service. Most are designing these web services as a REST API. You'll need to think about the information that your mobile apps need and build API endpoints for those things (users, posts, etc). I also recommend making your ASP.net and PHP site/application use the same API your mobile apps do.

By setting up an API like this, you can reduce the work required for your apps. You're mobile app doesn't know that to get the latest 10 posts, it has to query SELECT * FROM posts order by create_date desc limit 10. It just has to make a request to http://myapi/posts/latest.

There are several posts on Stack Overflow and Programmers.SE about setting up Web Service APIs, I recommend reviewing them.


Help my database isn't performing fast enough! 100M Merge with 6M need < 1 hour! I have a server right now receiving more raw data files in 1 hour then I can upsert (insert -> merge) in an hour.

I have a table with 100M (rounded up) rows. Table is currently MyISAM. The table has 1000 columns mostly boolean and a few varchar.

Currently the fastest way i've found to get the information into my DB until now was:

Process raw data into CSV files.
Load Data In File to rawData Table.
Insert rawData table into Table1. (on dupe key do my function)
Truncate rawData
Repeat.
Worked fine until im merging 6M+ Rows into 100M rows and expecting it to take under an hour.

I got 16G of ram so I set my Key_Buffer_Pool to 6G.
I have my query cache pool to 16M
I have my query cache limit to 10M
I would just replace the information however it has to be an Upsert, Update the fields that are true if exists and insert if it does not.

Things im looking into atm; - Possibly switching server table to InnoDB? |-> Not sure about the performance, as the insert into an empty table is fine, its the merge that's slow.

Maybe allowing more table cache? Or even Query Cache?
mysql sql mysqli innodb myisam

Merge Code:

b.3_InMarket = (b.3_InMarket OR r.3_InMarket),

To compare my 2 bool columns.

Update

Ok I set Raid0
Changed my query to Lock Write on tables when inserting
When importing csv im disabling keys then re-enabling them  before upsert.
Changed concurrent_insert to 2

If your table has a table with more than 1000 columns, it cannot be converted to InnoDB. In that case, run this query

SELECT CEILING(SUM(index_length)/POWER(1024,2)) num
FROM information_schema.tables WHERE engine='MyISAM';

This will give you the correct size for key_buffer_size in MB.

Since you are doing an UPSERT, you should set concurrent_insert to 2 to make INSERTs go faster. You may want to consider changing the table's row format to Fixed. I wrote about why to do both in StackOverflow. In essence, if you make the table's row format Fixed, all table rows are the same size. Thus, INSERTs and UPDATEs would operate on the exact same length of data. Management of row access is far more reasonable.

Since MyISAM only caches indexes (in the key buffer), all data must be read from disk. anything you can do to getting better RAID performance (as asked by @TomTom) would help your cause as well.


Check if two databases have same structure I have a Postgres database that was developed over time, adding tables and keys when needed.
Now I'm using a tool (Enterprise Architect) to document it. The tool allows to create automatically the SQL needed to re-create the database.
I want to know if the database that I create and the original one have the same structure (same tables, same primary keys, same foreign keys etc). I'm not interested on data contained in database, only in structure.
Is there a way to check if two different databases have the same structure?

PostgreSQL has a comprehensive set of system catalogs. These hold the metadata for a database i.e. table names, columns, index definitions and so forth. By comparing the content of the original database's catalog with that of the DB generated from the model you can check completeness. Turns out, there's an app for that.


Relation of PAGELATCH waits with PAGEIOLATCH and WRITELOG I am seeing increase in PAGELATCH, PAGEIOLATCH and WRITELOG waits after migration to SQL Server 2017 (from SQL Server 2008). While I can explain increase in PAGEIOLATCH and WRITELOG (we've other disks - HDD instead of SSD), but don't know why PAGELATCH goes up too.

Are they anyhow related?

What I can see around they are not, cause PAGELATCH belongs to memory, while the others to IO.

While I can explain increase in PAGEIOLATCH and WRITELOG (we've other
  disks - HDD instead of SSD)

I would not come to that conclusion without doing more analysis. Some of the possible cause Paul Randal mentioned in this article.

Here are some possible root causes (not an exhaustive list):

External Windows memory pressure on SQL Server causing the memory
manager to reduce the buffer pool size
Plan cache bloat causing extra memory to be borrowed from the buffer
pool
A query plan doing a table/clustered index scan (instead of an index
seek) because of:

a workload volume increase
a parameter sniffing problem
a required nonclustered index that was dropped or changed
an implicit conversion

Are they anyhow related?

No. Read this article for possible causes and solution.

Knee-Jerk Wait Statistics : PAGELATCH by Paul Randal.

Also read the answer of this question.

High PAGELATCH_* and WRITELOG waits. Are they related?

I suggest you look at your workload and identify what changed between 2008 and 2017.  Try to corollate those changes to the change in your wait-type. The New and Improved Cardinality Estimator was released in SQL Server 2014, which might not produce optimal plan for all your code written for 2008. Keeping in mind hardware change can also play a role.  Once you have all the information you will be able to identify the root cause of changes in wait-type.

The New and Improved Cardinality Estimator in SQL Server 2014


best way to deal with imperfect xls source file that needs to be imported into SQL via SSIS The data is formatted like this:

Date       Type       Account
1/1/14     AAA        111
                      112
                      113

           BBB        114
                      115

1/4/14     AAA        116
                      117

           CCC        118

Each new line represents a new row in the Excel source file. So accts 112 and 113 are date 1/1/14 type AAA, and accts 114 and 115 are date 1/1/14 and type BBB.  Accts 116 and 117 are 1/14/14 and type AAA, etc. There are lots of blank rows and cells.

What I want, is to import this Excel file into SQL and have every account record include the date and type.

I have SQL server 2008 and Office 2010.  I can't edit the Excel source file, but I could save a local copy I suppose if necessary, but I'm guessing there's an easier way in the SSIS import process or in SQL.

Two options come to mind. If you go with the local copy approach you can manually use the AutoFill feature to fill in the blank values. For an automated approach, the one approach I would think of in T-SQL is to use a cursor to loop through all the rows, capture the non-NULL Dates and Types into variables and when you encounter a NULL value, UPDATE the current record with the last non-NULL value. An integer identity may be needed in the table you're importing to confirm the rows have been inserted in the order as found in the file.


Trying to connect two SQL Server databases I have been trying to connect to a SQL Server instance. When I use the IP address, it connects. But when I use the server name, it doesn't. The main reason why I need to use the name is because I want to setup replication.
This is the error I am getting:

Any ideas on what I could be missing?
I am in a Windows environment and I used the machine IP. Firewall is opened for port 1433 TCP.

Looks like name resolution is not working. Do you have a DNS server? If not, you may have to make entries in the hosts files on both computers to enable name resolution. The hosts file on Windows is at c:\windows\system32\drivers\etc\hosts.
Try using FQDN. If the server is in a different domain, and a host record is not created in DNS, this would be required to resolve. You could also try creating an alias in SQL Server Configuration Manager.


Is SELECT * ok in a Trigger. Or am I asking for trouble? I'm caught in a debate at work, and I need some advice on possible Pitfalls I could be overlooking.

Imagine a scenario where a Trigger is used to copy Deleted Records to an Audit Table.
The Trigger uses SELECT *. Everyone points and shouts and tells us how bad this is.

However, if a modification is made to the Structure of the Main Table, and the Audit Table is overlooked then the Trigger will generate an error letting people know the Audit table also needs modification.

The error will be caught during testing on our DEV servers. But we need to ensure Production Matches DEV, so we allow SELECT * in Production Systems (Triggers only).

So my Question is: I'm being pushed to remove the SELECT *, but I'm not sure how else to ensure we are automatically capturing Development Errors of this nature, any ideas or is this best practice?

I've put together an example below:

--Create Test Table
CREATE TABLE dbo.Test(ID INT IDENTITY(1,1), Person VARCHAR(255))
--Create Test Audit Table
CREATE TABLE dbo.TestAudit(AuditID INT IDENTITY(1,1),ID INT, Person VARCHAR(255))

--Create Trigger on Test
CREATE TRIGGER [dbo].[trTestDelete] ON [dbo].[Test] AFTER DELETE
NOT FOR REPLICATION
AS
BEGIN
    SET NOCOUNT ON;
    INSERT  dbo.TestAudit([ID], [Person])
    SELECT  *
    FROM    deleted
END

--Insert Test Data into Test
INSERT INTO dbo.Test VALUES
('Scooby')
,('Fred')
,('Shaggy')

--Perform a delete
DELETE dbo.Test WHERE Person = 'Scooby'

UPDATE (rephrase question):

I'm a DBA and need to ensure Developers don't provide poorly thought-out Deployment scripts by contributing to our Best Practice Documentation.
SELECT * causes an error in DEV when the Developer overlooks the Audit Table (this is a safety net) so the error is caught early in the development process. But somewhere in the SQL Constitution - 2nd amendment it reads "Thou shalt not use SELECT *" . So now there is a push to get rid of the Safety Net.

How would you replace the Safety Net, or should I consider this to be best practice for Triggers?

UPDATE 2: (solution)

Thankyou for all of your input, I'm not sure if I have a clear answer because this appears to be a very Grey subject. But collectively you've provided discussion points that can help our developers move forward with defining their Best Practice.

Thanks Daevin for your contribution, your answer provides the groundwork for some test mechanisms that our Developers can implement. +1

Thanks CM_Dayton, your suggestions contributing to best practice can be beneficial to anyone who is devloping Audit Triggers. +1

Big Thanks to ypercube, you've raised plenty of thought around the issues concerning tables undergoing different forms of definition changes. +1

In conclusion:

Is Select * ok in a tigger? Yes, it's a Grey area, don't blindly follow the "Select * is Bad" ideology".

Am I asking for Trouble? Yes, we do more than just add new columns to tables.

Typically, it is considered "lazy" programming.

Given that you're specifically inserting two values into your TestAudit table here, I'd be careful to make sure your select is also getting exactly two values. Because if, for some reason, that Test table has or ever gets a third column, this trigger will fail.

Not directly related to your question but if you're setting up an audit table,  I'd also add some additional columns to your TestAudit table to...

track the action you are auditing (delete in this case, vs inserts or updates)
date/time column to track when the audit event occurred
user ID column to track who carried out the action you are auditing.

So that results in a query like:

INSERT dbo.TestAudit([ID], [Person], [AuditAction], [ChangedOn], [ChangedBy])
SELECT [ID], [Person],
   'Delete', -- or a 'D' or a numeric lookup to an audit actions table...
   GetDate(), -- or SYSDATETIME() for greater precision
   SYSTEM_USER -- or some other value for WHO made the deletion
FROM deleted

That way you're getting the exact columns you need and you're auditing what/when/why/who the audit event is about.


PostgreSQL Correlation Between "max_connections" and "numbackends" I'm trying to figure out why the field numbackends in pg_stat_database never goes above 16 (and often stays the same even after a restart).

I have max_connections set to 100 and max_locks_per_transaction set to 64. shared_buffers is set to 128MB.

We are in the process of separating the database off the same server as the application, so obviously there's some resource contention going on, but is there any other property or field I should be looking at?

Running PostgreSQL 9.4 on RHEL 6.

I also just cleared the memory cache, and the printout from free -h shows 16G used, 15G free, 139M shared, 39 buffers, and 7.6 cached.

Any help would be greatly appreciated.

max_connections is the upper limit for the number of database connections to all databases in the PostgreSQL cluster.

numbackends is the number of backends connected to a certain database.

The only connection between these numbers is that the former must be greater or equal than the latter.

It seems like you don't have more than 16 connections to the database. This is not a sign to worry; I would take it as an indication that your connection pool is working.

Remark: shared_buffers is very likely set way too low, which will harm performance.


pg_ctl promote is not working I'm working on setting up a two-node PostgreSQL 9.4 environment. Right now I have test instances running on port 5532 on two identical Centos servers. node1 is correctly sending logs to a shared directory, and node2 is a hot_standby, and it's correctly reading and processing those logs.

I want to test failover. Here are the steps I followed:

issue a checkpoint on node1 (run psql as postgres user, type "checkpoint;")
stop the instance on node1 (systemctl stop postgresql-9.4-test.service)
promote the instance on node2 (pg_ctl -D path_to_directory promote)

All of these steps seems to work. But when I try to create a table on node2, I get "ERROR:  cannot execute CREATE TABLE AS in a read-only transaction" , and if I look at the settings for node2, it tells me that hot_standby is on.

The only change that I can see in node2's directory is that there's now a zero-length file called "promote".

What am I missing? I don't see anything in the documentation about any other steps to follow in order to promote a standby server.

Edited to add: select pg_is_in_recovery() returns true.

I didn't create a trigger file because that's not supposed to be required if you're using pg_ctl promote.

I think I have found the answer: this only works with streaming replication.

My original setup used WAL replication. That was working, except for the promotion. I even tried adding a trigger file, and that didn't help.

Then I changed my setup to use asynchronous streaming replication, and pg_ctl promote worked! When I looked in the log on the secondary server, I saw

received promote request
redo done at 86/8A000028
last completed transaction was at log time 2015-09-14 16:15:46.316812-04
selected new timeline ID: 2
archive recovery complete
autovacuum launcher started
database system is ready to accept connections

and when I ran "select pg_is_in_recovery()" on that server, it returned false.

Also, I see that recovery.conf has been renamed to recovery.done .


Saving passport information For starters: Our company is based in Germany and thus is based on German law.

Germany now has adopted a new act regarding anti-money laundering and which information needs to be stored.

Now we're forced to provide our customers (real estate agencies) with the possibility to store passport information alongside a customers record.

I do not want to participate in any security-discussions, since this whole thing is going to be heavily encrypted anyway, so security should be out-of-scope.

I'm more wondering on how to design such an SQL Server table to be ready for international passports and "identity documents". Are there information that ANY country specifies in their passports, some international standards or such?

Which columns would be needed to conform to other countries' anti money-laundering laws?

According to this information on the ICAEW site, the things to be copied are fairly well defined. This is based in the UK.  See:

http://www.icaew.com/en/members/practice-resources/news/money-laundering-what-do-you-do-with-passports

Basically they say that identification: "For the purpose of due diligence evidence under the Money Laundering Regulations you may copy the personal details page only.

HM Stationery Office (HMSO) guidance also makes it clear that passport photocopies must be in black and white only, so that they cannot be mistaken for an actual passport page.

Furthermore, you are only allowed to copy the details page."

The HMSO (Her Majesty's Stationary Office) has similar details.

http://www.hmso.gov.uk/copyright/guidance/gn_20(old%20format).htm

So it seems that if you preserve a black and white copy of the personal details page, you are complying.  Of course, the one who makes the copy (or scan) should verify personally against the original passport that the bearer is indeed the person described.

The verification process may be more stringent depending on where you are.


Is there a list what datatype corresponds to the system_type_id in Sys.columns Is there a list what datatype corresponds to the system_type_id in Sys.columns. I am looking for an easy way to examine the columns we have in the database and i would like to avoid the INFORMATION_SCHEMA.COLUMNS.

Yes you can look in sys.types for such a list or use the type_name function to look up an individual type_id.


Abnormal PREEMPTIVE_OS_AUTHENTICATIONOPS Waits Problems

Seeing this wait increase and very high, which it never shows up as a wait.
Users don't get back to the database when they make requests - it takes a while before they land on the database and when they do, they get an immediate resolution to query/insert/etc.
It really appears there's an issue with the users getting confirmed before querying - this is just a guess based on some reading.  Microsoft provides nothing on this wait that I can find.
The normal volume for users dropped 99% when this wait took off.
Developers seeing "you are not authorized to access microsoft iis" on their end - but we've checked disk space on all servers, which is fine.

What we've done:

Restarted all servers - SQL and web.
CPU, memory, disk all seem fine on SQL Server.
This wait continues to increase.
Error log doesn't show any abnormal behavior.

Overview of issue: people will run a query on the website, we don't see it on the back-end until much later, when I do see it, it runs immediately and they get their result.  There seems to be a delay from the intermediate server to the database server, possibly related to authentication (a guess based on this wait).  It's possible this wait is completely unrelated, but it continues to increase and I've never seen this.

This is an issue external to SQL Server. You should check that your authenticating AD servers for this machine are working correctly, and that you are not accidentally attempting to authenticate to AD in another data center (presuming that you have one). Additionally look for problems on the AD servers, such as high CPU utilization.
For more details on this wait type look at https://www.sqlskills.com/help/waits/preemptive_os_authenticationops/
This may also be due to inadvertent routing of authentication traffic to an AD domain controller in another data center as identified via a network trace. The trace will identify the DC(s) involved for further troubleshooting.


Delete duplicate with same ID PHP/PDO/MYSQL I'm new to databases and I need your help I'm working on a website and I have few problems. I'm using PDO.
There is a user table, with unique ID, name and password. User enters his username and password and is put to the next page where he enters time, date and a third parameter. Those 3 parameters are put into anoter database with user's ID (ID is set with PHP's session). This table has no primary key, since user can insert multiple entries.
Sounds great, but... I want to delete duplicates, or even better prevent user to insert a duplicate.
I tried with this:

$alter = "ALTER IGNORE TABLE data ADD UNIQUE INDEX (ring_time, ring_date, bell_mode)";
$query = $db->prepare($alter);
$query->execute();

try {
    $insert_s = "INSERT INTO data(id, ring_time, ring_date, bell_mode) VALUES(?, ?, ?, ?)";

    $query = $db->prepare($insert_s);

    $query->execute(array($_SESSION['id'], $_POST['ring_time'], $_POST['ring_date'], $_POST['mode']));
} catch(PDOException $e) {
    echo "
Already entered.
"
}

This works somehow right, but if different user inserts same values, it catches the error, even though his ID is different. I'm pretty sure that $alter statement is wrong.
The other thing is: I want to delete every entry which date (the table's date entry is 'date' type) is past today's day. I tried with:

DELETE FROM data WHERE CURDATE() > ring_date";

but with no success, it deletes stuff, but not the right way.

It seems like just the session id would be 'unique' for defining the User?  And maybe a separate table would hold the multiple bell_mode values for each user?  You have to think through the application to figure out what is "unique"; we cannot do it for you.

CURDATE() is a date, not a datetime.  NOW() is a datetime.

CURDATE() < ring_date -- before midnight this morning

It is clumsy to have two separate columns for a "date" and a "time".  I strongly suggest you combine those two columns into a DATETIME datatype; I'll call it ring_dt.  DATE(ring_dt) and TIME(ring_dt) are the 'date' and 'time' parts of ring_dt.  It's easier to take a DATETIME apart than to put it together.

CURDATE() < ring_dt -- same as above
ring_dt > NOW() - INTERVAL 1 DAY  -- more than 24 hours ago (to the second)
ring_dt > CURDATE() - INTERVAL 1 DAY  -- before yesterday's 12am. (not very useful)

- INTERVAL 1 DAY and - INTERVAL 24 HOUR have the same effect.


Two SQL servers for one website I have a website that is deployed on an environment as follows:

a load balance.
two web server.
two database servers.

both of the two database server have the same structure and queries.
I found out that each one of them is storing different data than the other one and not connected.

i do not know much about this. i just need that any update that occurs on any servers be reflected in the other server.

EDIT:
I stopped one of the servers and work on only one of them until we try the replication as it seems to be a wrong configuration.

Load balancing writes in SQL Server is hard. Are you sure this is actually a requirement? If not, and the servers just ended up this way because the implementers didn't understand the requirement, I would start with a single copy of the database that both web applications use, instead of trying to give each database an independent copy.

If it really is the requirement, then you could look into Distributed Partitioned Views, Peer-to-Peer Replication, Bidirectional Replication, or Merge Replication, all of which allow you to combine writes or otherwise share data from multiple instances. My guess, though, is that these solutions are overkill for your actual business requirements.


SQL query to list all student users, add column to show who were logged in at least once I have a query, which lists those users, which are students:

SELECT  *
FROM mdl_user as u
JOIN mdl_role_assignments as ra ON u.id = ra.userid

WHERE
ra.roleid = 5  /* is student */

I have a table with user ids, who were logged in at least once:

SELECT * FROM mdl_user_lastaccess

I need to list all student users, and have a new column, which shows if the user was logged in or not. Logged in: value 1, not logged in: value 0.

I came up with this query, but the loggedin column is always 1. The 0 values are missing:

SELECT  *, (SELECT IF ((SELECT count(*) as c FROM mdl_user as u, mdl_user_lastaccess as ula WHERE u.id = ula.userid), 1, 0)) as loggedin
FROM mdl_user as u
JOIN mdl_role_assignments as ra ON u.id = ra.userid

WHERE
ra.roleid = 5

Any idea how to fix it?

WRONG SOLUTION:

select u.*,
       case
       when ul.userid is null then 0
       else 1
       end loggedin
  from mdl_user u
  join mdl_role_assignments ra
    on u.id = ra.userid
  left join (
     select ula.userid
       from mdl_user_lastaccess ula
      group by ula.userid
) ul
on u.id = ul.userid
WHERE
ra.roleid = 5

ORDER BY loggedin DESC

Results 455 rows, which contains duplicates.

GOOD SOLUTION:

SELECT DISTINCT u.*, IF(ul.userid IS NULL, 0, 1) AS loggedin, ul.courseid
FROM mdl_user u
JOIN mdl_role_assignments ra ON u.id = ra.userid
LEFT JOIN (
    SELECT ula.userid, ula.courseid
    FROM mdl_user_lastaccess ula
    GROUP BY ula.userid
) ul ON u.id = ul.userid
WHERE ra.roleid = 5
ORDER BY loggedin DESC

I had to use DISTINCT to ignore the duplicates.

FINAL GOOD SOLUTION:

SELECT  u.*, IF(la.userid IS NULL, 0, 1) AS loggedin, la.courseid
  FROM mdl_user                 AS u
  JOIN mdl_role_assignments     AS ra ON ra.userid = u.id
  LEFT JOIN mdl_user_lastaccess AS la ON la.userid = u.id
 WHERE ra.roleid = 5           -- is student
 GROUP BY u.id                 -- to collapse all sessions for certain user
 ORDER BY loggedin DESC

Looks like your trying to see if a record for the userid exists in the mdl_user_lastaccess table, you can achieve that with a left join, try this

select u.*,
       case
       when ul.userid is null then 0
       else 1
       end loggedin
  from mdl_user u
  join mdl_role_assignments ra
    on u.id = ra.userid
  left join (
     select ula.userid
       from mdl_user_lastaccess ula
      group by ula.userid
) ul
on u.id = ul.userid

Another approach using the exists statement

select u.*,
       1 as loggedin
  from mdl_user u
  join mdl_role_assignments ra
    on u.id = ra.userid
 where exists (
    select 1
      from mdl_user_lastaccess ul
     where u.id = ul.userid
 )

union

select u.*,
       0 as loggedin
  from mdl_user u
  join mdl_role_assignments ra
    on u.id = ra.userid
 where not exists (
    select 1
      from mdl_user_lastaccess ul
     where u.id = ul.userid
 )


Delete into new table (like select into) The select into syntax will create a new table to insert data into (I know that types may not be consistent etc. but it gives you a rough copy).

Is it possible for a delete output into statement to also create a new table in the same way, without having to define the table first?

Not that I know of. The OUTPUT.. INTO clause requires an existing table.

On a related note

Using SELECT.. INTO as opposed to INSERT INTO.. SELECT or OUTPUT.. INTO can have negative side-effects depending the situation. Some examples why I think you should create the table first:

If you're creating a table inside of a transaction, SQL Server may place a lock on (parts of) the database schema, which would prevent other users from creating, altering or dropping database objects. I'm assuming that this could even affect reading the schema, such as opening a database in Object Explorer in Management Studio. Create the table outside the transaction, then populate inside the transaction.
If you want to assign a clustered index to your new table, and that clustered index matches the populating query's sort order, SELECT.. INTO will create the table as a heap (unsorted), so when you apply the clustered index after, SQL Server will have to sort the contents of the table all over again.


How to use COALESCE with multiple rows and without preceding comma? I'm trying to achieve the following:

California | Los Angeles, San Francisco, Sacramento
Florida    | Jacksonville, Miami

Unfortunately, I'm getting ",Los Angeles, San Francisco, Sacramento, Jacksonville, Miami"

I can achieve my desired results using the STUFF function, but was wondering if there's a cleaner way of doing it using COALESCE?

STATE       | CITY
California  | San Francisco
California  | Los Angeles
California  | Sacramento
Florida     | Miami
Florida     | Jacksonville

DECLARE @col NVARCHAR(MAX);
SELECT @col= COALESCE(@col, '') + ',' + city
FROM tbl where city = 'California';
SELECT @col;

Thanks

This might be the cleaner approach you're after. Basically, check if the variable has been initialized yet. If it hasn't, set it to the empty string, and append the first city (no leading comma). If it has, then append a comma, then append the city.

DECLARE @col nvarchar(MAX);
SELECT @col = COALESCE(@col + ',', '') + city
  FROM dbo.tbl WHERE state = 'California';

Of course, that only works for populating a variable per state. If you are pulling the list for each state one at a time, there is a better solution in one shot:

SELECT [state], cities = STUFF((
    SELECT N', ' + city FROM dbo.tbl
    WHERE [state] = x.[state]
    FOR XML PATH(''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 2, N'')
FROM dbo.tbl AS x
GROUP BY [state]
ORDER BY [state];

Results:

state       cities
----------  --------------------------------------
California  San Francisco, Los Angeles, Sacramento
Florida     Miami, Jacksonville

To order by city name within each state:

SELECT [state], cities = STUFF((
    SELECT N', ' + city FROM dbo.tbl
    WHERE [state] = x.[state]
    ORDER BY city
    FOR XML PATH(''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 2, N'')
FROM dbo.tbl AS x
GROUP BY [state]
ORDER BY [state];

In Azure SQL Database or SQL Server 2017+, you can use the new STRING_AGG() function:

SELECT [state], cities = STRING_AGG(city, N', ')
  FROM dbo.tbl
  GROUP BY [state]
  ORDER BY [state];

And ordered by city name:

SELECT [state], cities = STRING_AGG(city, N', ')
                         WITHIN GROUP (ORDER BY city)
  FROM dbo.tbl
  GROUP BY [state]
  ORDER BY [state];


Save Results as CSV *with* headers in SSMS With SQL Server 2008 R2, when I save the results as a CSV there are no headers. I can work around this by copying and pasting with the "Copy with Headers" , or just grab the headers and paste them in the CSV etc, but is there a better way to do this?

In SSMS you need to change some options:

Tools - Options - Query results - sql server - results to grid (or text) -> Include column headers when copying or saving the results.

Changed settings are applied to new, but not existing query windows.


How Can I clone a database in Microsoft SQL server using PHP? I am creating a portal where user can create the b2c or b2b store itself, so I need to clone the sample database to perform the task. It's not looking simple to clone the database in MSSQL. Please help if someone knows the solution.

$sql = "RESTORE DATABASE test2 FROM DISK=N'/var/pathto/TEST.bak'
        WITH REPLACE, RECOVERY
        , MOVE 'TEST' TO '/var/pathto/TEST.mdf'
        , MOVE 'TEST_log' TO '/var/pathto/TEST_log.ldf'";
$stmt   =   sqlsrv_query($conn, $sql);
if($stmt === false)
{
    echo '
'; print_r(sqlsrv_errors()); echo '
';
    die();
}else
{
    exit('executedd.');
}

What's wrong with my script?

/**************************************/

After hours of research below script is working but still showing restoring in SQL Server Management studio, check the attached screenshot.

$sql = "CREATE DATABASE test9";
$stmt   =   sqlsrv_query($conn, $sql);
if($stmt === false)
{
    echo '
'; print_r(sqlsrv_errors()); echo '
';
    die();
}else
{
    //echo ('executedd.');
}

$sql = "USE test9";
$stmt   =   sqlsrv_query($conn, $sql);
if($stmt === false)
{
    echo '
'; print_r(sqlsrv_errors()); echo '
';
    die();
}else
{
    //echo ('executedd.');
}

$sql = "BACKUP DATABASE test9 TO DISK = '/var/opt/mssql/data/test9.bak'";
$stmt   =   sqlsrv_query($conn, $sql);
if($stmt === false)
{
    echo '
'; print_r(sqlsrv_errors()); echo '
';
    die();
}else
{
    //echo ('executedd.');
}

$sql = "RESTORE DATABASE test9 FROM DISK='/var/opt/mssql/data/test4.bak'
        WITH REPLACE, RECOVERY
        , MOVE 'test4' TO '/var/opt/mssql/data/test9.mdf'
        , MOVE 'test4_log' TO '/var/opt/mssql/data/test9_log.ldf'";
$stmt   =   sqlsrv_query($conn, $sql);
if($stmt === false)
{
    echo '
'; print_r(sqlsrv_errors()); echo '
';
    die();
}else
{
    //echo ('executedd.');
}
exit;

Your best option is to take and then restore a backup of the database.  If your sample database is fairly static then I suggest making a gold standard backup and using that.  If it's under constant modification then taking a backup and then restoring it on the spot should be sufficient.

BACKUP DATABASE [Sample] TO DISK=N'<path to file>'

RESTORE DATABASE [TargetName] FROM DISK=N'<path to file>'
WITH REPLACE, RECOVERY
, MOVE 'file1' TO '<new path for data file>'
, MOVE 'log1' TO '<new path for log file>'

Just make those commands dynamic and grant whatever user you are using for this db_creator and db_backupoperator

However, this command assumes that [TargetName] doesn't already exist.  You can check for the existence of a database by querying sys.databases.  If you need to override an existing database, then you need to kick everyone out before starting the restore.

Both of the commands above should be run from [master] to avoid conflicts.

This command will set a database to single_user mode, which will kick everyone out.  However, the first connection to it will take the only allowed connection, so you should run this and then immediately run the restore command, preferably in the same transaction.

ALTER DATABASE [TargetName]
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;


What are some efficient ways in using COUNT(*) with WHERE Instead of calling COUNT(*) with a WHERE clause, is there anything one can do to make this work more efficiently on the server?  I would like to get this count on complicated joins, just wondering the best way to do this. Using SET NOCOUNT OFF? Because SET NOCOUNT ON is enabled by default in a new stored procedure and is suggested I guess by Microsoft.  Thank you.

SET NOCOUNT has nothing to do with your ability to perform counts or the efficiency of doing so.  This setting merely enables the reporting of affected rowcounts to the client (e.g. the n row(s) affected. message in Management Studio). The reason it is recommended to be off is because it is unnecessarily chatty and can break some applications / APIs.

As for getting more efficient counts from complex joins, it's possible that your joins could be made more efficient, but we can't see them. It's also possible that you can pay that cost at a different time by using filtered indexes, indexed views, or pre-aggregating yourself at insert/update time, using a trigger, etc. Sometimes this can pay off but other than a general suggestion there's not much else we'll be able to offer you without a lot more details.