Mysql multiline statement causes syntax error // php, using wordpress to send a raw query string
    $output = $wpdb->query("
      INSERT INTO wp_priceGrids (gridId) VALUES (NULL); SELECT MAX(gridId) FROM wp_priceGrids;
    ");

Results in:

WordPress database error: [You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT MAX(gridId) FROM wp_priceGrids' at line 1]
INSERT INTO wp_priceGrids (gridId) VALUES (NULL); SELECT MAX(gridId) FROM wp_priceGrids;

Tried

Deleting / commenting the 2nd line. Works. A new row is added and
visible as I check through CLI
Adding backticks (``) around the
field name gridId. Seems not to affect anything, error is the same
Removing the hard return between statements (as you can see above).
No change

Question

Is it a syntax error? I've checked against examples and I think what I have matches their formats

owner@G700:~$ mysql --version
mysql  Ver 14.14 Distrib 5.7.25, for Linux (x86_64) using  EditLine wrapper
owner@G700:~$ php --version
PHP 7.2.15-0ubuntu0.18.04.1 (cli) (built: Feb  8 2019 14:54:22) ( NTS )
Copyright (c) 1997-2018 The PHP Group
Zend Engine v3.2.0, Copyright (c) 1998-2018 Zend Technologies
    with Xdebug v2.7.0, Copyright (c) 2002-2019, by Derick Rethans
    with Zend OPcache v7.2.15-0ubuntu0.18.04.1, Copyright (c) 1999-2018, by Zend Technologies

Your multiquery is subject to a race condition. You may not get back the value of the insert you do.

Use insert-id like:

$output = $wpdb->query("
      INSERT INTO wp_priceGrids (gridId) VALUES (NULL)";
$lastid = $wpdb->insert_id;

I've also heard the multiquery aspect of the mysql protocol is generally considered a mistake. Recommend avoiding it.


How to count rows based on a column value I am using Posgresql and I want to write a query to perform the following.
Suppose the table is like this:

id  section views
1     1      10
2     2       8
3     1      20
4     1      21
5     2      0

I want to count each row that has the same "section" value (ordered by "views") like this:

 id  section views contador
 4   1        21     1       //section 1 contador 1
 3   1        20     2       //section 1 contador 2 ...
 1   1        10     3
 2   2         8     1   //section 2 contador 1
 5   2         0     2   //section 2 contador 2

You need to use a Window Function like row_number()

SELECT
  id,
  section,
  views,
  row_number() OVER (PARTITION BY section ORDER BY views DESC)
FROM table;


Multiple source CASCADE DELETE behavior I'm having trouble understanding Postgres's CASCADE DELETE behavior.

If table_A REFERENCES table_B, and the column referenced could have multiple instances of the same value, will the table_A row be DELETEd even if there are still valid references upon a table_B DELETE?

If so, how can table_A only be DELETEd by CASCADE when there are no valid references left?

I think you must be misunderstanding CASCADE foreign keys, and/or the rules around foreign keys in general.

A foreign key can only refer to a table with a unique constraint or primary key. So in your example, you cannot create a foreign key referencing table_b if there can be multiple instances of a value in the referenced column.

When you DELETE FROM ... a table, and another table has an ON DELETE CASCADE foreign key reference to it, all referencing rows are deleted from the other table.

The column referenced cannot have multiple instances of the value, because you cannot create a foreign key to a column that is not unique.

The referring column of the table with the constraint  may have multiple values that refer to the column in the referenced table. If so, when the referenced value is removed, all these are deleted, as you would expect in a CASCADE.

regress=> CREATE TABLE table_b (id integer);
CREATE TABLE
regress=> INSERT INTO table_b (id) VALUES (1), (2), (3), (1);
INSERT 0 4

Can't create the foreign key, no unique constraint or PK:

regress=> CREATE TABLE table_a (b_id integer REFERENCES table_b(id) ON DELETE CASCADE);
ERROR:  there is no unique constraint matching given keys for referenced table "table_b"

Can't add a PK or unique constraint, has duplicates:

regress=> ALTER TABLE table_b ADD PRIMARY KEY (id);
ERROR:  could not create unique index "table_b_pkey"
DETAIL:  Key (id)=(1) is duplicated.


Doubt about Database design (newbie) I am learning about Database design,  I have doubt about some database design conepts
I want to know which is better design between two design
Design 1
there is two tables:
Object and Field
Object
| ID | NAME|
ID is PK
Field
| ID | OBJECT_ID | NAME
Both ID and OBJECT_ID are PK and OBJECT_ID IS FK

Design 2
there is three tables: Object, ObjectField and Field
Object
| ID | NAME |
ID is primary key
Field
| ID | NAME |
ID is primary key
ObjectField
| OBJECT_ID | FIELD_ID |
Both OBJECT_ID AND FIELD_ID is PK and FK
relation is stored in another table

There is no "better" between those two designs. They are just different. So it depends on what you want to store in your database. If every field you store belongs to just one object, then the first design is the better choice, but if you want to have fields and objects stored and each one can relate to another, the second design is better. Let me give you two short examples.

If object is a city with name and zip code like this:

ID          NAME
90201       Bell
90202       Bell Gardens

And object are persons and where they live:

ID  OBJECT_ID   Name
1   90201       Name_Person_1
2   90201       Name_Person_2
3   90202       Name_Person_3

You could store where each person lives without the redundant information of the name in the second table. But each row in the Field-table has to match exactly one entry in the Object-table.

The second design is to be used, if there is a relation between Object and Field and one field can belong to many Objects and one Object may belong to many Fields. That can be the case for a relation used for classes. A student can be enrolled in many classes and in each class there are many students. So your Object table would be students and you Field table would be class. Then you would store the relationship between those two in the ObjectField table.

Students:
ID          NAME
123         Joe
124         Jill

Classes:
ID          NAME
1           Math
2           History

StudentClass:
Student_ID  Class_ID
1           1
2           1
1           2

The student-class table would store redundant information in the field-table in your first design. But on the other hand would the second design need more space due to a third table, which is unnecessary in the zip code example. So it really depends.

One last thing. In your first design it is enough, if only ID is a primary key in the field table. There is no need to have a combined primary key consisting of ID and OBJECT_ID.


Index fragmentation increase as more row being deleted I have a table that contains more than 18 million records. I have a process that clears data out of that table everyday.  Index fragmentation was low.

This table has high transaction throughput. Every second it stores about 3 to 5 new records, so we know that clearing old records for this table needs to be fast.

The delete statement is like this:

Delete top 1000
From MyTable
Where CreationDate < 'Some Date'

Ideally, we keep running it until no more rows can be deleted.

For the first 6 million records the delete process goes well, but as time passes the delete starts slowing down until it impacts other application that access the same table. In addition, many foreign key indexes become fragmented.

My questions are:

Does deleting a lot of rows cause fragmentation on the foreign key indexes? (that is, indexes the dependent tables)
Does the deletion become slow because of the fragmented foreign key indexes? (slower reference data lookup)
Is there a balanced strategy that can keep the performance of both the deletes and the foreign key indexes high?

I'm on SQL Server 2005 Standard edition.

[Update] I have included more information here
The actual table name is called "VehicleLocation"
Key columns:

VehicleLocationKey (PK, char(36), not null)
AgencyVehicleKey (FK, char(36), not null)
AssignmentKey (FK, char(36, null)
EmployeeKey (FK, char(36), null)

Indexes

VehicleLocation_AssignmentKey (Non-Unique, Non-Clustered)
VehicleLocation_CreationDate (Non-Unique, Non-Clustered)
VehicleLocation_MessageGenerationDate (Non-Unique, Clustered)
VehicleLocation_pk (Unique, Non-Clustered)

Object Dependencies for VehicleLocation (~ 10.5 mil rows)

VehicleLocationAPC (~76000 rows)
VehicleLocationFare (0 rows)
VehicleLocationGF ( 0 rows)
VehicleLocationInpt (0 rows)
VehicleLocationOBD (~ 15000 rows)
VehicleLocationTP (~8.3 million rows)

All the above tables has indexes on their primary key and on VehicleLocationKey (FK) table.

In addition, we use GUID as primary key (bad idea, but it is legacy). On top of that I see that VehicleLocationTP index has a fragmentation of 96%, which is very high.

This isn't fragmentation.

Fragmentation is generated of course, but deletes will simply create "islands" of remaining pages, which is less evil then GUID/clustered key INSERT fragmentation.

If you're PK is an IDENTITY, then CreationDate should roughly track this so you're actually deleting chunks of contiguous rows anyway.

Do you have an index on CreationDate
Do you have delete cascades?
Is the TOP 1000 in a single transaction?

For point 3, doing a loop inside a transaction is pointless: is this it?

At some point, a statistics update may be needed if you delete enough rows but I don't think it's that.

Other options:

why not use TRUNCATE TABLE, wrapped in a stored procedure with EXECUTE AS OWNER
use SYNONYMs for poor man's partitioning


How do I control order/arrangement of certain columns in a resultset? Is it possible to control the sort order ourselves in a result set? So if I want to give it a custom order of appearance (instead of the ASC or DESC order), how can I go about doing it? I have read some answers on Stackoverflow which talk about using CASE statements in ORDER BY, but I do not understand how that works and how to make it work to get what I want.

In the above column for example, I want, say, Mitchell to be last, Carson to be first, Reiter to be fifth, etc.

You could create a new ordering table with sort order and link that to your original table. Then sort it by the sort order.
Like this:

DECLARE @SortTable TABLE (LastName nvarchar(50), SortOrder int)

INSERT INTO @SortTable
VALUES        ('Carson', 1)
            , ('Vargas', 2)
            , ('Ansman-Wolfe', 3)
            , ('Pak', 4)
            , ('Reiter', 5)
            , ('Varkey Chudukatil', 6)
            , ('Blythe', 7)
            , ('Mitchell', 8)

SELECT      YT.*
FROM        YourTable     AS YT
INNER JOIN  @SortTable    AS ST
    ON      ST.LastName   = YT.LastName
ORDER BY    ST.SortOrder

I used a table variable, but of course you can also use a normal table that you add in your database. Make sure you add all the possible values for lastname, otherwise you would miss out rows.


PostgreSQL maximize performance SSD I will have a huge PostgreSQL 9.3 database with many tables with more than 100M entries per table. This database will be basically be read-only (once I fill all the necessary tables and build the indexes no more write operations on the DB) and single-user access (run and benchmark multiple queries from localhost), since the DB will be used only for research purposes. Queries will always use JOIN on integer DB fields.

I will probably buy a SSD (256-512GB) for this purpose. I have not used an SSD for a DB before, so is there anything I should be afraid of? Can I put the entire DB on the SSD, or just the indexes? Is there any particular advice / tutorial required for tuning PostgreSQL for SSDs? Note, that I have a good workstation with an i7 and 32Gb of RAM, so perhaps you can offer some advice there too.

About SSDs, the main advice is to lower 'random_page_cost' to 1 (equals to 'seq_page_cost') in postgresql.conf, in addition to others usuals settings.


Increased disk consumption after upgrading to MySQL 8 I have a MySQL 8 DB in AWS RDS, and after upgrading from MySQL 5.7 to 8 a couple months ago, we have noticed free disk space being consumed at a very rapid rate, at least double the previous rate.

One particular table shows about 500GB of data_length + index_length when querying against INFORMATION_SCHEMA.TABLES, and yet its .ibd file is allocated to over 1TB on disk when querying INFORMATION_SCHEMA.FILES or INFORMATION_SCHEMA.INNODB_TABLESPACES (we have innodb_per_file_table=1). There is very little fragmentation, judging by the comparison of data_free against the data + index length.

How can I find the cause of the increased disk consumption rate? Is there anything inherent to MySQL 8 that uses more disk? Our application hasn't changed, but it does have rather large JSON columns -- possibly something in MySQL 8 handles JSON on disk in a less efficient way?

This is most likely because of a bug in the version of mysql you are using. The issue is that mysql does not release its file handle on temporary files until you restart mysqld. This is a confirmed bug and you can read others experience with it here.

Upgrading past 8.0.15 should have resolved the issue.


Optimize the DB select query JSON B type extract We have a query which we want to optimize for high performance.
The message column in table is of JSONB type and it holds a large json object.
Is there a way to optimze this?
We have an index already on the batch_num
SELECT jsonb_array_elements_text(message #> '{results}') AS res FROM TABLE WHERE batch_num = '11';

You painted yourself into a corner by modeling this with jsonb. The whole large thing has to be read to extract the attribute.
The best I can think of is to break the desired value out of the large object into its own column:
ALTER TABLE mytab
   ADD results jsonb GENERATED ALWAYS AS (message -> 'results') STORED;

This will work on recent PostgreSQL versions; for older versions you can use a trigger that does the same thing.
Then you only have to read the results column instead of the whole message.


Can I restore a table with flashback database even if I used "drop table purge"? If I issue the following statement:
drop table DontDrop purge;

It's going to be flushed out of my recycle bin so flashback statement won't work:

select * from DontDrop as of timestamp to_timestamp(sysdate-1);

Now, can I use a flashback database statement to restore it?

flashback database to timestamp (sysdate-1);

Flashback database can be used to restore a dropped table that is no longer in the recyclebin.

Provided the following is true:

1) Flashback database is turned on which you can check via:

select flashback_on from v$database;

2) You have the flashback logs to flashback that far, which you can check via:

select oldest_flashback_time from v$flashback_database_log;.

3) You have the backup archived redo/archived redo on disk to flashback the database that far.

The syntax looks like the following:

sqlplus / as sysdba
shutdown immediate;
startup mount;
FLASHBACK DATABASE TO TIME 'SYSDATE-1';
startup open resetlogs;

The Oracle Database 12c docs about flashback database can be found here.


How to export Sql Server DB schema as an ALTER statements? I have a 2 months old DB & I want to fetch new Schema for that DB without deleting the data in it using ALTER statements. Is it possible?
Currently I am taking a backup of table data of Old DB in some temporary DB & then replacing the Old DB with the new one & Inserting the backup data from the temporary DB. What could be the best solution to migrate the Schema?

I would write something like this, and first run in on the new database - to get the migration code, and then running resulting migration code on the old database - to alter the schema.
It's just a part to add missing columns and not complete. You should also check for is_identity, constraints, other data types and etc.
Maybe there is a complete code in the internet...
SELECT 'IF NOT EXISTS(SELECT 1 FROM sys.columns WHERE object_id=OBJECT_ID('''+t.name+''') AND name='''+c.name+''')
ALTER TABLE ' + QUOTENAME(t.name) + '
ADD ' + QUOTENAME(c.name) + ' ' + tt.name
       + CASE WHEN tt.name IN ('varchar','nvarchar') THEN '(' + CAST(c.max_length as varchar(10)) + ')'
              ELSE '' END
       + ' '
       + CASE WHEN c.is_nullable = 1 THEN 'NULL' ELSE 'NOT NULL' END + char(13)+char(10)+char(13)+char(10)
FROM sys.tables t
JOIN sys.columns c ON c.object_id = t.object_id
JOIN sys.types tt ON tt.user_type_id = c.user_type_id

Result - migration code:
IF NOT EXISTS(SELECT 1 FROM sys.columns WHERE object_id=OBJECT_ID('users') AND name='id')
ALTER TABLE [users]
ADD [id] int NOT NULL IDENTITY

IF NOT EXISTS(SELECT 1 FROM sys.columns WHERE object_id=OBJECT_ID('users') AND name='login')
ALTER TABLE [users]
ADD [login] varchar(100) NOT NULL

IF NOT EXISTS(SELECT 1 FROM sys.columns WHERE object_id=OBJECT_ID('users') AND name='email')
ALTER TABLE [users]
ADD [email] varchar(100) NULL


Access to performance counters from T-SQL procedure I want to write a stored procedure, which is returning CPU utilisation, Memory utilisation and Count of rows in table T1. So it should look like:

SELECT cpu, memory, (SELECT COUNT(*) FROM T1) as T1RowCount
FROM perfmon

But the problem is that I can't access cpu and memory from script: I looked at sys.dm_os_ring_buffers, sys.dm_os_performance_counters and others, but they don't provide actual information (for example, every counter in Resource Pool Stats group always returns 0).

Another workaround is configuring PerfMon to write directly in SQL table and then just fetch them in this stored procedure, but it has two minuses: it's complex and it requires external tool configuration when I really want to collect required info using vanilla T-SQL.

I have a program which is reporting its health (CPU usage, Number of allocated objects of type T and so on). But I currently do not provide such information about SQL Server which may be installed on another machine so I want to. But for unknown reason Resource Pool Stats object just do not provide any information: counters are just empty.

Rowcount tell me how many elements are not processed. Rows are added by producer and are deleted by consumer. So if this number is 0 then producer is not working. If this number is growing then something happened with consumer... There are several cases when this information may be useful.

What am I currently getting from counters is... nothing:

This should be possible via SQLCLR. The PerformanceCounter class is in in System.dll, which is one of the Supported .NET Framework Libraries. The Assembly containing the custom .NET code will need to be set to at least EXTERNAL_ACCESS.

I will try this myself later and update this answer with the results.


Are CTEs multithreaded? Here and here seem to suggest that they are, but I've also been told (source unlocatable) that a multi-row single-statement insert will only use one thread for all writing, that to multithread it, the query should be broken up and sent to as many connections as there are cores.

The documentation states that CTEs are executed "concurrently", but none linked say explicitly in effect that "CTEs will use all available cores to process any parallel sub-statements and/or the main query".

Do CTEs always use all available cores for any parallel tasks?  If so, are there any caveats aside from what could be reasonably assumed from chaining with RETURNING?

Postgres currently does not use parallelism when executing a single query. The entire execution engine is single threaded for each query (you can of course still execute multiple queries in parallel). This lack of "intra query parallelism" is one of the great drawbacks of running Postgres or MySQL. Basically, it makes those engines unfit for purpose on large datasets.

To get multi-threaded Postgres execution, you either need to split up the query into multiple queries yourself or run a Postgres compatible variant like Amazon Redshift or Greenplum (the former has added intra query parallelism to the code base, the latter does the splitting for you).

Whenever you feel the need to run a parallel query, first ask yourself: Could I add indexes that would make this fast enough without parallelism? If that answer is "Yes", pursue that option first. Very often though, you need brute force. And when it comes to brute force, vanilla Postgres is unfortunately not a particularly good database engine.


UPDATE table based on the same table I have a table with product descriptions, and each product description has a product_id and a language_id. What I want to do is update all of the fields with a language_id of 2 to be equal to the same product_id where the language_id is 1.

So far I've tried the following query, but I'm getting errors indicating that MySQL doesn't want to update a table where the table's also being used in the subquery.

UPDATE
  products_description AS pd
SET
  pd.products_seo = (
    SELECT
      pd2.products_seo
    FROM
      products_description AS pd2
    WHERE
        pd2.language_id = 1
    AND pd2.products_id = pd.products_id
  )
WHERE
  pd.language_id <> 1

Is there a "simple" way around this limitation in MySQL? Or any "tricks"? I'm a little surprised that my query doesn't work, as it seems logical.

This is rather risky business, and I can understand why. It has to do with the way MySQL processes subqueries. I wrote about it back on Feb 22, 2011 : Problem with MySQL subquery
Performing JOINs involving SELECTs and subquery SELECTs are OK. On the flipside of things, UPDATEs and DELETE can be a rather death-defying adventure.
SUGGESTION
Try refactoring the query so that it is an INNER JOIN of two tables
UPDATE
    products_description pd INNER JOIN products_description pd2 ON
    (pd.products_id=pd2.products_id AND pd2.language_id=1 AND pd.language_id<>1)
SET pd.products_seo = pd2.products_seo;

Give it a Try !!!


Design problem with placing proper constrains I have been trying to solve this problem but in every solution there seems to be something missing. So we have organization for collecting donations from donors for people that need help.

Member can create many action for collecting donations and people in need can have many actions for them. So we have following entities : donors, donations, members, people_in_need.

Now the key thing is that donor can donate only once for a particular action. That means I cant have action table with 3 (pk/fk) keys for member, people_in_need, donation. It seems that I am focusing on creating this middle table with 3 keys and I can't think of solution because of it.

Any ideas? My design so far is really simple at the moment:

Donor (DonorID, Name, Age)

Donations (DonationID, Amount)

Member (MemberID, Name)

PeopleInNeed (PeopleID, Name)

My best design was to create a relationship between Donor and Donation where I have Donor as FK in Donation , and then I created 'middle' table Action where I have composite key (MemberID, DonationID, PeopleID, Date). Now problem is that same donor can place more than one donation in Action and that is really the only problem I am facing.

Edit 2:

Actually, one particular action may be related to only one people in need. I meant that one people in need can have more that one different actions for them. That is way I put both MemberID and PeopleID as PK's in Action table.

Lets start first with the part of the design that seems OK. You have Member and Donor and PeopleInNeed. It's all right to have 3 tables, one for each entity:

Donor
    DonorID      PK
    Name
    Age

Member
    MemberID     PK
    Name

PeopleInNeed
    PeopleID     PK
    Name

The problem is in the Action and Donation tables. You say:

Member can create many action for collecting donations and people in need can have many actions for them.

I take that to mean that Actions are created by a member (so a 1-to-many relationship between Member and Action).

Also that PeopleInNeed may be related to multiple Actions and an action may be related to multiple people in need (so a many-to-many relationship between PeopleInNeed and Actions.

Actually, one particular action may be related to only one people in need. I meant that one people in need can have more that one different actions for them. That is way I put both MemberID and PeopleID as PK's in Action table.

The Action table becomes:

Action
    ActionID     PK
    MemberID          FK1 -> Members
    PeopleID          FK2 -> PeopleInNeed
    ActionCreatedDate

Then we have Donations. You say:

Now the key thing is that donor can donate only once for a particular action.

That tells me that Donation is just a many-to-many table between Donor and Action.

Donation
    DonorID      PK   FK1 -> Donors
    ActionID     PK   FK2 -> Actions
    DonationDate
    Amount

You could make the (DonorID, ActionID) a UNIQUE constraint instead, if you absolutely need a surrogate DonationID PK as well.


using *tables* as Table-Valued Parameters (TVP) MS SQL 2008 supports TVP: a useful feature for bulk uploading data to a stored proceedure for processing.

Rather than create a user-defined type, is it possible to leverage an existing table definition?  For example, is it possible to create a stored proceedure with the following signature?

CREATE PROCEDURE usp_InsertProductionLocation
@TVP **LocationTable** READONLY

The documentation seems to suggest that this is not possible.

SAMPLE CODE

/*
Sample code from:
http://msdn.microsoft.com/en-us/library/bb510489.aspx
*/

USE AdventureWorks2008R2;
GO

/* Create a table type. */
CREATE TYPE LocationTableType AS TABLE
( LocationName VARCHAR(50)
, CostRate INT );
GO

/* Create a procedure to receive data for the table-valued parameter. */
CREATE PROCEDURE usp_InsertProductionLocation
    @TVP LocationTableType READONLY
    AS
    SET NOCOUNT ON
    INSERT INTO [AdventureWorks2008R2].[Production].[Location]
           ([Name]
           ,[CostRate]
           ,[Availability]
           ,[ModifiedDate])
        SELECT *, 0, GETDATE()
        FROM  @TVP;
        GO

/* Declare a variable that references the type. */
DECLARE @LocationTVP
AS LocationTableType;

/* Add data to the table variable. */
INSERT INTO @LocationTVP (LocationName, CostRate)
    SELECT [Name], 0.00
    FROM
    [AdventureWorks2008R2].[Person].[StateProvince];

/* Pass the table variable data to a stored procedure. */
EXEC usp_InsertProductionLocation @LocationTVP;
GO

/*
The following is not part of the original source code:
*/

CREATE TABLE LocationTable(
 LocationName VARCHAR(50)
, CostRate INT );
GO

No, you can't leverage an existing table definition, you need to define an explicit type. This was asked for back in 2007 and was closed as "won't fix" but I still strongly encourage you to up-vote and leave a comment describing your use case and how this will help your business be more productive. You could even point to this question to demonstrate how tedious it can be to try and automate this.

Item on UserVoice (Formerly Connect #294130)

You can do this today, dynamically, though... for example for your simple definition:

-- you would pass these two in as parameters of course:
DECLARE
  @TableName SYSNAME = N'LocationTable',
  @TypeName  SYSNAME = N'LocationTypeTable';

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql = @sql + N',' + CHAR(13) + CHAR(10) + CHAR(9)
    + QUOTENAME(c.name) + ' '
    + s.name + CASE WHEN LOWER(s.name) LIKE '%char' THEN
        '(' + CONVERT(VARCHAR(12), (c.max_length/
        (CASE LOWER(LEFT(s.name, 1)) WHEN N'n' THEN 2 ELSE 1 END))) + ')'
        ELSE '' END
        -- need much more conditionals here for other data types
    FROM sys.columns AS c
    INNER JOIN sys.types AS s
    ON c.system_type_id = s.system_type_id
    AND c.user_type_id = s.user_type_id
    WHERE c.[object_id] = OBJECT_ID(@TableName);

SELECT @sql = N'CREATE TYPE ' + @TypeName
    + ' AS TABLE ' + CHAR(13) + CHAR(10) + '(' + STUFF(@sql, 1, 1, '')
    + CHAR(13) + CHAR(10) + ');';

PRINT @sql;
-- EXEC sp_executesql @sql;

Results:

CREATE TYPE LocationTypeTable AS TABLE
(
    [LocationName] varchar(50),
    [CostRate] int
);

Disclaimer: This does not deal with all kinds of other things like MAX types, precision and scale for numerics, etc. The final solution would have to be more robust to account for all potential column definitions but this should give you a start.

In SQL Server 2012 there are new DMVs and stored procedures that will make it much easier to derive column metadata from existing tables (or stored procedures or even ad hoc queries) without having to mess with all the conditional logic against sys.types and sys.columns. I blogged briefly about these enhancements in December. It's still tedious, but it's somewhere between the awful unmaintainable spaghetti above and the ability to just say " as copy of [table x]"...


Does cmdexec have additional process isolation beyond cmd.exe? I have a cmdexec step in a sql agent job that includes a redirection into a file at the end. It works as expected from a CMD shell running in the context of SQL Agent service account and produces a file in the desired location. If I run it as an agent job, however, the step 'succeeds' but never produces the output file. In both cases, the service account obviously has filesystem permissions and system rights sufficient to perform this action.

Is the cmdexec environment more restrictive in someway than just running cmd.exe?

Go to the advanced tab for the step.  Use one of the options to capture more output such as "Include step output in history" or the "Output File" setting. This should give you more information about the step and what is happening.


Considerations for performance comparison with a high fragmented heap? I am planning to perform some performance comparisons for different structures of one and the same table. The table I want to investigate on is a heap having ~ 1Mio rows and 123 columns.

Setup:
I have copied the table from production to a test system. It is a heap table and I want to check options to improve it.
Then I created 3 fresh copies of this table's data.

Copy1:

Clustered index on Primary Key (uniqueidentifier)
Same Nonclustered indexes & foreign keys

Copy2:

Clustered index on an existing IDENTITY Column
Same Nonclustered indexes & foreign keys

Copy3:

Clustered index on another uniqueidentifier and int column that is frequently used to filter a list of the rows
Same Nonclustered indexes & foreign keys

I have collected several typical and frequently used sample INSERT, UPDATE and SELECT queries from production environment. My plan is to run those multiple times against all those four tables and compare the total IO Statistics and total cpu time statistics as well as the Execution plan details to hopefully come to a valuable result telling me which table performs best with the regular queries of the application.

Since the three table copies are brandnew, so there is no fragmentation in place.
For a fair comparison I also re-builded all indexes of the original table.

I am not sure about the fragmentation of the "heap-index" of this table, it is ~60%. I wonder if I have to take care of this first or just leave it as it is since it will be in place in production as well and even if I fixed it, it will however come back and maybe influence query performance anyway?

I would beside an answer to the question above also appreciate any helpful comments what to consider as well to have the best possible result of this work. Especially regarding the data cache. Not sure if I should always clear it in advance of the queries or better run the queries once to FILL it before taking the samples?

Since the three table copies are brandnew, so there is no fragmentation in place. For a fair comparison I also re-builded all indexes of the original table.

A more realistic test would be to try recreate the fragmentation that would have resulted from the table being in each design from the start. This way you are comparing the result of the designs as they would look after real world use rather than after a fresh rebuild.

If your application keeps a full audit trail for that data then you could perhaps rebuild each copy by replying that audit. Otherwise you might need to make up some heuristic (for instance if the data includes a creation or last modified date inserting the rows into each copy in that order).

One thing to note when doing this is to do each insert individually rather than a block copy - when you insert or update many rows at once it is bright enough to bunch the index updates to reduce page splits which it can't do with the individual inserts that would result from real application access. The following will illustrate this (using the amount of space allocated as an indication of free page space fragmentation due to page splits caused by the randomness of UUID ordering):

SET NOCOUNT ON
CREATE DATABASE TestFragUUID
GO
USE TestFragUUID
CREATE TABLE IndividualInserts (ID UNIQUEIDENTIFIER PRIMARY KEY CLUSTERED, AnotherID UNIQUEIDENTIFIER UNIQUE)
CREATE TABLE SingleLargeInsert (ID UNIQUEIDENTIFIER PRIMARY KEY CLUSTERED, AnotherID UNIQUEIDENTIFIER UNIQUE)
GO
INSERT IndividualInserts SELECT NEWID(), NEWID()
GO 100000
WITH CTen AS (SELECT TOP 10 name FROM sys.objects)
INSERT SingleLargeInsert
SELECT NEWID(), NEWID()
FROM   CTen unit
CROSS JOIN cTen ten
CROSS JOIN cTen hun
CROSS JOIN cTen tho
CROSS JOIN cTen tth
GO
EXEC sp_spaceUsed 'IndividualInserts'
EXEC sp_spaceUsed 'SingleLargeInsert'
GO
USE master
DROP DATABASE TestFragUUID


Queries on database migration I have a SQL Server 2008 database of size around 1 TB. I want to migrate it to SQL Server 2012. I have few queries:

To migrate, I will take full backup of 2008 and restore it on 2012. I want to know if the compatibility version internally remains 2008 or changes to 2012?
To leverage full potential of 2012 after migration, what considerations to take during migration. After migration all 2012 new features and engine improvements should be available for the migrated database.

I did small tests Database compatibility level remains the same.

1.Created dummy database on 2008r2
2.Restored it on 2012

So while restoring,i could see version being changed,but after restoring compatability level remained the same.So you need to alter database to 110 compatibility level to get benefits of SQL 2012

Database 'backupdb' running the upgrade step from version 699 to version 700.
Database 'backupdb' running the upgrade step from version 700 to version 701.
Database 'backupdb' running the upgrade step from version 701 to version 702.
Database 'backupdb' running the upgrade step from version 702 to version 703.
Database 'backupdb' running the upgrade step from version 703 to version 704.
Database 'backupdb' running the upgrade step from version 704 to version 705.
Database 'backupdb' running the upgrade step from version 705 to version 706.

From Paul Randal Q&A sessions,Nakul blogs,below are the details of version.

These file versions are the internal storage format, and have nothing to do with the compatibility level of the database

http://beyondrelational.com/modules/2/blogs/77/Posts/19759/0311-sql-server-database-files-upgraded-from-version-nnnn-to-mmm-why-databases-cannot-be-restored-ba.aspx

For second question,i would recommend running the database upgrade advisor,please see below link for more details

https://technet.microsoft.com/en-us/library/ms144256(v=sql.110).aspx

http://beyondrelational.com/modules/2/blogs/77/Posts/19759/0311-sql-server-database-files-upgraded-from-version-nnnn-to-mmm-why-databases-cannot-be-restored-ba.aspx


Oracle Grid Control Notification for Data Guard failures Grid Control 10.2.0.5.

Does anyone know a way to configure a Grid Control notification rule for a failure of the SQL APPLY process on a logical standby database? I'd really like an email notification that the SQL APPLY process has encountered an error and is no longer applying redo to the logical standby.

I can get notifications on job failures, database down, listener stops, agent stops monitoring, but don't see how to get notified for this event.

Apply Lag will notify you when the apply is slow, not just when it has stopped.  This may give you too many notifications, but it seems like you could tweak the threshold to limit these.

See also:
Oracle® Data Guard Concepts and Administration 10g Release 2 (10.2)
Oracle® Data Guard Broker 10g Release 2 (10.2)


How can I make an index covering a ts_vector expression and also an integer? I am using the ruby pg_search gem, where code like this: Food.search("orange") produces a query like this.
SELECT "foods".*
FROM "foods"
INNER JOIN
  (SELECT "foods"."id" AS pg_search_id,
          (ts_rank((to_tsvector('simple', coalesce("foods"."name"::text, ''))), (to_tsquery('simple', ''' ' || 'orange' || ' ''' || ':*')), 0)) AS rank
   FROM "foods"
   WHERE ((to_tsvector('simple', coalesce("foods"."name"::text, ''))) @@ (to_tsquery('simple', ''' ' || 'orange' || ' ''' || ':*')))) AS pg_search_d4392ced9dff0647fed4ed ON "foods"."id" = pg_search_d4392ced9dff0647fed4ed.pg_search_id
ORDER BY pg_search_d4392ced9dff0647fed4ed.rank DESC,
         "foods"."id" ASC;

Ive created this index, which seems to work quite well
CREATE INDEX concurrently foods_gin ON foods USING gin
(to_tsvector('simple', coalesce("foods"."name"::text, '')));

I have another column, category. there are 9 categories. For a given search as shown above for name, I want to show results from category 9 first, and then all the other categories (order doesn't matter).
ideally the results could be paginated, so a perfect query/index combo would be great.
is this possible to do, and/or will it be a truly gigantic index?
p.s. suggestions for a better name for the question are welcome

With the help of the btree_gin extension, you can include an int column into your index, but doing so will not offer you an advantage.  The way gin indexes work, it can't be used for ordering.
You should write your query first, and worry about optimizing once you have something to optimize.


Postgres: Get nextval in sequence without actually incrementing sequence? It looks like select nextval('table_name') actually does the value increment.

My goal is to "predict" the nextval value on all tables in the database without actually making any incrementation. This should be a read-only operation.

I cannot run the select nextval on a read-only connection since it's actually trying to make a transaction. I would like to be able to query this and monitor the sequences on a read-only replica database.

How would you tackle this and meet the goal?

I would assume that SELECT currval('seq_name') + 1 or SELECT last_value + 1 FROM seq_name would be the answer, but that's only if you're incrementing by 1, or you know the increment off-hand.

If you're not incrementing by 1 and you don't know the increment, then try this:

SELECT currval('seq_name') + i.inc
  FROM (SELECT seqincrement AS inc
          FROM pg_sequence
         WHERE seqrelid = 'seq_name'::regclass::oid) AS i;

or

SELECT last_value + i.inc
  FROM seq_name,
      (SELECT seqincrement AS inc
         FROM pg_sequence
        WHERE seqrelid = 'seq_name'::regclass::oid) AS i;


MariaDB 10.3.27 Memory usage problem Greetings!
I tried to optimize MariaDB configuration with my.cnf. Everything was OK, but available memory falling down each hour. I'm using MariaDB with Zabbix server on the same server. CPU: 4 Cores, RAM: 8 GB (before was 4 GB and it was OK), HDD: 128 GB.
Could you please tell me what should I fix?
[mysqld]
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql

connect_timeout                 = 60
wait_timeout                    = 28800
max_connections                 = 200
max_allowed_packet              = 64M
max_connect_errors              = 1000

tmp_table_size                  = 256M
max_heap_table_size             = 256M
table_open_cache                = 1024

log_error                       = /var/log/mysql/mysql-error.log
slow_query_log_file             = /var/log/mysql/mysql-slow.log
slow_query_log                  = 1
long_query_time                 = 5

innodb_file_per_table           = 1
innodb_status_file              = 1
innodb_buffer_pool_size         = 5G
innodb_flush_method             = O_DIRECT
innodb_io_capacity              = 2000
innodb_log_file_size            = 640M
innodb_log_buffer_size          = 16M
innodb_buffer_pool_instances    = 5

skip-external-locking
bind-address                    = 127.0.0.1
event_scheduler                 = 1
query_cache_type                = 0
join_buffer_size                = 256K
read_buffer_size                = 256K
read_rnd_buffer_size            = 256K
sort_buffer_size                = 256K
key_buffer_size                 = 16M
thread_stack                    = 192K
thread_cache_size               = 8
myisam-recover-options          = BACKUP
query_cache_limit               = 2M
query_cache_size                = 128M
expire_logs_days                = 10
max_binlog_size                 = 100M

These are dangerously big; lower them to about 1% of RAM.
tmp_table_size                  = 256M
max_heap_table_size             = 256M

Some of the allocated space gradually grows until it hits a limit derived from a setting or combination of settings.
Do you have anything other than MySQL running on that machine?  If not, I don't expect it to fill up the 8GB.


Workaround the snapshot isolation in a writable CTE The postgres docs say:

  The sub-statements in WITH are executed concurrently with each other and with the main query. Therefore, when using data-modifying statements in WITH, the order in which the specified updates actually happen is unpredictable. All the statements are executed with the same snapshot (see Chapter 13), so they cannot "see" each others' effects on the target tables. This … means that RETURNING data is the only way to communicate changes between different WITH sub-statements and the main query.

I'm looking for a way to work around this effect in a situation like this:

Schema Setup:

create table t1(id serial);

create table t2(id integer);

create function f1() returns integer language sql as $$
  insert into t1 default values returning id; --
$$;

create function f2() returns integer language sql as $$
  with w as (insert into t1 default values returning id)
  insert into t2(id) select id from w join t1 using(id) returning id; --
$$;

Example:

select f2()
/*
|     F2 |
----------
| (null) |
*/

select * from t1
/*
| ID |
------
|  1 |
*/

select * from t2
/*
| ID |
------
*/

SQLFiddle

I'd like the insert in f2 to see the data in t1 and so end up with 1 row in t2.

If you return * from the CTE then you are effectively reading the row that you are currently trying to read from t1 -- that's how you mange to read the changes you're making to t1.

In your current code the join to t1 is redundant by the way, but I get the impression that you're using an example more simple than you really want.


Convert rows to column for every nth row by ID I'm trying to convert rows to columns.

I have the following columns: user_id, meta_key, meta_value

I have the following rows: 'first_name', 'last_name', 'stillingsbetegnelse'

Right now I can generate the rows and columns sorted and from a specific user_id. In my case it's from 15. This is with the following query :

SELECT user_id, meta_key, meta_value
FROM DATA_A ORDER BY user_id DESC;
where meta_key IN ('first_name', 'last_name', 'stillingsbetegnelse')
AND user_id > 15;

And the result is:

user_id     meta_key                meta_value
16          first_name              Laxgdfds
16          last_name               Narentdsaa
16          stillingsbetegnelse     Andet
18          first_name              Riedsa
18          last_name               Joe
18          stillingsbetegnelse     Andet

(all information is anonymized)

How do I get the following output?

first_name    last_name         stillingsbetegnelse
Laxgdfds      Narentdsaa        Andet
Riedsa        Joe               Andet

I have tried methods with pivot, but I'm getting an error:

    select
        p.*
    from DATA_A
    pivot(
        max([meta_value])
        for [meta_key] in ([first_name], [last_name], [stillingsbetegnelse])
) as p

I think the problem is that I want to take values only from a certain ID. How can I implement this feature? I only want the states data above, but I'm getting a lot of other data, that I don't have any use for.
...........................

With help i found a solution from Stackoverflow.
The solution is

> SELECT user_id,    MAX(IF(meta_key = 'first_name', meta_value,NULL))
> AS Fornavn,    MAX(IF(meta_key = 'last_name', meta_value,NULL)) AS
> Efternavn,    MAX(IF(meta_key = 'stillingsbetegnelse',
> meta_value,NULL)) AS  FROM DATA_A GROUP BY user_id;

Here's the code I ran to simulate the problem you had and see what the error message was and what to do, however when I ran it - it ran successfully.

CREATE TABLE #TestTable
(   [user_id]   varchar(50) NULL
,   [meta_key]  varchar(50) NULL
,   [meta_value]    VARCHAR(50) NULL
)

INSERT INTO #TestTable
VALUES  ('16'   , 'first_name'          , 'Laxgdfds'    )
    ,   ('16'   , 'last_name'           , 'Narentdsaa'  )
    ,   ('16'   , 'stillingsbetegnelse' , 'Andet'       )
    ,   ('18'   , 'first_name'          , 'Riedsa'      )
    ,   ('18'   , 'last_name'           , 'Joe'         )
    ,   ('18'   , 'stillingsbetegnelse' , 'Andet'       )

SELECT  P.*
FROM    #TestTable
PIVOT   (MAX([META_VALUE])
         FOR [META_KEY] IN ([FIRST_NAME], [LAST_NAME], [STILLINGSBETEGNELSE])
        ) AS P

So I would recommend 1) copying and running my code, and if that doesn't work responding back with the error message. 2) If my code works with no error message, then I would recommend reviewing your original code for possible typos.


SQL Agent running Ola scripts: all fine and perfect but a differential backup on a specific database fails I've two SQL servers with multiple DBs. On both, I've set up maintenance tasks using the Ola procedures. One DB server is Enterprise SQL with Agent and the other is Express using Win scheduler to run procedures.

All database backups are done towards a network share (QNAP NAS) with plenty of space (800Gb with 750GB still free). ALL of the backups, of system DBS and user DBS  works perfectly but, a differential backup of the main user DB is always failing with I/O error,

  Message 3271, gravity 16, error 112 Not enough space on disk

which obviously is a false error reason.  I'm running FULL backups at 6:00 and 20:00 with DIFF backups at lunch break (13:00). In between LOG backups every hour.  Please note that ALL of the other backups run, even the DIFF backups on smaller user DBs, the only one failing is always on the same DB (which is the biggest, most important and active).

All checks on DB give no errors.
Networks shares works.
Surfing forum I noticed that others are experiencing such I/O error
erratically.
SQL Server version is 13.0.5101.9

Any ideas?

For the help of everybody I post the resolution I did found to my problem.  Scanning other forums I noticed that this I/O error 112 - "not enough space on disk" is fairly a common issue, erratic but not so infrequent.

I read a post where the issue is described to come from the sql backup algorithm which, before starting the backup operation itself, is calculating, based on DB dimension, how much space on disk will be needed. If the result (space calculated to be needed) is not enough, you get the error and the backup is aborted. Unfortunately sometime this algorithm seems to have glitches. This should be applicable on backups made with compression option on only, but in my (and other) cases may create issues independently from.

There is a TRACE OPTION which is disabling such algorithm, and the backups start anyhow without such pre-check. I've therefore set DBCC TRACEON(3042, -1) on the SQL instance, as suggested, and bingo!, now also my DIFF backups are going on smoothly.

Thanks anyhow for the interest.


How can I delete duplicates records in a table efficiently I know there are several options to delete duplicate rows,  eg:

ROWID
Analytical fn's like Rank(), row_number()

What would be the most efficient way?

If you can afford to have a temporary table, then creating new table with the distinct values of the old one is a lot faster that deleting the rows. Something like:

create table no_dupes
as
select min(id) as id,
       col_a,
       col_b,
       col_c,
       min(col_d) as col_d,
       min(col_e) as col_e
from table_with_dupes
group by col_a, col_b, col_c;

Of course this has the drawback of needing to re-recreate all indexes and constraints. But in my experience it way faster - especially if you are deleting a large number of rows in a large table.


How can this unique index allow duplicate rows? Is there a way that this unique index has allowed duplicate rows? I thought maybe there are some extra space characters, but I can't find them.

=> select *, length(keyword), length(country), length(language) from keyword where id in (4588076, 4951423);
   id    |       keyword       | seed_id | source | search_count | country | language | volume | cpc  | competition | modified_on | violation | revenue | length | length | length
---------+---------------------+---------+--------+--------------+---------+----------+--------+------+-------------+-------------+-----------+---------+--------+--------+--------
 4588076 | power wallet review |         | SPYFU  |            0 |         |          |     70 | 0.11 |        0.31 |             |           |         |     19 |        |
 4951423 | power wallet review |         | SPYFU  |            2 |         |          |     70 | 0.11 |        0.31 |             |           |         |     19 |        |
(2 rows)

The index is

"keyword_keyword_country_language" UNIQUE, btree (keyword, country, language)

PostgreSQL 9.5.3

OK I was planning to remove the other two columns, but I thought I would test the keyword column and found this:

=> select k1.id, k1.keyword, k2.id, k2.keyword, k1.keyword=k2.keyword from keyword k1, keyword k2 where k1.id=4588076 and k2.id=4951423;
   id    |       keyword       |   id    |       keyword       | ?column?
---------+---------------------+---------+---------------------+----------
 4588076 | power wallet review | 4951423 | power wallet review | f

The answer can be found in the amazing documentation .. it LOOKS like you have NULL values in your tables..  When the DB checks for uniqueness, it says "does NULL equal NULL?  NOPE!" and allows it.

The important bit below (emphasis mine):

  are not allowed. Null values are not considered equal. A multicolumn unique

If you want to keep the uniqueness across ALL THREE COLUMNS and, at the same time, treat nulls as equal, then you have to get creative with your UNIQUE indexes by making them partial indexes..

CREATE UNIQUE INDEX ix1 ON table (col1, col2wNull, col3wNull) WHERE col2wNull is not NULL and col3wNull is not Null;
CREATE UNIQUE INDEX ix2 ON table (col1, col2wNull) WHERE col2wNull is not Null and col3wNull is Null;
CREATE UNIQUE INDEX ix3 ON table (col1, col3wNull) WHERE col2wNull is Null and col3wNull is not Null;
CREATE UNIQUE INDEX ix4 ON table (col1) WHERE col2wNull is NULL and col3wNull is NULL;

As you can see .. it can easily get a bit crazy.

A different alternative would be to make col2wNull and col3wNull be defined as NOT NULL and provide some default value for when nothing is supplied. THIS MAY OR MAY NOT BE A GOOD IDEA depending on what you're doing. "Magic values" have a tendency to give you lots of problems later.

With regard to your edit and the two strings appearing to be equal, but the database reporting that they are not - I can only imagine that there are some "invisible" characters (UTF-8?) that are in the string. Or it could be something as simple as one string has an additional space on the end. It partially depends on how you are saving it into the database. (Are you performing a trim() on them, lower(), etc..)

You could try comparing the strings in various other ways (such as looking at md5 hash).  I believe you can ask postgres to convert the column value into hex to view, as well, but how to go about doing that is escaping me at the moment (my apologies).


Update row if it has a certain set of values, add new row if it returns to initial value I'm having some trouble importing data into a table that I'm not sure how to describe succintly enough that can I can properly search for it (not that I haven't tried).

I have a table with three columns (actually more like 150, but these are the ones that matter):

order_number, date, status

that I update with some CSV data I don't control.

Status can have 5 states: 'pending', 'started', 'cancelled', 'not done' and 'complete'.

Each order will start 'pending', then progress to 'started', and finally end at 'cancelled', 'not done' or 'complete'.

If they end at 'cancelled' or 'complete', there's no problem, I just keep updating the status for the day and that's it.

The problem is when the status ends at 'not done'.
Usually when that happens, there's a new attempt only the next day, so I can just use the date to differentiate between attempts, but frequently enough to be a problem, a new attempt is made on the same day, and I need to keep both the first attempt, and the new one (that can still fail).

There's no field meant specifically for differentiating the attempts, nor changes to the order number, the time fields I could possibly try to use are populated with estimated times until the attempt is finished, and the only reason I know there was more than one attempt is because I get two rows for the same order on the same day.

Currently I'm loading the data into a temporary table, then using "INSERT...ON DUPLICATE KEY UPDATE" with a unique key on order_number and date to load it into its proper place, but that's causing me some loss of data.

I've tried adding a calculated field with value based on the final status to the index, but then I got at least two rows for each order: one for the final status, and one for 'started'.

I'm looking for some ideas for how I can solve it without processing the data outside the database before insert, since it would take awhile on the limited hardware available at the moment.

MySQL is rusty so bear with me

You are on the right path using a staging table to import the CSV file,  but you going to need to break the Inserts and the Update into separate steps

Not stated but i'm going to assume the import may have specific Order show up for each status changed so the order may show up 5 or more times from the Import and that the CSV file is created serially  meaning  PENDING, STARTED,  NOT DONE, (CANCELED, COMPLETE) would appear in that order never in a mixed order.    Cancel Showing before Pending or Start.  If that is the case order the Update may need to fiddled with

Create ImportTable (Serial_ID INT NOT NULL AUTO_INCREMENT,
"columns from the CSV" )

Created an ID field so the order of the CSV is maintained by the import.

Import the CSV. Not sure this is the correct MySQL code to Import and set a column to a different value

LOAD DATA INFILE 'import.csv'
INTO TABLE ImportTable
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(@id, "lis of columns ")
SET id =nextval('Serial_ID)  ;

Second step get all the new orders we are going to ignore the status for the first import

Insert into OrderTable
(Select Distinct "columns to insert", 'importing' as OrderStatus
 from ImportTable where Order_id not in
   (Select Order_id from OrderTable )
)

Now have all the orders in the system lets just process the status changes serially
Don't need the CASE statement for  Order_Status unless the CSV order is all random..

DELIMITER $$
CREATE FUNCTION process_import_status() returns bool
BEGIN
DECLARE @(list of vars for COLUMNS to Update)
DECLARE updates_cursor CURSOR FOR
 SELECT "columns to update"
  CASE Order_status
    WHEN 'pending' THEN 1
    WHEN 'started' THEN 2
    WHEN 'not done' THEN 3
    WHEN 'cancelled' THEN 4
    WHEN 'complete' THEN 5
 END as 'sort_order
 FROM ImportTable order by sort_order, Serial_ID, date;

updates_loop  Loop:
  FETCH  updates_cursor INTO @(list of vars for COLUMNS to Update);
  update OrderTable SET "list of columns"= @(LIST of COLUMNS)
     where Order_id = @order_id ;
END LOOP  updates_loop;
END $$$
DELIMITER ;

This should get you close to what you need to do..


What is the correct term to describe a single "data point" in a database? I'm not sure if this question is appropriate, but anyway here goes.

I want to communicate the number of "data points" in my database. That is every single value, including more generic values such as date_created, date_modified, id, etc. (e.g. sum(table_row_count*number_columns) )

Currently, I have been using the word "data point". However, this can sometimes lead to confusion as often people associate "data point" to a unique value representing some kind of measurement. For example if I am storing weather data, and I said my database has 100 million data points, then some people might assume that I have collected 100 million unique measurements of weather.

Is there a better term instead of "data points"?

The term
Attribute
would be the most appropriate.

Relational Database Theory

Basically, you're talking about columns and 'Column' is most appropriate when talking about the database itself or in SQL terms, however in common parlance this may not be understood by the layperson. 'Field' is a somewhat archaic term that can also be used and is usually associated with forms, but 'Attribute' is the technically correct word in Relational Theory, and would probably be most likely to be convey the meaning that you want to people.


What's an alternative solution to using a null value for a foreign key field? I have a few tables in my database which contain a Foreign Key field linking to another table. The problem is, the value for that field isn't always going to be present, and so sometimes I might have to insert a null foreign key field. To elaborate:

I have a table Sense_LanguageSource which lists individual etymologies of entries in a dictionary. This has the following fields:

LanguageSourceID (PK - Autonumber)
SenseFK (FK - Number)
LanguageCode (FK - Number)
SourceWord (Short text)
LanguageSourceType (Short text)
IsWaseieigo (Yes/No)

The LanguageCode field is the problematic case. It's used to list the language which the etymology derives from, and in this case it's a foreign key linking to a LanguageCodeValue table, which contains both the ISO-639-2B code and the full name of the language.

However, some of the etymologies I'm going to insert into the database don't have LanguageCodes, so I would have to insert a 0 into that field. Initially, Access wouldn't let me do this, so I disabled Referential Integrity. Then when I reopened the database, Access said that the Database was compromised and needed to be repaired. So I think that I need an alternative solution to this.

I would make it less normalised and simply have a text field for the LanguageCode in there, but there are two tables that refer to the LanguageCodeValue table, and it seems to make a lot of sense to have a table describing what each LanguageCode refers to. The solutions I can think of right now are:

Remove the relationship between the LanguageCode fields and the LanguageCodeValue table (so that it's purely a reference table, and not linked,) and just use SELECT queries if I need to look-up the meaning of the LanguageCode.
Keep the link between the tables without Referential Integrity to allow null foreign keys, and hope that the database doesn't break down.
Insert a ""null"" value in the LanguageCodeValue table to refer to (by which I mean an actual record with a key value of 0 or similar, and blank values for the text fields), so that instead of entering a null foreign key, I can just link to a record that doesn't mean anything.
Another solution?

As I'm new to databases and not sure about best design practices, I would like to know which solution is most appropriate in my situation.

As Akina said in the comments, try to avoid breaking relationships and referential integrity in your databases. If there is a documented need to have a value in there that indicates "none", "unknown", "other" or something like that, it is better to have that in your lookup table than try to hammer Access apart to allow for NULLs in a Foreign Key field.


How to use sequential UUID primary key for range query by date I'm using a sequential timebased GUID as my primary key in MySQL 5.6 in a transaction table with over 1 billion rows. The application is written in JAVA and GUID is generated as described here: http://wiki.fasterxml.com/JugHome

The question that I have is whether or not it is possible to range query the data by date without additional indexes? I've seen this question and the answer which states that this is somehow possible: Sequential GUID or bigint for 'huge' database table PK

Existing implementation is using Type 1 UUID, as described here. Then I came across this article, which describes efficient way to save the UUID, by re-organizing its contents.

The question that I have is whether searching by such UUID for a date range (and how it can be done) will be efficient, in other words, is it worth to save storage space by not adding index on the Date column?

The code for such is here, but it only works for "Type 1" UUIDs.  It's a matter of shuffling the bits around.  And while you are at it, BINARY(16) is more compact than VARCHAR(36) or whatever you have now.

More

By rearranging the bits of a type-1 UUID, you can take advantage of "locality of reference" when using an index on it.  For example, if you had News articles.  Users tend to look at recent articles and not touch the old ones.  New rows will be cached; old ones will drift into oblivion.

A suitably rearranged UUID would provide the equivalent of ORDER BY datetime.


How do I create a user for SQL Server whose queries (any/all) are time limited? Is there any way to create a database user in SQL Server 2005 so that none of their queries are allowed to exceed a certain time limit (for e.g 20-30 seconds) or all queries are limited to a certain number of cycles - and then the query times out - i.e. preventing this user from really using up any significant resources per query?

Anyone know of a way to do this?

thank you!

There are ways, but nothing which is perfect.

If the user only accesses the SQL Server database through a data layer (e.g., the user only goes through a specific web application and does not connect using SQL Server Management Studio), George Mavritsakis's answer can work.  If they do connect via SSMS, however, you have to go a step further.

You could set the query governor cost limit (in sp_configure) to a particular time limit, but that affects all users by default.  This will kill a query if the expected time is greater than your maximum, noting that expected time depends on statistics and more complex queries can foul up your calculations.

If you just want to do this for one login, you can create a logon trigger and if that login is your resource-wasting user, run

SET QUERY_GOVERNOR_COST_LIMIT 600 --or whatever value you want

in the trigger body.  Note again that the governor uses estimated times rather than actual times, so it's not foolproof.  It would, however, stop an attempt to do a select * against a 10 million row table if that's the actual problem you're having.

The downside of creating a logon trigger is that you have to remember that it's there.  Like all other types of triggers, it could lead to "unexpected" behavior if somebody who is not aware of it (e.g., your eventual replacement) tries to dig through why this person keeps getting weird error messages.

Note that in more recent versions, Microsoft has pushed away from the query governor, especially as they've shifted to the resource governor.  But it's still in 2012 (and doesn't appear to be deprecated) and if you're stuck on 2005, I guess that's no problem...


Copying large amounts of data to another target tables I'm migrating large amounts of data +40m of rows to a new version of the tables, my question would be more like, what could be the best way to improve the performance in terms of logging and execution time etc.

I have considered the below check list:

Set recovery model to bulk-logged recovery model. After bulk importing the data, switch the recovery model back to the full recovery model.
Load data with the TABLOCK hint to release any locking during the executions.
Set MAXDOP to 30 and change it back to normal.

Example of the statements:

/*Insert Example*/

INSERT INTO [dbo].[Order](

      [CREATED]
      ,[LAST_MODIFIED]
      ,[number]
      ,[LAST_MODIFIED]
      ,[Owner_FK]
      ,[Owner2_FK]
      ,[Owner3_FK]

SELECT
      [CREATED]
      ,[LAST_MODIFIED]
      ,[number]
      ,[LAST_MODIFIED]
      ,[Owner_FK]
      ,[Owner2_FK]
      ,[Owner3_FK]

FROM [Order_old]
WHERE [Owner_FK] IS NOT NULL
   OR [Owner2_FK] IS NOT NULL
   OR [Owner3_FK] IS NOT NULL

/*Delete Example*/

DELETE Ord FROM [Order] Ord
WHERE [Owner_FK] IS NOT NULL
   OR [Owner2_FK] IS NOT NULL
   OR [Owner3_FK] IS NOT NULL

/*Update Example*/

UPDATE Order_Type
SET [TYPE]=NULL
WHERE [TYPE]=''

I thought about integration services but that is a limitation I have, Also I had a look at BCP tool, but seems there is no way to transfer from one table to another unless we export it to a file and then we import it from that file.

I recommend reading through the data loading performance guide, fully understanding your maintenance window and what kind of locks you can take during it, and testing on a different database with table sizes as close to the ones in the production. The question that you asked is pretty generic, but one big thing that you missed is that you should consider disabling nonclustered indexes before loading a large amount of data. It can be more efficient to rebuild the indexes after the data load is complete.

  Set Recovery Model to bulk-logged recovery model. After bulk importing
  the data, switch the recovery model back to the full recovery model.

Make sure that you understand the consequences of the switch before doing it. A recovery model of bulk-logged can help if it makes some of your queries be eligible for minimal logging. Under the right conditions the insert query can be eligible for minimal logging (target table is empty and you have TABLOCK). Read the data performance guide for full details. The update and delete queries will not benefit from the recovery model switch unless you disable and rebuild indexes as part of that process.

  Add table hit TABLOCK to release any locking during the executions.

This is one of the requirements for minimal logging within SQL Server but otherwise isn't likely to really help you.

  Set MAXDOP to 30 and change it back to normal.

What makes you want to use a MAXDOP of 30? The typical advice is to limit your queries to MAXDOP 8 and to consider going over 8 only for specific operations that can parallelize well such as index rebuilds. All of the queries that you listed in your question aren't likely to parallelize well. On SQL Server 2012 you aren't eligible for parallel insert. The part of the query that finds the data to be inserted is eligible for parallelism, but do you really want 30 parallel workers for that part? The delete and update parts of the queries are also single-thread.


multiple similar rows, where only one field has changed I have a basic database set up for a school spelling bee (the scenario may seem slightly silly, but I'm using it as an excuse to learn a little more about database design). The main tables contains the words, definition, and associated attributes as shown, partOfSpeech and level being linked to two other tables.

The way that the spelling bee is structured is that each level contains a subset of the words from levels below it, and at the moment the words are entered in the database as they appear on paper, so certain words are occurring two or three times - the only thing that has changed for that row is the value of 'level'. For example, above, the word 'impostor' occurs three times, once for level 3, once for level 6 and once for level 7.

My question is how better structure my table to remove this duplication? Effectively I want the word, definition, and other fixed attributes in the table once, with the levels 3, 6 and 7 associated with it. In my head at least, it would also make it easier for me to not pull the same word twice from the database, if I choose to code the front-end not to.

Any help would be appreciated.

Plan A:  Use SELECT DISTINCT word FROM ... when pulling out.  That is, eliminate dups when fetching.

Plan B:  Have a SET datatype to list 3,6,7.

Plan C:  Use an extra many:many table to map "imposter" to each of the 3 levels.  There would be 3 rows for that word in the new table, but only one row in the current table.

Plan C may be the "proper" way to do it.


sys.dm_exec_requests and cpu_time If a query is executing on a multi-core server, and the query uses parallel threads, will sys.dm_exec_requests.cpu_timeshow the cpu time consumed by all threads executing the query? I assume this is the case, but I can't find documentation indicating this.

Elapsed time usually shows how long the request was running for, and CPU - a cumulative time of all processes. For example,  I usually see CPU x4 times higher than the elapsed time when the request's DOP is 4. When DOP is 1, then elapsed time matches CPU time.


PL/SQL block running without printing any output I'm trying to exexcute a pl/sql block using dbms_output to obtain a console output but without reason the unique answer by SQL developer is 'Anonymous block finished'.

declare
    v_count integer;
    lines dbms_output.chararr;
    num_lines number;
 begin
    dbms_output.ENABLE;
    num_lines := 0;

    for r in (select table_name, owner from all_tables
              where owner = 'SYSAME'
              and table_name like 'SYZ%'
              and table_name not in (select distinct table_name from all_tab_columns
                                      where OWNER = 'SYSAME'
                                      and table_name like '%SYZ%'
                                      and column_name not like '%FL_IDE_FILIALE%'))

    loop
        execute immediate 'select count(*) from ' || r.table_name || ' where fl_ide_filiale = 12'
            into v_count;
        dbms_output.Put_line(r.table_name ||';'|| r.owner ||';'|| v_count ||';'|| SYSDATE );
        num_lines := num_lines + 1;
    end loop;

    dbms_output.get_lines(lines, num_lines);
    dbms_output.Put_line(num_lines);

    FOR i IN 1..num_lines LOOP
      dbms_output.Put_line(lines(i));
    END LOOP;

end;

Any idea why i don't obtain any output ?

Q2: is there any way to plot the output not with a string line and in a table-like way. In this case someting like a select with this values :
r.table_name, r.owner, v_count, SYSDATE

You must run SET SERVEROUTPUT ON before you execute the PL/SQL script.


Query a name in large database, optimizations? if you would like to query a name in a very large database, are there some ways to optimize this query? If 1 million names are in the database, and the name has to stay as it was registered (and not with a number for example , myName_108008080 ). I am wondering that in the context of games, when someone is looking for someone else in the database just with his name, the query is quite fast even when the database contains many entries.

Thanks

I understand the feeling that 1mil+ rows is "Very Large", however you should understand that to a lot of database people you aren't getting into a large table until you have 10s-100s of millions of rows.  And even then that is middling large.

That being said if I understand what you are asking for you are looking for "indexes".  This is a fairly large concept at times and what index(s) you need really depends on what queries you are running.

I can think of two major categories though that might match what you are looking for.  The first is simple.  You have the name in a column and you want to search the table for everyone with a given name.  For example you are looking for everyone with the name Ken and you have a query like this.

SELECT * FROM TableName WHERE NameColumn = 'Ken'

or you want to get everyone's name that starts with Ken and your query looks like this.

SELECT * FROM TableName WHERE NameColumn LIKE 'Ken%'

In this case a simple index on the NameColumn column of your table will suffice and should give you excellent performance.

If however you want to search for Bob and get everyone who has names such as Bobbie, Robert, Rob etc.  Then I believe you want full text indexing.  This is a bit more complicated and has a number of requirements to set up.  If this is what you need then I suggest either having an in house DBA do the work (someone familiar with full text indexing) or do some research yourself.  For example for SQL Server 2012 the BOL entry is Here.

I'm a SQL Server person so my answers are going to bias that way but in all of the RDBMS' that I know of indexing in one form or another is how you improve basic performance of a well written query on a well designed system.  If you need help with the design of the query or system then you will have to provide a great deal more information starting with which RDBMS you are using and your table structure and the query you are trying to optimize.


Could Stack Overflow run on SQL Server Web Edition? I am starting a new web venture that may need to scale to a high number of users.

I am confident with the SPLA licencing for SQL Server Web edition, but want to know if I will need to factor in upgrading to Standard, Enterprise or DataCenter (pretty sure it won't be this one).

I know one shouldn't think about scaling before he needs to, but this will affect the architecture of the site and the business plan.

I know the processor limit is per physical processor, not thread, so that doesn't worry me. However some of the mirroring and backup features would worry me. Does SO rely on these features?

tl;dr:
Could a site like Stack Overflow run on SQL Server Web? What aspects of maintenance and high availability would be impossible to achieve?

Considering that SO is one of the most highly visited sites in the world, it may not be the best site to compare against for a new venture.

That being said, SO, to my knowledge, has rolled their own solution in terms of scaling various aspects of the system, including caching just about everything that they can to remove as much load off of the database as possible. Most systems of the SO scale end up in this scenario because no off-the-shelf product or built-in scaleability feature fully meets the needs of these extremely-high-traffic sites.

So, while I wouldn't be very surprised if SQL Web couldn't handle high traffic loads, I would bet that the built-in scaleability pieces you get from other tiers of SQL Server will not, on their own, be enough to handle these sorts of extremely high loads. (hence why SO doesn't rely on them as their primary scale vector.)

Lastly, the business plan for any new venture that is concerned with a high potential to scale needs to also include a revenue stream that scales along with the business volume (whatever that may be).

So, if you scale in the volume of data your system has to hold and manage, does revenue scale with it? Likewise for number of users, or any other metric that will grow. If you find that you are not scaling your revenue as fast as the expense of scaling the combined metrics, you have issues to worry about.


Guidance on inter-server database or data transfer and system architecture I'm a database developer (not a DBA) responsible for about 20 different non-transactional 1TB SQL Server 2008 - 2012 databases each on its own server. In order to be able to query across databases, the business would like me to co-locate these databases on one query server; however for data loading purposes (periodic large bulk loads) it is convenient to maintain the 1 database/server model. For some databases the tables are truncated and reloaded on a monthly basis; for others, new data is populated on top of existing data via daily jobs. How do you recommend that I transfer data from the source databases to the query server? I want to maintain control over when the data is transferred.  Upgrading to a newer version of SQL Server is possible.

You could use Availability Groups for this, which I've done in the past. You would have 20 AGs, and each would have the query server as a replica, probably an async replica. You could also use replication for this, especially since it's unlikely that the users need all tables in every database. I don't like supporting replication though. Maybe the query server would be a Data Warehouse.


Collect SQL Server Query Execution time in seconds I'm trying to find a way of collecting SQL server query response times over say for the last two days. Is there a way to achieve this? I know there are the query stats DMV's but I dont seem to be achieving what I required. I dont have access to query store therefore unable to use that at the moment either.

I'm looking for query response time from SQL server as I have people blaming the sql server but I can't see no memory pressure / cpu pressure or any bottlenecks. Therefore now trying to get the response times for queries ran against the server.

Thanks

There are indeed multiple methods.
Query Store
Since you have SQL Server 2016, may I recommend Query Store? You can enable it per database from the "properties" menu in SSMS, or by running something similar to the following script:
USE [master]
GO
ALTER DATABASE [TestDB] SET QUERY_STORE = ON
GO
ALTER DATABASE [TestDB] SET QUERY_STORE (OPERATION_MODE = READ_WRITE, QUERY_CAPTURE_MODE = AUTO)
GO

Once it is turned on, you can look at query performance details.

Gotchas:  Fair warning - turning on Query Store will clear the plan cache! Query Store, as a newer feature, can have some quirks. I've run across several myself. As far as performance, I see it used in environments with 20k batches/sec and only a couple of percent CPU overhead.
DMVs
Another option is to use the dmvs. The one you are looking for in this case is sys.dm_exec_query_stats, specifically the "elapsed_time" columns. Here's a query to look at recent long-duration queries.
SELECT TOP 50
st.text,
SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
    ((CASE statement_end_offset
        WHEN -1 THEN DATALENGTH(st.text)
        ELSE qs.statement_end_offset
    END - qs.statement_start_offset)/2) + 1
) AS statement_text,
qp.query_plan,
qs.execution_count,
qs.last_execution_time,
qs.last_worker_time/1000000.0 AS last_worker_time_s, --this is CPU time
qs.last_elapsed_time/1000000.0 AS last_elapsed_time_s --this is clock time
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE last_execution_time > DATEADD(minute,-5,GETDATE()) --last 5 minutes
ORDER BY qs.last_elapsed_time DESC

Gotchas to be mindful of with this dmv are that it doesn't capture RECOMPILE queries, and it doesn't capture canceled queries (which is what happens with a query timeout from the application).
SSMS
Finally, if you know the exact query, you can run it in SSMS and look at the actual execution plan. Highlight the select, and look at the properties window on the right. You'll see extremely helpful information about CPU and duration (in milliseconds). Similar information exists for the operators as well.

Gotchas with this method include the fact that queries submitted through SSMS do not necessarily reflect what happens with application queries. Canonical link here.
In short, I highly recommend Query Store.


Multiple databases in same AWS RDS PostgreSQL instance I have an existing database in a RDS AWS PostgreSQL instance. I need to create one more database in this instance as spinning up a separate cluster will be expensive. There doesn't seem to be any option to create one more database inside this instance. I understand that I can create a separate schema for this purpose(like in MySQL) but I need to keep the resources for these separate and hence the need for a separate database.

Please let me know if it is possible.
PostgreSQL 9.6.5

I believe you can. Based on AWS RDS FAQs:

  Q: How many databases or schemas can I run within a DB instance?

  RDS for Amazon Aurora: No limit imposed by software
  RDS for MySQL: No limit imposed by software
  RDS for MariaDB: No limit imposed by software
  RDS for Oracle: 1 database per instance; no limit on number of schemas per database imposed by software
  RDS for SQL Server: 100 databases per instance
  RDS for PostgreSQL: No limit imposed by software

You can create additional database by connecting to you DB instance and do a CREATE DATABASE. Check this guide

  To create additional databases, connect to the DB instance and use the SQL command CREATE DATABASE.

And the blog post on how to create new database in an existing PostgreSQL DB Instance:

psql --host=SOME-DBMS-HOST --dbname EXISTING_DB \
     --username=YOUR-USERNAME --password \
     --command="CREATE DATABASE new_database WITH OWNER some_owner"


How to split one table into two in a single statement? I've got an old table I want to split into two, but I'd rather not run two near-identical queries across the whole thing. The following does not work because you can only return column from the table you're inserting into:

WITH comment_insert AS (
    INSERT INTO comments (content)
        SELECT content
          FROM old_schema.comments
          JOIN area ON area.id = old_schema.comments.area_id
        RETURNING id, area.id AS area_id
)
INSERT INTO area_comment (parent_id, area_id)
SELECT comment_insert.id,
       comment_insert.area_id
  FROM transitional_schema.mon_area_comment

Is it possible to do something like this, putting some columns in the "parent" table and the parent ID plus some other columns into another table, using a single query?

Modelling scheme:

CREATE TABLE old_table (id INT, val1 INT, val2 INT);
INSERT INTO old_table VALUES (11,111,1111), (22,222,2222);

CREATE TABLE new_table1 (id INT GENERATED ALWAYS AS IDENTITY, val1 INT);
CREATE TABLE new_table2 (id_table1 INT, val2 INT);

WITH cte AS
(
INSERT INTO new_table1 (val1)
SELECT val1
FROM old_table
RETURNING id, val1
)
INSERT INTO new_table2 (id_table1, val2)
SELECT cte.id, old_table.val2
-- use both old table
FROM old_table
-- and CTE table
JOIN cte
    ON old_table.val1 = cte.val1; -- the relation is set
                                  -- by the value saved into new table
                                  -- not by old primary index

SELECT * FROM new_table1;
SELECT * FROM new_table2;

id | val1
-: | ---:
 1 |  111
 2 |  222

id_table1 | val2
--------: | ---:
        1 | 1111
        2 | 2222

db<>fiddle here

Pay attention - in last INSERT both CTE and source table are used.


Optimizing a large number of INSERT ... SELECT statements I have a MySQL InnoDB database with two tables related by a foreign key. Each time a row is inserted into one it uses an INSERT ... SELECT statement from the other to get the corresponding foreign key.

I have an application that is continuously doing a large number of these INSERT ... SELECT statements to populate this database. I have read that using a multiple row insert, i.e. INSERT INTO table (col1, col2, col3) VALUES (1,2,3), (4,5,6), ..., can be significantly faster than individual inserts. Unfortunately it appears that splitting the database into separate tables prevents me from taking advantage of this? Is there a workaround or another way to improve the speed of these inserts?

EDIT:

Would any of the following be likely to help?

Combining the INSERT ... SELECT statements with a UNION.
A multiple row insert into a temporary table followed by a JOIN and
a single INSERT ... SELECT.
Splitting the inserts among multiple threads.

SUGGESTIONS

Increase the size of your Log Buffer. The default value for innodb_log_buffer_size is 8M. You should make 256M or 512M. (Restart Required)
Turn off the Double Write Buffer. Set innodb_doublewrite to 0. (Restart Required)
OPTIONAL : Temporarily disable change buffering, which throttles secondary index updates.
OPTIONAL : In your application, you should limit the number of rows to insert. Try insert 500 or 1000 rows at a time. If the rows have long VARCHAR columns, then insert 100 or 200 rows at a time.

SUMMARY
Add these lines to my.cnf
[mysqld]
innodb_log_buffer_size = 256M
innodb_doublewrite = 0

then, restart mysql.
If these do not improve things, then try disabling the change buffering with
SET GLOBAL innodb_change_buffering = 'none';

You could always change it back to 'all' after a number of large bulk inserts.
GIVE IT A TRY !!!


Releasing disk space by using DELETE or TRUNCATE? More than a week ago, I deleted all the rows in a postgresql table (not via truncate, instead with delete from ...). Select count (*) reveals table rows are now 0.

When I now run queries to query disk space, I still see the table taking up space. Specifically, all the indexes still exist and are taking up space. How do I get rid of them and release disk space? Secondly, why do they remain in the first place, when I've gotten rid of all the rows?

Here's the table's detailed description in case warranted:

                                      Table "public.links_photoobjectsubscription"
     Column     |           Type           |                                 Modifiers
----------------+--------------------------+----------------------------------------------------------------------------
 id             | integer                  | not null default nextval('links_photoobjectsubscription_id_seq'::regclass)
 viewer_id      | integer                  | not null
 updated_at     | timestamp with time zone | not null
 seen           | boolean                  | not null
 type_of_object | character varying(15)    | not null
 which_photo_id | integer                  |
 which_link_id  | integer                  |
 which_group_id | integer                  |
 which_salat_id | integer                  |
Indexes:
    "links_photoobjectsubscription_pkey" PRIMARY KEY, btree (id)
    "links_photoobjectsubscription_seen" btree (seen)
    "links_photoobjectsubscription_updated_at" btree (updated_at)
    "links_photoobjectsubscription_viewer_id" btree (viewer_id)
    "links_photoobjectsubscription_which_photo_id" btree (which_photo_id)
Foreign-key constraints:
    "links_photoobjectsubscription_viewer_id_fkey" FOREIGN KEY (viewer_id) REFERENCES auth_user(id) DEFERRABLE INITIALLY DEFERRED
    "links_photoobjectsubscription_which_photo_id_fkey" FOREIGN KEY (which_photo_id) REFERENCES links_photo(id) DEFERRABLE INITIALLY DEFERRED
    "which_group_id_photoobjectsubscription" FOREIGN KEY (which_group_id) REFERENCES links_group(id) ON DELETE CASCADE
    "which_link_id_photoobjectsubscription" FOREIGN KEY (which_link_id) REFERENCES links_link(id) ON DELETE CASCADE
    "which_salat_id_photoobjectsubscription" FOREIGN KEY (which_salat_id) REFERENCES links_salatinvite(id) ON DELETE CASCADE

From the docs on VACUUM

  There are two variants of VACUUM: standard VACUUM and VACUUM FULL. VACUUM FULL can reclaim more disk space but runs much more slowly. Also, the standard form of VACUUM can run in parallel with production database operations. (Commands such as SELECT, INSERT, UPDATE, and DELETE will continue to function normally, though you will not be able to modify the definition of a table with commands such as ALTER TABLE while it is being vacuumed.) VACUUM FULL requires exclusive lock on the table it is working on, and therefore cannot be done in parallel with other use of the table. Generally, therefore, administrators should strive to use standard VACUUM and avoid VACUUM FULL.

You essentially need to issue a command to rewrite the whole table (from the same doc),

  Tip: Plain VACUUM may not be satisfactory when a table contains large numbers of dead row versions as a result of massive update or delete activity. If you have such a table and you need to reclaim the excess disk space it occupies, you will need to use VACUUM FULL, or alternatively CLUSTER or one of the table-rewriting variants of ALTER TABLE. These commands rewrite an entire new copy of the table and build new indexes for it. All these options require exclusive lock. Note that they also temporarily use extra disk space approximately equal to the size of the table, since the old copies of the table and indexes can't be released until the new ones are complete.

In the same document,

  Tip: If you have a table whose entire contents are deleted on a periodic basis, consider doing it with TRUNCATE rather than using DELETE followed by VACUUM. TRUNCATE removes the entire content of the table immediately, without requiring a subsequent VACUUM or VACUUM FULL to reclaim the now-unused disk space. The disadvantage is that strict MVCC semantics are violated.

And, from the docs on TRUNCATE

  TRUNCATE quickly removes all rows from a set of tables. It has the same effect as an unqualified DELETE on each table, but since it does not actually scan the tables it is faster. Furthermore, it reclaims disk space immediately, rather than requiring a subsequent VACUUM operation. This is most useful on large tables.


Show Rows as Columns in SQL Server I havent worked with Pivot. Not sure if pivot is the right thing to use. Is it possible to show this data:

-------------------
| Name     | Code |
-------------------
|Test A    | 1    |
|Test B    | 2    |
|Test C    | 3    |
-------------------

In this format:

------------------------------
| Customer | Fleet | Account |
------------------------------
|Test A    |Test B |Test C   |
------------------------------

If Code is 1 it is Customer.  If Code is 2 it is Fleet.  If Code is 3 it is Account.

You can accomplish this with pivot, when you are dealing with translating the columns into rows you will still need to utilize an aggregate. The pivot has three primary pieces:

Group
Spread
Aggregate

DECLARE @table TABLE (name VARCHAR(255), code INT);

INSERT INTO @table
VALUES ('Test A', 1), ('Test B', 2), ('Test C', 3);

SELECT [1] AS 'Customer', [2] AS 'Fleet', [3] AS 'Account'
FROM @table
PIVOT(MAX(name) FOR code IN (
            [1],
            [2],
            [3]
            )) pvt;

In the working example above we see the grouping as Name and we spread the results of code across three columns with hard coded values. The aggregate is based on the MAX value of name, this is what let's us pivot the row by pivoting on a group with a max value that would only return one row.

Now if you want to create this dynamically vs hardcoding, you'll need to look into dynamic sql.

Here's an article on dynamic pivot sql on MS SQL Tips by Aaron Bertrand


t-sql - combinatorics I'm trying to find all possible character combinations in a variable length string.

For example: '--' would have 2^n = 2^2 = 4 possibilities, 'x-', '-x', 'xx', '--'

I think that essentially I need to loop through c(2,2) + c(2,1) + c(2,0) where
c(n,r) = n! / (r! * (n-r)!) but I'm having trouble getting things to work in with a cte. So far everything starts to break down with you add characters to the string.

Using a numbers table -

declare @s varchar(15)
set @s = '--'

;with subset as (
select cast(stuff(@s,number,1,'x') as varchar(15)) as token,
     cast('.' + cast(number as char(1)) + '.' as varchar(11)) as permutation,
     cast(1 as int) as iteration ,
     number
from numbers where number between 1 and len(@s)
union
select @s, '.0.', 1, 0
) ,

combination as (
select  cast(stuff(token,n.number,1,'x') as varchar(15)) as token ,
    CAST(permutation+CAST(n.number AS CHAR(1))+'.' AS VARCHAR(11)) AS permutation,
    iteration + 1 as iteration,
    n.number
from subset s   inner join numbers n on substring(s.permutation,2,1) = n.number + 1
where n.number between 1 and len(@s)
)

select * from subset union combinations

This returns

token           permutation iteration   number
--------------- ----------- ----------- -----------
--              .0.         1           0
x-              .1.         1           1
-x              .2.         1           2
xx              .2.1.       2           1

I can't figure out how to get it working past two characters ( '---', '----' ...)
Maybe I'm looking at this wrong any pointers would be greatly appreciated. Sorry about the sql i know it's pretty ugly with lots of errors. Even after days of research my knowledge of cte's is horrid.

Suppose you have a auxiliary Numbers table with integer numbers.

DECLARE @s VARCHAR(5);
SET @s = 'ABCDE';

WITH Subsets AS (
SELECT CAST(SUBSTRING(@s, Number, 1) AS VARCHAR(5)) AS Token,
CAST('.'+CAST(Number AS CHAR(1))+'.' AS VARCHAR(11)) AS Permutation,
CAST(1 AS INT) AS Iteration
FROM dbo.Numbers WHERE Number BETWEEN 1 AND 5
UNION ALL
SELECT CAST(Token+SUBSTRING(@s, Number, 1) AS VARCHAR(5)) AS Token,
CAST(Permutation+CAST(Number AS CHAR(1))+'.' AS VARCHAR(11)) AS
Permutation,
s.Iteration + 1 AS Iteration
FROM Subsets s JOIN dbo.Numbers n ON s.Permutation NOT LIKE
'%.'+CAST(Number AS CHAR(1))+'.%' AND s.Iteration < 5 AND Number
BETWEEN 1 AND 5
--AND s.Iteration = (SELECT MAX(Iteration) FROM Subsets)
)
SELECT * FROM Subsets
WHERE Iteration = 5
ORDER BY Permutation

Token Permutation Iteration
----- ----------- -----------
ABCDE .1.2.3.4.5. 5
ABCED .1.2.3.5.4. 5
ABDCE .1.2.4.3.5. 5
(snip)
EDBCA .5.4.2.3.1. 5
EDCAB .5.4.3.1.2. 5
EDCBA .5.4.3.2.1. 5


Creating a table with many optional fields I am a developer (student) looking for some advice on what best approach should I follow while creating a table to model an entity that has a number of optional fields.

There exists a need to model an Organization entity with a few key fields such as id and name. There is also a join table from Users to Organization that specifies who belongs to the Organization and what their role(s) are.

The question I have pertains to a number of optional fields that belong to the Organization, such as website, email, and social links. Below are my ideas of approaching the problem thus far:

Add them to the table as optional fields

Pros: Easy CRUD on one table, faster than navigating joins, etc.
Cons: It seems a bit dirty to me. Might it become difficult to do migrations in the future?

Create a contact_information table that references an organization_id, references a contact_type_id (of website, email, Facebook, etc.) from a lookup table, and has a generic value field for the actual content.

Pros: feels cleaner, allows for an arbitrary number of contact types in the future
Cons: probably a lot slower. Tons more tables.

I am leaning towards #2, because it is a similar approach to what I'm doing for physical addresses, but I'm unsure whether or not it is the best solution as DBA is not my forte. If there is a 3rd or even 4th option that I am unaware of, I'd be really interested to hear of those as well.

Your second option is more flexible, but I'm not sure why you're worried about "tons more tables". Usually this would be done with a single table:

contact_types
-------------
  id (PK)
  name

contact_details
---------------
  id
  contact_type_id (FK to contact_types.id)
  value

organization_contacts
---------------------
  id (PK)
  contact_detail_id (FK to contact_details)
  organization_id (FK to organizations)

populated like this:

contact_types
-------------
ID  | name
----+-----------
1   | web_url
2   | facebook_url
3   | phone_1

contact_details
---------------
id  | type_id  | value
----+----------+-------
1   | 1        | www.stuff.com
2   | 3        | (111) 111-1111
3   | 2        | facebook.com/?profileid=stuff

organization_contacts
---------------------
id | contact_detail_id  | organization_id
---+--------------------+----------------
1  | 1                  | 1
2  | 2                  | 1
3  | 3                  | 1

This schema only has 3 tables (not "tons") and you can have as many contact types as you want. The joins aren't that complicated. Your contact_details table will be large as there is 1 record per piece of contact info, but unless you have "tons" of contact info for each organization and "tons" or organizations, this probably won't be too big a problem. ;)

You could also have a contact_details table which stores all of the fields. Something like this:

contact_details
---------------
  id (PK)
  main_email
  secondary_email
  web_url
  facebook_url
  linkedin_url
  myspace_url
  street_address_line_1
  street_address_line_2
  street_address_line_3
  city
  prov_state
  country
  postal_code
  phone_num_1
  phone_num_2
  fax_num

This structure is much simpler but is more static. If you don't plan to change your set of contact data very much, and you think that most records will have most (or above a certain threshold of) fields filled out, I suspect this would perform better.


Export entire SQL Server Database into platform independent data files On a quarterly basis I need to provide our clients a copy of all their data.  We need to provide this data in a database independent format (in other words I cannot simply send them a backup file).  The typical client has roughly 1GB of data.  This includes BLOB fields with files up to 10mb in size.  These files are a mix of Office documents, PDFs, and images.

I’ve looked into both the BCP and the XML query capabilities, and from my understanding both only seem to work at the table level which would mean I would have to write a query per table.  The schema size of this database makes those options prohibitively time consuming, not to mention future maintenance of this process seems costly.

The database independent format can be anything.  XML and scripts (scripts have to support both Oracle and SQL) are less desirable due to the storage size that these typically require.

This Powershell script should be able to solve most of your issue. I use a system table select to get my list of tables, then BCP to export all the data. The challenge is exporting your BLOBs. This script will export those as character data, which may not be useful to you in your situation.

Import-Module sqlps -DisableNameChecking
$outputdir="C:\TEMP"
$database="AdventureWorks2012"

$tables = Invoke-SqlCmd -ServerInstance localhost -Database $database -Query "SELECT s.name+'.'+t.name TableName FROM sys.schemas s JOIN sys.tables t ON s.schema_id = t.schema_id WHERE t.type = 'U'"

foreach($table in $tables.TableName){
    $outfile = Join-Path -Path $outputdir -ChildPath "$table-$(Get-Date -Format 'yyyyMMddhhmm').csv"
    $cmd = "& bcp $database.$table out '$outfile' -T -k -c -t','"
    Invoke-Expression $cmd | Out-Null
}

For an Oracle export, you'll want to use the import/export tool, but the process is more or less the same.