MySQL write performance after truncate & optimize Once a year we truncate a table of  several GBs and millions of rows and then run optimize in order to shrink the size of the corresponding ibd file on the file system.
However, as this is write intensive table, we are wondering whether running optimize is actually a good idea. Maybe it’s smarter to leave the file size as is and let MySQL reuse already allocated disk space.
Is it more efficient in terms of write performance to optimize the table in order to shrink the file size and then let it progressively grow again, or just reuse space previously allocated?
We use InnoDB as storage engine and MySQL 5.7 as DBMS.

TRUNCATE TABLE throws away the table and creates a new, empty table, with the same schema.  There is nothing left to OPTIMIZE.
If you are really throwing away the data, why did you keep it?  Or, maybe you would prefer to toss the "oldest" part of the data each week?  And do it with virtually no impact on writes?  If so, you have a classic case for PARTITIONing.  Use PARTITION BY RANGE(TO_DAYS(..)) and a weekly task to DROP the  oldest partition and deal with making a new partition.  Details:  http://mysql.rjweb.org/doc.php/partitionmaint
Be sure to have innodb_file_per_table=ON.
(If this does not address your Question, please elaborate on it.)


What are the pro's and con's of using PostgreSQL vs MySQL for binary data? I read that MySQL has binary safe blobs, but PostgreSQL does not.

So far what I've found suggests that "binary safe" is something that really relates to functions and handling of data, not the data itself. It seems that the difference is that MySQL natively has ways of storing and manipulating blobs without having to specify data formatting, but PostgreSQL requires using either "quoted-printable
encodings" or "base64" encoding for binary data.

Is that all there is to it? What else should I worry about with respect to the differences between MySQL and PostgreSQL in the handling of blobs? I'm starting a new project, not migrating data, so I'm just wondering if there are particular reasons to choose one or the other with respect to their handling of binary blobs.

That reference you link to is complete nonsense.  PostgreSQL supports binary data and binary-safe handling of data just fine.

There will be some small differences between MySQL and PostgreSQL about how the types and certain functions are named, but overall it won't make much difference.  There are probably other, better reasons for choosing one system over the other.


Lowering MySQL Memory Usage without Restart We are using Percona Server 5.5.35-rel33.0-611.precise with InnoDB in our production server. Recently Mysql memory went high and it did't drop from there. We are afraid whether it would result in OOM sooner. We would like know whether mysql memory can be freed with out doing a restart

we tried RESET QUERY CACHE but it didn't help

We came across this post https://serverfault.com/questions/238...ear-the-buffer
where it says we can use

innodb_max_dirty_pages_pct=0;

to clear the buffer. We would like to know whether its safe to use in Prodution enviorment( no data loss) ?

Does it clear the clear memory ?

does it require a mysql restart ?

Please advise

thanks,
Santhosh

Have a look at the InnoDB Architecture from Vadim Tkachenko

When you set innodb_max_dirty_pages_pct to 0, it just expedites dirty pages out of the buffer pool. The changed pages also exist in the Double Write Buffer inside the System Tablespace (ibdata1).
Even if mysqld crashes, the crash recovery cycle is initiated during mysqld's start up. Thus, no need to worry about data loss.

Does it clear the clear memory ?

No, it does not clear memory. It simply sends the pending changes (data pages and index pages) from the Buffer Pool into the Double Write Buffer and then to the .ibd files.

does it require a mysql restart ?

No. Login to MySQL and run
mysql> SET GLOBAL innodb_max_dirty_pages_pct = 0;

Please note that this clearing of the buffer pool happens on MySQL shutdown anyway. Setting innodb_max_dirty_pages_pct to 0 will keep the buffer pool as clean as possible. Doing this about 5 minutes before doing an actual shutdown will allow for a faster shutdown without changing anything else.
If you want the setting to be permanent, add this to my.cnf
[mysqld]
innodb_max_dirty_pages_pct = 0

You can do this in a production environment and it would be safe if your have really good disks. If the Disks are slow, you will see increased I/O. If you have a high-write application, you should leave it set to the default value of 75. (If you use MySQL 5.1, the default is 90).
OTHER THINGS TO TRY
Drop the Query Cache and Disable Query Cache Mutexing
mysql> SET GLOBAL query_cache_size = 0;
mysql> SET GLOBAL query_cache_type = 0;

From my post https://dba.stackexchange.com/questions/65888/recommendation-for-mysql-please-mysqltuner/65928#65928

Reduce max_connections in half
Reduce the sizes of your per-connection buffers

join_buffer_size
sort_buffer_size
read_buffer_size
read_rnd_buffer_size

GIVE IT A TRY !!!
CAVEAT : If you change any of these settings and you want them permanent, add them to my.cnf so that future restarts of mysqld will have the setting in place.


Does SQL Server support restoring to a point in time down to the millisecond? Using the Management Studio GUI, I am only able to restore a database to a point in time down to the second.  Is this just a limitation of the GUI or if I use a script will it restore to the millisecond I specify?

I'm trying to do it with a script and it 'seems' like it's ignoring the millisecond but unfortunately the date value I'm comparing to is, unfortunately, not necessarily the exact time the record was written so I just want to rule out any possibility that the problem could be because of a limitation of SQL Server.

Yes that is a limitation of SSMS.  When using T-SQL you can control it to the three millisecond mark.  The input is done via a datetime variable so it's bound by the normal datatime data type rules which is precise to the 3 milliseconds.


How to clear Last_SQL_Error in show slave status a failed "create user root" query error is being displayed in show slave status\G

Password is visible there and I want to clear the error. How should I?

You can do this with

STOP SLAVE;
RESET SLAVE;
START SLAVE;

read more about it here


Compare The length of two string variables inside an Expression task in SSIS Hope you're doing well.
I need to compare the Length of two string variables inside an expression task in SSIS so that if they have the same length the package will execute the next step and if not it will stop
I've implemented something like this so far but it won't work

The images show My expression task and the regular expression within the
    task and the precedence constraint from the expression task to the next  step

I found the answer
I Created a variable @[User::Process] with Boolean data type and
I changed the Expression to this

@[User::Process] = (DT_BOOL)((DT_I4) LEN( @[User::FileFormat] )==LEN( @[User::ExcelName] ) ? 1: 0)

And Changed the precedence constraint of the expression task to This


How can I get identity-like values in a MERGE INSERT statement I would like to merge a table bar into another table foo, and while doing so I have to assign a unique value to a column messagenr. This is my attempt to do so (sql-server):

MERGE foo AS target
using bar AS source
ON target.message LIKE source.message
WHEN NOT matched BY target THEN
INSERT (messagenr,
        language,
         message)
VALUES ((SELECT Max(messagenr)
         FROM   foo)
       + 1,
       'EN',
       source.message);

Of course it is not working. I get the very same value for messagenr for every new dataset inserted.
I cannot modify the table structure itself, so I have to work with the structure given.

How can I get a UNIQUE value for messagenr in every new dataset in the target table?

Let me set up next example:

CREATE TABLE foo(messagenr int, language varchar(10), message varchar(100));
CREATE TABLE bar(language varchar(10), message varchar(100));
GO

INSERT INTO foo VALUES
(1, 'EN', 'M1'),
(2, 'EN', 'M2'),
(3, 'ES', 'M3');

INSERT INTO bar VALUES
('FR', 'M1FR'),
('CA', 'M1CA'),
('DE', 'M1DE');
GO

Since you can't increase messagenr value inside the transaction, and your MERGE statement only insert values, maybe you could use another approach.

You can use a CTE or a subquery to enumerate the returned rows in this way:

SELECT (SELECT MAX(messagenr) FROM foo) +
       ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RN,
       bar.language,
       bar.message
FROM   foo
JOIN   bar
ON     bar.message LIKE '%' + foo.message + '%'

RN | language | message
:- | :------- | :------
4  | FR       | M1FR
5  | CA       | M1CA
6  | DE       | M1DE

Now you can insert new values using the previous query:

;WITH x AS
(
    SELECT (SELECT MAX(messagenr) FROM foo) +
           ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RN,
           bar.language,
           bar.message
    FROM   foo
    JOIN   bar
    ON     bar.message LIKE '%' + foo.message + '%'
)
INSERT INTO foo (messagenr, language, message)
SELECT RN, 'EN', message
FROM   x;

This is the final result:

SELECT * FROM foo;

messagenr | language | message
--------: | :------- | :------
        1 | EN       | M1
        2 | EN       | M2
        3 | ES       | M3
        4 | EN       | M1FR
        5 | EN       | M1CA
        6 | EN       | M1DE

dbfiddle here


Unique constraint error Oracle I have two databases - one is 11g and another 12c. 12c db has the exact data of 11g (expdp/impdp). Now I'm facing an error in one of the tables with unique constraint error. I have checked the structure of the table and constraints enabled for the table in both the databases, that is same as well. Now how can I sort this out? I'm facing this error on the application which is running on it, when I click one of the tabs.
Caused by: java.sql.SQLIntegrityConstraintViolationException: ORA-00001:unique constraint (USER1.HIGHT_PK) violated is the error i'm getting

You wrote

  12c db has the exact data of 11g (expdp/impdp)

But are you sure?

1) Did you make a consistent export of your tables? This means
all table data is from the same point in time. For this it is sufficient that you
stop all other write activity on the database during export. Or you set the flashback_scn parameter
when using expdp or the consistent parameter when using exp.

2) Are your sequence value consistent with your table data? This means
all table data and the state of the sequence is from the same point in time.
  For this it is sufficient that you
stop all other read and write activity on the database during export.
This is the only way i know to guarantee this consistency.
I think that enen the flashback_scn or consistent parameters can't help here.
They are useful for for transactional data only. I think the structures like sequences are extracted before the table data is read, here is the log from an export
https://community.oracle.com/thread/3519031

> expdp system/system123 directory=DUMP_DIR dumpfile=hsfa.dump schemas=HSFA content=metadata_only

Export: Release 10.2.0.5.0 - 64bit Production on Thursday, 20 February, 2014 7:55:03

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_16":  system/******** directory=DUMP_DIR dumpfile=hsfa.dump schemas=HSFA content=metadata_only
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SYNONYM/SYNONYM
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
(...)

Solution

If you didn't make a data consistent export (case 1) you should repeat the export with the appropriate [consistency parameter].
If you have a problem with the sequences (case 2) you can try the following. Recreate the sequences of
the target database (where you have imported the schema) like  the current sequences of the source database.
Now all sequences produce values that weren't yet used by the application.
 Most application have no problems (should not have problems) if there are gaps in the series of sequence numbers.


SQL Server 2000-2014 migration using 2008 Express as intermediary We have SQL Server 2000 Standard Edition, and we're researching migration to SQL Server 2014.  I already know that I need to use 2008 as an intermediate step.  My question is, do we need to purchase a licensed 2008, or will 2008 Express work.  The 2008 edition will only be installed briefly (as long as it takes to restore to it, and then backup from it), so I'm hoping Express is good enough.

You could use Express, but it does not have all features that the full versions do and cannot handle databases larger than 10GB each. Please note the 10 GB restriction applies to the total size of data files only (so, for example, log files do not count toward the limit).

