Script to automate converting a clustered index on primary key (with foreign keys to it) column to non clustered?

Well, luckily for you, I did this very thing a few months back... I just never actually published it to my blog... Guess I should get to that at some point, but until then, here you go:

/***************************************************************************************************************
 *
 *  This Script will perform the following operations:
 *    1) Drop All RI
 *    2) Drop All PKEYS
 *    3) Recreate PKEYS as NONCLUSTERED
 *    4) Recreate ALL RI
 *
 *  To do this it will create a work table and populate it with the commands to execute then iterate through
 *  the work table to execute pregenerated commands.
 *
 ***************************************************************************************************************/

CREATE TABLE #workTable
(
    CommandID   INT IDENTITY(1,1),
    Command     VARCHAR(MAX)
)

SET NOCOUNT ON;

/***************************************************************************************************************
 *
 *  DROP FOREIGN KEYS
 *
 *  http://stackoverflow.com/questions/159038/can-foreign-key-constraints-be-temporarily-disabled-using-t-sql
 *
 ***************************************************************************************************************/

INSERT INTO #workTable (Command) VALUES ('PRINT ''-- DROP FOREIGN KEY CONSTRAINTS --''');

INSERT INTO #workTable (Command)
SELECT 'ALTER TABLE [' + sch.NAME + '].[' + OBJECT_NAME(fk.parent_object_id) + ']' + ' DROP  CONSTRAINT ' + '[' + fk.NAME + ']'
FROM sys.foreign_keys AS fk
INNER JOIN sys.schemas AS sch ON sch.schema_id = fk.schema_id
ORDER BY fk.NAME

INSERT INTO #workTable (Command) VALUES ('PRINT ''-- DROP FOREIGN KEY CONSTRAINTS -- COMPLETE'' + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)');

/***************************************************************************************************************
 *
 *  DROP CLUSTERED PKEYS
 *
 *  http://social.technet.microsoft.com/wiki/contents/articles/2321.script-to-create-or-drop-all-primary-keys.aspx
 *
 ***************************************************************************************************************/

INSERT INTO #workTable (Command) VALUES ('PRINT ''-- DROP CLUSTERED KEYS --''');

DECLARE @object_id int;
DECLARE @parent_object_id int;
DECLARE @TSQL NVARCHAR(4000);
DECLARE @COLUMN_NAME SYSNAME;
DECLARE @is_descending_key bit;
DECLARE @col1 BIT;
DECLARE @action CHAR(6);

SET @action = 'DROP';
--SET @action = 'CREATE';

DECLARE PKcursor CURSOR FOR
    select kc.object_id, kc.parent_object_id
    from sys.key_constraints kc
    inner join sys.objects o
    on kc.parent_object_id = o.object_id
    where kc.type = 'PK' and o.type = 'U'
    and o.name not in ('dtproperties','sysdiagrams')  -- not true user tables
    order by QUOTENAME(OBJECT_SCHEMA_NAME(kc.parent_object_id))
            ,QUOTENAME(OBJECT_NAME(kc.parent_object_id));

OPEN PKcursor;
FETCH NEXT FROM PKcursor INTO @object_id, @parent_object_id;

WHILE @@FETCH_STATUS = 0
BEGIN
    IF @action = 'DROP'
        SET @TSQL = 'ALTER TABLE '
                  + QUOTENAME(OBJECT_SCHEMA_NAME(@parent_object_id))
                  + '.' + QUOTENAME(OBJECT_NAME(@parent_object_id))
                  + ' DROP CONSTRAINT ' + QUOTENAME(OBJECT_NAME(@object_id))
    ELSE
        BEGIN
        SET @TSQL = 'ALTER TABLE '
                  + QUOTENAME(OBJECT_SCHEMA_NAME(@parent_object_id))
                  + '.' + QUOTENAME(OBJECT_NAME(@parent_object_id))
                  + ' ADD CONSTRAINT ' + QUOTENAME(OBJECT_NAME(@object_id))
                  + ' PRIMARY KEY'
                  + CASE INDEXPROPERTY(@parent_object_id
                                      ,OBJECT_NAME(@object_id),'IsClustered')
                        WHEN 1 THEN ' CLUSTERED'
                        ELSE ' NONCLUSTERED'
                    END
                  + ' (';

        DECLARE ColumnCursor CURSOR FOR
            select COL_NAME(@parent_object_id,ic.column_id), ic.is_descending_key
            from sys.indexes i
            inner join sys.index_columns ic
            on i.object_id = ic.object_id and i.index_id = ic.index_id
            where i.object_id = @parent_object_id
            and i.name = OBJECT_NAME(@object_id)
            order by ic.key_ordinal;

        OPEN ColumnCursor;

        SET @col1 = 1;

        FETCH NEXT FROM ColumnCursor INTO @COLUMN_NAME, @is_descending_key;
        WHILE @@FETCH_STATUS = 0
        BEGIN
            IF (@col1 = 1)
                SET @col1 = 0
            ELSE
                SET @TSQL = @TSQL + ',';

            SET @TSQL = @TSQL + QUOTENAME(@COLUMN_NAME)
                      + ' '
                      + CASE @is_descending_key
                            WHEN 0 THEN 'ASC'
                            ELSE 'DESC'
                        END;

            FETCH NEXT FROM ColumnCursor INTO @COLUMN_NAME, @is_descending_key;
        END;

        CLOSE ColumnCursor;
        DEALLOCATE ColumnCursor;

        SET @TSQL = @TSQL + ');';

        END;

    INSERT INTO #workTable (Command)
    SELECT @TSQL;

    FETCH NEXT FROM PKcursor INTO @object_id, @parent_object_id;
END;

CLOSE PKcursor;
DEALLOCATE PKcursor;

INSERT INTO #workTable (Command) VALUES ('PRINT ''-- DROP CLUSTERED KEYS -- COMPLETE'' + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)');

/***************************************************************************************************************
 *
 *  CREATE NONCLUSTERED PKEYS
 *
 *  http://social.technet.microsoft.com/wiki/contents/articles/2321.script-to-create-or-drop-all-primary-keys.aspx
 *
 * NOTE: Subsection where script determines if PKEY or not was modified to only create NONCLUSTERED pkeys
 *
 ***************************************************************************************************************/

INSERT INTO #workTable (Command) VALUES ('PRINT ''-- CREATE NONCLUSTERED PKEYS --''');

--SET @action = 'DROP';
SET @action = 'CREATE';

DECLARE PKcursor CURSOR FOR
    select kc.object_id, kc.parent_object_id
    from sys.key_constraints kc
    inner join sys.objects o
    on kc.parent_object_id = o.object_id
    where kc.type = 'PK' and o.type = 'U'
    and o.name not in ('dtproperties','sysdiagrams')  -- not true user tables
    order by QUOTENAME(OBJECT_SCHEMA_NAME(kc.parent_object_id))
            ,QUOTENAME(OBJECT_NAME(kc.parent_object_id));

OPEN PKcursor;
FETCH NEXT FROM PKcursor INTO @object_id, @parent_object_id;

WHILE @@FETCH_STATUS = 0
BEGIN
    IF @action = 'DROP'
        SET @TSQL = 'ALTER TABLE '
                  + QUOTENAME(OBJECT_SCHEMA_NAME(@parent_object_id))
                  + '.' + QUOTENAME(OBJECT_NAME(@parent_object_id))
                  + ' DROP CONSTRAINT ' + QUOTENAME(OBJECT_NAME(@object_id))
    ELSE
        BEGIN
        SET @TSQL = 'ALTER TABLE '
                  + QUOTENAME(OBJECT_SCHEMA_NAME(@parent_object_id))
                  + '.' + QUOTENAME(OBJECT_NAME(@parent_object_id))
                  + ' ADD CONSTRAINT ' + QUOTENAME(OBJECT_NAME(@object_id))
                  + ' PRIMARY KEY'
                  + ' NONCLUSTERED'
                  + ' (';

        DECLARE ColumnCursor CURSOR FOR
            select COL_NAME(@parent_object_id,ic.column_id), ic.is_descending_key
            from sys.indexes i
            inner join sys.index_columns ic
            on i.object_id = ic.object_id and i.index_id = ic.index_id
            where i.object_id = @parent_object_id
            and i.name = OBJECT_NAME(@object_id)
            order by ic.key_ordinal;

        OPEN ColumnCursor;

        SET @col1 = 1;

        FETCH NEXT FROM ColumnCursor INTO @COLUMN_NAME, @is_descending_key;
        WHILE @@FETCH_STATUS = 0
        BEGIN
            IF (@col1 = 1)
                SET @col1 = 0
            ELSE
                SET @TSQL = @TSQL + ',';

            SET @TSQL = @TSQL + QUOTENAME(@COLUMN_NAME)
                      + ' '
                      + CASE @is_descending_key
                            WHEN 0 THEN 'ASC'
                            ELSE 'DESC'
                        END;

            FETCH NEXT FROM ColumnCursor INTO @COLUMN_NAME, @is_descending_key;
        END;

        CLOSE ColumnCursor;
        DEALLOCATE ColumnCursor;

        SET @TSQL = @TSQL + ');';

        END;

    INSERT INTO #workTable (Command)
    SELECT @TSQL;

    FETCH NEXT FROM PKcursor INTO @object_id, @parent_object_id;
END;

CLOSE PKcursor;
DEALLOCATE PKcursor;

INSERT INTO #workTable (Command) VALUES ('PRINT ''-- CREATE NONCLUSTERED PKEYS -- COMPLETE'' + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)');

/***************************************************************************************************************
 *
 *  CREATE FOREIGN KEYS
 *
 *  http://stackoverflow.com/questions/159038/can-foreign-key-constraints-be-temporarily-disabled-using-t-sql
 *
 ***************************************************************************************************************/

INSERT INTO #workTable (Command) VALUES ('PRINT ''-- CREATE FOREIGN KEY CONSTRAINTS --''');

INSERT INTO #workTable (Command)
SELECT 'ALTER TABLE ' + const.parent_obj + '
    ADD CONSTRAINT ' + const.const_name + ' FOREIGN KEY (
            ' + const.parent_col_csv + '
            ) REFERENCES ' + const.ref_obj + '(' + const.ref_col_csv + ')'
FROM (
    SELECT QUOTENAME(fk.NAME) AS [const_name]
        ,QUOTENAME(schParent.NAME) + '.' + QUOTENAME(OBJECT_name(fkc.parent_object_id)) AS [parent_obj]
        ,STUFF((
                SELECT ',' + QUOTENAME(COL_NAME(fcP.parent_object_id, fcp.parent_column_id))
                FROM sys.foreign_key_columns AS fcP
                WHERE fcp.constraint_object_id = fk.object_id
                FOR XML path('')
                ), 1, 1, '') AS [parent_col_csv]
        ,QUOTENAME(schRef.NAME) + '.' + QUOTENAME(OBJECT_NAME(fkc.referenced_object_id)) AS [ref_obj]
        ,STUFF((
                SELECT ',' + QUOTENAME(COL_NAME(fcR.referenced_object_id, fcR.referenced_column_id))
                FROM sys.foreign_key_columns AS fcR
                WHERE fcR.constraint_object_id = fk.object_id
                FOR XML path('')
                ), 1, 1, '') AS [ref_col_csv]
    FROM sys.foreign_key_columns AS fkc
    INNER JOIN sys.foreign_keys AS fk ON fk.object_id = fkc.constraint_object_id
    INNER JOIN sys.objects AS oParent ON oParent.object_id = fkc.parent_object_id
    INNER JOIN sys.schemas AS schParent ON schParent.schema_id = oParent.schema_id
    INNER JOIN sys.objects AS oRef ON oRef.object_id = fkc.referenced_object_id
    INNER JOIN sys.schemas AS schRef ON schRef.schema_id = oRef.schema_id
    GROUP BY fkc.parent_object_id
        ,fkc.referenced_object_id
        ,fk.NAME
        ,fk.object_id
        ,schParent.NAME
        ,schRef.NAME
    ) AS const
