Locate the smallest missing element based on a specific formula I need to be able to locate a missing element from a table with tens-of-millions of rows, and has a primary key of a BINARY(64) column (which is the input value to calculate from). These values are mostly inserted in order, but on occasion I want to reuse a previous value that was deleted. It's infeasible to modify the deleted records with a IsDeleted column, as sometimes a row is inserted that is many millions of values ahead of the currently existing rows. This means the sample data would look something like:

KeyCol : BINARY(64)
0x..000000000001
0x..000000000002
0x..FFFFFFFFFFFF

So inserting all the missing values between 0x000000000002 and 0xFFFFFFFFFFFF is infeasible, the amount of time and space used would be undesirable. Essentially, when I run the algorithm, I expect it to return 0x000000000003, which is the first opening.

I've come up with a binary-search algorithm in C#, which would query the database for each value at position i, and test if that value was expected. For context, my terrible algorithm: https://codereview.stackexchange.com/questions/174498/binary-search-for-a-missing-or-default-value-by-a-given-formula

This algorithm would run, for example, 26-27 SQL-queries on a table with 100,000,000 items. (That doesn't seem like a lot, but it's going to be occurring very frequently.) Currently, this table has approximately 50,000,000 rows in it, and performance is becoming noticeable.

My first alternative thought is to translate this to a stored-procedure, but that has it's own hurdles. (I have to write a BINARY(64) + BINARY(64) algorithm, as well as a slew of other things.) This would be painful, but not infeasible. I've also considered implementing the translation algorithm based on ROW_NUMBER, but I have a really bad gut feeling about this. (A BIGINT is not nearly big enough for these values.)

I'm up for other suggestions, as I really need this to be as quick as possible. For what it's worth the only column selected by the C# query is the KeyCol, the others are irrelevant for this portion.

Also, for what it's worth, the current query that fetches the appropriate record is along the lines of:

SELECT [KeyCol]
  FROM [Table]
  ORDER BY [KeyCol] ASC
  OFFSET <VALUE> ROWS FETCH FIRST 1 ROWS ONLY

Where <VALUE> is the index supplied by the algorithm. I also haven't had the BIGINT issue with OFFSET yet, but I will. (Only having 50,000,000 rows right now means that it never asks for an index above that value, but at some point it'll get above the BIGINT range.)

Some additional data:

From deletions, the gap:sequential ratio is about 1:20;
The last 35,000 rows in the table have values > BIGINT's maximum;

There are a few challenges with this question. Indexes in SQL Server can do the following very efficiently with just a few logical read each:

check that a row exists
check that a row doesn't exist
find the next row starting at some point
find the previous row starting at some point

However, they cannot be used to find the Nth row in an index. Doing that requires you roll your own index stored as a table or to scan the first N rows in the index. Your C# code heavily relies on the fact that you can efficiently find the Nth element of the array, but you can't do that here. I think that algorithm isn't usable for T-SQL without a data model change.

The second challenge relates to the restrictions on the BINARY data types. As far as I can tell you cannot perform addition, subtraction, or division in the usual ways. You can convert your BINARY(64) to a BIGINT and it won't throw conversion errors, but the behavior is not defined:

  Conversions between any data type and the binary data types are not
  guaranteed to be the same between versions of SQL Server.

In addition, the lack of conversion errors is somewhat of a problem here. You can convert anything larger than the biggest possible BIGINT value but it'll give you the wrong results.

It's true that you have values right now that are bigger than 9223372036854775807. However, if you're always starting at 1 and searching for the smallest minimum value then those large values cannot be relevant unless your table has more than 9223372036854775807 rows. This seems unlikely because your table at that point would be around 2000 exabytes, so for the purposes of answering your question I'm going to assume that the very large values do not need to be searched. I'm also going to do data type conversion because they seem to be unavoidable.

For the test data, I inserted the equivalent of 50 million sequential integers into a table along with 50 million more integers with a single value gap about every 20 values. I also inserted a single value that won't properly fit in a signed BIGINT:

CREATE TABLE dbo.BINARY_PROBLEMS (
    KeyCol BINARY(64) NOT NULL
);