If that does not fit your needs, you could use a trial version of SQL Server 2008 R2 (ISO Image, or VHD) which is good for 180 days. As Aaron has researched, the EULA for the trial does cover this activity. The EULA states,

  You may install and use any number of copies of the software on your devices. You may use the software only to demonstrate and internally evaluate it.
  You may not use the software in a live operating environment unless Microsoft permits you to do so under another agreement.

Which does apparently include the use of an evaluation copy as a stepping stone.

Either way, you should  use SQL Server 2008 R2 and not SQL Server 2008.


Microsoft Optimal method for columns into 1 word: XML Raw or Concat What is fastest, performance method to make columns into one word? Does Microsoft have recommendation, or is there performance Big-O studies on FOR XML RAW vs Concat?

Company has multiple tables with different column types including int, varchar, string, different number of columns, some not/not null, maybe anywhere from 5-25 columns in a table.

SELECT (Column1, Column2, Column3, Column4... FOR XML RAW)))

SELECT CONCAT(Column1,'|',Column2,'|',Column3,'|',Column4...))

We are seeing different timing results on our server, however both seem to be relatively close, curious if there is any general optimal to apply, rather than reviewing 1000 tables.

Background will be utilized to apply hashing of row later.

In general, if you truly need to know what will be the fastest for your workload then you need to do your own benchmarking. Take a look at the following query plans:

Which one do you think would be faster if you had to pick one without any additional context? I would pick the bottom one. It simply appears to do less work because there's no nested loop join and there's no UDX operator. To generate the above query plans, put 6.5 million rows into a temp table:

SELECT s1.*
INTO #source_data
FROM master..spt_values s1
CROSS JOIN master..spt_values t2;

This table has six columns with different data types. Some allow nulls and some don't. So it seems to meet your criteria. The top query takes about 3X longer on my machine than the bottom one:

DROP TABLE IF EXISTS #t;

SELECT HASHBYTES('SHA2_256', (SELECT name, number, type, low, high, status FOR XML RAW)) hash_value
INTO #t
FROM #source_data
OPTION (MAXDOP 1);

GO

DROP TABLE IF EXISTS #t;

SELECT HASHBYTES('SHA2_256', CONCAT(name, '|', number, '|', type, '|', low, '|', high, '|', status)) hash_value
INTO #t
FROM #source_data
OPTION (MAXDOP 1);

This seems like a perfectly reasonable result. It will add overhead to convert data to XML and to have a nested loop join. Why do that when you don't have to? In your question you suggest that sometimes the FOR XML RAW technique is faster for your data. If so, there's something about your data or testing methodology that you aren't telling us. My suggestion is to come up with a sharable reproduction that demonstrates that the FOR XML RAW method is significantly faster and to ask a new question. As is we have to guess what's going on, which isn't helpful for anyone.


External compression tools for database backup files Can I use external compression tools like WinZip, 7zip or winrar to compress rman backup files?

The DBMS is Oracle. A senior DBA claims that backups could be damaged by external compression tools. He argues that the tools shrink blocks which are not full and at a restore the database might have problems because control sums don’t match. I didn't find a source on that.

Since the backup is just a file, you can use whatever file tools there exists, including any and all compression tools.

In order to restore, you will ofcourse need to uncompress the file first.

A far better solution is to use any built-in compression of the backups from whatever sql engine you use. That saves both IO, space and time.


Is there a more efficient way to run this query for first/max transaction? For each account, I need the first transaction date, the largest transaction amount, and the time taken to get from first to largest. Data is truncated and reloaded frequently, and I have read-only access (no schema or index changes). The table has well over a million rows, so creating and updating a temp table is way too slow; cross apply seems inefficient as well. With a CTE and window function I only have to hit the table twice. But is there a better approach? Simplified example of the table and my query follow:

CREATE TABLE [dbo].[trans](
    [Tran_ID] [int] IDENTITY(1,1) NOT NULL,
    [Account_ID] [varchar](50) NULL,
    [Tran_Date] [datetime] NOT NULL,
    [Tran_Amount] [money] NOT NULL
) ON [PRIMARY];

EDIT: @Peter the table has the below clustered index. This is the only index, there are no primary or foreign keys or other constraints.

CREATE CLUSTERED INDEX [idx_trans_ID_DATE] ON [dbo].[trans]
(
    [Account_ID] ASC,
    [Tran_Date] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

WITH HighestTran AS (
            SELECT
                Account_ID,
                Tran_Date,
                Tran_Amount,
                ROW_NUMBER() OVER (PARTITION BY Account_ID ORDER BY Tran_Date ASC) AS FirstTranRow,
                ROW_NUMBER() OVER (PARTITION BY Account_ID ORDER BY Tran_Amount DESC) AS MaxTranRow
            FROM
                dbo.trans
            WHERE
                Tran_Amount > 0
            )

SELECT
    t1.Account_ID,
    t1.Tran_Date,
    t2.Tran_Date,
    t1.Tran_Amount,
    t2.Tran_Amount
FROM
    HighestTran t1
    INNER JOIN HighestTran t2 ON t1.Account_ID = t2.Account_ID AND t1.FirstTranRow = 1 and t2.MaxTranRow = 1
ORDER BY t1.Account_ID;

This does it in a single table scan:

SELECT Account_ID
  , MIN(Min_Date) AS DateFirst
  , MIN(DATEDIFF(day, Min_Date, NULLIF(Tran_Date, Min_Date))) AS Days_Passed
  , MAX(Tran_Amount) AS Max_Amount
FROM (
  SELECT Account_ID
     , Tran_Date
     , Tran_Amount
     , MIN(Tran_Date) OVER ( PARTITION BY Account_ID) AS Min_Date
     , MAX(Tran_Amount) OVER (PARTITION BY Account_ID) AS Max_Amount
  FROM trans
  GROUP BY Account_ID, Tran_Date, Tran_Amount
) I
WHERE I.Tran_Amount = I.Max_Amount
  OR I.Tran_Date = I.Min_Date
GROUP BY Account_ID

This query assumes that only a few rows are found per Account_ID. You can improve the speed of the query (which does a sort) by adding Tran_Amount to the index. While your question does say you cannot edit the schema, at least you can have a discussion with the DBA if you see an improvement on experimental data.


Fast way to load a large amount of test data I've trying to populate a table with a load of dummy data so I can about with optimisation etc.

I've got the following:

WHILE @RowCount < 3000000
BEGIN

    SELECT @Random = ROUND(@Upper * RAND(), 0)

    INSERT INTO [dbo].[Test]
               ([Id]
               ,[OtherKey]
               ,[Description])
         VALUES
               (@RowCount
               ,@Random
               ,CAST(@Random AS VARCHAR(max)))

    SET @RowCount = @RowCount + 1
END

However, this seems very slow.

Is there a better way to automate loading semi-random rows into a database table?

New Script

This one seems to be pretty quick:

USE [Test]
GO

/****** Object:  Table [dbo].[Test]    Script Date: 17/10/2016 21:22:39 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

USE [Test]
GO

CREATE TABLE [dbo].[Test](
    [Id] [int] NOT NULL,
    [OtherKey] [int] NOT NULL,
    [Description] [varchar](max) NOT NULL,
    [Time] [datetime] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

-- Add sample data

DECLARE @RowCount INT
DECLARE @Random INT
DECLARE @Upper INT

SET @Upper = 1000
SET @RowCount = 0

WHILE @RowCount < 1000000
BEGIN

    SELECT @Random = ROUND(@Upper * RAND(), 0)

    INSERT INTO [dbo].[Test]
               ([Id]
               ,[OtherKey]
               ,[Description]
               ,[Time])
         VALUES
               (@RowCount
               ,@Random
               ,CAST(@Random AS VARCHAR(max))
               ,GETDATE())

    SET @RowCount = @RowCount + 1
END

GO

/****** Object:  Index [IX_ID]    Script Date: 17/10/2016 22:18:48 ******/
CREATE CLUSTERED INDEX [IX_ID] ON [dbo].[Test]
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

/****** Object:  Index [IX_OtherKey]    Script Date: 17/10/2016 21:22:46 ******/
CREATE NONCLUSTERED INDEX [IX_OtherKey] ON [dbo].[Test]
(
    [OtherKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

If anyone has any faster way though then I'd be very happy to hear it.

Here are a few guiding principles to load not small amounts of test data:

1) When possible use minimal logging.

If you can avoid writing unnecessary data to the transaction log, great, why not do that? This will require your test database to use a recovery model of simple. You also need to be careful to follow the rules which allow you to get minimal logging. If you need to insert into a table with a clustered index that already contains data trace flag 610 can help.

2) Avoid table and column DDL choices which contribute to unnecessary overhead.

As you discovered it is sometimes more efficient to create a clustered index after the data is loaded instead of before it, although it can go either way depending on the data. Sometimes creating the clustered index after can be faster simply because SQL Server can insert data in parallel into an existing heap (as of SQL Server 2016) and it can create the clustered index in parallel. This won't be a minimally logged operation and it will require all of the table's data to be sorted so this might not work if your development system isn't sized large enough.

As you also discovered, definitely create nonclustered indexes after you have loaded the data instead of before.

I believe that there is some overhead to using the VARCHAR(MAX) datatype so I would avoid that if possible.

3) Avoid row by row operations when possible. SQL Server is generally more efficient with set based solutions.

There are a few row by row operations in your code. You are doing a WHILE loop and just processing one row at a time. You're also creating a transaction for every row that you're inserting. There must be some overhead to creating and committing a transaction, right? Why pay that for every row? If your system is big enough you can often just insert your test data with a single query, especially if you are able to use minimal logging.

4) Use parallelism when possible.

The code that you have runs one row at a time and cannot take advantage of multiple cores on the server.

I will give you one way to approach problems like this, but this query may not meet your needs exactly.

WITH
L0 AS (SELECT 1 AS c UNION ALL SELECT 1),
L1 AS (SELECT 1 AS c FROM L0 A CROSS JOIN L0 B),
L2 AS (SELECT 1 AS c FROM L1 A CROSS JOIN L1 B),
L3 AS (SELECT 1 AS c FROM L2 A CROSS JOIN L2 B),
L4 AS (SELECT 1 AS c FROM L3 A CROSS JOIN L3),
L5 AS (SELECT 1 AS c FROM L4 A CROSS JOIN L4),
NUMS AS (SELECT 1 AS NUM FROM L5)
SELECT TOP 1000000
  CAST(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS INT) [ID]
, CAST(t.RAND_VALUE AS INT) [OTHERKEY]
, CAST(t.RAND_VALUE AS VARCHAR(100)) [DESCRIPTION]
, CAST(GETDATE() AS DATETIME) [TIME]
INTO [X_JRO_TEST_RAND_2]
FROM NUMS CROSS JOIN (SELECT ROUND(1000 * RAND(CHECKSUM(NEWID())), 0) RAND_VALUE) t;

I will break the query down for you using the guiding principles that I listed at the top of the post.