ORDER BY const.const_name

INSERT INTO #workTable (Command) VALUES ('PRINT ''-- CREATE FOREIGN KEY CONSTRAINTS -- COMPLETE'' + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)');

SET NOCOUNT OFF;

/***************************************************************************************************************
 *
 *  Iterate through Work Table on the database
 *
 ***************************************************************************************************************/

--SELECT * FROM #workTable
DECLARE @Command VARCHAR(MAX)

DECLARE WorkTableCursor CURSOR
FOR
    SELECT Command
    FROM #workTable

OPEN WorkTableCursor

FETCH NEXT FROM WorkTableCursor
INTO @Command

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT @Command
    EXECUTE (@Command)

    FETCH NEXT FROM WorkTableCursor
    INTO @Command
END

CLOSE WorkTableCursor
DEALLOCATE WorkTableCursor

DROP TABLE #workTable


MS Access: Capture ListBox Scroll Event?

There is no such event handler available in Access.  Access controls are very limited compared to, for example, the .Net counterparts or other development environments.

I can't disagree with using "built in routines", but even if the scrollbar did expose more events, you'd still be trying to force the listbox to behave in a non-standard way.  Especially in Access you'll have better luck implementing something with specialized buttons as you've already mentioned.


Change default timezone to America Pacific

I used below query to convert UTC timezone to America Pacific. Field
value would be in timestamp(6).

CONVERT_TZ(field,'UTC','US/Pacific')


Building and analyzing trends in a database

Although many DBMS support XML storage and processing it is not the native format. To gain the greatest benefit from a move to a DB you must pull the data from their current files and store then in tables. This is known as an Extract, Transform, Load (or ETL) job. There are specific ETL tools available. The major vendors have them, and independent and open-source tools exist. There is, of course, an overhead to learning these tools and writing the ETL jobs. They are most useful when there is an on-going need to process new files into the future.

For one-off or ad hoc tasks the native capabilities within the DBMS itself often suffice. My suggestion is you create a staging table with a single column of type XML (if your chosen technology supports it). Load the file directly into here without any processing. Then use the DBMS's native XML tools to shred the XML and populate the "real" tables.

Next comes the question of what the destination tables should look like. Relational databases work best when table design is normalised. Simply reproducing to record / item structure will not suffice. It will be a nightmare to query and performance will be awful.

Table design and normalisation is part science and part craft. It depends on the inherent structure of the data and also the meaning users impart to the data. If all your records have the same number of items, and all the items have the same meaning across records you may be OK with a single table. There will be one column per item (properly named, NOT item1, item2 ...) and a row per record. If the current Excel sheet has a very regular, ordered, no-nonsense, one-value-per-cell layout you could start by reproducing that then refining as experience and confidence grows.


If you only have one drive, you won't gain performance by splitting it logically but you said you want to split the drive into logical drives for logs and data so if one runs out of room it will not cripple the others' content. However, if either your data drive or your log drive or your tempdb drive is out of space then you are down. I would instead keep on the same drive in order to maximize the space you do have. It really sounds like you have plenty of room for two 35 GB databases. Monitor your mdfs and ldfs for auto growths and your drive for free space. Move your system databases to the same drive. Compress your databases. If the databases are not in simple recovery model, be sure to take frequent log backups. Compress your backups. Make sure you are copying your backups to another location so you have a copy in case your drive crashes. Make sure you have jobs to prune msdb history and old backups.
I wouldn't size my log files or my tembdb based on a rule of 25% or anything else. Size them pretty small to start and watch them autogrow and then resize them to that.


how to check whether or not queries are doing partition elimination when accessing my partitioned table?

For partition elimination to come into play, you would need to use the partitioned column in your join or predicate clauses which would allow SQL Server to narrow down the search using the partition metadata available. To check if partition elimination is occurring or not, look for PtnId or RangePartitionNew in the query plan.

See this post for more information. If you want to look at the XML plan, then look for RunTimePartitionSummary which provides the partition access summary details. However, this would need to be retrieved from the actual query plan.

Another option would be to partition align your indexes so that they can help with searches on additional columns. An example of that is available in this post.


How properly call stored procedure from EXEC(SQL)?

You can use it either way, however there is a one thing you should keep in mind
EXEC() function is SQL injection prone

Take a look at this:

DECLARE @Data varchar(50)
SET @Data= ''''';TRUNCATE TABLE corruptTable'
execute (N'select * from corruptTable where Data = ' + @Data)

Executing this command, will give you no results (since we added that our data equals empty string) however, if you query the table ,table will be empty.

Where as in sp_executesql you are explicitly declaring parameters
and

@Param = ''''';TRUNCATE TABLE corruptTable' will be compared as it is

So in the example:

