Select one column into debit and credit SELECT TOP 1000 [LEAD_CO_MNE]
      ,CASE
         WHEN CURRENCY <> 'KHR' THEN  AMOUNT_FCY
         ELSE AMOUNT_LCY
         END 'Amount' --I want to split into type of amount debit or crdit
     ,CURRENCY

How could I split the 'Amount' into 2 column debitand credit base on the condition if > 0 credit else debit.

declare @t table (LEAD_CO_MNE int identity, CURRENCY varchar(10), AMOUNT_FCY int, AMOUNT_LCY int);
insert into @t (CURRENCY, AMOUNT_FCY, AMOUNT_LCY)
values ('KHR', 10, 20), ('KHR', -10, -20), ('KHR', 30, 40), ('other', 1, 2);

with cte as
(
SELECT [LEAD_CO_MNE]
      ,CASE
         WHEN CURRENCY <> 'KHR' THEN  AMOUNT_FCY
         ELSE AMOUNT_LCY
         END Amount --I want to split into type of amount debit or crdit
     ,CURRENCY
from @t
)

select [LEAD_CO_MNE],
       CURRENCY,
       case when Amount > 0 then Amount else 0 end as Credit,
       case when Amount < 0 then Amount else 0 end as Debit
from cte;


creating an index for varchar column greater than 900 bytes I have a var char column in SQL Server 2008 which is usually less than 100 bytes but in some cases can be huge up 5607 bytes. I need to index it, and I can't because the max index size in SQLServer is 900 bytes.

Is there any way in the world I can add any sort of an index to this column?  For example, create an index on the first 900 bytes?

Thanks

If you need an index so seeks on the first n characters work well (where n <= 900 bytes), you can create a computed column of LEFT(column, n) and index that. If you want an index for exact matching, sorry, there isn't a way that I know of to do that unless you use a checksum or similar. Can you explain the actual business problem? What strings > 900 bytes do you feel would benefit from such an index, and why?


Why Isn't the SQL Server Agent Service Set to Automatic Startup by Default? After a default install of SQL Server 2008 R2, the SQL Server Agent process seems to be set to manual startup. What is the reason for this? Is setting to automatic a bad idea or pointless for some reason?

During the install process for SQL 2008 R2 it gives you the option to change the start-up type.

I can see a slight desire to not have it start automatically depending on what you might be using it for. I have some setups that the user has some Agent jobs that clean up data or immediately start processing data. If I am troubleshooting, or in general the server goes down, I may not want all that processing to start back up immediately after bring the server back online.

In most instances though you will see the Agent set to automatic start up.


How to convert Bytes to GB using sql query Im trying to verify value that have been changed in the DB using query. Howeverm it return the value I passed (in GB) to bytes.
I wanted to verify to esnure value stored in (bytes) is correct when I make comparison with gigabytes.

set serveroutput on
DECLARE
static_value VARCHAR2(4000);
BEGIN
SELECT value
INTO  static_value
FROM   v$parameter WHERE  name = '&1';
DBMS_OUTPUT.PUT_LINE('&1 is set to '|| RTRIM(static_value));
DBMS_OUTPUT.PUT_LINE(&2);
DBMS_OUTPUT.PUT_LINE(RTRIM(static_value));
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Failed to set parameter because of '|| SQLCODE);DBMS_OUTPUT.PUT_LINE('Error message - '|| SUBSTR(SQLERRM,1,128));
END;/

