best way for creating Index in mysql (multi-column/table)? i have a big problem with my database..

I need a very fast query over 2 tables.
1. table: (node)   ~42.500.000 entrys
2. table: (waynode ~51.200.000 entrys

The following query is much to slow:

SELECT  `node`.ID,`node`.LATITUDE,`node`.LONGITUDE,`waynode`.WAYID
    FROM  `node`, `waynode`
    WHERE  `node`.ID= `waynode`.NODEID
      AND  `node`.LATITUDE BETWEEN '49.558' AND '49.568'
      AND  `node`.LONGITUDE BETWEEN '11.3395' AND '11.3495'
    ORDER BY  `waynode`.WAYID

The current time is over 180 seconds..

I also have a INDEX over 2 columns created with ALTER TABLE node ADD INDEX (LATITUDE, LONGITUDE).  When i am using the following select, it's very fast:

SELECT  `node`.ID,`node`.LATITUDE,`node`.LONGITUDE
    FROM  `node`
    WHERE  `node`.LATITUDE BETWEEN '49.558' AND '49.568'
      AND  `node`.LONGITUDE BETWEEN '11.3395' AND '11.3495'

But I need the waynode.NODEID like in the first SQL-Statement.
May someone help me please?

best regards
Ingo

See if this runs faster:

SELECT  n.ID, n.LATITUDE, n.LONGITUDE,
        wn.WAYID
    FROM
      ( SELECT  ID, LATITUDE, LONGITUDE
            FROM  `node`
            WHERE  LATITUDE  BETWEEN '49.558'  AND '49.568'
              AND  LONGITUDE BETWEEN '11.3395' AND '11.3495'
      ) AS n
    JOIN  `waynode` AS wn  ON n.ID= wn.NODEID
    ORDER BY  wn.WAYID

This might also work 'fast':

SELECT  n.ID, n.LATITUDE, n.LONGITUDE,
      ( SELECT  WAYID
            FROM  waynode
            WHERE  n.ID= NODEID
      ) AS WayID
    FROM  `node` AS n
    WHERE  LATITUDE  BETWEEN  '49.558' AND '49.568'
      AND  LONGITUDE BETWEEN '11.3395' AND '11.3495'
    ORDER BY  WayID

For further discussion, please provide EXPLAIN SELECT ... for any query you want to discuss and SHOW CREATE TABLE ... for the tables in question.


Hiding variable input in psql I am attempting to prompt for a username and password when running a database creation script, and I want to hide any text input for my password prompt. In Oracle's sqlplus, I can use HIDE at the end of the line to suppress the user's input from appearing on the screen. Is there an equivalent for PostgreSQL's psql?

Current progress:

\prompt 'Enter database user password: ' ss_password

\password username

... will change the password for a given username, whilst suppressing the inputted password. I don't know of a way to do it for a given variable.


How to take mysqldump with Generated column? I am getting below error while importing the dump,

  ERROR 3105 (HY000) at line 82: The value specified for generated
  column 'column_name' in table 'table_name' is not allowed.

When I describe the table with error, there are some generated columns in it.

  option_type         | varchar(45)  | YES  | MUL | NULL    | VIRTUAL
  GENERATED

What is the correct command to use when taking mysql dump for the database contains generated column?

MySQL server source and destination version:

  mysqld Ver 5.7.26 for Linux on x86_64 (MySQL Community Server (GPL))

MariaDB mysqldump CLI version:

  mysqldump Ver 10.17 Distrib 10.3.12-MariaDB, for Linux (x86_64)

This is a problem when using mysqldump from MariaDB with virtual generated columns.
MariaDB's mysqldump apparently dumps the generated values, but MySQL only accepts DEFAULT as value for a virtual generated column.
It seems like you need to use MySQL's mysqldump to correctly dump and restore virtual generated columns on a MySQL server.


Max Workers available and yet pending task high While exploring the data from system health extended events for event so server diagnostics I see for column query processing XML as below:

Max workers - 2944 ( based on 80 schedulers on my server)

Max workers created - 3097

Max workers available/idle - 2262

Pending tasks - 89

MS reports this as warning if there is any pending tasks greater than 1.

I am not able to understand this data from system health events that why there would be pending tasks when worker threads are already available as shown above and how are workers created more than maxworkers available as 2944 compared to created as 3097

And when should it be a concern or I should say better to interpret this information from query processing.

Update--

Wait stats since 04/20

CXPACKET  - 26.8%
PREEMPTIVE_OS_ENCRYPT_MESSAGE - 18.24%
LATCH_EX  -  17.9%

For threadpool

waiting task count=10407
wait time ms=29989ms
max wait time=641ms
signal wait time =1ms

Thanks

Your numbers don't totally make sense.  In particular, the value for "workersCreated" should not be higher than the value for "maxWorkers."  Here's an example of a normal-looking entry:

<queryProcessing maxWorkers="576"
                 workersCreated="86"
                 workersIdle="46"
                 tasksCompletedWithinInterval="950"
                 pendingTasks="0"
                 oldestPendingTaskWaitingTime="0"
                 hasUnresolvableDeadlockOccurred="0"
                 hasDeadlockedSchedulersOccurred="0"
                 trackingNonYieldingScheduler="0x0">

In your case, you've hit the max worker threads limit (as evidenced by the THREADPOOL waits in sys.dm_os_wait_stats).  Investigating those is a pretty big task, and probably tough to cover in this Q&A format.  This is what's causing tasks to be pending though.  Generally, you'll need to

Fix blocking issues (if that's what's causing threads to tie up schedulers)
Reduce parallelism (by reducing MAXDOP or CTFP) - this is kind of a band-aid fix
Change the app to run less queries at a time

Based on other questions you've asked on the site, I imagine that blocking and long-running transactions are the reason you're hitting this.  You'll need to continue to work with the vendor.

By the way, you can double check that the "max worker threads" setting hasn't been modified by running:

EXEC sp_configure @configname = 'max worker threads';

It should show the run_value as 0 if it hasn't been changed.


Read Oracle BLOB type via SQL I have an oracle table with field type BLOB, length 4000, data type "2004".
I know that this field simply contains some XML.  How can I read the value of the BLOB, using an SQL select statement?  Is there some conversion function to use?
The same table/field in Sql Server is simply text.

This oracle package will do the job

select dbms_lob.substr(b,2000,1) from blobtest;


mysql replication on windows server How to setup replication(Master/slave) in MySQL 5.6 while both servers are windows based?
All the things i have fond are Linux based replication. kindly someone help who experienced replication with windows based server.

Replication setup on a windows server is equivalent to doing it in other systems: The official manual provides you a detailed guide. In short:

Configure on both servers a different server-id, and activate the binary log on the master. If it is not already done, the most typical way to do that is modify the my.ini file and restart the service (net restart mysql):

[mysqld]
log-bin
server-id = 1

Make also sure that the master is accesible from the slave node and it is not bound to localhost (bind-address = 127.0.0.1)
Create a user on the master for the slave to connect with REPLICATION SLAVE privileges (use MySQL-command line for this):

mysql master> CREATE USER 'user_name'@'ip.of.the.slave' IDENTIFIED BY 'password';
mysql master> GRANT REPLICATION SLAVE ON *.* TO 'user_name'@'ip.of.the.slave';

Make a backup of the master and apply it to the slave. There are several ways to do that (stopping the server and copying the files works), but the easiest would be to se mysqldump (navigate to the bin directory of your sql-server installation (f.e. C:\Program Files\MySQL\MySQL Server 5.7\bin). Also you may need to provide credentials by appending this to both commands: -uroot -pPassword)

(on the master windows command line) mysqldump --all-databases --master-data > dump.sql
(on the slave windows command line ) mysql < dump.sql

At the beginning the dump.sql you will find a line like this:

-- CHANGE MASTER TO MASTER_LOG_FILE = 'file_name', MASTER_LOG_POS = file_pos;

Use those parameters to configure the replication on the slave:

mysql slave> CHANGE MASTER TO MASTER_HOST = 'ip.of.the.master',
                 MASTER_USER = 'user_name_you_just_created',
                 MASTER_PASSWORD = 'password_you_just_defined',
                 MASTER_LOG_FILE = 'file_name',
                 MASTER_LOG_POS = file_pos;

(beware, no quotes for integers, like MASTER_LOG_POS).
If everything is ok until now, just start replication on the slave:

mysql slave> START SLAVE;

To check that it is running, execute:

mysql slave> SHOW SLAVE STATUS;

And look for:

   Slave_IO_Running: Yes
  Slave_SQL_Running: Yes

GTID-based replication is a bit different, and I have not mentioned it.


index scan in select statement While testing query:

select AccountID, SchemaID, Name, Description, CurrentVersion
from [test].[dbo].[FormSchema]
where SchemaID like '%[^a-zA-Z0-9-_]%'

After running execution plan it is showing clustered index scan with 100% cost.  There is one clustered index on column schemaId.

My question is how can I make it index seek rather than scan?

SchemaID like '%[^a-zA-Z0-9-_]%' is going to be problematic no matter what you do.

Having said that, if you added an index like this:

CREATE INDEX IX_FormSchema
ON dbo.FormSchema(SchemaID)
INCLUDE (AccountID, SchemaID, Name, Description, CurrentVersion);

And change the query to:

WHERE SchemaID like '[^a-zA-Z0-9-_]%'

you might get an index seek.

Having said that, an index scan is not inherently a bad thing.  In fact, SQL Server is choosing to do an index scan because it believes that is the fastest way to get your results.


Best way to transfer millions of rows from one database to other There are two databases: DatabaseA and DatabaseB with the same schema. These databases have millions of rows.

The plan is to convert them into one single database (DatabaseCommon) and multiple schemas (schemaA and schemaB).

What is the best way to transfer data, indexes, etc. from the existing databases to DatabaseCommon?

Is there a Postgres script that can handle this use-case?

We can have downtime. There will be around 20 databases. Users are restricted to their own schema.

First rename the schemas in the current databases to have the names you want to end up with:

alter schema public rename to schema_a;

And the same for other databases.  Whether you need to change your apps, SQL functions, etc. depends on what kind of coding standards you used in the first place.  This can probably be done with the system online, if your apps can cope and if you juggle search_path appropriately.

Then lock people out of each database in turn, and use pg_dump to dump out the schema and data, and load it to the successor database.

pg_dump database_a -n schema_a | psql -1 database_common

If you have extensions in special schemas, elaborate systems of roles and grants, and stuff like that, then you might need to do some work to get that to work.  There is no substitute for having a test/QA database and using it for testing and practicing.


Is the order of attributes in a unique index still important in Oracle 12c? The internet mostly says, the order in a unique index does matter for the reasons of index search tree structure, but a colleague stated, Oracle changed that recently.

I can't find any information on such a change in the docs.

Is the order still influencing look-up speed for a unique index?


left join not working CREATE TABLE `table1` (
`id` INT(10) NOT NULL
)
ENGINE=InnoDB;

CREATE TABLE `table2` (
`id` INT(10) NOT NULL,
`value` INT(10) NOT NULL
)
ENGINE=InnoDB;

INSERT INTO table1
VALUES (1), (2), (3), (4);

INSERT INTO table2
VALUES (1, 100), (2, 200);

I want to get table like:

id value
1  100
2  200
3  null
4  null

My query is

SELECT t1.id, t2.value
FROM table1 t1
LEFT JOIN table2 t2
    ON t2.id = t1.id

and I got:

1 100
2 200

How should I query the database to get what I want?

hye i tried below query in mysql and SQL server databases its working fine, so please check once again. and tell me which database u tried.

SELECT t1.id, t2.value
FROM table1 t1
LEFT JOIN table2 t2
    ON t2.id = t1.id

id   value
--  --------
 1       100
 2       200
 3    (NULL)
 4    (NULL)

Thanks.


Why could query performance improve enormous after db restore on new server with worse hardware? A asp.net page doing very slow to load a list from SQL Server has increased it's performance very much after the whole database has been restored on a new SQL Server. The asp.net app has moved from and to the same servers.

The page's performance has improved to something about 1/5 of the previous load time.

The new system even has less Memory (12 GB instead of 30 GB).
- on the old SQL Server is restricted to use 20 GB of 30 GB and it claims 20 GB.
- on the new SQL Server is restricted to use 8 GB of 12 GB and it actually claims 4.5 GB only

The new system has an Intel Xeon E CPU while the old has an Intel Xeon X CPU.

Users say the performance has improved since the moving in general.

Unfortunately I have no benchmark of the page and/or the queries I am investigating on and also not for the involved servers at all.

This behavior is the opposite of my expectations!

So the question is, what could possibly have improved this?

Do statistics possibly play a role here? I would guess they are part of the db and so included in the restored db - or have they been created from scratch? Any thoughts and ideas are very welcome.

EDIT 11-March 2016:

I tested disk performance of both systems: The old disk performance made 0,3 MB/S to 55 MB/s with Average of 30 Mb/s. The new system shows 141 Mb/s to 1690 Mb/s with average 803 Mb/s. So since the disk performance seems to be more than 10x better, I guess this maybe a good reason for better performance. We are having a lot of disk usage because of bad design (select *) and lots of blobs and (n)TEXT types. So lots of data is not in cache and has to be loaded repetetively from storage. Additional the transaction log has been separated from the data files to another drive (Average 800 Mb/s), this was not the case on the old server).
Would anyone agree that we can assume this is (besides all your interesting thoughts) the main reason for improvement?