1) SELECT INTO creates a HEAP table as part of the insert. If the database has a recovery model of simple then this operation will be minimally logged. I can save work by not writing unnecessary data to the transaction log.

2) The table does not have any indexes before loading data. I'm also using VARCHAR(100) instead of VARCHAR(MAX).

3) To generate numbers efficiently I'm using a technique popularized (probably created) by Itzik Ben-Gan. All of the CTEs are there to generate 1000000 rows. I would check out the article for more details on how the approach works. To make RAND() return a different value for each row I used one of the techniques documented in this stack overflow post.

4) As of SQL Server 2014 the query optimizer can insert data into a heap in parallel when using SELECT INTO syntax. As of SQL Server 2016 the query optimizer can insert data into a heap in parallel even without SELECT INTO syntax. On my test system the query runs in parallel.

On a test system here your code took 10:30 for a million rows but the query above took only 23 seconds. I only measured the data loading step and didn't bother with the indexes. It's worth pointing out that there are probably even more efficient ways to generate data in the form that you wanted, and that my code does not have different values for the TIME column.

It's also worth pointing out that if your solution runs fast enough for your purposes, great, go ahead and keep it. Sometimes it isn't necessary to get every last bit of performance out of code, especially code run behind the scenes for development purposes.


Creating a view with a Left Outer Join and Where Condition Let's say I have a User Table and a User Record table which lists Data Sources associated with that User.

I want to create a (Sql Server) View that lists all the contents of a User, and an optional column which lists a specific row from a User Record table only if a particular condition is met.

This is what I have so far:

create view
select user.*,user_record.external_identifier
from [User] user
left outer join [User Record] user_record on user_record.USER_ID = user.ID

Adding a "where user_record.source = 'Peoplesoft' will filter out the User rows which do not have an associated Peoplesoft User record.

If I have 100 users in the User table and 95 of those users have Peoplesoft User Records, I want 100 records to get returned with a "null" in the last column for the five without an association.

I know this is relatively simple, but I'm struggling. Thanks in advance.

In this particular case what you have isn't a WHERE condition.  It's actually part of the join.

select user.*,user_record.external_identifier
from [User] user
left outer join [User Record] user_record
    on user_record.USER_ID = user.ID
    AND user_record.source = 'Peoplesoft'

In English, you want to return every row from [User] and any information from [User Record] where there is a match based on USER_ID/ID AND the [User Record].source is Peoplesoft.

Putting it in the where clause would mean you want to return every row from [User] and any information from [User Record] where there is a match based on USER_ID/ID and from that result set return only those rows from this output that meet the condition [User Record].source = Peoplesoft.

CREATE TABLE #user (id int, col2 int)
CREATE TABLE #userrec (user_id int, source varchar(50))

INSERT INTO #user values (1,1),(2,2),(3,3)
INSERT INTO #userrec values (2,'Peoplesoft'),(3,'test')

-- Without the condition at all
SELECT *
FROM #user #user
left outer join #userrec user_record
    ON user_record.USER_ID = #user.ID

-- Condition is in the WHERE clause
SELECT *
FROM #user #user
left outer join #userrec user_record
    ON user_record.USER_ID = #user.ID
WHERE user_record.source = 'Peoplesoft'

-- Condition is in the JOIN
SELECT *
FROM #user #user
left outer join #userrec user_record
    ON user_record.USER_ID = #user.ID
   AND user_record.source = 'Peoplesoft'


innodb_flush settings on Amazon RDS Setting innodb_flush_log_at_trx_commit = 2 improves my application performance considerably on my local development environment. The response time and number of requests per second on a 40 concurrent users benchmark is improved by a factor of 8, at least.

However, on my testing environment at Amazon RDS, which is a clone of my production environment, absolutely nothing changes. My benchmark results from after and before setting it to 2 are virtually the same. The server was rebooted and the right value is there when querying for SHOW VARIABLES.

I expected improvement wouldn't be as much as with a local server, but I expected something at all, even if not worth the consequences. Any ideas on what might be wrong? Does Amazon RDS doesn't update it immediately somehow or loads default settings from somewhere else?

Trying this today I get "rds-modify-db-parameter-group:  Malformed input-The parameter innodb_log_file_size cannot be modified." so I guess they disabled that, which is pretty silly.


Table size is not changed after new column is added I am trying to check how the table size is going to be increased after adding new column with default values.

