If you have a look at this question here:
space usage on sys.allocation_units and sp_spaceused

You will fin this script below, that will tell you teh size of your indexes.

;WITH agg AS
(   -- Get info for Tables, Indexed Views, etc
    SELECT  ps.[object_id] AS [ObjectID],
            ps.index_id AS [IndexID],
            NULL AS [ParentIndexID],
            NULL AS [PassThroughIndexName],
            NULL AS [PassThroughIndexType],
            SUM(ps.in_row_data_page_count) AS [InRowDataPageCount],
            SUM(ps.used_page_count) AS [UsedPageCount],
            SUM(ps.reserved_page_count) AS [ReservedPageCount],
            SUM(ps.row_count) AS [RowCount],
            SUM(ps.lob_used_page_count + ps.row_overflow_used_page_count)
                    AS [LobAndRowOverflowUsedPageCount]
    FROM    sys.dm_db_partition_stats ps
    GROUP BY    ps.[object_id],
                ps.[index_id]
    UNION ALL
    -- Get info for FullText indexes, XML indexes, Spatial indexes, etc
    SELECT  sit.[parent_id] AS [ObjectID],
            sit.[object_id] AS [IndexID],
            sit.[parent_minor_id] AS [ParentIndexID],
            sit.[name] AS [PassThroughIndexName],
            sit.[internal_type_desc] AS [PassThroughIndexType],
            0 AS [InRowDataPageCount],
            SUM(ps.used_page_count) AS [UsedPageCount],
            SUM(ps.reserved_page_count) AS [ReservedPageCount],
            0 AS [RowCount],
            0 AS [LobAndRowOverflowUsedPageCount]
    FROM    sys.dm_db_partition_stats ps
    INNER JOIN  sys.internal_tables sit
            ON  sit.[object_id] = ps.[object_id]
    WHERE   sit.internal_type IN
               (202, 204, 207, 211, 212, 213, 214, 215, 216, 221, 222, 236)
    GROUP BY    sit.[parent_id],
                sit.[object_id],
                sit.[parent_minor_id],
                sit.[name],
                sit.[internal_type_desc]
), spaceused AS
(
SELECT  agg.[ObjectID],
        agg.[IndexID],
        agg.[ParentIndexID],
        agg.[PassThroughIndexName],
        agg.[PassThroughIndexType],
        OBJECT_SCHEMA_NAME(agg.[ObjectID]) AS [SchemaName],
        OBJECT_NAME(agg.[ObjectID]) AS [TableName],
        SUM(CASE
                WHEN (agg.IndexID < 2) THEN agg.[RowCount]
                ELSE 0
            END) AS [Rows],
        SUM(agg.ReservedPageCount) * 8 AS [ReservedKB],
        SUM(agg.LobAndRowOverflowUsedPageCount +
            CASE
                WHEN (agg.IndexID < 2) THEN (agg.InRowDataPageCount)
                ELSE 0
            END) * 8 AS [DataKB],
        SUM(agg.UsedPageCount - agg.LobAndRowOverflowUsedPageCount -
            CASE
                WHEN (agg.IndexID < 2) THEN agg.InRowDataPageCount
                ELSE 0
            END) * 8 AS [IndexKB],
        SUM(agg.ReservedPageCount - agg.UsedPageCount) * 8 AS [UnusedKB],
        SUM(agg.UsedPageCount) * 8 AS [UsedKB]
FROM    agg
GROUP BY    agg.[ObjectID],
            agg.[IndexID],
            agg.[ParentIndexID],
            agg.[PassThroughIndexName],
            agg.[PassThroughIndexType],
            OBJECT_SCHEMA_NAME(agg.[ObjectID]),
            OBJECT_NAME(agg.[ObjectID])
)
SELECT sp.SchemaName,
       sp.TableName,
       sp.IndexID,
       IIF(sp.IndexID > 0, COALESCE(si.[name], sp.[PassThroughIndexName]), N'<Heap>')
             AS [IndexName],
       sp.[PassThroughIndexName] AS [InternalTableName],
       sp.[Rows],
       sp.ReservedKB,
       (sp.ReservedKB / 1024.0 / 1024.0) AS [ReservedGB],
       sp.DataKB,
       (sp.DataKB / 1024.0 / 1024.0) AS [DataGB],
       sp.IndexKB,
       (sp.IndexKB / 1024.0 / 1024.0) AS [IndexGB],
       sp.UsedKB AS [UsedKB],
       (sp.UsedKB / 1024.0 / 1024.0) AS [UsedGB],
       sp.UnusedKB,
       (sp.UnusedKB / 1024.0 / 1024.0) AS [UnusedGB],
       so.[type_desc] AS [ObjectType],
       COALESCE(si.type_desc, sp.[PassThroughIndexType]) AS [IndexPrimaryType],
       sp.[PassThroughIndexType] AS [IndexSecondaryType],
       SCHEMA_ID(sp.[SchemaName]) AS [SchemaID],
       sp.ObjectID
       --,sp.ParentIndexID
FROM   spaceused sp
INNER JOIN sys.objects so
        ON so.[object_id] = sp.ObjectID
LEFT JOIN  sys.indexes si
       ON  si.[object_id] = sp.ObjectID
      AND  (si.[index_id] = sp.IndexID
         OR si.[index_id] = sp.[ParentIndexID])
WHERE so.is_ms_shipped = 0
--so.[name] LIKE N''  -- optional name filter
--ORDER BY ????


Changing own user permissions with Oracle SQL not being the DBA

You mentioned as one of your examples as "from production to development" .. so I'll start there, and with that, assume that your different "environments" are, in fact, different instances/databases as well.

Always "PULL" data ... rather than "PUSH".
So in other words, login to where you the data will be INSERTED.  That id should have INSERT access to the target tables. That same id should ONLY HAVE READ access to the source (ie production).
You can set that up via a db link or such.

You then

INSERT into <local table>
  select * from <remote table>@dblink;

and you can't go wrong, since you don't have access.

This works for any two environments ... if you setup those db links using READ ONLY ids, and you do the INSERT locally with a user with local INSERT privs .. you're safe.


Make a (copy of a) postgis database available to customer?

If it's one time setup, I think dump would be easiest. But if You need your update be available on customer, then I think streaming replication would be easier, set up once and no need to update in the future as the replication will handle the data updates.

http://www.postgresql.org/docs/current/static/high-availability.html


Check partition function if list of value exists, if not iterate through list and add new partition value to the partition function?

SQL Server table partitioning is natively range partitioning. Although you can implement list partitioning (i.e. partitioning columns exactly match boundaries), take care to ensure exact boundary matches to avoid excessive data movement and logging during SPLIT operations. Consider creating a CHECK constraint on the fact table matching the boundary values (e.g. TEXT IN('A','B','C', …).

Below is an example script based on the DDL and sample data in your question, which assumes (at least) a clustered index exists on the fact table. I added some additional boundaries to illustrate new boundaries between existing ones can be introduced during the ETL process that without requiring data movement, assuming partitioning column values match boundaries exactly. See inline comments for details of each step.

The move from staging to the fact table took a couple of seconds in my test. Be sure to update fact table stats afterward.

CREATE PARTITION FUNCTION PF_TEXT(varchar(20))
    AS RANGE RIGHT
    FOR VALUES ('A', 'B', 'C', 'D', 'E', 'M', 'S');
CREATE PARTITION SCHEME PS_TEXT
    AS PARTITION PF_TEXT
    ALL TO ([PRIMARY]);

CREATE TABLE dbo.fact_partitioned(
    Col1 int NOT NULL,
    Col2 int NOT NULL,
    TEXT varchar(20) NOT NULL
        INDEX cdx CLUSTERED ON PS_TEXT (TEXT)
) ON PS_TEXT (TEXT);

--load some example data into fact table
WITH
     t10 AS (SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) t(n))
    ,t1k AS (SELECT 0 AS n FROM t10 AS a CROSS JOIN t10 AS b CROSS JOIN t10 AS c)
    ,t1g AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS num FROM t1k AS a CROSS JOIN t1k AS b CROSS JOIN t1k AS c)
INSERT INTO dbo.fact_partitioned WITH(TABLOCKX) (Col1, Col2, TEXT)
SELECT 1,1,'A'
FROM t1g
WHERE num <= 1000000
UNION ALL
SELECT 1,1,'B'
FROM t1g
WHERE num <= 1000000
UNION ALL
SELECT 1,1,'C'
FROM t1g
WHERE num <= 1000000
UNION ALL
SELECT 1,1,'D'
FROM t1g
WHERE num <= 1000000
UNION ALL
SELECT 1,1,'E'
FROM t1g
WHERE num <= 1000000
UNION ALL
SELECT 1,1,'M'
FROM t1g
WHERE num <= 1000000
UNION ALL
SELECT 1,1,'S'
FROM t1g
WHERE num <= 1000000;
GO

--example ETL objects and data
DROP TABLE IF EXISTS dbo.fact_partitioned_staging;

--staging table must have same indexes as target (aligned)
CREATE TABLE dbo.fact_partitioned_staging(
    Col1 int NOT NULL,
    Col2 int NOT NULL,
    TEXT varchar(20) NOT NULL
        INDEX cdx CLUSTERED ON PS_TEXT (TEXT)
) ON PS_TEXT (TEXT);

WITH
     t10 AS (SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) t(n))
    ,t1k AS (SELECT 0 AS n FROM t10 AS a CROSS JOIN t10 AS b CROSS JOIN t10 AS c)
    ,t1g AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS num FROM t1k AS a CROSS JOIN t1k AS b CROSS JOIN t1k AS c)
INSERT INTO dbo.fact_partitioned_staging WITH(TABLOCKX) (Col1, Col2, TEXT)
--replaces existing partition
SELECT 2,2,'C'
FROM t1g
WHERE num <= 1000000
UNION ALL
--new partion BETWEEN 'E' and 'M'
SELECT 2,2,'G'
FROM t1g
WHERE num <= 1000000
--new partion AFTER 'S'
UNION ALL
SELECT 2,2,'X'
FROM t1g
WHERE num <= 1000000;
GO