The amount of memory available to the SQL Server is an important input to the choice of execution plan made by the query optimizer.
It is certainly possible that the new SQL Server, with its reduced memory, is encouraging plans without memory-consuming operations (e.g. sorts and hashes) that happen to be 'better' for the type of queries you are executing.
If this is indeed a primary factor, reducing the amount of memory available on the original server would also likely increase its performance. Typically, this would be unusual, assuming statistics are good and the queries usually get reasonable cardinality estimates. Nevertheless, it might explain what you are seeing; a detailed analysis would be required to be sure.
To answer your final question: yes statistics are a persistent part of the database and are included in a backup/restore.
Update
Based on the new information in the question concerning the vastly improved I/O performance of the second system, yes, that is most likely the reason.


Backing up MS SQL Server database creates a huge transation log I'm hosting a SQL Server 2012 database server with about 100 databases.  I use the standard maintenance plan to backup all user databases daily and do hourly transaction log backups.

One database, which is about 80 MB, backs up fine at 6 AM but the following transaction log is typically 1-2 GB.  I've checked and the transaction log is getting properly truncated on the full backup but it immediately fills up after the backup.  I've tried switching to simple recovery, shinking the transaction log file, then re-enabling full recovery.  That all works fine and the .log file goes down to 4-5 MB.  However, within a day it's already up to 2 GB - most of which seems to happen when I perform the full backup.  The log has gotten up to 10 GB even though it gets truncated daily.

The database is only accessed through a single web application which doesn't appear to have anything hitting the database during the time where the transaction log balloons out.

I've attempted to query the transaction log itself but can't see anything obvious.

How can backing up an 80 MB database create a 2 GB transaction log? How can I find out what data is actually in the log and determine where it's coming from?

The statement "transaction log is getting properly truncated on the full backup but it immediately fills up after the backup" is not how backups operate though.  All transaction log truncation is done during the first transaction log backup after the full backup, the truncates are deferred until the full backup is completed.

What happens to transaction log backups during full backups?

If you are seeing a bigger transaction log after a full backup but before the first transaction log backup, that's typically by design.  However if you are saying you have almost no activity going on during the full backup which would cause a gig of activity then I would immediately start by looking at your jobs.  Often a reindex will cause a lot of logging but it would be odd for that to be the main culprit.  Carefully monitor what happens during that period and run the same queries.  Feel free to profile what is going on during that time as this could be a compound issue.


Why can't I use null values in joins? I have solved the query problem by using ...row_number() over (partition by... this is a more general question on why we cannot use columns with null values in joins. Why can't a null be equal to a null for the sake of a join?

Why can't a null be equal to a null for the sake of a join?

Just tell Oracle to do that:

select *
from one t1
  join two t2 on coalesce(t1.id, -1) = coalesce(t2.id, -1);

(Note that in standard SQL you could use t1.id is not distinct from t2.id to get a null-safe equality operator, but Oracle does not support that)

But this will only work if the replacement value (-1 in the example above) does not actually appear in the table. Finding such a "magic" value for numbers might be possible, but it will be very difficult for character values (especially because Oracle treats an empty string as null as well)

Plus: no index on the id columns will be used (you could define a function based index with the coalesce() expression though).

Another option that works for all types, without magic values:

              on t1.id = t2.id or (t1.id is null and t2.id is null)

But the real question is: does this make sense?

Consider the following sample data:

Table one

id
----
1
2
(null)
(null)

Table two

id
----
1
2
(null)
(null)
(null)

Which of combination of null values should be chosen in the join? My above example will result in something like a cross join for all null values.

T1_ID  | T2_ID
-------+-------
     1 |      1
     2 |      2
(null) | (null)
(null) | (null)
(null) | (null)
(null) | (null)
(null) | (null)
(null) | (null)


Should my application utilize database credentials for user login? I'm working on an application that has an ASP front end and a SQL Server database.  The way credential management is currently performed is there is a custom built User Table in the database that stores their email and encrypted password.  When the user logs-in, the password is retrieved from the database, decrypted by ASP engine, and is compared to the password they submitted.  Then, their userID is stored in the ASP session.

I think the user should log into the database instead of the application, since this is a database-centric app.  This way, history can be maintained by triggers on update and can capture the user's database credentials, instead of relying on ASP to pass the user name in.

What are best practices for where user management should be performed?  Is this idea even possible?  I've tried to do research on this topic, but can't find any good information.

I apologize if this is the wrong venue to ask this question.

Depends on who is allowed to access your application.

Probably not a good idea to give DB access to users since they'll be able to connect to the database directly which is probably not what you want.

If its a whitelist, maintain a list of users and if applicable which roles they hold in the database.

Run your web application using a Windows account, and grant access to that identity to the database.

Authenticate users using Windows Authentication, dont perform password management on your own.


select count(customer) from
(
    select customer from table
    group by customer
    having sum(value)<>0
) X


Is there a way to find the partition or subpartition name where my ID belongs I know if I need to select from any partition I need to provide partition name

SELECT * FROM TABLE PARTITION(P1);

What if I need to know which partition a particular value belongs using a sql query then?

SELECT PARTITION_NAME FROM TABLE WHERE DOC_ID = 'S00102981655537O';

Error

ORA-00904: "PARTITION_NAME": invalid identifier
00904. 00000 -  "%s: invalid identifier"
*Cause:
*Action:
Error at Line: 25 Column: 8

Oracle version: 11g

First get rowid of the row and object id of the object using DBMS_ROWID.

SQL> insert into t3 values('S00102981655537O',sysdate,'IN-RJ');

1 row created.

SQL> select dbms_rowid.rowid_object(rowid) from t3 where doc_id='S00102981655537O';

DBMS_ROWID.ROWID_OBJECT(ROWID)
------------------------------
             93178

SQL> select subobject_name from user_objects where data_object_id=93178;

SUBOBJECT_
----------
SYS_SUBP81

SQL> select * from t3 subpartition(SYS_SUBP81);

DOC_ID           DOC_DATET STATE
-------------------- --------- ----------
S00102981655537O     31-MAR-16 IN-RJ

As we can see that row belongs to SYS_SUBP81 subpartition.


Restore script while setting up database mirroring on secondary server I am trying to set up database mirroring for 10-15 databases . All these are around 1-2 TB in size. They have multiple groups and hence are scattered on multiple mount points on their primary server.

Now to begin with database mirroring , as best practise i want to make sure tat when database are restored on mirror they are on exact drives and paths as they were on primary. We have all the mount points and storage similar to primary.

Now this can be done manually but its way too tedious finding the path and drive and restoring database one by one. I see many PS scripts for restore but they just copy and restore db on default path. I know we can change using MOVE option but still finding current location of each files within those database is tedious.

Is there a better way of doing this that i am unable to figure out or a way i can prepare restore script for above case?

We have all the mount points and storage similar to primary.

If both primary and secondary have same file layout / structure, you will not even require to use RESTORE ... WITH MOVE. Just use restore database from disk .. with norecovery.

Alternatively, you can use

backup-dbadatabase with FileCount parameter to stripe the backups, CompressBackup. you can even use buffercount and maxtransfersize to make the backups faster. Then use
restore-dbadatabse with -UseDestinationDefaultDirectories parameter. This way it uses source folder structure when it does restore at destination. Just make sure it use NoRecovery parameter since you will set up dbs for mirroring.

  Is there a better way of doing this that i am unable to figure out or a way i can prepare restore script for above case?

Use dbatools as I mentioned above and no need to prep script. dbatools takes care of automation :-)


Parameter triggers different execution plan, but why? I have a table

CREATE TABLE [dbo].[Numbers]
    (
        [Date] [date] NULL,
        [Time] [time](3) NULL,
        [Value] [char](10) NULL
    )

and the table has > 10 Billion rows, therefore it is partitioned by month and has a clustered index on [Date], [Time] ASC

Now I use a table valued function that reads this data:

SELECT *
FROM [dbo].[QueryNumbers] ('2012-10-08','2012-10-08','07:00:00.000','08:00:00.000')

This returns me around 6000 rows in 1 second

However when I do the same like this:

declare

@StartDate date,
@EndDate date,
@StartTime time(3),
@EndTime time(3),