I use the following default values: NULL, ''(empty string) and some text(some random text)` but it seems the table size is not changed.

I am using the following statements:

EXEC sp_spaceused '[Table_A]';

ALTER TABLE [dbo].[Table_A]
ADD [Loops] NVARCHAR(900)
CONSTRAINT [DF_Loops] DEFAULT('XXX') WITH VALUES;

EXEC sp_spaceused '[Table_A]';

ALTER TABLE [dbo].[Table_A]
DROP CONSTRAINT [DF_Loops]

ALTER TABLE [dbo].[Table_A]
DROP COLUMN [Loops];

but the sp_spaceused always returns:

name                                               rows                 reserved           data               index_size         unused
------------------------------------------------- -------------------- ------------------ ------------------ ------------------ ------------------
Table_A                                            73540994             8701688 KB         6658544 KB         2042976 KB         168 KB

Could anyone tell what I am doing wrong?

You've added a new column, but SQL Server - if the default value is a runtime constant - will not update the existing pages that hold the table's data, it will be only a metadata change and that's the reason you see no increase in the space used.
For all intents and purposes (as Aaron mentioned in a comment), the values are there - and a SELECT will reveal them.
Think of it as SQL-Server black magic. It manages to store information in 0 bytes. Remus Rusanu has a nice blog post about this optimization and more details about what (magic) happens in the background and when you subsequently update the column:
Online non-NULL with values column add in SQL Server 2012
The basic info about it, in MSDN docs, ALTER TABLE:

Adding NOT NULL Columns as an Online Operation
Starting with SQL Server 2012 Enterprise Edition, adding a NOT NULL column with a default value is an online operation when the default value is a runtime constant. This means that the operation is completed almost instantaneously regardless of the number of rows in the table. This is because the existing rows in the table are not updated during the operation; instead, the default value is stored only in the metadata of the table and  the value is looked up as needed in queries that access these rows. This behavior is automatic; no additional syntax is required to implement the online operation beyond the ADD column syntax. A runtime constant is an expression that produces the same value at runtime for each row in the table regardless of its determinism. For example, the constant expression 'My temporary data', or the system function GETUTCDATETIME() are runtime constants. In contrast, the functions NEWID() or NEWSEQUENTIALID() are not runtime constants because a unique value is produced for each row in the table.


Table variable for large amount of data One of our stored procedure was using Cursor to update rows. Total rows to be updated varies from 1000 to 2000. In our scenario, this stored procedure was taking 50 minutes to complete the update.

I replaced the Cursor and used Table Variable. At any point of time, the Table Variable will also contain 1000 to 3000 rows. When executed the update finished in 2 minutes.

Server is Windows Server 2012 64-bit with 64GB RAM.
SQL Server 2008

I want to know whether to replace Table Variable with #TempTable? In the scenario mentioned above, Table Variable is working fine.

I checked few posts on Table Variable. One post says SQL Server decides when to keep Table Variable in memory and when to move it to TempDB.

For the above configuration mentioned, when will Table Variable be moved to TempDB?

Actually Temp table and Table variable use tempdb (Created on Tempdb). Please check the below code which I will use to create Temp Table and Variable Table. and check where they were created.

BEGIN TRAN

DECLARE @DtmStartDateTime DATETIME = GETDATE()

-- Create Temp Table and Table Variable

CREATE TABLE #TempTable
(CandidateID INT)

DECLARE @TempVairable TABLE (EmployeeID INT)

-- Check Where Temp Table and Table Vairable Created
SELECT * FROM tempdb.sys.all_objects
WHERE type = 'U' -- User Table
AND create_date >= @DtmStartDateTime

-- This Filter will be used to check the     new tables

ROLLBACK


SQL Server 2008 R2 : Unexpected timeout with SQL Agent Job/powershell I have a SQL agent job set to execute a SQL query against a single database and then save each row into a different file with powershell before zipping the files up and emailing me.

In one of my SQL instances this job runs just fine without any modification. In another instance the execution of the query times out after 5 seconds. I've looked and I can't find any timeout settings on the server or in the script that are remotely that low.

I tried modifying the connection string and sqlcommand both to have timeout values of 600 and for some reason this is still timing out after an extremely short period of time. I've tried running the query via SSMS and the results are returned in about 15 seconds.

What else can I look into to try and troubleshoot this?

Here is what I use in my PowerShell scripts that I experience issues for timeout.

Invoke-SQLcmd $SQL -QueryTimeout 65535 -ConnectionTimeout 0

Reference:
https://docs.microsoft.com/en-us/powershell/module/sqlserver/invoke-sqlcmd?view=sqlserver-ps


Setting up a central CLR stored procedure / function respository library for internal stored procs in other databases to use? I would like to use code that I developed in C# CLR to be used in all of the databases on the system so that I don't have to set each to trustworthy and turn CLR on and keep a bunch of the same code inside each one.

Is there a best way to do this from an administrative and security standpoint? The CLR functions are very basic like string breakers, email validation, url en/decode, base64, and etc. I would like only the dbo schema in each database to be able to access the functions.

Is there any simple way to do this?
Also I am not clear if the CLR dll is embedded and if I move the database, it tags along, or do I have to move the dll as well.

Thanks

At our company we have that exact setup. When you create a CLR assembly a binary representation of the assembly is stored within the database that you create it in. This enables you to take it with you (and even script it out) should you move the database at any point in time.

A couple of months back our data center got flooded - filling several servers full of water. When I rebuilt them I only used the backups of the db that had been taken the night before. So far we have had no issues.. (touch wood!)

I am not sure if this is the right thing to do from a security perspective but the way we grant access to the CLR procs etc is to create a role within the shared database, and then add users from other databases to that role. The role is then granted execute on the CLR procs.

There may be access issues if the CLR is trying to do things like access resources outside of the database that it is contained within but you can set the permission on the assembly when you create it. The below link has a lot more information regarding permissions than I can explain here though:

http://msdn.microsoft.com/en-us/library/ms345101.aspx

I hope that this helps you.


Having troubles creating table SQL I'm new to SQL and Im trying to create a simple database, but I'm having problems with second one, I'm using Oracle Database express 11 and when I try to run this code I get error: Missing right parenthesis

CREATE table "CURSOS" (
    "Nombre_curso" VARCHAR2(20) UNIQUE,
    "Cod_curso"  VARCHAR2(10),
    "Apellido2"  VARCHAR2(15),
    "NIF_Prof"   VARCHAR2(9),
    "Max_al"     INT (2),
    "Fecha_ini"  DATE,
    "Fecha_fin"  DATE,
    "Horas"      INt(6) NOT NULL,
    constraint  "CURSOS_PK" primary key ("Cod_curso"),
    constraint  "CURSOS_CK" CHECK (Fecha_ini<Fecha_fin),
    constraint  "CURSOS_FK" FOREIGN KEY (Nif_Prof) REFERENCES PROFESORES,
)

I'll hazard the guess of the extra comma at the end of PROFESORES may be causing the error to be thrown.  It's the only thing that looks out of place.


SQL Server / MySQL to DTD (+XML) Is there any way I can at least export a table schema from MSSQL or MySQL to a DTD file? Also the same for the data of the tables to XML.

I am really not getting how to write DTDs, so I badly need a generator, and seeing how I have the DB set up, I'd say that's the easiest way?

You could try using MySQL's mysqldump utility.

There is an option to make mysqldump export in XML

[redwards@lw-lts-155 ~]$ mysqldump --help | grep xml
  -X, --xml           Dump a database as well formed XML.

In your case, you could dump the table mydb.mytable like this:

mysqldump --xml mydb mytable > mytable.xml

EXAMPLE

For the given table

mysql> show create table groupby\G
*************************** 1. row ***************************
       Table: groupby
Create Table: CREATE TABLE `groupby` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `num` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=17 DEFAULT CHARSET=latin1
1 row in set (0.01 sec)

mysql> select * from groupby;
+----+------------+
| id | num        |
+----+------------+
|  1 |  360370418 |
|  2 |  429187192 |
|  3 | 1064824747 |
|  4 | 1328623077 |
|  5 |  740702064 |
|  6 | 1071610692 |
|  7 |  428008187 |
|  8 |  279178462 |
|  9 |  360370418 |
| 10 |  429187192 |
| 11 | 1064824747 |
| 12 | 1328623077 |
| 13 |  740702064 |
| 14 | 1071610692 |
| 15 |  428008187 |
| 16 |  279178462 |
+----+------------+
16 rows in set (0.00 sec)

mysql>

just mysqldump to a textfile.

Here is what the XML actually looks like

<?xml version="1.0"?>
<mysqldump xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<database name="eggyal">
        <table_structure name="groupby">
                <field Field="id" Type="int(11)" Null="NO" Key="PRI" Extra="auto_increment" Comment="" />
                <field Field="num" Type="int(11)" Null="YES" Key="" Extra="" Comment="" />
                <key Table="groupby" Non_unique="0" Key_name="PRIMARY" Seq_in_index="1" Column_name="id" Collation="A" Cardinality="16" Null="" Index_type="BTREE" Comment="" Index_comment="" />
                <options Name="groupby" Engine="MyISAM" Version="10" Row_format="Fixed" Rows="16" Avg_row_length="9" Data_length="144" Max_data_length="2533274790395903" Index_length="2048" Data_free="0" Auto_increment="17" Create_time="2012-11-26 22:39:22" Update_time="2012-11-26 22:39:22" Collation="latin1_swedish_ci" Create_options="" Comment="" />
        </table_structure>
        <table_data name="groupby">
        <row>
                <field name="id">1</field>
                <field name="num">360370418</field>
        </row>
        <row>
                <field name="id">2</field>
                <field name="num">429187192</field>
        </row>
        <row>
                <field name="id">3</field>
                <field name="num">1064824747</field>
        </row>
        <row>
                <field name="id">4</field>
                <field name="num">1328623077</field>
        </row>
        <row>
                <field name="id">5</field>
                <field name="num">740702064</field>
        </row>
        <row>
                <field name="id">6</field>
                <field name="num">1071610692</field>
        </row>
        <row>
                <field name="id">7</field>
                <field name="num">428008187</field>
        </row>
        <row>
                <field name="id">8</field>
                <field name="num">279178462</field>
        </row>
        <row>
                <field name="id">9</field>
                <field name="num">360370418</field>
        </row>
        <row>
                <field name="id">10</field>
                <field name="num">429187192</field>
        </row>
        <row>
                <field name="id">11</field>
                <field name="num">1064824747</field>
        </row>
        <row>
                <field name="id">12</field>
                <field name="num">1328623077</field>
        </row>
        <row>
                <field name="id">13</field>
                <field name="num">740702064</field>
        </row>
        <row>
                <field name="id">14</field>
                <field name="num">1071610692</field>
        </row>
        <row>
                <field name="id">15</field>
                <field name="num">428008187</field>
        </row>
        <row>
                <field name="id">16</field>
                <field name="num">279178462</field>
        </row>
        </table_data>
</database>
</mysqldump>

As for the DTD itself, you should look around StackOverflow and ServerFault

You could also XML dump just the structure using --no-data option

mysqldump --xml --no-data mydb mytable > mytable.xml

which produces

<?xml version="1.0"?>
<mysqldump xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<database name="eggyal">
        <table_structure name="groupby">
                <field Field="id" Type="int(11)" Null="NO" Key="PRI" Extra="auto_increment" Comment="" />
                <field Field="num" Type="int(11)" Null="YES" Key="" Extra="" Comment="" />
                <key Table="groupby" Non_unique="0" Key_name="PRIMARY" Seq_in_index="1" Column_name="id" Collation="A" Cardinality="16" Null="" Index_type="BTREE" Comment="" Index_comment="" />
                <options Name="groupby" Engine="MyISAM" Version="10" Row_format="Fixed" Rows="16" Avg_row_length="9" Data_length="144" Max_data_length="2533274790395903" Index_length="2048" Data_free="0" Auto_increment="17" Create_time="2012-11-26 22:39:22" Update_time="2012-11-26 22:39:22" Collation="latin1_swedish_ci" Create_options="" Comment="" />
        </table_structure>
</database>
</mysqldump>

Take that output and scult it into a DTD


Should a database 'administrator' have the ability to deep dive into runtime query performance issues? I acknowledge that everyone's experience and abilities are different. Having said that, I want to avoid setting expectations too high (or too low) for a DBA that through actions; appears to be an 'administrator'.
Given:

I am a developer that is deep diving into SQL Server trouble shooting
and performance issues. At night I'm watching Brent Ozar videos with
a bucket of popcorn.
A company with several divisions, this division having around 100ish team members
Many customers with databases having millions of rows with ETL
A small DBA team that is stretched to handle these same customers. Handle HAG issues, backup, restores, create new deployments for new or upgrading customers.

I am NOT attempting to justify my own opinion. I wish to adjust either my opinion or others in the company.
Question:
Given the above, should expectations be that a DBA is simply an 'administrator'? Is this something you've typically seen?

I went into this soap opera (see my other recent questions) with the expectation that a DBA is 'expected' to deep dive. I have come to believe that I have misunderstood and I am leaning towards a DBA - being an 'administrator'.
I welcome others' experiences and perhaps advice on refining this question.

Titles are just that, nothing more than a set of words, and the relevancy of their meaning are dependent on how you define that meaning within the context of your domain. In this case that domain is your company, so you guys should define the DBA role to fit the needs of the business (realistically under the umbrella of what a DBA can do).
DBA is one of those kinds of roles that I've found to evolve as a many hats or crossover type of position. The reasoning being is it's a career heavily populated by a mix of people who either were Software Developers, System Administrators, or neither, in a past life.
As someone who went from Software Developer to resident DBA to actual DBA at another company and back to a quasi-Software Developer / DBA now, I got to experience most of the database development and performance tuning side of being a DBA as opposed to server and database management - though I have and still do manage some of those aspects as well. This was because of my prior experience as a developer and because the companies I worked for needed someone who could write code but also help optimize performance in the database layer both from an architectural standpoint and a query tuning one.
So in summary, there is a rainbow of DBA roles out there between ones who essentially just administrate database systems and get their hands dirty under the hood of the servers themselves, others who purely do database design and performance tuning, and others who have a mix of actual software development (even outside the database layer) responsibilities and do performance tuning and database management.


Performance effect of indexing foreign keys My database is for an HR application with performance reviews and I am interested in whether I should index my foreign key.

There are two tables involved. The first is a ratings table with 2 columns: ID (Integer, Primary Key) and Description (Varchar). It will only have 4 rows (poor, average, above average and excellent) and will "never" change.

The second table is a review table. There will be 1 row for each employee every six months. So we are looking at thousands of entries and not millions. Each row has approximately 25 evaluation fields, which are foreign keys into the rating table. Examples would be typical questions where employee is rated on Completing Work on Time, Works well with others etc...

From what I've researched, there doesn't appear to be any real reason to create an index on each of those keys since I will never be adding and/or deleting from the ratings table. Are there any other considerations that I should take into account? Or am I worrying about nothing since by db is so small?

It may still be useful to have indexes on the larger table to help the optimizer for specific SELECT queries, even if you will "never" change any of the values.

However, without a lot more knowledge of your system and the types of queries you will run, it's pretty difficult for any of us to able to tell you whether you should have indexes there or not. I'm afraid it's just something you're going to have to test.

Some background in this great article by Erin Stellato:

http://www.sqlperformance.com/2012/11/t-sql-queries/benefits-indexing-foreign-keys

You also might consider avoiding the joins in some cases - when you are sure the values will never change, and eliminating the join can simplify the execution plan, you can consider just inline constants, e.g.

SELECT CASE RatingID
  WHEN 1 THEN 'poor'
  WHEN 2 THEN 'average'
  ...
END
FROM dbo.BiggerTable
...

Also I suggest never naming something ID. If it's a RatingID, call it RatingID everywhere it exists in the model.


Memcache implementation I have a Wordpress site that uses WP Super Cache plugin in order to make my blog posts and pages stay as HTML so that less PHP/MySQL code is executed. However, there's a featured box that can't get cached. So, I configured it manually to bypass cache and stay dynamic (code is executed on every page load).

So, most part of the entire page is executed using HTML and a specific part (a featured bar box) uses a PHP/MySQL.

The code that runs on every page load are some simple <?php $query = mysql_query("SELECT * [... rest of my code here ...]"); ?> to select some data from my database and some $fetch and <?php echo [...] ?> to print the results in the webpage, of course. But instead of everytime calling database to get this results, it would be much better to cache this results. So, I have three options:

Let WP Super Cache caches my entire page. But I can't do that, because inside my box that currently isn't getting cached, the database data can change anytime (normally 1 time per day). So, I can't cache this box and we have to discart this option 1.
Activate query_cache in my.cnf. However, this will only cache MySQL and don't PHP. :(
Than, we have memcache (or APC, I don't know much about it's difference). After all my explanations, here's the final question: I'd like to know if installing memcache in my server is enought to get it working imediatally or I have to make any changes in my code to adapt it with memcache; I'd like to know if it will really help me with both PHP and MySQL for this specific part of code I have to cache or it's results is similar to enabling query_cache? Or is using APC rather than memcache better in my case?

Thanks,
André.

You should decide what exactly you need, before deciding those 3 options.

My suggestion is as following:

Install memcache and the necesary php module to use with memcache. Once you install memcache, you will have an IP and PORT to connect to, for SET / GET operations
Decide where exactly in your Wordpress php code you need to inject the code that displays the "featured" stuff
Add a php code similar to:

CONNECT to memcache
Check for the existance of a KEY/VALUE pair, named "FEATURED STUFF"
If there is, GET that pair, and display the pair's value
If not, run the SELECT query, FETCH the results, build a string that will contain the HTML body of "FEATURED STUFF", PUT the string in memcache as a KEY/VALUE pair.

The code above should not be cached with WP Super Cache. Let memcache do the cacheing for you.

If you need help with the actual implementation, let me know.

Sample PHP Code:

$memcache = new Memcache;
$memcache->connect('localhost', 11211) or die ("Could not connect");

if ($gotten = $memcache->get('featured_stuff')) {
    echo 'CACHE HIT'</br>';
    echo $gotten;
} else {
    # Never mind - fetch it and store for next time!
    $gotten = dbfetch($query, ...);
    $memcache->set($featured_stuff,$gotten);
    echo $gotten;
}


Get Job step run for all jobs and job steps using TSQL I was wondering if there is an easy way to check the "Run as" value for each job step using TSQL code instead of opening every job on the server and each individual step? I cannot figure out where to get this data from and I have scoured the MSDB in search of this but cant find anything in the sysjobs and related tables? SQL has to store this value somewhere. The question is where?

This should get you close, if NULL for ProxyName then it's using the SQL Server Agent credentials.

USE msdb

SELECT J.job_id
    , J.name
    , S.step_name
    , S.step_id
    , P.name AS ProxyName
    , SP.name AS CredentialUserName
    , SP.type_desc AS CredentialUserType
FROM msdb.dbo.sysjobs J
    INNER JOIN msdb.dbo.sysjobsteps S ON S.job_id = J.job_id
    LEFT OUTER JOIN msdb.dbo.sysproxies P ON P.proxy_id = S.proxy_id
    LEFT OUTER JOIN sys.server_principals SP ON SP.sid = P.user_sid


Using Python to insert JSON into PostgreSQL I have the following table:

create table json_table (
    p_id int primary key,
    first_name varchar(20),
    last_name varchar(20),
    p_attribute json,
    quote_content text
)

Now I basically want to load a json object with the help of a python script and let the python script insert the json into the table. I have achieved inserting a JSON via psql, but its not really inserting a JSON-File, it's more of inserting a string equivalent to a JSON file and PostgreSQL just treats it as json.

What I've done with psql to achieve inserting JSON-File:

Reading the file and loading the contents into a variable

\set content type C:\PATH\data.json

Inserting the JSON using json_populate_recordset() and predefined variable 'content':

insert into json_table select * from json_populate_recordset(NULL:: json_table, :'content');

This works well but I want my python script to do the same.
In the following Code, the connection is already established:

connection = psycopg2.connect(connection_string)
cursor = connection.cursor()
cursor.execute("set search_path to public")

with open('data.json') as file:
        data = json.load(file)

query_sql = """
insert into json_table select * from
json_populate_recordset(NULL::json_table, '{}');
""".format(data)

cursor.execute(query_sql)

I get the following error:

Traceback (most recent call last):
  File "C:/PATH", line 24, in <module>
    main()
  File "C:/PATH", line 20, in main
    cursor.execute(query_sql)
psycopg2.errors.SyntaxError: syntax error at or near "p_id"
LINE 3:     json_populate_recordset(NULL::json_table, '[{'p_id': 1, ...

If I paste the JSON content in pgAdmin4 and use the string inside json_populate_recordset() it works. I assume im handling the JSON file wrong...

My data.json looks like this:

[
    {
        "p_id": 1,
        "first_name": "Jane",
        "last_name": "Doe",
        "p_attribute": {
            "age": "37",
            "hair_color": "blue",
            "profession": "example",
            "favourite_quote": "I am the classic example"
        },
        "quote_content": "'am':2 'classic':4 'example':5 'i':1 'the':3"
    },
    {
        "p_id": 2,
        "first_name": "Gordon",
        "last_name": "Ramsay",
        "p_attribute": {
            "age": "53",
            "hair_color": "blonde",
            "profession": "chef",
            "favourite_quote": "Where is the lamb sauce?!"
        },
        "quote_content": "'is':2 'lamb':4 'sauce':5 'the':3 'where':1"
    }
]

There's no need to have python parse the json; just pass through the file contents, retrieved with .read(), to cursor.execute() as a parameter (& make sure to wrap in in a tuple, as shown below). And don't forget connection.commit() or you'll never actually see the inserted data as it'll be rolled back when the connection closes after python finishes running.

import psycopg2
connection = psycopg2.connect(connection_string)
cursor = connection.cursor()
cursor.execute("set search_path to public")

with open('data.json') as file:
    # change json.load(file) to file.read()
    data = file.read()

# Just put a placeholder %s instead of using {} and .format().
query_sql = """
insert into json_table select * from
json_populate_recordset(NULL::json_table, %s);
"""

# change .execute(query_sql) to .execute(query_sql, (data,))
cursor.execute(query_sql, (data,))
# Add a commit on the connection.
connection.commit()


Import (flat file) into SQL Server I've seen a couple of questions about the subject backfiring so I'll try to be specific about my concern...

I have an old (MS Access) frontend I'm trying to rebuild and I left the part that troubles me most for last, so here I am.
The task is to 'grab' a file (used to be an XLS), now it would more that likely be by-product of it, say CSV for the sake of the example.

So this is my first steppingstone, I'm concerned about the result of my import, according to Pinal Dave BULK INSERT is a non atomic operation:

  If there is any error in any row it will be not inserted but other
  rows will be inserted.

This is not appealing to me since I tend not to trust the user input, I want more control thus being able to trigger an error and not importing any part of the file would be better. I'm not sure if this effectively wipes the possibility of using BULK INSERT for this process?

I also tried reading line by line and inserting using a LOOP cycle, this is effective but unfortunately its takes to long even after optimizing my code I just went from 15 minutes to 4, and that's only for 850 records.

I'm a bit mad that the process worked so smoothly when running from the Access frontend. That was because of the DoCmd.TransferSpreadsheet VBA method. This allowed for fast XLS import into a temp table and from that one the execution carried on updating tables.

I would like to know what other options have guys tried when importing data from flat file sources into SQL Server. Admittedly I'm a bit confused at this point.

Any help would be appreciated.

BULK INSERT into VARCHAR(MAX) in my opinion, then you shouldn't have errors and from there you can manipulate the data as needed and run standard SQL to make sure it fits within your requirements before actually placing it in the destination table. Just a suggestion but it's how I've done it in the past.


Are "Always On Failover Cluster Instances" and "SQL Server Failover Clustering" the same thing? I'm reading documentations of SQL Server. On this page at https://docs.microsoft.com/en-us/sql/sql-server/failover-clusters/windows/windows-server-failover-clustering-wsfc-with-sql-server, it says:

Both Always On Availability Groups and Always On Failover Cluster
Instances use WSFC as a platform technology, registering components as
WSFC cluster resources.

I'm a little bit confused. In previous version of SQL Server (like SQL Server 2008) there is a technology called SQL Server Failover Clustering, is it the same thing as Always On Failover Cluster Instances? Hope someone can clarify on this. Thanks in advance.

Yes it is the same thing. See section 3 which explains this term.

First lets get few terms straight.

Windows Server Failover Clustering

  A Windows Server Failover Clustering (WSFC) cluster is a group of
  independent servers that work together to increase the availability of
  applications and services. SQL Server 2016 takes advantage of WSFC
  services and capabilities to support Always On availability groups and
  SQL Server Failover Cluster Instances.

Always On Availability Groups (SQL Server)

  The Always On availability groups feature is a high-availability and
  disaster-recovery solution that provides an enterprise-level
  alternative to database mirroring. Introduced in SQL Server 2012,
  Always On availability groups maximizes the availability of a set of
  user databases for an enterprise. An availability group supports a
  failover environment for a discrete set of user databases, known as
  availability databases, that fail over together. An availability group
  supports a set of read-write primary databases and one to eight sets
  of corresponding secondary databases. Optionally, secondary databases
  can be made available for read-only access and/or some backup
  operations. An availability group fails over at the level of an
  availability replica.

  Failovers are not caused by database issues such as a database
  becoming suspect due to a loss of a data file, deletion of a database,
  or corruption of a transaction log.

Always On Failover Cluster Instances (SQL Server)

  As part of the SQL Server Always On offering, Always On Failover
  Cluster Instances leverages Windows Server Failover Clustering (WSFC)
  functionality to provide local high availability through redundancy at
  the server-instance level—a failover cluster instance (FCI). An FCI is
  a single instance of SQL Server that is installed across Windows
  Server Failover Clustering (WSFC) nodes and, possibly, across multiple
  subnets. On the network, an FCI appears to be an instance of SQL
  Server running on a single computer, but the FCI provides failover
  from one WSFC node to another if the current node becomes unavailable.

Item number 2 and 3 can be combined and item 1 is a prerequisite for implementing any combination. 2 alone, 3 alone, 2 and 3 together.

When you combine 2 and 3 you get:  Failover Clustering and Always On Availability Groups (SQL Server)

  You can set up a second layer of failover at the server-instance level
  by implementing SQL Server failover clustering together with the WSFC
  cluster. An availability replica can be hosted by either a standalone
  instance of SQL Server or an FCI instance. Only one FCI partner can
  host a replica for a given availability group. When an availability
  replica is running on an FCI, the possible owners list for the
  availability group will contain only the active FCI node. + Always On
  availability groups does not depend on any form of shared storage.
  However, if you use a SQL Server failover cluster instance (FCI) to
  host one or more availability replicas, each of those FCIs will
  require shared storage as per standard SQL Server failover cluster
  instance installation.

I would highly recommend reading this FAQ by SQLHA (Allan Hirt) which clarifies many basic questions when you combine these 3 items.


SQL Server: Additional cpus slow down batch I'm running SQL Server 2012 in Windows 2008R2 in a virtualized environment.  I've observed the following under both VMware Workstation 9 and Hyper-V 2012R1 and I don't know how to address it.

I've got a batch that takes around 5 minutes to run when there is a single CPU in the virtual machine.  Bumping up anywhere from 2-8 causes it to take over 10 minutes to run.  Watching the Task Manager I see that there is not much if any parallel execution and lots of context switching.  If I limit sqlservr.exe to a single CPU by setting the processor affinity in Task Manager the time drops back down to 5 minutes.

The particular batch that I'm running is makes heavy use of cursors and dynamic sql which cannot be eliminated.

The query has been profiled and optimized.  Statistics are all up to date and indexes are rebuilt.

Is there anything I can do to SQL Server to get better behavior?  This seems not right.  I would like to add additional CPU resources to the VM so that they can be used if necessary without a drastic performance hit for serialized processing.

CPU is i7-4770K with VT-x enabled both with and without hyperthreading enabled.

EDIT: Some more stats-
Running from the root partition in Hyper-V with 1 CPU takes 4:41 and with 8 CPUS takes 7:30.
Running without virtualization 1 CPU takes 4:11 and with 8 CPUS takes 5:23.

EDIT2: On the recommendation of Jon Siegel I have gotten Query Plan traces for both cases.  The query plans are long (500k lines) due to the loops and curors.  There are no obvious differences between the two and I don't see any signs of parallelism in use.

Additionally, setting MAXDOP=1 at the server level has the same effect as the affinity setting in that it limits to 1 CPU and makes the batch go fast.

Jon also made me aware of the Resource Governor feature of SQL 2012 EE which should allow the ability to limit CPUs on a per connection basis as a workaround.  The relevant setting is the AFFINITY SCHEDULER option for the resource pool.

So, at this point to sum it up mutlicore:loop*cursor*exec() = MS-SQL-no-likey (and virtualization is not helping the matter).

I can only guess about the query because I can't see it or the data. Perhaps it simply doesn't lend itself to parallel execution. In that case, you can use a query hint OPTION ( MAXDOP 1 ) (after the ORDER BY clause) to force the query to execute on only one core.

http://technet.microsoft.com/en-us/library/ms181714.aspx


In Teradata SQL how to get random number between a pair, coming from field/column? I am recently got stuck upon a simple problem in Teradata: how to get random number between a pair of number coming from field/column?
The function RANDOM( lower_bound , upper_bound ) apparently is restricted to only accept fix number rather than field / column name.
--THIS ONE FAILS:
WITH TRIAL(CATEGORY, VAL_MIN, VAL_MAX) AS (
SELECT 'A', 0, 3 FROM CTE UNION ALL
SELECT 'B', 1, 9 FROM CTE UNION ALL
SELECT 'C', 2, 5 FROM CTE UNION ALL
SELECT 'D', 1, 12 FROM CTE
), CTE(DUMMY) AS (SELECT 'X')
SELECT T.*,
RANDOM(T.VAL_MIN, T.VAL_MIN) --HERE'S THE PROBLEM
FROM TRIAL T;

--THIS ONE WORKS:
WITH TRIAL(CATEGORY, VAL_MIN, VAL_MAX) AS (
SELECT 'A', 0, 3 FROM CTE UNION ALL
SELECT 'B', 1, 9 FROM CTE UNION ALL
SELECT 'C', 2, 5 FROM CTE UNION ALL
SELECT 'D', 1, 12 FROM CTE
), CTE(DUMMY) AS (SELECT 'X')
SELECT T.*,
RANDOM(1, 7) --HERE'S THE PROBLEM
FROM TRIAL T;

I'm pretty sure there's simple solution for this. Greatly appreciate any help.

This will return a number within the requested range:
RANDOM(0, 2147483647) mod (T.VAL_MAX - T.VAL_MIN +1 ) + T.VAL_MIN

But there might be a minimal bias towards the lower numbers due to MOD, e.g. for 1-3 you get following probablities:
1,2 = 715,827,883 / 2,147,483,648
  3 = 715,827,882 / 2,147,483,648


What exactly is a "thread" in thread_cache_size, Thread_connected and thread_created? In the MySQL 5.7 manual, I read that the thread cache has a size determined by the thread_cache_size system variable.

The default value is 0 (no caching), which causes a thread to be set up for each new connection and disposed of when the connection terminates.

But what exactly is a "thread"?

A connection thread becomes inactive when the client connection with which it was associated terminates.

Again, I can't understand what is a "thread" that I'm reading about.

Threads_created: The number of threads created to handle connections.

What the word "thread" refers to in this case?

Threads_cached: The number of threads in the thread cache.

Again this word "thread".

Threads_connected: The number of currently open connections.

I can't understand what is a thread. It is not connection I guess...

But what is it? Maybe it's only a matter of translation in my language, but I can't make up my mind to understand what is a "thread"

On *nix, a MySQL thread is a relatively heavy-weight "process".  You can see the "processes" in top or ps.

For Windows, thread_cache_size = 0 is OK.  This is because threading is done differently.

On *nix, thread_cache_size should be a modest number, say, 20.  It is the maximum number of unused processes to keep open.  Suppose you have a burst of activity and briefly peak at 30 threads running (Threads_connected), but then 7 disconnect.  That leaves 23 connected, plus the cache of 7 "processes" not yet terminated (Threads_cached).  Let's say another 18 disconnect.  You are down to 5 running and 20 (the cache size) held in reserve.

A "thread_pool" is something different.  Furthermore, Oracle and MariaDB implemented different things here.

The STATUS values of Threads_cached/connected/created/running give a peak into whether the VARIABLE thread_cache_size needs tuning.

Threads_created/Uptime is how fast a new process is being created because the cache is too small.  Recommend keeping this under 3/second.


Unable to use a temporary table more than once I am using SQL Server 2008 to build a stored procedure. In it, I create a temporary table (#results) using recursion for getting all the ancestors given a specific record.

My specific problem is, when I try to use this #results table twice, the second time it returns this message:

  Invalid object name '#results'.

Here is my stored procedure:

SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[usp_GetSurveyByWorkflow]
 @WorkflowId INT  = NULL
AS
BEGIN
 DECLARE @SurveyService int = NULL;
 DECLARE @Theme int = NULL;
 DECLARE @TopAncestor int = NULL;
 DECLARE @Survey int = NULL;

 SET NOCOUNT ON
 SELECT @Theme = THEMEID FROM [dbCSSA].[dbo].[TYPESOFSERVICES] WHERE TYPEOFSERVICEID = @WorkflowId;
;WITH #results AS
(
    SELECT  THEMEID,
            THEMEIDPARENT
    FROM    [dbCSSA].[dbo].[THEMES]
    WHERE   THEMEID = (@Theme)
    UNION ALL
    SELECT  t.THEMEID, t.THEMEIDPARENT FROM [dbCSSA].[dbo].[THEMES] t
            INNER JOIN #results r ON r.THEMEIDPARENT = t.THEMEID
)
SELECT @SurveyService = IDSERVICESURVEYS FROM [dbCSSA].[dbo].[SURVEYSERVICES] WHERE IDTHEMESERVICE =
    (SELECT THEMEID FROM #results WHERE THEMEIDPARENT IS NULL);
--It fails here:
SELECT @TopAncestor = THEMEID FROM #results WHERE THEMEIDPARENT IS NULL;
SELECT @Survey = SURVEYID FROM [dbCSSA].[dbo].[SURVEYSERVICES] WHERE IDTHEMESERVICE = @TopAncestor;
SELECT * FROM FORMSURVEYS
    INNER JOIN FIELDSURVEYS ON FORMSURVEYS.IDFORM = FIELDSURVEYS.FORMID
    FULL JOIN OPTIONVALUESFIELDSURVEYS ON FIELDSURVEYS.IDFIELD = OPTIONVALUESFIELDSURVEYS.IDFIELD
    WHERE FORMSURVEYS.SURVEYID = @Survey
END

What am I doing wrong?

Community wiki answer generated from comments to the question by various people.

Despite the name, you are declaring #results as a Common Table Expression (CTE), not a temporary table. CTEs only apply to the same statement they are defined in.

You can solve your problem using a temporary table, using syntax like:

CREATE TABLE #mytable (<columns>);
INSERT INTO #mytable...

or

SELECT *
INTO #results
FROM ...

Additional comments by the author:

As Pointed out by Shanky, I am using a CTE instead of a temp table, which as Queue Man said, loses scope when I assign it to a variable.

So I ended up creating a temp table and inserting the contents of my CTE #results as suggested by JNK.


What Name from Event Library? I'm new in SQL Server's extended events and you can create your own session and select your own name in Event library.
From your experience, what name from event library have you selected in order to faciliate your work or similar?
Thank you!

Brent Ozar has a good list of use cases for extended events and how to implement them.
More specifically Erik Darling also released a procedure sp_HumanEvents: Humanizing Extended Events that simplifies monitoring extended events for the usual use cases.


INET6_ATON returns NULL for input parameter I'm making a MySQL Stored Procedure which takes a string representation of an IPv4 or v6 address and converts it to binary using INET6_ATON. I know that my version of MySQL supports this function because I can run SELECT HEX(INET6_ATON("::ffff:127.0.0.1")); and it returns the correct representation.

However, when I try to put this in to a Stored Procedure and use an input parameter, it always returns NULL no matter what I put in to it.

Here's a simplified version I made using a Stored Function instead:

DROP FUNCTION IF EXISTS `addrToBinary`;
DELIMITER ;;
CREATE FUNCTION `addrToBinary` (`in_remote_ip` varchar(45)) RETURNS varchar(45)
DETERMINISTIC
BEGIN
    RETURN HEX(INET6_ATON(in_remote_ip));
END;;
DELIMITER ;

SELECT
    addrToBinary("::ffff:127.0.0.1"), -- NULL
    HEX(INET6_ATON("::ffff:127.0.0.1"));    -- 00000000000000000000FFFF7F000001

Any ideas on how I can get this function to work the same inside a Stored Procedure/Function as it does typing it in manually?

UPDATE: I've just found out that I'm in fact running MariaDB (5.5.5-10.0.17-MariaDB). Here's a screenshot of the issues when I run it using Adminer:

C:\>mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.6.22 MySQL Community Server (GPL)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test
Database changed
mysql> DROP FUNCTION IF EXISTS `addrToBinary`;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> DELIMITER ;;
mysql> CREATE FUNCTION `addrToBinary` (`in_remote_ip` varchar(45)) RETURNS varchar(45)
    -> DETERMINISTIC
    -> BEGIN
    ->     RETURN HEX(INET6_ATON(in_remote_ip));
    -> END;;
Query OK, 0 rows affected (0.01 sec)

mysql> DELIMITER ;
mysql> select `addrToBinary`("::ffff:127.0.0.1");
+------------------------------------+
| `addrToBinary`("::ffff:127.0.0.1") |
+------------------------------------+
| 00000000000000000000FFFF7F000001   |
+------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT
    ->     addrToBinary("::ffff:127.0.0.1"),
    ->     HEX(INET6_ATON("::ffff:127.0.0.1"));
+----------------------------------+-------------------------------------+
| addrToBinary("::ffff:127.0.0.1") | HEX(INET6_ATON("::ffff:127.0.0.1")) |
+----------------------------------+-------------------------------------+
| 00000000000000000000FFFF7F000001 | 00000000000000000000FFFF7F000001    |
+----------------------------------+-------------------------------------+
1 row in set (0.00 sec)

mysql>

Works Fine For Me By Hand in MySQL 5.6.22 for Windows

mysql> select `addrToBinary`(@given_ip);
+----------------------------------+
| `addrToBinary`(@given_ip)        |
+----------------------------------+
| 00000000000000000000FFFF7F000001 |
+----------------------------------+
1 row in set (0.00 sec)

mysql>

It works when passing in a parameter from the MySQL Console

If you are doing this from PHP, please make sure the IP is properly enclosed in quotes


Storing floating point values for various units of measurement (kg, ml, mm, pl) I have a table like the following:

order_item
------------
serial      | id (PK)
integer     | order_id
????        | quantity
integer     | unit_of_measure_id (PK) --> ml, l, kg, mg, mm, m, etc.

I'm doing an application where I need to store the amount of material used in chemical analysis. Sometimes materials are used in such a small amount as microliters, or sometimes more, like one or two liters.

Is it the numeric data type good enough?

I have read that it is a good idea to convert the values to the smallest unit of measure. So if I have 10 liters, I have to pass to 10000000 microliters. But what if you need to use a unit of measurement even lower in the future? like picoliters? Inevitably, the application will have to use floating point numbers I think.

If you need precise storage for numbers with yet unknown precision, the data type numeric is the way to go. Like @dezso already commented, it stores what you pass exactly without rounding, up to an insane amount of digits. Per documentation:

  up to 131072 digits before the decimal point; up to 16383 digits after the decimal point

Be aware, however, that even numeric cannot store the result of calculations like 1::numeric/3 exactly, since that is impossible on principal. Postgres seems to truncate the result at a scale of 20 unless instructed otherwise, while JDBC only displays a scale of 12:
SQL Fiddle.

You pay a price for extreme exactness. Per documentation:

  However, arithmetic on numeric values is very slow compared to the
  integer types, or to the floating-point types described in the next section.

Won't matter much as long as you don't do heavy computations with your numbers.

As for units, I would not use multiple competing units for the same unit of measurement. Go with just one. So just m for meter, not mm or km etc.

I would stick to SI units if that's at all practical for your use case.

You already mentioned the alternative for better performance: combine integer (or bigint?) numbers with the smallest units of measure.


How do I uniquely update values of a text column before applying UNIQUE constraint? I need to apply a unique constraint to a TEXT field which may or may not already contain duplicate values. If for instance I have ABC twice, I don't mind renaming them as ABC-1, ABC-2, but what if I already had ABC-1 as an entry? Seems like a bit of an recursive problem. I probably need to do this through a cursor, selecting all duplicate rows and updating them one by one.

Does anyone have a snippet to share? I'm using PostgreSQL 9.4 and plpgsql.

Ended up writing a nested loop which will select all duplicate entries in table dup_table and add a dash to each duplicate dup_name field until no duplicate records exist.

DO $$DECLARE
  tmpRec RECORD; tmpInt INTEGER;
BEGIN
    LOOP
      tmpInt := 0;

      FOR tmpRec IN
        SELECT x_id FROM (
          SELECT x_id, ROW_NUMBER()
          OVER (PARTITION BY dup_name) AS "dup"
          FROM dup_table
        ) x WHERE "dup" > 1
      LOOP
        tmpInt := 1;

        UPDATE dup_table SET dup_name = dup_name || '-' WHERE x_id = tmpRec.x_id;

      END LOOP;

      EXIT WHEN tmpInt = 0;
    END LOOP;
END$$;


Why is the MSDB database TRUSTWORTHY? The TRUSTWORTHY setting can be rather dangerous if you aren't careful and except for specific circumstances the recommendation is to keep it turned off.  However by default the MSDB database has TRUSTWORHTY set ON by default.  I'm curious why?

I've read this entry in BOL

  Note By default, the TRUSTWORTHY setting is set to ON for the MSDB database. Altering this setting from its default value can result in unexpected behavior by SQL Server components that use the MSDB database.

But I'm curious about specifics.  Why specifically does MSDB need TRUSTWORTHY turned on? What functions use it?

There are dozens of objects in msdb that reference the master database.

If msdb were not marked as TRUSTWORTHY, then users would need permission to both the msdb object they are interacting with, plus the master object that is being referenced.

For example, msdb users who get permissions via the SQLAgentUserRole database role are granted execute on msdb.dbo.sp_enum_sqlagent_subsystems. The call stack for that procedure eventually hits master:

* msdb.dbo.sp_enum_sqlagent_subsystems
    * msdb.dbo.sp_enum_sqlagent_subsystems_internal
        * master.dbo.xp_instance_regread

If msdb is not marked as TRUSTWORTHY, then users who are part of the SQLAgentUserRole database role would ALSO need execute permission on master.dbo.xp_instance_regread.

Technically speaking, it is probably possible to remove the TRUSTWORTHY setting in msdb and instead grant specific permissions in master. However, those required permissions are neither documented nor supported.


Direct, simple SQL query results in "ORA-01406: fetched column value was truncated" All the information I've seen on this issue is always in regards to running a query via some other utility (e.g. TOAD); however, I am seeing this issue when I simply try to select * from v$asm_disk; on the SQLPLUS command line. I am running the query as SYSDBA  and it fails. If I log in to the ASM instance as SYSASM, I am able to run select * from v$asm_disk;. I have two boxes with identical OS and kernel versions, that were set up in the same manner.
Ultimately the issue comes from not being able to query the underlying fixed view which is used to create this view, X$KFDSK_STAT. On one server, as SYSDBA I can query that view, and on the other, I cannot.
Has anyone seen this before?
Thanks

From what I’ve found, there is a special character in the label on the instance that doesn't work and throwing the error.
Instance causing issues
SQL> SELECT ASMNAME_KFDSK, FAILNAME_KFDSK, LABEL_KFDSK FROM x$kfdsk;

ASMNAME_KFDSK                  FAILNAME_KFDSK                 LABEL_KFDSK
------------------------------ ------------------------------ -------------------------------
DG01_DATA_0000                 DG01_DATA_0000                 ▒▒▒▒▒u▒▒▒▒▒
DG01_DATA_0001                 DG01_DATA_0001

Instance working fine (From the +ASM instance)
SQL> SELECT ASMNAME_KFDSK, FAILNAME_KFDSK, LABEL_KFDSK FROM x$kfdsk;

ASMNAME_KFDSK                  FAILNAME_KFDSK                 LABEL_KFDSK
------------------------------ ------------------------------ -------------------------------
DG01_DATA_0000                 DG01_DATA_0000

ASM Provisioning String Has Junk Characters "kfdhdb.driver.provstr:ORCLDISKÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ " ( Doc ID 2088023.1 )


Best Way to Migrate 100 tables To New Database I need to migrate 100 out of 150 tables (data and structure) and everything else (sprocs, views etc.) to a new empty SQL Server database.

I have been using the Import/Export wizard and it has been causing me headaches.  First, I scripted out and created all the tables (because the import wizard will not create constraints) then I imported the data.  Some of the data did not match up so I used Toad data sync to sync it from the source.  I have pretty much got everything fixed but there has to be a better way to do this and here are my thought.  I would love to get some feedback on the pros and cons of each method:

  1.Restore Backup of database to target database and delete the tables I do not need

This would be the fastest method but I wonder will SQL Server utilize the space from the deleted tables? Or is there a way to make it?

  2.Restore Backup to Target database and create a new file group.  Then, move all the table clustered indexes over to the new file group and delete the old one.

Are there any issues with doing this?  If I just moved the sys and users tables to the new file would that be enough?  Anything else need to be copied over?

I would recommend backup and restore method.

If you are on sql server 2008R2 and up, then standard edition will allow you to compress the backup, so that you can transfer it to the new server relatively faster than using an uncompressed backup.

Once you restore the backup to the new server, just drop the tables / SPs / Views / Triggers, etc that you don't need.

After you drop the tables, just do a one time shrink (with Some Caveats) of your database to release space back to the OS. Note for when shrinking use DBCC SHRINKFILE (not DBCC SHRINKDATABASE)

Note: It would also be a good idea to "truncate" the tables (as @shanky pointed out) - if the tables are not having any FK relationship to other tables.


How to define custom STATIC Global Variables/Constants in MySQL As a C# developer who did database work more than 7 years ago (with MSSQL) I am now developing a MySQL Database solution where Inline SQL is prohibited in code and the developers must go through Stored Procedures (Routines).

The core challenge is defining CONST/STATIC values on the database side in one place that all of my stored procedures can access. For example:

# == RECORD STATE
SET GLOBAL RECORD_STATE_NORMAL = 0;
SET GLOBAL RECORD_STATE_ARCHIVED = 1;
SET GLOBAL RECORD_STATE_DELETED = 2;

These values will never change and the database stored procedures need to use them when writing certain record details. I also need to share these values with the developers as they may need to pass core static defined values to some of their stored procedure calls.

The core problem is MySQL apparently refuses to let me create and set my own global variables outside of the configuration file (which I don't want to do since we're deployed on AWS RDS). Any time I try to set a global variable I get an error 1193 "unknown system variable".

I need a way to define a long list of Global static values on the database side (simple numbers) that all of my stored procedures can reference.

How can I do this?

Since MySQL 5.5 it's not possible to set a global user-defined variable.

A work-around might be to create a stored procedure that would return what you need.

DROP PROCEDURE IF EXISTS HOUSE_SMALL_TYPE;
DELIMITER //
CREATE PROCEDURE HOUSE_SMALL_TYPE ()
BEGIN
SELECT 0;
END//
DELIMITER ;

and then call it.

CALL HOUSE_SMALL_TYPE();
The DROP statement is required in order to be able to modify it.

Credit to shock_one on SO for the above answer

I was also thinking another option may be setting your constant values in a seperate table that all of your stored procedures could access.


Why do database servers have different time_source values I have two SQL Server 2008 R2 Enterprise SP1 servers that have different values for sys.dm_os_sys_info.time_source.

The time_source column of sys.dm_os_sys_info indicates the API that SQL Server is using to retrieve wall clock time:

0 = QUERY_PERFORMANCE_COUNTER
1 = MULTIMEDIA_TIMER

What causes the difference and can I change SQL Server to use the more precise QUERY_PERFORMANCE_COUNTER value?

+--------------------------+---------------------------+-------------------------+
|                          | Original                  | New                     |
+--------------------------+---------------------------+-------------------------+
| time_source              | QUERY_PERFORMANCE_COUNTER | MULTIMEDIA_TIMER        |
| os install               | clean                     | clean                   |
| virtual/physical         | physical                  | virtual                 |
| hypervisor               | n/a                       | vmware, ESXi 5.5        |
| VMware hardware Version  | n/a                       | 7                       |
| bcd nx                   | optin                     | optout                  |
| bcd recovery enabled     | no                        | yes                     |
| bcd /usepmtimer          | no                        | no                      |
| sql sku                  | sql standard 2008 r2      | sql standard 2008 r2    |
| sql upgrade from express | no                        | no                      |
| sql auto-close any db    | no                        | no                      |
| power plan               | high performance          | balanced                |
| dm_os_performance_counter| yes                       | yes                     |

+--------------------------+---------------------------+-------------------------+

My coworker sent this very interesting link: How It Works: Timer Outputs in SQL Server 2008 R2

VMWare uses a different API for time source depending on the OS version and the VM Hardware version. They also have a whitepaper with lots of details.

According to MSDN, SQL Server uses the following logic to decide which timer to use:

Windows does not have an API to indicate what source QueryPerformanceCounter is using at this time so SQL Server 2008 R2 startup times the invocation of QueryPerformanceCounter (10 times) and when it exhibits repeated, small cycle behavior (< 600 cycles) the QueryPerformanceCounter will be used to accept invariant TSC timings instead of the GetTickCount/timeBeginPeriod interrupt timing behavior.   If the timing exceeds 600 cycles the multi-media timer behavior of SQL Server is used instead.
Note:  Windows is exposing the timer source API for QueryPerformanceCounter in a Windows 2008 R2 based fix so the timing source determination by SQL Server will be replaced with the API call in a future build.  Target releases are the SP1 of Windows 2008 R2 and SQL Server 2008 R2.

The above explanation indirectly answers your questions:

SQL Server uses an internal process to determine which timer to use.
You cannot tell SQL Server 2008 R2 which timer to use, other than by using trace flags 8049 and 8038.  In my testing, neither of those trace flags made any difference to the timer_source column of sys.dm_os_sys_info.

We rebooted the majority of our servers over the weekend.  I compared the list of machines using MULTIMEDIA_TIMER from before and after the reboot.  I can now report that some of the servers that were using the MULTIMEDIA_TIMER are now using QUERY_PERFORMANCE_COUNTER and vice-versa.
Since our servers are virtualized, I presume simultaneously booting machines in our virtualized environment is resulting in some machines taking longer than 600 CPU cycles to return a value through the QueryPerformanceCounter API.  This effect appears to be randomly distributed across our environment.


MYSQL Subquery in columns of select statement How are subqueries in the column field of select (projection) paired with the result of the main query? in the form:

 SELECT id,email,(SELECT name From Names WHERE Names.id=Users.id) as name
 FROM Users

Is the subquery executed once per row, from the output of SELECT id,email FROM Users,and thus, one should use LIMIT 1 on the subquery (since only 1 row from the subquery can be paired with a row from the main query), or does the subquery run once, and then each result is paired with the corresponding row from SELECT id,email FROM Users, much like the equivalent join: SELECT id,email,name FROM Users JOIN Names ON Users.id=Names.id

in ideal situation, when Names.id = Users.id return only 1 record, both queries the same.

The difference when it not true.

SELECT id,email,(SELECT name From Names WHERE Names.id=Users.id) as name
 FROM Users

will stop work and return error, so You will need add LIMIT class

SELECT id,email,(SELECT name From Names WHERE Names.id=Users.id ORDER BY something LIMIT 1) as name
 FROM Users

at the same time query

SELECT id,email,name FROM Users JOIN Names ON Users.id=Names.id

continue work without errors, this query return all rows from Names related to Users

In some other cases when You expect only 1 Name, You will need add GROUP BY conditions

SELECT id,email,name FROM Users JOIN Names ON Users.id=Names.id GROUP BY Users.id

But this condition could return unpredicted name from Names (and it not 100% legal construction for SQL), and You again would need add 1 more level of JOIN with derived tables, and some time it could be ugly construction,

so, You always can compare what form of query more correct for selected case, simple example:

SELECT t1.id,t1.email,t2.name FROM Users t1 JOIN
(SELECT id, name FROM Names n1 INNER JOIN
(SELECT MAX(dateregistered) as dateregistered, id FROM Names GROUP BY id) n2 ON n1.id=n2.id AND n1.dateregistered=n2.dateregistered) t2
ON t1.id = t2.id

will return same result as:

SELECT id,email,(SELECT name From Names WHERE Names.id=Users.id ORDER BY dateregistered DESC LIMIT 1) as name
FROM Users

Add: Example with Names look like not realistic, but real situation - when You need request not name which is really 1 per person, but actual postal address for client with 10 years history. He can have 20 addresses, and You need return most resent


Can I fix this insertion anomaly without using a composite key? Suppose I have a schema with the following basic elements:

Client : id (PK)

Pet : id (PK)
      owner_id (FK to Client)

Contract : id (PK)
           client_id (FK to Client)

PetInContract : pet_id (FK to Pet)
                contract_id (FK to Contract)
                PK(pet_id, contract_id)

So a client may have multiple pets and multiple contracts, and each contract may involve any number of the client's pets.

This schema has the problem that it's possible to insert pets into a contract that are not in fact the pets of the client associated with the contract.  I believe adding client_id to the primary key in the Contract table fixes this problem:

Contract : client_id (FK to Client)
           id
           PK(client_id, id)

PetInContract : pet_id (FK to Pet)
                client_id
                contract_id
                PK(pet_id, client_id, contract_id)
                FK(client_id, contract_id) references Contract

My question: is the problem fixable in a way that doesn't involve using a composite key?  (ie, what if each table in the database is required to have an identity column?)  Are there advantages/disadvantages to going one route vs the other?

You can use a CHECK CONSTRAINT and scalar-function to validate if the pet and contract combo is correct based on the pet owner and contract client being the same ID.

Check out this db<>fiddle for an example of this. Also, see here for more information on CHECK CONSTRAINT.

The other alternative is to create your PK on ID columns and create a UNIQUE constraint on your composite keys, effectively making them surrogate keys.

I would probably opt for a composite key or unique constraint myself, check constraints really only enforce the business/app logic on INSERT/UPDATE to prevent the mismatch scenario, whereas keys can provide other benefits.

This is simply a solution that wasn't a composite key as per your question.


How do I separate the values of one column into multiple columns In MS SQL-Server ID     Contact No  Ext   Type
 --     ----------  ----  ----
0001    75865558    123   work
0001    207586558   NULL  home
0001    207586559   NULL  cell
0001    746655558   321   work
0002    946655558   323   work
0002    2356841     NULL  home
0003    6655558     NULL  cell

I want to split the table up into

ID      HPhone     CPhone1     CPhone2   WPhone1   Ext1   WPhone2    Ext2
 --     ----------  ----------  --------  --------- ----   ---------  -----
0001     207586558  207586559    NULL     75865558  123    746655558  321
0002     2356841     NULL        NULL     946655558 323      NULL     NULL
0003      NULL      6655558      NULL      NULL     NULL     NULL     NULL

Any id can at most have 1 HPhone, 2CPhones, and 2 WPhones/Ext.

Im new to databases so Im not specifically looking to be given the answer but any advise about how I can split the first table into something similar to the second table would be appreciate.

This sounds like a PIVOT, but those quickly become messy when you're pivoting multiple things conditionally. So I would use MAX/CASE aggregates, like this (the CTE is used to give a deterministic order for numbers of the same type; if you want to change that, just change the ORDER BY inside the OVER() clause):

;WITH x AS
(
  SELECT *, rn = ROW_NUMBER() OVER
            (PARTITION BY ID, [Type] ORDER BY [Contact No])
  FROM dbo.SourceTable
)
SELECT ID,
  HPhone  = MAX(CASE WHEN [Type] = 'home' THEN [Contact No] END),
  CPhone1 = MAX(CASE WHEN [Type] = 'cell' AND rn = 1 THEN [Contact No] END),
  CPhone2 = MAX(CASE WHEN [Type] = 'cell' AND rn = 2 THEN [Contact No] END),
  WPhone1 = MAX(CASE WHEN [Type] = 'work' AND rn = 1 THEN [Contact No] END),
  Ext1    = MAX(CASE WHEN [Type] = 'work' AND rn = 1 THEN [Ext] END),
  WPhone2 = MAX(CASE WHEN [Type] = 'work' AND rn = 2 THEN [Contact No] END),
  Ext2    = MAX(CASE WHEN [Type] = 'work' AND rn = 2 THEN [Ext] END)
FROM x
GROUP BY ID
ORDER BY ID;


How do I get multiple rows output in one line? The data is like Jane has items pen pencil eraser.

If I write the statement 'select * from item'
the output is:

Jane pen
Jane pencil
Jane eraser
Jack box
Jack wiper

I want the output to be like:

Jane pen pencil eraser
Jack box wiper

How to do this?

You can try as below:

SELECT name,
LISTAGG(item_name, ' ') WITHIN GROUP (ORDER BY item_name) AS item_name
FROM item
GROUP BY name;


Excel Spreadsheet Import to MySQL I want to add about 1000 records to a table that contains 31 fields.

The worst part is those 1000 records are in the form of an excel sheet having some columns left unfilled. Is there any easy way to import this excel sheet into my table having the unfilled entries as NULL ?

Using Linux and MySQL

Since MySQL 5.1, there has been a native tool called MySQL for Excel.
It is bundled along with MySQL for Visual Studio, a bunch of connectors, sample databases and so forth.
The MySQL Documentation has a webpage for

MSI Installation
Editing
Importing
Appending
Exporting

There is also a forum for users of this product (As of this posting, there are 5 active questions that have activity this month)
MANUAL ALTERNATIVE
If you have the patience, you could do the following
STEP 01) Create a table using the CSV Storage Engine
When you create the table like this:
CREATE TABLE mydb.mytable
(
    i INT NOT NULL,
    c CHAR(10) NOT NULL,
    ...
    column29 INT NOT NULL,
    column30 CHAR(10) NOT NULL,
    column31 CHAR(30) NOT NULL
) ENGINE=CSV;

This will create two files under the mydb subfolder

mytable.frm
mytable.CSV

CAVEAT : I do not think NULL columns are allowed for MySQL CSV tables
STEP 02)

Using Excel, export the spreadsheet with the same number of columns as the MySQL CSV table. Empty Cells should come out as "".

STEP 03) If using MySQL in Linux, you could do this
cd /var/lib/mysql/mydb
mv mytable.CSV mytable_old.CSV

STEP 04) Copy the Excel output into /var/lib/mysql/mydb/mytable.CSV
STEP 05) In the mysql client, run
FLUSH TABLES;
SELECT * FROM mydb.mytable;

This will close and reopen all tables. If you see your data, CONGRATULATIONS !!!
STEP 06) If you get an Error
If you get an error on SELECT * FROM mydb.mytable;, then one of the cells is not field enclosed with double quotes. Just re-export the data and try Step 04 again until you get it right
For Those using MySQL for Windows
If you have MySQL for Windows, you can drop in mydb\mytable.CSV becauswe of the way Windows locks files. You will have to run net stop mysql, copy the file in, and net start mysql, skip the FLUSH TABLES; and just run SELECT * FROM mydb.mytable;
Give it a Try !!!


List all databases of a certain user ( SQL Server 2008 R2 ) I'm really looking for this answer for some time. I bet there is a post here but, I really can't find ( even on google ).

I think it's a simple task.

I want to list all databases for a specific user.

Is there a way to do this?

It can be a view or a cursor. I just can't think of anything.

You can impersonate that login and use the HAS_DBACCESS built-in function.

USE master;
GO
EXECUTE AS LOGIN = N'your_login_name';
GO
SELECT name, HAS_DBACCESS(name)
  FROM sys.databases;
GO
REVERT;

If you need to find the user mapping in each database (e.g. the user name in the database is not always the same as the login name), you can use dynamic SQL, e.g.

CREATE TABLE #db(name SYSNAME);
GO
use master;
GO
EXECUTE AS LOGIN = 'floob';
GO
INSERT #db(name)
  SELECT name FROM sys.databases
  WHERE HAS_DBACCESS(name) = 1
  AND database_id > 4; -- don't really care about system DBs, right?
GO
REVERT;
GO

DECLARE @sql NVARCHAR(MAX) = N'', @login SYSNAME = N'floob';

SELECT @sql += N'
  UNION ALL
  SELECT login = @login, db = ''' + QUOTENAME(name) + N''',
    username = p.name
    FROM ' + QUOTENAME(name) + N'.sys.database_principals AS p
    WHERE sid = SUSER_SID(@login)'
  FROM #db;

SET @sql = STUFF(@sql, 1, 11, N'');
EXEC sys.sp_executesql @sql, N'@login SYSNAME', @login;
GO
DROP TABLE #db;
GO