INSERT INTO dbo.BINARY_PROBLEMS WITH (TABLOCK)
SELECT CAST(SUM(OFFSET) OVER (ORDER BY (SELECT NULL) ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS BINARY(64))
FROM
(
    SELECT 1 + CASE WHEN t.RN > 50000000 THEN
        CASE WHEN ABS(CHECKSUM(NewId()) % 20)  = 10 THEN 1 ELSE 0 END
    ELSE 0 END OFFSET
    FROM
    (
        SELECT TOP (100000000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
        FROM master..spt_values t1
        CROSS JOIN master..spt_values t2
        CROSS JOIN master..spt_values t3
    ) t
) tt
OPTION (MAXDOP 1);

CREATE UNIQUE CLUSTERED INDEX CI_BINARY_PROBLEMS ON dbo.BINARY_PROBLEMS (KeyCol);

-- add a value too large for BIGINT
INSERT INTO dbo.BINARY_PROBLEMS
SELECT CAST(0x00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000008000000000000000 AS BINARY(64));

That code took a few minutes to run on my machine. I made the first half of the table not have any gaps to represent a sort of worse case for performance. The code that I used to solve the problem scans the index in order so it will finish very quickly if the first gap is early on in the table. Before we get to that let's verify that the data is as it should be:

SELECT TOP (2) KeyColBigInt
FROM
(
    SELECT KeyCol
    , CAST(KeyCol AS BIGINT) KeyColBigInt
    FROM dbo.BINARY_PROBLEMS
) t
ORDER By KeyCol DESC;

The results suggest that the maximum value that we converts to BIGINT is 102500672:

╔══════════════════════╗
║     KeyColBigInt     ║
╠══════════════════════╣
║ -9223372036854775808 ║
║            102500672 ║
╚══════════════════════╝

There are 100 million rows with values that fit into BIGINT as expected:

SELECT COUNT(*)
FROM dbo.BINARY_PROBLEMS
WHERE KeyCol < 0x00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000007FFFFFFFFFFFFFFF;

One approach to this problem is to scan the index in order and to quit as soon as a row's value doesn't match the expected ROW_NUMBER() value. The entire table does not need to be scanned to get the first row: only the rows up until the first gap. Here's one way to write code that is likely to get that query plan:

SELECT TOP (1) KeyCol
FROM
(
    SELECT KeyCol
    , CAST(KeyCol AS BIGINT) KeyColBigInt
    , ROW_NUMBER() OVER (ORDER BY KeyCol) RN
    FROM dbo.BINARY_PROBLEMS
    WHERE KeyCol < 0x00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000007FFFFFFFFFFFFFFF
) t
WHERE KeyColBigInt <> RN
ORDER BY KeyCol;

For reasons that won't fit in this answer, this query will often be run serially by SQL Server and SQL Server will often underestimate the number of rows that need to be scanned before the first match is found. On my machine, SQL Server scans 50000022 rows from the index before finding the first match. The query takes 11 seconds to run. Note that this returns the first value past the gap. It's not clear which row you want exactly, but you should be able to change the query to fit your needs without a lot of trouble. Here's what the plan looks like:

My only other idea was to bully SQL Server into using parallelism for the query. I have four CPUs, so I'm going to split the data up into four ranges and do seeks on those ranges. Each CPU will be assigned a range. To calculate the ranges I just grabbed the max value and assumed that data was evenly distributed. If you want to be smarter about it you could look at a sampled stats histogram for the column values and build your ranges that way. The code below relies on a lot of undocumented tricks that aren't safe for production, including trace flag 8649:

SELECT TOP 1 ca.KeyCol
FROM (
    SELECT 1 bucket_min_value, 25625168 bucket_max_value
    UNION ALL
    SELECT 25625169, 51250336
    UNION ALL
    SELECT 51250337, 76875504
    UNION ALL
    SELECT 76875505, 102500672
) buckets
CROSS APPLY (
    SELECT TOP 1 t.KeyCol
    FROM
    (
        SELECT KeyCol
        , CAST(KeyCol AS BIGINT) KeyColBigInt
        , buckets.bucket_min_value - 1 + ROW_NUMBER() OVER (ORDER BY KeyCol) RN
        FROM dbo.BINARY_PROBLEMS
        WHERE KeyCol >= CAST(buckets.bucket_min_value AS BINARY(64)) AND KeyCol <=  CAST(buckets.bucket_max_value AS BINARY(64))
    ) t
    WHERE t.KeyColBigInt <> t.RN
    ORDER BY t.KeyCol
) ca
ORDER BY ca.KeyCol
OPTION (QUERYTRACEON 8649);

Here is what the parallel nested loop pattern looks like:

Overall, the query does more work than before since it'll scan more rows in the table. However, it now runs in 7 seconds on my desktop. It might parallelize better on a real server. Here's a link to the actual plan.

I really can't think of a good way to solve this problem. Doing the calculation outside of SQL or changing the data model may be your best bets.


Getting errors when I try to group by CTEs I would like to do a group by on object_name on my last cte in this query but I get the following error :

Column 'CTE1.TimesExecuted' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. Msg 8120, Level 16, State 1, Line 85 Column 'CTE1.CountExec' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

WITH CTE1 AS(
    select  Object_name
            , max(Date_of_Object_Analysis) as MaxDate
            ,count(Object_name) as CountExec
            ,min(Date_of_Object_Analysis) as MinDate
            , SUM([Number_Times_Executed]) AS TimesExecuted
            from
    _Admin_Database_Objects_Analysis_Tbl
    --where Object_Name = '_Admin_Client_Bing_GeoCordinates_Tbl'
    Group by Object_name
    )
    ,
    CTE2 AS (
            SELECT c1.Object_name
            ,cast(c1.CountExec as int)*cast(c1.CountExec as int ) as CountMultiplied
            ,c1.CountExec+c1.CountExec+c1.CountExec+c1.CountExec as Addedup
            FROM _Admin_Database_Objects_Analysis_Tbl c2
            iNNER join CTE1 c1
            on c1.Object_name= c2.Object_Name

    ),
    --select * from CTE2

    CTE3 AS (

        SELECT c3.Object_name

            --,c2.CountMultiplied/c1.CountExec as Final_result
            --,c2.CountMultiplied
            ,c1.TimesExecuted/c1.CountExec as New_new_result
            --,c2.Addedup
            ,c1.MinDate
            FROM _Admin_Database_Objects_Analysis_Tbl c3
            INNER join CTE2 c2
            on c3.Object_name= c2.Object_Name
            INNER JOIN CTE1 c1
            on c3.Object_Name = c1.Object_Name
    Group by c3.Object_name

    )select * from CTE3

In your first CTE you grouped by Object_Name and all of the other entries were contained within an aggregate.  In your last CTE that isn't the case.

    SELECT c3.Object_name
        ,c1.TimesExecuted/c1.CountExec as New_new_result
        ,c1.MinDate

I've removed the commented lines for clarity, but you'll notice that none of these are contained within an aggregate. (MIN, MAX, AVG, etc).  If you want to group by c3.Object_name you'll need to put something around the other fields.  For example:

    SELECT c3.Object_name
        ,SUM(c1.TimesExecuted/c1.CountExec) as New_new_result
        ,MIN(c1.MinDate)


Oracle Golden Gate error 00146 I am deploying Oracle Golden Gate to sync data from Sql Server 2008 to Sql Server 2008. One of the extract process have stopped.This is the error:

2014-12-21 17:22:09  ERROR   OGG-00146  Call to VAMRead returned with error stat
us 600: VAM Client Report <[mssqlvam::CGGSApi::PartialUpdateOperation] Updates a
re not supported on tables that do not have TRANDATA added. TranId: 0000:001b210
9, Table: "dbo"."Sys_MaxKey">.

I try to use command:

add trandata Sys_MaxKey

but the problem still exists,and i can't start ext002 (extract process name).

The oracle documentation says this about this Golden gate error

  OGG-00146: Call to {0} returned with error status {1,number,0}: {2}

  Cause: One of the VAM API functions implemented in the  VAM module
  returned an error status on completion of the call to that function in
  the VAM kernel,  after previously reporting a fatal error in the call.

  Action: Contact Oracle Support or, if you are working with an  Oracle
  GoldenGate developer, contact that person


How do I find the FamilyGUID of an exsting database When executing a RESTORE command, I understand that SQL Server ensures that an existing database is not being overwritten by a different database.  This check can be bypassed using REPLACE.

I understand that SQL Server uses the backup file's FamilyGUID to determine whether the database being restored is the same as the database being overwritten.  Is this correct?

I understand that the FamilyGUID of the backup file can be determined using

RESTORE headeronly FROM DISK = N'Q:\MyBackup.bak'

But how can I find the FamilyGUID of the database that is being overwritten?

You can use the catalog view - sys.database_recovery_status

SELECT DB_NAME(database_id) as DatabaseName, database_guid, family_guid
FROM master.sys.database_recovery_status

You can make your restore process more sophisticated by comparing the  family_guid of the backup file and the database to be restored.

Just dump the info of RESTORE headeronly into a temp table and use above query to compare. If they are different then there is a problem.


Excel query execution status I have inherited this huge Excel file that has a connection to the database and it will retrieve data on opening. This file is huge and it takes a long time to update (hours) and I'd like to know where in the process I am . My questions are:

Is there a way to see the query that is running by this Excel connection?
Is there a way to find out execution status, i.e. what percentage is done?

Thank you,

Is there a way to see the query that is running by this Excel connection?

Once you've gotten Excel to finish opening the first time, you can get the query in Excel in a number of ways.  One approach is:

Right-click on any cell in the results
Go to the "Table" sub-menu
Click "Edit Query"

You may also want to go into the "Connection Properties" withing Excel and uncheck the "Refresh data when opening the file" option if you would like to be able to open the file quickly, and then refresh the data on demand.

If you don't want to wait on Excel to finish loading results, you can get the query text using sp_WhoIsActive (more details on that next).

  Is there a way to find out execution status, i.e. what percentage is done?

Not exactly.  You can run sp_WhoIsActive to see information about the running query (including the query text!).

One possible solution to get an "estimated progress" would be to run the query in SSMS after executing the following statement:

SET STATISTICS IO ON;

This will give you the number of reads done by the query for that specific execution.  Next time the query is running, you can compare that to the "reads" column returned by sp_WhoIsActive to see approximately how far along you are.

This isn't an extremely reliable approach if the number of rows returned by the query changes significantly, or if various circumstances cause the execution plan for the query to change.  But if the query is stable, this could give you a general idea of how far a long the query is.


How to Import a BACPAC file to Azure SQL and Overwrite Existing Database? Say I have a local database named MyDatabase. I want to move it to Azure SQL and replace an existing database currently residing there named MyDatabase. I know how to create a BACPAC file locally. I know how to import the BACPAC to my Azure storage account. However, once the BACPAC is in Azure storage, I don't know the preferred way of overwriting the existing MyDatabase database with the copy in storage. I could import the BACPAC file and create a second database, then delete the first, and rename the database just imported. However, is the best or preferred way of doing this?

You are correct.  In Azure you cannot restore on an existing database.

You have to restore with a different name.
Delete old database
Rename new one to old database name.

You have few ways to restore from .BACPAC file.

You can do it directly from your on prem .BACPAC location by using
.\sqlpackage.exe command line tool.

.\sqlpackage.exe /a:Import /sf:C:\filename.bacpac /tsn:ServerName.database.windows.net /tdn:destinationDBName `
 /tu:adminaccountName@serverName /tp:$credentialPW
You can also use the copy that you uploaded in your storage account.

$ResourceGroupName = "RGName"
$ServerName = 'ServerName'
$DatabaseName = "DestinationDBName"

$StorageName = "StorageAccountName"
$StorageKeyType = "StorageAccessKey"
$StorageUri = "http://$StorageName.blob.core.windows.net/swwstoragecontainer/BackpacFileName.bacpac"
$StorageKey = "***********************************"

$credential = Get-Credential

$importRequest = New-AzureRmSqlDatabaseImport -ResourceGroupName $ResourceGroupName -ServerName $ServerName
 -DatabaseName $DatabaseName -StorageKeytype $StorageKeyType -StorageKey $StorageKey
 -StorageUri $StorageUri -AdministratorLogin $credential.UserName -AdministratorLoginPassword $credential.Password `
 -Edition Standard -ServiceObjectiveName S0 -DatabaseMaxSizeBytes 50000

Get-AzureRmSqlDatabaseImportExportStatus -OperationStatusLink $importRequest.OperationStatusLink
In portal you can directly import the .BACPAC file into your server as a database.


SQL Server connectivity and script execution using PowerShell I am designing a PowerShell script that can execute some sql scripts in a folder. Actually this must be done remotely. I am able to connect to the particular server remotely using:

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = servername; Database = dbname; Integrated Security = True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand

But when I use:

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
$server = new-object ("Microsoft.SqlServer.Management.Smo.Server")"SQL01\Instancename"

I am getting an error related with SMO dll. Since I am trying to execute remotely, in this machine SQL Server is not installed so SMO.dll is not available. How can I achieve my aim?

You need to have the accompanying assembly on the machine that you are trying to load on.

If you want to use SMO you have two options:

Install the required files on that machine
Run your script on a box that already has SMO

In order to install SMO without having to install SQL Server, follow the instructions on this BOL reference, Installing SMO.


Return type of statement What is the return datatype of this statement :

INSERT INTO sample_table
VALUES (
    #{id},
    #{Name},
    ...
)
ON DUPLICATE KEY UPDATE
    column_id   = VALUES(column_id),
    column_name     = VALUES(column_name)

Rows_affected is set
Errors could occur

But there is no "datatype" being returned.  SELECTs return resultsets; writes do not.


Retrieving the top 5 users with the most views on their posts in total I have 3 tables:

users
posts
visits

Each time a post gets visited, a new visit will be inserted in the following format:
| id | post_id

My post table has:
| id | text | user_id ...

I'm trying to retrieve the TOP 5 users with the most views on their posts in total.
For example:

User1 -> 4
User2 -> 2

SELECT count(v.id) AS count FROM visits AS v RIGHT JOIN pastes AS p ON v.post_id = p.id RIGHT JOIN users AS u ON id = p.id

But all I get is an array with "count: 12".

nbk has answered already, I have set up your tables in a SQL fiddle, this makes it easier to play around. Consider using this tool for any more questions, because that makes it easier to talk about table structure etc.
http://sqlfiddle.com/#!9/45220f/2
To elaborate more on the question/answer already given: You need to group by a column, so that all columns sharing the same value will be grouped to a single row in the result. Then you can use aggregate functions on this group, like count. There are other aggregate functions like sum (to add numbers in a group). For more information see the documentation at https://dev.mysql.com/doc/refman/8.0/en/aggregate-functions.html .


Centralized master data shared among other several DBs I'm researching and investigating about all possible ways to create a master data database for my portfolio of applications.
To give you a quick overview, an example will come in handy:

I want to create a Users database that several different applications will use. Instead of creating user tables in each application I need to centralize in one point all these users data and their permissions, for each application.

All this applications have MS SQL server as rdbms, so the Replication engine with a single publisher and several subscribers is an option. However, I would like to know if there are other alternatives, and what's more important, if any of these alternatives could be platform independent (for example, a master DB designed in MySQL with two slaves which could be Oracle and MS SQLServer).

Thanks for your help!!

If your environment is currently heterogeneous or you're moving in that direction already, look into using an enterprise service bus to abstract away the implementations of each database. If it isn't, then I would advise against doing so unless absolutely necessary -- this will complicate your environment a lot, especially as you start adding in HA/DR solutions.

Replication should work for SQL Server. I'm not 100% sure of the exact supported features for heterogeneous replication, nor are your requirements very specific in the question, so I can't comment on that.

Master Data Services is a SQL Server only solution (introduced in 2008 R2, which is higher than the version you've tagged the question with), and beyond that I'm not familiar enough with it to say if it would work for your situation.


Snapshot isolation transaction failed in database 'Master' We have a production database running in snapshot isolation level.
The application have a functionality where the user can selected a few records and command it to be processed.
A lot is done while the process run (executes SP, create temp tables,run CURSORS (sic) etc).
All those things takes several minutes to run.

This morning a user selected a big chunk of records and got this message:

  Snapshot isolation transaction failed in database 'Master' because the
  object accessed by the statement has been modified by a DDL statement
  in another concurrent transaction since the start of this transaction.
  It is disallowed because the metadata is not versioned. A concurrent
  update to metadata can lead to inconsistency if mixed with snapshot
  isolation

After a few minutes the user tried again , this time with small chunks of records and was able to workaround the problem.

Maybe it's a performance problem but I was puzzled by the Master DB being the place where the problem ocurred.
I can expect it to happen in the production DB or even in the TempDB.

My Question is? What is the role of the Master DB in this problem? Can it be just a misleading error message anyway?

Fortunately we have trace running in production.
Analysing the data I found an 'ALTER LOGIN' being doing in the 'MASTER' system db.

But it still strange because those commands are from a DIFFERENT APPs using a different connection, technology, etc.
Basicaly costumer got an "COA - Client Online App" and support have an "ALA - Adm Local App".
Locks like support changed the password of a user in the ALA and caused the COA to raise a error in a DB intensive operation.
It's still strange because even the users/costumers in both instances are different and don't share any data.

I was naively expecting two unrelated transactions from two unrelated connections to not crash one another.


Creating a UNIQUE constraint from a JSON object Lets take some example table peoples , that got only 2 fields: id and data(json).

SELECT data FROM peoples ;
{"name": "Adam","pos":"DBA","age":22 }
{"name": "Alice","pos":"Security","age":33 }
{"name": "Bob","pos":"Manager","age":42 }

I want to create constraint for "pos" field, that must be unique.
I've searched over the internet about JSON constraints but no results.

How can I handle this problem ?

First and foremost: I agree with both the comments of @a_horse_with_no_name and @dezso: you should normalize your data. JSON is not for that.

However, if some  reason I cannot fathom really makes this an advantage, it is possible:

Create an expression based UNIQUE INDEX:

CREATE UNIQUE INDEX people_data_pos_idx ON peoples( (data->>'pos') ) ;

If, at this point, you try to insert the following piece of data into your table (with an already existing ->>pos):

INSERT INTO peoples(data)
VALUES
    ('{"name": "Eve", "pos":"DBA", "age":34}') ;

You get this as a response:

ERROR: duplicate key value violates unique constraint "people_data_pos_idx"
SQL state: 23505
Detail: Key ((data ->> 'pos'::text))=(DBA) already exists.

NOTE: I've assumed that data.pos will always be a string. If you want to generalize, you can use ( (data->'pos') ) instead. You would index then a JSON(B) expression instead of a text. Check JSON Functions and Operators.


SQL Server options that influence log buffer flushing or commits-per-second performance? I am investigating two SQL Server 2014 instances, both running identical software, both running identical hardware (VMs), both loaded with the same test database, but one with hugely greater performance on a microbenchmark.
The benchmark essentially runs the following in a loop:
BEGIN TRANSACTION
    INSERT INTO [tablename] ([data])
    VALUES (CONVERT(NVARCHAR(2000), NEWID()))
COMMIT

One server can achieve approximately 200 transactions per second. The other, approximately 2000 transactions per second.
Since this transaction is doing so little, I hypothesized that the bottleneck could be disk I/O in flushing the log buffer. I thought the DELAYED_DURABILITY option was sure to explain the difference. But alas, it was set to Disabled on both servers. An audit of other options has turned up no other apparent configuration differences.
So my questions are:

Is there any other way that transaction durability could be delayed when that option is set to Disabled, e.g. a master override config file somewhere?
Is there any other way that log buffer flushing could be influenced?
Are there any other settings I should look into that might explain the wide variance in performance that I am seeing?

N.B. I'm aware that this is a microbenchmark and is not representative of the application workflow - however, real-world testing has shown a performance difference too, and this transaction loop seems to be the minimum reproducible example that shows a difference.
Additional details
Looking at the wait stats, WRITELOG is top of the list.

both running identical hardware (VMs)

If the performance of this minimal test is that drastically different, and you have thoroughly confirmed that the software (and instance / database level settings - for example the checkpoint-related settings like indirect checkpoint, recovery interval, target recovery time, etc might affect a benchmark like this one) is the same, then the statement about the hardware being the same is probably not exactly right.
You mentioned WRITELOG is your highest wait.  With this workload, WRITELOG will almost inevitably be the highest wait, since that's all you're doing.  Is the difference in WRITELOG waits between the two servers large enough to account for the 10x difference in transactions per second?
If that wait is significantly higher on the slow server, then it's possible that, despite the same hardware specs being specified at the VM level, the "path" to the I/O system is slower on the second VM.  Or the underlying storage is slower.

Check out the sys.dm_io_virtual_file_stats DMV to see if the slow server is experiencing slower writes or more frequent / longer stalls than the fast server.
Check the SQL Server error log to see if there are any "SQL Server has encountered X occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [M:\SomeDataFile.mdf] in database [SomeDatabase] (5)." error messages.

If you're seeing slowness reported in those places, you'll need to work with the VM administrator -  to see if there is a difference in the underlying storage, or the access to the storage (if it's going out to a SAN).


Safe to Shrink Database in this Scenario? For years I've had it drilled in to me:

  Never ever shrink a database file.

I understand why of course, and I never do, mainly because I never need to.

Now though, I have a database with an .mdf file that is 800GB. It was a system designed to collect data over a certain amount of time, with no retention period.

A retention period of 90 days was recently placed on this data, and as such the developer has cleared about 3 years worth of data from the tables. Data and Indexes now total roughly 70GB, so now I have an .mdf file which is grossly over-sized.

I want to shrink it to reclaim some of that valuable disk space.

I'm planning to perform a shrink prior to rebuilding indexes and updating statistics (i.e the weekly maint. plan)

I'm not breaking any DBA laws here am I? I assume this is an acceptable scenario in which to perform a SHRINK as it is a true one off?

Thanks

This should answer your question : http://www.sqlskills.com/blogs/paul/why-you-should-not-shrink-your-data-files/

Quoted:

  So what if you do need to run a shrink? For instance, if you’ve
  deleted a large proportion of a very large database and the database
  isn’t likely to grow, or you need to empty a file before removing it?

  The method I like to recommend is as follows:

Create a new filegroup
Move all affected tables and indexes into the new filegroup using the CREATE INDEX … WITH (DROP_EXISTING = ON) ON syntax, to move the

  tables and remove fragmentation from them at the same time
      Drop the old filegroup that you were going to shrink anyway (or shrink it way down if its the primary filegroup)

  Basically you need to provision some more space before you can shrink
  the old files, but it’s a much cleaner mechanism.

  If you absolutely have no choice and have to run a data file shrink
  operation, be aware that you’re going to cause index fragmentation and
  you should take steps to remove it afterwards if it’s going to cause
  performance problems. The only way to remove index fragmentation
  without causing data file growth again is to use DBCC INDEXDEFRAG or
  ALTER INDEX … REORGANIZE. These commands only require a single 8KB
  page of extra space, instead of needing to build a whole new index in
  the case of an index rebuild operation.

So if you have multiple filegroups you can use this technique, otherwise you can make a shrink and then do a rebuild of your indexes.


RMAN backup set, nothing else, how can I duplicate? I have what appears to be an RMAN backup set, it has been multiplexed in 32 files, each containing 2-3 dbf's inside. The backupset refers to an SPFILE file, but it was not included in the set, the backup piece containing just that file was not included.

I have no catalog, no infrastructure, and no way to connect to original database.  In addition the creators of the backupset have been evading my requests, I'm not sure why exactly but it appears to be business reasons.  In other words this is what I have, I cannot get anything better than what I've got right now.

I have an Oracle enterprise server, with the data in a backup directory.
What is the proper way to proceed?  Is it possible to duplicate from an auxiliary backup data TO a fresh blank database?  Meaning, is it possible to use an auxiliary as a primary?

If you know the sid name, you can copy a pfile over and edit it to change the values to match the database name. Make sure that you set B_CREATE_FILE_DEST to where you want the data files.

Then do a startup nomount on the database. In RMAN do "CATALOG START WITH '/full_path_to_your_backup';" You can then use DB_FILE_NAME_CONVERT to convert the file names to be in the right directory.

You can then try to restore a control file. try list backup; and restore the oldest control file. Mount the database and try to restore the database.

It could be that the reason why the people who gave you the backup are avoiding you is because they know the backup is incomplete. But you have to try to restore what you can, then at least when you go back to them you can ask a more specific question.


Incremental statistics: find out which partitions have been resampled TL;DR: Is it possible to find out which partition(s) have been resampled and which have not when incremental statistics are used? Platform is SQL Server 2014 Enterprise edition.

The long version with some background information is like so.

Assuming a fairly typical DW environment, there is a partitioned table. The partitioning is based on a date column. This is done as staging data is loaded on separate table and after pre-processing, partition switching is used to move data into the production fact table. Oh, and a clustered columnstore index is in use. There are about a thousand partitions used. The DB is running on a virtual machine.

There are some 7.5 gigarows (100 GB) in the fact table. Daily growth is about five megarows. This is way too small a growth rate to trigger automatic statistics update, save trace flag 2371 (which haven't been tried).

The knee-jerk developer reaction to outdated statistics was updating them. For 7.5 gigarows, full update for all the stats takes some five hours. For a single statistics update, the processing performance is around 20 minutes or 90 megarows per second.

As the system is on a VM platform, business rules limit its costs. Neither memory nor IOPS are easily increased. Five hours update job is way too slow to be included on nightly ETL process, so either the stats stay outdated, are updated on unexpected time or will be updated in a maintenance window.

As the SQL Server is version 2014 Enterprise edition, it supports incremental statistics which sound just like the solution. After converting the stats to incremental ones, processing a single stat for single partition takes only 20 seconds. Grand total for newly-switched partition is about five minutes. This sounds great and certainly fits in the ETL process.

The thing that I wonder is how to manage incremental stats in partition switched environment. Assuming the stats are converted and updated as incremental on date D, how does one find out unprocessed partitions on, say, date D+2? Updating stats in ETL process is trivial, as the switching process obviously is aware of the partition id. But if there are partitions that are not resampled, how does one find those?

Last update for stats can be found from sys.dm_db_stats_properties
Partition numbers are available from sys.partitions
Partitioning function values are in sys.partition_range_values

One could pick last update date L for a stat and compare it to today's date T. Then calculate which partition id L points and wheter it is the same as T's. Then proceed with update for all the partition ids [L, T). This sounds tricky and error prone, so is there better a way? A DMV that shows which partitions are used for resampling would be nice, but there isn't one, is there?

My first question would be to ask why you're actually using incremental in the first place. Here's an answer that I posted regarding incremental statistics, a blog post by Erin Stellato that illuminates one of the primary complaints and pitfalls with incremental statistics (they're not used at the partition level by the optimizer), and two blog posts by me that work through evaluating any potential use case for incremental statistics.

Having said that, to know when a partition's stats have been sampled you can use an undocumented DMF (sys.dm_db_stats_properties_internal()) to get partition-level information. I have a comment on this blog post that describes how to understand the hierarchy at a fairly high level.

select
    sysdatetime(),
    schema_name = sh.name,
    table_name = t.name,
    stat_name = s.name,
    index_name = i.name,
    leading_column = index_col(quotename(sh.name)+'.'+quotename(t.name),s.stats_id,1),
    s.stats_id,
    parition_number = isnull(sp.partition_number,1),
    s.has_filter,
    s.is_incremental,
    s.auto_created,
    sp.last_updated,
    sp.rows,
    sp.rows_sampled,
    sp.unfiltered_rows,
    modification_counter = coalesce(sp.modification_counter, n1.modification_counter)
from sys.stats s
join sys.tables t
    on s.object_id = t.object_id
join sys.schemas sh
    on t.schema_id = sh.schema_id
left join sys.indexes i
    on s.object_id = i.object_id
        and s.name = i.name
cross apply sys.dm_db_stats_properties_internal(s.object_id, s.stats_id) sp
outer apply sys.dm_db_stats_properties_internal(s.object_id, s.stats_id) n1
where n1.node_id = 1
    and (
            (is_incremental = 0)
               or
            (is_incremental = 1 and sp.partition_number is not null)
         )
    and t.name = '<<TABLENAME>>'
    and s.name like '<<STATNAME>>%'
order by s.stats_id,isnull(sp.partition_number,1);


Deleting duplicate rows with condition(Using SQL and LINQ) I have a table with 5 columnsID,CPT4,CPT4Mod,ItemCharge,Eff_Date.

Deletion Condition:

When rows exists with duplicate CPT4and CPT4Mod field values but different ItemCharge and Eff_Date values, exclude all but the most recent Eff_Date records.

I need a query which can achieve this in two scenarios.

When CPT4Mod is given by the user.
When  CPT4Mod is not given by the user.

Later I need to achieve the same using LINQ.

WITH KeepLatest AS
(
  SELECT *,
     RANK() OVER (
       PARTITION BY CPT4,CPT4Mod
       ORDER BY Eff_Date DESC, ItemCharge) as Rnk
  FROM dbo.YourTable
 )
 DELETE KeepLatest
 WHERE Rnk > 1
 ;

By using RANK() instead of ROW_NUMBER() you will avoid deleting anything if you have a row which is identical across all four columns - although I'm not sure why you'd want that.

If you know the CPT4Mod value, include: AND CPT4Mod = @CPT4Mod


Is it possible to import a single mysql database directory? I am somewhat new to mysql. I created some databases recently and, to back them up, I just copied the folders with those database names in the mysql data directory (i.e containing only db.opt, .frm, and .ibd files). Unfortunately, my computer crashed. I now realize I did not back up the databases in the correct way (as I understand now, I should either have backed up the entire mysql data directory or used mysqldump). However, is there any way to recover the information from these data folders or is it lost?

OK, spurred on by RolandoMySQLDBA's comment (I didn't know what to look for earlier), I was able to solve this.

Ultimately, I used this blog post to recover the table data from the .frm files.

Then, I used the technique here to import the ibd data.


How to do Row Level Security in SSRS 2008 R2 (or 2012) with Active Directory Groups? I would like to create a single report that filters data based on what Active Directory group the user is in.

Through various searches I see that Row Level Security is possible in SSRS in the Report
Model.  However, I can't seem to find any instructions for SSRS 2008 R2.  They all seem to be for 2005 and it seems that Microsoft has removed any sort of feature like this in 2008 along with most of their articles.

Is this even possible anymore?  If not, what's the alternative?  I don't want to have to create a report per group and I don't want people to see data that is not available to them.

Do I need to somehow handle this in a View?  If so, how do I get TSQL and AD to talk?  Will the user's SSRS credentials be passed to the view?

SSRS in the Report Model is available in sql 2008 R2, but not in express edition

check
http://msdn.microsoft.com/en-us/library/ms365305(SQL.105).aspx

and here is how to apply it on sql 2008 R2
http://msdn.microsoft.com/en-us/library/ms365343(SQL.105).aspx

and if you want to create a view and get TSQL and AD to talk

you can use the following functions to get username

select suser_name() as 'usrname'
or select suser_sname() as sname
or select system_user as system_

or if you want to get data from ADSI, you will need to create a linked server connection to ADSI and get users info
http://blog.namwarrizvi.com/?p=254
http://www.kodyaz.com/articles/active-directory-services-queries-using-openquery.aspx

and use the where clause

finally check this article "SSRS 2008 R2: Implementing Row-level Security"
http://www.sqlservercentral.com/blogs/sqldownsouth/2011/10/13/ssrs-2008-r2_3A00_-implementing-row_2D00_level-security/


SQL Server "empty table" is slow after deleting all (12 million) records? I have a SQL Server 2008 instance with approximately 150 columns. I have previously populated this table with approximately 12 million entries, but have since cleared the table in preparation for a new data set.

However, commands that once ran instantly on an empty table such as count(*) and select top 1000 in SQL Management Studio now take eons to run.

SELECT COUNT(*) FROM TABLE_NAME

took over 11 minutes to return 0, and SELECT TOP 1000 took almost 10 minutes to return an empty table.

I've also noticed that the free space on my hard drive has literally disappeared (down from about 100G to 20G). The only thing that happened between was a single query I ran:

DELETE FROM TABLE_NAME

What in the world is going on?!?

You've already been told about why TRUNCATE would be so much faster/better/sexier than DELETE, but there's still a question left to address:

Why is SELECT slower after DELETE completed?

That is because DELETE has only ghosted the rows. The table is just as big as when it had 12M rows, even though it has none. To count the rows (0), it takes just as much time it took to count 12M rows. In time the ghost cleanup process will garbage collect these ghosted records and deallocate pages that contained only ghosts, and your SELECTs will speed up. But right now if you check Skipped Ghosted Records/sec in perfmon is probably skyrocketing during SELECT COUNT(*). You could also speed up things by rebuilding the table: ALTER TABLE ... REBUILD.

TRUNCATE would had also taken care of this problem, as it leaves no ghosts behind.

See also Inside the Storage Engine: Ghost cleanup in depth.


View operation timestamps for long running operations in PSQL console When I do long running queries, I tend to use tmux and a psql console. It will not show me any output of when some operation was started or completed.
How can I get the psql console to show some timestamps?

Clarification:
I'm hoping for a non-scripting solution, just in the psql console. (Or does nobody use that console?) I'm maybe naive but still trying to do most of the database stuff using the database itself and its tools (using procedures, etc.)

There are various options for retrieving timing information while using psql.

The \timing command.
In scripts you can use

\echo `date`

to insert the date in the output.

You can have the date in prompt strings using eg

\set PROMPT1 = '%`date` %n@%m:%/%R%# '

For both 2 and 3 you can supply arguments to date to get the date in the format you want.

If using psql non-interactively, you can pipe the output through ts (from moreutils) or through awk '{ print strftime("%Y-%m-%d %H:%M:%S"), $0; fflush(); }'(GNU's version of awk is needed) to prepend a timestamp to each line of output.


R Script permissions error I executed this to check to see if I have write permissions on a folder and it fails (returns -1). I am a system admin and SQL admin.  I have full access to this directory but the R Script says I do not.  If I change the mode of the script to check for read or execute permissions, it succeeds (returns 0).  What else can I check to fix this?

EXECUTE sp_execute_external_script
       @language = N'R'
      ,@script = N'OutputDataSet <- data.frame(file.access("C:/Program Files/Microsoft SQL Server/MSSQL13.MSSQLSERVER/R_SERVICES/library/", 2));'
WITH RESULT SETS (([DefaultLibraryName] VARCHAR(MAX) NOT NULL));
GO

I think you 'might' need to grant the proper authority on that folder to the Windows group SQLRUserGroup.

I have a test SQL Server instance where R-Services is installed.  I took your script and changed the directory to be C:/Scripts (just as a test).

EXECUTE sp_execute_external_script
       @language = N'R'
      ,@script = N'OutputDataSet <- data.frame(file.access("C:/scripts/", 2));'
WITH RESULT SETS (([DefaultLibraryName] VARCHAR(MAX) NOT NULL));
GO

I got the same -1 return code as you.

I then granted FULL access against C:/Scripts to the Windows group SQLRUserGroup and ran the same script code.  It executed successfully with a zero (0) being returned.


How do I set a database back to 'Restoring...'? Sorry if this is a noob question. I have transaction log shipping set up for a failover scenario. All the databases are in 'Restoring...' state until I do restore database x with recovery. At this time I disable log shipping.

Once I have resolved the situation and I want to enable log shipping and carry on again. Do I need to run a command to set them back to Restoring or do I leave it and the log shipping will automatically sort it out?

It's not quite as simple as re-enabling log shipping as you need to do some initial setup to be able to switch from one instance to another.

This article helps with that

You also need to perform some clean-up before switching back to the original primary as it will be holding databases that are now out-of-date.

If your hardware on the primary and secondary are the same and there is no performance loss or maintenance headache in staying on the secondary server then I would recommend doing that until the next failure as the step of failing back is not needed.


Postgres memory settings (RAM, work_mem, etc.) for complex text searches on indexed tsvectors I know very little about Postgres memory settings.
I have developed a paid-access database, which mostly stores text (and some metadata)
2 tables have 10 millions rows with little text (1 paragraph for each row)
2 other tables have 100,000 rows with the full texts (40 pages for each row)
Overall, the database size is about 10 GB.

The purpose of the project is to enable users to make complex text searches (sometimes combined with metadata queries such as title or data, which are unproblematic).
Text searches are made on indexed tsvectors in the tables described above, which are automatically filled a trigger.
They are rather fast on my personal computer (MacBook Pro 2019 - 2,6 GHz Intel Core i7 6 cores - RAM 16 GB 2400 MHz DDR4).
Unfortunately, as I am going into production, they are rather slow on the remote ODS server (RAM 2 GB),

Hence my question:
What are the ideal memory settings for complex text searches on indexed tsvectors?

Does it help when the server RAM size is higher than the database size (10 GB in my case), so that the database can be entirely stored in RAM?

Should I increase work_mem, or other settings?

As a side question, does it help to reduce the number of tables interrogated, or is that irrelevant?
(say, if users were only allowed to interrogate the tables with paragraphs, and not the tables with full texts)

Thank you very much for your help !

Welcome!

More RAM may make a big difference, but it's worth looking into the configuration and some specific queries to see where they're being constrained.

Here's a couple of next steps:

Compare your current settings to those recommended by PGTune and try out the recommendations if there are big differences
Use explain (ideally explain (analyze, buffers)) locally and on your remote server to see if it's memory constrained, or something else

Feel free to edit your question with more information from what you find! If you do, comment on this anwer to notify me :)


How can I split Date column values in Access into 2 columns? I have a date column that stores values for begining to end date as:
070314-073114
So I need to split it to 2 columns that has the Start Date as 070314 and End Date as 073114, any suggestions?

This database is still in Microsoft Access, and eventually after cleaning it up, we will move it to SQL Server.

Thanks in advance

ALTER TABLE table ADD COLUMN Start_Date TEXT(6)
ALTER TABLE table ADD COLUMN End_Date TEXT(6)

update table
set Start_Date = Left(column,6),
End_Date = Right(column,6)


How would I undo or edit the last line of a multi-line query in mysql? I often make mistakes when entering multi-line queries in mysql. Is there an easier way to undo the last line instead of retyping the entire query?

mysql> SELECT age, name
    -> FROM people
    -> WHERE age > 20
    ->

For example, I meant to type age < 20, is there a way to quicky fix that? Normally I would ctrl + C and use the up arrow key to re-enter first two lines quickly, but I feel like there should be an easier way.

I tried reading documentation and read some forums but I couldn't find anything. It should be a new feature if it doesn't exist.

Type edit.

mysql> edit

It would open a vi editor. You can make any number of mistakes and undo them.
Once done with your multiline query, exit(:wq) out the editor.
Type ; to execute your query.

mysql> ;


Is it possible to match two different Data types in PostgreSQL? I want to compare two field values from different tables of PostgreSQL both are of different data types that is

CREATE TABLE IF NOT EXISTS departments(
  _id SERIAL NOT NULL UNIQUE PRIMARY KEY,
  departmentName text,
  active BOOLEAN
);

CREATE TABLE IF NOT EXISTS departments_users(
  _id SERIAL NOT NULL UNIQUE PRIMARY KEY,
  department_id integer[]
);

INSERT INTO departments(_id,departmentName)
VALUES
    ( 1, 'aa' ),
    ( 2, 'bb' ),
    ( 3, 'cc' ),
    ( 4, 'dd' );

INSERT INTO departments_users(_id,department_id)
VALUES
  ( 1, ARRAY[1,2,3] );

in the above 2nd table am using department_id which are all present in departments table but in second table am storing it in array if i want get the data which has the common department id
expected output should be:

_id | departmentName
-----------------------
1   | aa
2   | bb
3   | cc

So is it possible to match 2 different datatypes?

Yes, you can do this with either ANY as mentioned in @ypercube's comment, or with <@ (the containment operator)

SELECT *
FROM departments
JOIN departments_users
  ON departments._id = ANY(department_id);
 _id | departmentname | active | _id | department_id
-----+----------------+--------+-----+---------------
   1 | aa             |        |   1 | {1,2,3}
   2 | bb             |        |   1 | {1,2,3}
   3 | cc             |        |   1 | {1,2,3}
(3 rows)

If that doesn't work with an index you may need.

SELECT *
FROM departments
JOIN departments_users
  ON department_id @> ARRAY[departments._id];


Backup/Restore Users/Passwords/Privileges I am moving from one server to another and I want to backup all databases + users/privileges/passwords from my MySQL Server. I found to backup a database using mysqldump, but I cannot figure out, how to backup all users and the given privileges. Is there a way to achieve this or do I have to set this newly up on the new server?

The 'mysql' database contains users/privileges/passwords. So take the dump of mysql database along with other databases

mysqldump [options] --all-databases > all_databases_dump.sql

mysqldump -u root -p mysql user > user_table_dump.sql

These mysql database tables contain grant information

user: User accounts, global privileges, and other non-privilege columns.

db: Database-level privileges.

tables_priv: Table-level privileges.

columns_priv: Column-level privileges.

procs_priv: Stored procedure and function privileges.

After restoring cross check with

select Host, user, password from user ;

SHOW GRANTS FOR 'user'@'localhost';


What's the practice to generate long user id like those on Facebook? Now I am about to launch a site and I have trouble coming up with a way to create long user id(e.g. 4000002).

Do I set id to auto-increment(Starts at 0) and then add something(e.g. 4000000) to this id and put this new number in a new column in my table?

Does any body have a better practice to achieve this?

Thanks in advance!

Why not just set the auto_increment to 4000000 and let it count from there?

ALTER TABLE `table` AUTO_INCREMENT=4000000;

Also, make sure your id column can fit large numbers (bigint or similar)


Tracking events in a task database In designing a task database with the usual set of tables (Users, Tasks etc), I'm wondering if there is an accepted way to track changes to fields in the task table?

I could just provide a text log of what changed and when, but can I somehow store the old and new values in their natural type? I could have one field for each datatype (int, datetime etc) and then a bitmask to denote which one has relevant data in it, but it seems like it would be a bit clunky to report on. Another idea was to have one table for each datatype (IntEvents, DateTimeEvents etc) but that looks problematic too. Presumably this is a fairly common problem, has anyone else solved it in an elegant way? It needs to be compatible with SQL Server and MySQL at least, and Postgre as well ideally.

EG:

TABLE: Tasks
ID int pk
Name varchar(255)
Description text
Created datetime
DueBy datetime
Creator int -> users
Owner int -> users
Status int -> statuses
Priority int -> priorities
// etc

How can I create an events table to track changes to all these different datatypes?

The simple text log table could be:

TABLE: Events
ID int pk
UserID int
TimeStamp datetime
Fieldname varchar(32)
OldValue varchar(255)
NewValue varchar(255)

But this is a bit difficult to report on (eg show me all the events where a DueBy date was moved back by more than N days)

I've looked at table design to track significant events associated with an item but I'm not sure it's quite the same problem (unless I've misread it).

You can create a copy of the original Events table, e.g. EventAudit, with an additional field for the date the change was made and perhaps a field identifying the user who made the change, and insert old values to that table. It needn't have a primary key (although it could): EventID would be a foreign key from Events.

This design would enable reporting on changes the same way the original tasks are reported, and you can index any number of columns in the table for that purpose. You may need to consider database size/disk space used by this table; if there are any BLOBs in Events you would probably want to insert them into EventAudit only if they have changed. Otherwise I suggest copying the entire row before the change.


Pulling data into Access from an external API [Apologies in advance if this has been asked an answered elsewhere.]

I have recently dived into Microsoft Access 2016 to handle a relatively small (but still too big for Excel) database with somewhat sensitive (read should not live on the internet) information. So far so good.

What I would like to do is be able to pull down information from a publicly available API to update a few columns of a table.

Ex:
For each entry in this table take field A and submit it to the web API.
The API would then return a list of numbers which I then parse and plug into fields B, C, and D.

QUESTIONS:

Is this even possible inside Access?
Where should I look/what should I search for to figure out how to do it?

I would rather not have to pipe it through Excel or some third party service if possible. I am comfortable programming and don't mind picking up a new language but I can't seem to figure out where to start. Any help from "hit the _____ button." to "Searching for _____ will get you started." would be appreciated.

The most straightforward way to have Access VBA interact with a web service would be to use a MSXML2.ServerXMLHTTP object like this:

Option Compare Database
Option Explicit

Sub WebRequestExample()
    Dim httpReq As Object, response As String
    Set httpReq = CreateObject("MSXML2.ServerXMLHTTP")
    httpReq.Open "GET", "http://checkip.dyndns.com", False
    httpReq.Send
    response = httpReq.responseText
    Set httpReq = Nothing
    Debug.Print response
End Sub

In this case the response string contains

<title>Current IP Check</title>Current IP Address: 192.168.53.49

If the web service returns a relatively short and straightforward response then you could just parse it yourself.


Why should I create an ID column when I can use others as key fields? Possible Duplicate:
  Why use an int as a lookup table's primary key?

So far, I'm accustomed to creating an ID column for every table and it is practical in a way that it makes me not think about decision making about primary key theories.

The professor in my university suggested the class to make primary keys from one or more fields which make up to one unique info about each column. And yes, I want to have a habit of applying natural keys instead of surrogate keys. On Wikipedia, the advantages and disadvantages of surrogate keys are listed, I strictly recommend This Article

I have seen people use integer ID fields for everything and nobody judges this method because

it "looks" efficient
a number field is used and it looks cooler because of its size per
row in memory

I am beginning to think an extra ID field is just creating redundant data with no actual benefit. So why should I create an ID column when I can use other columns as key fields?

If your ID field is 32 bits, it's equivalent to 4 ASCII characters
already.
If your Id field is 64 bit integer, it's 8 characters string so it
doesn't actually save that much of  memory (what implied here is the memory used on comparison. extra id column is already adding to the memory used (both HDD and RAM) )
An extra ID field doubles your indexing cost because you will also
    index a unique field you can use as a primary key.
You make extra joins if you need the data you could have used as a key field, for example, if you stored a unique user ID in one
blog post, to show the name of the author, you make a join query, if
your key field was the author's name, you don't need to join because
you store the relevant data in the blog post table. a foreign key
field with meaningful data reduces the need for subquery or join

Creating an extra id field "adds" to the memory load, it's not a
replacement of a unique string field, you are not replacing a
char-varchar field with an integer, you are adding an extra
column and it creates extra data flow. so any comparison of
data store should be done between "string" and "int+string". adding
an integer id field does not save space.

on the other hand

assigning a primary key data which gets value from user input, can be
problematic because people can enter, for say, their social security
number wrong and the actual person who wants to register won't be
able to register because of the unique policy. This can be
circumvented by adding extra digit or digits to the original number.

Extra resources:

Comparison of Natural vc Surrogate keys

My conclusion from reading articles is that I should use natural keys whenever possible instead of skipping thinking about natural keys and using surrogate keys each time, as if it is a standard.

1 - It's faster.  A JOIN on an integer is much quicker than a JOIN on a string field or combination of fields.  It's more efficient to compare integers than strings.

2 - It's simpler.  It's much easier to map relations based on a single numeric field than on a combination of other fields of varying data types.

3 - It's data-independent.  If you match on the ID you don't need to worry about the relation changing.  If you match on a name, what do you do if their name changes (i.e. marriage)?  If you match on an address, what if someone moves?

4 - It's more efficient  If you cluster on an (auto incrementing) int field, you reduce fragmentation and reduce overall size of the data set.  This also simplifies indexes needed to cover your relations.

EDIT

To the specific points you just added:

1 and 2 - It's still much quicker to compare an int than a string, space considerations aside.  You are also conveniently ignoring overhead needed to store length of variable length fields (normally 2 bytes per field per row).

3 - If you cluster on the ID field then it doesn't add anything extra.  It SAVES space since you are using a more efficient row id.

4 - And then when that person changes their username your links all break.

5 - You really don't know what you are talking about here.  You do have to store the data, that's correct, but it's much more efficient to index and JOIN on the int than on a combination of other fields.


Efficient query to get last row group by multiple columns I have a table like the following:

spread_id serial NOT NULL,
game_id integer NOT NULL,
sportsbook_id integer NOT NULL,
spread_type integer NOT NULL,
spread_duration integer NOT NULL,
home_line double precision,
home_odds integer,
away_line double precision,
away_odds integer,
update_time timestamp without time zone NOT NULL,
game_update_count integer NOT NULL

I'm trying to get the last row inserted (max update_time or game_update_count), for each group of sportsbook_id, spread_type, spread_duration, and game_id.

The following query gets me close, but I am not able to select the lines/odds without Postgres complaining.

SELECT
     spreads.game_id, sportsbook_id, spread_type, spread_duration,
     MAX(game_update_count) AS game_update_count
FROM spreads
LEFT JOIN schedule ON
        schedule.game_id = spreads.game_id

WHERE date >= '2012-01-01' AND date <= '2012-01-02'
GROUP BY
     spreads.game_id, sportsbook_id, spread_type, spread_duration
ORDER BY
    spread_duration, spread_type, sportsbook_id, spreads.game_id,
    game_update_count DESC;

Anyone have any thoughts on a better approach to take?

The simplest way in Postgres is with DISTINCT ON:

SELECT DISTINCT ON (1,2,3,4)
       sp.game_id, sportsbook_id, spread_type, spread_duration, game_update_count
FROM   spreads sp
LEFT   JOIN schedule sch USING (game_id)
WHERE  date >= '2012-01-01'
AND    date <= '2012-01-02'
ORDER  BY 4,3,2,1, game_update_count DESC;

Details:

Select first row in each GROUP BY group?

The numbers are just syntax shorthand referring to the ordinal position of SELECT items.

If game_update_count can be NULL, you'll want game_update_count DESC NULLS LAST.

PostgreSQL sort by datetime asc, null first?


Using DISTINCT in window function with OVER I'm trying to migrate a query from Oracle to SQL Server 2014.

Here is my query which works great in Oracle:

select
count(distinct A) over (partition by B) / count(*) over() as A_B
from MyTable

Here is the error i got after tried to run this query in SQL Server 2014.

Use of DISTINCT is not allowed with the OVER clause

Anyone know what is the problem? Is such as kind of query possible in SQL Server?
Please advise.

This gives the distinct count(*) for A partitioned by B:

dense_rank() over (partition by B order by A)
+ dense_rank() over (partition by B order by A desc)
- 1


Query single column, all rows in Postgres 2D Array Is there any way to fix the query below so I don't have to specify an arbitrary max row count?

DROP TABLE IF EXISTS array_test;

CREATE TABLE array_test (
  id serial primary key,
  name text,
  array_column integer[][]
);

insert into array_test (name, array_column) VALUES
  ('rowa', ARRAY[[1,2,3],[4,5,6]]),
  ('rowb', ARRAY[[4,5,6],[7,8,9],[10,11,12]]);

This does what I want, but I don't like hard-coding 9999 as an arbitrary max row count:

select array_column[1:9999][2:2] from array_test;

You can use the functions array_upper() and array_lower() to identify array dimensions dynamically:

select array_column[1:array_upper(array_column, 1)][2:2] from array_test;

While being at it, if arrays can have non-standard dimenstions, it would be safer to use

SELECT array_column[array_lower(array_column, 1):array_upper(array_column, 1)][2:2]
FROM   array_test;

One subtle difference: While your code returns an empty array for an empty array in the base table, my alternatives returns NULL since array_lower() and array_upper() return NULL. (For NULL in the base table, all return NULL.

I suggest to use a more revealing test case, so you don't miss corner cases:

INSERT INTO array_test (name, array_column) VALUES
  ('rowa', ARRAY[[1,2,3],[4,5,6]])
, ('rowb', ARRAY[[4,5,6],[7,8,9],[10,11,12]])
, ('rowc', ARRAY[[1,2,3]])
, ('rowd', ARRAY[[1],[2],[3]])
, ('rowe', '{}')
, ('rowf', NULL);

There is another "hack" to do exactly the same as your original, but never cut off elements. Use the maximum possible array bounds (which are integer numbers):

SELECT array_column[-2147483648:2147483647][2:2] FROM array_test;

Inspired by Daniel's answer on my related question:

Normalize array subscripts for 1-dimensional array so they start with 1


Performance: ALTER TABLE with default value vs. iterating over all records to set new value For adding values to a new column, I have the following options:

Using default to add value during ALTER TABLE command.
Doing ALTER TABLE without default, then fetching all the records and iterating over all of them to save them with the new value.

I am interested to know how much performance difference should I expect?

I am adding a new column to a table with 14+ million records.

The column needs a default, unfortunately, I'll need to change a lot of code to allow putting default value. However, it's easier to iterate over all the records and save them with the value for the new column.

Before making my decision, I am looking for metrics of performance differences between the two options.

I am working on an application to be used in multiple machines, different environments and DBMS. I wouldn't be installing the application, it's the client's job. The application has scripts to aid them with the same. I understand that without system specifications it's hard to make predictions.

Is making broad predictions possible? As in, accessing all records requires O(n) time, similarly information on time requirements of using default during ALTER TABLE and other processes will also help.

The primary difference you need to be concerned about is whether or not a table lock is acceptable for an extended period of time.  One approach acquires a lock against the table and one acquires locks on rows (if done correctly).

You might also consider, if possible, creating a view against the original table that also includes the new column (and value).  Export the view data using BCP.  Then, drop and recreate the table with the new column and use BCP to import.  You could probably take advantage of being minimally logged.

Many popular databases offer a free version for non-production use. If you want to understand the performance difference your best bet is to run tests on the most common platforms that you expect your clients to use.


Query: Most recent record for unique combination of two fields in MS Access 2010 I'm trying to run a query on some traffic volume data but I'm having some trouble.
I would like to pull out the most recent data for each section of road, but this is proving more complicated than expected.

Here is an example of the data I am working with:

S_ID | DATE      | DIRECTION | AADT
1001 | 9/16/2013 | North     | 700
1001 | 9/16/2013 | South     | 600
1001 | 1/30/2011 | NULL      | 1093
1001 | 2/31/2009 | NULL      | 1091
1211 | 1/17/2012 | NULL      | 945
1235 | 1/23/2014 | North     | 500
1235 | 6/19/2014 | South     | 350
1235 | 5/10/2011 | South     | 354
1235 | 5/10/2011 | North     | 399
1301 | 9/16/2013 | East      | 700
1301 | 4/16/2012 | West      | 600
1301 | 1/30/2014 | NULL      | 1093

I am interested in the returning the most recent AADT (Annual Average Daily Traffic) value for each SectionID and direction.
In the data above, I would want the values below returned:

S_ID | DATE      | DIRECTION | AADT
1001 | 9/16/2013 | North     | 700
1001 | 9/16/2013 | South     | 600
1211 | 1/17/2012 | NULL      | 945
1235 | 1/23/2014 | North     | 500
1235 | 6/19/2014 | South     | 350
1301 | 1/30/2014 | NULL      | 1093

I have the following code:

SELECT  a.*
INTO    recent_data
FROM    primarytrafficcountdata a
INNER
JOIN    (
        SELECT  section_id,
                direction,
                MAX(date) AS maxdate
        FROM    primarytrafficcountdata
        WHERE   YEAR(date) <= 2014
        AND     YEAR(date) >= 2011
        GROUP BY
                section_id,
                direction
        ) groupeda
ON      (a.section_id = groupeda.section_id
AND      a.direction = groupeda.direction
AND      a.date = groupeda.maxdate)
OR      (a.section_id = groupeda.section_id
AND      a.direction IS NULL and groupeda.direction IS NULL
AND      a.date = groupeda.maxdate)
;

But this returns:

S_ID | DATE      | DIRECTION | AADT
1001 | 9/16/2013 | North     | 700
1001 | 9/16/2013 | South     | 600
1001 | 1/30/2011 | NULL      | 1093
1211 | 1/17/2012 | NULL      | 945
1235 | 1/23/2014 | North     | 500
1235 | 6/19/2014 | South     | 350
1301 | 9/16/2013 | East      | 700
1301 | 4/16/2012 | West      | 600
1301 | 1/30/2014 | NULL      | 1093

This is the most recent dates, which I want, but I want to eliminate the North/South or East/West directions and only use the NULL directions when the NULL direction is more recent or vice-versa when the North/South or East/West are the most recent records.

Does anyone know how I would do this?

I am using Microsoft Access 2010.

Try this:

SELECT  a.* INTO recent_data
FROM primarytrafficcountdata a
 INNER JOIN (SELECT section_id,MAX(date) AS maxdate
              FROM primarytrafficcountdata
              WHERE YEAR(date) <= 2014 AND YEAR(date) >= 2011
             GROUP BY section_id) groupeda
 ON (a.section_id = groupeda.section_id AND a.date = groupeda.maxdate);


Query for row combine BEGIN
    declare @t1 table(
date1 date,
EmpID int,
bnumber varchar(24),
Name varchar(50),
Depname varchar(50),
mintime time,
maxtime time,
Status1 varchar(50))

insert into @t1 exec dbo.InOut2 @fromDate=@fdate,@toDate=@tdate

select distinct EmpID,
case when Status1='Present' then count (date1) end as 'Present',
case when Status1='Late-Absent' then count(date1) end as 'Late-Absent',
case when Status1='Absent'then count(date1)end as 'Absent' from  @t1
where date1 between @fdate and @tdate
group by EmpID, Status1

my output is like this

empid present late-absent  absent
124   null     null        3
124   null      5          null

but I want to see the output like these

empid  present  late-absent   absent
124     null          5           3

EDITS: - jcolebrand (brought in "answers" as edits with verbiage cleanup)

Instead of case what can I use to do this:

max(case when Status1='Present' then count (date1) end) as 'Present' ,

When I use this I get:

  Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

The result is still same I do not get my expected result, it's still

empid   present   late-absent    absent
124      0          0              3
124      0          5              0

You can't use COUNT inside another aggregate. What you're trying to do is add 1 for each row that meets the criteria, so use the CASE to return 1 or 0 depending of whether the criteria is met or not. Then aggregate the results of that CASE with a SUM:

SELECT EmpID,
SUM((CASE WHEN Status1='Present' THEN 1 ELSE 0 END)) end as 'Present',
SUM((CASE WHEN Status1='Late-Absent' THEN 1 ELSE 0 END)) end as 'Late-Absent',
SUM((CASE WHEN Status1='Absent' THEN 1 end ELSE 0 END)) as 'Absent' from  @t1
where date1 between @fdate and @tdate
GROUP BY EmpID

I also removed Status1 from your aggregate, because it would create a separate row for each occurrence of employee and Status1 - what you want is one row per employee only.


Split field values using specific character Please help me with this.

It is possible to split field values using a specific character?
Here is my sample table

value
10uF
2K
1.0uF
200UF

I want it to split by this:

value       capacitance/resistance
 10            uF
 2             K
 1.0           uF
 200           UF

PS: I am using Oracle and the numbers should be separated from letters

Using regular expressions is an option:

create table capacity (inputdata varchar2(30));

insert into capacity(inputdata) values('10uF');
insert into capacity(inputdata) values('2K');
insert into capacity(inputdata) values('1.0uF');
insert into capacity(inputdata) values('200UF');

commit;

select regexp_substr(inputdata,'^[0123456789.]*') value,
regexp_replace(inputdata,'^[0123456789.]*','') unit
from capacity
;

here is this example on sqlfiddle
Maybe you should improve the regular expression pattern of the number format

Regular expression functions are described here in the manual   and on the following pages


Using Central Management Server to provide convincing performance statistics Central Management Server

The company I work offers dedicated VMware hosted servers to its Windows clients. Among those Windows clients, there are essentially 60 single instance SQL Servers ranging in versions from 2000 to 2008. Among them you will find 40 Express editions, 10 Standard editions and several MSDE and workgroup editions.

Via a shiny new SQL Server 2008 R2 Standard edition configured as the Central Management Server, I can access every one of these remote machines simultaneously.  Each server is registered in a group based on its version, 8, 9 or 10.  This means that I can query each group of servers based on their version to avoid compatibility errors.

The problem

Normally, we can only sell the idea of increasing RAM, CPUs or moving SQL Server to a dedicated machine because the customer asked us to do so for N reasons.

The goal

I would like to use Central Management Server to go one step further by going to our customers and saying, if you did "this" then your website or application would be faster times X. Imagine an ASP web page that takes 3 seconds to load, by increasing the RAM, in other words by what the buffer pool really needs, that same page would load in 1 second.

I found this line of T-SQL code from Brent Ozar's Take Over an Unknown Server

    SELECT  @@SERVERNAME AS ServerName ,
            YEAR(backup_finish_date) AS backup_year ,
            MONTH(backup_finish_date) AS backup_month ,
            CAST(AVG(( backup_size / ( DATEDIFF(ss, bset.backup_start_date,
                                                bset.backup_finish_date) )
                       / 1048576 )) AS INT) AS throughput_mb_sec_avg ,
            CAST(MIN(( backup_size / ( DATEDIFF(ss, bset.backup_start_date,
                                                bset.backup_finish_date) )
                       / 1048576 )) AS INT) AS throughput_mb_sec_min ,
            CAST(MAX(( backup_size / ( DATEDIFF(ss, bset.backup_start_date,
                                                bset.backup_finish_date) )
                       / 1048576 )) AS INT) AS throughput_mb_sec_max
    FROM    msdb.dbo.backupset bset
    WHERE   bset.type = 'D' /* full backups only */
            AND bset.backup_size > 5368709120 /* 5GB or larger */
            AND DATEDIFF(ss, bset.backup_start_date, bset.backup_finish_date) > 1 /* backups lasting over a second */
    GROUP BY YEAR(backup_finish_date) ,
            MONTH(backup_finish_date)
    ORDER BY @@SERVERNAME ,
            YEAR(backup_finish_date) DESC ,
            MONTH(backup_finish_date) DESC
GO

This provides the kind of information I'm looking for.

Questions

The queries I've been using show general statistics about things like differences between the target and total memory, basic wait stats and surface area issues.  That's great if you're in IT, but not very helpful or convincing for the guy who only cares about his web page or report opening up 2 seconds faster.

How can I, using Central Management Server, provide convincing performance related information for the individual servers managed with it?
After finding interesting servers thanks to CMS, what 3rd party software do you recommend that is capable of providing decent performance graphs/charts without having to add anything to the client server or modify it in any way. Keep in mind, these servers always belong to someone else.  We only have the right to monitor.
Question about security.  I am using a windows login called dba who only has VIEW SERVER STATE for SQL Server 2005 and 2008.  Is that safe?  For SQL Server 2000, the user is simply able to log into SQL Server.  Should I restrict his access in any way?

How can I, only using Central Management Server, provide convincing
  performance related information for the individual servers managed
  with it?

Unless I've missed a key feature somewhere along the line, you can't. What does it give you?

Logical organisation of your servers and instances.
Policy Based Management can be applied to server groups.
Multi-server queries.

For performance data you'll want to look at installing the Management Data Warehouse alongside your Central Management Server. It isn't as useful as the various 3rd party tools for collecting SQL performance data but it's an improvement on roll-your-own Perfmon/SSRS/Excel solutions.

  I would like to use Central Management Server to go one step further
  by going to our customers and saying, if you did "this" then your
  website or application would be faster times X.

If only it were that simple. You're going to be on shaky ground if you offer this sort of advice to customers, especially if you're sending them a bill for the proposed upgrades. You can certainly offer some insight but not guarantees:

ServerX has a low page life expectancy, it would benefit from additional RAM.
ServerY has increased from an average 50% to 70% CPU in the past month. Has anything changed or have you seen an increase in visitors/customers? Perhaps you should plan for an upgrade.
ServerZ used an average 500IOPS at the start of the year, it's now reached 750IOPS.


Import Excel Table to SQL Server - with date column I'm trying to import a excel table that has a column with dates. My query works without the column date. I'm using ODBC to import.

strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & ActiveWorkbook.FullName & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
cn.Open strCon
cn.Execute "INSERT INTO [ODBC;DRIVER=SQL Server;SERVER=MYSERVEREXPRESS;Trusted_Connection=Yes;DATABASE=TESTDB].[tbTableTest1] ([opDate], [columnFloat], [columnText]) SELECT t.MY_DATE, t.COL1, t.COL2 FROM [BD$] t"

My table:

MY_DATE     COL1    COL2
11/26/2012  21,43   MAR
11/26/2012  21,45   GFM

What am i doing wrong?

Internally, Excel stores dates as an integer value. I've run into this before and solved it both in SSIS and straight TSQL

https://stackoverflow.com/questions/9085928/reading-in-date-column-if-first-row-isnt-a-date-with-ssis-excel-data-source/9086831#9086831

In your case, you might need to make a computed column that actually applies the formula, assuming you don't have to worry about the 1900 date.

-- code approximate as I'm late for a meeting
ALTER TABLE tbTableTest1
ADD opActualDate AS DATEADD(day, opDate - 1,CAST('1899-12-31' AS datetime))


How to query in such a way that values becomes columns? Having the table foo like:

| id | name |
|  1 | Foo1 |
|  2 | Foo2 |

And the table bar like:

| id | foo_id | key | value |
|  1 |      1 |   a | foo1a |
|  2 |      1 |   b | foo1b |
|  3 |      2 |   a | foo2a |
|  4 |      2 |   b | foo2b |

How can I mount my SQL in a way that produces an output like:

| id | name |   a   |   b   |
|  1 | foo1 | foo1a | foo1b |
|  2 | foo2 | foo2a | foo2b |

I have no idea even if it is possible.

On a high-level problem (maybe there is a better solution) I want to make the keys on a entity dynamic.

I think what you are looking for is called a Pivot.  I work primarily with Microsoft SQL Server, so I don't know all the inter-workings of this.

There appears to be a really well received Stack Overflow post that answers a similar question for MySQL.

https://stackoverflow.com/questions/7674786/mysql-pivot-table

Hopefully that give you what you are looking for.


MySQL InnoDB does not return the last row on select with DESC order We have a table scheme like following:

CREATE TABLE IF NOT EXISTS `offers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `campaign_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `price` double NOT NULL,
  `ip` varchar(15) NOT NULL,
  `cdate` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `campaign_id` (`campaign_id`,`price`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin5 AUTO_INCREMENT=190514 ;

On each new offer by the user we check if the last order is given by the same user with:

"select user_id from offers where campaign_id='".$campaign['id']."' order by id desc limit 1"

if the user_id is same we prevent the new offer to protect the user from accidental double clicks.

if there is not any problem with the offer we insert the offer with:

"insert into offers(campaign_id,user_id,price,ip,cdate) values (".$campaign['id'].",".$user['id'].",'".$price."','".$_SERVER['REMOTE_ADDR']."',".time().")"

But the problem is the select only returns the last inserted row after about 1 second. That means the user can insert multiple offers if they click the button too fast.

We use 5.5.30-30.2-log Percona Server as our database server.
Following is our my.cnf file:

[mysqld]
datadir                         = /var/lib/mysql
tmpdir                          = /var/lib/mysqltmp
socket                          = /var/lib/mysql/mysql.sock
skip-external-locking           = 1
skip-name-resolve
open-files-limit                = 40000
max_heap_table_size             = 64M
tmp_table_size                  = 64M
log-error                       = /var/log/mysqld.log
thread-cache-size               = 50
table-cache                     = 4096
table-open-cache                = 4096
table-definition-cache          = 512
query-cache-size                = 0
query-cache-limit               = 16M
query-cache-type                = 0
sort-buffer-size                = 1M
read-buffer-size                = 1M
read-rnd-buffer-size            = 8M
join-buffer-size                = 1M
tmp-table-size                  = 64M
max-heap-table-size             = 64M
back-log                        = 100
max-connections                 = 10000
max-connect-errors              = 10000
max-allowed-packet              = 256M
interactive-timeout             = 360
wait-timeout                    = 360
innodb                          = FORCE
key-buffer-size                 = 32M
myisam-sort-buffer-size         = 4M
innodb-buffer-pool-size         = 60G
innodb-log-file-size            = 256M
innodb_log_files_in_group       = 2
innodb-log-buffer-size          = 4M
innodb-file-per-table          = 1
innodb-thread-concurrency       = 8
innodb-flush-log-at-trx-commit  =2
server-id                       = 1
slow-query-log                  = 1
slow-query-log-file             = /var/lib/mysqllogs/slow-log

Transaction isolation levels and database locking can make it very difficult to handle the problem you've described.

Hopefully ypercube's suggestion does the trick.

If it's valid to have multiple (campaign_id, user_id) combinations, just not within seconds of each other, you might want to consider adding a column with the user's web/application session id, and putting a unique index on (campaign_id, user_id, session_id).

This would prevent a user from being able to enter the same data during a single login session - whether that's useful or not really depends on your application. You might be able to come up with some other unique index that serves your purposes.

It might also be worth trying to prevent data being submitted twice from the client side. You'll see that sort of behaviour all through StackExchange, for example - the interface changes in such a way that you can't actually hit the same button twice.


Oracle's (+) notation within function If we use (+) notation we can have outer join. For example:

select *
from tab_a, tab_b
where tab_a.num = tab_b.num(+)

Is outer join.
If we use function around columns, for example:

select *
from tab_a, tab_b
where round(tab_a.num, 2) = round(tab_b.num(+), 2)

Is it still outer join? Or is it becomes inner join?

Is it still outer join? Or is it becomes inner join?

It is still an outer join:

SQL Fiddle

Oracle 11g R2 Schema Setup:

create table tab_a(num integer);
create table tab_b(num integer);

insert into tab_a(num) values(1);
insert into tab_a(num) values(2);

insert into tab_b(num) values(2);
insert into tab_b(num) values(3);

Query 1:

select *
from tab_a, tab_b
where tab_a.num = tab_b.num(+)

Results:

| NUM |    NUM |
|-----|--------|
|   2 |      2 |
|   1 | (null) |

Query 2:

select *
from tab_a, tab_b
where round(tab_a.num, 2) = round(tab_b.num(+), 2)

Results:

| NUM |    NUM |
|-----|--------|
|   2 |      2 |
|   1 | (null) |


How MySQL use the InnoDB Buffer? For example, when using mysqltuner.pl, it told me to increase the InnoDB Buffer Size to > 5GB as my data is 5.6GB

Does it mean: if I allocate enough for the InnoDB Buffer, data will be loaded from the memory instead of disk, and will greatly improve QPS for my database?

Short Answer:  Rule of Thumb:  If you have more than 4GB of RAM, then set innodb_buffer_pool_size to about 70% of available RAM.  Set and forget.

Long Answer:

"Greatly improve QPS?" -- It depends.

The buffer_pool is a "cache", maintained roughly as "least-recently-used".

If your data + indexes are bigger than the buffer_pool, there may be some slowdown.  Either live with the speed or get more RAM.

I have seen terabyte datasets run just fine on 32GB servers.  It depends on the "working set".  If the application only rummages through a portion of the rows (eg, "recent" rows), then a small buffer_pool works just as good as a big one.

If you have UUIDs, you are hosed.  These lead to random access that leads to slow-due-to-IO versus buy-more-RAM.

mysqltuner has some good points and some bad points.  Don't increase your buffer_pool to "big enough" unless you have 8GB or more of RAM.  Swapping is much worse for performance than shrinking the buffer_pool.

With a big enough buffer_pool, no it won't automatically load everything.  As a "cache", things (16KB blocks) are loaded as needed, then left there.  If it is not big enough and your "working set size" is big, those blocks will be bumped out to make room for other blocks.  Automatically.  The only cost is speed.


Delete rows from a large table I have a very large table and I must delete big amount of data from it until some point.

The table has only one non-clustered index on a column which is not relevant to use in my delete query. The first idea that comes to me is to include to the index a unique column which I could use in my delete query.

My question is, do you think it will be a good option or something better can be done?

You should do 2 things for efficiently deleting the data from your big table :

Delete in batches and/or do careful batching
Watch out your Transaction log size - if db in simple recovery mode - perform frequent CHECKPOINTS or if db is full recovery mode - perform frequent log backups.


Referencing a Join table I have a relationship that I am trying to represent in a database.  Imagine I have series of many to many relationships...

Say student to proffesor, and professor to classes...

A student can many professors and professors can obviously have many students.  A student also has many classes, each taught by a different or similar professor, and professors can teach many classes.

To relate those many to many relationships to each other, you obviously need a join table between each.

But, if you relate a student to a professor, you are automatically linking him to all the classes that he teaches.  Which doesn't necessarily have to be the case.  You could take one class with one professor, and have another class with another professor, or you could have two classes with one professor and another with another professor.

Obviously you could just link the student directly to the classes and then link the professors to the classes.  But Imagine, for the sake of this question, that for some reason, you can only link students to classes.  Imagine this is a legacy database for instance.

Would it be good form to instead of linking directly to the professor, link instead a link-table of the professor... So I would have a link table to a link table of say Prof ID 2, class ID 3... That would have a link id of lets say 2.  I would then have a link table with student 3 linked to Prof->Class ID 3.  So that student is now linked to professor ID 2 and class ID 3.  Think of that relationship like this...

Is that a good way to represent that relationship, or is there a better way to do this?

To model the classical student - class - prof situation, you need to reverse your model. class can implement the n:m relationship between student and prof. To be precise:

[student] m -- n [class] n -- 1 [prof]

The n:m relationship between student and class would be implemented by another table. Like [participant]:

[student] 1 -- n [participant] n -- 1 [class] n -- 1 [prof]

A prof has many classes. A class has many participants. A student is participant in many classes. Student and prof are only linked by participation in classes in this model.

Generally, just because a student is linked to a professor in some way does not mean the student participates in all classes of the professor. That's an unwarranted over-interpretation. What a relationship between two entities actually means is defined by additional meta-data, usually indicated by the column names used.


Why am I able to create MV logs using PK — even though there is no PK? I have a table that does not have a primary key:

I'm able to create MV logs on the table — without specifying whether to use the primary key or the rowid:
create materialized view log on maximo.workorder;

It looks like Oracle has chosen to create the MV logs using the primary key option (even though the table doesn't have a primary key):

I've tested the MV logs and they are working correctly. Any edits that I make to the WORKORDER table  are showing up in the MV logs.

Why is it possible for Oracle to create the MV logs using the primary key option — even though the table doesn't have a primary key?

Edit:
Related question here: Create fast-refresh MV over dblink on table without PK?

A unique constraint on a NOT NULL column (which you have, as I checked this table in a database where Maximo was installed) is a good enough substitute for a primary key in this case.
SQL> create table t1(c1 number, c2 number);

Table created.

SQL> create materialized view log on t1;
create materialized view log on t1
*
ERROR at line 1:
ORA-12014: table 'T1' does not contain a primary key constraint

SQL> alter table t1 add unique(c1);

Table altered.

SQL> create materialized view log on t1;
create materialized view log on t1
*
ERROR at line 1:
ORA-12014: table 'T1' does not contain a primary key constraint

SQL> alter table t1 modify c1 not null;

Table altered.

SQL> create materialized view log on t1;

Materialized view log created.

SQL> select master, primary_key from user_mview_logs;

MASTER                         PRI
------------------------------ ---
T1                             YES


What does it mean if the spt_monitor - table in SQL Server is empty? What does it mean for my environment if I find that the spt_monitor table in SQL Server is empty?

What is this table, how is it used and what populates it?

I am using SQL Server 2005.

My spt_monitor - table is not empty.

I just asked if it were empty....?

To answer your specific question: "What would it tell me if this table were empty?"  - It would tell you that you or some process somehow truncated or deleted this table. It should always have a row, and when you run the sp_monitor system stored procedure this row gets overwritten with a new row.

More Information on spt_monitor and sp_monitor

spt_monitor is a system table stored in the master database. This table gets populated for the first time when you instal SQL Server. It contains various information about some system resources utliization that are tracked inside of SQL Server (described below).

The purpose of this table is to provide a way for you to compare some of these metrics over time. The table will only ever have one row. The work of giving the differences is done in the stored procedure described below -

sp_monitor is a system stored procedure stored procedure in the master database. This stored procedure reads the current row in spt_monitor into a temporary table and then inserts a new row and compares the difference between all of the various counters tracked (including the lastrun column to give an elapsed time.

This stored procedure then gives the current value for each counter as well as the difference in the time between executes in parenthesis.

I've included a quick sample output from my one of my virtual machines. This is activity on a mostly sleeping VM for a 46 second timeframe. To explain the counters, look at the packets_received column. There have been a total of 777 packets received on this instance since it was last restarted according to the @@pack_received system function, and in the past 46 seconds there has been on received.

Sample Output:

sp_monitor
------------------------*/
last_run                current_run             seconds
----------------------- ----------------------- -----------
2012-12-15 22:39:56.770 2012-12-15 22:40:42.500 46

cpu_busy                  io_busy                   idle
------------------------- ------------------------- -------------------------
5(0)-0%                   3(0)-0%                   12366(44)-95%

packets_received         packets_sent             packet_errors
------------------------ ------------------------ ------------------------
777(1)                   627(1)                   0(0)

total_read          total_write         total_errors        connections
------------------- ------------------- ------------------- ------------------
723(0)              153(1)              0(0)                275(0)

What Do You Do if You Find The Table Empty?

Again, I am pretty sure that an empty table here is an indication that you, or some monitoring tool or process you are running emptied the table. Or possibly that there was an issue with a portion of the installation. If you find this table empty, there should be a file in your SQLRoot\MSSQL\Install directory called u_tables.sql This file creates some system tables and populates them. If you search for spt_monitor you'll see the create and insert script for this table. The reelinsert script (in SQL Server 2012 RTM) looks like this:

insert into spt_monitor
    select
    lastrun = getdate(),
    cpu_busy = @@cpu_busy,
    io_busy = @@io_busy,
    idle = @@idle,
    pack_received = @@pack_received,
    pack_sent = @@pack_sent,
    connections = @@connections,
    pack_errors = @@packet_errors,
    total_read = @@total_read,
    total_write = @@total_write,
    total_errors = @@total_errors
go

A Final Word

I'm not sure why you are using this table. I would suggest that you read up on each of the system functions above in books online. Some of them have notes of warning about various conditions where they can return incorrect data. For instance the note for [@@cpu_busy][1] talks about the data type and a warning that if it accumulates more than 49 days of time, it is no longer accurate or valid. Same with some of the other counters.

I have actually never run sp_monitor on an instance of mine. It wasn't until this question that I dug deeper and learned about what it is and what it does. It looks interesting, it provides data on a host of counters (you can learn about each of those functions above here) that look to provide some useful data at first blush.

But then when you look deeper, most of the information grabbed is probably best collected in other ways. For most of the information,  I would look at a combination of perfmon counters, DMVs and virtual file stats. The total_errors counter is interesting but I can't really think of a useful case for the information presented that way. I want to get my disk write errors  (total_errors) and alerts as they happen and not worry about a cumulative count so much. Looking at total connections since a restart could be interesting and if you didn't want to track this information through perfmon or repeated queries at DMVs and adding it, that could be helpful.