SET @StartDate = '2012-10-08';
SET @EndDate ='2012-10-08';
SET @StartTime ='07:00:00.000';
SET @EndTime = '08:00:00.000';

SELECT *
FROM [dbo].[QueryNumbers] (@StartDate,@EndDate,@StartTime,@EndTime)

The same query takes 3 minutes (which is a desaster), I played a bit with the parameters and it seems that the time parameter trigger the different behaviour. Anybody has a hint for me what is going wrong here ?

Your first query has constants for the parameters, so when sql compiles the plan it knows exactly what the parameters are going to be, and can use that knowledge to pick an optimal plan.

In the second query, the values are parameterized, therefore sql will create a plan that will work for any potential values for the parameters.

You can test this by running the parameterized query with the OPTION (RECOMPILE) clause.  This will allow sql to create an optimal plan for those specific values.

declare

@StartDate date,
@EndDate date,
@StartTime time(3),
@EndTime time(3),

SET @StartDate = '2012-10-08';
SET @EndDate ='2012-10-08';
SET @StartTime ='07:00:00.000';
SET @EndTime = '08:00:00.000';

SELECT *
FROM [dbo].[QueryNumbers] (@StartDate,@EndDate,@StartTime,@EndTime) OPTION (RECOMPILE)


T-SQL: Complete Database in one table at a relational data model every logical relation is a single table with values and so on. I know someone who is this too much work and he wants to create only one table which contains all. This could be look like:

ID | Name       | N    | M    | Value
------------------------------------------------
1  | Customer   | NULL | NULL | NULL
2  | LastName   | NULL | NULL | NULL
3  | FirstName  | NULL | NULL | NULL
4  | NULL       | 1    | 2    | Thomson
5  | NULL       | 1    | 3    | Steve
6  | House      | NULL | NULL | NULL
7  | Color      | NULL | NULL | Blue
8  | Color      | NULL | NULL | Red
9  | Blue House | 6    | 7    | NULL
10 | Red House  | 6    | 8    | NULL

Make it sense to design a database in this kind or should it be "classic"?

Thanks for any response.

Congratulations, someone has reinvented EAV (Entity Attribute Value).  Please study up on the subject with them.

The short form is: one or more EAV tables can be useful in certain specific cases, usually when accompanied by other tables, but you lose most of the benefit of a relational database when you move to them.

I would also ask: If someone wants to put everything in a single EAV, for what purpose does someone still want to use SQL Server?  What benefits does SQL Server provide at that point over other approaches, particularly transactional databases (one example is a simple structure where you have "key columns" then "data columns" and you always look up data via the key).

Wikipedia has a reasonable entry.

A couple interesting notes from Wikipedia (the second of which applies in particular to the "only one Value column in the whole database" idea you listed someone as having proposed):

an index on the value column of an EAV table is essentially useless
constant data type inter-conversions are required if one wants to do anything with the values

Tony Andrews covers EAV as well as OTLT with an example of an EAV query that gets a bit cumbersome.

Aaron Bertrand argues for the EAV approach in select cases.

Aaron Bertrand has perhaps the best bullet list:

Cons from Aaron:

it is difficult to control the attribute names, and to make them consistent;
it is difficult to enforce data type integrity and referential integrity;
it is difficult (and slow) to pivot and/or self-join the values to make a single row;
it is difficult to make certain attributes mandatory;
and, we've been trained to view this as the "wrong" way to solve the problem.

Pros from Aaron:

But what do you do when you have customers that demand real-time, on-demand addition of attributes that they want to store?


Should I use SSIS to write programming logic? I have come across a fairly complex system where the business logic is written in SSIS. Since SSIS is not able to perform a big portion of this logic, it is heavily dependent on C# script tasks to perform the operations.

To give you an example of things that requires C# Script task, the application requires making API calls to a 3rd party application for getting the data and also requires a third party library for performing some calculations on the data.

Calling out to C# script tasks makes development and debugging more difficult... but at the same time, SSIS allows programmer to make use of C# script tasks.

What are the applications of SSIS?
Is there any benefit in implementing a complex logic using SSIS + C#
Script Tasks? If we need to use C# Script , then why not
write the whole logic in C#?

To answer your question directly, if you know T-SQL and c# SSIS can do pretty much anything you need. My personal beef with it as a platform however stems from the fact the it tends to deal with data procedurally, instead of as a set. This is important because it's where RDMS's shine, so you're cutting off one of your best resources off at the knees. From my perspective, despite it's vast capabilities, it's more of a platform for people who aren't comfortable programming.

To answer your question less directly, I found myself in a very similar situation 12 months ago. I found it useful to start by asking myself these two questions:

What language am I most comfortable with?
And what environment am I most comfortable in?

After answering these questions, I chose to build my solution using those tools which enabled my skills.

In my case the answer was T-SQL and SSMS. Thus, I elected to remove the dependency on SSIS completely, and went the custom T-SQL/Agent route. In the end, I couldn't be happier with the decision. Maintaining & debugging the solution is a simple task, since I'm so familiar with the language an environment.


Does there exist a way to determine the exact file that contains an allocation unit in a filegroup of multiple files? I was hoping to get a granular view of which database files contained which allocation units for the various HoBTs (both aligned and non-aligned) living in a database.

The query I've always used (see below) has served me well until we began creating multiple data files per filegroup and I'm only able to figure out how to get as granular as the filegroup level.

select
    SchemaName = sh.name,
    TableName = t.name,
    IndexName = i.name,
    PartitionNumber = p.partition_number,
    IndexID = i.index_id,
    IndexDataspaceID = i.data_space_id,
    AllocUnitDataspaceID = au.data_space_id,
    PartitionRows = p.rows
from sys.allocation_units au
join sys.partitions p
    on au.container_id = p.partition_id
join sys.indexes i
    on i.object_id = p.object_id
    and i.index_id = p.index_id
join sys.tables t
    on p.object_id = t.object_id
join sys.schemas sh
    on t.schema_id = sh.schema_id
where sh.name != 'sys'
    and au.type = 2
union all
select
    sh.name,
    t.name,
    i.name,
    p.partition_number,
    i.index_id,
    i.data_space_id,
    au.data_space_id,
    p.rows
from sys.allocation_units au
join sys.partitions p
    on au.container_id = p.hobt_id
join sys.indexes i
    on i.object_id = p.object_id
    and i.index_id = p.index_id
join sys.tables t
    on p.object_id = t.object_id
join sys.schemas sh
    on t.schema_id = sh.schema_id
where sh.name != 'sys'
    and au.type in (1,3)
order by t.name, i.index_id,p.partition_number;

However, this query won't work when there are multiple files in a filegroup as I can only get so far as to relate an allocation unit to a data space and, ultimately, a filegroup. I'd like to know if there's another DMV or catalog that I'm missing that I can use to further identify which file in the filegroup contains an allocation unit.

The question behind this question is that I'm trying to evaluate the actual effects of compressing partitioned structures. I know I can do a before-and-after using FILEPROPERTY(FileName,'SpaceUsed') for the file and a before-and-after on sys.allocation_units.used_pages/128. to get this information, but the exercise itself made me wonder if I could identify the specific file that contains a specific allocation unit.

I've been messing around with %%physloc%% in the hopes it could help, but it doesn't quite get me what I'm seeking. The links below were provided by Aaron Bertrand:

Where are my Rows? – Using the %%physloc%% Virtual Column (sqlity.net)
SQL Server 2008: New (undocumented) physical row locator function by Paul Randal

Try the following query. It first creates a local temporary table and then populates it with the AllocationUnitID-to-FileID associations found in sys.dm_db_database_page_allocations, an undocumented Dynamic Management Function (DMF) introduced in SQL Server 2012 (for versions prior to 2012, you can get this info from DBCC IND()). That local temp table is then JOINed into a modified version of the original query.

The data from that DMF is placed into a temporary table for performance since, depending on the size of the database, it could take more than a few seconds to get that data. The DISTINCT keyword is used because that DMF returns one row per data page, and there are multiple data pages per each allocation unit.

I left-JOINed that data into the original query since the original query returns allocation units that have 0 data pages (typically ROW_OVERFLOW_DATA and LOB_DATA types). I also added the total_pages field so that it would be easier to relate that data point to the rows that have NULLs for the Data Files. If you don't care about the Allocation Units that have 0 rows, then it would be fine to change that LEFT JOIN to be an INNER JOIN.

IF (OBJECT_ID(N'tempdb..#AllocationsToFiles') IS NULL)
BEGIN
    -- DROP TABLE #AllocationsToFiles;
    CREATE TABLE #AllocationsToFiles
    (
      ObjectID INT NOT NULL,
      IndexID INT NOT NULL,
      PartitionID INT NOT NULL,
      RowsetID BIGINT NOT NULL,
      AllocationUnitID BIGINT NOT NULL,
      AllocatedPageFileID SMALLINT NOT NULL
    );
END;