DECLARE @Param varchar(50)
SET @Param= ''''';TRUNCATE TABLE corruptTable'
execute sp_executesql N'select * from corruptTable where Data = @Data',N'@Data varchar(50)',@Data = @Param

It wont yield any results, but it wont execute truncate table, because like i said, it is only treated as a parameters.
Other than that, performance vise, there is no difference.

Note that you should call procedure with schema name included, and should be aware that dynamic SQL executions will create a new plan individually from stored procedure each time you call it.

Update

Since you are testing it on production,and the tables are busy, there are many reasons why "sometimes" its slow. I cannot give you exact details because i dont know logic and environment in the first procedure, but here are some things to watch for:

Statistics Update in process: before plan is created, query optimizer checks whether statistics are up to date - since table is busy, statistics will be updated automatically at certain insert/update threshold and it can happen that it waits for statistics to be updated first before it can proceed, OR it can update statistics by itself so again you have to wait
Locks : I assume your first procedure is retrieving records from a certain table. It can happen that while it tried to get certain records, these records/table are already locked by some update/insert/delete statement. So again you had that slight delay before it actually executed.

Also if you are getting a lot of records from first procedure, consider using temp table instead, table variable is not the best choice.

And lastly you can always diagnose procedure execution using Extended Events, and log it to a table/file if it exceeds certain threshold, along with execution plan,and waits so you can compare it with the regular executions.


How simplify/optimize a search of keywords in two tables at the same time?

The default Postgresql configuration will be used.

The default PostgreSQL configuration is meant to be adequate for smallish servers.  If you are worried about scalability, which you seem to be, then restricting yourself to using the default configuration is going to be a poor plan.

  WHERE p.name ILIKE ANY(potential_parts)

You are probably going to want a trgm_ops index on p.name, and similarly on T.name

You might also need to add user-space code which prevents people from searching on things of less than 3 or 4 characters.

  We can consider that each table will have more than a million of row.

Make a million rows of dummy data, and see what happens.  No use speculating when you can just do it.


Does changing the TDE Master Key always require decryption and re-encryption?
  The DB Master Key and/or the DB encryption key.

The main two secrets involved in TDE are the Database Encryption Key (DEK) and the Server Certificate. The DEK is what actually encrypts and decrypts the data in the database, but the Server Certificate is used to protect (among other protections already involved) the Database Encryption Key (DEK).

To your question, If you rotate the DEK you must decrypt and encrypt all data in the database because it is the key which does this.

If, however, you rotate the Server Certificate protecting the DEK, then no data encryption or decryption of the physical database would need to take place.

It doesn't matter the version or type of software, if you encrypt data with an asymmetric key pair and want to rotate to another asymmetric key pair, you'll first need to decrypt the data with the old set of keys and encrypt it with the new.


How to set full text catalog location SQL Server 2008/2012

Keith,

Full-text catalog had a complete architecture change in 2008 & above and that's why this option is removed and NOT necessary. Definitely NOT the answer you were looking for :-)

Ref: http://msdn.microsoft.com/en-us/library/ms189520(v=SQL.100).aspx

    Important
    Beginning with SQL Server 2008, a full-text catalog is a virtual object and does not belong to any filegroup. A full-text catalog is a logical concept that refers to a group of full-text indexes.

    ON FILEGROUP filegroup
    Beginning with SQL Server 2008, this clause has no effect.
    IN PATH 'rootpath'
     Note
    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.
    Beginning with SQL Server 2008, this clause has no effect.


Before tweaking the disk you should tweak memory usage for the DB - especially with mySQL.

From what I read I suspect that your DB is doing heavy write IO - which is faster on an ext2 and on a "real" disk.

Update 2011-11-23 (after migration to dba):

Perhaps you should analyze your DB with the free TOAD version.


According with your last edit:

SELECT
    F.*,
    C.*
FROM db1.Fields AS F
JOIN db1.Content AS C ON (C.ID = F.ID AND C.ForUser = F.ForUser
                          AND C.ForCategory = F.ForCategory)
WHERE `the field you want to filter`=`the value you want to search`;

You can filter with Any field in Fields or Content.


An UPDATE without WHERE clause will update all the rows of the table. For those rows that don't have a matching address_id in table B, the subquery returns an empty result set, so the value is updated to NULL.

There's probably a more elegant way to do it but this should only update the matching rows:

update TableA A
set email = (select email from TableB B where A.address_id = B.address_id)
where exists
      (select 1 from TableB B where A.address_id = B.address_id) ;

Another option is to use MERGE:

MERGE INTO TableA AS A
USING
  ( SELECT address_id, email
    FROM TableB
  ) AS B
  ON A.address_id = B.address_id
WHEN MATCHED THEN
  UPDATE SET email = B.email ;


Validate XML using XSD inside TSQL function in SQL Server 2012?

One option (possibly your only option if doing this within the context of a scalar function is a hard requirement) is to use SQLCLR. SQLCLR scalar functions can do try / catch / finally structures within the C# or VB.NET code. So, you could pass in both the XML document and the XSD, both as the SqlXml type, and do the validation there. Just return a SqlBoolean for whether or not it validated.

Be sure to do the following so that the SQLCLR function can participate in parallel plans:

specify IsDeterministic = true in the SqlFunction attribute.
make sure that the function is in an Assembly that is marked as WITH PERMISSION_SET = SAFE.


Oracle 11g user created with Create session but able to update the table?

There is no such thing as a read only privilege. If someone grants update,delete,insert on x to public, everyone who can create a session can modify the contents of that table.

Also, if you create a user x and using a dba account create a table x.y, the user x - who owns the table y - can modify the contents of his/her table, because he/she is the owner of that table.


Attach SQL Server 2008 R2 on SQL Server Express 2008

You can't attach a SQL Server 2008 R2 database to a SQL Server 2008 or 2005 instance. You can only go up, not down; you can't make your 2008 R2 database compatible with previous versions. The solution is to remove your Express instance and replace it with a 2008 R2 instance, then you will be able to attach your database...


This looks right. The slave will pick up where it left off when it starts back up.

I will note that unless you supply the --skip-slave-start option, the slave should start automatically.


Oracle Database 11g Failing to mount on windows server 2012

Use a local connection.

sqlplus sys as sysdba

or

sqlplus / as sysdba

In your output, you used a remote connection. Database instances can be started through remote connections only if you have registered them in the listener manually with static registration.


Disk page size for MySQL and MySQL cluster


Unknown Windows SID Mapped to Proxy During SSIS Package Execution

Check the blog post I wrote concerning how to identify this S-1-9 SID.

From that post:

  This can happen as a result of using EXECUTE AS USER = 'SomeUser'; to switch context to another user that doesn’t have a server-level account.

I imagine you'd see this exact scenario for contained users.

There is also an MSDN post from 2006 showing this SID.


Database Mail works in Production but not in Test Database

To send Database mail, users must be a user in the msdb database and a member of the DatabaseMailUserRole database role in the msdb database.

To add msdb users or groups to this role use SQL Server Management Studio or execute the following statement for the user or role that needs to send Database Mail.

EXEC msdb.dbo.sp_addrolemember @rolename = 'DatabaseMailUserRole'
    ,@membername = '<user or role name>';
GO

Source: Microsoft docs  Permission denied on sp_send_dbmail


Easiest way of porting data into a database

You can use Microsoft Access' built-in import features to import data from just about any source.

For Access 2007, the "ribbon" menu has an "External Data" tab, where you can select "Text".

From there it is pretty simple to import text-based data.  As you can see from the screenshot, there are a number of other types of data you can import.


If you are really sure that your Server name is correct you can still try to specify the port explicitly like that:

sqlcmd -S SQLMAIN,1433 -i rdmdb.sql

If you run your SQL Server database as a Contained Database it might be necessary to login to the database direclty using -d:

sqlcmd -S SQLMAIN,1433 -d yourDatabase -i rdmdb.sql

Make sure that you really can access your SQL Server on the specified port. You can try this using telnet.

telnet SQLMAIN 1433

If you don't use a windows login you might need to provide your username and passowrd using -U (Username) and -P (Password).

sqlcmd -S SQLMAIN,1433 -d yourDatabase -U User -P Password -i rdmdb.sql

If you use the integrated authentication, you can also try the Trusted Connection using -E

sqlcmd -S SQLMAIN,1433 -d yourDatabase -E -i rdmdb.sql

Please be also be sure that the specified input file can be accessed and found at your specified location.

Hopefully this will answer your question and your getting your connection to work. :-)


Modelling a relationship involving books, book parts and reviews

Alternative to your approach (exclusive arcs) which you may want to consider is to create a common parent table for Book, and BookPart.  For instance, ReviewableEntity:

ReviewableEntity
 (re_id INT NOT NULL ,
  re_type NOT NULL enum('book','part'),
  PK (re_id)

 );
 Book
  (
  re_id INT NOT NULL ,

  ... book attributes
   PK (re_id),
   FK (re_id) REFERENCES ReviewableEntity(re_id),
  )

 BookPart
  (
    re_id INT NOT NULL ,

  ... book part attributes
   PK (re_id),
   FK (re_id) REFERENCES ReviewableEntity(re_id),
  )

Now Review can have just one foreign key to ReviwableEntity .

There is a challenge to enforce that book details are always stored in book table, and book parts in BookPart table which can be solved in multiple ways .
  1. Create  stored procedures that insert/update data properly and deny direct manipulation of data with REVOKE
  2. Carry over re_type attribute to detail tables (Book and BookPart)
  3. Ignore the problem on db level, and deal with it by administrative means,
 e.g. force developers to insert into proper table(surprisingly , for this  particular case it's not the worst approach )  .

Update.

Compared to exclusive arcs a parent table has the following advantages :
  1. Adding new reviewable type is an easy and cheep operation (creating a new table + modifying enum) which can be performed online even for very large table.  Exclusive arc approach requires adding column to existing table and modifying code that enforces non-nullability of only one field
 2. Other tables can reference ReviewableEntity without knowing exact type
 3. Extracting common attributes from detail tables and storing them in common parent will allow queries to hit only one (parent) table thus eliminating joins.

On the other hand, exclusive arc is a way simpler design, and doesn't require extra table .

Also, your choice may be influenced by RDMBS features.  From what I remember, mysql didn't have CHECK constraints, so enforcing on db level NOT NULL for only one of IdBook, IdBookPart  will require trigger .

Another option (which I personally don't like) is to have join tables , book_part_review( IdBookPart, IdReview), and book_review(IdBook, IdReview).


"dateformat" independent form of datetime string in query for SQL Server?

Use datetime strings in ISO 8601 format:
YYYY-MM-DDThh:mm:ss[.mmm]
YYYYMMDD[ hh:mm:ss[.mmm]]

To use the ISO 8601 format, you must specify each element in the
format, including the T, the colons (:), and the period (.) that are
shown in the format.
The brackets indicate that the fraction of second component is
optional. The time component is specified in the 24-hour format.
The T indicates the start of the time part of the datetime value.
The advantage in using the ISO 8601 format is that it is an
international standard with unambiguous specification. Also, this
format isn't affected by the SET DATEFORMAT or SET LANGUAGE setting.

More info


How to Drop Tables using a variable in SQL Server?

Without questioning your motives, just looking at the stored proc's code, you need to make one or 2 small adjustments as follows:

CREATE PROCEDURE dbo.procdroptable
    @TABLENAME SYSNAME
AS
 BEGIN
    SET NOCOUNT ON;
    DECLARE @SQL NVARCHAR(MAX)
    SELECT @SQL = 'DROP TABLE dbo.' + QUOTENAME(@TABLENAME) + '';
    EXEC sp_executesql @SQL;
 END
GO

You can then execute this like so: exec dbo.procdroptable @TABLENAME = 'yourtablename'

Note: this proc also assumes that all your tables will belong to the [dbo] schema.


How to count hierarchical rows count with a CTE

Here's the trick you're after - use the CTE to build a list of each region, cross referenced with every region which counts towards it:

WITH cte AS (
  SELECT [ID] AS [PID],[ID] FROM [dbo].[Regions]
  UNION ALL
  SELECT cte.[PID], r.[ID]
  FROM cte
    JOIN [dbo].[Regions] r ON r.[ParentRegionID] = cte.[ID]
)

SELECT [PID], [ID]
FROM cte
ORDER BY 1,2

And then let's use it as a cross-reference table to count devices:

WITH cte AS (
  SELECT [ID] AS [PID],[ID] FROM [dbo].[Regions]
  UNION ALL
  SELECT cte.[PID], r.[ID]
  FROM cte
    JOIN [dbo].[Regions] r ON r.[ParentRegionID] = cte.[ID]
)

SELECT
  r.[ID] AS [RegioId],
  r.[Name] AS [RegionName],
  count(d.[ID]) AS [DevicesCount]
FROM
  [dbo].[Regions] r
  LEFT JOIN cte x ON r.[ID] = x.[PID]
  LEFT JOIN [dbo].[Devices] d ON x.[ID] = d.[RegionId]
GROUP BY
  r.[ID],
  r.[Name]
ORDER BY r.[ID]

Gives:

| RegioId | RegionName | DevicesCount |
|---------|------------|--------------|
|       0 |    Region0 |            5 |
|       1 |    Region1 |            2 |
|       2 |    Region2 |            2 |
|      11 |   Region11 |            1 |
|      12 |   Region12 |            0 |
|      21 |   Region21 |            0 |
|      22 |   Region22 |            1 |


Is there a rule of thumb for including the value of a foreign key in a table to avoid large joins?

Don't denormilize, keep 2 tables (cars and colors) whatever tables size.
I think that you don't need to select 10 millions with one query but if you realy need it, you can do it by batch.
I don't know your problematic but by default, never denormilize.

If you have a problem with big tables you can expose it but i am pretty sure that denormalization will be not the solutiuon.


Classic fool who has default Full Backup model and out-of-control log file ---- how to I get this controlled?

Do I have to do anything OTHER than a switch to the simple recovery model?

If you have a full backup, and don't need point-in-time recovery, simply switch the database to Simple recovery.  After the next Checkpoint you should be able to shrink the log file.  If the active VLF happens to be at the end of the file, rebuild an index or two, and try again.


You need a pivoting function. MySQL doesn't have a pivot function per se, you can use conditional aggregation as a workaround.

SELECT
    T.data_item_id,
    MAX(CASE WHEN T.field_name = 'Education' THEN value END) AS Education,
    MAX(CASE WHEN T.field_name = 'Contract or Permanent' THEN value END) AS ContractOrPermanent,
    MAX(CASE WHEN T.field_name = 'Notice period' THEN value END) AS Noticeperiod,
    MAX(CASE WHEN T.field_name = 'Total Experience' THEN value END) AS TotalExperience,
    MAX(CASE WHEN T.field_name = 'Relevant Experience' THEN value END) AS RelevantExperience
FROM
    extra_field AS T
WHERE
    T.data_item_id = 1219 -- Your filter here
GROUP BY
    T.data_item_id

Result:

data_item_id    Education   ContractOrPermanent Noticeperiod    TotalExperience RelevantExperience
1219            MBA         Permanent           30 days         12 yrs          12 yrs

The problem is that you will have to add as many columns and CASE with each different value that you have.

If you need this dynamically (variable amount of columns depending on row's data) you can use prepared statements but it's another level of complexity.


Converting RTF in a text column to plain text in bulk

I ended up making a CLR function to convert it.

I found this library, I then tweaked it a tiny bit to remove things I did not need like logging and Drawing methods, which allowed me to mark it as safe.

I then just made this small class.

using System.Data.SqlTypes;
using Itenso.Rtf.Converter.Text;
using Itenso.Rtf.Support;

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString RtfToPlainText(SqlString text)
    {
        if (text.Value.StartsWith(@"{\rtf"))
        {
            RtfTextConverter textConverter = new RtfTextConverter();
            RtfInterpreterTool.Interpret(text.Value, textConverter);
            return textConverter.PlainText;
        }
        else
            return text;
    }
}

And ran this in SQL

sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO

CREATE ASSEMBLY ConversionsSqlExtensionsAssembly
from 'E:\Code\ConversionsSqlExtensions\bin\Debug\ConversionsSqlExtensions.dll'
WITH PERMISSION_SET = safe
go

CREATE function RtfToPlainText(@value nvarchar(max))
returns nvarchar(max)
AS EXTERNAL NAME ConversionsSqlExtensionsAssembly.StoredProcedures.RtfToPlainText

And it is fast and works great!


How to create an oracle dump(.dmp) file using Toad?

What is your Toad version?

I have 9.5 here and there are three options:

Database > Export > Export Utility Wizard
Database > Export > Export DDL
Database > Export > Generate Database Script

There's one last option, in case you only need to get the 'skeleton' for one schema:

Database > Export > Generate schema script

Have you tried any of that?

[EDIT]
I've tried Alex Poole approach and it didn't work for me either.

Sqlplus to the entry:

p08[oracle] (sigepsti) /oracle/product/11.2.0/db/network/admin$ sqlplus system@sigepsti

SQL*Plus: Release 11.2.0.4.0 Production on Thu Jul 31 17:07:23 2014

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

Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

(sigepsti)SQL> exit

Expdp error (exp gets the same error):

p08[oracle] (sigepsti) /oracle/product/11.2.0/db/network/admin$ tnsping sigepsti

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 31-JUL-2014 17:07:37

Copyright (c) 1997, 2013, Oracle.  All rights reserved.

Used parameter files:
/grid/product/11.2.0/grid/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = dborasigepsti)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = sigepsti)))
OK (0 msec)
p08[oracle] (sigepsti) /oracle/product/11.2.0/db/network/admin$ expdp system@'dborasigepsti:1521/sigepsti'

Export: Release 11.2.0.4.0 - Production on Thu Jul 31 17:07:50 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

UDE-12504: operation generated ORACLE error 12504
ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA


mongodump fails after exactly 10 minutes

It looks like there was a bug as after upgrading to MongoDB v4.2.1 the issue does not happen any more.


Can we do PITR of one specific PostgreSQL database

Restore your backup on another server. Perform a point-in-time recovery. Export the data / tables you’re interested in and import them into your production database.
This is generally how it’s done. You don’t want to lose all the other data but cherry-pick the data you need from a restore on another server.


Why does Oracle consider a larger number to be smaller?

Your quantity columns are being stored as strings, rather than numbers, causing (for example) 10 to be "less" than 9 - a string comparison is obviously based on the characters in the string rather than whole numbers, hence the result you are seeing.

Oracle is not converting the values to numbers when you do a comparison ('<' and '>' in your case), but it is implicitly converting them to numbers when you perform mathematical operations on them, hence the - works as expected.

If you cast the VARCHAR columns to a NUMBER when doing the comparison your query will work correctly:

select *
from orders
where TO_NUMBER(qty)<TO_NUMBER(qtyshipped);

Having said that, you're better off recreating your table with the correct datatypes.

Example DB Fiddle to demonstrate all of this is here.


How to create database, and add user linked to login?

Not sure how dbo is coming into play. Here is how you create a user in a database mapped to a server login, whether that login is already associated to a user in any other database:

CREATE LOGIN foo WITH PASSWORD = 'bar', CHECK_POLICY = OFF;
GO
CREATE DATABASE splunge;
GO
USE splunge;
GO
CREATE USER foo FROM LOGIN [foo];
GO
-- then permissions:
GRANT ... TO foo;
EXEC sp_addrolemember N'<role>', N'foo';
etc.


You should be able to use CROSS APPLY.

In an effort to provide a Minimum Complete Verifiable Example, I've included a 'simple' SPLIT TVF which is intended to emulate your INTLIST_TO_TBL function. This is for demonstration purposes only and there are more efficient ways to split a string that are beyond my answer.

IF EXISTS ( SELECT  *
            FROM    sys.objects
            WHERE   object_id = OBJECT_ID(N'[dbo].[Split]')
                    AND type IN (N'FN', N'IF', N'TF', N'FS', N'FT') )
    DROP FUNCTION [dbo].[Split] ;
GO
CREATE FUNCTION [dbo].[Split](@String varchar(8000), @Delimiter char(1))
returns @temptable TABLE (element int,items varchar(8000))
as
begin
    declare @element int=1
    declare @idx int
    declare @slice varchar(8000)

    select @idx = 1
        if len(@String)<1 or @String is null  return

    while @idx!= 0
    begin
        set @idx = charindex(@Delimiter,@String)
        if @idx!=0
            set @slice = left(@String,@idx - 1)
        else
            set @slice = @String

        if(len(@slice)>0)
            begin
                insert into @temptable(Element,Items) values(@element,@slice)
                set @element=@element+1
            end

        set @String = right(@String,len(@String) - @idx)
        if len(@String) = 0 break
    end
return
end

GO

Now that we have the Split TVF, let's see an example of how to incorporate it.

DECLARE @tbl TABLE (id VARCHAR(100))

INSERT @tbl
VALUES ('111,1487')
    ,('462')
    ,('2492')
    ,('3184')
    ,('3181,3184')
    ,('3181')
    ,('440')
    ,('1436')

SELECT DISTINCT b.items
FROM @tbl a
CROSS APPLY [dbo].[Split](a.id, ',') b


MySQL pivot query trouble sorting column by total

I've edited your example and I used WITH ROLLUP, CASE and FIELD statements to sort and make this:

Information:

mysql> SELECT * FROM test.tblAnnualData;
+----------+---------+------------+------+-----------+
| REPORTER | PARTNER | NET_WEIGHT | YEAR | COMMODITY |
+----------+---------+------------+------+-----------+
| Egypt    | Canada  |          5 | 2010 | wheat     |
| Germany  | UK      |          1 | 2011 | wheat     |
| Mexico   | France  |          5 | 2011 | wheat     |
| Norway   | USA     |          2 | 2012 | wheat     |
| Peru     | France  |          3 | 2011 | wheat     |
| Spain    | USA     |          3 | 2010 | wheat     |
+----------+---------+------------+------+-----------+
6 rows in set (0.00 sec)

Dynamic Query:

SET @@group_concat_max_len = 500000;
SET @QUERY1 = NULL;

SELECT GROUP_CONCAT(DISTINCT CONCAT(" SUM(CASE WHEN PARTNER = '",PARTNER,"' THEN NET_WEIGHT ELSE 0 END) AS '",PARTNER,"'")
ORDER BY PARTNER ASC)
INTO @QUERY1
FROM tblAnnualData;

SET @QUERY1 = CONCAT("SELECT
                        REPORTER,
                        TOTAL,
                        USA,
                        France,
                        Canada,
                        UK
                    FROM (SELECT
                            IFNULL(REPORTER,'TOTAL') AS REPORTER,
                            SUM(NET_WEIGHT) AS TOTAL,",@QUERY1," FROM tblAnnualData
                    WHERE COMMODITY = 'wheat'
                    #AND Year = 2011
                    GROUP BY REPORTER WITH ROLLUP) AS A
                    ORDER BY FIELD(REPORTER,'TOTAL') DESC,
                        TOTAL DESC,
                        REPORTER ASC;");
PREPARE QUERY1 FROM @QUERY1;
EXECUTE QUERY1;

It is the same as this query:

SELECT
    REPORTER,
    TOTAL,
    USA,
    France,
    Canada,
    UK
FROM (SELECT
        IFNULL(REPORTER,'TOTAL') AS REPORTER,
        SUM(NET_WEIGHT) AS TOTAL,
        SUM(CASE WHEN PARTNER='USA' THEN NET_WEIGHT ELSE 0 END) AS USA,
        SUM(CASE WHEN PARTNER='France' THEN NET_WEIGHT ELSE 0 END) AS France,
        SUM(CASE WHEN PARTNER='Canada' THEN NET_WEIGHT ELSE 0 END) AS Canada,
        SUM(CASE WHEN PARTNER='UK' THEN NET_WEIGHT ELSE 0 END) AS UK
    FROM tblAnnualData
    GROUP BY REPORTER WITH ROLLUP) AS A
ORDER BY FIELD(REPORTER,'TOTAL') DESC,
    TOTAL DESC,
    REPORTER ASC;

Why FIELD?

I used FIELD to sort by first when the field is TOTAL (that is the REPORTER aggregated field of the row generated by WITH ROLLUP), then I sort by the TOTAL of NET_WEIGHT. After that I finish with the REPORTER, just in case if some REPORTER has same TOTAL of other/others.

Testing the Dynamic Query:

mysql> SET @@group_concat_max_len = 500000;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @QUERY1 = NULL;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> SELECT GROUP_CONCAT(DISTINCT CONCAT(" SUM(CASE WHEN PARTNER = '",PARTNER,"' THEN NET_WEIGHT ELSE 0 END) AS '",PARTNER,"'")
    -> ORDER BY PARTNER ASC)
    -> INTO @QUERY1
    -> FROM tblAnnualData;
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> SET @QUERY1 = CONCAT("SELECT
    "> REPORTER,
    "> TOTAL,
    "> USA,
    "> France,
    "> Canada,
    "> UK
    "> FROM (SELECT
    "> IFNULL(REPORTER,'TOTAL') AS REPORTER,
    "> SUM(NET_WEIGHT) AS TOTAL,",@QUERY1," FROM tblAnnualData
    "> WHERE COMMODITY = 'wheat'
    "> #AND Year = 2011
    "> GROUP BY REPORTER WITH ROLLUP) AS A
    "> ORDER BY FIELD(REPORTER,'TOTAL') DESC,
    "> TOTAL DESC,
    "> REPORTER ASC;");
Query OK, 0 rows affected (0.00 sec)

mysql> PREPARE QUERY1 FROM @QUERY1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
Statement prepared

mysql> EXECUTE QUERY1;
+----------+-------+------+--------+--------+------+
| REPORTER | TOTAL | USA  | France | Canada | UK   |
+----------+-------+------+--------+--------+------+
| TOTAL    |    19 |    5 |      8 |      5 |    1 |
| Egypt    |     5 |    0 |      0 |      5 |    0 |
| Mexico   |     5 |    0 |      5 |      0 |    0 |
| Peru     |     3 |    0 |      3 |      0 |    0 |
| Spain    |     3 |    3 |      0 |      0 |    0 |
| Norway   |     2 |    2 |      0 |      0 |    0 |
| Germany  |     1 |    0 |      0 |      0 |    1 |
+----------+-------+------+--------+--------+------+
7 rows in set, 1 warning (0.00 sec)

mysql>

Try it in SQLFiddle


INET6_NTOA output is set as null to varchar(39) variable

This looks like a genuine bug. I get the expected result in MySQL 5.7.20 (see below), but it returns null in MariaDB 10.2.10. The issue should be reported in the MariaDB Jira.

mysql> DELIMITER ;;
mysql> CREATE PROCEDURE p() CONTAINS SQL BEGIN DECLARE ip_full_addr varchar(39) DEFAULT ""; SELECT INET6_NTOA(UNHEX('20000000000000000000000000000000')) into ip_full_addr; SELECT ip_full_addr; END;;
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;
mysql> call p();
+--------------+
| ip_full_addr |
+--------------+
| 2000::       |
+--------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)


Restoring differential backup creates DEFUNCT log file?

Here are the steps to reproduce this, tested on SQL 2012 SP1 Developer Edition.  This does not occur on SQL 2008.  To summarize, a database created in SQL 2012 while the model database is in SIMPLE recovery, that has a full backup taken while an extra log file exists, cannot create usable differential backups if that extra log file is ever deleted.

ALTER DATABASE [model] SET RECOVERY SIMPLE
GO
CREATE DATABASE [DefunctTest]
GO
ALTER DATABASE [DefunctTest] ADD LOG FILE ( NAME = N'DefunctTest_log2', FILENAME = N'D:\DefunctTest_log2.ldf' , SIZE = 25600KB , FILEGROWTH = 10%)
GO
BACKUP DATABASE [DefunctTest] TO DISK = 'D:\DefunctTestPostLogFile.bak' WITH INIT
GO
ALTER DATABASE [DefunctTest]  REMOVE FILE [DefunctTest_log2]
GO

BACKUP DATABASE [DefunctTest] TO DISK = 'D:\DefunctTestFull.bak' WITH INIT
GO
BACKUP DATABASE [DefunctTest] TO DISK = 'D:\DefunctTestDiff.bak' WITH DIFFERENTIAL, INIT
GO
--Show that the backups only have the one log file.
RESTORE FILELISTONLY FROM DISK = 'D:\DefunctTestFull.bak'
RESTORE FILELISTONLY FROM DISK = 'D:\DefunctTestDiff.bak'
GO
RESTORE DATABASE [DefunctTest2] FROM DISK = 'D:\DefunctTestFull.bak' WITH
MOVE 'DefunctTest' TO 'D:\DefunctTest2.mdf',
MOVE 'DefunctTest_log' TO 'D:\DefunctTest2_log.ldf', REPLACE, NORECOVERY
GO
--This restore will have the error.
RESTORE DATABASE [DefunctTest2] FROM DISK = 'D:\DefunctTestDiff.bak' WITH RECOVERY
GO

USE [DefunctTest2]
SELECT * FROM sys.database_files
GO

I submitted a Connect item for this bug here.  The only way I've been able to remove this defunct file is to detach the database, and re-attach with ATTACH_REBUILD_LOG.

UPDATE: The bug that creates this scenario in my repro script seems to have been fixed by this KB: https://support.microsoft.com/en-us/kb/2830400.  From the comments it appears an additional fix is available for SQL2012/2014, the scenarios seem very similar:  https://support.microsoft.com/en-us/kb/3009576


Blockquote
  Is there a way of implementing the reads from the replica but the writes on the main database (I am thinking about this so that I don't have to deal with merges and I don't really care about the write speed)?
  Blockquote

Technically possible but probably not in your scenario. Bi directional transactional replication would be required, which isn't invisible to the app/website.


Necessity of a DB-replication as extra security layer

If the application (publicly accessible) is attacked / DDoS´d, only the connected replica DB will be affected and the main DB can operate normally

That's the reason.
If the Application running against the replica database is publicly accessible, then it can be attacked.  If it is attacked then, as you say, the replica database will bear the brunt of that attack.  The remainder of the company, running against the primary database, can continue operating [largely] as normal.
As long as the web application is of secondary importance to your company, then this arrangement makes sense.  If you work for eBay or Amazon?  Not so much!
That's not to say that the primary database is completely unaffected by this!  The replication processes, trying to write to the over-loaded replica, may be impacted too, which may feed back into poorer performance on the primary or, depending on how the replication system works, potential disk space problems with logs not being shipped and cleared down as quickly as they should be.


Certainly, running a backup will have an impact on the performance profile of the machine serving the database being backed up.

For a FULL Backup, the operation consists of reading every allocated page in the entire database, and writing those pages to the backup file/device.

Since your users are complaining about the application being slow during the period of time when the backup takes place, I'd say it's obvious that the backup is having an impact.


Rewrite trigger from PostgreSQL to MySQL

You cannot do this in an AFTER INSERT trigger. Why ???

According to the Book

Chapter 19 Page 251 Paragraph 3 under the subheading BEFORE and AFTER TRIGGERS

  The most significant difference between BEFORE and AFTER triggers is that in an AFTER trigger you are not able to modify the values about to be inserted into or updated with the table in question -- the DML has executed, and it is too late change what the DML is going to do.

Just make this as a BEFORE INSERT trigger and update the needed columns:

DELIMITER $$
CREATE TRIGGER connections_INSERT_update_connection_root_trg BEFORE INSERT ON connections
FOR EACH ROW
BEGIN
    IF NEW.connection_root IS NULL THEN
        NEW.connection_root = NEW.connection;
    END IF;
END $$
DELIMITER ;

GIVE IT A TRY !!!


Oracle Datapump: modify fully qualified objects when cloning a schema

if you grant privileges (as you mention above), the problem may seem to go away, but your code executing in SCHEMA_B may be accessing data from tables in SCHEMA_A, unless that is what you want.

Unfortunately there are no easy answers to this problem, since remap_schema option of datapump doesn't really support what you are trying to do. However you may try following option (I have done it before),

Extract all plsql code and/or view definition (datapump can do this for you using impdp with sqlfile option),
use sed or any other tool of your choice and replace SCHEMA_A. to SCHEMA_B. where you see objects prefixed. Be careful, occasionally unwanted lines might get changed, so you will have to track that and implement workarounds.
impdp tables,indexes,constraints to SCHEMA_B
using sqlplus connect to SCHEMA_B and run the modified scripts from step 2 to load all plsql code, create views and other dependent objects etc.

It is cumbersome, but short of fixing your source schema, I don't think you have too many choices. Luckily if you are in a situation where code doesn't change that often, you can hold on to converted files and avoid these manual steps until something breaks.

Oh btw, if your pl/sql code looks like "create or replace schema_a.package_name", bad luck, remap_schema won't support that either (until 11.2, I have not tested in 12c yet).


PostgreSQL: how do I list all triggers that use a specific field?

You can do that by querying pg_proc and pg_trigger,

SELECT t.tgname FROM pg_trigger t, pg_proc p
WHERE t.tgfoid=p.oid AND p.prosrc like '%studentid%';


MySQL database datafiles in windows SystemData directory

Those three files represent one MyISAM table

For the MyISAM table, mydb.TableName, here is what they are

TableName.frm : Table format file (all Storage Engines have this one)
TableName.MYD : MyISAM data for the table
TableName.MYI : MyISAM Indexes for the Table

Click to read MySQL Documentation for MyISAM Storage Engine. In part, it says:

  Each MyISAM table is stored on disk in three files. The files have names that begin with the table name and have an extension to indicate the file type. An .frm file stores the table format. The data file has an .MYD (MYData) extension. The index file has an .MYI (MYIndex) extension.


Backup a large postgres database for PITR

There are several ways of doing what you're wanting.

I would recommend using a specific solution such as WAL-E, barman, or OmniPITR to make the logistics of managing basebackups and WAL archives easier on yourself, or using pg_basebackup. Some of these tools, depending on the versions, will allow you to back up off your replica, which will reduce load on your primary server, and not push data out of the filesystem cache, which are all very good things.

And as you expected, there are definitely pros and cons with your approaches.

For your first question, you have to consider that if you take a single basebackup and just keep archiving WAL segments, the time to restore will grow longer with each WAL segment you archive, as it takes a significant amount of time restore each file.
So that is not ideal, and most people tend to take a backup on a schedule that allows them to meet recovery requirements, and sometimes keep multiple basebackups as their storage allotments allow.
Additionally, with a very, very, very large WAL archive, sometimes things can happen with individual WAL segments (which is why you always test your backups), and if a WAL segment fails to restore, no following segments will restore, and you're out of luck.

A general rule I've heard and use myself is if I take weekly basebackups, keeping around 3 weeks of WAL segments and 2 basebackups allows me enough peace of mind to have at least one known-good backup, and a known-good set of WAL, and a fairly reasonable restore time. I am a huge believer in testing the backups I take regularly.

For your second question, either one works well, though, pg_basebackup is easier to use and requires less of your own assembly. If you use rsync in your own script with start and stop basebackup, you have to check the exit code of every command. If you don't and a command fails, you can end up with a corrupt basebackup that's useless. The above mentioned tools, along with pg_basebackup, take care of all of those pedantic details for you.

For your third question, in some cases, yes, you can make a backup off of a replica, instead of the primary. If you can manage it, doing the basebackup and archiving off of a replica is the way to go, because taking a basebackup generates a lot of I/O, and it affects your OS filesystem cache. Unfortunately, it is not supported until 9.2+ with pg_basebackup, so you'll need to use an external tool such as OmniPITR or to upgrade your version of PostgreSQL. So long as you have the WAL to go with it, there shouldn't be any timeline issues or consistency issues with the snapshot from the replica.

Finally, a lot of your questions and best practices are covered in the following talk from Magnus Hagander at PgCon 2013: PostgreSQL Backup Strategies

It is well worth the time spent to watch the video.

Hope that helps. =)


How to use mysql sandbox to build s - m - m -s topology?

Well afaik you can't if you rely on mysqlsandbox to orchestrate it for you. But you can create a master-master for M1 <-> M2 and then create two standalone instance and configure each one to slave off of one master. It's pretty easy since you have all replication user created "rsandbox".


Check if a database table has been renamed

There is no other way to establish the identity of a database table in PostgreSQL than through the OID (system column tableoid). Creation time is not stored in PostgreSQL.
Relying on the table OID is not only proprietary to PostgreSQL, but also somewhat unsafe, since OIDs can get reused. I recommend that you keep an inventory of your table modifications in a separate database table.


Database-agnostic stored procedure call passing array of name/value pairs

Pass the values as XML (in a VARCHAR). The following database all support shredding XML from the query language:

Postgres
MySQL
SQL Server
Oracle
DB2 UDM

Obviously, not every obscure database on the planet supports it. But the ones above are realistically the only ones you need to care about to hit 99.9% of the database market.


SELECT that is null if there is one more register in this date that is not null

You had one closing parentheses too many in the latter query. You also have columns date and datefin and the distinction between the two is not clear. If you want to do a comparison per day, you have to make sure both rows are from the same day in the inner query (a.date = b.date) and there has to exist b.dateinit where a.dateinit is smaller (<) not smaller or equal (<=). You also erroneously compare datefin columns in the subquery when you should compare dateinit columns. It seems you want to get rows from dates older than 2015-02-27:

SELECT * FROM capturainfo a where date < "2015-02-27"
AND datefin is null
AND EXISTS ( SELECT * FROM capturainfo b WHERE a.date = b.date AND a.dateinit < b.dateinit AND a.userid = b.userid )


Inefficient queries on partitioned tables

It depends on the query, so if you have a specific problem you'll need to give us details for the query (and possibly table structure) in order to get specific help.

Generally speaking though:

If the filter is not sargable then it is no help to the query planner in the same way as references to indexes where the clause is not sargable - for instance if a function or sub-squery that can not be simplified down to a single value for all possible rows is involved.
If there are other filters on columns covered by non-partitioned (or unaligned) indexes the query planner may consider searching that way to be more efficient than using the partitioning rule.


No results are returned because, unsurprisingly, there are no records that meet the conditions of the WHERE clause.  SLEEP() will return 0 (if not interrupted), and all operands will be cast as doubles for the division operation (hence the warning about truncated incorrect double).  In essence, the query is the same as

SELECT fname, lname FROM contacts WHERE fname = 0 / 0 / 0;

Division by zero results in null, so you're basically looking for records where fname = NULL, which is never true.


How to re-arrange rows to best fit within groups?

Here it the code from @DTest's Answer
SET @folder = 1;
SET @items = 0;
SELECT
    id, num_items,
    (SELECT IF(((@items:=@items+num_items)>100), @folder:=@folder+1, @folder)) as folder,
    IF(@items>100,@items:=0,@items) as checkItems
FROM foo;

Staring at this query, I would shudder to even think about writing of a Stored Procedure Technique for fitting items in folders. Then, it hit me ...
You could simply take the same query and ORDER BY num_items. That would actually pack as many items into a folder as possible before switching to another folder.
mysql> use ali
Database changed
mysql> CREATE TABLE foo (
    ->   id tinyint unsigned primary key AUTO_INCREMENT,
    ->   num_items tinyint unsigned
    -> );
Query OK, 0 rows affected (0.24 sec)

mysql>
mysql> INSERT INTO foo VALUES (1,4),(2,33),(3,74),(4,44),(5,24),(6,34),(7,46),(8,55),(9,11);
Query OK, 9 rows affected (0.17 sec)
Records: 9  Duplicates: 0  Warnings: 0

mysql> SET @folder = 1;
Query OK, 0 rows affected (0.03 sec)

mysql> SET @items = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT id, num_items, (SELECT IF(((@items:=@items+num_items)>100),
    -> @folder:=@folder+1, @folder)) as folder,
    -> IF(@items>100,@items:=0,@items) as checkItems
    -> FROM foo ORDER BY num_items;
+----+-----------+--------+------------+
| id | num_items | folder | checkItems |
+----+-----------+--------+------------+
|  1 |         4 |      1 |          4 |
|  9 |        11 |      1 |         15 |
|  5 |        24 |      1 |         39 |
|  2 |        33 |      1 |         72 |
|  6 |        34 |      2 |          0 |
|  4 |        44 |      2 |         44 |
|  7 |        46 |      2 |         90 |
|  8 |        55 |      3 |          0 |
|  3 |        74 |      3 |         74 |
+----+-----------+--------+------------+
9 rows in set (0.08 sec)

mysql>

This packing would be somewhat lopsided in iterms of the number of entries per folder but the sum of the items per folder are pressed as close to 100 as possible.
Please note that if you want later folder with more items, simply change ORDER BY num_items to ORDER BY num_items DESC.
Give it a Try !!!
UPDATE 2013-01-09 12:52 EDT
What you need are three(3) mechanisms:

MECHANISM #1 : You need to imbed the query in a subquery
MECHANISM #2 : Order the subquery output by folder,num_items
MECHANISM #3 : Use a variable to monitor when you change folders from each row. When such a change occurs, you simply reset the folder counter to 1.

Here is the new code:
SET @order_val = 0;
SET @folder = 1;
SET @curr_folder = 1;
SET @items = 0;
SELECT *,
    @order_val:=IF(folder=@curr_folder,@order_val+1,1) ordervalue,
    @curr_folder:=folder current_folder
FROM
(
    SELECT * FROM
    (
        SELECT
            id, num_items,
            (SELECT IF(((@items:=@items+num_items)>100),
            @folder:=@folder+1, @folder)) as folder,
            IF(@items>100,@items:=0,@items) as checkItems
        FROM foo
    ) AA ORDER BY folder,num_items
) A;

Here is it execution:
mysql> SET @order_val = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @folder = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @curr_folder = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @items = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT *,
    ->     @order_val:=IF(folder=@curr_folder,@order_val+1,1) ordervalue,
    ->     @curr_folder:=folder current_folder
    -> FROM
    -> (
    ->     SELECT * FROM
    ->     (
    ->         SELECT
    ->             id, num_items,
    ->             (SELECT IF(((@items:=@items+num_items)>100),
    ->             @folder:=@folder+1, @folder)) as folder,
    ->             IF(@items>100,@items:=0,@items) as checkItems
    ->         FROM foo
    ->     ) AA ORDER BY folder,num_items
    -> ) A;
+----+-----------+--------+------------+------------+----------------+
| id | num_items | folder | checkItems | ordervalue | current_folder |
+----+-----------+--------+------------+------------+----------------+
|  1 |         4 |      1 |          4 |          1 |              1 |
|  2 |        33 |      1 |         37 |          2 |              1 |
|  5 |        24 |      2 |         68 |          1 |              2 |
|  4 |        44 |      2 |         44 |          2 |              2 |
|  3 |        74 |      2 |          0 |          3 |              2 |
|  6 |        34 |      3 |          0 |          1 |              3 |
|  7 |        46 |      3 |         46 |          2 |              3 |
|  9 |        11 |      4 |         11 |          1 |              4 |
|  8 |        55 |      4 |          0 |          2 |              4 |
+----+-----------+--------+------------+------------+----------------+
9 rows in set (0.00 sec)

mysql>

Give it a Try !!!


SSRS bulk permission update 2008R2 to 2014

I was able to hack together a solution from a few different blogs-

function Add-SSRSItemSecurity
(
    [Parameter(Position=0,Mandatory=$true)]
    [Alias("url")]
    [string]$webServiceUrl,

    [Parameter(Position=1,Mandatory=$true)]
    [Alias("path")]
    [string]$itemPath,

    [Parameter(Position=2,Mandatory=$true)]
    [Alias("u")]
    [string]$groupUserName,

    [Parameter(Position=3,Mandatory=$true)]
    [Alias("r")]
    [string]$role,

    [Parameter(Position=2)]
    [bool]$inherit=$true
)

{

    #Fix item path if not starting with /
    if(!$itemPath.StartsWith("/")) { $itemPath = "/" + $itemPath}

    #Create Proxy
    Write-Host "[Add-SSRSItemSecurity()] Creating Proxy, connecting to : $webServiceUrl"
    $ssrsProxy = New-WebServiceProxy -Uri $webServiceUrl -UseDefaultCredential

    $type = $ssrsProxy.GetType().Namespace;
    $policyType = "{0}.Policy" -f $type;
    $roleType = "{0}.Role" -f $type;

    Write-Host "[Add-SSRSItemSecurity()] Retrieving all existing policies."
    $policies = $ssrsProxy.GetPolicies($itemPath, [ref]$inherit);

    $a = 1;
    foreach($policy in $policies)
    {

        foreach($r in $policy.Roles)
        {
            $msg = "[Add-SSRSItemSecurity()]  Existing Policy # {0} Group Name: {1}, Role: {2}" -f $a, $policy.GroupUserName, $r.Name
            Write-Host $msg
        }
        $a+=1;
    }

    $msg = "[Add-SSRSItemSecurity()] Total Existing Policies: " + $policies.Length;
    Write-Host $msg

    $Policy = $policies |
    Where-Object { $_.GroupUserName -eq $groupUserName } |
    Select-Object -First 1

    if (-not $Policy) {
        $Policy = New-Object ($policyType)
        $Policy.GroupUserName = $GroupUserName
        $Policy.Roles = @()
        $Policies += $Policy
        $msg = "[Add-SSRSItemSecurity()] Adding new policy: '{0}'" -f $GroupUserName
        Write-Host $msg
    }

    $r = $Policy.Roles |
        Where-Object { $_.Name -eq $role } |
        Select-Object -First 1
    if (-not $r) {
        $r = New-Object ($roleType)
        $r.Name = $role
        $Policy.Roles += $r
        $msg = "[Add-SSRSItemSecurity()] Adding new role: '{0}'" -f $role
        Write-Host $msg
    }

    #Set the policies
    $ssrsProxy.SetPolicies($itemPath,$policies);

}

$ReportServerUriSource = 'http://server1/ReportServer/ReportService2010.asmx?wsdl'
$ReportServerUriTarget = 'http://server2/ReportServer/ReportService2010.asmx?wsdl'
$rsProxy =       New-WebServiceProxy -Uri $ReportServerUriSource -UseDefaultCredential
$SourceItems = $rsProxy.ListChildren("/", $true) | `
         SELECT TypeName, Path, ID, Name

 $targetFolder = $rsProxyTarget.GetPolicies($SourceItem.Path,[ref]$InheritParent)
 foreach($SourceItem in $SourceItems)
 {
  $Policies = $rsProxy.GetPolicies($SourceItem.Path, [ref]$InheritParent)
      if($InheritParent -eq $false)
     {
 $x = $Policies.Count
 $y = 0
 while($y -lt $x)
 {
     $name = ($Policies[$y]).Roles[0].Name

     $z = ($Policies[$y]).Roles.Count
     $q = 0

 while($q -lt $z)
     {
  Add-SSRSItemSecurity -webServiceUrl $ReportServerUriTarget `
                     -itemPath $SourceItem.Path`
                     -groupUserName ($Policies[$y]).GroupUserName`
                     -role ($Policies[$y]).Roles[$q].Name
         $q++
  }

     $y++
 }
}
}


As far as I understand, your task is not so much to make a backup - how to automatically restore it to another RDS SQL Server in the development environment.
In general, there are two ways on how to achieve it:

Create a native backup and restore it to dev
Export \ Import Data tier application

Amazon RDS SQL Server does not support the BACKUP DATABASE command, you need to use a stored procedure instead:
EXEC [msdb]. [Dbo]. [Rds_backup_database]
@source_db_name = '<database name>',
@ S3_arn_to_backup_to = N'arn:aws:s3:::<bucket name>/sample.backup '

To restore a database to a dev server, please use the following stored procedure:
exec msdb.dbo.rds_restore_database
@restore_db_name = '<database name>',
@s3_arn_to_restore_from = 'arn:aws:s3:::<bucket name>/sample.backup';

You will need to do some pre-configuration on your RDS SQL Server - you can find more details here.
Note. These stored procedures do not directly perform the backups, but run it asynchronously. After completing the procedure, you will receive a Task that should be tracked.
You can view all the tasks using the
EXEC procedure [msdb].[Dbo].[Rds_task_status]

Unfortunately, this architecture makes it difficult to use Agent Job. And asynchrony prevents us from making a very reliable solution on powershell.
However, you do not have to use backup to transfer a database to a dev server.
You can use the sqlpackage.exe utility which is intended for data export / import. It is not recommended to use it for backups, as it is necessary to set a global read lock to export consistent data. However, for your task, this can be neglected.
SqlPackage can either create a .bacpac file or .dacpac file. If you specify a /p:ExtractAllTableData = True parameter for the /action:Extract command, then you will get actually identical files.
My experiments have shown that it is better to export to .dacpac, since in this case, you can avoid import errors using the /p option: TreatVerificationErrorsAsWarnings = True
Thus, to export the database, use
sqlpackage.exe /action:Extract /SourceServerName:<RDS Server name> /SourceDatabaseName:<db name> /SourceUser:admin /SourcePassword:<password> /TargetFile:<patch to target .dacpac> /p:ExtractAllTableData=True

and for import
sqlpackage.exe /action:Publish /TargetServerName:<RDS Server name>  /TargetDatabaseName:<db name> /TargetUser:admin /TargetPassword:<password> /SourceFile:<patch to source .dacpac> /p:TreatVerificationErrorsAsWarnings=True

Such a script is much easier to automate in powershell, since both commands are synchronous


How to export all tables with user in Oracle 10g Enterprise edition?

You can use Oracle Datapump to perform export-import in schema(user) level.

Here are some examples.

expdp scott/tiger schemas=SCOTT directory=TEST_DIR dumpfile=SCOTT.dmp logfile=expdpSCOTT.log

The above command exports SCOTT schema in a dump file called SCOTT.dmp in a directory specified using DIRECTORY parameter, and writes it's log in expdpSCOTT.log file.

You can copy this dump file to another host and use Datapump Import to import it as shown below.

impdp scott/tigerschemas=SCOTT directory=TEST_DIR dumpfile=SCOTT.dmp logfile=impdpSCOTT.log

References:
Data Pump Export
Data Pump Import
CREATE DIRECTORY


Installing PostGIS in a different schema causes topology extension to not find geometry type

The reason for the schema move is that we are using database schemas to isolate data for multiple tenants. Thus, public will not always be in the search path. By moving PostGIS to its own schema, we can keep that in the search path at all times

Good luck with that. It's no longer possible in new versions of PostGIS, including 2.4.2 r16113 -- the version you're using. From the site,

  As of PostGIS 2.3, the postgis extension was changed to no longer allow relocation. All function calls within the extension are now schema qualified.

You'll have to install it into public, and then move it. YMMV. It's not supported. Again, from the official site,

UPDATE pg_extension
  SET extrelocatable = TRUE
    WHERE extname = 'postgis';

ALTER EXTENSION postgis
  SET SCHEMA postgis;

ALTER EXTENSION postgis
  UPDATE TO "2.4.1next";

ALTER EXTENSION postgis
  UPDATE TO "2.4.1";


What is the name of the default schema in Oracle?

By default, it is the user you used to log in, and you can query it as below:

select sys_context('USERENV', 'CURRENT_SCHEMA') from dual;

You can change your current schema in your session however:

alter session set current_schema=USER2;

If you create a table after this, without specifying the schema, that table will be created in the USER2 schema regardless of what user you logged on with.


The standby server has to write the WAL that it gets from the primary server, it needs enough disk space for that.

Since DB2 is not only a standby, but also a primary server for DB3 and DB4, it might need to retain WAL for a while (I don't know how you configured that).

Just give all standby servers as much disk space as the primary has, then you should be good. Be generous with disk space!

It could be that extra disk space is used if you use replication slots and a standby cannot keep up, perhaps because of a slow network connection. That could be the cause of your out-of-disk condition.

Remember that DELETE and VACUUM do not free any disk space.


Subquery in where clause forces seq scan instead of using trigram index

You get that problem because of the OR, which very often cripples query performance, and because the optimizer cannot "optimize away" that OR condition.

If you cannot simplify the query to get rid of the OR, you can try to create an index on datasets_id in the hope to get a "bitmap or" that combines the two bitmap index scans.

A general problem with subqueries is that PostgreSQL can estimate how many rows a subquery will return, but it does not know whether the result values will be "most common values" or not, so it may choose a less than optimal execution plan.

Sometimes execution can be improved by splitting one query into two queries so that the results of one query become constants in the other, and the optimizer can choose a better plan.


Database design for product purchase history

The question you should be asking is whether product attribute changes in general are of interest or if you are particularly concerned with changes in price.
What you probably really want is to record the price actually paid in your sale transaction details.  This is something that you'll need for proper financial accounting.  There are lots of things that could impact the price paid. Maybe there was a sale on. Maybe the customer has a coupon or a loyalty discount card? Note how being on sale could be considered an attribute of the product, but a coupon or customer discount would never be.
Don't think of the price as being (only) an attribute of the product. Once it becomes the price actually paid, it's an attribute of the sale, not an attribute of the product.  Think of the price on your product table as being "the current normal price".  The price on your sale transaction detail is "the price actually paid on this sale".  You will need to have both because they are semantically different.  You just need to know where to look depending on which meaning is of interest for any specific purpose.


Mysql: How does circular replication avoid infinite looping of a transaction

Yes, you are correct. This relies on the server ID being set correctly in the standard MySQL configuration. In MySQL 5.6 and above you can configure the server to use GTIDs (Global Transaction IDentifiers) and they are used on all servers to determine if transactions need to be executed or not


Is ALTER TABLE ... DROP COLUMN really a metadata only operation?

There are certain circumstances where dropping a column can be a meta-data-only operation.  The column definitions for any given table are not included in each and every page where rows are stored, column definitions are only stored in the database metadata, including sys.sysrowsets, sys.sysrscols, etc.

When dropping a column that is not referenced by any other object, the storage engine simply marks the column definition as no longer present by deleting the pertinent details from various system tables.  The action of deleting the meta-data invalidates the procedure cache, necessitating a recompile whenever a  query subsequently references that table.  Since the recompile only returns columns that currently exist in the table, the column details for the dropped column are never even asked for; the storage engine skips the bytes stored in each page for that column, as if the column no longer exists.

When a subsequent DML operation occurs against the table, the pages that are affected are re-written without the data for the dropped column.  If you rebuild a clustered index or a heap, all the bytes for the dropped column are naturally not written back to the page on disk.  This effectively spreads the load of dropping the column over time, making it less noticeable.

There are circumstances where you cannot drop a column, such as when the column is included in an index, or when you've manually created a statistics object for the column.  I wrote a blog post showing the error that is presented when attempting to alter a column with a manually created statistics object.  The same semantics apply when dropping a column - if the column is referenced by any other object, it cannot simply be dropped.  The referencing object must be altered first, then the column can be dropped.

This is fairly easy to show by looking at the contents of the transaction log after dropping a column.  The code below creates a table with a single 8,000 long char column.  It adds a row, then drops it, and displays the contents of the transaction log applicable to the drop operation.  The log records show modifications to various system tables where the table and column definitions are stored.  If the column data was actually being deleted from the pages allocated to the table, you'd see log records recording the actual page data; there are no such records.

DROP TABLE IF EXISTS dbo.DropColumnTest;
GO
CREATE TABLE dbo.DropColumnTest
(
    rid int NOT NULL
        CONSTRAINT DropColumnTest_pkc
        PRIMARY KEY CLUSTERED
    , someCol varchar(8000) NOT NULL
);

INSERT INTO dbo.DropColumnTest (rid, someCol)
SELECT 1, REPLICATE('Z', 8000);
GO

DECLARE @startLSN nvarchar(25);

SELECT TOP(1) @startLSN = dl.[Current LSN]
FROM sys.fn_dblog(NULL, NULL) dl
ORDER BY dl.[Current LSN] DESC;

DECLARE @a int = CONVERT(varbinary(8), '0x' + CONVERT(varchar(10),      LEFT(@startLSN, 8), 0), 1)
      , @b int = CONVERT(varbinary(8), '0x' + CONVERT(varchar(10), SUBSTRING(@startLSN, 10, 8), 0), 1)
      , @c int = CONVERT(varbinary(8), '0x' + CONVERT(varchar(10),     RIGHT(@startLSN, 4), 0), 1);

SELECT @startLSN = CONVERT(varchar(8), @a, 1)
    + ':' + CONVERT(varchar(8), @b, 1)
    + ':' + CONVERT(varchar(8), @c, 1)

ALTER TABLE dbo.DropColumnTest DROP COLUMN someCol;

SELECT *
FROM sys.fn_dblog(@startLSN, NULL)

--modify an existing data row
SELECT TOP(1) @startLSN = dl.[Current LSN]
FROM sys.fn_dblog(NULL, NULL) dl
ORDER BY dl.[Current LSN] DESC;

SET @a = CONVERT(varbinary(8), '0x' + CONVERT(varchar(10),      LEFT(@startLSN, 8), 0), 1);
SET @b = CONVERT(varbinary(8), '0x' + CONVERT(varchar(10), SUBSTRING(@startLSN, 10, 8), 0), 1);
SET @c = CONVERT(varbinary(8), '0x' + CONVERT(varchar(10),     RIGHT(@startLSN, 4), 0), 1);

SELECT @startLSN = CONVERT(varchar(8), @a, 1)
    + ':' + CONVERT(varchar(8), @b, 1)
    + ':' + CONVERT(varchar(8), @c, 1)

UPDATE dbo.DropColumnTest SET rid = 2;

SELECT *
FROM sys.fn_dblog(@startLSN, NULL)

(The output is too big to show here, and dbfiddle.uk won't allow me to access fn_dblog)

The first set of output shows the log as a result of the DDL statement dropping the column.  The second set of output shows the log after running the DML statement where we update the rid column.  In the second result set, we see log records indicating a delete against dbo.DropColumnTest, followed by an insert into dbo.DropColumnTest.  Each Log Record Length is 8116, indicating the actual page was updated.

As you can see from the output of the fn_dblog command in the test above, the entire operation is fully logged.  This goes for simple recovery, as well as full recovery.  The terminology "fully logged" maybe misinterpreted as the data modification is not logged.  This is not what happens - the modification is logged, and can be fully rolled back.  The log is simply only recording the pages that were touched, and since none of the table's data-pages were logged by the DDL operation, both the DROP COLUMN, and any rollback that might occur will happen extremely quickly, regardless of the size of the table.

For science, the following code will dump the data pages for the table included in the code above, using DBCC PAGE, style "3".  Style "3" indicates we want the page header plus detailed per-row interpretation.  The code uses a cursor to display the details for every page in the table, so you may want to make sure you don't run this on a large table.

DBCC TRACEON(3604); --directs out from DBCC commands to the console, instead of the error log
DECLARE @dbid int = DB_ID();
DECLARE @fileid int;
DECLARE @pageid int;
DECLARE cur CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY
FOR
SELECT dpa.allocated_page_file_id
    , dpa.allocated_page_page_id
FROM sys.schemas s
    INNER JOIN sys.objects o ON o.schema_id = s.schema_id
CROSS APPLY sys.dm_db_database_page_allocations(DB_ID(), o.object_id, NULL, NULL, 'DETAILED') dpa
WHERE o.name = N'DropColumnTest'
    AND s.name = N'dbo'
    AND dpa.page_type_desc = N'DATA_PAGE';
OPEN cur;
FETCH NEXT FROM cur INTO @fileid, @pageid;
WHILE @@FETCH_STATUS = 0
BEGIN
    DBCC PAGE (@dbid, @fileid, @pageid, 3);
    FETCH NEXT FROM cur INTO @fileid, @pageid;
END
CLOSE cur;
DEALLOCATE cur;
DBCC TRACEOFF(3604);

Looking at the output for the first page from my demo (after the column is dropped, but before the column is updated), I see this:

PAGE: (1:100104)

BUFFER:

BUF @0x0000021793E42040

bpage = 0x000002175A7A0000          bhash = 0x0000000000000000          bpageno = (1:100104)
bdbid = 10                          breferences = 1                     bcputicks = 0
bsampleCount = 0                    bUse1 = 13760                       bstat = 0x10b
blog = 0x212121cc                   bnext = 0x0000000000000000          bDirtyContext = 0x000002175004B640
bstat2 = 0x0

PAGE HEADER:

Page @0x000002175A7A0000

m_pageId = (1:100104)               m_headerVersion = 1                 m_type = 1
m_typeFlagBits = 0x0                m_level = 0                         m_flagBits = 0xc000
m_objId (AllocUnitId.idObj) = 300   m_indexId (AllocUnitId.idInd) = 256
Metadata: AllocUnitId = 72057594057588736
Metadata: PartitionId = 72057594051756032                                Metadata: IndexId = 1
Metadata: ObjectId = 174623665      m_prevPage = (0:0)                  m_nextPage = (0:0)
pminlen = 8                         m_slotCnt = 1                       m_freeCnt = 79
m_freeData = 8111                   m_reservedCnt = 0                   m_lsn = (616:14191:25)
m_xactReserved = 0                  m_xdesId = (0:0)                    m_ghostRecCnt = 0
m_tornBits = 0                      DB Frag ID = 1

Allocation Status

GAM (1:2) = ALLOCATED               SGAM (1:3) = NOT ALLOCATED
PFS (1:97056) = 0x40 ALLOCATED   0_PCT_FULL                              DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED

Slot 0 Offset 0x60 Length 8015

Record Type = PRIMARY_RECORD        Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS
Record Size = 8015
Memory Dump @0x000000B75227A060

0000000000000000:   30000800 01000000 02000001 004f1f5a 5a5a5a5a  0............O.ZZZZZ
0000000000000014:   5a5a5a5a 5a5a5a5a 5a5a5a5a 5a5a5a5a 5a5a5a5a  ZZZZZZZZZZZZZZZZZZZZ
.
.
.
0000000000001F2C:   5a5a5a5a 5a5a5a5a 5a5a5a5a 5a5a5a5a 5a5a5a5a  ZZZZZZZZZZZZZZZZZZZZ
0000000000001F40:   5a5a5a5a 5a5a5a5a 5a5a5a5a 5a5a5a             ZZZZZZZZZZZZZZZ

Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4

rid = 1

Slot 0 Column 67108865 Offset 0xf Length 0 Length (physical) 8000

DROPPED = NULL

Slot 0 Offset 0x0 Length 0 Length (physical) 0

KeyHashValue = (8194443284a0)

I've removed most of the raw page dump from the output shown above for brevity.  At the end of the output, you'll see this for the rid column:

Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4

rid = 1

The last line above, rid = 1, returns the name of the column, and the current value stored in the column on the page.

Next, you'll see this:

Slot 0 Column 67108865 Offset 0xf Length 0 Length (physical) 8000

DROPPED = NULL

The output shows that Slot 0 contains a deleted column, by virtue of the DELETED text where the column name would normally be.  The value of the column is returned as NULL since the column has been deleted.  However, as you can see in the raw data, the 8,000 character long value, REPLICATE('Z', 8000), for that column still exists on the page.  This is a sample of that part of the DBCC PAGE output:

0000000000001EDC:   5a5a5a5a 5a5a5a5a 5a5a5a5a 5a5a5a5a 5a5a5a5a  ZZZZZZZZZZZZZZZZZZZZ
0000000000001EF0:   5a5a5a5a 5a5a5a5a 5a5a5a5a 5a5a5a5a 5a5a5a5a  ZZZZZZZZZZZZZZZZZZZZ
0000000000001F04:   5a5a5a5a 5a5a5a5a 5a5a5a5a 5a5a5a5a 5a5a5a5a  ZZZZZZZZZZZZZZZZZZZZ
0000000000001F18:   5a5a5a5a 5a5a5a5a 5a5a5a5a 5a5a5a5a 5a5a5a5a  ZZZZZZZZZZZZZZZZZZZZ


How to do Incremental statistics on auto_created stats(_WA_) in SQL server

You have the default settings.

is_auto_update_stats_on= True;
is_auto_update_stats_async_on= False;

So when you run a query that needs updated stats, the stats will update before your query runs.  You could update stats more agressively, or allow the query to run with the old stats while the stats are updated by turning on 'is_auto_update_stats_async_on'.

And yes, modifying an auto-created statistic to be incremental appears to work fine.  eg in AdventureWorksDW after partitioning FactInternetSales:

ALTER DATABASE current SET AUTO_CREATE_STATISTICS ON (INCREMENTAL = ON)
GO
UPDATE STATISTICS [dbo].[FactInternetSales]([_WA_Sys_00000004_4E88ABD4]) WITH INCREMENTAL = ON
GO
UPDATE STATISTICS [dbo].[FactInternetSales]([_WA_Sys_00000004_4E88ABD4]) WITH RESAMPLE ON PARTITIONS(2)
GO


I'd recommend configuring log shipping to migrate your databases. Hours/days/weeks before your planned migration, configure log shipping from your current (2014) production Availability Group. You should perform the log shipping restores on all nodes of the new (2016) Availability Group.

Also in advance, pre-configure the Availability Groups on the new servers, but do not create the Listener.

This will minimize downtime during your cutover maintenance by restoring the majority of your data ahead of the maintenance window.

During the cutover, the steps to complete the migration would be:

Shut down applications/kill existing sessions.
Perform tail log backups for the (2014) databases you are migrating. (BACKUP LOG... WITH NORECOVERY)
Allow the log shipping restore jobs to run a final time, or manually restore the final logs to all new (2016) servers WITH NORECOVERY.
On one of the new servers, recovery the databases (RESTORE DATABASE... WITH RECOVERY).
Add the databases to the Availability Groups, and join secondary servers.
Drop the Listeners on the old (2014) AGs.
Create the Listeners on the new (2016) AGs.
Turn on applications.


Those users would need to run SSMS as an administrator in order to connect. It's explained here.


Which databases are recommended to be used as embedded databases?

A list can be found on Wikipedia. I think the overwhelming leader in embedded sql databases is sqlite. Found everywhere!


sql server: get all the databases in which a login is being used

Check out my answer on a similar question. Code below will return a list of logins and a flag if they're currently mapped to a database user.

CREATE TABLE ##UnusedLogins (
    [Login] NVARCHAR(255),
    [InUse] BIT
)

INSERT INTO ##UnusedLogins ([Login], [InUse])
SELECT [login].[name] AS [Login],
    0 AS [InUse]
FROM sys.server_principals [login]
WHERE [login].[name] NOT LIKE 'NT SERVICE\%'
    AND [login].[name] NOT LIKE 'NT AUTHORITY\%'
    AND [login].[name] NOT LIKE '##%'
    AND [login].[type_desc] <> 'SERVER_ROLE'
    AND [login].[name] <> 'sa'

EXEC sp_msforeachdb 'UPDATE ul
SET [InUse] = 1
FROM ##UnusedLogins ul
INNER JOIN sys.server_principals [login] ON [login].[name] = ul.[Login]
INNER JOIN [?].sys.database_principals [user] ON [user].[sid] = [login].[sid]'

SELECT * FROM ##UnusedLogins

DROP TABLE ##UnusedLogins


mysqldump: Got error: 2049: Connection using old (pre-4.1.1) authentication protocol refused (client option 'secure_auth' enabled)

If you don't use a newer or equal version as 5.7.5 as client, you can add the parameter --skip-secure-auth to mysqldump. But since this is deprecated and not supported in newer version, it's best to migrate away from Pre-4.1 Password Hashing and the mysql_old_password plugin


Problem with SELECT CASE WHEN THEN, 0 result

found solution, sorry, my mistake !

SELECT i.fav_id,
        CASE
           WHEN i.fav_id = 2 THEN 3
           WHEN i.fav_id = 3 THEN 2
           WHEN i.fav_id = 7 THEN 8
           WHEN i.fav_id = 8 THEN 7
           ELSE i.fav_id
       END fav_id
FROM `favorites` AS i WHERE i.member = '123456'


How to setup local database development process for small web team?

Managing environments

I think you definitely don't want to be forced into a single database version.  You've got enough developers that you will inevitably have multiple development work streams, and requirements to apply patches to the current production environment independent of development workstreams.

You can use Liquibase or a manual process to produce patch scripts to upgrade versions.  I suggest starting out with a manual process and using the schema comparison tool for QA on the patches.  Clean, automated, transparent synchronisation of a nontrivially complex database is a bit utopian.

Your central data model can be kept in whatever system takes your fancy.  I've used everything from tedious enterprise repository tools to create table scripts.  Create table scripts play nicely with ordinary source control tools such as subversion and not all repository tools do a good job of versioning.

Whatever you use as your master data model repository you need a fairly clean mechanism for deploying an environment from that model.  It should be structured so that rollouts to an environment are easy.  You also need a mechanism to patch from one released version to the next.

What I've done

I've done the following in the past when I was managing development environments.  It's not particularly high tech, but it's amenable to version control and automated builds, so it makes it easy to roll out an environment to a specific version, and maitaining a large number of environments is quite practical.

Maintain a central repository: This could be a set of database creation scripts held in a version control systems, or a repository model in a data modelling tool.  Take your pick.  This model should have a build mechanism that allows an environment to be rolled out from the scripts without a lot of manual intervention.

If you have a lot of reference data you will need a load mechanism for it.  Depending on how you want to do it, you could keep this in a database or in a set of files.  The advantage of files is that they can also be versioned and labelled from the same version control system as your code base.  A bunch of CSV files and bulk loader scripts in a source control repository can do this easily enough.

One option for deploying development environments is to take backups of the production database patched to the appropriate version and make them available for devs to restore into a development environment.

Make it easy to roll out: Like any CI build process, the database should be deployable from a single script.  Set it up so that database connections can be paramaterised, or the script is location independent and can just be run through the connection.

Patch scripts: You will need roll forward and probably roll back scripts from each released version.

Build test environments from the repository model: This ensures that development on environments that are out of sync with the repository gets caught in testing.

Test the deployment process: Automated patching scripts, however they are created should be testable.  Schema comparison tools are quite good for this, even if you dont't use them to generate the patch scripts.

Create a reference environment with the repository model build you tested against
Create a smoke test environment with either a backup of your production release or a build based on the current released version.
Run the patch script against the smoke test environment.
Use the schema comparison tool to compare the smoke test environment with the reference environment.  The patch script should result in the two databases being identical, so you can investigate any differences.

What I like about this process

This is a bit heavyweight, and was designed for deploying into fairly bureaucratic and opaque production environments.  However, it has the following strengths:

Developers can tinker where they need to.
Multiple branches and streams of development can be accommodated.
The deployment scripts themselves can be tested in a repeatable manner.  This is very helpful to shut down deployment bunfights, as the repeatability can be demonstrated.
The schema comparison tools provide QA on the deployment itself.
Testing is always done against what is expected to be released, and this will catch issues arising from environments being out of sync.
Deployment based on repository models and patch scripts means that uncontrolled rubbish doesn't get accidentally migrated from development environments into production.
A lot of the process can be automated, although it is often desirable to prepare and test the deployment patch scripts manually.
Environments are cheap and easy to deploy without having to jump through hoops.
Developers are forced to make a system that is amenable to a simple build and deployment process.
Developers are forced to learn basic database administration tasks, but test and production environments are insulated from noob mistakes.

How it addresses your requirements

Developers have local copy of data to run development code againstThe deployment scripts or DB images mean that they can set up an environment from any version that's available.
Able to rollback database structure to a previous changesetAgain, sorted by the deployment scripts.  Either through DDL or test database backup images created through a controlled process, developers can bring up an environment to any specific version that you have.
Able to separate new feature schema changes vs schema design fix changesPatches to a common version can be maintained in a separate fork in the svn tree.  If database backups are used as the reference environments they need to be stored somewhere with the same folder structure as the branching of the source control projects.
Able to modify database structure locally for testingThe simple deployment process allows devs to tinker, and easily restore an environment to a local state, or bring up a reference environment to do comparisons and make change sets against.


If I update a column record in a table, will indexes that do NOT have this column in it be affected?

No, _idx_TableA will not be affected for this operation.  I have modified your example and added another index (NCI) that actually includes the key column Col3.  Here's my example code:

use testdb;
go

CREATE TABLE [DBO].[TableA](
    [Col1] [nchar](5) NOT NULL,
    [Col2] [nchar](2) NULL,
    [Col3] [int] NULL
CONSTRAINT [TableA_PK] PRIMARY KEY CLUSTERED
(
    [Col1] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [_idx_TableA]
ON [DBO].[TableA] ([Col2])
WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
GO

create nonclustered index IX_Col3
on dbo.TableA(Col3);
go

insert into dbo.TableA
values
    ('a', 'b', 10),
    ('b', 'c', 11),
    ('c', 'd', 12);
go

update dbo.TableA
set Col3 = 13;

If I capture the post execution plan of the UPDATE command, you will see something similar here:

As you see from the above screenshot, the indexes that are updated are the clustered index, and my nonclustered index IX_Col3.  The index _idx_TableA does not get updated.  Therefore, only the indexes that contain Col3 will be affected, and therefore updated by your particular example.


ODBC connection for testing Availability Group Listener

Converting my comment to answer :

You can use SQLCMD.exe -S <AG listener> -D <AG database> -E.

You may want to specify AGlistener,port_no to see if you can connect to the listener.


This resetroot.bat method did not work for me, even after following the instructions above.

My XAMPP uses this DB version:

Server: MariaDB
Server version: 10.0.17-MariaDB mariadb.org binary distribution

However, using this answer, I was able to do it manually.

Go to your **xampp\mysql\bin** folder
Edit my.ini and insert skip-grant-tables below [mysqld]
Restart MySQL
Set new password for your root user by running UPDATE mysql.user SET
Password=PASSWORD('new_password') WHERE User='root' in phpMyAdmin in
the mysql database (or just leave it like this if MySQL cannot be
accessed from remote hosts)
Remove the skip-grant-tables in the my.ini file


The easiest solution is actually free: Microsoft's Virtual Labs. You get access to SQL Servers in the cloud, plus step-by-step instructions on what to experiment with. (You aren't required to do their tutorials, either - you can use the VMs for anything you like.)

Keep in mind that there's a few restrictions: you can't surf the web from there, and you can't copy/paste things in. Because of that, it's a little limited - but you can't beat free.

If you're going to run your own SQL Server, I wouldn't recommend doing it in the cloud since you pay by the hour. You're better off using a free virtualization tool like Hyper-V, ESXi, or VirtualBox, and then installing the totally free SQL Server Developer Edition. Good luck on your learning journey!


Is there any ways to retrieve datatable in row field in mysql database

The closest you can get is to use the GROUP_CONCAT function:

SELECT car.*, GROUP_CONCAT(color SEPARATOR ',')
FROM car
INNER JOIN carColor ON car.id=carColor.carID
GROUP BY carColor.carID;

[sqlfiddle]

Be aware of the group_concat_max_len option that can truncate your concat results.


Availability Groups - Can async replica delay failover of sync replica?

When a Secondary (Node 3) is running in Asynchronous-Commit mode, Primary (Node 1) does not wait for a confirmation that Secondary has indeed finished writing the logs to its own log file.
It will still wait for a confirmation from a secondary in Synchronous-Commit mode (Node 2) though.

Right after Node 1 will be finished writing the log record to its own log file, it will consider that the transaction has been completed and the client will be notified.

With Asynchronous-Commit mode, Secondary is considered to never be synchronized with the Primary.
Secondary always remains unsynchronized and some lag with the Primary is to be expected although it will try to keep up and catch up with the Primary,

Therefore problems or lags on a Secondary in Asynchronous-Commit mode won't impact the Primary.

Much more information are available on this MSDN page: Availability Modes (AlwaysOn Availability Groups)


SQL Server 2016 SP 1 on HP 3PAR environment causes weird head blocker

When you're troubleshooting a blocking chain happening live, start with sp_WhoIsActive:

sp_WhoIsActive @get_locks = 1

There's a blocked-by column that shows you who's blocking who. The lead blocker won't have anyone in their blocked-by column.

Once you've found the lead blocker, click on the locks column - it's an XML field that expands to show you the full list of locks held by that session.

It could be that the select is part of a longer transaction (there's also an open transactions column), or that the select isn't really what's blocking.

Your best bet for getting specific, actionable advice here would be to post a picture of the sp_WhoIsActive output, including the blocking columns, and then the contents of the XML for the locks.


Query to change all field in a given mysql table?

Perhaps this UPDATE may do it:

UPDATE tablename SET `time` = `time` - INTERVAL 2 YEAR;