The result above return
static_value = 6442450944
and
&2 = 6g (which i specified i shell script that I call and pass the parameter to the sql query above.

How can I convert from bytes to gb in this case and compare static_value IS EQUAL TO &2?

you can use below function :

create or replace function format_size(p_size IN NUMBER)
return VARCHAR2
IS
  v_i number;
  type array_t is varray(3) of varchar2(10);
  v_array array_t := array_t(' Bytes', ' KB', ' MB',' GB');
BEGIN
  v_i := floor(log(1024, p_size));
  return to_char(round(p_size/power(1024, v_i), 2))||v_array(v_i+1);
END;


How to create multiple tables based on existing tables in postgreSQL? I have several tables, and I need to create a matching _hist table for each one. For example if I have:

CREATE TABLE public.test (
    first       text,
    middle      text,
    "last"      text,
    "timestamp" timestamp without time zone NOT NULL default now()
);

I would like to create:

CREATE TABLE public.test_hist (
    "action"    text,
    "user"      text,
    first       text,
    middle      text,
    "last"      text,
    "timestamp" timestamp without time zone NOT NULL default now()
);

I'm in the initial phase of the project I'm working on, and requirements keep changing.

As I run the changed script that drops and re-creates tables, I would like for _hist tables to somehow get automatically generated, so I don't need to make the same change in two places.

Is it possible to do that with SQL at the end of my table creation script?

I have postgreSQL 10.

Or should I do it in python?

Most of these procedural things can be done using a PL/pgSQL DO Block. So you don't have to get into another language. Assuming your main tables have a naming pattern like 'mt_%', you could do something like the following.

do
$create_hist_tables$
declare
  main_table text;
begin
for main_table in
  select
    'public.' || quote_ident(table_name)
  from
    information_schema.tables
  where
    table_name like 'mt_%'
    and table_schema = 'public'
loop
  execute 'CREATE TABLE ' || main_table || '_hist ("action" text, "user" text, like ' || main_table || ' including all)';
end loop;
end
$create_hist_tables$
;

Building a long query using concatenation will not be neat for maintainability. So you could replace the single create query with a series of create and alter commands within the loop.

I have tried this on PG 11.4.


How to design a schema for a site that accepts logins from multiple sites (e.g. facebook connect, google, flickr, etc..)? I'm building a site ("The Site") and I'm considering how to design the DB schema for it.

I have a few scenarios that will affect the outcome:

The user can choose to register a native account on the site, which will make him use that account for login in to his account on the site in the future.
The user can choose to login via another site ("external site") (such as facebook connect, google, foursquare, etc..), which will make him use that for login in to his account on the site in the future.
Doesn't really matter which login method he chose (native account or external site account), the user will be able to authenticate his account on the site with external sites (such as facebook, twitter, instagram, etc...) for using the data he has on the external site (such as his tweets from twitter, his photos from flickr, etc..), in the site.

How should I design the schema of the DB ?

For the first two scenarios, I thought of having a table called Users that has an id, and a type fields. The type reference a different table, such as FacebookUsers or GoogleUsers, each has a user_id field - which has a foreign relation to the Users.id field - and other fields such as the first_name, profile_picture, etc..

However:

I'm not sure how smart it is to use this approach.
I don't know how to implement the third scenario in such an approach.

There is a similar question in stackoverflow

And the solution is

  You need 2 additional fields in your
  table, one is the login source
  (twitter, openID, facebook) and the
  other is the source user id (the id
  provided from twitter, openID,
  facebook) I recommend making this a
  string as some log in sources have
  letters in their user IDs and not just
  numbers. more

I'm not sure how smart it is to use this approach.

  This approach is good, actually sites
  opting it. see SO login page
  https://stackoverflow.com/users/login

I don't know how to implement the third scenario in such an approach.

  I think  you can implement this, only
  if the thrid party sites provide
  access. This question is off-topic
  here, you can ask this in
  stackoverflow


Making sense of sys.objects, sys.system_objects, and sys.sysobjects? In this question I was writing a query using sys.sysobjects. However, one of the answers mentioned sys.system_objects. I'm just wondering what is the difference between these tables?

sys.objects
sys.system_objects
sys.sysobjects

sysobjects has more things.

> SELECT count(*) FROM sysobjects;
2312

> SELECT count(*) FROM sys.system_objects;
2201

> SELECT count(*) FROM sys.objects;
> 111

SELECT count(*)
FROM sys.sysobjects
WHERE NOT EXISTS (
  SELECT 1
  FROM sys.system_objects
  WHERE system_objects.object_id = sysobjects.id
);
> 111

As noted in my previous post sys.sysobjects is deprecated:

  Note taken from sys.sysobjects (Transact-SQL)

  This SQL Server 2000 system table is included as a view for backward compatibility. We recommend that you use the current SQL Server system views instead. To find the equivalent system view or views, see Mapping System Tables to System Views (Transact-SQL). This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

You would now have to combine sys.system_objects and sys.objects to retrieve all items that are stored in the deprecated sys.sysobjects system table.

SELECT * FROM sys.system_objects
UNION ALL
SELECT * FROM sys.objects AS o

Result:

(2171 row(s) affected)

sys.system_objects

  Contains one row for all schema-scoped system objects that are included with Microsoft SQL Server. All system objects are contained in the schemas named sys or INFORMATION_SCHEMA.

sys.objects

  Contains a row for each user-defined, schema-scoped object that is created within a database, including natively compiled scalar user-defined function

For example sp_MScleanupmergepublisher is ìs_ms_shipped but not in the sysschema (it is in dbo) so it's in sys.objects and not sys.system_objects. This is possibly because  it is a shell for sys.sp_MScleanupmergepublisher_internal and is probably defined on instance creation. If you have SSMS right-click on the dbo.sp_MScleanupmergepublisher system stored procedure and then select Modify:

USE [master]
GO
/****** Object:  StoredProcedure [dbo].[sp_MScleanupmergepublisher]    Script Date: 19.12.2017 12:12:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER procedure [dbo].[sp_MScleanupmergepublisher]
as
    exec sys.sp_MScleanupmergepublisher_internal


Is MSMerge_History.time in GMT or local server time? Is the time column in the MSMerge_History table GMT or local distribution server time?

The MSmerge_history table does have a column named time and it's of the type datetime, it just isn't documented.

To answer your question, based on my tests it appears to be local server time.  The time column has a default value of getdate().


Is there a recommended way to update a database schema when updating and app's RPMs? I have a pretty standard server layout:

2 Application servers running Tomcat on Redhat.
2 Database servers running Postgres on Redhat. One database is a master and replicates to the slave.

I'd like to automatically upgrade the scheme when I update the web apps. The web apps are deployed using RPMs. I'm at a loss to figure out if there's a recommended way of doing this.

Unless the tools you're using have built-in support for database schema migrations (like ActiveRecord/Rails does, albeit rather unsafely) - you need to do it yourself in your application.

The usual technique is to have a table in your DB that contains metadata about the schema and application. You might structure it in key/value form, or as a single row with multiple cols; it doesn't matter. Either way, this table should store a schema version.

When you connect on app startup, before doing anything else with the DB, you should query the metadata table for the schema version. If it's less than the current version in the application, your application should apply schema change scripts from the old version to the new version. I used @Singleton @Startup EJB3s for that, and read the schema change scripts using the classloader from property files embedded in the application jars; you're probably using Spring or some similar framework with similar capabilities.

Because you have two application servers, you'll probably have to LOCK TABLE your_database_metadata_table before beginning to apply changes to ensure that both servers don't attempt to apply the same database migration scripts.

DDL changes are easier in PostgreSQL than some other DBs because of its support for transactional DDL. You can BEGIN a transaction, apply all the database changes in one go - DDL, metadata table updates, etc etc - and COMMIT. You won't land up with a half-applied schema change. This is only viable when the schema change won't take too long or you can afford a delay. If you're ALTERing the data type of a 100-million row table, you don't want to do that in a single big transaction unless everything else can wait.

Replication is taken care of automatically if you're using the built-in streaming replication; it doesn't differentiate between DDL and DML, they're are all just database changes as far as the replication system is concerned.

If you're using another replication engine like Slony-I or Bucardo, then it depends on how the replication engine deals with schema changes. This is engine specific, so you need to look into the docs for your replication engine. (The current mess with 3rd party replication and DDL changes should improve dramatically some time after the 9.3 event triggers, logical replication and command deparsing support is added to PostgreSQL core. Some of that looks like it'll slip to 9.4.)

Now, if you want to do your DDL changes non-disruptively without interruptions to the applications, that's a whole other level of fun.


When converting a table valued function to inline, why do I get a lazy spool? I have a table valued function that I want to convert to inline to improve performance.
It sets the value of variables to the value four bit columns in one row of a table (looking up the row using a function parameter as the key), then runs one or more of four different insert statements (differing only by the WHERE clause) to populate a temp table based on which of the bit variables have a value of 1, and then returns the content of the temp table.

I rewrote it to be one SELECT (with a CTE to get the four bit column values) using four OR clauses in the WHERE to get the same result.

However, the plan cost jumped from .003 to over 2.5. On the other hand, the actual performance is better judging by the output from SET STATISTICS TIME ON. 75% of the cost of the inline version is for a lazy spool. Is there a good way to avoid the lazy spool and improve performance even more? The output could be anywhere from one row to thousands.

A "lazy" spool, is not really lazy in the "bad" sense of the word.  It's lazy in that it may remove the need to re-scan the input data for the given operation, resulting most of the time, in improved performance.

From this MSDN page:

  The Lazy Spool logical operator stores each row from its input in a hidden temporary object stored in the tempdb database. If the operator is rewound (for example, by a Nested Loops operator) but no rebinding is needed, the spooled data is used instead of rescanning the input. If rebinding is needed, the spooled data is discarded and the spool object is rebuilt by rescanning the (rebound) input.

  The Lazy Spool operator builds its spool file in a "lazy" manner, that is, each time the spool's parent operator asks for a row, the spool operator gets a row from its input operator and stores it in the spool, rather than consuming all rows at once.

You mention in your question that performance has increased by converting the function into an inline variant; I'd say that's winning.  Looking at the largest cost operator is not always the best way to optimize your code; for instance that would never inform you that moving from a multi-statement-TVF to an inline-TVF would be so performance enhancing.  If you want more specific performance improvements for the code in question, adding that code to your question will help us help you.


How to find when the user last logged in Mysql I need to find all the users when they last logged in in mysql. My goal is to cleanup users that are not used for months. Please help

mysql hasn't such a thing.
You could force the users to change the password every 90 days and select the date, where the users didn't change their password.

By

SELECT User FROM mysql.user WHERE password_last_changed <  NOW() - INTERVAL  3 MONTH;

Everything else would require things like enable file logs see for more information


Full Transaction Log on closed source system I have a closed source application which has the transaction log filling up on a daily basis to 60+gigs.

I have ran a trace on the database and no inserts and updates are occurring. The following statement though is being executed THOUSANDS of times per minute which doesn't seem right.

set transaction isolation level read uncommitted

If this is not the cause of the issue, is there anything else I can do to find the cause of this issue without turning off full backups as its required.

Thanks

You can try to use some third party tools for transaction log reading, ApexSQL, Idera, RedGate. You can also try to determinate by using fn_dblog function, with all that executions you should be able to easily see the problematic statement:

SELECT [Current LSN], Operation, Context, [Transaction ID], [Begin time] FROM sys.fn_dblog (NULL, NULL)


Creating very quickly an composite partial index on 2 new columns with null values I'm adding 2 new columns on big table. Those 2 new columns have no default value and no constraint. I also want to add a composite partial index on those 2 new columns.

CREATE UNIQUE INDEX CONCURRENTLY index_users_on_col1_and_col2 ON public.users USING btree (col1, col2) WHERE ((col1 IS NOT NULL) AND (col2 IS NOT NULL));

I need that to be done concurrently to avoid writes lock. The 2 new columns will start having value only a few days after I add them and I add the index.

So basically the index will be empty when creating.

Is there a way to create the index by "saying" to PostgreSQL it should create the index directly empty and not scan the table?

There is no built-in way to do this.  If you are willing to compile your own PostgreSQL binaries it might be relatively easy to implement this.  This part of the code gets whacked around a bit between versions to add new features and performance improvements, so you would have to target a specific version.


Does Restore "From database" restore from the most recent backup of the database or the actual live database? In SSMS 2005, I want to use the GUI to copy a database by restoring in to a new database.  I was using the "From database" radio button for the source, but then the databases in the list disappeared recently.  I read the sql server documentation and it says that only databases that are on record as having a backup  will show up in this list.  Nothing in the documentation says whether this backup will the actual data source or not.

When I select "From Database" in the source options, does it pull from the most recent backup of the database, or the actual live database?

It will not pull from the live database. There is no SQL command for this (yet). It will give you a selection of recent backups you can pick from. You can only restore from a database snapshot and from a backup, not from a live database.


Is it possible through only T-SQL to physically move the data and log files of a database from one drive to another drive (on the same server)? I know you can alter the database to update their data and log file paths in the database properties, but is there also a way to script out the actual physical move of the files themselves?...maybe through a database command?

Below is a solution that uses robocopy with xp_cmdshell to move the files. Afterwards the DEL command is used to delete the old files.
It has its flaws (only data files + log files to be moved), only one data location, no checks for missing or needed \'s, error handling should be better,...

But you might find use in simply printing the commands by setting the @debug parameter to 1.

Test database used

CREATE DATABASE [Test]
 CONTAINMENT = NONE
 ON  PRIMARY
( NAME = N'Test', FILENAME = N'G:\SQL\MSSQLSERVER\Test.mdf' , SIZE = 10240KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1048576KB ),
( NAME = N'test2', FILENAME = N'D:\DATA\test2.ndf' , SIZE = 10240KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON
( NAME = N'Test_log', FILENAME = N'E:\LOG\Test_log.ldf' , SIZE = 10240KB , MAXSIZE = 2048GB , FILEGROWTH = 1048576KB )
GO

All files are moved to F:\Data and F:\Log in the example

Full script:

DECLARE @DBNAME varchar(255) = 'Test' -- No [ ] 's
DECLARE @NewDataFileLocation varchar(4000) = 'F:\Data\' -- end with \
DECLARE @NewLogFileLocation varchar(4000) = 'F:\Log\' -- end with \
DECLARE @debug bit = 1; -- 1 to print, 0 to execute

-- main file table, can be kept after commands are exeucted if created as a physical table and the drop command and the end is removed

CREATE TABLE #files (logicalname varchar(255),
                     physical_name varchar(4000),
                     movelogicalfilecommand nvarchar(max),
                     movephyisicalfilecommand nvarchar(max),
                     error bit)

-- insert the commands for the data files
INSERT INTO #files(logicalname,physical_name,movelogicalfilecommand,movephyisicalfilecommand)
SELECT  [name],
        [physical_name],
        'ALTER DATABASE ' +QUOTENAME(DB_NAME(database_id)) +'
 MODIFY FILE (name='''+[name]+'''
             ,filename='''+@NewDataFileLocation+RIGHT(physical_name,(CHARINDEX('\',REVERSE(physical_name))-1))+''');',
           'ROBOCOPY "'+
           REVERSE(SUBSTRING(REVERSE(physical_name),CHARINDEX('\',REVERSE(physical_name))+1,LEN(physical_name)))
           + '" "'
           + LEFT(@NewDataFileLocation,(LEN(@NewDataFileLocation)-1)) +  '" "'+
           RIGHT(physical_name,CHARINDEX('\',REVERSE(physical_name))-1) +'" '

FROM sys.master_files
WHERE QUOTENAME(DB_NAME(database_id)) = QUOTENAME(@DBNAME)
AND type_desc = 'ROWS';

-- insert the commands for the log files

INSERT INTO #files(logicalname,physical_name,movelogicalfilecommand,movephyisicalfilecommand)
SELECT  [name],
        [physical_name],
        'ALTER DATABASE ' +QUOTENAME(DB_NAME(database_id)) +'
 MODIFY FILE (name='''+[name]+'''
             ,filename='''+@NewLogFileLocation+RIGHT(physical_name,(CHARINDEX('\',REVERSE(physical_name))-1))+''');',
           'ROBOCOPY "'+
           REVERSE(SUBSTRING(REVERSE(physical_name),CHARINDEX('\',REVERSE(physical_name))+1,LEN(physical_name)))
           + '" "'
           + LEFT(@NewLogFileLocation,(LEN(@NewLogFileLocation)-1)) +  '" "'+
           RIGHT(physical_name,CHARINDEX('\',REVERSE(physical_name))-1) +'" '

FROM sys.master_files
WHERE QUOTENAME(DB_NAME(database_id)) = QUOTENAME(@DBNAME)
AND type_desc = 'LOG';

-- Logically move the files in the system catalogs

DECLARE @cmd nvarchar(max), @movelogicalfilecommand nvarchar(max), @movephyisicalfilecommand nvarchar(max);
DECLARE C CURSOR FOR
SELECT movelogicalfilecommand
FROM #files;
OPEN C;
FETCH NEXT FROM C INTO @movelogicalfilecommand;
WHILE @@FETCH_STATUS = 0
BEGIN

BEGIN TRY

    SET @cmd = @movelogicalfilecommand;
    IF @debug = 1
    PRINT @CMD;
    ELSE
    EXEC SP_EXECUTESQL @cmd;

END TRY

BEGIN CATCH

    UPDATE #files
    set error = 1
    WHERE movelogicalfilecommand = @movelogicalfilecommand;

END CATCH

FETCH NEXT FROM C INTO @movelogicalfilecommand;
END
CLOSE C;
DEALLOCATE C;

-- Set the database offline
-- subsequently move the physical files with robocopy

DECLARE @cmd2 NVARCHAR(4000)
IF NOT EXISTS (SELECT * FROM #files where error = 1)
BEGIN

    SET @cmd = 'ALTER DATABASE '+QUOTENAME(@DBNAME)+' SET OFFLINE WITH ROLLBACK IMMEDIATE'
    IF @debug = 1
    PRINT @CMD;
    ELSE
    EXEC SP_EXECUTESQL @cmd;

    DECLARE C CURSOR FOR
    SELECT movephyisicalfilecommand
    FROM #files;
    OPEN C;
    FETCH NEXT FROM C INTO @movephyisicalfilecommand;
    WHILE @@FETCH_STATUS = 0
    BEGIN

    BEGIN TRY

        SET @cmd2 = @movephyisicalfilecommand;
        IF @debug = 1
        PRINT 'EXEC XP_CMDSHELL '''+@cmd2 +'''';
        ELSE
        EXEC XP_CMDSHELL @cmd2;

    END TRY
    BEGIN CATCH

        UPDATE #files
        set error = 1
        WHERE movephyisicalfilecommand = @movephyisicalfilecommand;

    END CATCH

    FETCH NEXT FROM C INTO @movephyisicalfilecommand;
    END
    CLOSE C;
    DEALLOCATE C;
END
ELSE
SELECT 'ERROR moving logical files'

-- Set the database online

IF NOT EXISTS (SELECT * FROM #files where error = 1)
BEGIN

    BEGIN TRY

        SET @cmd = 'ALTER DATABASE '+QUOTENAME(@DBNAME)+' SET ONLINE'
        IF @debug = 1
        PRINT @CMD;
        ELSE
        EXEC SP_EXECUTESQL @cmd;

    END TRY
    BEGIN CATCH

        UPDATE #files
        set error = 1
        WHERE movephyisicalfilecommand = @movephyisicalfilecommand;

    END CATCH
END
ELSE
SELECT 'ERROR moving physical files';

DECLARE @physical_name varchar(4000)

IF NOT EXISTS (SELECT * FROM #files where error = 1)
BEGIN
    DECLARE C CURSOR FOR
    SELECT physical_name
    FROM #files;
    OPEN C;
    FETCH NEXT FROM C INTO @physical_name;
    WHILE @@FETCH_STATUS = 0
    BEGIN

    BEGIN TRY

        SET @cmd2 = 'DEL "'+@physical_name +'"';
        IF @debug = 1
        PRINT 'EXEC XP_CMDSHELL '''+@cmd2+'''';
        ELSE
        EXEC XP_CMDSHELL @cmd2;

    END TRY
    BEGIN CATCH

        UPDATE #files
        set error = 1
        WHERE physical_name = @physical_name;

    END CATCH

    FETCH NEXT FROM C INTO @physical_name;
    END
    CLOSE C;
    DEALLOCATE C;
END
ELSE
SELECT 'ERROR setting database online';

DROP TABLE #files;

Output example

ALTER DATABASE [Test]
 MODIFY FILE (name='Test'
             ,filename='F:\Data\Test.mdf');
ALTER DATABASE [Test]
 MODIFY FILE (name='test2'
             ,filename='F:\Data\test2.ndf');
ALTER DATABASE [Test]
 MODIFY FILE (name='Test_log'
             ,filename='F:\Log\Test_log.ldf');
ALTER DATABASE [Test] SET OFFLINE WITH ROLLBACK IMMEDIATE
EXEC XP_CMDSHELL 'ROBOCOPY "G:\SQL\MSSQLSERVER" "F:\Data" "Test.mdf" '
EXEC XP_CMDSHELL 'ROBOCOPY "D:\DATA" "F:\Data" "test2.ndf" '
EXEC XP_CMDSHELL 'ROBOCOPY "E:\LOG" "F:\Log" "Test_log.ldf" '
ALTER DATABASE [Test] SET ONLINE
EXEC XP_CMDSHELL 'DEL "G:\SQL\MSSQLSERVER\Test.mdf"'
EXEC XP_CMDSHELL 'DEL "D:\DATA\test2.ndf"'
EXEC XP_CMDSHELL 'DEL "E:\LOG\Test_log.ldf"'


MySQL After Insert trigger - how to handle case where no rows were inserted UPDATED based on Akina's answer -

In MySQL the After Insert trigger fires for every insert call, even in cases where the insert fails and no rows are inserted. While any changes made by the trigger are rolled-back by MySQL on failure, external service calls made by the trigger may cause changes that need to also be rolled back.

Is there a way to catch this edge case in the trigger so it can be handled?

For ref my general trigger format is -

CREATE TRIGGER `db`.`table_name_AFTER_INSERT` AFTER INSERT ON `table_name` FOR EACH ROW
BEGIN

-- HANDLE ERRORS
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
    get diagnostics condition 1
      @p1 = MESSAGE_TEXT,
      @p2 = RETURNED_SQLSTATE,
      @p3 = MYSQL_ERRNO,
      @p4 = SCHEMA_NAME,
      @p5 = TABLE_NAME;
    INSERT INTO `db_errors_table_name` (`location`, `error`)
        VALUES ('Some descriptive text here regarding loc', @p1);
END;

-- Call external service here

END

the After Insert trigger fires for every insert call, even in cases where the insert fails and no rows are inserted

That's not right.

Single query is a transaction, trigger action is a part of it, if fail the whole transaction is rollbacked, including changes made by trigger.

create table test (id int not null primary key, val int);

✓

create table log(id int, val int, cnt int not null auto_increment primary key);

✓

create trigger tr after insert on test for each row
insert into log (id, val) values (new.id, new.val);

✓

insert into test values (1,1), (2,2);

✓

select * from test;

id | val
-: | --:
 1 |   1
 2 |   2

select * from log;

id | val | cnt
-: | --: | --:
 1 |   1 |   1
 2 |   2 |   2

insert into test
select 3,3 union all select 2,4 union all select 5,5 order by 2;

Duplicate entry '2' for key 'PRIMARY'

select * from test;

id | val
-: | --:
 1 |   1
 2 |   2

select * from log;

id | val | cnt
-: | --: | --:
 1 |   1 |   1
 2 |   2 |   2

insert into log (id, val) values (6,6);

✓

select * from log;

id | val | cnt
-: | --: | --:
 1 |   1 |   1
 2 |   2 |   2
 6 |   6 |   4

db<>fiddle here

Pay attention to the hole in log.cnt values. Autoincrement value 3 is absent because it was used during 2nd insertion and then it was removed during rollback caused by duplicate error.


Can sp_BlitzFirst pass the @ExpertMode parameter to sp_BlitzWho When running sp_blitzFirst @expertmode = 1 the first result set(result set of sp_blitzwho)does not return nearly as many columns and it does in Brent's video. With a little troubleshooting it was uncovered that the reason this is happening is because the calling stored procedure is not passing @expertMode = 1 to sp_blitzWho.
To prove that i can run sp_blitzWho and pass @expertMode = 1 in which case the etire result set is returned.

So my question is: Was there a change to latest version of these stored procedures?
Where can i find the version that work like the one Brent displays in his video?
Thank you for looking in to this.
Best Regards.

You're watching a 4-year-old video on YouTube, and sp_BlitzFirst no longer works that way.

If you want to run sp_BlitzWho with @ExpertMode = 1, your best bet is to go ahead and call it directly.


Create function in central database or repeat in each database? One of my developers has written a SQL function that works like the VB.Net function (LastIndexOf) and wants to publish it. My question is what would be the reason to put this in a central database versus putting it in each user database?

The developer was trying to put it in sys schema on his master db so he wouldn't have to qualify calls to it from user databases... sigh

But I wasn't sure what the valid excuse would be to centralize it (obviously not master database) versus each user database?

The way I prefer to do this: put the function in a utility database, and create a synonym to it in each regular database. This way you get the best of both worlds:

there is only one copy of the object to maintain
the developer doesn't have to provide three- or four-part names

e.g.

USE UtilityDB;
GO
CREATE FUNCTION dbo.LastIndexOf(...) ...
GO
USE otherDB;
GO
CREATE SYNONYM dbo.LastIndexOf FOR UtilityDB.dbo.LastIndexOf;
GO

This is especially powerful for CLR functions, since there is extra administrative overhead for changing/deploying those.

And this is way preferable to using master (and marking as a system object, which isn't guaranteed to be forward portable). I'd love to know how your developer expects to create his function in the sys schema, though.

I do understand that maintaining multiple copies of a function in 500 databases is no more difficult really than maintaining a single copy, but having multiple copies is really only a benefit when you do have exceptions (e.g. client A wants their function to handle NULLs differently, or something). In which case I would leave the synonym in all the other databases, and introduce a special version of the function only in that database.

(This assumes that the function doesn't rely on any data access within a client's database, of course - which can certainly complicate matters.)


Upload multiple files to SQL Server without access to the Server Box I am trying to create a process that would bulk upload multiple csv files periodically to individual tables in SQL server. The issue I am having is those files are not located on the same server.

I can use the bulk upload command to upload files from a folder on the SQL server box but not from our shared drive which is located on a different server. There are multiple people who would be running this process and so it might not be feasible because not all of them would get that kind of access to the SQL server box.

Is there any way of accomplishing this with the same level of efficiency and speed?
I have thought of sending each line in the csv files but those files are huge and would take a long time
Just looking for ideas to make this work.
Would SSIS be less or more or equally efficient?

If there are no transformations involved, you could write this process fairly simply in SSIS with a for loop in the control flow that iterates over a file directory, then passes each file name as a variable to a data flow.  The filename for the csv would be bound to that variable, and your destination could similarly be bound to the file name.  Just use bulk mode in your target component and you should get pretty good efficiency.  This works great if the folder is accessible to all users and you add a step to either check and see that files in the directory are processed or move them somewhere else when they've been processed.  The SSIS process could be invoked from the SSIS catalog on the SQL Server directly, or scheduled on a regular basis.

Alternatively, you could use the bcp Utility for a CLI experience.  This is probably a lot simpler in that it doesn't require writing an SSIS process and management of that process, but it requires more knowledge on the end of the user invoking it.  This tool doesn't require the files be on the server: you provide a path to the file as the invoking user, along with credentials to authenticate, and the tool will handle the data movement.


Introducing FOREIGN KEY constraint 'FK_X_Y' on table 'X' may cause cycles or multiple cascade paths So while publishing my database scripts in Visual Studio, I get this exact error: Introducing FOREIGN KEY constraint 'FK_Users_Manager' on table 'Users' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

This is my simplified script for creating a self referencing Users table:

CREATE TABLE [dbo].[Users]
(
    [UserId] INT NOT NULL IDENTITY,
    [ManagerId] INT NULL,
    [Data] NVARCHAR(1024) NULL,
    CONSTRAINT [PK_Users_UserID] PRIMARY KEY ([UserId] ASC),
    CONSTRAINT [FK_Users_Manager] FOREIGN KEY ([ManagerId]) REFERENCES [Users] ([UserId]) ON DELETE SET NULL
)

Even if somehow I made a cycle on these relationships, I don't see why wouldn't deleted row's direct children have their ManagerId set to NULL?

Should I create a trigger for this scenario to set NULLs for me instead? Or is there something I am clearly missing?

You can use an INSTEAD OF trigger here, or have logic that re-assigns all the direct-reports prior to deleting a user.  EG

--drop table if exists users
go
CREATE TABLE [dbo].[Users]
(
    [UserId] INT NOT NULL,
    [ManagerId] INT NULL,
    [Data] NVARCHAR(1024) NULL,
    CONSTRAINT [PK_Users_UserID] PRIMARY KEY ([UserId] ASC),
    CONSTRAINT [FK_Users_Manager] FOREIGN KEY ([ManagerId]) REFERENCES [Users] ([UserId]) ON DELETE NO ACTION
)

go
create trigger tg_users_del on [Users] instead of delete
as
begin
  set nocount on;
  update Users set ManagerId = null where ManagerId in (select UserID from deleted);
  delete from Users where UserId in (select UserID from deleted);
end

GO
INSERT INTO Users(UserId, ManagerId) values (1,null),(2,1),(3,1),(4,2)

DELETE FROM Users where UserId = 1

select * from Users


async_network_io issues with BIDS This is related to my previous question drastically different runtimes in BIDS and the SSRS Web Portal.

I am struggling with refreshing some reports in BIDS because they run forever.  I have now noticed that when I refresh the report in BIDS the query will experience ASYNC_NETWORK_IO waits.  (also CXPACKET but I understand that's more an effect than a cause)

The post Need help with ASYNC_NETWORK_IO seems to indicate SQL Server is waiting on the client, which I assume in this case is BIDS on my desktop.

If it can be identified from this snippet of information, where is the problem here?  Is it my desktop and its lack of oomph?  Could it be the network?  Is my report doing too many calculations on the report-side?  Where else should I look?  How can I fix it?

I will add that oftentimes the data never full returns.  Instead I receive the following (redundant) error message:

  An error occurred during local report processing.  An error has occurred during report processing.  Exception of type 'System.OutOfMemoryException' was thrown.

We actually ran into a similar issue at StackOverflow and Kyle blogged about it:

http://blog.serverfault.com/2011/03/16/views-of-the-same-problem-network-admin-dba-and-developer/

The problem can be a number of things:

Queries bringing back too much data (like select * from a wide table with a lot of XML or binary fields)
Client-side apps processing data row by row instead of pulling it all into memory and then doing whatever work necessary
Underpowered app server hardware (or in your case, client machines) that are paging to disk or churning on CPU.  I see this a lot on over-committed virtual machines.

To tune it, start by looking at Perfmon counters on your own machine.  My tutorial on it is at http://www.BrentOzar.com/go/perfmon, and it includes a list of counters to gather, how to analyze 'em, and how to interpret your bottleneck.


How to get TNS connect strings (like tnsping) to check a dblink via a session? I would like to know, if there is any chance to get the currently used TNS connect string, that will be used for a DB-Link from a db-server.

I'm connected to a schema on a database located on any server.
Now, I have some database links, using tnsnames and which are currently not working properly.
I would like to check, which target database is behind this identifier to verify easily, if this can be correct.

If I would have OS access to the db-server, I would be able to read the ./tnsnames.ora or using tnsping.

But the DDL of the dblink just uses the TNSNAME (RDBX in this example):

  CREATE DATABASE LINK "ABC"
     CONNECT TO "RUSER" IDENTIFIED BY VALUES ':1'
     USING 'RDBX';

Of course, I could ask a DBA, but this is not always as easy in our environment and I would like to be able to double-check it myself without having OS-access.

I do not think that is possible.
Someone or something needs access to the Filesystem to read the tnsnames.ora.
When using the DB-link oracle will get the definition for RDBX from the tnsnames.ora file. This is transparent to the user, so the user will not notice. As @EdStevens suggested I would try to solve the political issues and work with the DBA.
Or, ask the OS-admin to have a look for you.


Why is AWS RDS' maximum auto_explain.log_min_duration parameter 214783? That seems like a really arbitrary number of milliseconds to use as a maximum.  What can I do if I want to log even longer-running queries on a data warehouse?

C.f. https://collabshot.com/show/83dd1e

This is not specific to AWS RDS (well part of it specific to that), it is in the upstream code.

The setting is restricted to INT_MAX / 1000.  But I can't figure out why, as that setting is never multiplied by anything, much less 1000.  This seems like a bug to me.  You might want to report it to the PostgreSQL bugs mailing list.

In the meantime, I guess your option would be to set it to the max and then filter your log files.  It seems unlikely to have millions of queries each taking 40 minutes, so filtering it shouldn't be too onerous.

update

And soon after your report, it has been fixed in PostgreSQL for all supported versions.  It will be included in the next bugfix release, which is due out soon.  I don't know when Amazon is likely to both roll out the next bugfix release to RDS, and fix their own independent bug on the same subject matter.


How to show distinct column one time and different seperately? Here is the key value table

Key  value
one  rose
one  apple
one  Grapes

result showing like this

Key value
one rose
    apple
    Grapes

This really is something that the display layer should handle.  But it can be done in SQL Server.  The below sample should show you the basic method.

The Setup

DECLARE @SampleTable AS TABLE
    (
    Keys VARCHAR(10) NOT NULL
    , Val VARCHAR(20) NOT NULL
    );

INSERT INTO @SampleTable
    (Keys, Val)
VALUES ('one', 'rose')
    , ('one', 'apple')
    , ('one', 'grapes')
    , ('two', 'boxes')
    , ('two', 'jars')
    , ('three', 'ships');

SELECT Keys, Val
FROM @SampleTable;

The Query, using ROW_NUMBER and PARTITION BY with a CASE statement to selectively hide the key name.

WITH CTE_Table AS
    (
    SELECT S.Keys
        , S.Val
        , ROW_NUMBER() OVER (PARTITION BY S.Keys ORDER BY S.Keys, S.Val) AS GroupOrder
    FROM @SampleTable AS S
    )
SELECT Keys = CASE WHEN C.GroupOrder = 1 THEN C.Keys ELSE '' END
    , C.Val
FROM CTE_Table AS C;


SQL Server Index Maintenance-Conditional Rebuilds I have a question about index maintenance. I have a weekly process that does all of my database checks, etc. and a weekly rebuilding of indexes. Those rebuilds have been causing some locking pressure on one of our apps and I am looking at the possibility of scaling the rebuilds back, making them conditional under the right circumstances.

Now, I understand that every case and every application is different, imposing different kinds of tuning if I wanted it 100% optimized. But what I am looking for is a general "good enough" algorithm.

As we are running SQL 2016 Enterprise, it looks like I can do index rebuilds online, which will probably help with the locking pressure, but it would also be nice to be able to look at something in the database and say, hey, "this one can be skipped entirely this week.", either because it's one of my reference tables (no table content changes since last week), or some other slowly changing table...

For various reasons, I gave up on Microsoft's SQL Maintenance Plans a while back and I now use an in house c# program to do all of my SQL maintenance (transaction logs, weekly full backups, weekly checkdbs, weekly index maintenance, etc.) all while sending me an email when it's done letting me know if things went smoothly, or went south somewhere along the way. So, I don't mind adding some queries to my code to see about making index maintenance conditional or not.

It's totally an opinion and observation based in my current project where we handle databases over 20 TBs . We do not do any rebuild or re-organize of indexes as that is very expensive operation. So please check if that is actually helping you out in performance. Rather we focus more on update stats daily with change or modified stats.

If needed, you can use ola hallegren which has excellent parameters to set that up else use the one available from Microsoft.  The SQL tiger team has been doing an excellent job for such activities in their git hub page for adaptive index defrag proc  here


Since you are using SQL Server 2005, you need to perform a single insert for each value.  Upgrade to SQL Server 2012 or higher, and you get the multiple VALUES clause, making this much easier.
Something like:
INSERT INTO __RADHE (ItemNo)
VALUES ('34933945');

I would recommend using find-and-replace to replace the commas with the required syntax.
Replacing the commas in your value-list above with:
); INSERT INTO __RADHE (itemNo) VALUES (

Seems to get you almost all the way there.
As an aside; you should always specify the schema when referencing objects.  When specifying PRIMARY KEY CLUSTERED, you are implicitly creating a constraint, which will be named by SQL Server if you don't specify a name.  Specify a name if you don't like names similar to PK__T1__3214EC27023D5A04.
For instance, your CREATE TABLE statement should be something like:
CREATE TABLE dbo.[__RADHE]
(
    itemNo VARCHAR(10) NOT NULL
        CONSTRAINT PK___RADHE
        PRIMARY KEY CLUSTERED
);

If there is a chance the source data contains duplicate entries, you may find it easier to delay creating the primary key constraint until the data has been inserted, and duplicates removed.  To do that, create the table like this:
CREATE TABLE dbo.[__RADHE]
(
    itemNo VARCHAR(10) NOT NULL
);

Then insert all the rows using the INSERT INTO ... statements as above.
Run this to remove duplicates:
;WITH src AS (
    SELECT r.*
        , rn = ROW_NUMBER() OVER (PARTITION BY r.itemNo ORDER BY r.itemNo)
    FROM dbo.__RADHE r
)
DELETE
FROM src
WHERE rn > 1;

Then create the primary key constraint:
ALTER TABLE dbo.__RADHE
ADD CONSTRAINT pk__RADHE
PRIMARY KEY CLUSTERED
(itemNo);

As a side note, unless you use a TABLOCKX lock hint on the table, all rows are individually logged using either the UNION ALL approach or the individual INSERT INTO approach I detail above.  If you can afford to lock the entire table for the duration of the inserts via TABLOCKX, then only the page allocations are logged, vastly reducing the amount of logging that takes place.  Having said all that, there are of course a lot of "gotchas" that might prevent bulk logging of pages - as detailed by Paul White here.


Use of Parallel hints in a SQL query What's difference between parallel (4) and parallel 4 with and without brackets?
For eg :

select /*+parallel(4) */ * from table_name;
select /*+parallel 4 */ * from table_name;

The first is proper syntax, the second is not.
See here: https://riptutorial.com/oracle/example/4853/parallel-hint


How do I implement insert-if-not-found for transactions at serializable isolation level? I'm having a hard time figuring out how to exactly implement a 'insert if not found' function. Consider the following.

We have a table called artist with 2 columns, (name, id) where name is the unique and id is a serial primary key. It's a contrived example, but it illustrates my problem:

    SESSION A                     SESSION B
1.                                SELECT id FROM artist
                                    WHERE name = 'Bob';
2.  INSERT INTO artist (name)
      VALUES ('Bob')
3.                                INSERT INTO artist (name)
                                    VALUES ('Bob')
4.   code that users 'Bob'
     (e.g., a FK to Bob's ID)
5.                                ??? Bob already exists, but we
                                  can't find it
4.  COMMIT

Session B begins by trying to find an artist called Bob, which fails. However, Session A then creates Bob. Session B tries to insert an artist called Bob, which fails as it violates the primary key. But here's the bit I don't get -- if I change operation 3 to be a select on artist the table is still empty! This is because I'm using the serializable isolation level, but how can I handle this case?

It seems the only option I have is to abort the entire transaction and try again. If this is the case, should I throw my own 'could not serialize' exception, indicating the application should retry? I already wanted this 'find-or-insert' in a plpgsql function, where I would INSERT, and if that failed SELECT but it seems impossible to find the conflicting row...

This is a bit of a FAQ. You'd find more information if you searched for ON DUPLICATE KEY UPDATE (the MySQL syntax), MERGE (the SQL-standard syntax), or UPSERT. It's surprisingly hard.

The best article I've seen on it yet is Depesz's "why is upsert so complicated". There's also the SO question Insert, on duplicate update (postgresql) which has suggestions but lacks explanation and discussion of the issues.

The short answer is that, yes:

  It seems the only option I have is to abort the entire transaction and
  try again.

When using SERIALIZABLE transactions you just have to re-issue them when they fail. Which they will. By design - and much more frequently on Pg 9.1 and above because of greatly improved conflict detection. Upsert-like operations are very high conflict, so you may land up retrying quite a bit. If you can do your upserts in READ COMMITTED transactions instead it'll help, but you should still be prepared to retry because there are some unavoidable race conditions.

Let the transaction fail with a unique violation when you insert the conflicting row. If you get a SQLSTATE 23505 unique_violation failure from the transaction and you know you were attempting an upsert, re-try it. If you get a SQLSTATE 40001 serialization_failure you should also retry.

You fundamentally cannot do that retry within a PL/PgSQL function (without dirty hacks like dblink), it must be application side. If PostgreSQL had stored procedures with autonomous transactions then it'd be possible, but it doesn't. In READ COMMITTED mode you can check for conflicting inserts made since the transaction started, but not after the statement that calls the PL/PgSQL function started, so even in READ COMMITTED your "detect conflict with select" approach simply will not work.

Read depesz's article for a much better and more detailed explanation.


The number of copies for each table stored in physical storage I am new to Postgres and the database field. My question is that how many copies of each table are stored in physical storage device (memory, disk and so on)? I assume we need more than one copies for each table. If we only have one copy for each table, then it will result in one point failure if that storage device is broken. Is my understanding correct?

Thanks,
Zack

It sounds like you might be coming from an Oracle background, where the Oracle database generally manages the storage system directly.

That's not how PostgreSQL works. It uses the file systems, RAID support, etc provided by the operating system.

To get redundant storage with PostgreSQL you need to set up redundant stoage - in the form of RAID, or whatever - in the underlying operating system. Then simply put the PostgreSQL data directory and tablespace(s) on file systems that are stored on the redundant storage array(s).

A common configuration for small installs is:

  SATA disk 1           SATA disk 2
       |                     |
       -----------------------
                   |
                   v
  [Linux 'md' software RAID driver, RAID 1]
                   |
                   v
  [ext4 file system mounted on /var/lib/pgsql]
                   |
                   v
 [PostgreSQL datadir in /var/lib/pgsql/9.3/data]

Of course, that does you no good at all if the machine is destroyed by fire/lightning surge/etc, or someone does an rm -rf /var/lib/pgsql. Which is why you should also have replication to another machine, preferably off-site.

Replication won't help you if someone does a DROP TABLE precious_data; ... which is why you should additionally have frequent logical backups (pg_dump) and/or point in time recovery with WAL archiving.


Mysql Pivot rows into dynamic columns with unknown number of columns I have an output of a query as follows:

bid  | code | amount        |
-----------------------------
2915 | BF   |   -10700.00   |
2915 | YQ   |   -300.00     |
2915 | YR   |   0.00        |
2915 | YM   |   0.00        |
2915 | WO   |   -153.00     |
2915 | IN   |   -329.00     |
2915 | K3   |   0.00        |
2915 | CUTE |   -50.00      |
-----------------------------

I need to convert the values in code column to individual columns with column name as corresponding code and amount shall be the value.

bid  | BF       | YQ     | ... | CUTE  |
----------------------------------------
2915 | -10700.00| -300.00| ... | -50.00|

The issue is that the result of my initial query has dynamic codes and hence need to generate columns accordingly.

Already tried the following logic but couldn't achieve the goal:

dynamic pivot table using mysql, mysql rows to columns, mysql transpose

All the above links use some expression or the other to generate the columns.

My original resulting rows are result of simple select on multiple joined tables, and has no calculation or expression scope.

Kindly suggest some pointer or hint on how to achieve this.

Many thanks in advance.

Procedure:

DELIMITER @@;

DROP PROCEDURE IF EXISTS pivot@@;
CREATE PROCEDURE pivot ( IN schema_name VARCHAR(64) /* database name */
                       , IN table_name VARCHAR(64)  /* table name */
                       , IN id_name VARCHAR(64)     /* row values field name */
                       , IN key_name VARCHAR(64)    /* col values field name,
                                                           must be char or varchar type
                                                           and <= 64 chars long */
                       , IN value_name VARCHAR(64)  /* val values field name */
                       )
pivot:BEGIN
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET @error := 1;
    SET @error := 0;
    SELECT character_maximum_length
        INTO @maxlen
        FROM information_schema.columns
        WHERE table_schema = schema_name
          AND table_name = table_name
          AND column_name = key_name
          AND data_type IN ('char', 'varchar');
    IF @error OR !@maxlen OR @maxlen IS NULL THEN
        SELECT '@error OR @maxlen=0 OR @maxlen IS NULL', @error, @maxlen;
        LEAVE pivot;
    END IF;
    DROP TEMPORARY TABLE IF EXISTS temp_pivot;
    SET @sql := CONCAT('CREATE TEMPORARY TABLE temp_pivot (key_name VARCHAR(',
                       @maxlen,
                       ')) ENGINE=Memory SELECT DISTINCT `',
                       key_name,
                       '` key_name FROM `',
                       schema_name,
                       '`.`',
                       table_name,
                       '`;');
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DROP PREPARE stmt;
    SELECT GROUP_CONCAT(CONCAT( ', MAX(CASE `',
                                key_name,
                                '` WHEN ''',
                                temp_pivot.key_name,
                                ''' THEN `',
                                value_name,
                                '` END) `',
                                temp_pivot.key_name,
                                '`') SEPARATOR '')
        INTO @sql
        FROM temp_pivot;
    DROP TEMPORARY TABLE temp_pivot;
    SET @sql := CONCAT('SELECT `',
                       id_name,
                       '`',
                       @sql,
                       ' FROM `',
                       schema_name,
                       '`.`',
                       table_name,
                       '` GROUP BY `',
                       id_name,
                       '`;');
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DROP PREPARE stmt;
    SET @error := NULL;
    SET @maxlen := NULL;
    SET @sql := NULL;

END pivot@@;

DELIMITER ;

Usage example:

/* USE test; */
DROP TABLE IF EXISTS testtab;
CREATE TABLE testtab(id INT, `key` VARCHAR(16), val INT);
INSERT INTO testtab (id, `key`, val)
VALUES (1,'key1',11),
       (1,'key2',12),
       (1,'key3',13),
       (2,'key1',21),
       (2,'key2',22),
       (2,'key4',24),
       (3,'key1',31),
       (3,'key2',32),
       (3,'key3',33),
       (3,'key4',34);
SELECT * FROM testtab;

CALL pivot('test', 'testtab', 'id', 'key', 'val');

DROP PROCEDURE pivot;
DROP TABLE testtab;


What are the Pros and Cons of assigning value for ids from an offset? (Ex: 100,200,1000) Pre-dominantly, everybody uses the int data-type or it's variants tinyint,smallint and mediumint to generate a unique value for the id field of their database table(s).

I have seen so many questions, asking How to start auto-increment from a value (offset) ? When it comes to generating ids for orders table in specific, I feel everybody wants to start off from an offset.

So, in this context, my questions are:

Is it more of a business rule? If so, how does a business rule make sense while generating numbers?
If it's an individual's decision then what could be the possible elements  to be considered while deciding to assign id's value from an offset??
Why not start from 1 for all the ids and what is the need to start assigning ids from an offset? What problem does it solve? What are the Pros and Cons of doing so?

There are a variety of reasons why you may want an offset. Please be aware, some of these reasons are not necessarily good DBA practice.

As dnoeth mentioned, your primary key may be used in some other business process such as an order number. In that case, you don't want your customers thinking they are only the 11th order!  Being order number 1100011 is much better.
You may have a old system that is in read only mode.  It has all your old customers in it with their primary keys, and you want the new system to pick up where it left off.
Same idea, you know you will be importing PKS of a certain value at some point in the future, so you start with ones you know won't cause a collision.

Personally, the only time I offset is to avoid PK collisions. Sure, there was that one time when I was younger that I just though starting at PK ID 10000 was cool, but that was a long time ago.

Really, there are no Cons of doing so.  Except for some nominal space issues between data types and potentially losing the ability to assign PKs up to the data type limit, the database doesn't care if it starts at 1 or 1,000,000. It is going to index it all the same.


Repairing corrupt SQL Server database backup? I'm looking at a problem for someone who was backing up to a cloud-based service. Apparently the customer's server crashed while it was performing some maintenance on the cloud-based service's datastore, and this left the datastore in a corrupted state. At the same time, the customer's server was unrecoverable due to the crash.

The cloud-based service's developers had to write a script to find chunks of data that belonged to a database backup and stitch them together into a file.

This resulted in a backup (.bak) file that I can tell has data in it (I can run 'strings' on it and see a header, along with file names, table names, rows of data, etc), but won't be processed by SQL Server. I get:

restore headeronly from disk = 'c:\restore.bak'

Msg 3241, Level 16, State 0, Line 1
The media family on device 'c:\restore.bak' is incorrectly formed. SQL Server cannot
process this media family.
Msg 3013, Level 16, State 1, Line 1
RESTORE HEADERONLY is terminating abnormally.

Both the source instance on the machine that died and the one I'm testing the restore on are SQL Server 2008 R2, so unfortunately version mismatches aren't the source of the error.

I compared with a valid backup file of another database in a hex editor (xvi32) and noticed that the valid backup file began with the string 'tape' at byte 0, but the corrupt one had some other data with no discernible meaning for 158 bytes before that. I deleted 158 bytes from the corrupt backup up to the string 'tape' and that got me a little farther. Now 'RESTORE FILELISTONLY' worked, whereas before it threw the same error as above.

'RESTORE HEADERONLY' now displays 'incomplete' instead of throwing error 3013. If I trace it in Process Monitor, I can see it tries to read 512 bytes at offset 7168, then tries to read another 512 bytes at an offset past the end of the file. The file is almost certainly incomplete; it was originally 95.8 MB according to a reporting tool in the cloud-based service, but is only 92.95 MB on my filesystem.

I tried forcing a restore on the partially repaired (?) file:

restore database corrupt from disk = 'c:\restore_partiallyrepaired.bak' with continue_after_error, move 'corrupted' to 'c:\corrupt.mdf', move 'corrupted_log' to 'c:\corrupt.ldf'

This made SQL Server crash but gave a clue:

Date        3/3/2012 9:52:06 AM
Log     SQL Server (Archive #1 - 3/3/2012 9:52:00 AM)
Source      spid55
Message
The backup data at the end of "c:\restore_partiallyrepaired.bak" is incorrectly formatted.
Backup sets on the media might be damaged and unusable. To determine the backup sets on
the media, use RESTORE HEADERONLY. To determine the usability of the backup sets, run
RESTORE VERIFYONLY. If all of the backup sets are incomplete, reformat the media using
BACKUP WITH FORMAT, which destroys all the backup sets.

 RESTORE detected an error on page (48:538976288) in database "corrupt" as read from the
 backup set.

 Hit Fatal Error: Server is terminating

I tried various methods for 'hack-attaching' the 42.5 MB MDF that the restore produced, but it was unrepairable using DBCC CHECKDB.

At this point I'm going to send this back to the cloud-based service's support team with my findings and see if this gives any clues to them about how to extract a non-corrupt file.

I guess my questions are:

1) Is there anything else I can try here?

2) Is there anyone you know of who could help on a billable basis? For example, could MS support or some ex-MS consultants hack this backup file into a usable format?

Thanks!

I do know a consultant who does this kind of work, but it's nowhere near cheap - think mid-five-figures to start the project, paid in advance.  It's not for the faint of heart and you only want to go that route as a last resort.


Merging two rows into one by creating two new columns based on criteria from same table (SQL) the data that I have looks like this

name      year      quarter      type        score
a         2016         Q1         x           40
a         2016         Q1         y           50

I want to write a statement that will merge these two rows into one column output based on the name year and quarter regardless of type by creating two new columns, or one new column. The output that I would like is:

name      year      quarter      type        score      x_score      y_score
a         2016        Q1          x           40          40           50

I tried writing some subqueries, but could not understand how to effectively do this.

Thanks in advance

Here is one way:

SELECT name, year, quarter, type, coalesce(x_score, y_score) score, x_score, y_score
FROM (
   select name, year, quarter, min(type) type
      , min(case when type='x' then score else null end) x_score
      , min(case when type='y' then score else null end) y_score
   from t1
   group by name, year, quarter
);

Using this setup data:

drop table t1;
create table t1 (name varchar2(1), year number(4), quarter Varchar2(2), type varchar2(1)
   , score number(3));
insert into t1 (name, year, quarter, type, score) values ('a',2016,'Q1','x',40);
insert into t1 (name, year, quarter, type, score) values ('a',2016,'Q1','y',50);
insert into t1 (name, year, quarter, type, score) values ('b',2016,'Q1','x',40);
insert into t1 (name, year, quarter, type, score) values ('a',2017,'Q1','x',40);
commit;
select * from t1;


When to switch from MyISAM to InnoDB? We have a MySQL 5.0 server running all tables as MyISAM. We have two slaves that, in the last month, we have upgraded to MySQL 5.5. Their tables are also still MyISAM.

My original plan was to upgrade the 5.0 master to 5.5 and then convert to InnoDB. Now I'm wondering, would it make more sense to start before the upgrade? I've been wary to since I've heard that InnoDB under 5.5 is much faster than InnoDB under 5.0. Is this unfounded, and should I start now?

If you need concurrency of heavy UPDATEs and INSERTs, you will want InnoDB

If you need deadlock resolution, you will want InnoDB

If you want a storage engine that caches both data and indexes, you will want InnoDB

If you want to access multiple CPUs effective, you will want InnoDB (and tune it to do so)

Please refer to my past articles on InnoDB:

Feb 12, 2011 : How do you tune MySQL for a heavy InnoDB workload?
Apr 14, 2011 : What are the main differences between InnoDB and MyISAM?
Aug 04, 2011 : Optimizing InnoDB default settings
Oct 05, 2011 : Query runs a long time in some newer MySQL versions
Dec 19, 2011 : Which first: upgrade mysql version or convert storage engine?
Jan 09, 2012 : Any gotchas at all with converting from MyISAM to InnoDB?
Jan 27, 2012 : importing myisam 5.0 database into a 5.5 innodb server
Jan 18, 2012 : Insert-heavy InnoDB table won't use all my CPU
Jan 21, 2012 : Is InnoDB Engine up to speed against Memory Engine?
Jul 16, 2012 : decreased performance of stored procedure when migrated from mysql server 5.0 to 5.5

You said you heard that MySQL 5.5 is faster than MySQL 5.0. Out of the box, that's not true. YOU MUST TUNE MySQL 5.5 TO GET GREAT PERFORMANCE OUT OF IT !!! Here are my past articles on that subject:

Jun 19, 2011 : How do I properly perform a MySQL bake-off?
Nov 24, 2011 : Why mysql 5.5 slower than 5.1 (linux,using mysqlslap)
Jul 18, 2012 : decreased performance of stored procedure when migrated from mysql server 5.0 to 5.5

You mentioned MyISAM being faster than InnoDB. If there are a lot of wide VARCHAR columns in the MyISAM with the default ROW_FORMAT=Dynamic (Default), that can degrade MyISAM performance. Saying one storage engine is faster than the other depends on the application, read/write I/O ratio, storage engine tuning.

See my posts around this subject

Sep 20, 2011 : Best of MyISAM and InnoDB
April 12, 2012 : Mysql: Insert performance INNODB vs MYISAM (Not My Post, but good answers are given)
May 02, 2012 : Which mysql storage engine to choose?
May 03, 2012 : Which is faster, InnoDB or MyISAM?

Addressing your issue of whether to convert now or later to InnoDB:

In the fifth link I provided ( Which first: upgrade mysql version or convert storage engine? ), I recommend converting first. However, in your particular case, I would recommend converting later. The reason is simple: You are moving the data to a different server. MyISAM tables are very easy to port. Doing an rsync or scp of /var/lib/mysql is more expedient (faster) that converting to InnoDB because the data will be slightly (sometimes significantly) big when existing as InnoDB.

There are two things you will need to do:

NEED #1

If you rysnc/scp /var/lib/mysql you will overwrite /var/lib/mysql/mysql. Therefore, I propose you move your data like this:

STEP01) On the MySQL 5.5 machine, mkdir /root/mysql_schema
STEP02) On the MySQL 5.5 machine, cp /var/lib/mysql/mysql/* /root/mysql_schema/.
STEP03) rsync or scp /var/lib/mysql from MySQL 5.0 Server to MySQL 5.5 Server
STEP04) On the MySQL 5.5 machine, cp /root/mysql_schema/* /var/lib/mysql/mysql/.
STEP05) Get the grants from MySQL 5.0 machine as pure SQL and save the file as MySQL50Grants.sql

Jul 26, 2011 : Restoring an old backup to latest MySQL release (See the CAVEAT)
Jan 27, 2012 : importing myisam 5.0 database into a 5.5 innodb server
Jun 13, 2012 : Fastest way to move a database from one server to another

STEP06) Execute the MySQL50Grants.sql on the MySQL 5.5. Server

NEED #2

You need a straightforward script to convert MyISAM to InnoDB. Please run the following on the MySQL 5.5 DB Server :

echo "SET SQL_LOG_BIN = 0;" > /root/ConvertMyISAMToInnoDB.sql
MYSQL_CONN="-u... -p..."
mysql ${MYSQL_CONN} -AN -e"SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=InnoDB;') InnoDBConversionSQL FROM information_schema.tables WHERE engine='MyISAM' AND table_schema NOT IN ('information_schema','mysql','performance_schema') ORDER BY (data_length+index_length)" > /root/ConvertMyISAMToInnoDB.sql
less /root/ConvertMyISAMToInnoDB.sql

When you are satisfied with the conversion script, login to mysql on MySQL 5.5 and run

mysql> source /root/ConvertMyISAMToInnoDB.sql

Give it a Try !!!


get all dates in the current month i have a query in MySQL which serves me very well by getting all the records within the current month;

SELECT date_field,val FROM MY_TABLE WHERE date_field>=(CURDATE()-INTERVAL 1 MONTH);

The above query works well. so if this month we only had two records and 28 days it will bring only two records.

date_field | val
========================
2015-02-08 | 567
2015-02-09 | 345

But i want the number of records returned to be exactly the same as the number of days of the current month. if the current month has 28 days and only had two records it should bring;

date_field | val
========================
2015-02-01 | 0
2015-02-02 | 0
2015-02-03 | 0
2015-02-04 | 0
2015-02-05 | 0
2015-02-06 | 0
2015-02-07 | 0
2015-02-08 | 567
2015-02-09 | 345
2015-02-10 | 0
2015-02-11 | 0
2015-02-12 | 0
2015-02-13 | 0
2015-02-14 | 0
2015-02-15 | 0
2015-02-16 | 0
2015-02-17 | 0
2015-02-18 | 0
2015-02-19 | 0
2015-02-20 | 0
2015-02-21 | 0
2015-02-22 | 0
2015-02-23 | 0
2015-02-24 | 0
2015-02-25 | 0
2015-02-26 | 0
2015-02-27 | 0
2015-02-28 | 0

How can i modify my query to achieve the above result?

You can create a dynamic table of dates for the current month

SELECT date_field
FROM
(
    SELECT
        MAKEDATE(YEAR(NOW()),1) +
        INTERVAL (MONTH(NOW())-1) MONTH +
        INTERVAL daynum DAY date_field
    FROM
    (
        SELECT t*10+u daynum
        FROM
            (SELECT 0 t UNION SELECT 1 UNION SELECT 2 UNION SELECT 3) A,
            (SELECT 0 u UNION SELECT 1 UNION SELECT 2 UNION SELECT 3
            UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7
            UNION SELECT 8 UNION SELECT 9) B
        ORDER BY daynum
    ) AA
) AAA
WHERE MONTH(date_field) = MONTH(NOW());

NOTE: If you cut-and-paste the above query as is, it will generate the whole month for you

You then LEFT JOIN this to your original query

SELECT
    AAA.date_field,
    IFNULL(BBB.val,0) val
FROM
(
    SELECT date_field
    FROM
    (
        SELECT MAKEDATE(YEAR(NOW()),1) +
        INTERVAL (MONTH(NOW())-1) MONTH +
        INTERVAL daynum DAY date_field
        FROM
        (
            SELECT t*10+u daynum FROM
            (SELECT 0 t UNION SELECT 1 UNION SELECT 2 UNION SELECT 3) A,
            (SELECT 0 u UNION SELECT 1 UNION SELECT 2 UNION SELECT 3
            UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7
            UNION SELECT 8 UNION SELECT 9) B ORDER BY daynum
        ) AA
    ) AA WHERE MONTH(date_field) = MONTH(NOW())
) AAA LEFT JOIN (SELECT date_field,val FROM MY_TABLE) BBB
USING (date_field);

Give it a Try !!!


Restore from SQL Server 2008 R2 to SQL Server 2014 fails I have a SQL Server database backup of SQL Server 2008 R2. I want to restore that backup file that is around 40 GB on to a Microsoft SQL Server 2014 Express Edition (64-bit) and it runs till 95% and then gives me this error:

  CREATE DATABASE or ALTER DATABASE failed because the resulting
  cumulative database size would exceed your licensed limit of 10240 MB
  per database

You will not be able to restore a database from Microsoft SQL Server 2008 (paid version) to Microsoft SQL Server 2014 Express (Free version) due to its database file limit.

For others who need reference

SQL Server Express versions have a hard size limit on the database size.

I have listed them below from 2000-2008_r2

MSDE "SQL Server 2000" 2gig free limit
SQL Server 2005 4Gig free limit
SQL Server 2008 4gig free limit.
SQL Server 2008 R2 and newer: 10gig free limit.

Please upgrade to SQL Server 2014 (Paid version) to resolve this issue.


Applying user-defined fields to arbitrary entities Currently we have an old (rather crude) system that has user-defined fields, which are mapped against rows in arbitrary tables. This was an after-the-fact modification based on a customer request, and it wasn't really designed to scale well. Our system has around 60 different types of entities, which makes things even more complicated. Essentially the implementation looks like this:

USER_DEFINED_FIELDS:

UDF_ID         int
ENTITY_TYPE    int
UDF_NAME       nvarchar(64)
UDF_DATA_TYPE  int
UDF_FORMAT     nvarchar(16)
UDF_MASK       nvarchar(24)
UDF_FLAGS      int

UDF_VALUES_NUMBER:

UDF_ID         int
ENTITY_ID      int
VALUE          int
MODIFIED       datetime

UDF_VALUES_TEXT:

UDF_ID         int
ENTITY_ID      int
VALUE          nvarchar(255)
MODIFIED       datetime

etc...

This gets nice and fun when we generate our own ways to index compound primary keys, but that's another DailyWTF-worthy story.

Obviously this is pretty hideous, and leads to some spectacularly horrific queries being generated, but it's worked alright for now because we limit each entity to a maximum of 5 user-defined fields. As a quick disclaimer, I wasn't with the company when this design decision was made! ;)

Anyway, we're about to start a shiny new project and will inevitably need a better way of doing this, with no restrictions on the number of UDFs we can apply to entities, increased performance, and less horror in the generated query department. If we could run a NoSQL solution like Mongo or Redis I'd be happy and wouldn't need to bother you all, but sadly that's not an option. Instead, I need a way to do this from within SQL Server 2008 R2.

So far, I've come up with the following options:

Individual UDF table for each entity type, with identical structures.

Benefits: Queries are faster and simpler, solves the compound index problem, can search by UDF.
Downsides: Table versioning is more cumbersome, lots of extra tables, difficult to index across similar entity types (e.g. purchase orders and sales orders)

Binary blob UDF column in every entity's table.

Benefits: Really fast: no fetch overhead, no extra JOINs.
Downsides: Can't search by UDF, extra proprietary code required.

XML column in every entity's table.

Benefits: Fast like binary blobs, seems to be indexable.
Downsides: No idea how they work - looks complicated!

So, do any of these ideas have merit? Is there a better alternative?

The immediate question is "what do you mean by 'user defined' fields?"  Who defines these?  For purpose of this I am assuming that these are fixed for each table type by a small number of users (administrators) who have special access.

One thing I have done in the past is to have separate UDF tables for each entity type, but instead of separate rows per value, allow stored procedures to alter the columns on those UDF tables, so you can essentially join straight across, 1:1.  This approach I call "semi-EAV."  I think have catalogs like your user_defined_fields table to help the app generate queries.

Of the methods you are discussing, I would suggest using XML if my semi-EAV approach is not sufficient.  The tradeoff here is that semi-EAV allows you to do more rigorous schema validation using the tools you are familiar with in SQL.  The advantage of XML is that you can have a great deal of additional flexibility that this approach doesn't allow.

As a note, with LedgerSMB, we are talking about moving from semi-EAV for custom values to JSON, but we are on PostgreSQL.  XML might have been an option as well.


Maximum normalization I have been studying the normal forms rules to guide me with database design and data storage, the problem I am having is that once I optimize for the first normal form (1NF), I figured I may well just go the whole way(even though not mandatory).

However a occurrence showed in my design that I haven't came across in examples online or books.

A example concept.

We want to store all data about the some car variations.

We must store values make, series, model, start year, end year.

Unormalized

+------+--------+-------+------------+----------+
| make | series | model | start year | end year |
+------+--------+-------+------------+----------+
| BMW  |   5    |  E12  |    1972    |   1981   |
| BMW  |   5    |  E28  |    1981    |   1988   |
| BMW  |   5    |  E34  |    1988    |   1996   |
| BMW  |   5    |  E39  |    1995    |   2004   |
+------+--------+-------+------------+----------+

Normalized

car_make

+----+------+
| id | make |
+----+------+
| 1  | BMW  |
+----+------+

car_series

+----+--------+---------+
| id | series | make_id |
+----+--------+---------+
| 1  |   1    |    1    |
| 2  |   3    |    1    |
| 3  |   5    |    1    |
| 4  |   7    |    1    |
+----+--------+---------+

car_model

+----+-------+------------+----------+-------- --+
| id | model | start year | end year | series_id |
+----+-------+------------+----------+-----------+
| 1  |  E12  |    1972    |   1981   |     3     |
| 2  |  E28  |    1981    |   1988   |     3     |
| 3  |  E34  |    1988    |   1996   |     3     |
| 4  |  E39  |    1995    |   2004   |     3     |
+----+-------+------------+----------+-----------+

One problem arises here in the last table, should I also include a column make_id in the car_model table?

It is beneficial as I would not have to join car_series, then select make_id, to select make by id, however I believe this may not be sticking to normalization, because I would be repeating data(even though it is a id its still repeat).

Can this design be normalized further?

On another note with regards to the start year, end year handling, How would you professionals do this? I am curios if there is any range tricks to enforce integrity(although the E39 overlaps, lets speak as though no dates overlap).

Thanks in advance for any thoughts.

No, you should not include a column make_id in the car_model table, it is implicitly defined by the series_id.  If you need to see the make details you could make a view that looks like your un-normalized table.

No this design cannot be normalised further.

To force the year ranges to be non-overlapping you could add a trigger (as MySql does not support check constraints) to ensure that start_year >= max(end_year) and end_year >= start_year.

The trigger would look something like this:

create trigger trg_car_model_date_range_unique before insert on car_model
for each row
begin
    if new.start_year < (select max(end_year) from car_model) then
        signal sqlstate '45000' set message_text = 'Date range is not unique';
    end if;
    if new.end_year < new.start_year then
        signal sqlstate '45000' set message_text = 'Date range is invalid';
    end if;
end


Select from arrays in a way similar to IN I have this table and data:

CREATE TABLE sal_emp (
    name            text,
    pay_by_quarter  integer[]
);

INSERT INTO sal_emp
VALUES
   ('Bil'  , '{1, 2, 3, 4}'),
   ('Bill2', '{5, 6, 7, 8}');

I can run query:

select name from sal_emp where pay_by_quarter @> ARRAY[1,4];

And got "Bil".

Is there a way to run a query like:

select name from sal_emp where pay_by_quarter @> ARRAY[1,4,5]

and get "Bil", "Bill2"?

You can use overlap (&&) operator which means "have elements in common".

SELECT
    name
  FROM sal_emp
  WHERE
    pay_by_quarter && ARRAY[1,4,5]

Documentation link is here: Array Operators


How to find resource intensive SQL operations in Postgresql? High CPU usage spike I am getting frequent high CPU usage alerts from production DB server. As I want to investigate more into this by my own, I have collected the running DB queries for that high CPU usage time period.

SELECT * FROM pg_stat_activity;

From the collected data, observed that there is one frequently accessed table with UPDATE operation. From further investigation I came to know that the table is the most frequently accessed one in the DB and there is nothing possibly resource intensive.

Following are the details I got from EXPLAIN for that query,

                                  QUERY PLAN
-------------------------------------------------------------------------------
 Update on foo_table  (cost=0.00..1271.14 rows=1 width=55)
   ->  Seq Scan on foo_table  (cost=0.00..1271.14 rows=1 width=55)
         Filter: (id = 2::bigint)
(3 rows)

So far the only certain data I have is the time period when this high CPU usage triggering operation is happening. Though unable to figure out which query is taking too much time to process.

I have tried following queries, I couldn't find convincing stats.

SELECT relname, idx_tup_fetch + seq_tup_read as TotalReads from pg_stat_all_tables WHERE idx_tup_fetch + seq_tup_read != 0 order by TotalReads;

SELECT * FROM pg_stat_all_tables ORDER BY seq_scan DESC;

I am helpless as there is no way for me to figure out what is happening, like is it normal because the work load or is it indexing related issue or query optimization would solve it,etc.

DB spec:
Size: 150+ GB
CPU: 8 core
RAM: 16 GB
Storage: SSD with no swap.

The problem was with the indexing of few tables. I was able to identify those tables with Read stats by performing the following query.

SELECT relname, idx_tup_fetch + seq_tup_read as TotalReads from pg_stat_all_tables WHERE idx_tup_fetch + seq_tup_read != 0 order by TotalReads desc LIMIT 10;


Why do I get "The initial snapshot for publication is not yet available."? We are using Transactional Replications with updatable subscriptions. 1 publisher, 1 subscriber.

SQL Replication started encountering a constraint error on 3/6 at 10AM because people at two different offices entered data that violated a unique constraint. Totally expected.

However, at 12:08PM on 3/6, SQL Replication stopped reporting that error and instead said:

"The initial snapshot for publication 'TheDB' is not yet available."

Why would it stop retrying replication and instead start throwing this error? Now we need to regenerate the snapshot.

No DB backups or restores were performed at this time
The snapshot was successfully applied at the subscriber months ago when replication was initially set up.
There is a message in the log from 12:07 on 3/6 saying

[000] Request to run job COTOPSVR\TheDB-TheDB-COTCPSVR\DB-3 (from User CTOB\Administrator) refused because the job is already running from a request by User sa

No one manually initiated any job or did anything at all at 12:06PM.

Any ideas? I know how to fix this...I want to prevent this from happening again.

With basic Transactional Replication there are a variety of agents.

Helpful MSDN article

There is the Snapshot Agent which is responsible for the initial snapshot of data and schema. This can be scheduled to run at regular intervals (I had mine scheduled for every 12 hours).

There is the Log Reader Agent, which reads the transaction logs and sends those which are marked for replication on to the distributor.

Finally, there is the Distribution Agent which is responsible for moving the snapshot(s) and the transactions from the distributor to the subscriber(s).

Your error is telling you that it cannot apply transactions that are marked for replication to the subscriber because the initial snapshot (and/or any of the subsequent snapshots) have not yet been applied. You need to troubleshoot why this snapshot is not being applied. There are many reasons why it could be happening.

Has the Distribution Agent lost permissions to the directory that contains the snapshot files?

Is there some blocking activity on the subscriber that is causing the snapshot to be applied slowly?

Have you overloaded the distributor and/or subscriber by pushing all articles from a large published database to all subscribers at the same time?


How to Determine Optimal Disk Cluster Size for Firebird 2.5 I'm using Firebird 2.5.6 Classic Server on a Windows 7 64-bit machine. We're moving the database to a separate disk (32GB NTFS Volume) and are now provisioning that disk. My question is, what should be taken into account when deciding disk cluster size? The goal being to get optimal performance when the database is reading from disk. I'm hoping you all can provide information on the relevant factors so that I can make my own decision, and so this question will be helpful to others in different situations.

Should the cluster size (allocation unit size) match the Database Page Size? I'm not very knowledgeable in how Firebird uses the disk though, maybe it makes more sense to use a bigger cluster size? I read this question on the same topic but about SQL Server. I'm unsure if Firebird SQL behaves similarly to SQL Server when reading from disk, or if there are other factors to take into account.

A good question to ask here would be: are you using the server for OLTP or OLAP type queries?

If you are using OLTP and your queries are carefully crafted to retrieve and modify only one record at a time, then use a 4kb block size (I'm assuming that in OLTP, you don't have any records with record sizes in excess of 4kb?).

Personally, I would go with what it says here. This article recommends a 4kb block size for Interbase systems - I know they're not the same, but they are "kissing cousins..." :-). Unless you have a compelling reason not to, then choose 4kb.

Despite this, by far the most important thing in this article is what it says at the end:

  Although 4KB seems to be the best page size for most databases, the
  optimal size depends on the structure of the specific metadata and the
  way in which applications access the data. For this reason, you should
  not consider the 4KB page size guideline to be a magic value. Instead,
  you should perform testing with your application and database under
  several different page sizes to analyze which configuration gives the
  best performance.

This is a very nicely phrased, technical way of saying YMMV, make sure and kick the tyres before you buy! FWIW, you could also look at this article - it popped up! BTW, not using Firebird 3? Check what the article says about 3.

Your question got me interested - unfortunately, there aren't many Firebird benchmarks on the internet - but I did find this about Postgresql. Again, not the same thing, but it is an MVCC system and its numbers might indicative?


If function behave differently in MySQL 5.5.49 I'm running MySQL 5.5.49 on my Windows 7 32-bit

I'm using a stored procedure:

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_testing`(
IN in_param VARCHAR(50)
, OUT out_1 VARCHAR(50)
)
BEGIN

    DECLARE v_no_por_cabang VARCHAR(50);
    DECLARE v_no_por_cabang1 VARCHAR(50);
    DECLARE v_no_por_cabang2 VARCHAR(50);

    SET v_no_por_cabang = in_param;

    SET v_no_por_cabang1 = IF(v_no_por_cabang = 0, NULL, v_no_por_cabang);

    SET v_no_por_cabang2 = IF(v_no_por_cabang = '0', NULL, v_no_por_cabang);

    SELECT v_no_por_cabang1;
    SELECT v_no_por_cabang2;

END$$

and I'm calling it like this:

CALL sp_testing('POR1',@out1);

The output for v_no_por_cabang1 is NULL. The output for v_no_por_cabang2 is POR1.

Why does the IF function behave like that? In my previous test in MySQL 5.5.xx on Ubuntu 12 the same stored procedure didn't behave like that. It returned POR1 for v_no_por_cabang1.

Anybody know why?

It's because in the comparison v_no_por_cabang = 0 you're converting v_no_por_cabang implicitly to a number. When you use single-quotes like in v_no_por_cabang = '0' you're correctly comparing two strings.

MySQL also tells you so, when you execute show warnings; afterwards. Result:

+---------+------+------------------------------------------+
| Level   | Code | Message                                  |
+---------+------+------------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'POR1' |
+---------+------+------------------------------------------+

Fun fact: When your string starts with a number, MySQL converts the first number part of the string to a number.

root@localhost:playground > CALL sp_testing('1POR1',@out1);

+------------------+
| v_no_por_cabang1 |
+------------------+
| 1POR1            |
+------------------+
1 row in set (0.01 sec)

+------------------+
| v_no_por_cabang2 |
+------------------+
| 1POR1            |
+------------------+
1 row in set (0.01 sec)

Query OK, 0 rows affected, 1 warning (0.01 sec)

root@localhost:playground > CALL sp_testing('0POR1',@out1);
+------------------+
| v_no_por_cabang1 |
+------------------+
| NULL             |
+------------------+
1 row in set (0.00 sec)

+------------------+
| v_no_por_cabang2 |
+------------------+
| 0POR1            |
+------------------+
1 row in set (0.00 sec)


Using PostgreSQL to hold an array of data I am trying to use a postgreSQL data base column to store values from an array (or point to an enum table which does this).  I will get an array of data like ['one','two','five']. The array can contain any amount of items from 0 to 10.
I have an enum table with these values:
id    value
1     one
2     two
3     three
4     four
5     five

I want my database cell to point to all of the values that are contained in the array.  Is this even possible or is there a common way to structure database tables to fix this issue.
So my desired solution when passed the array ['one','two','five'] would be to have a single cell in my database hold the enum pointers 1, 2, 5.

Create a view on the table that joins with the enum table and shows an array of text.
Then create an INSTEAD OF INSERT OR UPDATE trigger on the view that translates the strings to numbers and stores the number array in the underlying table.


Nested loops estimate way too low, causes tempdb spills I'm working on tuning a query, and I think the main problem right now is that there is a Nested Loops step early on with a row estimate that is way too low, causing too little memory to be allocated and downstream steps to spill to tempdb. The troublesome step is the one in the green box below.

https://www.brentozar.com/pastetheplan/?id=Sk8E-6YAM

The two inputs into the Nested Loops both have accurate row estimates of 2.4M rows, but the output of that step is estimated at only 37 rows, while the actual is 2.4M.

All three Table Spool steps below have the exact same estimated vs actual as well, which makes me think they're getting their estimate from the Nested Loops. Every branch spills to tempdb in a Sort step.

I'm thinking if I can just get the estimated rows out of the Nested Loops corrected, it will not only prevent spills on the top branch, but that the Table Spools will also inherit the correct estimate, get a sufficient memory grant, and also not spill.

SQL Server 2016 SP2 with the legacy CE on.

Here's the query

SELECT  Object18.Column1,
        Object18.Column3,
        Function1(Object19.Column12) AS Column13,
        Function2(Object19.Column12) AS Column14,
        Function3(DISTINCT (CASE WHEN Object19.Column15 = ? THEN Object18.Column6 END)) AS Column16,
        Function3(DISTINCT (CASE WHEN Object19.Column17 = ? THEN Object18.Column6 END)) AS Column18,
        Function3(DISTINCT Object18.Column6) AS Column19
from Object8 Object18
join Object20 Object19 on Object19.Column20 = Object18.Column7 and Object19.Column3 = Object18.Column3
where Object19.Column8 = ?
GROUP BY Object18.Column1, Object18.Column3
option (recompile)

I was able to get the estimates corrected by creating an index on the temp table after inserting. This solved the tempdb spills, but performance didn't improve much. Ultimately, a combination of separating the data query from the aggregation, and shuffling some other queries around to get maximum benefit of parallelization, combined to reduce the speed by 4x. Thanks everyone, especially Joe Obbish


SQL Server distribution database log file grows uncontrollably after full database backup We have a merge replication environment that is pushing to 8 subscribers. This is working fine. Our distribution database is setup in Simple recovery mode. We have a maintenance plan that will backup all database every day at 00:30. Once this process completes, the distribution log file grows over the next 30 minutes and absorbs all the remaining space on the hard drive (about 90GB)

What then happens is that the distribution database shows as "Recovery Pending" and we cannot do anything till we restart the machine. After this I can shrink the log file down to 2MB.

I have no idea why this is happening. The log file is running at about 10MB during the day. The database size is sitting at 15GB.

It sounds like you have a high volume of transactions happening with your replication. If so, that "clean up: distribution" job should probably be running far more often. It sounds to me like it's happening once a day. On my high transaction replication server I have it running every 15 minutes to keep just the latest 36 hours. That way it paces itself and doesn't bog down.

Here's an article that might help - http://www.sqlservergeeks.com/blogs/singhsatnam84/sql-server-bi/563/sql-server-controlling-growth-of-a-distribution-database


Force PostgreSQL clients to use SSL on Amazon RDS I'm running a PostgreSQL 9.4.x instance on Amazon RDS.  I can successfully connect to that server using SSL using a command line similar to this one:

psql "host=x.y.z.rds.amazonaws.com dbname=aaa user=bbb sslmode=verify-full"

How can I force clients to use SSL (to make eavesdropping harder)?  The following should fail to connect:

psql "host=x.y.z.rds.amazonaws.com dbname=aaa user=bbb sslmode=disable"

As far as I can tell, on Amazon RDS you can't edit pg_hba.conf, so I can't put hostssl entries in there.

This AWS blog post suggests how it could be done with MySQL, but I can't find links to similar information for PostgreSQL.

My motivation is to prevent myself (or other users) from accidentally transmitting sensitive information unencrypted across the network.

Looks like this has finally been added. From the docs:

  You can use the rds.force_ssl parameter to force connections to your PostgreSQL database to use SSL. The default value is 0 (off). When you set this parameter to 1 (on) and restart your instance, your database will refuse any non-SSL connections. This parameter also sets the PostgreSQL parameter ssl to on as well as modifies your instance's pg_hba.conf to support this SSL configuration.


Copy SQL Server DB into another instance without SA access I've two SQL Server databases and both of them are on cloud.

First server is source
Second server is destination

I only have a non SA access. And I'm using DBForge Studio for managing the database. I'm trying to copy the database from the first server to the second server through DBForge→Database Sync→Copy Database and then select copy (I'm following this article).

Note:
The database already exists in destination with same name, but no tables yet.

I got an error message when trying to copy:

  The EXECUTE permission was denied on the object 'xp_fileexist'.....

Then I try to copy & overide but I got the same error message.

So I try to use another way like export & import. I export the database from source server into .sql, but there are only queries for insert, it didn't serve the query for create table schema, and I tried to import into another file like .csv and .xsl but I got the same, it only contain data without schema.

Is there any better way to copy the database without SA access?

Is there any better way to copy the database without SA access?

Yes, you do not have to be sa or sysadmin member to perform the activity that you described, but following are the least privileges required to do so.:

You must be member of

Database role: db_ddladmin or Database role: db_owner at source server
Server role: dbcreator at destination server

You can verify current membership with following query (1=YES, 0=NO):

---- Source server
select  IS_MEMBER ('db_ddladmin' ) as Is_DDLAdmin,
        IS_MEMBER ('db_owner' ) as Is_DBOwner
go

---- Destination server
select  IS_SRVROLEMEMBER ('dbcreator' ) as Is_DBCreator
go

I haven't used dbForge studio, but using SSMS free tool provided by Microsoft, and while having permissions (as mentioned above), you can achieve what your looking for (export data along with objects into .sql format)

via  SSMS -> Tasks -> Generate script option. focus on following options at Advanced scripting options

Types of data to script = Data and schema
Script for server version = your desired version


How to resolve error ORA-19802 when starting up an Oracle database instance? I tried starting up my instance and received the error ORA-19802. Cannot use db_recovery_file_dest without db_recovery_file_dest_size. How can I resolve the problem?

Answer here: https://community.oracle.com/thread/2203255

reference answer:

You can always export, edit and import a pfile, even when the instance won't start like this:

SQL> create pfile = '/tmp/pfile' from spfile='+DATA/rac/spfilerac.ora';

edit the pfile with the text editor of your choice and import back:

SQL> create spfile='+DATA/rac/spfilerac.ora' from pfile='/tmp/pfile';**


utlrp.sql fails to execute I just created a docker image of Oracle 11g ee following this tutorial https://vitalflux.com/how-to-install-oracle-11g-ee-on-docker/ and loaded some tables inside.

The process worked fine and no errors happens during the load of a huge sql file (10K+) with sqlplus. But on the last step the process breaks. I could pinpoint the issue to the following line for recompilation:

@?/rdbms/admin/utlrp.sql

The error received is the following:

ERRORS DURING RECOMPILATION
---------------------------
                          0

Function created.

   p_schemaname  dbms_id;
                 *
ERROR at line 5:
ORA-06550: line 5, column 18:
PLS-00201: identifier 'DBMS_ID' must be declared
ORA-06550: line 5, column 18:
PL/SQL: Item ignored
ORA-06550: line 6, column 18:
PLS-00201: identifier 'DBMS_ID' must be declared
ORA-06550: line 6, column 18:
PL/SQL: Item ignored
ORA-06550: line 62, column 34:
PLS-00320: the declaration of the type of this expression is incomplete or
malformed
ORA-06550: line 62, column 12:
PL/SQL: SQL Statement ignored
ORA-06550: line 65, column 33:
PLS-00320: the declaration of the type of this expression is incomplete or
malformed
ORA-06550: line 64, column 12:
PL/SQL: Statement ignored

Function dropped.

I tried to find an answer on google but the best I could find is that there are some invalid objects. Which I couldn't find any.

SQL> SELECT owner,
  2         object_type,
  3         object_name,
  4         status
  5  FROM   dba_objects
  6  WHERE  status = 'INVALID'
  7  ORDER BY owner, object_type, object_name;

no rows selected

I am executing the process as sys with sysdba. Maybe the re-compilation is not necessary but I cannot remove it from the script.

A week ago I tried to do the same thing on a "real" Oracle 11g installation without docker. There the script worked. So I assume that there is something wrong with the container but I am not sure what exactly.

Did anybody encounter this issue before, do you have any ideas?

Thanks in advance

Version

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

DBMS_ID is a subtype in DBMS_STANDARD starting with version 12c.

DBMS_ID does not exist in 11g.

You have a messed up environment with 12c scripts and a 11g database.

The code that fails is in $ORACLE_HOME/rdbms/admin/reenable_indexes.sql, which is called by $ORACLE_HOME/rdbms/admin/utlprp.sql, which is called by $ORACLE_HOME/rdbms/admin/utlrp.sql.

This is an environmental issue but we do not know what you have done so far. In a properly created and configured environment this would not happen.

If you want to use 11g, look for a 11g image. There must have been someone desperate enough to create one. Like this one: docker-oracle-ee-11g. No, I haven't tested it.