IF (NOT EXISTS(SELECT * FROM #AllocationsToFiles))
BEGIN
  --TRUNCATE TABLE #AllocationsToFiles;
  INSERT INTO #AllocationsToFiles (ObjectID, IndexID, PartitionID, RowsetID,
                                   AllocationUnitID, AllocatedPageFileID)
    SELECT DISTINCT alloc.[object_id], alloc.[index_id], alloc.[partition_id],
           alloc.[rowset_id], alloc.[allocation_unit_id], alloc.[allocated_page_file_id]
    FROM   sys.dm_db_database_page_allocations(DB_ID(), NULL, NULL, NULL,
                                               'LIMITED') alloc
    WHERE  alloc.is_allocated = 1
    AND    alloc.is_iam_page = 0;
END;

SELECT
    SchemaName = sh.name,
    TableName = t.name,
    IndexName = i.name,
    PartitionNumber = p.partition_number,
    IndexID = i.index_id,
    IndexDataspaceID = i.data_space_id,
    AllocUnitDataspaceID = au.data_space_id,
    PartitionRows = p.[rows],
    TotalPages = au.total_pages,
    AllocationUnitType = au.type_desc,
    LogicalFileName = dbf.[name],
    PhysicalFileName = dbf.[physical_name]
    --,p.[object_id], p.[partition_id], au.allocation_unit_id
FROM sys.allocation_units au
INNER JOIN sys.partitions p
        ON au.container_id = IIF(au.[type] = 2, p.[partition_id], p.[hobt_id])
INNER JOIN sys.indexes i
        ON i.[object_id] = p.[object_id]
       AND i.index_id = p.index_id
INNER JOIN sys.tables t
        ON p.[object_id] = t.[object_id]
INNER JOIN sys.schemas sh
        ON t.[schema_id] = sh.[schema_id]
LEFT JOIN (#AllocationsToFiles alloc
       INNER JOIN sys.database_files dbf
               ON dbf.[file_id] = alloc.AllocatedPageFileID
          )
        ON alloc.ObjectID = p.[object_id]
       AND alloc.IndexID = p.index_id
       AND alloc.PartitionID = p.partition_number
       AND alloc.AllocationUnitID = au.allocation_unit_id
WHERE sh.name <> N'sys'
ORDER BY t.name, i.index_id, p.partition_number;


SSIS convert DT_WSTR to date I'm new to SSIS trying to covert data types. I'm pulling data from Google Analytics and loading data into a SQL 2008 database. The date column from Google analytics appears as "DT_WSTR" and the values are in the format "20130402" (year:month:day).  I want to change the format to a date format so i can load it into a date field in my database.  How do I do this?

Thanks

You have to use convert DT_WSTR to DT_DBTIMESTAMP. You can find BOL reference here.

for e.g.instead of "Getdate()" use your Date_Column

(DB_TIMESTAMP)((DT_WSTR,4)YEAR(GETDATE()) + RIGHT("0" + (DT_WSTR,2)MONTH(GETDATE()),2) + RIGHT("0" + (DT_WSTR,2)DAY(GETDATE()),2) + (DT_WSTR,10) Time_Column)

Also,  you can use the Data Conversion transformation, which is probably easier if you are only changing the data type.

EDIT: You can also look for an option of SSIS GoogleAnalyticsSource from codeplex.


How to connect use INSERT query using SELECT query in mySQL I have 2 tables, cce_sa_fa & cce_activity

I used the following line to get the cce_sa_fa not included in cce_activity

select id from cce_sa_fa where id NOT IN (select safa from cce_activity)

Using this query, I got 113 rows. Now I need to INSERT these ids into cce_activity. Here the id needs to be inserted and other columns are with fixed value. Usual insert query for cce_activity is as follows.

INSERT INTO `cce_activity` (`id`, `safa`, `name`, `mark`) VALUES (NULL, '"+id_of_ cce_sa_fa+"', 'Activity 1', '0');

How can I insert all the ids from the SELECT query to INSERT into cce_activity

if columns same:

INSERT INTO cce_activity select * from cce_sa_fa where id NOT IN (select safa from cce_activity)

if not, something like:

INSERT INTO cce_activity (col1, col2, col3) select col1, col2, col3 from cce_sa_fa where id NOT IN (select safa from cce_activity)


Database is not showing up in the SSMS I have a test database and I was able to see the database in SSMS and all of a sudden Its not listng my database and When I checked the mdf and ldf files they seems to be locked . I am using Sql server 2008 R2. Can anyone help me?

Just converting the part of comment to an answer so as to close this question:

It seems like someone had the database detached, because of which database was not visible and hence you were also able to view its respective mdf and ldf.

Right Click Databases-->  click Attach--> Choose the respective mdf files for the database in question.

Refresh and check , the database should be visible.


Understanding Backup Types I have recently started a role as a junior DBA... Yesterday I created a new database for a co-worker and my boss asked me to make sure we get a backup plan on it asap.

I didn't think much about it, went into the agent, created a job that took a full backup every night @ 1AM.  Being proud of myself I went to bed and didn't think anymore about it.  This morning I started thinking that was not "Good enough" - if the database died at 12AM, then they would lose almost 23 hours of data, which would probably get me fired. :)

So to make sure I understand this - I think I need to do Differential Backups in addition to my full backup.. After looking at this tutorial, I had a few questions:

Would I just go into the agent and add a job that happens at every hour (for example) that creates a differential backup?
If my understanding is correct, that would backup the Transactional log every hour until 1AM when it does a Full backup, then it would "Reset" the T-Log and start over again from the next day, so at the most would be 1 hour of data loss - Is that correct?
So in the end, I would have 2 jobs on the agent, one that fires off every day @ 1AM to do a "FULL" backup, and one that fires off every hour that does a differential backup?

Kin is correct to point you to Ola Hallengren's backup and maintenance solution.

It sounds like you're new, so also consider looking into:

SQLServerCentral's Top 6 Myths of Transaction Logs.
Paul Randall's Transaction Log blog entries
Paul Randall's Backup and Restore blog entries

Mandatory: Read A SQL Server DBA myth a day: (30/30) backup myths

Kimberly Tripp's Transaction Log blog entries

Mandatory: Read Transaction Log VLFs – too many or too few?

Aaron Bertrand is correct to point you to Why Does the Transaction Log Keep Growing or Run Out of Space?.

Usr is correct; until you test restores, assume your backups are worthless.

The commenters are also correct, you must consult with the business.

In more detail, you need:

RPO: Recovery Point Objective.  How much data loss is acceptable - i.e. restore back to within an hour before the loss?  A day?
RTO: Recovery Time Objective.  How long it takes to get the system back up again.
At least a skeleton DR plan, in particular, what types of "disasters" are your RPO and RTO good for?

corrupt database
hard drive crash
multiple drive crash resulting in RAIDset loss
accidental file deletion
server dropped during move
server stolen
building burned down
regional natural disaster (large hurricanes, earthquakes, typhoons, tsunami, major volcanic eruptions, major flooding)

Budget

After you get this, you can go back and renegotiate the previous points.

As far as a general backup plan, I would start with considering:

Search for anywhere someone's truncating the log... and stop that!  You don't want to breaking your log chain!

Likewise if you're switching in and out of SIMPLE recovery model.

Figure out where your backups need to go; backing up to the same disk spindles that hold your databases or logs is pretty pointless by itself; lose the disks, and you lose both the active database and the backups all in one fell swoop.
In all cases, set CHECKSUM on.
Full backups for everything, regularly.

Maybe this is daily, maybe it's weekly, maybe it's biweekly, maybe even monthly.
NOTE: Keeping more than one around is useful; for FULL (and BULK-LOGGED if there are no bulk-logged changes) recovery model databases, you can skip a corrupt full backup if you have a prior full backup and an unbroken log chain.

Full backups are the ONLY backup allowed for Master.

Timing on this must be at least as frequent as your RPO.

Don't forget to back up msdb too, and you might as well toss model in.

yes, model.  Sometimes it has user defined types, etc.

Differential backups on SIMPLE mode databases

Timing on this must be at least as frequent as your RPO.

OPTIONAL: Differential backups on FULL and BULK-LOGGED databases

these can allow you to have faster restores
these can also allow you to "skip over" damaged/lost/corrupt transaction log backups that are BETWEEN your full backup and a DIFFERENTIAL, after which you can continue restoring transaction logs as long as your log chain is unbroken from that point.

Log backups on all FULL and BULK-LOGGED database.

Timing on this must be at least as frequent as your RPO.
This is MANDATORY in order to keep t-log size down

Run test restores; it doesn't matter if they're on the same server or a different server, just run them.
Who has access to the backups
Encryption of the backups

Key management of the encryption

Offsite storage

And how that affects RTO

during the "larger" disasters; even so much as a blizzard or mudslide can add hours or days to this.


Making the Oracle optimizer use a virtual column to find out about a partition I have a large table that has three columns like this:

"START_DATE" DATE,
"START_VALUE" NUMBER(10,7)
"START_DATE_VALUE" NUMBER(18,7)
  GENERATED ALWAYS AS
  (
    (extract(YEAR FROM START_DATE) * 10000 +
     extract(MONTH FROM START_DATE)*100 +
     extract(DAY FROM START_DATE))*power(10,3) +
     (START_VALUE+180)
  ) VIRTUAL

The START_DATE_VALUE column is a virtual column that is used for partitioning. However, when I have a query like this:

select *
from mytable
where
      start_date > to_date('02-01-2012', 'MM-DD-YYYY')
 and start_value > 120.23452

It scans all partitions for the result. How can I make Oracle use the virtual column and then pick just the right partition to work on it?

Sorry my table definition is very large, I can't copy it in here.

I think you have a problem with your virtual column definition. For your special values 2012-02-01 (in YYYY-MM-DD format) and 120.23452 the value of the virtual column is

2012*10000+2*100+1*1000+180+120.23452 = 20120000+200+1000+300.23452 =20121500.23452

and not

20120201300.23452

as you expected.

Also check if your virtual column is the column your table is partitioned by.

From the VLDB and Partitioning Guide:

  Virtual column-based partitioned tables benefit from partition pruning
  for statements that use the virtual column-defining expression in the
  SQL statement.

So I think select-statement

select * from mytable where start_date > todate('02-01-2012', 'MM-DD-YYYY') and    start_value > 120.23452

should be something like

select * from mytable where (
  extract(YEAR FROM START_DATE) * 10000 +
  extract(MONTH FROM START_DATE)*100 +
  extract(DAY FROM START_DATE))*power(10,3) +
  (START_VALUE+180)
)  > 20121500.23452

for partition pruning to take place.

The

todate

function you use in your select statement does not exist in oracle sql. The name of the function is

to_date

.


What happens if we copy neo4j's data folder while the server is running? If my neo4j server is running and I copy the data folder using copy/rsync folder, what are the chances of data corruption?

The copied store might be corrupted, but also might be recoverable.

There's a good reason neo4j has a backup tool: this method is not safe.

If you need to snapshot the file system, cleanly shut down the DB first.


Using Mysql and MongoDB together I currently have all of my data in a MySQL database which handles my entire application system. My current setup is a Java desktop application that publishes data to the database through a NodeJS API. There is an iOS application that connects to this API as well as a website that allows people to view the data. The website updates the data on the screen every second for each person. I fear that I will not be able to keep my costs down as the users scale because the query that is run every second contains a lot of joins and sorts.

What I would like to do is add a Mongo database like a cache. Overtime certain data is inserted to MySQL from my API, I would have the API run that intensive query and store the results in the mongo database. I am currently having to take the results form the query and transform it into a lot of nested arrays and return it in son form. It seems like I could save a lot of time/resources by only running the query when data changes and then storing it already formatted into the Mongo database.

I actually started with using purely MongoDB a while back, but it was her to run reports and analyze the type of data I am storing. It really needs to be stored normalized for easier reports.

Does this idea seem feasible and practical? I feel like I will have to do a lot of scaling with the MySQL side of things as my user base grows.

