SET IDENTITY_INSERT inside INSTEAD OF INSERT trigger not working I'm trying to perform a indentity insert inside a instead of insert trigger, but when I try to insert data into the data, it throws and error msg "Msg 544, Level 16, State 1, Line 36
Cannot insert explicit value for identity column in table 'PixTest' when IDENTITY_INSERT is set to OFF.".

I'm using SQL SERVER 2014 Developer Edition 64 Bit installed on Windows 8 64 bit.

Here is some sample code that might help:

CREATE TABLE PixTest
(
      ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
      TESTE VARCHAR(10)
)
GO

CREATE TRIGGER AutoIncrement_Trigger ON PixTest
instead OF INSERT AS
BEGIN

    DECLARE @ID INT;
    SELECT @ID=ID FROM inserted;

    IF COALESCE(@ID,0)<> 0
        BEGIN

            SET IDENTITY_INSERT PixTest ON
            INSERT INTO PixTest(ID, TESTE)
            SELECT ID,TESTE FROM inserted
            SET IDENTITY_INSERT PixTest off

        END

    ELSE

        INSERT INTO PixTest(Teste)
        SELECT TESTE FROM inserted;

END;
go

INSERT INTO PixTest(ID, TESTE) VALUES (1, 'TESTE 1')
GO

I googled about using SET IDENTITY_INSERT inside of a trigger, and couldn't find anything saying it is not possible, but the code above doesnt seem to work.

Any ideas?

The error isn't coming from the trigger.  It's coming from your insert statement.

INSERT INTO PixTest(ID, TESTE) VALUES (1, 'TESTE 1')

It's getting parsed and returning an error before you ever get to the trigger.  Change it to this and it works.

INSERT INTO PixTest(TESTE) VALUES ('TESTE 1')

Executed this script :

DBCC CHECKIDENT (PixTest, NORESEED)

Result says Identity value is NULL.

  Checking identity information: current identity value 'NULL', current
  column value 'NULL'. DBCC execution completed. If DBCC printed error
  messages, contact your system administrator.

Then we believe you need to follow either of the other two ways stated by others above.


How does a Page in SQL Server distinguishes between the data of different columns of a table? I know that row offset array at the end of page stores information about the starting index of any row. Does it also store information about column data? And where does it store? If not, how does it identify the different column data?

Column meta-data are stored in system catalog tables. These are held separately from the physical record.  The meta-data can be queried using the sys.columns catalog view. Records within a data page are a structure that includes a null bitmap, fixed-length fields, variable-length fields, and a column offset array entry for each variable length field present in the record. See Paul Randal's Anatomy of a Record for specifics of the internal data record structure. The physical record structure is significantly different when PAGE or ROW compression is used, for BLOBs (varchar(max) et al), and when a clustered columnstore index exists.

SQL Server uses the schema defined by the column meta-data to determine how to interpret the physical record structure and return the requested column values from the fields in the record. The physical record may or may not contain fields for each column, depending on whether the data type is fixed-length or variable, a NULL value, or columns added after initial table creation.

For example, if one adds a varchar NULL column to an existing table, that is a meta-data only operation that doesn't modify existing physical records. Similarly, adding a NOT NULL column with a default constraint is a meta-data only operation in Enterprise Edition because SQL Server uses meta-data to return the default value for existing rows even though the value is not physically present in records until later modified.

Records are stored in pages. They're stored in a FixedVar format, variable-length columns are stored at the end. The pages themselves are tied sysallocunits which is tied to sysrowsets which is tied sysschobjs which is tied to syscolpairs for schema.

Definitely check out DBCC_PAGE and for more information, check our

Mark S. Rasmussen - Revealing the SQL Server Magic

All of the information was grabbed from that vid. Seen it a few times now. Haven't played with it yet.


Create a MySQL database with charset UTF-8 I'm new to MySQL and I would like to know:

How can I create a database with charset utf-8 like I did in navicat?

create mydatabase;

...seems to be using some kind of default charset.

Update in 2019-10-29
As mentions by @Manuel Jordan in comments, utf8mb4_0900_ai_ci is the new default in MySQL 8.0, so the following is now again a better practice:

CREATE DATABASE mydatabase CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