--dynamic list partitioning load
SET XACT_ABORT ON; --best practice with explict transactions
DECLARE @SQL nvarchar(MAX);
BEGIN TRY

    BEGIN TRAN;

    --get distinct incremental load TEXT values from staging
    DECLARE @incremental_load_boundaries TABLE(
          incremental_load_boundary varchar(20) NOT NULL PRIMARY KEY
        );
    INSERT INTO @incremental_load_boundaries(incremental_load_boundary)
        SELECT DISTINCT TEXT
        FROM dbo.fact_partitioned_staging;

    --recreate new partition function/scheme with existing partition boundaries plus new distinct TEXT values
    IF EXISTS(SELECT 1 FROM sys.partition_schemes WHERE name = N'PS_TEXT_STAGING')
        DROP PARTITION SCHEME PS_TEXT_STAGING;
    IF EXISTS(SELECT 1 FROM sys.partition_functions WHERE name = N'PF_TEXT_STAGING')
        DROP PARTITION FUNCTION PF_TEXT_STAGING;
    SELECT @SQL =
        N'CREATE PARTITION FUNCTION PF_TEXT_STAGING(varchar(20)) AS RANGE RIGHT FOR VALUES ('
        + STRING_AGG(QUOTENAME(boundary,''''),',')
        + N');'
    FROM (
        --existing target table boundaries
        SELECT CAST(value AS varchar(20)) AS boundary
        FROM sys.partition_range_values
        WHERE function_id = (SELECT function_id FROM sys.partition_functions WHERE name = N'PF_TEXT')
        UNION
        --staging table boundaries
        SELECT incremental_load_boundary
        FROM @incremental_load_boundaries
        ) AS boundaries;
    PRINT @SQL;
    EXECUTE sp_executesql @sql;
    CREATE PARTITION SCHEME PS_TEXT_STAGING AS PARTITION PF_TEXT_STAGING ALL TO ([PRIMARY]);

    --partition staging table using new partition scheme with end state boundaries
    CREATE CLUSTERED INDEX cdx ON dbo.fact_partitioned_staging(TEXT)
        WITH(DROP_EXISTING=ON)
        ON PS_TEXT_STAGING(TEXT);

    --SPLIT existing partition function to add new boundaries
    --no data movement needed when existing partition column values exactly match existing boundaries
    SELECT @SQL = STRING_AGG(
        'ALTER PARTITION SCHEME PS_TEXT NEXT USED [PRIMARY];ALTER PARTITION FUNCTION PF_TEXT() SPLIT RANGE('
        + QUOTENAME(new_boundaries.boundary, '''')
        + N')'
        , ';')
    FROM (
        --existing target table boundaries
        SELECT CAST(value AS varchar(20)) AS boundary
        FROM sys.partition_range_values
        WHERE function_id = (SELECT function_id FROM sys.partition_functions WHERE name = N'PF_TEXT_STAGING')
        EXCEPT
        --all boundaries
        SELECT CAST(value AS varchar(20)) AS boundary
        FROM sys.partition_range_values
        WHERE function_id = (SELECT function_id FROM sys.partition_functions WHERE name = N'PF_TEXT')
        ) AS new_boundaries
    PRINT @SQL;
    EXECUTE sp_executesql @sql;

    --truncate partitions to be reloaded
    SELECT @SQL =
        N'TRUNCATE TABLE dbo.fact_partitioned WITH(PARTITIONS('
        + STRING_AGG(CAST(partition_number AS varchar(10)),',')
        + N'));'
    FROM (
        SELECT DISTINCT $PARTITION.PF_TEXT(incremental_load_boundary) AS partition_number
        FROM @incremental_load_boundaries
        ) AS existing_boundaries;
    PRINT @SQL
    EXECUTE sp_executesql @sql;

    --switch in incremental load partitions
    SELECT @SQL = STRING_AGG(
        N'ALTER TABLE dbo.fact_partitioned_staging SWITCH PARTITION '
        + CAST(partition_number AS varchar(10))
        + N' TO dbo.fact_partitioned PARTITION '
        + CAST(partition_number AS varchar(10))
        , ';')
        + N';'
    FROM (
        SELECT DISTINCT $PARTITION.PF_TEXT(incremental_load_boundary) AS partition_number
        FROM @incremental_load_boundaries
        ) AS existing_boundaries;
    PRINT @SQL
    EXECUTE sp_executesql @sql;

    --assert staging table is now empty
    IF EXISTS(SELECT 1 FROM dbo.fact_partitioned_staging)
        RAISERROR('Assertion failed: staging table is not empty',16,1);

    COMMIT;

END TRY
BEGIN CATCH
    IF @@ROWCOUNT > 0 ROLLBACK;
    THROW;
END CATCH;
GO


Computed Column Bit based on other Bit Columns

Do you mean IsOk = 1 if the other three bit columns all = 1 else IsOk = 0?

If so: -

CREATE TABLE #Employee
(
[empNumb]       [INT] identity(1,1) PRIMARY KEY,
[IsOk]          AS [HasValidHours] & [NoDemerits] & [NoAccidents],
[HasValidHours] [BIT] NOT NULL,
[NoDemerits]    [BIT] NOT NULL,
[NoAccidents]   [BIT] NOT NULL
);

See & (Bitwise AND) (Transact-SQL)


SQL Server 2012 - Dropping Snapshots takes hours

Can you please confirm the Compatibility level of the database you upgraded? (Right-click the database -> Properties -> Options -> Compatibility level). If this is set to 2005, please don't change it without thoroughly testing your applications, etc. as it could definitely break your environment! Just curious as to the value here.

Also, I did some work with a test system and didn't see the behavior you describe. Even if I had an open transaction using the DB snapshot I could drop it from another session and the query running on the snapshot would immediately fail (rather than block me from dropping the DB snapshot).

I would recommend taking a closer look at the disk where the DB snapshot is created (since dropping the DB snapshot physically deletes the snapshot file created). There possibly could be speed issues there causing the delete of the snapshot file to simply take close to an hour...or there could be contention at the file level (something trying to back up the DB snapshot file while you're trying to drop the DB snapshot, virus tools scanning the DB snapshot file, etc.).


Can't connect to SQL Server DB from SSMS network error

As you said that the SQL Server Error code : 18456 is coming with state: 8.

Mostly the Error code : 18456 with state : 8 coming due to user authentication.

As per MSDN Blog here the key to the message is the 'State' which the server will accurately set to reflect the source of the problem.

For Troubleshooting Error 18456

As SQLBlog of @AaronBertrand here ,  you can find out every error details with description , based on their state with Error code: 18456.

After Edit

As SQLSERVERLEARNER Blog here  the

  Error Number: 18456 Severity: 14 State: 1

may have so many reasons.

Reasons for this error:

User does not have access on the server
Provided user name is wrong
Provided Password is Wrong
Using windows login credentials for SQL Server authentication
Login for the user is disabled
Login was successful but access to the server failed
Problems with SQL Service
Password for the user has to be changed
User does not have access to perform the specified acton on the
server, Only has limited access
User does not have previlages to access the specific database,
 agent service etc
Even here MSDN BOL here
 mention that for State : 1 , Error information is not available.
 This state usually means you do not have permission to receive the
 error details. Contact your SQL Server administrator for more
 information.
As here
 State 1 now occurs when a login is disabled – but actually, the
 error in the log is 18470, not 18456 – because the login is
 disabled, it doesn't get that far.  See state 7. Prior to SQL
 Server 2005, State 1 always appeared in the log for all login
 failures, making for fun troubleshooting. The login is disabled
 and the password is incorrect.  This shows that password validation occurs first, since if the password is correct and the
 login is disabled, you get error 18470 (see state 1 above). It's
 possible that your application is sending cached credentials and
 the password has been changed or reset in the meantime – you may
 try logging out and logging back in to refresh these credentials.

For further your ref here and here


How to store iterations of similar datasets

Extending comment to answer:

MySQL can handle lots of rows. So making separate schema for each month gives no benefits unless those datasets differ in schema definition.

With some millions of rows per month you have to design your schema and index carefully enough to make querying fast. When you do it so dataset_id (some month identifier or something) is first in primary key for each table, then (at least in InnoDB by default) your data are clustered by it, so they are kept close together in table structure and disk access is not wasted.

If some tables are going to be really big, then you can use partitioning by dataset/month (probably one partition per month, partitioning key has to be part of primary key and each unique index - not sure if it has to be last in index or anywhere..). That way each partition is like physical table, your queries always access specific dataset so can only work on one such "subtable" instead of all data, and you can easily drop old partitions to reclaim some space (probably after moving the data into some archive).


select subquery condition in where statement

You can't reference a column alias on the same level where you introduced it. You need to wrap your query into a derived table. Note that the columns in the SELECT list of your sub-query are pretty useless as you are only using the balance column in the outer query:

select *
from (
  select PARTY_ID,
         PARTY_NAME,
         IT_ID,
         IT_ID || ' - ' || IT_DESC || ' - ' || IT_BRAND,
         (select SUM(BALANCE)
          FROM (
            select NVL(WEAVING_YARN_TR.BAGS_OUT,0)-NVL(WEAVING_YARN_TR.BAGS_IN,0) as BALANCE
            from WEAVING_YARN_TR,
                   WEAVING_YARN_GATEPASS
            where WEAVING_YARN_TR.GP_NO = WEAVING_YARN_GATEPASS."GP_NO."
              and WEAVING_YARN_TR.YR_ID = WEAVING_YARN_ITEMS.IT_ID
              and WEAVING_YARN_GATEPASS.TO_FROM = WEAVING_PARTIES.PARTY_ID
            UNION ALL
            select NVL(null,0)-NVL(WEAVING_WARP_SET.TOTAL_BAGS_CONSUMED,0) as BALANCE
            from WEAVING_WARP_SET
            where WEAVING_WARP_SET.YARN_ITEM = WEAVING_YARN_ITEMS.IT_ID
              and WEAVING_WARP_SET.PARTY = WEAVING_PARTIES.PARTY_ID
           ) t
          ) AS STOCK_BALANCE
  FROM WEAVING_PARTIES
) x
  CROSS JOIN WEAVING_YARN_ITEMS
WHERE x.STOCK_BALANCE is not null;


What would be the correct size of redo logs?

Changing the size of your redo logs won't affect how much space is occupied by archived redo logs.  If you reduce the size of your redo logs from 200 MB to 100 MB, you'll end up with twice as many archived redo logs each of which is half the size.  The volume of archived logs that you generate is dependent on the amount of REDO your system generates not on the size of your redo logs.

In general, you want the redo logs to be large enough that you are not constantly swapping logs (generally not more than every 15 or 20 minutes) and small enough that you aren't risking too much data should there be a catastrophic server failure (assuming that archived redo logs are written to a different server and that you aren't using something like DataGuard to replicate the redo to a backup server in real time).


How to sum (aggregate) all the values in a key/value JSONB?

Despite having searched for hours I think I found my answer 10 minutes after posting!

Including for reference for others:

SELECT id, sum(value::float)
FROM testtable
CROSS JOIN LATERAL jsonb_each_text(qty)
GROUP BY id;


SQL Server 2012 AlwaysOn cannot connect using the Listener IP

I got a response from Amazon

"Cluster IPs aren't supported in VPC at this time."


SQL query to get the user, counting the minimum number of records

Add a WHERE clause to filter the rows where last_edit_user is null. Then put another query around it, to get the minimum count. Use this as a subquery in the HAVING clause to get only the ones with the count equal to the minimum.

SELECT last_edit_user,
       count(last_edit_user)
       FROM edited_elements_table
       WHERE last_edit_user IS NOT NULL
       GROUP BY last_edit_user
       HAVING count(last_edit_user) = (SELECT min(c)
                                              FROM (SELECT count(last_edit_user) c
                                                           FROM edited_elements_table
                                                           WHERE last_edit_user IS NOT NULL
                                                           GROUP BY last_edit_user) x);


How do I judge whether a given sql statement has sql injection risk or not

Analysis the sql comes from sql log only, trying to find out which sql statement has risk of sql injection.

By the time the SQL reaches your database, it's probably too late to tell.
By the time the SQL reaches the sql log (having been executed), it's definitely too late!
If you see lots of placeholders ('?' or ':name'), then your SQL is using Prepared Statements and, therefore, is probably safe from SQL Injection.
If you see lots of literal values, your database has no way of knowing how they got there.
For example, both of the following would reach you looking exactly the same but, as you can clearly see, one is clearly vulnerable.
$sql = "update table1 set name = 'fred' where id = 76543 ";

$name = $_POST['name'];
$id =   $_POST['id'];
$sql =  "update table1 set name = '$name' where id = $id ";

SQL Injection is an Application problem.  Plain and simple.
Point your Developers in the direction of this Accepted Answer, over on Software Engineering, particularly the Security section.


Replacing master.dbo.sysperfinfo with sys.dm_os_performance_counters

What are the differences between dm_os_performance_counters and master.dbo.sysperfinfo?

sys.dm_os_performance_counters is the replacement for sysperfinfo.  Please see this BOL reference on the mapping of system tables to system views/DMVs.  You should be using sys.dm_os_performance_counters.

  Do I need to pull dm_os_performance_counters from the Master context?

No, DMVs (or system catalog views) can be accessed from the context of any database, provided you have the appropriate permissions to view the contents.  The below example for illustration purposes would be just fine:

use NonMasterDatabase;
go

select *
from sys.dm_os_performance_counters;


New users not working on MySQL 5.6 in Amazon RDS

I forgot that I had created a stored procedure to set the time zone after every connection made.

Looking at the server log I found that the problem was that the new users didn't have permission to execute it.


Nested AD group access not working when nested more than 2 levels deep?

Sorry I don't get it. You write:

On my database I have added the group RGP-EA_PROD-RW as a user login and given it db_datareader and db_datawriter roles

Okay. So that means RGP-SparxEA-RW ought to be able to login and do stuff.
User1 is not a member of RGP-EA_PROD-RW so why do you expect for User1 to be able to login?

User1 is a member of BGP-FunctionalAnalyst who is a member of RGP-SparxEA-RW, who is a member of RGP-EA_PROD-RW. So yeah, User1 is ultimately a member of RGP-EA_PROD-RW.

MYDOMAIN\User1 suggests when you login you the username needs to be prefixed with a real domain.  There is a weirdness that the groups that work are all Domain Local Security Groups.  Maybe the Global Security Group only exists in Azure - not on the local domain controllers. Next step is to query group membership with VBscript or Powershell and confirm whether the group membership is what you think it is.
Standard practice is to make local groups inherit from global groups. In your case it's the other way around.


if exists, update else insert, with cursors in stored procedures only returns 1 row

It worked after using INSERT ... ON DUPLICATE KEY UPDATE. Thank you @RickJames. For anyone interested here is the code

DELIMITER //
CREATE PROCEDURE curhit12()

BEGIN

 DECLARE done INT DEFAULT FALSE;
 DECLARE apiId, noOfHits int(11);
 Declare apiContext varchar(255);
 DECLARE cur1 CURSOR FOR Select api_id from api;
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  OPEN cur1;

read_loop: LOOP
    FETCH cur1 INTO apiId;

    IF done THEN
      LEAVE read_loop;
    END IF;

    select api_context into apiContext from api where api_id =apiId;

SET noOfHits = (SELECT COUNT(*) FROM api_request_summary
                WHERE api_context=apiContext);

INSERT INTO api_hits (api_id,no_of_hits) VALUES (apiId,noOfHits)
   ON DUPLICATE KEY UPDATE no_of_hits=noOfHits;

  END LOOP;

CLOSE cur1;

END//
DELIMITER


How can I corrupt a table in SQL Server?

Paul Randal has several Sample corrupt databases to play with:

DemoDataPurity -  192-MB SQL Server 2005 database with a single 2570 (data purity) error
DemoFatalCorruption1 -  1-MB SQL Server 2005 database with a corrupt system table (that allows CHECKDB to complete)
DemoFatalCorruption2 - 1-MB SQL Server 2005 database with a corrupt system table (that terminates CHECKDB)
DemoNCIndex - 192-MB SQL Server 2005 database with a bunch of nonclustered index corruptions
DemoRestoreOrRepair - 1-MB SQL Server 2005 database with a page checksum failure (in fact a zero'd out page)
DemoCorruptMetadata - 1-MB SQL Server 2000 database with corrupt syscolumns table

Also, How to create a corrupt database using BULK INSERT/ UPDATE and BCP - SQL Server as a HEX editor.


MySQL Sharding tables/servers or other methods to reduce table sizes?

A good RDBMS can grow to accommodate extremely large data. 3Gb databases are very manageable, and very probably, as long as you can get a server with enough RAM, most queries will run pretty fast with little effort.

Even when you exceed RAM, indexes, caching and partitioning let you still perform well. Very often, applications access most a relatively small working set- for instance, 90% queries might be limited to last month's data- while the 10% can be queries over older data. "Last month's" data tends to be somewhat stable- it grows when you have more users, but other than that, it doesn't tend to grow with time. This "working set" often fits in RAM, gets cached and you still get great performance.

But then, you can again get slowness. With proper monitoring and analysis, you can locate the queries which are running slow and take steps to solve them.

This is often simple:

Queries or incorrect code: often we write queries that retrieve information which is unneeded, or which execute several queries when one would suffice and be faster (the typical case is an operation which needs to display n rows of a table and performs n queries when 1 would be enough. Performing aggregates outside the database is also frequent). This is easily fixed by changing your code
Queries which do not run efficiently. EXPLAIN is your friend here. Often, creating indexes that the query can use is enough (roughly, you'll want to index on columns which appear in the WHERE clause). Also, sometimes tweaking the query itself will yield good results

Another approach which gives good results is throwing hardware at the problem:

Buying more RAM
Getting more and better disks (RAID10 setups get faster if you add more disks, SSDs often have significant benefits, etc.)
It is not frequent, but sometimes CPU is your bottleneck- you can get faster processors and more cores/processors

In some other cases, replication and sharding might be a problem. Replication is complicated, but stuff such as Oracle RAC let's you build monster clusters (at a price). Sharding is another option, but it's often one of the most complex to implement- even applications which shard easily require lots of work to be sharded, and some applications can be notoriously hard to shard.


Which option do I have to modify to see more records without press `q`

You can turn off (and on) the paging in psql:

\pset pager [on|off]

If you don't specify on or off, the command will toggle the setting.  (More on \pset in the documentation.)
In the shell, you can even set the pager you want to use:

PAGER=/usr/bin/less

If you start psql after this, less will be your pager.


User info retrieval takes too long - ASP.NET Membership

The execution plan you provided shows the INSERT INTO...SELECT portion of the procedure running in 19 ms.  The SELECT portion, which produces the actual results, ran in 1 ms.

The specific parameters didn't produce any results, but that's because the @PageSize and @PageIndex parameters were NULL.

Even if you included all the users, the data suggests this procedure would still run in less than a second on these (relatively, for SQL Server) small tables.

All that is to say that the execution plan suggests that this is a problem outside of SQL Server.

It could be that the application has changed, and is running more queries than expected when that page loads (and this is just one of them).  Or there is significant latency between the app server and the new Azure VM, among probably many other potential issues.

You'll need to work with the application developers to try and narrow down what, exactly, is slow when this page loads.  It does not appear that SQL Serve is taking minutes to produce this list of users.


Are read replicas heavy reading in affect masterDB?

From your tags, I guess its for AWS RDS Aurora. Generally, Aurora's design differs from native databases. It has a distributed storage system.

Of course, if you huge select will affect the master when you have MyISAM in place. But Aurora completely designed with InnoDB. So it won't.

Not sure whether your replica and master are in the same AZ. Its a good practice to use different AZ in Aurora.

Due to its PIOPS, your massive select load will not affect your master anyway. If you have a predictable load on a particular time, use Aurora autoscaling to share the loads between multiple read replica.


Large database - too much time spent opening and closing tables

Generally throw RAM at it - especially given that you do not even say how much RAM you have. DAtabases love WARM and unless you are in the way to change the number of tables (90k is QUITE a lot) then basically thow RAM at it.

At least until you reach the low end of a low end database serer - which at these days would be 32gb memory (full memory loadout for a low end single processor Xeon).


PostgreSQL service start failed on CentOS 7

You have 3 steps before being abble to connect to a PostgreSQL Database:

Install PostgreSQL
Create the data cluster ($PGDATA)
Start the server

You already installed PostgreSQL, you need now to create the cluster. The initdb tool is what you need.

You will find documentation here.

It might be confusing because when you're under a debian based distros, apt and perl wrappers do every steps for you.


ORA 01031 Insufficient privileges on GRANT SELECT on ALL_CATALOG to a user as system user

To allow the user system to grant privileges on objects it doesn't own, use
GRANT ALL PRIVILEGES TO system WITH GRANT OPTION


Error in update. I did not find the error. Why is max(C) not found?

You just need a select within your update:

update z
set b = c / (SELECT max(c) FROM z) * 100;

Check it at db<>fiddle here

max(c) without more context is not part of the SQL standard. However, you can always use a SELECT returning a single row with a single column (of the appropriate type) where a value is expected.

That's what you'll get:

SELECT
    *
FROM
    z;

  a |      b |     c
 -: | -----: | ----:
  1 |  25.00 | 20.00
  2 |  12.50 | 10.00
  3 |  50.00 | 40.00
  4 | 100.00 | 80.00

And this works not only in PostgreSQL. See SQL Server, or MariaDB, or Oracle, or SQLite, or DB2


Best configuration of my.cnf for make fast Magento navigation

innodb_buffer_pool_size = 5G --> change to 70% of available RAM (after excluding other apps)

tmp_table_size = 2G --> only 500M

But... Neither of these is likely to solve the problem you pose.

You "can't configure away a performance problem".  Instead, you must provide the slow queries and the schema (SHOW CREATE TABLE).  The solution may be as simple as adding a suitable composite index.


Overhead of triggers and foreign key constraints?

Are the triggers/foreign key constraints a good idea ...

Short answer: Yes.

  ... even if I am very confident that my code will never attempt to violate them?

Two things:

Firstly, it's not always going to be "your" code.  Times change, people move on, stuff happens.  If someone else comes along and "inherits" your code base, you cannot be "very confident" that they will do as good a job as you.  Constraints in the database will protect them from themselves.

Secondly, What's to stop someone hooking up their favorite sqlite client and poking around in the database directly?  If all the data integrity is done in the Application, then going "under the radar", into the database directly, gets around that and allows someone to fill the database with complete rubbish, potentially breaking the Application in the process.  Again, constraints in the database will protect them from themselves.

  Where can I get information on their overhead?

It's called Testing.

Whilst you can get general advice about such things, you won't really know how it affects your database until you try it out in your [test] database.

Benchmark your Tests with and without the constraints.  See if the difference, if any, is acceptable [to you and your users].


Typical implementations that I'm familiar with, dealing with PHI (protected health information), which has stricter rules than simple PII, use these technical components:

Data-at-rest encryption, which includes either database-level encryption where supported by the DBMS or disk-level encryption, as well as backup and archived transaction log encryption.
Data-in-transit encryption, which essentially means TLS or IPSec for all network links.
Encryption key management system.
Row- and column-level access control. This should be implemented at the database level if the DBMS is capable of it, or in the application layer otherwise.
Network access control through explicit whitelisting (all network paths and ports are locked down by default and only open where needed).
Security monitoring of network traffic, OS and database logs for unusual access patterns.
Audit logging of all access paths, both at the database level (using database wire protocol capture if the DBMS has no native audit facility) and at the application level.
Secure erasure of data beyond its retention period.

This should cover most compliance requirements when implemented properly and augmented by the necessary organizational measures (identity and access management, separation of duties etc.)

The corollary of the above is that MySQL/MariaDB may not be a good choice for your use case.


Is there any way to trace the optimizer's work in Oracle?

Yes! This is called a 10053 trace in Oracle parlance.  It's more formally known as "Tracing of Optimizer Computations".

Check out MOS doc 225598.1, or Jonathon Lewis' blog post here: https://jonathanlewis.wordpress.com/2014/05/23/10053-trace/

There's loads of hits for "10053 trace" so if you can't find what you need, it's probably out there in search-land.


How to differentiate between the result sets retrieved for multiple OR conditions?

Typically I recommend materialising your resultset when this scenario has come up. Since queries of this nature are usually pretty broad and analytical (read: non-performant), this approach has the benefit of

reducing editing requirements (do you want to add the line to both the select and where clauses for each evaluation?)
snapshotting the underlying data for debugging
capturing "missed" rows from the underlying data that you thought you should have captured

Avoid:

Select
    if(col1 like '%text1%',TRUE,FALSE) as a,
    if(col1 like '%text2%',TRUE,FALSE) as b,
    col1
from table
where col1 like '%text1%'
   or col1 like '%text2%'

Prefer:

create temporary table foo as (
    Select
        if(col1 like '%text1%',TRUE,FALSE) as a,
        if(col1 like '%text2%',TRUE,FALSE) as b,
        col1
    from table
);

select *
from foo
where a
   or b;

Play with a demo of this approach on dbfiddle


Optimize index on a 2,135,044,521 row table

You might be able to reduce PAGEIOLATCH_SH waits for this query if you're able to change the data types generated by the ORM. The Timestamp column in your table has a data type of DATETIME but the parameters @p__linq__1 and @p__linq__2 have data types of DATETIME2(7). That difference is why the query plan for the ORM queries is so much more complicated than the first query plan that you posted that had hardcoded search filters. You can get a hint of this in the XML as well:

<ScalarOperator ScalarString="GetRangeWithMismatchedTypes([@p__linq__1],NULL,(22))">

As is, with the ORM query you cannot get any partition elimination. You will get at least a few logical reads for every partition that's defined in the partition function, even if you're just searching for a day of data. Within each partition you get an index seek so it doesn't take long for SQL Server to move on to the next partition, but perhaps all of that IO is adding up.

I did a simple reproduction to be sure. There are 11 partitions defined within the partition function. For this query:

DECLARE @p__linq__0 bigint = 2000;
DECLARE @p__linq__1 datetime2(7) = '20180103';
DECLARE @p__linq__2 datetime2(7) = '20180104';

SELECT 1 AS [C1]
, [Extent1].[Timestamp] AS [Timestamp]
, [Extent1].[Value1] AS [Value1]
FROM [DemoUnitData] AS [Extent1]
WHERE ([Extent1].[UnitID] = @p__linq__0)
AND ([Extent1].[Timestamp] >= @p__linq__1)
AND ([Extent1].[Timestamp] < @p__linq__2)
OPTION (MAXDOP 1) ;

Here's what IO looks like:

  Table 'DemoUnitData'. Scan count 11, logical reads 40

When I fix the data types:

DECLARE @p__linq__0 bigint = 2000;
DECLARE @p__linq__1 datetime = '20180103';
DECLARE @p__linq__2 datetime = '20180104';

SELECT 1 AS [C1]
, [Extent1].[Timestamp] AS [Timestamp]
, [Extent1].[Value1] AS [Value1]
FROM [DemoUnitData] AS [Extent1]
WHERE ([Extent1].[UnitID] = @p__linq__0)
AND ([Extent1].[Timestamp] >= @p__linq__1)
AND ([Extent1].[Timestamp] < @p__linq__2)
OPTION (MAXDOP 1) ;

IO is reduced as a result of partition elimination:

  Table 'DemoUnitData'. Scan count 2, logical reads 8


I think I would build a table with a queue of some sorts, and have a job poll that table and pick up any work it has to perform.

So for example your trigger writes a record id in a table called processqueue, then you have a job that picks up all the records from the processqueue table and do it's heavy lifting for each and every record, removing the record from the queue once finished.

Schedule that job to run according to latency requirements


Trouble setting up Postgres for the first time

The next thing to check is probably that you have a line in your postgresql.conf that looks like this:

listen_addresses = '*'

You will have to restart after changing that line. Otherwise your pg_hba.conf looks fine.

Good luck! Getting started is a big hurdle, but once you get through it, you will probably find that compared to MySQL things are a lot simpler and more consistent.


Mirroring SQL error log - Error: 8474, State: 11

The mirror state is likely confused at this point and should not be trusted. cherish.j.wang posted the following at: http://www.sqlservercentral.com/Forums/Topic1237273-1549-1.aspx

use master
GO
alter endpoint endpoint_name state = stopped;
GO
alter endpoint endpoint_name state = started;
GO

In that same discussion Robert Davis (SQL Soldier) agreed that would resolve the problem for the moment.  But he also pointed out that if you use the fully qualified domain name of the servers you are less likely to run into a name resolution error.

UPDATE: When you reset the endpoints, it will take the database offline for the duration of the restart.  However, it will not break the mirror.  The mirroring will stop running when the endpoint is stopped, but it will resume once the endpoint is again started.

The MSDN article: http://support.microsoft.com/kb/2490051 suggests.

Run these commands on the primary
If it does not resolve the problem, run the commands on the mirror.
If the database becomes SUSPENDED run ALTER DATABASE <Database Name> SET PARTNER RESUME


Parsing Typed XML with a default namespace and inconsistent root node

The three problems with your XQuery that I can see are (and none of this has anything to do with it being Typed XML):

You are not specifying the correct path to the /root node. It should be:

SELECT t.c.value('(.)[1]','varchar(50)') as type
from @ixml.nodes('/WebService/NewLeads/OutputSchema/root/item/transaction') as t(c)

XML is case-sensitive so you need to use an upper-case "T" for the "Transaction" node:

SELECT t.c.value('(.)[1]','varchar(50)') as type
from @ixml.nodes('/WebService/NewLeads/OutputSchema/root/item/Transaction') as t(c)

While those fixes would probably get you 1 value back (in this case, it should be "SaleOnly"), it won't iterate through the "item" nodes because you were too specific in the path supplied to the .nodes() function. Instead, the final node of that specification should be "item", which is what you want to iterate through. And in that case, you move the "Transaction" part up to the .value() function:

SELECT t.c.value('(./Transaction)[1]','varchar(50)') as type
from @ixml.nodes('/WebService/NewLeads/OutputSchema/root/item') as t(c)

Regarding the statement of:

  I work on xml both missing and containing second xmlns declaration in root element, as well as with structure trimmed down to just root.

The "structure trimmed down to just root" should be handleable by removing everything between the first / and the / right before /root... (i.e. the WebService/NewLeads/OutputSchema). So the resulting path would be:

from @ixml.nodes('//root/item') as t(c)

NOTE:
I am not able to get this to work 100% with the namespace declared in the <WebService> element (please see additional notes as this is no longer the case). Taking that out makes it work. Giving it a prefix, such as xmlns:something="http://www.orbis-software.com/WebSvcCon" makes it work, but then that needs to be declared in the methods.  The only way I can get it to work right now is by declaring the default namespace in each XML function (.nodes and .value) as follows:

SELECT t.c.value('declare default element namespace "http://www.orbis-software.com/WebSvcCon";
                  (./Transaction)[1]','varchar(50)') as [type]
from @ixml.nodes('declare default element namespace "http://www.orbis-software.com/WebSvcCon";
                  /WebService/NewLeads/OutputSchema/root/item') as t(c)

NOTE 2:
Even better, you can use WITH XMLNAMESPACES to declare one or more namespaces to use for the entire query, so no need to define in each XML function. The following both work:

;WITH XMLNAMESPACES (DEFAULT 'http://www.orbis-software.com/WebSvcCon')
SELECT t.c.value('(./Transaction)[1]','varchar(50)') as [type]
from @ixml.nodes('/WebService/NewLeads/OutputSchema/root/item') as t(c)

;WITH XMLNAMESPACES (DEFAULT 'http://www.orbis-software.com/WebSvcCon')
SELECT t.c.value('(./Transaction)[1]','varchar(50)') as [type]
from @ixml.nodes('//root/item') as t(c)

However, just keep in mind that if the document is missing the <WebService xmlns="http://www.orbis-software.com/WebSvcCon"> element and hence has no default namespace, then you need to remove the ;WITH XMLNAMESPACES part. Of course, if the <root> element has its own default namespace, then maybe you will need to keep it. You can play around with it until it works, now that you know the connection between these pieces.

NOTE 3:
If you do end up having two default namespaces declared -- one in the <WebService> element and one in the <root> element -- then you need to specify the URI noted in <root xmlns="bob"> and the // syntax instead of the fully-qualified path.  So if your XML looked like:

<WebService xmlns="http://www.orbis-software.com/WebSvcCon">
 <NewLeads>
  <OutputSchema>
  <root xmlns="http://someplace" type="array">

You would then use:

;WITH XMLNAMESPACES (DEFAULT 'http://someplace')
SELECT t.c.value('(./Transaction)[1]','varchar(50)') as [type]
from @ixml.nodes('//root/item') as t(c)

But that won't help if you do have the <WebService> element and yet the <root> element is missing the xmlns declaration. In that case you still need to specify the namespace noted in the <WebService> element. Fun, fun, fun :-).

NOTE 4:
Even more better: incorporating something mentioned in @wBob's answer, we can actually get rid of the ;WITH XMLNAMESPACES clause, and instead use a namespace wildcard. You just need to prefix every node of every XML function with *:. Now the query should look as follows:

SELECT t.c.value('(./*:Transaction)[1]','varchar(50)') AS [type],
       t.c.value('(./*:SaleProperty/*:PostDistrict)[1]','varchar(50)') AS [PostDistrict]
FROM  @ixml.nodes('//*:root/*:item') t(c);

Doing this means that the query works in all of your scenarios:

Full structure starting with "WebService" node, second xmlns declaration:

<WebService xmlns="http://www.orbis-software.com/WebSvcCon">
  <NewLeads>
    <OutputSchema>
      <root xmlns="uri" type="array">

Full structure starting with "WebService" node, single xmlns declaration:

<WebService xmlns="http://www.orbis-software.com/WebSvcCon">
  <NewLeads>
    <OutputSchema>
      <root type="array">

Trimmed down structure starting with "root" node, single xmlns declaration:

<root xmlns="uri" type="array">


We bumped the memory from 28GB (the original amount) to 40GB, leaving 8GB of memory for the OS and other processes. Immediately afterward everything returned to normal and has stayed stable. One of our DBAs speculated that SQL Server was confused about how much memory it really had available. I had checked the Total Server Memory both before and after and the numbers were consistent with I see in Server Properties but I find that assertion difficult to argue against


One Non-Clustered Index that covers every possible combination of predicates?

Multiple aspects needs to be considered when creating an index. An index on columns A,B is not the same as an index on B,A. the performance of a query which filters on both column A and B will be different depending on the order of the columns in the index.

Indexes slow down DML operations and increase the size of the database.

You can refer to the stackoverflow link below for more details.
https://stackoverflow.com/questions/5447987/why-cant-i-simply-add-an-index-that-includes-all-columns


No.

The words Secure Files should be interpreted that files can safely be stored in the database. This is often safer than storing them on the FileSystem with a reference from the database. If the file is in the database it is also protected by many of the database features. SecureFiles is the modern implementation of LOB's.
The new implementation has a great performance boost compared to the old inplementation.

dbfs is a fileystem that can be created inside the database and that can be mounted on Linux using dbfs_client. Regular operating system processes see it as a regular POSIX filesystem. The LOB's in a dbfs should be implemented as SecureFiles to have the best performance. The performance of dbfs is better than that of NFS, if using filesystem like logging. With normal logging dbfs has about the same performance as NFS, with the added possibility of replicating the data to standby databases.


SQL Server - How is a row updated under the hood?

I'm not a replication guru, but record may be marked as deleted only in case when an update is generating Page Split.

For the case of replication you can test it by looking at DBCC PAGE and look through transaction log.


"Scan interface error" when connecting to a DB2 database restored on a different server

Do you know which fix pack both versions were on? I found this APAR list which mentions the error if you move from FP1 back to the GA release of 10.1. It looks from what I'm seeing elsewhere on the web that this can result if you restore from a newer version of DB2 to an older version (possibly even fix pack-wise) such as the link above shows. Check to make sure both are at the same FP level to rule out the problem.


You can rewrite this using JOINs rather than subselects to get what should be the correct answer.

This is assuming you are aiming to set the @id in this query and carry it forward to another query.

In this situation, the LIKE is only useful if you have a case sensitive collation. If you have case insensitive then you can just use =.

 DECLARE @id INT;
  DECLARE @empId INT = 100;
  DECLARE @type VARCHAR (50) = 'Contractor';
  DECLARE @family VARCHAR (50) = 'Domain-EU';

  SELECT TOP 1 @id = et.typeid
  FROM  EmployeeMap AS em
  INNER JOIN EmpType AS et ON et.typeId = em.typeId
  INNER JOIN FamilyTree AS ft ON ft.familyId = em.familyId
  WHERE empId = @empId
    AND type LIKE @type
    AND familyName LIKE @family;


Why is PostgreSQL very slow when I execute a SELECT query with OR in WHERE clause?

This is a common issue - PostgreSQL planner cannot use indexes (it's not able to optimize OR, when OR is used for subselects) and rewriting with UNION helps. Last year Tom Lane wrote a patch, but as far as I know, this patch was not merged yet.


Oracle data transfer rate determined by rows instead of data size

More likely limited by the fetch size, which is a client side setting.

For example: https://xzwang.wordpress.com/2013/06/20/oracle-ole-db-performance/

Quoting some content of the blog post:

  Batch size controls how many rows SSIS retrieves with each “batch”.
  After retrieving all the rows in a batch, SSIS signals to Oracle that
  it is ready for another batch.   Of course, network latency is
  introduced during this signaling process.  Thus, decreasing the number
  of total batches also decreases the number of round-trips SSIS has to
  make.  The default batch size is only 100;  increasing this to a
  reasonable number should boost performance.

And:

https://scottadamsbi.wordpress.com/2010/11/02/sql-server-analysis-services-and-oracle-ole-db/

  If you are experiencing the same or similar issue, then you may wish
  to experiment with different settings for the FetchSize parameter on
  the Oracle OLE DB connection string e.g. Data Source=ORACLE.WORLD;User
  ID=User;FetchSize=100000;. The default setting is FetchSize=100. This
  means that the Oracle OLE DB provider will request and fetch 100 rows
  of data. SQL Server will then read 100 rows of data. Once all 100 rows
  have been read, the Oracle provider will request another 100 rows of
  data. This will continue untill all rows are read.


Saving a SQL Server database to a file and opening it on another server under any database name

Backup and restore are the simplest/safest ways to do this. There are a couple of things that you need to watch out for, or you'll get errors like the one you're seeing now.

SQL Server will default to restoring the files to their original physical locations. If you're restoring to a different server, or a different database name, these paths probably aren't correct. You will have to specify the correct new database name, and file locations (WITH MOVE...).
If you're overwriting an existing database, you'll need to tell SQL Server that you specifically intend to do this (WITH REPLACE). This is a safety feature; don't get into the habit of blindly using this option unless you know you need it.

The restore GUI tool in Management Studio supports both of these options.

To specify the name of the database you want to restore as, go to the "General" page, and enter the correct name in the "Database" field (under the "Destination" subhead).

To specify where you want the database files to be restored to, go to the "Files" page, and specify the new paths in the "Restore As" column.

If you're overwriting an existing database, you'll need to go to the "Options" tab and check the "Overwrite the existing database" option.

If this is a procedure you plan to repeat regularly, you can generate a script for the RESTORE command by clicking the "Script" button on the Restore Database window toolbar.


What is the name of this property in (SQL?) Databases?

If that value is unique and is used to identify a single row in a table ( in your example to uniquely identify a single client record) it would typically be considered a unique or primary key.

If it has some meaning and you already have a separate primary key it could could also be described as a business key or natural key.

Without a constraint to enforce uniqueness you cant guarantee the data is unique.  You should be careful to avoid developing with that as an assumption.


Ordering rows based on cross reference between columns

You can do this with a hierarchical recursive CTE.

The only problem is that you have a loop in your data, due to there being 3,3,4,4 and 4,4, 3,3.

drop table #tmp;

create table #tmp
(
lat1 int,
lng1 int,
lat2 int,
lng2 int
);

insert into #tmp values (1,1,2,2);
insert into #tmp values (2,2,5,5);
insert into #tmp values (3,3,4,4);
insert into #tmp values (4,4,3,3);
insert into #tmp values (5,5,4,4);

WITH cte AS
(
  SELECT
   lat1,lng1,lat2,lng2,
    0 AS level, cast('('+cast(lat1 as varchar) + ',' + cast(lng1 as varchar)+')' as varchar) as path
  FROM #tmp
  WHERE lat1=1 and lng1=1
  UNION ALL
  SELECT
  t.lat1,t.lng1,t.lat2,t.lng2,
  level+1, cast(path+','+'('+cast(t.lat2 as varchar) + ',' + cast(t.lng2 as varchar)+')' as varchar)
  FROM #tmp t
  INNER JOIN cte c
    ON c.lat2 = t.lat1 and c.lng2 = t.lng1
)
select * from cte;
;

The WHERE lat1=1 and lng1=1 part specifies the base of the hierarchy (which is probably the start of a journey, in this case).


sp_BlitzIndex from Brent Ozar will help you find and identify those:

https://www.brentozar.com/blitzindex/sp_blitzindex-duplicate-indexes/

After that You will have to check those indexes on your own and decide which of them are obsolete. This isn't a task I would try automating with any kind of script.


Why is this PostgreSQL not null constraint not catching this insert?

As the check constraint with the regex will only validate non-null values, I would split this up in three different check constraints:

create table application_user(
    -- other columns, primary key, etc.
    phone text,
    email text,
    constraint exactly_one check (num_nonnulls(phone, email)=1 ),
    constraint valid_email check (email ~ '(?:[a-z0-9!#$%&''*+/=?^_`{|}~-]+(?:\.[a-z0-9!#$%&''*+/=?^_`{|}~-]+)*|"(?:[\x01-\x08\x0b\x0c\x0e-\x1f\x21\x23-\x5b\x5d-\x7f]|\\[\x01-\x09\x0b\x0c\x0e-\x7f])*")@(?:(?:[a-z0-9](?:[a-z0-9-]*[a-z0-9])?\.)+[a-z0-9](?:[a-z0-9-]*[a-z0-9])?|\[(?:(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.){3}(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?|[a-z0-9-]*[a-z0-9]:(?:[\x01-\x08\x0b\x0c\x0e-\x1f\x21-\x5a\x53-\x7f]|\\[\x01-\x09\x0b\x0c\x0e-\x7f])+)\])'::text),
    constraint valid_phone check (phone ~ '\d{10}'::text)
);

check (num_nonnulls(phone, email)=1) only validates that either one of the column is specified. The other two only check the format of the respective column. This - I think - makes things a bit easier to understand.


Quote from the manual

  The RENAME form changes the name of the index (...) There is no effect on the stored data

(emphasis mine)

So yes, this is a simple update to the system catalog.

The statement sill needs an exclusive lock on the table if I'm not mistaken, but the lock will only be held for a very short period.


Dumping stored procedure result to a text file programmatically

The simplest method would be a 2 step approach :

Log the output of stored procedure to a physical table in the sql server database. e.g. insert into dbo.X_sp_name_out exec dbo.sp_name
Use BCP OUT or SQLCMD to export data to text file.
(Optional.. but important to manage table growth) Have a purging process to purge out the old records or before next execution of the SP, truncate the table.

Make sure you use N'your unicode data' when storing data.

You can think of using SSIS to do the same and schedule the package using SQL Agent job.


How to insert into view through GUI in pgAdmin?

Sorry, no luck with pgAdmin. Per documentation:

  Note that views can’t be edited and are read only by design; updatable
  views (using rules) are not supported at this time.

There is an item in the project's TODO list:

  Updateable View wizard

Seems like nobody tackled that, yet. (Contributors welcome.)
There have been requests to introduce this feature in the past. You could email pgadmin-support@postgresql.org to document popular demand ...

For now, it seems like Navicat supports it.


You can declare table variable with fields fully equal to SP output and save results to it. E.g.:

    DECLARE @who_results TABLE
    ([run_date] DATETIME,
     ...
    )
    INSERT INTO @who_results EXEC sp_blitzwho

After that, you can do everything you want with data in table variable.


SQL Deadlocks - Same Stored Procedure Called Concurrently

Is there a good method or pattern to have the 2nd call wait until the 1st is finished before continuing with its lookups?

The easiest way to have a block of code that can be run by only one session at a time is to use an Application Lock.  You can use SQL Server's locking engine, but instead of locking a specific row, page, or table, you create a lock with a custom name.  eg

begin transaction;

--begin exclusive section
exec sp_getapplock @Resource = 'Get_Data Exclusive Lock', @LockMode = 'Exclusive';

--do stuff in only one session

--end exclusive section
exec sp_releaseapplock @Resource = 'Get_Data Exclusive Lock';

commit transaction;


Monstrous query hanging on "copying to tmp table" in MySQL

Wow, that is a doozie of a query. At this point it has little to do with your configuration variables and you should focus on how to optimize the query.

It is tough to give you a magic bullet, though, without knowing what question you are trying to determine from this monstrous query. At first glance, it appears it is trying to do too much.

I would start by removing these two problems from your EXPLAIN plan:

1       PRIMARY <derived2>      ALL     NULL    NULL    NULL    NULL    397520  Using temporary; Using filesort
2       DERIVED <derived3>      ALL     NULL    NULL    NULL    NULL    397520  Using temporary; Using filesort

The first I think is the query for 'order_stats' derived table, the second I am assuming is the 'main' derived table, though it might be flipped. Regardless, I would start at the 'order_stats' (leave a comment with the pastebin of the explain and running time of non-explain, please).

Depending on what this query is trying to accomplish, I would recommend storing the results in a temporary table to be used in the join. One question I would like to know from your developer is, what is the english of the package_tickets and order_tickets computed columns. And if it could be accomplished another way if it turns out to be the bottleneck.


The quick and dirty way to show totals is to add a Totals row to any Datasheet View of a table or query.  First open the Datasheet, then on the Home ribbon (i.e. toolbar tab) click the Totals button (with the sigma sign).  A Total row with empty cells appears at the bottom of the data rows .  Clicking on a cell reveals a drop-down list with options for various aggregate functions, include sum.  See this documentation page.

A more advanced way to get totals is to create an aggregate query.  Within the standard Query Design View, click the Totals button on the query Design ribbon.  This enables and shows a Total row in the design grid.  For each column of the query, Totals options can be selected including "Group By" and various aggregate functions.  The same query can also be edited in the SQL view which will show a GROUP BY clause and various aggregate functions in the column SELECT list.  See this documentation page for further details.

Asking questions here is fine, but especially for beginning topics as this, the web contains many tutorial pages with much more detail, just as I linked to.  All I did was search for "Access sum fields" and "Access query total" to find those pages.  I suggest doing those same searches so that you can see various other resources and tutorials.


Can't read config files from pgAdminIII on local Kubuntu machine

pg_hba and postgresql.conf should stay under

/etc/postgresql/[pg_version]/main/

then you should link them to /var/lib.. with:

#:ln -s /etc/postgresql/[pg_version]/main/pg_hba.conf
/var/lib/postgresql/[pg_version]/main/pg_hba.conf


System disk run out of space when running heavy SQL queries on SQL Server 2012

SSMS query results cache to the C: drive by default. Go to Tool \ Options. See attached. Change this to another volume with more storage and you should be fine.


Query about MySQL Semaphores in Engine Status

Semaphores have to do with waiting for internal thread locks in the OS

You can find a nice discussion in Peter Zaitsev's SHOW INNODB STATUS walk through

The way I see it, this is related to two things

innodb_concurrency_tickets
innodb_thread_concurrency

My guess is that you set innodb_thread_concurrency to a nonzero number. This causes the InnoDB storage engine to throttle its use of internal threads. Consequently, this will cause transactions to go sit in the queue.

Way back in 2011, I wrote posts about this

Sep 12, 2011 : Possible to make MySQL use more than one core?
Aug 16, 2011 : Why do MySQL threads often show "freeing items" status when the query cache is disabled?
May 26, 2011 : About single threaded versus multithreaded databases performance

I went to Percona Live NYC in 2011. Ronald Bradford told me during a workshop to leave innodb_thread_concurrency at zero(0). Why ?

As I mentioned in the Aug 16, 2011 post:

  He plainly told me that I should never set values against innodb_thread_concurrency. Let it always be the default value, which is now zero(0). By doing so, you let the InnoDB storage decide how many innodb_concurrency_tickets to generate on its own. This is what infinite concurrency does.

If you have innodb_thread_concurrency as a nonzero value, please set it to 0 or remove it from my.cnf. Please note that MySQL 5.6 allows for 5000 tickets while MySQL 5.5 only allows 500.

If you want to change these values, you can do so dynamically with

SET GLOBAL innodb_thread_concurrency = 0;

I would recommend not setting innodb_concurrency_tickets at all.

I would also recommend reading Pythian's ONCE AGAIN ABOUT INNODB-CONCURRENCY-TICKETS


JOIN correctly no matter which end is linked

An OR appears to work fine in your fiddle example.

SELECT
  st.Submitting_User,
  COUNT(l.Link_ID) AS [Count]
FROM
  SupportTicket st
  JOIN Link l
    ON st.SupportTicket_ID = l.Entity_ID1
    OR st.SupportTicket_ID = l.Entity_ID2
WHERE
  l.Link_ID LIKE 'CAS%'
  AND st.Type = 'Tech Support'
  AND st.Date_Submitted BETWEEN '20140201' AND CURRENT_TIMESTAMP
GROUP BY
  st.Submitting_User, st.Submitting_Department
ORDER BY
  st.Submitting_User


Once you start stepping into areas like hierarchies, you probably want to know your RDBMS to some extent.  If your RDBMS supports recursive queries natively your hierarchies will look very different then if each iteration is a round trip to the db.

This being said there are a few warnings that come to my mind looking at your schema.  I believe these are likely to impact both performance and data integrity.

The separate parents table strikes me as somewhat dangerous.  What are the rules for parents and children?  Can you have blog -> menu_item -> blog -> comment -> menu_item?  This first creates the possibility of problems that may be difficult to track down, but also consider that if the rules are based on smaller tables, actually enforcing the rules becomes possible  If your menu items are broken off, a caching layer becomes a possibility. Same with other things that are less frequently changed, and the amount of effort to generate the menu on the part of the db will be reduced.
I actually think your second model, if things like menu items, etc. are broken off is likely to be both more scalable and faster.  I would still suggest breaking apart comments and blog posts, and tracking the relationships there more closely.   If you are worried about scalability and your site has 10000 blog posts and 1000000 comments, being able to filter the comments also by the attached blog post is likely to mean you can get to use better indexes at least on some db's.

I am reminded of the phrase "premature optimization is the root of all evil."  Design a good db schema first and then worry about speed and scalability.


Dynamic SQL is not allowed error for creating function in MySQL

You want to use a stored procedure instead of a stored function

You can rewrite it like this

DELIMITER $$
CREATE PROCEDURE hierarchy_connect_by_parent_eq_prior_id(IN value INT, IN reqdTable VARCHAR(50), OUT result INT)
BEGIN
    DECLARE _id INT;
    DECLARE _parent INT;
    DECLARE _next INT;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET @id = NULL;
    SET _parent = @id;
    SET _id = -1;
    SET @table_name = reqdTable;
    IF @id IS NULL THEN
        SET result =  NULL;
    END IF;
    LOOP

        SET @sql_text = concat('SELECT MIN(id) INTO @id FROM ', @table_name,' WHERE parent_id = _parent AND id > _id;');
        PREPARE stmt FROM @sql_text;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;

        IF @id IS NOT NULL OR _parent = @start_with THEN
          SET @level = @level + 1;
          SET result = @id;
        END IF;
        SET @level := @level - 1;

        SET @sql_text = concat('SELECT id, parent_id INTO _id, _parent FROM ', @table_name,' WHERE id = _parent;');
        PREPARE stmt FROM @sql_text;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;

    END LOOP;
END$$
DELIMITER ;

Then use it

CALL hierarchy_connect_by_parent_eq_prior_id(1, 'table1', @result);
SELECT @result;


How to improve query to include 2 critical pieces of employee data

Without the schema this is a little difficult to test but I think this will work. Even if I do make some syntax errors you will be able to see what I am getting at.

SELECT
    PayrollpersonID,
    PayrollDeductionYear,
    SUM(CASE WHEN PayrollDeductionMonth = 'January' THEN Variance ELSE 0 END) AS January,
    MAX(CASE WHEN (PayrollDeductionMonth = 'January' AND PayrollDeductionAmount = 0 AND invoice > 0) THEN 1 ELSE 0 END) AS 'January No Deduction',
    MAX(CASE WHEN (PayrollDeductionMonth = 'January' AND PayrollDeductionAmount > 0 AND invoice = 0) THEN 1 ELSE 0 END) AS 'January No Invoice',
    SUM(CASE WHEN PayrollDeductionMonth = 'February' THEN Variance ELSE 0 END) AS February,
    MAX(CASE WHEN (PayrollDeductionMonth = 'February' AND PayrollDeductionAmount = 0 AND invoice > 0) THEN 1 ELSE 0 END) AS 'February No Deduction',
    MAX(CASE WHEN (PayrollDeductionMonth = 'February' AND PayrollDeductionAmount > 0 AND invoice = 0) THEN 1 ELSE 0 END) AS 'February No Invoice',
    SUM(CASE WHEN PayrollDeductionMonth = 'March' THEN Variance ELSE 0 END) AS March,
    MAX(CASE WHEN (PayrollDeductionMonth = 'March' AND PayrollDeductionAmount = 0 AND invoice > 0) THEN 1 ELSE 0 END) AS 'March No Deduction',
    MAX(CASE WHEN (PayrollDeductionMonth = 'March' AND PayrollDeductionAmount > 0 AND invoice = 0) THEN 1 ELSE 0 END) AS 'March No Invoice',
    SUM(CASE WHEN PayrollDeductionMonth = 'April' THEN Variance ELSE 0 END) AS April,
    MAX(CASE WHEN (PayrollDeductionMonth = 'April' AND PayrollDeductionAmount = 0 AND invoice > 0) THEN 1 ELSE 0 END) AS 'April No Deduction',
    MAX(CASE WHEN (PayrollDeductionMonth = 'April' AND PayrollDeductionAmount > 0 AND invoice = 0) THEN 1 ELSE 0 END) AS 'April No Invoice',
    SUM(CASE WHEN PayrollDeductionMonth = 'May' THEN Variance ELSE 0 END) AS May,
    MAX(CASE WHEN (PayrollDeductionMonth = 'May' AND PayrollDeductionAmount = 0 AND invoice > 0) THEN 1 ELSE 0 END) AS 'May No Deduction',
    MAX(CASE WHEN (PayrollDeductionMonth = 'May' AND PayrollDeductionAmount > 0 AND invoice = 0) THEN 1 ELSE 0 END) AS 'May No Invoice',
    SUM(CASE WHEN PayrollDeductionMonth = 'June' THEN Variance ELSE 0 END) AS June,
    MAX(CASE WHEN (PayrollDeductionMonth = 'June' AND PayrollDeductionAmount = 0 AND invoice > 0) THEN 1 ELSE 0 END) AS 'June No Deduction',
    MAX(CASE WHEN (PayrollDeductionMonth = 'June' AND PayrollDeductionAmount > 0 AND invoice = 0) THEN 1 ELSE 0 END) AS 'June No Invoice',
    SUM(CASE WHEN PayrollDeductionMonth = 'July' THEN Variance ELSE 0 END) AS July,
    MAX(CASE WHEN (PayrollDeductionMonth = 'July' AND PayrollDeductionAmount = 0 AND invoice > 0) THEN 1 ELSE 0 END) AS 'July No Deduction',
    MAX(CASE WHEN (PayrollDeductionMonth = 'July' AND PayrollDeductionAmount > 0 AND invoice = 0) THEN 1 ELSE 0 END) AS 'July No Invoice',
    SUM(CASE WHEN PayrollDeductionMonth = 'August' THEN Variance ELSE 0 END) AS August,
    MAX(CASE WHEN (PayrollDeductionMonth = 'August' AND PayrollDeductionAmount = 0 AND invoice > 0) THEN 1 ELSE 0 END) AS 'August No Deduction',
    MAX(CASE WHEN (PayrollDeductionMonth = 'August' AND PayrollDeductionAmount > 0 AND invoice = 0) THEN 1 ELSE 0 END) AS 'August No Invoice',
    SUM(CASE WHEN PayrollDeductionMonth = 'September' THEN Variance ELSE 0 END) AS September,
    MAX(CASE WHEN (PayrollDeductionMonth = 'September' AND PayrollDeductionAmount = 0 AND invoice > 0) THEN 1 ELSE 0 END) AS 'September No Deduction',
    MAX(CASE WHEN (PayrollDeductionMonth = 'September' AND PayrollDeductionAmount > 0 AND invoice = 0) THEN 1 ELSE 0 END) AS 'September No Invoice',
    SUM(CASE WHEN PayrollDeductionMonth = 'October' THEN Variance ELSE 0 END) AS October,
    MAX(CASE WHEN (PayrollDeductionMonth = 'October' AND PayrollDeductionAmount = 0 AND invoice > 0) THEN 1 ELSE 0 END) AS 'October No Deduction',
    MAX(CASE WHEN (PayrollDeductionMonth = 'October' AND PayrollDeductionAmount > 0 AND invoice = 0) THEN 1 ELSE 0 END) AS 'October No Invoice',
    SUM(CASE WHEN PayrollDeductionMonth = 'November' THEN Variance ELSE 0 END) AS November,
    MAX(CASE WHEN (PayrollDeductionMonth = 'November' AND PayrollDeductionAmount = 0 AND invoice > 0) THEN 1 ELSE 0 END) AS 'November No Deduction',
    MAX(CASE WHEN (PayrollDeductionMonth = 'November' AND PayrollDeductionAmount > 0 AND invoice = 0) THEN 1 ELSE 0 END) AS 'November No Invoice',
    SUM(CASE WHEN PayrollDeductionMonth = 'December' THEN Variance ELSE 0 END) AS December,
    MAX(CASE WHEN (PayrollDeductionMonth = 'December' AND PayrollDeductionAmount = 0 AND invoice > 0) THEN 1 ELSE 0 END) AS 'December No Deduction',
    MAX(CASE WHEN (PayrollDeductionMonth = 'December' AND PayrollDeductionAmount > 0 AND invoice = 0) THEN 1 ELSE 0 END) AS 'December No Invoice',
    Total
FROM
    (SELECT
        PayrollpersonID,
        PayrollDeductionAmount,
        i.invoice,
        PayrollDeductionAmount-i.invoice as Variance,
        PayrollDeductionMonth,
        PayrollDeductionYear,
        Total = sum(PayrollDeductionAmount -i.invoice) over (partition by PayrollpersonID)
    FROM
        Employee_Deductions as d,
        (SELECT
        InvoicepersonID,
        sum(isnull(v.InvoiceAmount,0.00)) as invoice,
        InvoiceProcessedMonth,
        InvoiceProcessedYear
        FROM
            Employee_Invoices as v
        WHERE DeductionYear = '2015'
        GROUP BY InvoicepersonID, InvoiceProcessedMonth, InvoiceProcessedYear) as i
    WHERE
        d.PayrollpersonID = i.InvoicepersonID and
        d.PayrollDeductionMonth = i.InvoiceProcessedMonth and
        d.PayrollDeductionYear = i.InvoiceProcessedYear and
        d.PayrollDeductionYear = '2015' and
        d.PayrollDeductionAmount > 0) AS a
GROUP BY PayrollpersonID,
    PayrollDeductionYear,
    Total
ORDER BY PayrollpersonID;

You could have done this by stacking pivot statements but this can be messy and inefficient. This is a different way of pivoting data called the cross tab method. This method is usually more efficient than the pivot statement and easier to read.


Using the result set of a select query as the input of another select

You need to join the 3 tables together into one select rather then use the other two tables as part of a where clause. I have provided an example of joining all 3 together and having a condition for the third table.

SELECT a."Title", a."Num"
FROM   "Table" a,
       "Table2" b,
       "Table3" c
WHERE  a."Id" = b."Id"
AND    a."Id" = c."Id"
AND    c.foo-clause = foo-clause


Can I surveillance the backup taken in SQL Server?

You can create a server level event notification for the Audit Backup/Restore Event Class event. In processing the event you can do whatever you wish, including sending yourself email. See Event Notifications and CREATE EVENT NOTIFICATION. Event notifications can be used to trigger activated procedures.


Initialize replication sql server from backup of many databases

You can't do that.  Initializing from a backup is only appropriate when you want to overwrite the subscriber database.  In your scenario you should probably use regular snapshot initialization, so each subscription will create and load its own tables.


Restore server instance using logical backup and wal files

Yes it is possible the logical backup will be having data before failover say u took it on 3pm it will have complete data till 3pm

And suppose you fail-over the master say on 4 pm the wal-files will have records of changes done from 3pm to 4pm.

So just create a postgresql instance with help of pg_createcluster
and inserted the dump file first.

then copy the wall files to /var/lib/postgresql/9.1/main/pg_xlog/
path which is default path for most postgresql installation.
then finally
restart the services service postgresql restart


During table migration of tables from one tablespace to another, does it needs free space to do the alter table move?

Table move copies the whole table into a new segment, then drops the old segment. This means you need enough space to store the old segment and the new segment at the same time. For a 1 TB table full of data, you need another 1 TB free space for the move to succeed.


Help with a multi-language medical database

No, you should not split this out into different databases. This will cause more issues and complexity further down the line, for reporting cross country, possibly requiring cross database ownership chaining, etc.

You can achieve your requirements by a process of normalization. Many people say they have normalised a database by looking at the required attributes and splitting them into separate entities by "feel". However, proper normalization is a much more formal, structured approach and is applicable for good database design. There is a very good Wikipedia article at http://en.m.wikipedia.org/wiki/Database_normalization and Itzik Ben-Gans book T-SQL Querying also goes into lots of detail with good examples.

Also recommend that you validate your normalization with a full ERD (Entity Relationship Diagram) Your normalization and ERD are also very useful design documents for your projects documentation library.


Will a change from MyISAM to InnoDB require SQL query changes in MySQL?

I don't think you need to change any SQL statements and SP's, They should work as they were before.

You need to keep in mind some points

If you have MySQL version 5.5 or below and you have FULL TEXT index on Table then this is a issue, InnoDB doesn't support FULL TEXT index up to MySQL 5.5, They are supported in MySQL 5.6 and above.
If you have Spatial Indexes on table, They are yet not supported in InnoDB engine

There are a lot of other points that you should keep in mind are, Please must have a look at some great links which suggest what are various things you should consider in migrating table from MyISAM to InnoDB

What are the main differences between InnoDB and MyISAM.?
How do you tune MySQL for a heavy InnoDB workload. ?
Should I move to InnoDB during a planned migration. ?
Online conversion from MyISAM to InnoDB ?
Converting Tables from MyISAM to InnoDB.


I did something like this with MySql and you can save directly CSV files in MySQL. I think Apache Cassandra is a good choice for you

  CQL3 is very similar SQL, but with some limitations that come from    the scalability (most notably: no JOINs, no aggregate functions.)
  CQL3 is now the official interface. Don't look at Thrift, unless    you're working on a legacy app. This way, you can live without
  understanding ColumnFamilies, SuperColumns, etc.
  Querying by key, or key range (secondary indices are also available)
  Tunable trade-offs for distribution and replication (N, R, W)
  Data can have expiration (set on INSERT).
  Writes can be much faster than reads (when reads are disk-bound)
  Map/reduce possible with Apache Hadoop
  All nodes are similar, as opposed to Hadoop/HBase
  Very good and reliable cross-datacenter replication
  Distributed counter datatype.
  You can write triggers in Java.

You see the difference between NoSQL databases here
http://kkovacs.eu/cassandra-vs-mongodb-vs-couchdb-vs-redis


Rename the database name through terminal

Firstly, create the new database with the command;

mysql> CREATE DATABASE studentRecord;

Then transfer the contents from the old database into the new database;

shell$ mysqldump --opt studenttRecord | mysql studentRecord

(add in the username and password bits as appropriate)

The --opt part will add a number of standard flags to the data dump, and I refer you to the MySQl documentation for more information.

Once the transfer is complete, and you have verified it, you can drop your old database.

If your database is particularly huge, this can take some time to perform, so an alternative is to RENAME TABLE for each of the tables in the old database, and move them into the new database.

Create the database as above, then for each table do the following;

mysql> RENAME TABLE studenttRecord.table_name TO studentRecord.table_name;

Replacing table_name with each of your tables in turn (create a script of this in a text editor to help prepare a complete list!).

Hope that helps,
Dave


Redshift: Return rows if value exists in cell array

I will give it a try :

First use this query to find the maximum length of longest array for values field in your SampleTable:

SELECT MAX(JSON_ARRAY_LENGTH(values)) FROM SampleTable;

Then create a view according to the result you get from above query using the below format( I am supposing the max size output as 2 with index starting from 0 as per the sample table data you have provided ):

CREATE VIEW seq_0_to_2 AS (
    SELECT 0 AS i UNION ALL
    SELECT 1 UNION ALL
    SELECT 2
);

Then use the below query to get the desired results:

WITH exploded_array AS (
    SELECT id,name,phone, JSON_EXTRACT_ARRAY_ELEMENT_TEXT(values, seq.i) AS NewExtractedValues
    FROM SampleTable, seq_0_to_2 AS seq
    WHERE seq.i < JSON_ARRAY_LENGTH(values)
  )
SELECT *
FROM exploded_array
where NewExtractedValues = 6;

Hope this helps.

Inspired from https://torsten.io/stdout/expanding-json-arrays-to-rows/


Read Inside SQL Azure:

  SQL Azure provides logical databases
  for application data storage. In
  reality, each subscriber’s data is
  actually stored multiple times,
  replicated across three SQL Server
  databases that are distributed across
  three physical servers in a single
  data center. Many subscribers may
  share the same physical database.

Clustered keys are required so that the three replicas of your data can be kept in sync. W/o  a key, is impossible to know which rows were updated. Heaps (tables w/o a clustered index) have only physical 'keys' (fileid:pageid:slot) and since your 3 replicas of the logical database share physical database with other logical databases the physical address on one server has no meaning on the other replicas, hence heaps could not possibly be replicated.


Well there is no official method of getting the list of previous privileges list of a user or a role. But my suggestion would be doing a Fine-grained auditing on the role granted to the user. Or you can do standard auditing on the role by issuing audit <role_name>; . Here please note that if you are going to audit DBA or SYSDBA roles then set audit_sys_operations parameter to TRUE. Thank you.


InnoDB: will uncommitted transaction produce redo log?

innodb produce redo log during transaction and may sync to disk even if the transaction has not committed.


What function to use with this timestamp?

to_timestamp() expects the parameter to be given in seconds. Your value is in miliseconds. Just divide it by 1000:

SELECT to_timestamp(1462975819.250);

gives 2016-05-11 16:10:19.25+02


SQL Server 2014 SP2 CU10 Patch uninstall stuck at DowntimeTimingConfigAction

So I figured this out, Posting an answer to help out anyone else who is stuck.

When SQL updates a set of scripts is run on the system databases to make sure everything is set up right for the new version of SQL. When you uninstall an update it has to do the same thing but backwards.

In this case the backward scripts for 2014 SP2 CU10 back to base 2014 SP2 do not stop SQL correctly. So you just need to make sure to stop the SQL service first before running the uninstaller.

So to do this downgrade follow these steps for best results:

1. Restart the server, (make sure there are no other windows updates pending)
2. Stop the SQL Server Services
3. Uninstall the SQL Update
4. Start the SQL Server Services (start the 2ndary services first like Agent and SSRS first)
5. SQL will then run the downgrade scripts and will be happy after 5-10 minutes.
6. Restart the server again for good measure.

If your stuck at the place I was the way to get out of it is:

1. Open SQL server configuration manager
2. Open the proprieties of the SQL Server Service
3. Go to the Startup Parameters, add "-902" startup flag
4. Restart the server
5. Wait for the server and SQL to come fully back up
6. Remove the startup flag
7. Restart the SQL Server Service

Then Follow the first set of steps to fully remove the update.


How to set ALL the replication to DEADLOCK_PRIORITY LOW?

After having a good setup and monitoring the tempdb
we opted and used the READ COMMITTED SNAPSHOT as described in the comments.
How to implement Read Committed Snapshot and how it is different to Snapshot has been nicely described (including links explaining how to set it up) here.

In addition, READ_COMMITTED_SNAPSHOT (henceforth called RCSI) will
only give you some properties of optimistic concurrency. RCSI and
Snapshot (SI) differ in three main ways:

RCSI will be used immediately by all RC isolation queries.
RCSI provides a point-in-time (snapshot) view of the database for a single statement.
SI provides a point-in-time view for an entire transaction.
SI can have write conflicts.

also quoted from the same source:

Other useful details relating some gotchas and caveats when using
RCSI/SI can be found in these articles:

Implementing Snapshot or Read Committed Snapshot Isolation in SQL Server: A Guide
Read Committed Snapshot Isolation: Writers Block Writers (RCSI)


Create .monetdb file in the home directory as below:

cat << EOF > $HOME/.monetdb
user=monetdb
password=monetdb
EOF

Replace the above content with your monetdb username and password.
Change the file permission to prevent other user from reading it [optional]
Run:

export msqldump=$HOME/.monetdb
echo 'export msqldump=$HOME/.monetdb' >> $HOME/.bashrc

After making above changes, I have ran the below mysqldump command, it doesn't ask user to input password.

mysqldump -d[database-name] > [sql_dump_backup_path]

Source :How to setup mysqldump without password in cronjob


Having trouble starting my Oracle database

If you are running XE on windows the folder delimiters have to be consistent
Instead of

SPFILE='C:\oraclexe\app\oracle\product\11.2.0\server\dbs/spfileXE.ora'

Use

SPFILE='C:\oraclexe\app\oracle\product\11.2.0\server\dbs\spfileXE.ora'

The best suggestion is from thatJeffSmith. Use the prebuilt VM's from Oracle in their VM product Virtual Box

Also you can see if your Oracle is successfully installed by finding services.msc and looking for a windows service with Oracle in the name.  There should be a listener and a database service. The database service has to be running before you can connect from your windows vm and the listener has to be up to connect from other devices.


Replicating to Older Versions of MS SQL Server

Yes, this still holds true.  It is covered in Upgrade Replicated Databases in the SQL Server 2014 documentation.


Configuring sqlnet.ora and tnsnames.ora on the client side

The difference of oracle instant client and the oracle client installed by Oracle Universal Installer is in how they are installed. The Oracle universal installer maintains a registry which the instant client does not have or use. But the software components are the same independent of the method you use to install. If you use the jdbc oci driver then you use java to access the libraries of the Oracle OCI client which is the well known client that use all the clients that can use a tnsname.ora. These are sqlplus, ODBC program, C-Programs that use the Oracle Precompiler, ... Access with the OCI client can be configured with tnsnames.ora and sqlnet.ora and the well known environment variables (like ORACLE_HOME, ORACLE_SID, TNS_ADMIN,...). In java there is a new way to access the Oracle database by using the jdbc thin client. This is a client completely implemented in java that does not use the oracle OCI libraries but has implemented the necessary oracle net protocols in java to communicate with an Oracle database. This client cannot be influenced by tnsnames.ora, sqlnet.ora or the environment variables. It does not even need these files. It only needs an appropriate jar file that implements the thin client.

If you use the oci client for a program then  in the environment that starts this program the Oracle relevaant variables must be set. So if a program is started by a shell script this shell script should set and export the relevant variables. The directory stored in the  variable TNS_ADMIN is searchd for the tnsnames.ora and sqlnet.ora. If this variable is not set then other directories are searched for these files (or similar files). This depends on the operating system and of the value of ORACLE_HOME, if set. If no appropriate file is found default values are used.

DIAG_ADR_ENABLED=OFF determines if ADR-logging (available since 11.1)  is used or the traditional logging is used. If ADR-logging is not disabled the TRACE_DIRECTORY_CLIENT parameter will be ignored and the sqlnet trace files will be found somewhere in the diag-directories used by ADR-logging.


Simple. It means that your node cannot connect that other node. Check firewall settings between those nodes.

If you go to the command line, telnet node.ip.addr.ess 27107 should work.


In SQL server during Log Shippping which portion (active\Inactive) of the Log file (.ldf) backup is taken and further restored in SQL Server

I have difficulties understanding your question. Perhaps you can clarify or re-prase? Anyhow:

A regular log backup will clear the contents of the ldf file (a bit simplified). Using COPY_ONLY for the log backup will not clear the contents.

The .tuf file is not related to above. When you restore a log backup, you can specify that you can restore further backup, but still be able to SELECT in the database. This is the STANDBY option. SQL Server need to keep track of some things on this scenario, for the next restore, and this is the .tuf file.


I believe you want to mark the trigger NOT FOR REPLICATION.  This will prevent the trigger from firing when the Merge Agent makes the update.  Have a look at All about "Not for Replication".


SHOW PROCESSLIST: show processes from all sessions

Obtain this info from information_schema.PROCESSLIST. If your user have PROCESS privilege, and you need to view only your processes - use WHERE user = USER() filter.

Link


Database design - passenger, driver, wallet. How to handle multiple foreign keys with Id and type fields?

You cannot have that directly, but you can do the following:

Have two foreign key columns driverid and passengerid in wallet that reference the respective tables.
Add a check constraint that force one of the foreign key columns NULL and the other NOT NULL based on usertype.

CHECK (usertype = 'driver'    AND driverid IS NOT NULL AND passengerid IS NULL
    OR usertype = 'passenger' AND driverid IS NULL AND passengerid IS NOT NULL)

If you want, add a view on wallet that uses coalesce to map both foreign key columns to a single one.


Change collation on partition column (SQL Server 2017)

You don't need to drop the table(s).

One option (that I have tested) is:

Drop the partitioned non-clustered index(es); move partitioned clustered indexes, primary keys, and unique constraints to a non-partitioned file group; and unpartition heaps by creating a (temporary) clustered index on a non-partitioned file group. For help identifying which indexes are affected and what action needs to be taken for each one, please see the query I have posted in the "Finding and Fixing Ignored Objects" section of "Changing the Collation of the Instance, the Databases, and All Columns in All User Databases: What Could Possibly Go Wrong?" (which also contains examples of dealing with clustered indexes, contraints, and heaps).
Drop the partition scheme(s)
Drop the partition function(s)
Alter the column definition with the new collation
Drop and recreate the partition function to specify the COLLATE clause with the new collation for the input parameter (assumption here is that you are not changing the default collation of the database, and the input parameter will use the database's default collation for string types unless specified using the COLLATE clause, and the CREATE INDEX statement using this scheme and function will fail if the collation of this parameter does not match the collation of the partitioning column). Meaning, if you change the column's collation to SQL_Latin1_General_CP1_CI_AI but the database is still using SQL_Latin1_General_CP1_CI_AS, then you need to:

CREATE PARTITION FUNCTION PartitionFunc
(
  VARCHAR(36) COLLATE SQL_Latin1_General_CP1_CI_AI
)
...

Now, 40k+ tables is perhaps more than a few, but if they are all using the same partition scheme and function, then this is only 4 extra statements (drop and create for the partition schema and partition function) compared to what you already need to do even if partitioning wasn't being used (i.e. you would need to drop and recreate the indexes no matter what). So, while this does require a lot of I/O as the indexes are dropped and recreated, there is no way around that when changing a column's collation.

I am assuming (and have fingers crossed) that these columns are not:

part of the clustered index
key fields in a primary key or unique index/constraint that is foreign-keyed to

If either of those cases is true then you have a lot more work to do, but that has nothing to do with the partitioning.

Another option (given the desire to not recreate everything) might be to use the undocumented sqlservr.exe -q option to change the collation on ALL user columns and databases on the instance. This approach changes the meta-data for the database, columns, parameters, etc, and then rebuilds the indexes (again, no way around this when changing collations, so with 40k+ tables this will likely take a few minutes (or more) and the database, or the entire instance if you update the collation globally, will be offline during that time).

I just tested this scenario on SQL Server 2019 RC1, and it does not work for partition functions, but that doesn't mean that this approach isn't part of the overall solution. It just means that you really have no choice but to go through the process noted in the section above. The sqlservr.exe -q approach will handle everything else (except for user-defined table types, I believe). There are plenty of restrictions on being able to change a column's collation, and even more on changing the database's default collation (lots of dependencies in both cases), so if you are wanting to have a case-sensitive database now, then likely you will have other obstacles besides just the partitioned columns.

The thing that doesn't work with partition functions is that the collation of the input parameter is stored in sys.partition_parameters. This particular meta-data is not changed with the otherwise-awesome-even-if-undocumented -q option. The effects this has are:

Assuming the data is either VARCHAR but no change of code page, or NVARCHAR, then the data will still be partitioned as it was before. For example, let's say that you have a RANGE LEFT partition function using FOR VALUES ( 'D', 'H', 'O', 'V' ). In this case, partition #2 contains values > D and <= H.

When using the case-insensitive collation of SQL_Latin1_General_CP1_CI_AI, partition #2 would contain E, e, F, f, G, g, H, h. Simple enough, right?
However, when using the case-sensitive collation of SQL_Latin1_General_CP1_CS_AI, partition #2 would contain d, E, e, F, f, G, g, H. This partition now contains lower-case d, and does not contain lower-case h. This is due to most of the SQL Server collations sorting upper-case before lower-case (Windows collations sort lower-case first).

If data is VARCHAR and the code page is different, then things might get interesting. Any characters with values between 128 and 255 can potentially either have a different value and/or sort position, be mapped to a similar character because it is't available on code page of collation used for partition function collation (i.e. "best fit" mapping), or it might be missing on the old code page hence it becomes a "?" and sorts as that. Depending on what characters are being used, how they map (if they even do) to the other code page, and how the partition ranges are set up, you can experience anything ranging from "no effect at all" to "really strange and difficult to trace/debug behavior".

I have submitted a request to have this particular meta-data added to the sqlservr.exe -q operation, but Microsoft has no real incentive to fix it given that it's an undocumented feature. So, please support it (i.e. vote for it) so they can at least see that people do want it (thanks!):
Update Collation of Partition Functions and User-Defined Table Types via SQLSERVR -Q

That being said, the two main questions are:

What is the effect of changing the collation in the first place? What is the current collation and what is the new collation?

If the code page is changing, will there be data loss (the example code in the question shows a VARCHAR column, which makes this a possibility; not so for NVARCHAR)? This approach does not do a code page conversion, so characters with values between 128 and 255 might become some other character, and there are no warnings/errors when this happens (but if you don't have such data, then this is a non-issue).

If sensitivities are changing (i.e. going from Accent-Sensitive to Accent-Insensitive) then will there now be violations of uniqueness in an existing unique index/constraint?
Do you want the new collation used for EVERYTHING? If so, this might work. If not, then this approach is not viable as it changes EVERYTHING.

For complete details on this approach, please see my post:

Changing the Collation of the Instance, the Databases, and All Columns in All User Databases: What Could Possibly Go Wrong? (be sure to review the "Finding and Fixing Ignored Objects" section which contains queries to help with fixing partition functions)

If you have multiple databases and want to only change this one (again, EVERYTHING in this database, including the database's default collation), then you can:

Detach (or backup) this database.
Create a new, temporary instance just for making this change, using the same instance-level collation being used for the instance that this database currently belongs to.
Attach (or restore) the database to the new, temporary instance.
Update the temp instance's collation using the sqlservr.exe -q method.
Detach (or backup) the database from the temp instance.
Re-attach (or restore) the database to the instance it came from.


It looks like you need to do the following:

OR rr.sequence LIKE '05603[5-8]%'

But in general, I agree with Kin's comment on the question: "storing numbers as strings is not a good idea".


wait_timeout kills session in SLEEP MODE or all connection ?`

Wait Timeout is a variable which represent the time in seconds that MySQL will wait and kill the session if it is in idle/sleep state.

Default value is 28000 secs. If you want to change the wait time value, you also need to change the interactive_timeout variable.

There will be no active sessions with this time interval. If your session is showing as active and some activity is happening so long, then there is issue with the session.


Auto Update Statistics Duration and Interruptions

You can grab this data with the Auto Stats Event Class with SQL Trace.  One of the event's data columns is Duration.  As per the referenced BOL documentation above, here is a quote:

  Duration: Amount of time (in microseconds) taken by the event.

That should give you a pretty good idea when/how long auto stats is taking.  If you are seeing a lot of performance-halting auto update stats (synchronously), you could consider utilizing auto update statistics async.  Turning this on will tell the query optimizer to not wait for statistics to be updated in order to continue compiling the query.  This is one solution in order to prevent the direct interference of stats updates, but of course you're sacrificing fresh statistics for that particular query compilation.

For more information on this, please see the MSDN reference on Statistics.


Strategies to archive high frequent data in SQL Server

If performance is your only consideration if I were you I would consider partitioning your table(s) rather than just deleting the data.  Then you could look into either log shipping (if you want the whole database to be updated) or replication (if you only want a couple of tables copied).  As an alternative you could look into change data capture as a method of storing the changes you are making to the data.  You can then pull this information out to apply it to your other database.


SQL Server recovery tools cannot help here, they can only recover lost information from database files (mdf, ldf, ndf, bak, trn). You should search for a way to recover overwritten files in Windows

One of the solutions is to use the File History option in Windows. Your Windows version must support it and it had to be enabled at the time when the files were overwritten and

"File History saves copies of your files so you can get them back if they are lost or damaged"

Restore files or folders using File History