What is the data like?  Why does it need updating every second?  Etc.  (Your question is very vague; some of my Plans may not apply, but I can't tell without understanding the problem set.)

Plan A:

If you are SELECTing identical queries more often than you are changing the underlying table(s), the MySQL's "Query cache" may be an excellent solution.

query_cache_type = ON
query_cache_size = 100M  -- No more (unless using Aurora)

The rest is automagic.

Plan B:

Redis or Memcached make better caching tools than another database (eg, MongoDB).

Plan C:

Let's look at your queries and see if they can be sped up.  Better indexes, rearrange the schema, better queries, etc.

Plan D:

Devise some scheme that "knows" whether the data has changed, and avoid re-performing the query.


How much disk space do I need to add a primary key to a very large table? I'm using Microsoft SQL Server 2014 - 12.0.4100.1 Enterprise Edition (64-bit) on Windows NT 6.3  (Build 9600: )

I have a 491 GB table with 3.6 billion rows that does not have a primary key. The primary key I wish to add will only cover a single bigint column which contains unique values. The database has 477 GB of free space, but that's not enough it seems.

Trying to add it fails with the following error:

  Could not allocate space for object 'dbo.SORT temporary run storage:
  424251614560256' in database '[Redacted]' because the 'PRIMARY'
  filegroup is full. Create disk space by deleting unneeded files,
  dropping objects in the filegroup, adding additional files to the
  filegroup, or setting autogrowth on for existing files in the
  filegroup.

The database name in the error is the DB to which I'm trying to add the PK, not 'tempdb', which is what I often see in this sort of error.

The command I'm using:

ALTER TABLE REDACTED ADD CONSTRAINT [PK_REDACTED] PRIMARY KEY CLUSTERED
(
    [RedactedId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

At first, the command was failing after 8-9 minutes, then my DBA added another data file to the DB on a drive with about 150 GB of free space. Running it a second time failed after 24 minutes. That's progress, but I have no idea how much free space is needed, or how long it will actually take to add this index. There is currently 477 GB free in this database, but that's still not enough to run this sort it seems.

Is there any way to calculate how much space is needed to run this command on 3.6 billion bigint values? Or is there some other trick that will help this command succeed? What does that large number mean in the error?

I would also add that not only data pages will be moved when creating a clustered index - also all the non-clustered indexes will have to be rebuilt since each row in non-clustered index contains clustered key value. All these data movements need to be logged and sorting process will heavily use tempDb since you probably don't have 500 GBs of memory available, right? ;)

You'll find plenty of debates and different opinions about using heaps and I'm of course speculating here but most likely there's a reason why such a big table doesn't already have a clustered index defined on top of it. It's not exactly the best practise but sometimes it does make sense to define a table without clustered index (quick, minimally logged inserts) with a few narrow non-clustered indexes that cover frequent queries.

Decision about adding any kind of index (primary, clustered, non-clustered, XML, spatial, ...) depends of course on the problem you're trying to solve. In OLTP system you typically optimise your indexes to improve write performance while in reporting systems like OLAP you usually need to work on read types of workloads.

And few suggestions to wrap this up:
- primary key doesn't have to be clustered
- if the table is not partitioned you should definitely consider partition it
- if your goal is to make certain types of queries faster (and not trying to enforce uniqueness of your bigint column) you should consider creating additional (or modifying existing) non-clustered (possibly filtered) index or indexed view (perfect for aggregate queries)

Hope it helps, good luck!


Dates values displayed 0000-00-00 I have imported a csv file into my database created in phpmyadmin using sql.

  Server version: 5.6.37 - MySQL Community Server (GPL

I have a column for dates which has a datatype of date. The data displayed on webpage for all the dates are as 0000-00-00. However in the csv file the format is 12/2/2016. Could any one please help how could I resolve this issue?

Use user-defined variable for to convert the date value from dd\mm\YYYY format to the date/datetime datatype value during import:

LOAD DATA INFILE
....
(field1, field2, ..., @date, ...)
SET datefield = STR_TO_DATE(@date, '%d\/%m\/%Y');


Merge 2 SQL statements into 1? Can anyone help me transforming these 2 SQL statements into 1?

$query2 = "SELECT DISTINCT `UID` FROM `ratings`";
$query3 = "SELECT `RID`,`rating` FROM `ratings` WHERE `UID` = $uid";

original php code:

$arrayAux=array();
$query2 = "SELECT DISTINCT `UID` FROM `ratings`";

$result2 = mysql_query($query2);

if(!$result2) return $arrayAux;

    while ($row2=mysql_fetch_array($result2,MYSQL_ASSOC))
    {
    $array2=array();
    $uid=$row2['UID'];

            $query3 = "SELECT `RID`,`rating` FROM `ratings` WHERE `UID` = $uid";
            $result3 = mysql_query($query3);
            if(!$result3) return $arrayAux;
            while ($row3=mysql_fetch_array($result3,MYSQL_ASSOC))
            {
                $rid=$row3['RID'];
                $rating=$row3['rating'];

                $array2[$rid] = $rating;
                }

    array_push($arrayAux, $array2);
        }
return $arrayAux;

Thank you!

select rid,rating,uid from ratings group by uid;


IF NOT NULL then UPDATE else keep the value of the field I think I am somehow close to get it work, but for some reason I still get errors.

I have the following UPDATE query:

UPDATE DeviceAttribute
SET Details = CASE Name
          WHEN 'Accessories' THEN @Accessories
          WHEN 'Description' THEN @Description
          WHEN 'Specification' THEN @Specification
          ELSE Details
END
WHERE DeviceID = 10

The parameters are filled in by different users. The problem I face now is that even when you want to update a single field you still have to fill out with the old data the other parameters. Therefore I would like to set the option IF @parameter IS NULL THEN keep the value that is already stored in the DB. I tried to find some solution and something like the following query seems to be the solution but I can't get it to work:

UPDATE DeviceAttribute
SET Details = CASE Name
          WHEN 'Accessories' IS NOT NULL THEN @Accessories
          WHEN 'Description' IS NOT NULL THEN @Description
          WHEN 'Specification' IS NOT NULL THEN @Specification
          ELSE Details
END
WHERE DeviceID = 10

THE DB is stored in SQL Server 2008

Thanks in advance for the help.

EDIT for clarifying:

The original table looks like this

DeviceID|Name         |Details   |
     10 |Accessories  |earplugs  |
     10 |Description  |protectors|
     10 |Specification|BeatsByDre|

For publishing reasons I made a query to turn the tables. So when the Select query is ran the return table looks like the following

DeviceID|Accessories|Description|Specification|
     10 |earplugs   |protectors |BeatsByDre   |

I created a UI to let system users update the different fields like Accessories, Description,Specification.
The Update works if I update all fields with the query shown in the top. However when I Leave a textbox empty, then I get an error that the @parameter is missing a value. So trying to find a solution to update only the field where is something written. So if @parameter IS NULL then keep the original value in the DB.
I found another solution which is really close to what I want but can't get it to work.
Here is the other solution:
https://stackoverflow.com/questions/9387839/mysql-if-not-null-then-display-1-else-display-0

I think this will solve the issue:

UPDATE DeviceAttribute
SET Details = CASE Name
          WHEN 'Accessories'   THEN COALESCE(@Accessories, Details)
          WHEN 'Description'   THEN COALESCE(@Description, Details)
          WHEN 'Specification' THEN COALESCE(@Specification, Details)
          ELSE Details
              END
WHERE DeviceID = 10 ;

or this (to avoid redundant updates):

UPDATE DeviceAttribute
SET Details = CASE Name
          WHEN 'Accessories'   THEN @Accessories
          WHEN 'Description'   THEN @Description
          WHEN 'Specification' THEN @Specification
          ELSE Details
              END
WHERE DeviceID = 10
  AND ( Name = 'Accessories'   AND @Accessories IS NOT NULL
     OR Name = 'Description'   AND @Description IS NOT NULL
     OR Name = 'Specification' AND @Specification IS NOT NULL
      ) ;

or this, using a table value constructor:

UPDATE da
SET da.Details = upd.Details
FROM DeviceAttribute AS da
  JOIN
    ( VALUES
      ('Accessories'   , @Accessories),
      ('Description'   , @Description),
      ('Specification' , @Specification)
    ) AS upd (Name, Details)
    ON upd.Name = da.Name
WHERE da.DeviceID = 10
  AND upd.Details IS NOT NULL ;


I would like to launch a pg_dump command from a remote computer I would like to launch the command: pg_dump.exe -h 255.255.255.255 -p 5432 -U postgres -F p -a -v -f "test.db.dump" mydb from a remote computer. Both the database and the remote computer are under xp.

I face two troubles:

when executed on the command line of the server, pgdump will first prompt for a password. How can I grant the password in the command line ? A quick look at the documentation of pg_dump doesn't obviously accept a flag to do that (though I may have missed it).
if both machines had been under linux I would have tried an SSH connection with Putty to execute some commands on the psql server... but with both machines being under windows, I don't have an idea where to start. Does the psqlODBC driver allow access to pg_dump ? Is there an equivalent to SSL for windows ?

Only to explain the context, indeed my main goal is to achieve a diagnostic script on the remote machine that will gather some meaningful information, including a dump of some or all tables of the postgresql base. The diagnostic script will the copy the dump into another place on the domain where all diagnostic results are gathered.

The script has to be executed on the remote machine, because it must also performs other operations on other computers connected to the domain that aren't the postgresql database.

Regarding the password, you have two choices:

An environment variable named PGPASSWORD: http://www.postgresql.org/docs/current/static/libpq-envars.html
A configuration file containing the password: http://www.postgresql.org/docs/current/static/libpq-pgpass.html

I don't understand why you need to run the pg_dump remotely? You can run pg_dump on any computer that can connect to the database. It is not a "server application".

But to run a program on a remote Windows computer you can use PsExec


Can't Dump Database Because MS SQL Server Management Studio Won't Show Any Databases If 1 Is Permission Denied I am logged into SQL Server Management Studio with an account that has access to multiple databases, but not DB_123. As shown in the picture below, clicking to expand the "Databases" folder in the Object Explorer results in an error message.

I'm trying to follow instructions here and here to right-click on my database and export it that way. However, I cannot right-click on it because it won't show up in the Object Explorer. Yet I do have permission to query the database.
Is there any way to get the database(s) to appear in the Object Explorer or to otherwise dump the database(s) without using the Object Explorer?

One thing you can do, if you need both the data and schema, is script out a backup of the database instead of trying to use the UI in SSMS. You can find how to do that in the MS Docs: Create a Full Database Backup - Using Transact-SQL.
This is the T-SQL example from the above docs using your database name, for reference:
USE DB_D002;
GO
BACKUP DATABASE DB_D002
TO DISK = 'c:\backup folder\DB_D002.bak'
   WITH FORMAT,
      MEDIANAME = 'SQLServerBackups',
      NAME = 'Full Backup of DB_D002';
GO

Note if you run into the "Operating system error 5(Access is denied.)" error, then likely the file path you provided to the "TO DISK" option in the query does not provide write access to the SQL Server Service account (in Windows), and you can provide a different path that does or follow this StackOverflow on how to fix it for the specified path.


TIME Function changes between 5.5 and 5.6 I'm facing an issue, where I can't get the same result with the TIME function when migrating from MySQL 5.5.46 to MySQL 5.6.34.
The thing is, when I use the TIME function in 5.5, it returns NULL when datetime is '0000-00-00 00:00:00', but only if it's that value comming from the table, but with 5.6 I'm getting '00:00:00'. It doesn't sound bad, but there's many queries that are relying in the behavior from 5.5 (Result is NULL).
Here's an example I did to test exactly that behavior:

First, the table:

CREATE TABLE `prueba_time` (
  `check_in` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Then I insert one row with value 0000-00-00 00:00:00

mysql> INSERT INTO prueba_time VALUES ('0000-00-00 00:00:00');

And then I test the TIME function... The result in 5.5 is:

mysql> SELECT TIME(check_in),check_in,TIME('0000-00-00 00:00:00') FROM prueba_time;
+----------------+---------------------+-----------------------------+
| TIME(check_in) | check_in            | TIME('0000-00-00 00:00:00') |
+----------------+---------------------+-----------------------------+
| NULL           | 0000-00-00 00:00:00 | 00:00:00                    |
+----------------+---------------------+-----------------------------+

And the same query in 5.6:

mysql> SELECT TIME(check_in),check_in,TIME('0000-00-00 00:00:00') FROM prueba_time;
+----------------+---------------------+-----------------------------+
| TIME(check_in) | check_in            | TIME('0000-00-00 00:00:00') |
+----------------+---------------------+-----------------------------+
| 00:00:00       | 0000-00-00 00:00:00 | 00:00:00                    |
+----------------+---------------------+-----------------------------+

So... different result and I can't find out why...

The sql_mode it's different. In 5.5 it's empty, and in 5.6 I'm using STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION but according to what I've read there's no affecting in this. I hope you can help me with this.

I cannot see a problem here. If anything, I would say the problem was with the older behaviour, because it was just inconsistent. Depending on whether '0000-00-00 00:00:00' was passed as a literal or as a column value, TIME would return you either a 00:00:00 or a null. Since version 5.6, however, it consistently returns 00:00:00.

So, to answer why the behaviour changed, the previous behaviour was buggy and the new one is (more) consistent.


Use utl_match.jaro_winkler_similarity to select distinct rows using Oracle I have a table with log messages that many times contain repeated information with slightly different values.
For example:

Unable to make thread 19043 a realtime process
Unable to make thread 20763 a realtime process
Unable to make thread 22179 a realtime process
FEED_XYZ Secondary Instrument Lines not configured
FEED_ZZZ Secondary Instrument Lines not configured
(...)

I'd like to have just one line from each group above:

Unable to make thread 19043 a realtime process
FEED_XYZ Secondary Instrument Lines not configured

Is there a way to use a select query to retrieve distinct rows with utl_match.jaro_winkler_similarity?

Something like:

select log_message
from logs_table
where "utl_match.jaro_winkler_similarity between rows < 80"

I know that I can make a pipelined function or some other PL/SQL routine, but I just would like to double-check if there's some easier way of doing this.

I'm not overly familiar with the UTL_MATCH.JARO_WINKLER_SIMILARITY function, but using it in a UNION query, in conjunction with the ROW_NUMBER analytic function, will give you the result you desire:

WITH
    temp AS
    (
    SELECT log_message, ROW_NUMBER() OVER (ORDER BY log_message) rn
    FROM logs_table
    WHERE UTL_MATCH.JARO_WINKLER_SIMILARITY(log_message, 'Unable to make thread 19043 a realtime process') > 80
    UNION
    SELECT log_message, ROW_NUMBER() OVER (ORDER BY log_message) rn
    FROM logs_table
    WHERE UTL_MATCH.JARO_WINKLER_SIMILARITY(log_message, 'FEED_XYZ Secondary Instrument Lines not configured') > 80
    )
SELECT log_message
FROM temp
WHERE rn = 1
;

Result (screenshot from test DB at apex.oracle.com):

If you just need any value from each group (as opposed to the "first" value in each sorted group), you could use ROWNUM criteria instead of the ROW_NUMBER functions, which would return a similar result:

WITH
    temp AS
    (
    SELECT log_message
    FROM logs_table
    WHERE UTL_MATCH.JARO_WINKLER_SIMILARITY(log_message, 'Unable to make thread 19043 a realtime process') > 80
      AND ROWNUM = 1
    UNION
    SELECT log_message
    FROM logs_table
    WHERE UTL_MATCH.JARO_WINKLER_SIMILARITY(log_message, 'FEED_XYZ Secondary Instrument Lines not configured') > 80
      AND ROWNUM = 1
    )
SELECT log_message
FROM temp
;

However, unless there is a specific reason why you need to use UTL_MATCH.JARO_WINKLER_SIMILARITY, there's a chance you may be falling victim to the XY Problem. You could achieve identical results by replacing the two individual WHERE-clause lines (in either query above) with these:

WHERE log_message LIKE 'Unable to make thread %'
WHERE log_message LIKE 'FEED\_%' ESCAPE '\'

Or these (e.g. if there is a need for more fine-grained filtering):

WHERE REGEXP_LIKE(log_message, 'Unable\sto\smake\sthread\s\d+\sa\srealtime\sprocess')
WHERE REGEXP_LIKE(log_message, 'FEED_[A-Z]{3}\sSecondary\sInstrument\sLines\snot\sconfigured')


Connect windows 2000 to sql server 2016 I have a really old piece of industrial equipment that connects from windows professional to a sql server 2008r2 on win 2008R2, not completely patched.
We cannot replace the windows 2000, but would like to connect to a sql server 2016.
This is the connection string in the udl file
[oledb]
; Everything after this line is an OLE DB initstring
Provider=SQLOLEDB.1;Password=blabla;Persist Security Info=True;User ID=LVD;Initial Catalog=LVDLASER;Data Source=someserver
It works if I point to the old server.
If I change the servername, test it on a win 10 machine, it works.
On the win2000 I get this:

Test connection failed because of an error in initializing provider.
[DBNETLIB]ConnectionOpen (SECDoClientHandshake()).SSL Security error.
Any suggestion is very much appreciated.

Colin's suggestion fixed it. Installed windows installer 3.1 and found the native client 2005 somewhere and now it is ok.
Works also for sql server 2019 on windows 2019.


Database design - logging of same value when recorded in multiple tables I need to record changes to a particular value, which is read as part of multiple events that are all separately tracked, and be able to view a log of all of the values later.

I think an example will describe the problem better:
Let's say I'm designing a database for a rental car company.  The company has a list of all of their cars, and they want to track the odometer reading of each car as often as possible.  So, the company has the odometer read as part of a number of different events that are also recorded, such as renting, transfers, maintenance, etc.

The database might look something like this (note how OdometerReading is repeated):

Cars             Rentals             Transfers           Maintenance
====             =======             =========           ===========
Car_ID           Rental_ID           Transfer_ID         Maintenance_ID
SerialNumber     Car_ID              Car_ID              Car_ID
...              RentalDate          TransferDate        MaintenanceDate
                 OdometerReading     OfficeFrom          Notes
                 ...                 OfficeTo            OdometerReading
                                     OdometerReading     ...
                                     ...

Is this a good route to continue down? It seems odd to have the same field (OdometerReading) in different tables like this.

I need to be able to generate a report showing the history of the odometer readings over time.

I could write a query such as:

SELECT RentalDate, OdometerReading
FROM Rentals
UNION ALL
SELECT TransferDate, OdometerReading
FROM Transfers
UNION ALL
SELECT MaintenanceDate, OdometerReading
FROM Maintenance
ORDER BY RentalDate

Or, I could remove the OdometerReading field from all the tables and have an odometer readings table like this:

Rentals                 OdometerReadings
=======                 ================
Rental_ID               OdometerReading_ID
Car_ID                  Date
RentalDate              Mileage
OdometerReadings_ID
...

--(other tables removed for brevity)

This way, when viewing all the rental events, the odometer reading can still be included.

But this seems to give me other problems. The date is now recorded in two places (e.g., Rentals.RentalDate and OdometerReadings.Date), and it seems more difficult to determine where an odometer reading came from (although I believe you could left join the OdometerReadings table with potential source tables and filter where the right table is null).

What is a good route for recording the same value as part of a number of different records/events, each with their own table?

Another option is to use simple table inheritance for your events, with the event as the base type:

CarEvents              RentalEvents
=========              ============
ID <------------------ EventID
EventType         |    CustomerID
CarID             |    RentalDetail03
EventDate         |         ...
MilageReading     |    RentalDetailNN
GeneralDatail05   |
    ...           |
GeneralDatailNN   |    TransferEvents
                  |    ==============
                  `--- EventID
                  |    SourceOfficeID
                  |    DestinationOfficeID
                  |    TransferDetail04
                  |          ...
                  |    TransferDetailNN
                  |
                  |
                  |    MaintEvents
                  |    ==============
                  `--- EventID
                         ...

This means that queries about general properties (event date, car involved, etc.) only hit one table rather than needing to union the distinct sources, without duplicating the general details. Of course to report on a given event type you need to join in the relevant sub-table, and to report on multiple event types you have to join in multiple sub-tables.

Some databases have built in support for this form of inheritance which makes it behave a little more automatically (a bit like partitioning), meaning you have slightly less work to do as a developer, but this is essentially syntactic sugar and as you can see the basic structure is not at all rocket science.


mysql.gtid_executed table is empty I want to keep track of GTIDs and the corresponding mysqlbin log somewhere. I see this mysql.gtid_executed table, however on my mariadb DB, there are no rows in this table. Any idea how this gets populated ? These are the GTID related parameters. Any help is appreciated.
MariaDB [(none)]> show variables like '%gtid%';
+-------------------------+--------------+
| Variable_name           | Value        |
+-------------------------+--------------+
| gtid_binlog_pos         | 0-10001-4866 |
| gtid_binlog_state       | 0-10001-4866 |
| gtid_cleanup_batch_size | 64           |
| gtid_current_pos        | 0-10001-4866 |
| gtid_domain_id          | 0            |
| gtid_ignore_duplicates  | OFF          |
| gtid_pos_auto_engines   |              |
| gtid_seq_no             | 0            |
| gtid_slave_pos          | 0-10001-4866 |
| gtid_strict_mode        | ON           |
| last_gtid               |              |
| wsrep_gtid_domain_id    | 0            |
| wsrep_gtid_mode         | OFF          |
| wsrep_gtid_seq_no       | 0            |
+-------------------------+--------------+

gtid_executed table is from your former MySQL instance. It isn't used by MariaDB.
The global gtid%pos variables you have listed keep track of the gtid.


Reduce DB size - SQL Server, SSMS One of my databases was growing in size a few gigs every day. That was very unusual since it is still in development phase. After small research i discovered that the Even Log table was almost the size of the database (100gb) so i cleared it. I ran a script to check the table sizes in this database and everything went back to normal. That is, no ridiculously fat table. However, the general size of the database didn't change despite the diet to the problematic table.

Any suggestions?

BTW, I am using SQL SERVER 2012

thanks, David

I would be willing to bet that your database is full recovery and that you are not taking transaction log backups.  Log backups are required for the transaction log to clear and reuse space.  Otherwise it will just continue to grow and grow.


sp_Blitz script output I'm a very green DBA at my company, and ran the sp_Blitz script (on one of our test databases that is an exact copy of our production database), and I only get the message "Command(s) completed successfully.".

Is there a reason why I don't get a more detailed output?  Or is the output file written somewhere else?

As Denis Rubashkin said in a comment:

  If you had a look at the script text you would see that the script just creates a procedure sp_Blitz. You have to execute that proc (sample execution call with the most common parameters is in the bottom of the sp_Blitz script) to get an output you are waiting for.

You just created the procedure, but you still have to execute it. Here's some extra information if you have more questions:

There's a bunch of good resources you can check to get more information about any of the blitz procedures.

If you like to read:
This is the official documentation on the procedure, you can probably find everything you need here: sp_blitz documentation

If you rather watch videos:
Brent has made some great YouTube videos where he goes through the usage of the of the Blitz procedures.

This one is for the SP_Blitz, but there are also some vids for SP_BlitzIndex / sp_Blitzfirst. (All very useful procedures)
Brent Ozars youtube


SQL Server - Physical Table Design - Column Order - Null and Non Null Columns We are in the process of building out a database for a new application.  I got some feedback from the architect that he read somewhere there is a memory utilization benefit for having all of the non-nullable columns in a table first, then all the nullable columns. If there are nullable columns in-between non-nullable columns that some kind of memory or storage benefit is forfeited because those nullable columns are between non-nullable columns.

The columns were ordered in a slightly ambiguous order but in general it was prioritized for columns which were more likely to be used first.

I was under the impression that SSMS orders the columns on the page file by its own logic instead of the specific order they are created on the table.  That the order I put the columns in on the table was totally independent from how the data was stored in the page file.

Is there any documentation somewhere that would support this finding/understanding?

EDIT:

Having some more conversations around figuring out where this idea came from.  The request was an over simplification of having high density of data, or columns used most frequently in SELECT or JOIN operations in the front of the table to prevent them from moving to the Overflow section of the page file.

That the order of the columns assigned to the table is the same order the data is added onto the page file.  For a given row, the data is stored on the page file in the order the columns are set on the table.  If the row holds more data than the page file allows (assuming we are not using anything that would go into a LOB IMAGE or VARCHAR(MAX) or something like that) then the remainder is put onto the overflow file.  If that overflow data is needed than there is extra effort and time spent doing the lookup to that overflow file.  That can result in noticeable impacts to performance in high row environments (10's or 100's of millions of rows +).  So we would want to prioritize the column order in how often we think that column will be accessed, not necessarily if it is NULL or not.

Does that understanding/analysis sound right?

I am posting this here as an answer so that I have more room to post resources and links, as well as explain a bit.

To answer the specific question about NULLABLE vs NON-NULLABLE columns and their relative cardinality, Kimberly Tripp has an article here: https://www.sqlskills.com/blogs/kimberly/column-order-doesnt-matter-generally-but-it-depends/

Unfortunately I cannot find the reference that supports my statement about having variable width columns at the end of the column order list, but the reasoning was that updating a variable width column at the end of a row would be less likely to cause fragmentation (and off-page spill) than one in the middle of the column order.

But as I've previously said, these are hard to get right and unless you know exactly your data access patterns you will probably get it wrong.  There are many other low hanging fruit to tune over worrying about column order.  Switching to properly sized static column lengths would be an easier way to get better memory estimates and avoid fragmentation.

Indexes with proper fill factors, statistic updates, stored procedures for data access (so you can more tightly control execution plans) and more.  If you are on enterprise edition then you have more options, table compression will allow you to fit more data into memory and that's a far easier (and more widely applicable), partitioning will allow you to do really cool things on management side (although using it to accelerate your reads is a challenge (partition elimination is another hard thing to get right)).

Note, when I say hard to get right, I usually mean that it's hard to get SQL to use them to best advantage, so getting ROI for time spent optimizing is usually much better spent elsewhere.

To answer the last section of the question in the EDIT section.  Yes, pre-loading the column with primary keys, foreign keys and the other join columns would probably help.   But if you have indexes on those then it shouldn't matter as the index will be used to determine which rows to retrieve and then a keylookup performed.  I just don't think spending architecture time on it is worth the impact.  You are probably going to have an index on those columns in any case.


Oracle database startup fails several times. Giving ksvcreate Last night my production server database (ORACLE 10.2.04) was showing me number of connection exceeded as some times it show me. Then as usual I shut down abort my database then at the time database start-up it just stays blank and writing no error log and alert log after 30 min I abort the shut down by CTRL+c and issue the start up process once again then in alert.log file I found the ksvcreate: Process() creation failed this error but my database failed to start up please help me out why it is happening and any solution for it or nay parameter check for it.

Did anything on that server change recently?
What OS/version?
What hardware sizing?
What processes are on it?
How much memory is allocated for the database[s]? (sum sga & pga)

Normally this error has to do with a lack of resources. What else is running on this server that consumes memory? There is a good chance that due to lack of memory your server is swapping and doing so, it takes too long to start processes.

Monitor your memory usage.

If your application does bulk updates, without limits, this could easily fill your entire memory. See Bulk Processing with BULK COLLECT and FORALL for a good explanation. If your data set is growing over time, surprises are waiting to happen. Use limits in the pl/sql code.

Shutting down a database because there are performance problems is not exactly a solution for a problem, it is the denial of a real existing problem. Normally a database is online and available, a solid piece of foundation for an application. If your database/data has any value for your company, hire a dba to help you evaluate your systems.


Syntax check for creating logins in different SQL versions We are trying to write a script to do the following task:

Create a new login and grant datareader to the master database on all SQL Server 2012 and 2014.

The script we have did what we want but for some reason, SSMS is complaining about the syntax saying the user already exists but it doesn't exist.

    use master
    go
    IF EXISTS
    (select * from sys.syslogins
    where loginname =  'login_name')
    (select 1 from sys.syslogins)
    else

    if
    (select  left(cast(SERVERPROPERTY('ProductVersion') as varchar),2)) = '12'
    CREATE LOGIN [login_name] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
    CREATE USER [login_name] FOR LOGIN [login_name]
    ALTER ROLE [db_datareader] ADD MEMBER [login_name]

    if
    (select  left(cast(SERVERPROPERTY('ProductVersion') as varchar),2)) = '11'
    CREATE LOGIN [login_name] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
    CREATE USER [login_name] FOR LOGIN [login_name]
    ALTER ROLE [db_datareader] ADD MEMBER [login_name]

The trouble here is that the script evaluates the IF statement, and then the logic applies to the first statement afterwards. To handle multiple statement you need to put them within a BEGIN...END block.

Something like this should work for you

  USE master
    GO
    IF NOT EXISTS ( SELECT  *
                    FROM    sys.syslogins
                    WHERE   loginname = 'login_name' )
        BEGIN
            IF ( SELECT LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR),
                             2)
               ) = '12'
                BEGIN
                    CREATE LOGIN [login_name] FROM WINDOWS WITH DEFAULT_DATABASE=[master];
                    CREATE USER [login_name] FOR LOGIN [login_name];
                    ALTER ROLE [db_datareader] ADD MEMBER [login_name];
                END
            ELSE
                IF ( SELECT LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR),
                                 2)
                   ) = '11'
                    BEGIN
                        CREATE LOGIN [login_name] FROM WINDOWS WITH DEFAULT_DATABASE=[master];
                        CREATE USER [login_name] FOR LOGIN [login_name];
                        ALTER ROLE [db_datareader] ADD MEMBER [login_name];
                    END
        END
    ELSE
        BEGIN
            SELECT  1;
        END


Oracle: Get the row count in a cursor when using a OPEN-FOR-USING statement Within an Oracle procedure, after opening a cursor for a select statement, I fail to find a mean to count the number of rows fetched.

OPEN mycursor FOR
SELECT * FROM TABLE;

-- mycursor%ROWCOUNT is always set to 0, even if the cursor has rows.
IF mycursor%ROWCOUNT = 0
THEN
  <error processing code here>
END IF;

This is expected, as documented at Oracles's documentation website:

  The OPEN-FOR-USING statement associates a cursor variable with a
  query, executes the query, identifies the result set, positions the
  cursor before the first row in the result set, then zeroes the
  rows-processed count kept by %ROWCOUNT.

So, except for running a second, redundant 'select count(*) from table', are there any other means to find out the number of rows within the cursor?

EDIT #1:  I am not looping after loading the cursor; it is returned as is to the calling procedure.  However, I must raise an exception if there are no row.  These are the specifications.

Do the ROWCOUNT check after looping through the set.  If ROWCOUNT will remain 0 if there were no data in the cursor.  You could use a FOR LOOP construct like:

OPEN ....

FOR ... IN mycursor
LOOP
    /* Process data here */
END LOOP:

IF mycursor%ROWCOUNT = 0 ...

CLOSE mycursor;


Delete from table where two of the three keys in another select I have tableA which has 4 columns:

IDA (bigint), IDB (int), IDC (varchar(255)) and comment (varchar(255)). The primary key is (IDA, IDB, IDC).

I'm required to delete the records where the count of (IDA, IDB) is above 1 and the comment is "temp". I've come up with the following SQL statement:

DELETE FROM tableA WHERE IDA, IDB IN
    (SELECT IDA, IDB FROM tableA  GROUP BY IDA, IDB HAVING COUNT(*) > 1)
 AND comment = 'temp'

But I get the following error:
"An expression of non-boolean type specified in a context where a condition is expected, near ','. [...]"

Working on Microsoft SQL Server Enterprise Edition v9.00.3042.00, i.e. SQL Server 2005 Service Pack 2

I'm rather new and appreciate any help/suggestions.

Your code is valid SQL but not in SQL-Server. They haven't yet implemented the syntax (a,b) IN ((1,11), (2,12), ...) or (a,b) IN (SELECT expa, expb....).

You can work around with either an EXISTS subquery:

DELETE FROM tableA AS a
WHERE EXISTS
      ( SELECT 1
        FROM tableA AS b
        WHERE b.IDA = a.IDA AND b.IDB = a.IDB
        HAVING COUNT(*) > 1
      )
  AND a.comment = 'temp' ;

or probably more efficiently with a join:

DELETE a
FROM tableA AS a
  JOIN
    ( SELECT
      FROM tableA
      GROUP BY IDA, IDB
      HAVING COUNT(*) > 1
   ) AS b
     ON  b.IDA = a.IDA AND b.IDB = a.IDB
WHERE a.comment = 'temp' ;

and even better with a derived table or a cte (no self-join):

WITH dups AS
  ( SELECT comment,
           cnt = COUNT(*) OVER (PARTITION BY IDA, IDB)
     FROM tableA
  )
DELETE
FROM dups
WHERE comment = 'temp'
  AND cnt > 1 ;


How to differentiate between "no child rows exist" and "no parent row exists" in one SELECT query? Say I have a table C that references rows from tables A and B:

id, a_id, b_id, ...

and a simple query:

SELECT * FROM C WHERE a_id=X AND b_id=Y

I would like to differentiate between the following cases:

No row exists in A where id = X
No row exists in B where id = Y
Both such rows in A and B exist, but no rows in C exist where a_id = X and b_id = Y

The above query will return empty result in all those cases.

In case of one parent table I could do a LEFT JOIN like:

SELECT * FROM A LEFT JOIN C ON a.id = c.a_id WHERE c.a_id = X

and then check if the result is empty (no row in A exists), has one row with NULL c.id (row in A exists, but no rows in C exist) or 1+ rows with non-NULL c.id (row in A exists and at least one row in C exists). A bit messy but it works, but I was wondering if there is a better way of doing this, especially if there is more than one parent table?

I think I managed to get a satisfactory solution using the following two features:

Subselect bound to a column, which allows me to check if a row exists and (importantly) get a NULL value otherwise (e.g. SELECT (SELECT id FROM a WHERE id = 1) as a_id))
Common Table Expressions