Answer before 2019-10-29
Note: The following is now considered a better practice (see bikeman868's answer):

CREATE DATABASE mydatabase CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Original answer:

Try this:

CREATE DATABASE mydatabase CHARACTER SET utf8 COLLATE utf8_general_ci;

For more information, see Database Character Set and Collation in the MySQL Reference Manual.

You should use:

CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Note that utf8_general_ci is no longer recommended best practice. See the related Q & A:

What's the difference between utf8_general_ci and utf8_unicode_ci on Stack Overflow.


Why is CDC capture failing due to a missing dbo principal I am having a problem running the CDC change capture job against some of my test databases.

The error message I'm getting is the following:

  Executed as user: NT AUTHORITY\NETWORK SERVICE. Cannot execute as the database principal because the principal "dbo" does not exist, this type of principal cannot be impersonated, or you do not have permission. [SQLSTATE 42000] (Error 15517).

How can I fix this?

dbo is mapped to an invalid login. Re-map it to a valid one:

ALTER AUTHORIZATION ON DATABASE::[<dbname>] TO [sa];

Under Security, add the principal as a "SQL user without login", make it own the schema with the same name as the principal and then in Membership make it db_owner.


What should I ask my DBA in order to diagnose a slow application? I am working in the IT department of an industrial firm. The IT department is a handful of people, mainly project managers, and everything else is outsourced.

I have been managing a project, where a third party application has been bought off the shelf, and deployed by the vendor, but to be maintained on our own.

It consists of a central application server, connected to an SQL Server database, and a dozen heavy clients, all on our premises, on the same VLAN.

Most of the time, it runs fine, however, sometimes, when accessing records from the DB, the application is awfully slow.

I have been asking our DBA (outsourced) what is wrong, but have been responded by something along the lines of "Please ask me to do something with the DB, and I'll gracefully do it".

Hence the question, in a situation of sporadic sluggishness like this, which technical actions should I ask of my DBA to make progress on the case?

Please monitor the performance of the DB is apparently too vague, and they expect something along the lines of 'Enable this performance counter for that long' and so on...

While a good DBA can find out a lot on his own, it never hurts to give a lot of information.

Which application you're using. If you know server/db ect give that as well.
Test/Prod
When the issue occurs, eg. I open this form and it's slow, or when I do this...
What's slow and what's fast. For some applications it can be perfectly acceptable to wait 1-2 seconds, while others have to perform near instantly. It can often be really easy to get a first bump in performance, but after that it takes a lot longer to make it just a little bit faster
Which login you're using

Especially if he's external + perhaps a junior it never hurts to just give him all you've got.

After that it's mostly up to the DBA to setup monitoring.

Log WhoIsActive to a table
Use the build in query store if version is 2016 or higher
Buy a third party tool, eg. SentryOne/Red gate monitor

After he setup monitoring you could also mail him "I'm having issues right now, could you see if you notice anything special?" Do not expect him to reply right away, but it gives him a window that he can check later where he knows the issue occurred.

First thing you can do is figure out on your end what specifically causes this to kick off the slowness.

Is the number of clients
Is running a reports from multiple clients
Is it a network issue if the SQL server is in the cloud....
etc....

Armed with that information you can then tell the remote DBA  monitor the database and we will cause the slowness

Read over this link  about finding slow queries
https://serverfault.com/questions/182665/does-mssql-server-have-the-equivalent-of-mysqls-query-logs


Oracle shrinking / reclaiming free tablespace space I have a Oracle 12c tablespace with the size of 90GB. Almost 40GB are free.

TB1 54 %    90880   41383   49497   46  98304   50 %    ONLINE  PERMANENT

The tablespace has 3 datafiles.

E:\ORACLE\ORADATA\xx\TB1.DBF    33554416    True    100
F:\ORACLE\ORADATA\xx\TB1_2.DBF  33554416    True    100
G:\ORACLE\ORADATA\xx\TB1_3.DBF  25344000    True    100

How can I shrink the tablespace to reclaim the free 40GB (datafiles on disk)? I can not have a downtime.

I agree with EdStevens' comment, but here are some other ideas:
The AskTom website  has a script that will list exactly how much you can shrink the existing data files.
If that doesn't yield much savings, you can find the biggest indexes on that tablespace and rebuild them; that is a temporary solution and the indexes would eventually get big again, but if budget doesn't allow more disk space now, try this query:
select segment_name, sum(bytes)/1024/1024 as mb
from dba_segments
where tablespace_name='TB1' and segment_type like 'INDEX%'
group by segment_name
order by mb;

Then if you find an index named  ind_big_stuff is huge, then
alter index ind_big_stuff rebuild online;

Also, if your application is a commercial application, there may be several tables with zero rows.  Or if you have partitioned tables, you may have some partitions with zero rows.  You can drop space allocated to them with
exec dbms_space_admin.drop_empty_segments(schema_name=>'HR');

You can use the segment creation deferred clause on an alter table statement for things like partitioned tables, or tables you know will not have any rows.

https://orahow.com/reclaim-unused-space-in-oracle/
Has 2 approaches for identifying and reclaiming space.


How a DBA can monitor multiple huge SQL Server databases more than 2 tera bytes I need a some suggestions from you people, I am not a DBA but moving to DBA platform.

I have some questions/clarifications can any body answer? Please.

We have almost 350 servers and more than 2500 database how can we monitor all those in a shot(interms is one DBA can handle or multiple DBA are there in a shift).
How can we monitor all those databases backups and performance.
If per Client individual how many production server will be there.
As an average a DBA how many databases can handle.
How will we provide security for Database?

Can any one answer these questions please? Thanks in advance

Lots of questions so many answers. It depends on so many factors. Budget being one.

First of all I would like to point out that visiting some basic SQL Server courses will vastly increase your understanding of SQL Server and its requirements. IMHO switching from Windows Server Administrator to SQL Server DBA is not just something that can be accomplished by querying Google or asking questions in a forum. You can acquire some knowledge, but without the full picture, you are liable to break something in a productive environment.

Do a search for SQL Server Monitoring Tools in Google. You should get a list of well known products back.
That depends on the monitoring software you purchase. Some can handle these requirements some not. There are also freeware tools available that could help with certain aspects.
Some monitoring products can handle up to 200-250 servers per instance depending on the load and the frequency of polling.
Brent Ozar has an article on this aspect: How many servers can one person manage?, and then again: it depends.
So many aspects here:

Don't hand out the sa password! Never!
Windows Server admins are not DBAs and should not have access to the SQL Server instance. (My opinion)
People outside of the Server or DBA team should not have access to backup drives or to SQL Server database file locations.
Only a qualified DBA should be allowed to be have sysadmin server role
Limit each SQL Login/NT Login to the least possible number of databases.
Limit the SQL Login to different roles depending on usage

DB_NAME_User : db_datareader, db_datawriter
DB_NAME_Dbo : db_owner

Try Transparent Database Encryption
Try Database Backup Encryption

Security depends on the requirements of your business. Ask the head of IT for SLAs or OLAs and any company-wide security policies, etc.

One question you haven't yet asked is: Availability. Will you be required to have Always on Availability Groups?

You asked a lot of questions, I hope you have a general starting point.

As @hot2use said, this is a huge topic. I'm not going to answer but instead make a suggestion. Monitoring several databases such as you describe is a full time job, and this is what I'm doing currently in my firm. So again, I'm not answering (and hope not to be reward with bad points) but making a suggestion. That is, maybe you would be interested in a live monitoring solution that we can provide. Here's what it looks like:

Alerts are raised on customized thresholds on backup, uptime, users, high availability etc.


Page compression not working? I have created a table where I have around 100 partitions (per month), all of them are configured to use page compression. After that I'm inserting data to this table month by month.

I now would expect SQL Server to apply the page compression. However using sp_estimate_data_compression_savings it doesn't look like it. (I would have savings of around 40%-50%.)

Did I miss something in configuring page compression?

The problem with this is that I have now large files, 70-80 GB per partition. When I compress them, they are half empty. Regaining this disk space is impossible because if I try to shrink the data file that is behind the partition I end up with a fragmented clustered index.

How can I avoid this disk space waste party?

If you're using big fields like VARCHAR(MAX), you'll discover that off-page data isn't compressed with page compression. (Hey, go figure.)

To see how much data of your table is being stored off-page, use the free open source script sp_BlitzIndex (disclaimer: I'm a coauthor on that). You can run it at the table level like this:

EXEC sp_BlitzIndex @DatabaseName = 'mydb', @SchemaName = 'dbo', @TableName = 'whywontthiscompress'

Then, in the top result set where it lists the indexes, look at the amount of LOB data stored on the table. If it's a lot, that's probably why compression isn't working. (And you can post a screenshot of the top result set in your question if you'd like more help/clarifications on this.)

From Page Compression Implementation:

However, the metadata for the table indicates that page compression should be used. As data is added to the first data page, data is row-compressed. Because the page is not full, no benefit is gained from page compression. When the page is full, the next row to be added initiates the page compression operation. The whole page is reviewed; each column is evaluated for prefix compression, and then all columns are evaluated for dictionary compression.

Look on the below link

http://blog.sqlauthority.com/2010/03/01/sql-server-data-and-page-compressions-data-storage-and-io-improvement/


Postgres 9.4 wal_level=logical increases disk space I am trying to use the AWS Database Migration Service to migrate data from an RDS Postgres instance to an Aurora MySQL instance.

Database Migration Service requires the wal_level to be set as logical in the source Postgres DB. But when done, Postgres started consuming a lot of disk space to the point where the instance became unusable.

How long are the WAL logs retained? Are there other settings that can be enabled so disk space use is limited?

This is an AWS DMS issue.

DMS has recently added a feature WAL heartbeat [1] (run dummy queries) for replication from a PostgreSQL source so idle logical replication slots do not hold on to old WAL logs which may result in storage full situations on the source. This heartbeat keeps restart_lsn moving and prevents storage full scenarios.

Login to DMS Console.
Stop all the tasks related to the source endpoint
Click on "Endpoints" from left side selection pane.
Select the source Endpoint which you are using, as per the task.
Click on "Modify" from the top of the screen.
Expand "Advanced".
Under extra connection attribute, please add this:

heartbeatenable=Y;heartbeatFrequency=1

HeartbeatEnable – set to true (default is false)
HeartbeatSchema – schema for heartbeat artifacts (default is public)
HeartbeatFrequency – heartbeat frequency in minutes (default is 5 and the minimum value is 1)

Click on "Modify". You will be automatically redirected to the Endpoints screen.
Select the endpoint again and click on "Test Connection".
It should automatically start the testing. Wait for it to show "Successful".

Stopping the task alone will not clear replication slot, so the storage usage will still increase when the task is in the stopped state, you will need to delete task to clear the slot.

To clear up the replication slots use the following commands -

SELECT * FROM pg_replication_slots ; --finds all replication slots
select pg_drop_replication_slot('NameOfSlot'); -- you will get the name of the slot from the first command

References:
[1] PostgreSQL Source WAL Heartbeat
https://docs.aws.amazon.com/dms/latest/userguide/CHAP_ReleaseNotes.html#CHAP_ReleaseNotes.DMS230

To check on the replication slots of AWS RDS instance, this provides the storage held by the replication_slots(in MB):

SELECT pg_database.datname as "database_name", pg_database_size(pg_database.datname)/1024/1024 AS size_in_mb FROM pg_database WHERE datname != 'rdsadmin' ORDER by size_in_mb DESC;


The problem might be a result of how you are defining your users.  I don't think the wildcards are working as you're expecting them to. The best way to figure this out, in my opinion, is to create a new user and password for the 18.61.10.64 host on the yoda.er.com MySQL Server.

Keep in mind that when logging in locally, the MySQL client on your server might be using the unix socket to authenticate.

mysql> show variables like '%socket%';
+---------------+-----------------------------+
| Variable_name | Value                       |
+---------------+-----------------------------+
| socket        | /var/run/mysqld/mysqld.sock |
+---------------+-----------------------------+

Connecting to MySQL

If this is the case then a password isn't necessary when logging in locally onto the MySQL server.

Could you try this from the machine you are trying to connect to. Use a new user and password combination because we are not sure how many different entries you might have for your power user.

GRANT ALL PRIVILEGES ON *.* TO 'someuser'@'18.61.10.64' IDENTIFIED BY PASSWORD 'somepassword';

FLUSH PRIVILEGES;

Then from the shell on 18.61.10.64

mysql -u someuser -h ip.address.for.r2d2.er.com -psomepassword

Check if server is listening on port 3306 which is the default port. Check if bind-address option is enabled in my.cnf and disable it. Lastly, check firewall on the server.


How to store mp3 files in MySQL database using phpMyAdmin? I am new to databases, but I want a database for my site to store mp3 files in it, so later I play them on my site by using jplayer.

They say there are two ways to store data (in this case mp3 files) in the database:

By storing actual file itself in the database.
By only storing particular file URL, address, location or metadata, etc.

My question is what is the right way and how can I do that in phpMyAdmin?

In my personal opinion (however I am new to databases as well), I would look to store the relative URL,  which would be the VARCHAR from here you could store the relative path to the file (from where the script is being called from)  and load the file that way.

However it may be worth writing the SQL for your tables as well.  Just in case you need to alter or tweak your table structure in the beginning it makes it simpler to alter.  so it would look something like this:

create database music;

use music;

create table tbl_album(
    album_id int(5) not null auto_increment,
    album_name varchar(50)  not null,
    album_artist varchar(50) not null,
    album_genre varchar(50),
    ablum_year varchar(4),
    primary key(album_id)
);

create table tbl_tracks(
    track_id int(5) not null auto_increment,
    track_number int(2),
    track_name varchar(50) not null,
    album_id int(5),
    track_url varchar(100) not null,
    primary key(track_id),
    foreign key (album_id) references tbl_album(album_id)
);

PS: If you do choose to write the SQL for your tables I would recommend MySQL workbench which is free and a nice tool to use (http://mysqlworkbench.org/)

if(isset($_REQUEST['Image_Submit']))
{
        $errors= array();
        $price = $_POST['Image_Price'];
        $category = $_POST['Image_Category'];
        $cid = $_POST['cid'];
        $file_name = $_FILES["Select_Image"]["name"];
        $file_tmp = $_FILES["Select_Image"]["tmp_name"];
        $file_size = $_FILES["Select_Image"]["size"];
        $file_type = $_FILES["Select_Image"]["type"];
        $file_ext=strtolower(end(explode('.',$_FILES['Select_Image']['name'])));
        $path = "upload/".$file_name;

        $expensions= array("jpeg","jpg","png","mp3");

        if(in_array($file_ext,$expensions)=== false)
        {
            $errors[]="extension not allowed, please choose a JPEG or PNG file.";
        }

        if($file_size > 10485760)
        {
            $errors[]='File size must be excately 10 MB';
        }

        if(empty($errors)==true)
        {
            move_uploaded_file($file_tmp,"upload/".$file_name);
            echo "Success";
        }
        else
        {
            print_r($errors);
        }

        $table = $wpdb->prefix."image";
        $data = array(
                    'image_price' => $price,
                    'cid' => $category,
                    'image_category' => $category,
                    'image_path'    => $path
        );

        $insert = $wpdb->insert($table, $data);
}


Close MongoDB dead connections I have a script that connects to my local mongos and do some processing. However, during the process, I encountered many cursor timeout exceptions, which (I suspected) left many dead connections on my server.

When I run mongostat on my host, I see 450+ connections:

insert  query update delete getmore command  vsize    res faults  netIn netOut  conn repl       time
     0      0      0      0       0       1   977m   195m      0    62b   726b   458  RTR   02:11:07
     0      0      0      0       0       1   977m   195m      0    62b   726b   458  RTR   02:11:08
     0      0      0      0       0       1   977m   195m      0    62b   726b   458  RTR   02:11:09
     0      0      0      0       0       1   977m   195m      0    62b   726b   458  RTR   02:11:10

How can I make sure that these are idle resources that I should free up? If they are, how can I free them?

This should be taken care programmatically. Programmers have to close the connection whenever they are done with dB access else memory leaks could happen.
I'm not sure how these processes can be killed as an admin.

If you are using Linux, type in the terminal:

sudo crontab -e

Add this line:

1 * * * * /usr/bin/service mongodb restart >/dev/null 2>&1

That will clear connections every hour on the first minute. If you want it to happen every 15 mins, replace 1 with 1/4 etc.


Issues installing MySQL server on Ubuntu 13.04 I'm currently trying to install a MySQL server on my Ubuntu 13.04 machine. The problem is, when I try to install it, I get error messages indicating that not all packages could be downloaded. When I run sudo apt-get install mysql-server, and after hanging for a while at reading headers, the console reads as follows:

Reading package lists... Done
Building dependency tree
Reading state information... Done
The following extra packages will be installed:
  libaio1 libdbd-mysql-perl libdbi-perl libhtml-template-perl libmysqlclient18 libnet-daemon-perl libplrpc-perl mysql-client-5.5 mysql-common mysql-server-5.5 mysql-server-core-5.5
Suggested packages:
  libipc-sharedcache-perl tinyca mailx
The following NEW packages will be installed:
  libaio1 libdbd-mysql-perl libdbi-perl libhtml-template-perl libmysqlclient18 libnet-daemon-perl libplrpc-perl mysql-client-5.5 mysql-common mysql-server mysql-server-5.5 mysql-server-core-5.5
0 upgraded, 12 newly installed, 0 to remove and 3 not upgraded.
Need to get 8,077 kB/24.5 MB of archives.
After this operation, 84.9 MB of additional disk space will be used.
Do you want to continue [Y/n]? y
Err http://us.archive.ubuntu.com/ubuntu/ raring-updates/main mysql-client-5.5 i386 5.5.32-0ubuntu0.13.04.1
  Connection failed [IP: 91.189.91.13 80]
Err http://security.ubuntu.com/ubuntu/ raring-security/main mysql-client-5.5 i386 5.5.32-0ubuntu0.13.04.1
  Connection failed [IP: 91.189.92.190 80]
Failed to fetch http://security.ubuntu.com/ubuntu/pool/main/m/mysql-5.5/mysql-client-5.5_5.5.32-0ubuntu0.13.04.1_i386.deb  Connection failed [IP: 91.189.92.190 80]
E: Unable to fetch some archives, maybe run apt-get update or try with --fix-missing?

I've tried the suggested fix or running apt-get update and such, I've made sure I have no connections blocked, I've restarted, uninstalled, reinstalled, etc, all to no avail. I've been searching the web all day for solutions I haven't yet tried, but most people with this issue are running an older version of Ubuntu. Suggestions?

Assuming this isn't an Internet connection issue ("Connection failed" is a needlessly obscure message), then this looks like a problem with the particular Ubuntu mirrors your system is trying to use, rather than something specific to MySQL.

The answer posted on What to try when apt-get install xxx Results in Connection Failed seems like it should get you pointed in the correct direction to resolve this.

It's also possible that sudo apt-get update might fix your problem now, even if it didn't fix your problem before, since some time has passed.  I'm speculating, but it could be that if the information fetched by the update operation causes your system behave differently when it's trying to fetch the files you're needing to install, that might also resolve the problem.

Please see this website as I think it may answer your question.

It may be a problem that you are missing sources.

If that doesn't solve your issue, try posting on that website (it's a part of stackexchange).


How to find out the maximum number of datafiles per tablespace in Oracle? According to this Oracle document, the maximum number of database files per tablespace is "Operating system dependent; usually 1022".

I know there are queries for some of the other limits mentioned in that document (e.g. to determine the database block size: select value from v$parameter where name='db_block_size').

Is there a query to determine the maximum number of datafiles per tablespace?  (I tried select name from v$parameter where value='1022' but that returned no rows.)

db block size is a database parameter but maximum number of tablespace or datafile is a physical limit and you can not find them in v$parameter view. You can use documentation for that: https://docs.oracle.com/cd/B28359_01/server.111/b28320/limits002.htm#i287915

select count(*) from dba_data_files where tablespace_name='TBSNAME';


Configure RAM for SQL Server and NUMA Accidental DB admin here.

Question:

Would you still manually limit max RAM for SQL Server even though the sole purpose of that server is to serve SQL Server database engine only?

I have Microsoft Windows 2012 R2 Server, SQL Server 2012 Std, with 32GB RAM.

Admin constantly login to the SQL Server to use its console and all.

Secondly, how do you check if this Windows server is NUMA enabled?

Would you still manually limit max RAM for MSSQL eventhough the sole purpose of that server is to serve MSSQL only?

That is bit debatable topic considering you have SQL Server 2012 here. If you would have asked same question about SQL Server 2005/2008, which was on Windows Server 2003/2008, I would strongly suggest to set a max server memory limit due to bugs which were there in Windows operating system(2003/2008). But memory configuration and OS performance changed significantly from SQL Server 2012 onwards and Window Server 2008/2012 r2 onward respectively, if you ask my opinion, You can leave max server memory configuration to default if you have enough RAM on system and you are just using database engine features. If you ask MS engineers about this they would also say that SQL Server is coded well to manage memory by itself and if you have system dedicated just to SQL database engine you can leave SQL Server max server memory to default SQL Server will manage it efficiently.

Anyways if you still want to configure you can read Similar question and answers given On this SE Thread

There are situation where max server memory is required I have outlined that in This SE thread

  Secondly, how do you check if this Windows server is NUMA enabled?

A little search would lead you to this Blogs.msdn article. It has various pictorial description when SQL Server is NUMA aware and is using it.

Yes, you should limit the max memory for SQL Server even though the server has only one purpose. Otherwise SQL Server may start using memory Windows itself needs to operate well. It is usual to leave 1GB -> 20% of the total RAM to Windows, depending on your needs.

The NUMA configuration of the server is displayed in Task Manager by selecting a process, right-click and select Set Affinity. The Windows Resource Monitor also displays the NUMA information.


NVMe+bcache as huge storage cache for vertica database We are in process of selecting hardware for 3 machines vertica cluster capable to serve 20-30TB of data (15TB compressed). This translates to 8TB of post-RAID storage. With 16 CPU cores, the storage should yield 800MB/s of "large block random I/O" throughput.

Cost of the storage becomes pretty high, once we count in drives, RAID overhead and server chassis necessary to hold all the drives. 10k RPM drives are relatively small (SAS 10k RPM tops at 1.2TB max) and we need many of them to provide the total storage required. SSD are four times more expensive with SATA/SAS becoming the bottleneck.

I have been thinking about "hybrid" solution of using super-fast NVMe storage (2GB/s thoughput) as cache for a much more slower HDD backend (couple of 10TB SATA drives in RAID1). This can be accomplished by using Bcache technology of linux kernel.

The assumptions are:

most reporting queries to vertica cover only "hot" data area of hour/day/week worth of data. Bcache would cache the frequently used data onto NVMe.
as time goes, "hot" data area shifts on the backend disks surface and Bcache automatically adjusts cached blocks to represent it.
write queries (data import) of vertica will be much slower than reads and that would not block any read operations (write-through mode). This is ok, as we do data import in bulk and don't need it to be immediate.

Back of envelope calculations show that cost of such system to be in range of $2000 as opposed to $8000 fully SSD-based solution (per server).

Can this approach work for vertica (or any other analytics database)? Have anyone used the "fast cache in front of slow backend" approach for databases?

Very interesting !

Not sure how this would work around Tuple Mover internal crunching ? Block change all the time.

my take

I guess a UDT to extract data from Vertica and flush it into memcahe or similar would also be a solution


SSIS while upgrading SQL server I want to upgrade SQL server from 2005 to 2012 (I'm thinking of in-place upgrade).
Today I have several SSIS packages that works with other servers.
The SQL version in those servers is mainly 2005.

My question is what happen to the SSIS when i'm upgrading?

Do i need to upgrade the SSIS as well?

Could the SSIS work without me upgrading it?

Are there any compatibility issues with the integration server? can 2012 work with 2005 (and the opposite - because replication have compatibility problems)

I'm not a big fan of the SSIS so my goal is to touch it as less as i can so it will keep on working.

Unfortunately you need to migrate your 2005 packages to 2012 using Data Tools. Depending on the complexity of each one this task will be more or less complicated. Here some useful links that I have used in the past:

https://devinknightsql.com/2012/05/16/upgrading-packages-to-ssis-2012/

https://sqldusty.com/2012/03/19/upgrade-2008-ssis-packages-to-2012-like-a-boss/

https://blogs.msdn.microsoft.com/mattm/2008/03/22/package-upgrade-wizard/

Here are a couple of useful resources

https://msdn.microsoft.com/en-us/library/hh667275.aspx

https://sqldusty.com/2012/03/19/upgrade-2008-ssis-packages-to-2012-like-a-boss/


How to use a MySQL error handler in a stored procedure with prepare/execute I have a procedure defined like this:

drop procedure if exists drop_tables_like;
delimiter $$
create procedure drop_tables_like(myint int, pattern varchar(255), db      varchar(255) )
begin
DECLARE CONTINUE HANDLER FOR 1064
select now() - interval  myint day into @owa;
set group_concat_max_len = 65535;
select concat('drop table ', group_concat(table_name)) into @str_sql
from information_schema.tables
where
    table_schema=db and table_name like pattern and
    create_time <= @owa;

prepare stmt from @str_sql;
execute stmt;
drop prepare stmt;
end$$

The idea is to drop tables matching a certain pattern that have a create_time <= a specified date.

When executed, I get an error:

mysql> call drop_tables_like(7, 'monkey%', 'daweb2012_civicrm');
ERROR 1243 (HY000): Unknown prepared statement handler (stmt) given to     EXECUTE
mysql>

How can I declare a handler for an exception in a procedure that is using a prepared statement?

Here is a sample of updated procedure with 1064 and 1065 errors caught

refer to https://gist.github.com/onare/c8d3042ba4d18408aeee for a longer list of errors

drop procedure if exists drop_tables_like;
delimiter $$
create procedure drop_tables_like(myint int, pattern varchar(255), db      varchar(255) )
begin
DECLARE E INT DEFAULT 0;
DECLARE M TEXT DEFAULT NULL;

DECLARE CONTINUE HANDLER FOR 1064 SET E='1064', M="%s near '%s' at line %d";
DECLARE CONTINUE HANDLER FOR 1065 SET E='1065', M="Query was empty";

select now() - interval  myint day into @owa;
set group_concat_max_len = 65535;
select concat('drop table ', group_concat(table_name)) into @str_sql
from information_schema.tables
where
table_schema=db and table_name like pattern and
create_time <= @owa;

prepare stmt from @str_sql;
IF E> 0 THEN
        SELECT CONCAT("MySQL Error #", E,  ": ", M);;
        set E=0;
    else
        execute stmt;
        drop prepare stmt;
    end if;
end$$

Use DECLARE CONTINUE HANDLER FOR SQLSTATE '1064'  use link

           https://dev.mysql.com/doc/refman/5.0/en/declare-handler.html

        @Vietyank Your procedure working fine.The message showing is not an error it is an warning.

        Example:
        I have created a Database Test1;
        mysql>Create Database Test1;
        mysql>use Test1;
        mysql>CREATE TABLE t1(id int);
        mysql>SHOW TABLES;
        +-----------------+
        | Tables_in_test1 |
        +-----------------+
        | t1              |
        +-----------------+
        1 row in set (0.00 sec)
        mysql> drop procedure if exists drop_tables_like;
        Query OK, 0 rows affected (0.00 sec)

        mysql> delimiter $$
        mysql> create procedure drop_tables_like(myint int, pattern varchar(255), db
          varchar(255) )
            -> begin
            -> DECLARE CONTINUE HANDLER FOR 1064
            -> set group_concat_max_len = 65535;
            -> select concat('drop table ', group_concat(table_name)) into @str_sql
            -> from information_schema.tables
            -> where
            ->     table_schema=db and table_name like pattern;
            ->  prepare stmt from @str_sql;
            ->     execute stmt;
            ->     drop prepare stmt;
            ->
            -> end$$
        Query OK, 0 rows affected (0.00 sec)

        mysql> delimiter ;
        mysql>
        mysql> call drop_tables_like(7, 't%', 'test1');
        Query OK, 0 rows affected (0.06 sec)

        mysql> show tables;
        Empty set (0.00 sec)

        After successfully execution of stored procedure.I am again executed same procedure Now it shows warning because your database contains zero tables.

        mysql> drop procedure if exists drop_tables_like;
        Query OK, 0 rows affected (0.00 sec)
        mysql> delimiter $$
        mysql> create procedure drop_tables_like(myint int, pattern varchar(255), db
          varchar(255) )
            -> begin
            -> DECLARE CONTINUE HANDLER FOR 1064
            -> set group_concat_max_len = 65535;
            -> select concat('drop table ', group_concat(table_name)) into @str_sql
            -> from information_schema.tables
            -> where
            ->     table_schema=db and table_name like pattern;
            ->  prepare stmt from @str_sql;
            ->     execute stmt;
            ->     drop prepare stmt;
            ->
            -> end$$
        Query OK, 0 rows affected (0.00 sec)

        mysql> delimiter ;
        mysql>
        mysql> call drop_tables_like(7, 't%', 'test1');
        ERROR 1243 (HY000): Unknown prepared statement handler (stmt) given to EXECUTE.

    It shows above message because of your database doesn't contain tables so that SQL statement that you wrote returns NULL value.

mysql> select concat('drop table ', group_concat(table_name)) into @str_sql
    -> from information_schema.tables
    -> where
    ->     table_schema='Test1' and table_name like 't%';
Query OK, 1 row affected (0.00 sec)
mysql> SELECT @str_sql;
+----------+
| @str_sql |
+----------+
| NULL     |
+----------+
1 row in set (0.00 sec)

    If you change your error HANDLER TO DECLARE CONTINUE HANDLER FOR SQLEXCEPTION Then it shows only warning after procedure execution try it Once.


ERROR: Failed to convert parameter value from a String to a DateTime I have faced this error: Failed to convert parameter value from a String to a DateTime when trying to pass multiple selected dates from checkboxlist to my parameter to be used in sql.

I have tried it with other datatypes such as nvarchar and it works when I pass multiple selected values to 1 stored procedure parameter and return the select statement using dynamic sql to populate my gridview.

Ps. In my webserver i'm displaying in checkboxlist as e.g 31-Aug-2013, using DATE.DataTextFormatString = "{0:dd-MMM-yyyy}";. In my sql database, it is displayed as e.g 2013-08-31.

During debugging selectedDATE do read it as e.g 2013-08-31. however there is error at da.Fill(ds); line

ASPX.CS

    protected void Page_Load(object sender, EventArgs e)
    {
        DATE.DataTextFormatString = "{0:dd-MMM-yyyy}";

        using (SqlConnection conn = new SqlConnection(dbConn))
        {
            try //Call stored procedure
            {

                SqlCommand cmd = new SqlCommand(spddl, conn);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                da.Fill(ds);
                if (!IsPostBack)
                {
                    DATE.DataSource = ds.Tables[0];
                    DATE.DataTextField = ds.Tables[0].Columns["DATE"].ToString();
                    DATE.DataBind();

                }
                if (IsPostBack)
                {
                    Bind();
                }

            }

            catch (Exception i)
            {
                bool exception = true;
                if (exception == true)
                {
                    //txtMessage.Text += e.Message;
                }
            }
        }
    }

public void Bind()
   {
           using (SqlConnection conn = new SqlConnection(dbConn))
       {
           using (SqlCommand cmd = new SqlCommand(spretrieve, conn))
           {
                 String selectedDATE = String.Empty;

                if (DATE.SelectedValue == "All")
                {
                    selectedDATE = "DATE";
                }
                else
                {
                    foreach (ListItem item in DATE.Items)
                    {
                        if (item.Selected)
                        {
                            DateTime dtTemp = Convert.ToDateTime(item.Value);
                            selectedDATE += "'" + dtTemp.ToString("yyyy-MM-dd") + "',";
                        }
                    }

                    selectedDATE = selectedDATE.Substring(0, selectedDATE.Length - 1);
                }

               cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@param", SqlDbType.DateTime).Value = selectedDATE;

               conn.Open();
               SqlDataAdapter da = new SqlDataAdapter(cmd);
               DataSet ds= new DataSet();
               da.Fill(ds);
               GRIDVIEW.DataSource = ds.Tables[0];
               GRIDVIEW.DataBind();

           }
       }

SQL

ALTER PROCEDURE [dbo].[SP]

@param nvarchar(512)

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @sql nvarchar(max)
SET @sql = 'SELECT * FROM TABLENAME WHERE [COLUMN] IN (' + @param + ')'
EXEC sp_executesql @sql;
END

You need to convert date into nvarchar first through your query:

Convert(varchar,[date_attribute],106)


Granting access to all tables for a user I'm new to Postgres and trying to migrate our MySQL databases over.  In MySQL I can grant SELECT, UPDATE, INSERT, and DELETE privileges on a low privileged user and enable those grants to apply to all tables in a specified database.  I must be missing something in Postgres because it looks like I have to grant those privileges for each table one at a time.  With many databases and hundreds of tables per database that seems like a daunting task just to get off the ground.  In addition, once a database is in operation, adding tables happens frequently enough that I wouldn't want to have to grant permissions each time unless absolutely necessary.

How is this best accomplished?

First, you have to be able to connect to the database in order to run queries.  This can be achieved by

REVOKE CONNECT ON DATABASE your_database FROM PUBLIC;

GRANT CONNECT
ON DATABASE database_name
TO user_name;

The REVOKE is necessary because

  The key word PUBLIC indicates that the privileges are to be granted to
  all roles, including those that might be created later. PUBLIC can be
  thought of as an implicitly defined group that always includes all
  roles. Any particular role will have the sum of privileges granted
  directly to it, privileges granted to any role it is presently a
  member of, and privileges granted to PUBLIC.

If you really want to restrict your user to DML statements, then you have a little more to do:

REVOKE ALL
ON ALL TABLES IN SCHEMA public
FROM PUBLIC;

GRANT SELECT, INSERT, UPDATE, DELETE
ON ALL TABLES IN SCHEMA public
TO user_name;

These assume that you will have only one schema (which is named 'public' by default).

As Jack Douglas pointed out, the above only gives the privileges for the already existing tables.  To achieve the same for future tables, you have to define default privileges:

ALTER DEFAULT PRIVILEGES
    FOR ROLE some_role   -- Alternatively "FOR USER"
    IN SCHEMA public
    GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO user_name;

Here, some_role is a role that creates the tables, while user_name is the one who gets the privileges.  Defining this, you have to be logged in as some_role or a member of it.

And, finally, you have to do the same for the sequences (thanks to PlaidFan for pointing it out) - here it is the USAGE privilege that you need.

--Create User

CREATE USER my_user_test WITH LOGIN NOSUPERUSER NOCREATEDB  NOCREATEROLE    INHERIT NOREPLICATION   CONNECTION LIMIT -1 PASSWORD 'xxxxxxx';

-- Grant connect to my data base

GRANT CONNECT ON DATABASE my_db_test TO my_user_test;

-- Grant usage the schema

GRANT USAGE ON SCHEMA my_sch_test TO my_user_test ;

-- Grant all table for SELECT, INSERT, UPDATE, DELETE

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA my_sch_test TO my_user_test;


Can we capture only Slave_IO_Running in SHOW SLAVE STATUS in MySQL I wanted to capture only status/result of Slave_IO_Running and Slave_SQL_Running column values, which I will be using to integrate into our application.

Is there any way to get only the values of those fields instead of getting values of all fields of SHOW SLAVE STATUS.

My OS is windows.

I am using MySQL 5.0.24

Executing

mysql> SHOW STATUS LIKE 'Slave_running';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slave_running | ON    |
+---------------+-------+
1 row in set (0.04 sec)

From the manual:

  This is ON if this server is a replication slave that is connected to a replication master, and both the I/O and SQL threads are running; otherwise, it is OFF.

Caveat: If you're running MySQL prior to version 5.1.46 or 5.5.4, it's possible the slave IO thread could not be running, and the status variable indicate the slave is running. (MySQL Bug 51089)

As of MySQL 5.7, the above solution will not work by default. This is because many of the status variables from information_schema are being moved into performance_schema:

  As of MySQL 5.7.6, the Performance Schema also contains system and status variable tables (see Section 23.9.13, “Performance Schema System Variable Tables”, and Section 23.9.14, “Performance Schema Status Variable Tables”). The Performance Schema tables are intended to replace the INFORMATION_SCHEMA tables, which are deprecated as of MySQL 5.7.6 and will be removed in a future MySQL release.
  [src]

and

  These Slave_xxx status variables become unavailable through SHOW STATUS:

  Slave_heartbeat_period
  Slave_last_heartbeat
  Slave_received_heartbeats
  Slave_retried_transactions
  Slave_running

  Applications that use these status variables should be revised to obtain this information using the replication-related Performance Schema tables.

There are two options to get the slave_running status outside of SHOW SLAVE STATUS

The preferred method, as mentioned by the doc, is to update the application to pull from replication-connection-status.SERVICE_STATE and replication-applier-status.SERVICE_STATE in performance_schema:

  // this is the SLAVE IO THREAD status
  SELECT SERVICE_STATE FROM performance_schema.replication_connection_status;

  // this is the SLAVE SQL THREAD status
  SELECT SERVICE_STATE FROM performance_schema.replication_applier_status;

NOTE: This is for single master replication. Multi-master replication should be in replication_applier_coordinator.SERVICE_STATE

The second option is to turn on show_compatibility_56. This is a temporary fix to get you by without modifying the application, but will go away in a future release.

A useful command to display the result in a more inteligible way is

mysql> show slave STATUS\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 127.0.0.1
                  Master_User: repuser
                  Master_Port: 3305
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 426595
               Relay_Log_File: ip-172-31-17-228-relay-bin.000004
                Relay_Log_Pos: 19023
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table: (...)


How can I model a medical scenario in an entity-relationship diagram? I am a self-taught frontend developer that is trying to go full-stack, so I am trying to teach myself data modelling by means of working on some exercises out of a database management book.
In this way, I have started creating an entity-relationship diagram (ERD), for a medical scenario, that is shown bellow:

I am not quite finished as I am having some issues converting some of the relevant business rules into ERD notation. Also, when reading the business rules concerning the associations keep in mind that the entity types Physician, Order, Diagnosis, Treatment, and Patient are needed (because in the book they are stated as business rules and I would like to understand that specific model), and although they could probably be modeled in a different way, I am trying to understand this specific model.
Business scenario description

Physicians diagnose any number of Conditions affecting a Patient, and a Diagnosis may apply to many Patients.

The involved hospital records the following information: date and time of diagnosis, diagnosis code, and description.

Physicians may order and perform any number of Services and Treatments for a Patient, or may not perform any Treatment.

A Treatment or Service may be performed on any number of Patients, and a Patient may have Treatments performed or ordered by any number of Physicians.

For each Treatment or Service rendered, the hospital records the following information: Physician ordering the Treatment, treatment date, treatment time, and results.

Entity Type Definitions

Patient. A person who is either admitted to the hospital or is registered as an outpatient. Each patient has an identifier, the medical record number (MRN), and a name.

Physician A member of the hospital medical staff who may admit patients to the hospital and who may administer medical treatments. Each physician has a physician ID and name.

Diagnosis. A patient's medical condition diagnosed by a physician. Each diagnosis has a diagnosis has a diagnosis ID/code and diagnosis name. Example: 1032, STAPH FOOD POISIONING.

*Treatment. Any test or procedure ordered by and/or performed by a pysician for a patient. Each treatment has a treatment ID/treatment code and treatment name using standard codes. Procedure codes are used for diagnostic and therapeutic procedures. Example: 1244, Chest X-Ray.

Order. Any order issued by a physician for treatment and/or services such as diagnostic tests (radiology) and therapeutic procedures (physical therapy), or drugs (perscriptions). Each order has an order ID, order date, and order time.

In this manner, I was wondering if someone could look at this and derive the same ERD from the business rules I have or if I messed up the translation.
My considerations

The specific issue I can not seem to wrap my head around is the existence of multiple relationships between the entity types.
First of all, I do not even know what kind of cardinalities to put on regarding the relationship between Order and Treatment.

Another issue for me is the multiple relationships between Patient, Diagnosis and Physician. Also, the same issue with Physician, Patient, and Treament.
Are these perhaps suppose to be ternary degree relationships, and (or) contain weak entity types to make this more understandable? Like Order and Treatment, or Treatment and Diagnosis I could see one depending on another as a Treatment in my mind would depend on the Diagnosis (correct me if I am wrong).

Consideration about multiple relationships between entities

In real life, Patients and Physicians interact in several ways. The most obvious one is that you go to see your doctor when you're ill (that's one kind of relationship between Patient and Physician). But you, as a Patient, may refer one Physician to a friend of yours (who is also ill, and, as such a Patient), because you liked how it works. This is a ternary relationship involving two Patients (the referer, and the refered) and one Physician.

Non-binary relationships

Not all relationships are between two tables (such as in the previous case). Another instance: a Diagnosis is performed by a Physician to a Patient (and, you could add: at some time, in some place, after x, y and z tests, etc. as attributes to this relationship). Having one ternary relationship is not the same as having two relationships Physician makes Diagnostic and Patient diagnosed Diagnostic. It you ever only visited one doctor, no doubt about who diagnosed. As soon as you visit two doctors... who diagnosed what is much less clear.

This is not answering all your questions, but hope it helps.

For Orders and Treatment, the relationship appears to be that a Treatment is sort of a generic description / code / entity describing a what can be done, while an Order is what Will be or was done and to whom. There could be 0 to many treatments ordered.

So your doctor Orders a Treatment for a Patient with the various time/date attributes. The order links your patient to your doctor and to the treatment option the doctor requests. It would have columns like: OrderID (PK),  PatientID, DoctorID, TreatmentID, OrderDateTime, TreatmentStartDateTime TreatmentCompleteDateTime, etc.

So your ERD should show an Orders table at the center connecting out to Patients, Treatments, and Doctors tables.

A patient can have 0 to many Diagnoses. But a diagnosis must be assigned by a doctor. So you won't have a Diagnosis column under patient. Instead, you'd have a Patient-Diagnosis table to link patients to diagnoses and to doctors. This would list the PatientID, DiagnosisCodesID, DoctorID, and whatever time/date values might apply (prelim date, confirmed date, etc.).

Your ERD should have a Diagnoses table at the center, connected out to Patients, DiagnosisCodes, and Doctors table.

Does this help answer your questions?


Creating Clustered Index uses all my tempdb space. Why? Table size = 44GB  (no clustered index)

Row Count  = 122016576

Disk space allocated to tempdb = 200MB

I wanted to create a clustered index for the table due to slow query times so I ran the following query:

USE [myDatabase]
GO

CREATE CLUSTERED INDEX [IX_Name] ON [dbo].[myTable]
(
    [myColumn] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

After 50 minutes of executing, my server's tempdb files (4 total) ran out of space and the database was sent into "In Recovery" status for about 10 minutes.  It's up and running now but I'm confused as to why tempdb was utilized so much when I specifically said SORT_IN_TEMPDB = OFF.

Does anyone know why this operation utilized so much tempdb space?

Although you had SORT_IN_TEMPDB = OFF, that is not the only thing that uses tempdb.  You also had set ONLINE = ON.

Since the job ran 50 minutes before you had trouble, it may be that you had enough activity in that single transaction to fill tempdb with row versioning data.  This is described here:

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

In part it says: "Online index operations use row versioning to isolate the index operation from the effects of modifications made by other transactions. ... Concurrent user update and delete operations during online index operations require space for version records in tempdb."

EDIT: Actually a 200 MB tempdb seems quite small for a database with a 44 GB table.

It's got to sort the data for the clustered index some where and then rewrite your rows in that order.

Don't forget that SQL Server will add an extra integer, the "uniquifier", so that it can disambiguate between the non-unique values of "myColumn". This will cause all your non-clustered index to include "myColumn" + "uniquifier" (4-byte).


MYSQL: used variable on IN condition EDIT] The query below is not the actual query which I am having issues with performance. It is only a simplified query. As I only would like to know if it is possible to store multiple results into a variable and used that variable into the IN condition.

I had this query below inside a procedure  which I would like to change for optimization.

SELECT * FROM request WHERE facility_id IN (SELECT facility_id FROM facility);

What I would like to do was to store the subquery into a variable and used that variable on the main query.

SET @facilities = (SELECT facility_id FROM facility);

SELECT * FROM request WHERE facility_id IN (@facilities);

But I got an error Subquery returns more than 1 row.
How should I do it?
Any help is appreciated.

I have found some link that suggest that way http://www.mysqlperformancetuning.com/how-to-speed-up-mysql-by-optimization?nocache=1#comment-961. But I cannot figure it out.

You can use the GROUP_CONCAT aggregate function to produce a comma-delimited list of values:

SET @facilities = (SELECT GROUP_CONCAT(facility_id) FROM facility);

The result will be just a string value, however. If you use it like this:

SELECT * FROM request WHERE facility_id IN (@facilities);

you cannot expect to get a match on every ID in the @facilities list. This is because @facilities is not expanded into a list and instead is treated as a single item of the IN list. The commas inside it will be seen as just characters inside a string value, not as syntactic delimiters.

In order to use @facilities the way you want, you will have to build and execute a dynamic query around the value of @facilities. It could be something like this:

@stmt = CONCAT('SELECT * FROM request WHERE facility_id IN (', @facilities, ')');
PREPARE stmt FROM @stmt;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

This way the query will not contain a reference to @facilities – instead, it will have the contents of @facilities as its integral part, and the commas will thus be treated as part of the syntax.

Each query that you want to use the @facilities list in would have to be executed in the above manner. I think you will agree it is not very convenient. There are other considerations to keep in mind as well. When you use a dynamic query and concatenate the contents of a variable into the dynamic query as described, you may be open to SQL injection attacks. Also, with a large number of items in an IN list the performance may degrade.

I would suggest that instead of a variable you use a temporary table to store the IDs:

CREATE TEMPORARY TABLE tmp_facilities AS SELECT facility_id FROM facility;

A table is much more flexible in that you are not tied to just one syntactic structure. Although you could still use the temporary table in an IN predicate, as in your example:

SELECT
  *
FROM
  request
WHERE
  facility_id IN (
    SELECT facility_id FROM tmp_facility
  )
;

you could also use it in an EXISTS subquery:

SELECT
  *
FROM
  request AS r
WHERE
  EXISTS (
    SELECT * FROM tmp_facility AS f WHERE f.facility_id = r.facility_id
  )

;

or in a filtering join:

SELECT
  r.*
FROM
  request AS r
  INNER JOIN tmp_facilities AS f ON f.facility_id = r.facility_id
;

Having many options how to write your queries may be beneficial when you need to tune them for performance.

this problem happen because your sub-query return multidimensional array and IN function has argument in single dimensional array.


Stored procedures vs. inline SQL I know stored procedures are more efficient through the execution path (than the inline sql in applications). However, when pressed, I'm not super knowlegeable about why.

I'd like to know the technical reasoning for this (in a way that I can explain it to someone later).

Can anyone help me formulate a good answer?

I believe this sentiment was true at one point, but not in current versions of SQL Server. The whole problem was that in the old days ad hoc SQL statements could not be properly optimized because SQL Server could only optimize / compile at the batch level. Now we have statement-level optimization, so a properly parameterized query coming from an application can take advantage of the same execution plan as that query embedded in a stored procedure.

I still prefer stored procedures from the DBA side for the following reasons (and several of them can have a huge impact on performance):

If I have multiple apps that re-use the same queries, a stored procedure encapsulates that logic, rather than littering the same ad hoc query multiple times in different codebases. Applications that re-use the same queries can also be subject to plan cache bloat, unless they are copied verbatim. Even differences in case and white space can lead to multiple versions of the same plan being stored (wasteful).
I can inspect and troubleshoot what a query is doing without having access to the application source code or running expensive traces to see exactly what the application is doing.
I can also control (and know in advance) what queries the application can run, what tables it can access and in what context, etc. If the developers are writing queries ad-hoc in their application, they're either going to have to come tug my shirt sleeve every time they need access to a table that I didn't know about or couldn't predict, or if I'm less responsible/enthused and/or security-conscious, I'm just going to promote that user to dbo so they stop bugging me. Typically this is done when the developers outnumber the DBAs or the DBAs are stubborn. That last point is our bad, and we need to be better about providing the queries that you need.
On a related note, a set of stored procedures is a very easy way to inventory exactly what queries may be running on my system. As soon as an application is allowed to bypass procedures and submit its own ad-hoc queries, in order to find them, I have to run a trace that covers an entire business cycle, or parse through all of the application code (again, that I might not have access to) to find anything that looks like a query. Being able to see the list of stored procedures (and grep a single source, sys.sql_modules, for references to specific objects) makes everyone's lives much easier.
I can go to much greater lengths to prevent SQL injection; even if I take input and execute it with dynamic SQL, I can control a lot of what is allowed to happen. I have no control over what a developer is doing when constructing inline SQL statements.
I can optimize the query (or queries) without having access to application source code, the ability to make changes, the knowledge of the application language to do so effectively, the authority (never mind the hassle) to re-compile and re-deploy the app, etc. This is particularly problematic if the app is distributed.
I can force certain set options within the stored procedure to avoid individual queries from being subject to some of the Slow in the application, fast in SSMS? problems. Meaning that for two different applications calling an ad hoc query, one could have SET ANSI_WARNINGS ON, and the other could have SET ANSI_WARNINGS OFF, and they would each have their own copy of the plan. The plan they get depends on the parameters in use, stats in place, etc. the first time the query is called in each case, which could lead to different plans and hence very different performance.
I can control things like data types and how parameters are used, unlike certain ORMs - some earlier versions of things like EF would parameterize a query based on the length of a parameter, so if I had a parameter N'Smith' and another N'Johnson' I would get two different versions of the plan. They've fixed this. They've fixed this but what else is still broken?
I can do things that ORMs and other "helpful" frameworks and libraries are not yet able to support.

That all said, this question is likely to stir up more religious arguments than technical debate. If we see that happening we'll probably shut it down.

While I respect the submitter, I humbly disagree with the answer provided and not for "religious reasons".  In other words, I believe there is no facility that Microsoft has provided which decreases the need for the guidance to use stored procedures.

Any guidance provided to a developer which favors the use of raw text SQL queries must be filled with many caveats, such that I think the most prudent advice is to greatly encourage the use of Stored Procedures and, discourage your developer teams from engaging in the practice of embedding SQL statements in code, or submitting raw, plain-old text-based SQL requests, outside of SQL SPROCs (stored procedures).

I think the simple answer to the question of why use a SPROC is as the submitter surmised: SPROCs are parsed, optimized, and compiled. As such, their query/execution plans are cached because you've saved a static representation of a query and you, normally, will be varying it only by parameters, which is not true in the case of copied/pasted SQL statements which likely morph from page-to-page-and component/tier, and are often variablized to the extent that different tables, even database names, can be specified from call-to-call. Allowing for this type of dynamic ad hoc SQL submission, greatly decreases the likelihood of the DB Engine to re-use the query plan for your ad hoc statements, according to some very strict rules.  Here, I am making the distinction between dynamic ad hoc queries (in the spirit of the question raised) versus the use of the efficient System SPROC sp_executesql.

More specifically, there are the following components:

Serial and parallel query plans which don't hold user context and allow for reuse by the DB engine.
Execution context which allows for reuse of a query plan by a new user with different data parameters.
Procedure cache which is what the DB engine queries in order to create the efficiencies we seek.

When a SQL statement is issued from a web page, termed an "ad hoc statement", the engine looks for an existing execution plan to handle the request.  Because this is text submitted from a user, it will be ingested, parsed, compiled, and executed, if it is valid.  At this time it will receive a query cost of zero.  Query cost is used when the DB engine uses its algorithm in order to determine which execution plans to evict from cache.

Ad hoc queries receive an original query cost value of zero, by default.  Upon subsequent execution of the exact same ad hoc query text, by another user process (or the same one), the current query cost is reset to the original compile cost.  Since our ad hoc query compile cost is zero, this does not bode well for the possibility of reuse.  Obviously, zero is the least-valued integer, but why would it be evicted?

When memory pressures arise, and they will if you have a often-used site, the DB engine uses a cleanup algorithm to determine how it can reclaim memory that the Procedure cache is using.  It uses the current query cost to decide which plans to evict.  As you might guess, plans with a cost of zero are the first to be evicted from cache because zero essentially means "no current users of, or references to, this plan".

Note: Ad hoc execution plans - The current cost is increased by each user process, by the plan's original compile cost.  However, no plan's maximum cost can be more than its original compile cost...in the case of ad hoc queries...zero.  So, it will be "increased" by that value...zero - which essentially means it will remain the lowest cost plan.

Therefore, it is quite likely that such a plan will be evicted first when memory pressures arise.

So, if you have a server build-out with lots of memory "beyond your needs", you may not experience this issue as often as a busy server that has only "sufficient" memory to handle its workload.  (Sorry, server memory capacity and utilization are somewhat subjective/relative, though the algorithm is not.)

Now, if I am factually incorrect about one or more points, I'm certainly open to being corrected.

Lastly, the author wrote:

"Now we have statement-level optimization, so a properly parameterized query coming from an application can take advantage of the same execution plan as that query embedded in a stored procedure."

I believe the author is referring to the "optimize for ad hoc workloads" option.

If so, this option allows for a two-step process which avoids immediately sending the full query plan to the Procedure cache.  It only sends a smaller query stub there.  If an exact query call is sent back down to the server while the query stub is still in the Procedure cache, the full query execution plan is saved to the Procedure cache, at that time.  This saves on memory, which during memory pressure incidents, may allow the eviction algorithm to evict your stub less frequently than a larger query plan that was cached.  Again, this depends upon your server memory and utilization.

However, you have to turn this option on, since it's off by default.

Lastly, I want to stress that, often, the very reason developers would embed SQL in pages, components, and other places, is because they wish to be flexible and submit dynamic SQL query to the database engine.  Therefore, in a real-world Use Case, submitting the very same text, call-over-call, is unlikely to occur as are the caching/efficiencies we seek, when submitting ad hoc queries to SQL Server.

For additional information, please see:

https://technet.microsoft.com/en-us/library/ms181055(v=sql.105).aspx
http://sqlmag.com/database-performance-tuning/don-t-fear-dynamic-sql

Best,
Henry


GROUP BY rewrite to subqueries with easily interchangeable AND OR conditions Tables:

content        tag_map       extrainfo
|id|title|    |id|tagid|    |id|lat|lng|
|--|-----|    |--|-----|    |--|---|---|
|1 |Lorem|    |1 |8    |    |3 |0,4|0,1|
|2 |Ipsum|    |2 |9    |    |2 |1,1|1,2|
|3 |Dolor|    |1 |9    |
              |1 |7    |

id is unique index

SELECT * FROM content, tag_map, extrainfo
WHERE
tag_map.id = content.id
AND
extrainfo.id = content.id

#AND OR conditions here
#conditions can be very different but I would like
#to use the same query structure
#just change conditions here as necessary

GROUP BY content.id

this query works - but slow.
I found the most alternatives involve subqueries, but I can`t figure out a way to use subqueries and still be able to freely change AND OR conditions that involve columns from ALL tables.

Other non-subquery methods are welcome too.

First clean the join syntax:

SELECT * FROM content as c
inner join tag_map as t on c.id=t.id
inner join extrainfo as e on c.id=e.id
WHERE
#AND OR conditions here
#conditions can be very different but I would like
#to use the same query structure
#just change conditions here as necessary
GROUP BY c.id

Now substitute your and/or clauses in place of the rem'd out lines above. Then look into indexing those columns (indexes should cover all the fields in the conditions).

These simple steps will speed up significantly. If you need further speed ups, please post before and after speedup results along with complete details of existing indexes.


Duplicate Oracle Database from Windows to Linux I have a regular Oracle 12c database already created, named ECLS. I've been given a pile of .BKP files, not from this database, and have been told to replace everything in ECLS with the contents of these files.

Here's how I've attempted to do that so far:

$ sqlplus / as sysdba
SQL> shutdown immediate;
SQL> startup pfile='/u01/app/oracle/product/12.1.0/db_1/dbs/initECLS.ora' nomount;
SQL> exit
$ rman auxiliary /
RMAN> duplicate database to ECLS backup location '/u01/dumpfilesgohere' NOFILENAMECHECK;

Inside the pfile, I have this line set:

*.enable_pluggable_database=true

It runs for 2.5 hours, then it fails with this error message:

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 02/02/2017 14:05:04
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-11003: failure during parse/execution of SQL statement: alter database recover logfile '/u01/app/oracle/fast_recovery_area/ECLS/archivelog/2017_02_02/o1_mf_1_73358_d970psod_.arc'
ORA-10562: Error occurred while applying redo to data block (file# 66, block# 799698)
ORA-10564: tablespace NCDS_TS
ORA-01110: data file 66: '/u01/app/oracle/product/12.1.0/db_1/dbs/D:ORADATAFUUVZPD152204CC3CA44789537C2BE3F39041EDATAFILEO1_MF_NCDS_TS_CQ476L1O_.DBF'
ORA-10561: block type 'TRANSACTION MANAGED DATA BLOCK', data object# 106975
ORA-00600: internal error code, arguments: [ktbrcl:NOOP incompat_opt], [212], [], [], [], [], [], [], [], [], [], []

In the logs at /u01/app/oracle/diag/rdbms/ecls/ECLS/alert/log.xml I see these messages:

<msg time='2017-02-02T14:05:03.614-05:00' org_id='oracle' comp_id='rdbms' type='UNKNOWN' level='16' host_addr='127.0.0.1' pid='5138'>
 <txt>Errors in file /u01/app/oracle/diag/rdbms/ecls/ECLS/trace/ECLS_pr07_5138.trc:
ORA-00308: cannot open archived log &apos;D:\ORADATA\FUUVZP\ONLINELOG\O1_MF_1_CQ39NGLV_.LOG&apos;
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00600: internal error code, arguments: [ktbrcl:NOOP incompat_opt], [67], [], [], [], [], [], [], [], [], [], []
 </txt>
</msg>
<msg time='2017-02-02T14:05:03.614-05:00' org_id='oracle' comp_id='rdbms' type='UNKNOWN' level='16' host_addr='127.0.0.1' pid='5138'>
 <txt>Slave exiting with ORA-10562 exception
 </txt>
</msg>
<msg time='2017-02-02T14:05:03.614-05:00' org_id='oracle' comp_id='rdbms' type='UNKNOWN' level='16' host_addr='127.0.0.1' pid='5138'>
 <txt>Errors in file /u01/app/oracle/diag/rdbms/ecls/ECLS/trace/ECLS_pr07_5138.trc:
ORA-10562: Error occurred while applying redo to data block (file# 66, block# 1200147)
ORA-10564: tablespace NCDS_TS
ORA-01110: data file 66: &apos;/u01/app/oracle/product/12.1.0/db_1/dbs/D:ORADATAFUUVZPD152204CC3CA44789537C2BE3F39041EDATAFILEO1_MF_NCDS_TS_CQ476L1O_.DBF&apos;
ORA-10561: block type &apos;TRANSACTION MANAGED DATA BLOCK&apos;, data object# 106975
ORA-00600: internal error code, arguments: [ktbrcl:NOOP incompat_opt], [67], [], [], [], [], [], [], [], [], [], []
 </txt>
</msg>

The fact that I'm seeing Linux errors about files not being found and Windows file paths suggests to me that this is somehow related to the fact that the .BKP files were generated on a Windows machine while the place they're being used is a Linux machine.

How can I account for this? Is there some setting in the pfile that I need to change during startup? Or is there another argument that needs to be passed to RMAN in the duplicate command?

An Oracle employee told me that redo logs are not platform independent. They told me I'd either have to shut down the Windows database and take a cold backup of it without any redo logs, or I'd have to restore the files I already have to another Oracle database running on Windows.

We ended up setting up a new Windows machine to restore the database to.

The endian is different between Windows and Linux. You can use transportable table spaces to move your database. At some point you need to change the endian of your transportable table space export. Here is the documentation.

Transporting Data Across Platforms


Creating two sub queries using WITH clause I am struggling with really complex queries involving two sub-queries using WITH clause as described here.

But it appears to me I cannot use one sub-query inside my main query. I here is my query statement:

WITH trip_labelled AS
(
    SELECT DISTINCT ON (trips_1.trip_id) trips_1.trip_id,
            sum(segments.length::double precision) AS segments_length,
            sum(travelmode_profile.foot * segments.length)::real / GREATEST(sum(segments.length), 1::real) AS foot,
            sum(travelmode_profile.bike * segments.length)::real / GREATEST(sum(segments.length), 1::real) AS bike,
            sum(travelmode_profile.car * segments.length)::real / GREATEST(sum(segments.length), 1::real) AS car,
            sum(travelmode_profile.bus * segments.length)::real / GREATEST(sum(segments.length), 1::real) AS bus,
            sum(travelmode_profile.metro * segments.length)::real / GREATEST(sum(segments.length), 1::real) AS metro
           FROM trips trips_1
             JOIN segments ON segments.session_id = ANY (trips_1.session_ids)
             JOIN travelmode_profile USING (session_id, segment_id)
             LEFT JOIN session USING (session_id)
             LEFT JOIN ( SELECT DISTINCT ON (session_1.user_id) session_1.user_id,
                    response.response AS role
                   FROM session session_1
                     LEFT JOIN response USING (session_id)
                  WHERE response.question_id = 24) demography USING (user_id)
          WHERE segments.movement = true
          GROUP BY trips_1.trip_id
),
   trip_mode AS
(   SELECT trips.trip_id AS trip_id,
       trips.seconds_start,
       trips.seconds_end,
       trips.distance,
         CASE
            WHEN GREATEST(trip_labelled.car, trip_labelled.bike, trip_labelled.bus, trip_labelled.metro, trip_labelled.foot) = trip_labelled.car THEN 'car'::text
            WHEN GREATEST(trip_labelled.car, trip_labelled.bike, trip_labelled.bus, trip_labelled.metro, trip_labelled.foot) = trip_labelled.bike THEN 'bike'::text
            WHEN GREATEST(trip_labelled.car, trip_labelled.bike, trip_labelled.bus, trip_labelled.metro, trip_labelled.foot) = trip_labelled.bus THEN 'bus'::text
            WHEN GREATEST(trip_labelled.car, trip_labelled.bike, trip_labelled.bus, trip_labelled.metro, trip_labelled.foot) = trip_labelled.metro THEN 'metro'::text
            WHEN GREATEST(trip_labelled.car, trip_labelled.bike, trip_labelled.bus, trip_labelled.metro, trip_labelled.foot) = trip_labelled.foot THEN 'foot'::text
            ELSE NULL::text
        END AS travel_mode
   FROM trips
     JOIN trip_labelled USING (trip_id)
  )
  SELECT  session_id,
         COALESCE(gps.gpstime, gslocation.gpstime) AS gpstime,
         COALESCE(gps.lat, gslocation.lat) AS lat,
        COALESCE(gps.lon, gslocation.lon) AS lon,
        COALESCE(gps.alt, gslocation.alt) AS alt,
        COALESCE(gps.speed, gslocation.speed) AS speed,
        COALESCE(gps.acc, gslocation.acc) AS acc
        trip_mode.travel_mode
FROM raw_data.gslocation
FULL JOIN raw_data.gps
    USING (session_id, seconds, millis);

Error:

ERROR:  syntax error at or near "trip_mode"
LINE 53:   trip_mode.travel_mode

I cannot use travel_mode or trip_mode.travel_mode from second sub-query trip_mode in main query. But works on commenting out travel_mode/trip_mode.travel_mode. Where do I go wrong?

There are two problems with your query:

You are missing a comma before trip_mode.travel_mode.
If you want to reference the CTEs in the main query, you have to add them to the FROM clause.

You need to use parenthesis around the case statement.

Try this:

WITH trip_labelled AS
(
    SELECT DISTINCT ON (trips_1.trip_id) trips_1.trip_id,
            sum(segments.length::double precision) AS segments_length,
            sum(travelmode_profile.foot * segments.length)::real / GREATEST(sum(segments.length), 1::real) AS foot,
            sum(travelmode_profile.bike * segments.length)::real / GREATEST(sum(segments.length), 1::real) AS bike,
            sum(travelmode_profile.car * segments.length)::real / GREATEST(sum(segments.length), 1::real) AS car,
            sum(travelmode_profile.bus * segments.length)::real / GREATEST(sum(segments.length), 1::real) AS bus,
            sum(travelmode_profile.metro * segments.length)::real / GREATEST(sum(segments.length), 1::real) AS metro
           FROM trips trips_1
             JOIN segments ON segments.session_id = ANY (trips_1.session_ids)
             JOIN travelmode_profile USING (session_id, segment_id)
             LEFT JOIN session USING (session_id)
             LEFT JOIN ( SELECT DISTINCT ON (session_1.user_id) session_1.user_id,
                    response.response AS role
                   FROM session session_1
                     LEFT JOIN response USING (session_id)
                  WHERE response.question_id = 24) demography USING (user_id)
          WHERE segments.movement = true
          GROUP BY trips_1.trip_id
),
   trip_mode AS
(   SELECT trips.trip_id AS trip_id,
       trips.seconds_start,
       trips.seconds_end,
       trips.distance,
         (CASE
            WHEN GREATEST(trip_labelled.car, trip_labelled.bike, trip_labelled.bus, trip_labelled.metro, trip_labelled.foot) = trip_labelled.car THEN 'car'::text
            WHEN GREATEST(trip_labelled.car, trip_labelled.bike, trip_labelled.bus, trip_labelled.metro, trip_labelled.foot) = trip_labelled.bike THEN 'bike'::text
            WHEN GREATEST(trip_labelled.car, trip_labelled.bike, trip_labelled.bus, trip_labelled.metro, trip_labelled.foot) = trip_labelled.bus THEN 'bus'::text
            WHEN GREATEST(trip_labelled.car, trip_labelled.bike, trip_labelled.bus, trip_labelled.metro, trip_labelled.foot) = trip_labelled.metro THEN 'metro'::text
            WHEN GREATEST(trip_labelled.car, trip_labelled.bike, trip_labelled.bus, trip_labelled.metro, trip_labelled.foot) = trip_labelled.foot THEN 'foot'::text
            ELSE NULL::text
        END) AS travel_mode
   FROM trips
     JOIN trip_labelled USING (trip_id)
  )
  SELECT  session_id,
         COALESCE(gps.gpstime, gslocation.gpstime) AS gpstime,
         COALESCE(gps.lat, gslocation.lat) AS lat,
        COALESCE(gps.lon, gslocation.lon) AS lon,
        COALESCE(gps.alt, gslocation.alt) AS alt,
        COALESCE(gps.speed, gslocation.speed) AS speed,
        COALESCE(gps.acc, gslocation.acc) AS acca,
        trip_mode.travel_mode
FROM raw_data.gslocation
FULL JOIN raw_data.gps
    USING (session_id, seconds, millis);


Postgres plpgsql - Using a variable inside of a dynamic create statement Using Postgres pl/pgsql, I'm attempting to create a table using a dynamic EXECUTE command, such as:

 ...
 DECLARE
    tblVar varchar := "myTable";
 BEGIN
 EXECUTE 'CREATE TABLE $1 (
             foo integer NOT NULL,
             bar varchar NOT NULL)'
 USING _tblVar;
 ...

However, I continue to receive the error message

  ERROR:  syntax error at or near "$1"

If I don't use the $1 token and, instead, write the string myTable it works just fine.

Is there a limitation on using dynamic statements for CREATE calls?

In addition to what @filiprem wrote, here is how you do this properly:

...
DECLARE
   tbl_var text := 'myTable';   -- I would not use mixed case names ..
BEGIN
EXECUTE '
CREATE TABLE ' || quote_ident(tbl_var) || '(
   foo integer NOT NULL,
   bar text NOT NULL)';
...

Use quote_ident() to avoid SQL injection or syntax errors. It will quote names with non-standard characters or reserved words.

I also replaced the double-quotes you had around the string value in your example with single-quotes.

In the code above, you are using $1 which is an argument which you got as a parameter from the function. In this case, you can't use $1 inside the string. So, the code should be like this

EXECUTE 'CREATE TABLE ' || $1 || ' (
         foo integer NOT NULL,
         bar varchar NOT NULL)'

In here || this mark indicates that joining a string like + sign


TempDB data files don't shrink well on SQL 2008 So ideally you want to pre-size your TempDB data and log files appropriately so that this isn't a concern, but sometimes a rogue developer runs a crazy huge query on a production server during work hours, causing the TempDB data files to blow up huge.

If TempDB was literally the only thing on the drive, then I could probably just leave it like that, but on some servers I have several SQL instances that all share the same TempDB drive.

So how can I shrink these TempDB data files without restarting the instance?

I normally try:

DBCC SHRINKFILE (name = 'tempdev', size = 5000)

This worked fairly consistently in SQL 2000 and 2005 (assuming the actual tempdb activity had tapered off), but seems to not work very often in 2008 (on my current server, it only worked on 1 of 4 data files, the others continue to remain 2-3x larger).

You've got two different questions in here:

Q: Sometimes a rogue developer runs a crazy huge query on a production server during work hours, causing the TempDB data files to blow up huge.

A: When that happens, all of the TempDB data files will grow roughly equally, so you won't have to worry about shrinking specific ones. Frankly, you don't want to shrink them - if you've got drive space set aside for TempDB, just leave these files in place. Why keep re-fighting the same battle? You'll have a rogue developer run another query in a few weeks. Just leave this in place. You don't get bonused based off empty drive space.

Now, having said that, if you've got TempDB's data and log files on the same volume as your user databases (or heaven forbid, the boot drive), then that's the real root cause, and you need to fix that. Even if you don't put them on separate spindles, TempDB should be on a separate logical volume to mitigate this exact problem. When it fills up, it fills up - but it doesn't take user databases (or the entire server, in the event of a full C drive) offline.

Q: (DBCC SHRINKFILE) seems to not work very often in 2008

It's more a function of how SQL Server relies more and more on TempDB in each release. When something's active in TempDB, you can't move its data around, and each new version of SQL Server works more in TempDB. For example, when you enable Read Committed Snapshot Isolation, the version store it uses lives in TempDB. When you use AlwaysOn Availability Groups, it tracks user database statistics in TempDB too. This is just another reason why you set aside a logical volume for TempDB, size the data files to fill it up, and then walk away - your work here is done, and don't try to shrink those files.

Via @SomeGuy and http://calyansql.blogspot.com.by/2012/04/shrink-tempdb-data-file-without-sql.html:

DBCC FREEPROCCACHE
GO
DBCC DROPCLEANBUFFERS
go
DBCC FREESYSTEMCACHE ('ALL')
GO
DBCC FREESESSIONCACHE
GO
DBCC SHRINKDATABASE(tempdb, 5)
GO

See also https://support.microsoft.com/en-us/help/307487/how-to-shrink-the-tempdb-database-in-sql-server.


Compare timestamp type columns in SQL Server I am trying to get data since last timestamp. In my legacy database, all tables have a column named as recordStamp type timestamp. I want to rely on this to select records modified after the max(recordStamp) from previous select.

I tried with T-SQL; but this tells only equality. What are my options..

CREATE TABLE Cars (
    id int identity,
    model varchar(20),
    brand varchar(20),
    recordstamp timestamp)

INSERT Cars (model)
VALUES
('accord'),
('camry'),
('corolla'),
('civic'),
('prius')

--latest recordstamp: 0x0000000001DD8AAF
select * from cars order by recordstamp desc --latest recordstamp: 0x0000000001DD8AAF

--Update a record
Update Cars set brand = 'honda' where model = 'accord'

--I want only the accord in result.
select * from cars order by recordstamp desc

TIMESTAMP is easily the worst naming decision Microsoft has made, at least in SQL Server. Not only does the data not have any date or time information, using the name actually violated the ANSI standard.

I asked them long ago to deprecate the keyword and always refer to this as ROWVERSION. But still the tools and metadata show and script out TIMESTAMP even when you explicitly created the table using ROWVERSION. It's horribly misleading.

In any case, there is no way to derive date/time information from this column, or to tell how much time has passed when the column information changes by n. And I'm not sure how you're going to know what recordstamp to base it off of anyway, if you're trying to compare. In an isolated scenario you can do something like this:

SELECT *, CONVERT(int, recordstamp)
  FROM dbo.Cars;

DECLARE @ts int;
SELECT @ts = MAX(convert(int, recordstamp)) FROM dbo.Cars;

UPDATE Cars SET model += 'x' WHERE id = 1;

SELECT *, CONVERT(int, recordstamp)
  FROM dbo.Cars
  WHERE recordstamp > @ts;

But how are you going to do this when it's another session updating the table? And outside of a very controlled environment, you have no idea what's going to happen if this database is backed up and restored elsewhere, fails over, or even when the instance is restarted. The documentation states that it is increasing, but doesn't explicitly state that this is forever, and also warns against using it for tracking date/time in any way.

If modification date is important to you, do it right: add a datetime2 column to the table and keep it maintained with a trigger.

I think the following query will return you what you need

CREATE TABLE Cars (id int identity, model varchar(20), brand varchar(20), recordstamp timestamp)
go
INSERT Cars (model) VALUES ('accord'), ('camry'), ('corolla'), ('civic'), ('prius')
go
Update Cars set brand = 'honda' where model = 'accord';
go
select * from Cars
where  recordstamp = (select max(recordstamp) from cars)
go


Is there a document that specifies un-installation of SQL server 2016 R server Basically I want to remove all components of R server from SQL Server 2016 install?

Try this guide, maybe it will work for you: Cleanly Uninstalling Stubborn SQL Server Components by Aaron Bertrand.

The easiest way: Control Panel -> Programs & Features -> and uninstall every program that contains the words "SQL Server".

or, using PowerShell:

Get-ChildItem HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall |
select @{Name='Guid';Expression={$_.PSChildName}}, @{Name='Disp';Expression={($_.GetValue("DisplayName"))}} |
where-object {$_.Disp -ilike "*SQL*R"} |
where-object {$_.Guid -like '{*'} |
% {"rem " + $_.Disp; 'msiexec /x "' + $_.Guid + '"'; ''} > uninstallR2.bat


Trim whitespace (spaces, tabs, newlines) I'm on SQL Server 2014 and I need to clean whitespace from start and end of a column's content, where whitespace could be simple spaces, tabs or newlines (both \n and \r\n); e.g.

'    this content    '                          should become 'this content'
'  \r\n   \t\t\t this \r\n content \t  \r\n   ' should become 'this \r\n content'

and so on.

I was able to only achieve the first case with

UPDATE table t SET t.column = LTRIM(RTRIM(t.column))

but for the other cases it doesn't work.

For anyone using SQL Server 2017 or newer

you can use the TRIM built-in function. For example:

DECLARE @Test NVARCHAR(4000);
SET @Test = N'
    ' + NCHAR(0x09) + N'  ' + NCHAR(0x09) + N' this
 ' + NCHAR(0x09) + NCHAR(0x09) + N'  content' + NCHAR(0x09) + NCHAR(0x09) + N'
' + NCHAR(0x09) + N' ' + NCHAR(0x09) + NCHAR(0x09) + N'     ';

SELECT N'~'
        + TRIM(NCHAR(0x09) + NCHAR(0x20) + NCHAR(0x0D) + NCHAR(0x0A) FROM @Test)
        + N'~';

Please note that the default behavior of TRIM is to remove only spaces, so in order to also remove the tabs and newlines (CR + LFs), you need to specify the characters FROM clause.

Also, I used NCHAR(0x09) for the tab characters in the @Test variable so that the example code can be copied-and-pasted and retain the correct characters. Otherwise, tabs get converted into spaces when this page is rendered.

For anyone using SQL Server 2016 or older

You can create a function, either as a SQLCLR Scalar UDF or a T-SQL Inline TVF (iTVF). The T-SQL Inline TVF would be as follows:

CREATE
--ALTER
FUNCTION dbo.TrimChars(@OriginalString NVARCHAR(4000), @CharsToTrim NVARCHAR(50))
RETURNS TABLE
WITH SCHEMABINDING
AS RETURN
WITH cte AS
(
  SELECT PATINDEX(N'%[^' + @CharsToTrim + N']%', @OriginalString) AS [FirstChar],
         PATINDEX(N'%[^' + @CharsToTrim + N']%', REVERSE(@OriginalString)) AS [LastChar],
        LEN(@OriginalString + N'~') - 1 AS [ActualLength]
)
SELECT cte.[ActualLength],
       [FirstChar],
       ((cte.[ActualLength] - [LastChar]) + 1) AS [LastChar],
       SUBSTRING(@OriginalString, [FirstChar],
                 ((cte.[ActualLength] - [LastChar]) - [FirstChar] + 2)) AS [FixedString]
FROM   cte;
GO

And running it as follows:

DECLARE @Test NVARCHAR(4000);
SET @Test = N'
    ' + NCHAR(0x09) + N'  ' + NCHAR(0x09) + N' this
 ' + NCHAR(0x09) + NCHAR(0x09) + N'  content' + NCHAR(0x09) + NCHAR(0x09) + N'
' + NCHAR(0x09) + N' ' + NCHAR(0x09) + NCHAR(0x09) + N'     ';

SELECT N'~' + tc.[FixedString] + N'~' AS [proof]
FROM   dbo.TrimChars(@Test, NCHAR(0x09) + NCHAR(0x20) + NCHAR(0x0D) + NCHAR(0x0A)) tc;

Returns:

proof
----
~this
              content~

And you can use that in an UPDATE using CROSS APPLY:

UPDATE tbl
SET    tbl.[Column] = itvf.[FixedString]
FROM   SchemaName.TableName tbl
CROSS APPLY  dbo.TrimChars(tbl.[Column],
                           NCHAR(0x09) + NCHAR(0x20) + NCHAR(0x0D) + NCHAR(0x0A)) itvf

As mentioned at the beginning, this is also really easy via SQLCLR since .NET includes a Trim() method that does exactly the operation you are wanting. You can either code your own to call SqlString.Value.Trim(), or you can just install the Free version of the SQL# library (which I created, but this function is in the Free version) and use either String_Trim (which does just white space) or String_TrimChars where you pass in the characters to trim from both sides (just like the iTVF shown above).

DECLARE @Test NVARCHAR(4000);
SET @Test = N'
    ' + NCHAR(0x09) + N'  ' + NCHAR(0x09) + N' this
 ' + NCHAR(0x09) + NCHAR(0x09) + N'  content' + NCHAR(0x09) + NCHAR(0x09) + N'
' + NCHAR(0x09) + N' ' + NCHAR(0x09) + NCHAR(0x09) + N'     ';

SELECT N'~' + SQL#.String_Trim(@Test) + N'~' AS [proof];

And it returns the exact same string as shown above in the iTVF example output. But being a scalar UDF, you would use it as follows in an UPDATE:

UPDATE tbl
SET    tbl.[Column] = SQL#.String_Trim(itvf.[Column])
FROM   SchemaName.TableName tbl

Either one of the above should be efficient for using across millions of rows. Inline TVFs are optimizible unlike Multi-statement TVFs and T-SQL scalar UDFs. And, SQLCLR Scalar UDFs have the potential to be used in parallel plans, as long as they are marked as IsDeterministic=true and do not set either type of DataAccess to Read (the default for both User and System data access is None), and both of those conditions are true for both of the SQLCLR functions noted above.

Using function on large data can take long execution time. I have a dataset of 8million rows, usingfunction took more than 30 minutes to execute. replace(replace(replace(replace(@COLUMN,CHAR(9),''),CHAR(10),''),CHAR(13),''),CHAR(32),'') took only 5 sec. Thanks all. I see you @sami.almasagedi and @Colin 't Hart


List all Oracle procedures/functions (including in packages) I want to list all procedures/functions in an Oracle server, including procedures/functions in packages. Am I doing it right?

SELECT
CASE WHEN up.OBJECT_TYPE = 'PACKAGE' THEN up.PROCEDURE_NAME ELSE up.OBJECT_NAME END AS name,
CASE WHEN up.OBJECT_TYPE = 'PACKAGE' THEN up.OBJECT_NAME ELSE NULL END AS package
FROM USER_PROCEDURES up
LEFT JOIN USER_IDENTIFIERS ui ON up.PROCEDURE_NAME = ui.NAME
WHERE up.OBJECT_TYPE = 'PROCEDURE' OR ui.TYPE = 'PROCEDURE';

SELECT
CASE WHEN up.OBJECT_TYPE = 'PACKAGE' THEN up.PROCEDURE_NAME ELSE up.OBJECT_NAME END AS name,
CASE WHEN up.OBJECT_TYPE = 'PACKAGE' THEN up.OBJECT_NAME ELSE NULL END AS package
FROM USER_PROCEDURES up
LEFT JOIN USER_IDENTIFIERS ui ON up.PROCEDURE_NAME = ui.NAME
WHERE up.OBJECT_TYPE = 'FUNCTION' OR ui.TYPE = 'FUNCTION';


For the above requirement, following query should work.

SELECT j.jobid, j.name, af.id, af.kind, af.location
FROM jobs j
INNER JOIN job_queues jq ON jq.id = j.job_queue_id
INNER JOIN organisations o ON o.id = jq.organisation_id
LEFT OUTER JOIN job_attached_files jaf ON jaf.job_id = j.id
INNER JOIN attached_files af ON af.id = jaf.attached_file_id
WHERE o.Id = 1

P.S: I am posting this query without any testing, so if there are any compilation errors or any unwanted results, please correct the query if you want or notify me, so that I can edit my answer accordingly

Unless I misunderstand your question, you just want to join more than 1 table like on this other post?

https://stackoverflow.com/questions/14995000/sql-inner-join-more-than-two-tables


Finding all sql id, sql text, number of executions, execution time between two AWR snap ids in oracle The AWR report provides the SQL information for top 10-20 SQL statements. Is it possible to get information on SQL statements that do not make it to the top list?  How do we find all sql id, sql text, number of executions, execution time between two AWR snap ids in oracle?

You can select from the below views.

Historical SQL statistics: DBA_HIST_SQLSTAT
All SQL text: DBA_HIST_SQLTEXT
Map snap_id to actual time: DBA_HIST_SNAPSHOT

This will get those fields between 2 snap IDs, just replace the snap ID's in the where clause

select t.sql_id,
    t.sql_text,
    s.executions_total,
    s.elapsed_time_total
from DBA_HIST_SQLSTAT s, DBA_HIST_SQLTEXT t
where s.snap_id between 1000 and 2000;


Unable to delete secondary data file I wanted to create a second log file, but by mistake I created a data file.

Now I'm trying to remove it, but I get the error message "the file cannot be removed because it is not empty". The following query lists a lot of tables:

SELECT o.name AS TableOrIndex
FROM sysfiles f
JOIN dbo.sysfilegroups s
ON f.groupid = s.groupid
JOIN dbo.sysindexes i
ON i.groupid = s.groupid
JOIN dbo.sysobjects o
ON i.id = object_id(o.name)
AND i.indid in (0, 1)
WHERE f.name = 'DB_NDF_FILE'

UNION

SELECT i.name
FROM sysindexes i join sysfilegroups f ON i.groupid = f.groupid
JOIN sysfiles l ON f.groupid = l.groupid
WHERE l.name = 'DB_NDF_FILE'

What's the correct procedure to move these data to other data files so I can safely delete this newly created file?

Use:

DBCC SHRINKFILE('logicalname', EMPTYFILE);
ALTER DATABASE database_name REMOVE FILE logicalname;

I was unable to remove the ndf file. Even the query I posted on the question was being empty, meaning there was no table data on the ndf file, but still the remove command was failing.

I just copied create table code of all tables on the DB, removed the DB and recreated it with clean files. This is a ETL stage DB, there's no important data on it, so I could just "drop" all tables and recreate them.