Initial data:

CREATE TABLE people
(
  id integer not null primary key,
  name text not null
);

CREATE TABLE thing_types
(
  id integer not null primary key,
  name text not null
);

CREATE TABLE things
(
  id integer not null primary key,
  person_id integer not null references people(id),
  thing_type_id integer not null references thing_types(id),
  name text not null
);

INSERT INTO people VALUES (1, 'Bill');
INSERT INTO thing_types VALUES (1, 'game');

INSERT INTO things VALUES (1, 1, 1, 'Duke Nukem');
INSERT INTO things VALUES (2, 1, 1, 'Warcraft 2');

And the query:

WITH v AS (
  SELECT (SELECT id FROM people WHERE id=<person_id_param>) AS person_id,
         (SELECT id FROM thing_types WHERE id=<thing_type_param>) AS thing_type_id
)
SELECT v.person_id, v.thing_type_id, things.name
FROM
  v LEFT JOIN things
    ON v.person_id = things.person_id AND v.thing_type_id = things.thing_type_id

This query will always return at least one row. In case if both parent table ids are valid and there are some records, I get

person_id  thing_type_id  name
-------------------------------------
        1              1   Duke Nukem
        1              1   Warcraft 2

If either person_id or thing_type_id are invalid, I get one row where name is NULL and either person_id or thing_type_id is NULL:

person_id  thing_type_id  name
-------------------------------------
     NULL              1         NULL

If both person_id and thing_type_id are valid but there are no records in things, I get one row where both person_id and thing_type_id are not NULL, but the name is NULL:

person_id  thing_type_id  name
-------------------------------------
        1              1         NULL

Since I have a NOT NULL constraint on things.name, I know that this case can only mean that there are no matching records in things.


How bad is a tempdb drive failure (with multiple tempdb files on different drives)? I'm getting a new 16 core server for my SQL Server 2005 database. I would like to create 16 files for my temp DB, and put each one on a separate drive. If one of these drives fails, can SQL Server continue to function using the other 15 files?

Nope, I'm fairly confident SQL Server will fall over - I've had a few SAN attached drives disappear with SQL Server 2005. It's possible that SQL Server may not realise until it accesses TempDB on the drive that's failed.... in pre-prod, you should be able to simulate this by dropping the drive.

Unless you've carefully assessed this, I would also suggest you carefully reconsider your 16 TempDB datafile approach. The smart advice these days is to add TempDB datafiles only when you see TempDB contention (wait stats etc). If you really want to, start with 2 datafiles on the same drive.

The most recent TempDB recommendation I saw - it's Paul Randal, so essentially Mr SQL Server himself:
http://www.sqlskills.com/BLOGS/PAUL/post/A-SQL-Server-DBA-myth-a-day-(1230)-tempdb-should-always-have-one-data-file-per-processor-core.aspx


Copying a restoring database in SQL Server 2012 I've got two SQL Servers, one of these servers (Server A) is backing up transaction logs on some database and uploading them to the other (Server B). Unfortunately I have no access to Server A, I simply have to trust that it is doing its job of periodically uploading its transaction logs to Server B (via something like FTP).

Now, suppose Server B needs to recover the database to do some reading and writing. Doing this will break its ability to receive further transaction log backups.

Is there any way to copy/branch/backup the restoring database, so I can have one version of it that will continue to apply the transaction logs, and one version that will be recovered for reading/writing?

If you want to (1) maintain your log shipping environment and (2) want to have a read/write copy occassionally you will need to maintain 2 databases, TargetDB and Target2DB.

Question, could you just restore the backup from the Server A SourceDB to Server B Target2DB?  That might take a little longer, but there are fewer moving parts running all the time.

If read/only access would be sufficient, you can create a database snapshot for reading purposes. This creates a point in time snapshot of the database, supported by the .SNP file that maintains the needed data to support the snapshot, despite other changes coming in.

CREATE DATABASE TargetDB_Read
  ON (NAME = 'datafile', FILENAME = 'F:\TargetDB_Read.SNP')
  AS SNAPSHOT OF TargetDB

Note: You can create several snapshots if needed at different points in time. In that case, name the snapshot for the time it is created.  E.g. TargetDB_0800.)  See:
http://technet.microsoft.com/en-us/library/ms175876.aspx

Of course if you bring Target2DB online so as to do Read/Write, once you are done you will need to restore a Full backup of the SourceDB as the new Target2DB, then catch up with changes through restoring Differential and Log backups until you are once again ready for log shipping.


Are there any risks on using 'single-transaction' and 'extended-insert' on mysqldump? I'm new in db administration and I've looked for a faster import of a mysqldump.

I had a db with about 1.1 million rows and mostly InnoDB tables and wanted this to import on a managed server. Before I used the standard mysqldump command for dumping the db. At import I had a write speed of about 60.000 rows per hour which was extremly slow.

I searched for a way to speed thingsup and found a post here saying to use --single-transaction and --extended-insert on mysqldump. Now the dump is importing in under 30 seconds.

After a quick overview it seems that the data is correctly imported but I'm a little bit leery about the fast import.

Why is this soo fast after adding these two options?
Are there any disadvantages on using this options?

Thank you for your help.

this way is faster because

Server does not need to perform fsync() multiple times during data import. fsync is the operation of submitting data into disk
Extended insert enables sequential write to disk which means disk does not need to seek for a locations for every row.
If the table has Auto_Increment values, Server can spare adequate values for the batch insertion at once rather than once for every insertion.
The extended insert builds batch INSERT statements (many rows per INSERT). That inherently speeds things up 10-fold because of amortizing the statement overhead (Added By @Rick James in comments)

I know two side effects for this method.

undo log will get too large while performing this operation.
If an error happen during the operation, the entire import will be reverted (which might be an advantage in your case).


Add shared folder to AlwaysOn AG Failover We have setup a 2 node Synchronous AG with automatic failover to provide HA for our ERP application (Dynamics GP)

The GP desktop client loads a number of shared reports and dictionaries from a file share currently located on one of the database servers.  In order to provide complete high availability this share needs to be available in the event of a failure of the primary replica.

At first I thought I would just copy the folder and setup shares on the secondary replica but realized SMB is not accessible through the AG listener.

My second thought was to move the share to another location however this still introduces a single point of failure and defeats the purpose of HA.

My only 2 real choices are to somehow add the folder as a resource to the cluster, however the 2 nodes are virtual machines and creating a shared SAN disk would be complex

Or a messier option would be to create a task that checks the server name and in the event of a failover would update DNS to direct clients via UNC to the correct server share.

What would be the recommended approach for this?

The recommended way to do this is the way you don't want to do it--by creating a shared disk and the necessary cluster resources for a file share.

Other cluster options would require some sort of replication functionality.  Carbonite  Availability GeoCluster will allow you to create software replicated disk resource to accomplish this, and there are some other products that will do the same thing.  However, using third-party products introduces some risk because if there is a defect your cluster group may fail over or even fail to come online.  Accordingly, I personally would not opt for one of these solutions.


Minimum/Maximum Server Memory in SQL Server 2005 with /3GB and /Userva switches I have a 32-Bit Windows 2003 machine with 3.75GB RAM running one SQL Server 2005 instance. I am trying to optimize memory. If I use the switches in boot.ini like below,

/3GB /Userva=2490

should I set the min and max server memory on the SQL Server instance to that 2490, or less than that? (2490 = 3840MB available - 1350MB for Windows)

I suppose the real question here is "what are you trying to solve". At best you are only going to get a few hundred extra MB into your buffer pool. If you are doing this to solve a performance problem then you should make certain your problem is related to memory and not something else, like network.

Are you running Win2003 Enterprise, or Standard?

If Enterprise, you should look at using /PAE along with AWE to squeeze out some extra RAM.

If you are not running Enterprise, then I believe the /3GB and /USERVA switches are recommended by MS, but like I said before this is only going to gain you a few hundred extra MB at best.

The /3GB switch reduces the number of Page Table Entries (PTEs) for your O/S. So, if you are already seeing paging on your server, if you use /3GB you are likely to see more as a result.

Lastly, MS does not recommend using the /USERVA switch for values under 2900, so you may find yourself with a server in an unsupported state. Then again, it is Win2003, so it is just about out of support anyway, so that may not be an issue for you.


PostgreSQL 8.4 hangs on create table, reindex I have a rather small database(200Mb) which was running on ubuntu 10.10/amazon ec2 fine for a year almost but today I ran into the following problem:
When I create a table that references other table like:

    CREATE TABLE "limit" (
        "id" serial NOT NULL PRIMARY KEY,
        "currency_id" integer NOT NULL UNIQUE REFERENCES "currency" ("id")
DEFERRABLE INITIALLY DEFERRED,
        "min_amount" numeric(10, 2) NOT NULL,
        "max_amount" numeric(10, 2) NOT NULL
    )

The thread hangs and blocks all other threads eventually,(but I can still terminate it with kill -INT) though the other table (currency) is rather small like 24 entries. Sources out there say that I should reindex my table but then again, running

REINDEX table currency

hangs forever.
BTW creating the table without index, works like a charm.
What can be the possible explanation and solution to this?

if it is hanging forever, without significant CPU or IO activity, it is waiting on a lock.  The two things I would try here would be checking pg_stat_activity to see if something else is running that might conflict.  Maybe someone else has an open transaction in another window that has conflicting locks?  Then I would check pg_locks.

Then I would look at pg_terminate_backend() on the offending process and try again.

However create table is a weird one.  I can't think, off-hand, of any case where this should be blocked.  Wondering what kinds of locks are required for this and under what circumstances they can conflict.

Edit:  It occurs to me that the locking could be lower level than Pg-level locks and involve semaphores against shmem segments.  But if that's the case your best bet is to take this up with Amazon since it would be more of an OS issue.