Sql Server Auto Generated Primary Key
Value generation patterns
Mar 12, 2012 No such thing as SQL 2003. There are many ways to generate artificial primary keys (keys that have no meaning within the data), there's also the option not to use an artificial primary keys but. For the previous few months, I found 3 to 4 questions related to Auto-incremented ID with the VARCHAR / NVARCAHAR data type in SQL Server. So I decided to write an article on that, it might help people who are looking for a solution of this. Problem statement SQL Server provides functionality called Auto Incremented column. How to create an alpha numeric autogenerated column into sql server table and make the same column primary key. How to merged column in same diffrence database table without any primary key How do I alter a column to include a primary key in VB.NET. Mar 12, 2012 No such thing as SQL 2003. There are many ways to generate artificial primary keys (keys that have no meaning within the data), there's also the option not to use an artificial primary keys but. ' I hope you have got the way to create sql server table with auto incremented primary key column and If you like my work; you can appreciate by leaving your comments, hitting Facebook like button, following on Google+, Twitter, Linked in and Pinterest, stumbling my posts on stumble upon and subscribing for receiving free updates directly to your inbox. You can define a primary key in SQL Server by using SQL Server Management Studio or Transact-SQL. Creating a primary key automatically creates a corresponding unique clustered index, or a nonclustered index if specified as such. Learn how to define an auto increment primary key in PostgreSQL. Get instructions on learning how to use the serial data type nd how to use a custom sequence.
There are three value generation patterns that can be used for properties:
- No value generation
- Value generated on add
- Value generated on add or update
No value generation
No value generation means that you will always supply a valid value to be saved to the database. This valid value must be assigned to new entities before they are added to the context.
Value generated on add
Value generated on add means that a value is generated for new entities.
Depending on the database provider being used, values may be generated client side by EF or in the database. If the value is generated by the database, then EF may assign a temporary value when you add the entity to the context. This temporary value will then be replaced by the database generated value during SaveChanges().
If you add an entity to the context that has a value assigned to the property, then EF will attempt to insert that value rather than generating a new one. A property is considered to have a value assigned if it is not assigned the CLR default value (null for string, 0 for int, Guid.Empty for Guid, etc.). For more information, see Explicit values for generated properties.
Warning
How the value is generated for added entities will depend on the database provider being used. Database providers may automatically setup value generation for some property types, but others may require you to manually setup how the value is generated.
For example, when using SQL Server, values will be automatically generated for GUID properties (using the SQL Server sequential GUID algorithm). However, if you specify that a DateTime property is generated on add, then you must setup a way for the values to be generated. One way to do this, is to configure a default value of GETDATE(), see Default Values.
Value generated on add or update
Value generated on add or update means that a new value is generated every time the record is saved (insert or update).
Like value generated on add, if you specify a value for the property on a newly added instance of an entity, that value will be inserted rather than a value being generated. It is also possible to set an explicit value when updating. For more information, see Explicit values for generated properties.
Warning
How the value is generated for added and updated entities will depend on the database provider being used. Database providers may automatically setup value generation for some property types, while others will require you to manually setup how the value is generated.
For example, when using SQL Server, byte[] properties that are set as generated on add or update and marked as concurrency tokens, will be setup with the rowversion data type - so that values will be generated in the database. However, if you specify that a DateTime property is generated on add or update, then you must setup a way for the values to be generated. One way to do this, is to configure a default value of GETDATE() (see Default Values) to generate values for new rows. You could then use a database trigger to generate values during updates (such as the following example trigger).
Value generated on add
By convention, non-composite primary keys of type short, int, long, or Guid are set up to have values generated for inserted entities, if a value isn't provided by the application. Your database provider typically takes care of the necessary configuration; for example, a numeric primary key in SQL Server is automatically set up to be an IDENTITY column.
You can configure any property to have its value generated for inserted entities as follows:
Warning
This just lets EF know that values are generated for added entities, it does not guarantee that EF will setup the actual mechanism to generate values. See Value generated on add section for more details.
Default values
On relational databases, a column can be configured with a default value; if a row is inserted without a value for that column, the default value will be used.
You can configure a default value on a property:
You can also specify a SQL fragment that is used to calculate the default value:
Specifying a default value will implicitly configure the property as value generated on add.
Value generated on add or update
Warning
This just lets EF know that values are generated for added or updated entities, it does not guarantee that EF will setup the actual mechanism to generate values. See Value generated on add or update section for more details.
Computed columns
On some relational databases, a column can be configured to have its value computed in the database, typically with an expression referring to other columns:
Note
In some cases the column's value is computed every time it is fetched (sometimes called virtual columns), and in others it is computed on every update of the row and stored (sometimes called stored or persisted columns). This varies across database providers.
No value generation
Disabling value generation on a property is typically necessary if a convention configures it for value generation. For example, if you have a primary key of type int, it will be implicitly set configured as value generated on add; you can disable this via the following:
I can get the primary key of last inserted raw using LAST_INSERT_ID() function.but I want to know when two pcs insert a record at same time what happened to result of above function?is there any possibilty to give an wrong result?it means one pc insert a record and before get the id 2nd pc insert a record at same time,and above function will gives 2nd records id?is it happen?
I think you could find your answer in this statement from the MySql documentation
The ID that was generated is maintained in the server on a per-connection basis. This means that the value returned by the function to a given client is the first AUTO_INCREMENT value generated for most recent statement affecting an AUTO_INCREMENT column by that client. This value cannot be affected by other clients, even if they generate AUTO_INCREMENT values of their own. This behavior ensures that each client can retrieve its own ID without concern for the activity of other clients, and without the need for locks or transactions.
No, the id returned to your connection is just yours
Default the year based on month value
sql,sql-server
SQL Server is correct in what it's doing as you are requesting an additional row to be returned which if ran now 2015-06-22 would return '2016' Your distinct only works on the first select you've done so these are your options: 1) Use cte's with distincts with subq1 (syear, eyear,..
Matplotlib: Plot the result of an SQL query
python,sql,matplotlib,plot
Take this for a starter code : import numpy as np import matplotlib.pyplot as plt from sqlalchemy import create_engine import _mssql fig = plt.figure() ax = fig.add_subplot(111) engine = create_engine('mssql+pymssql://**:****@127.0.0.1:1433/AffectV_Test') connection = engine.connect() result = connection.execute('SELECT Campaign_id, SUM(Count) AS Total_Count FROM Impressions GROUP BY Campaign_id') ## the data data =..
PHP: While loop not working after adjusting SELECT for SQL injection prevention
php,mysql,select,sql-injection,associative-array
You cannot bind column and table names, only data. You need to specify the table and then bind for your '%calendar weekday%'. $stmt = $conn->prepare('SELECT ' . $selectLang . ' FROM `TranslationsMain` WHERE `location` LIKE ? ORDER BY `sortOrder`, ' . $selectedLang); $stmt->bind_param('s', $calendar_weekday); ..
Trying to rewrite mysql_* to pdo
php,mysql,pdo
I don't know the source of the array $arr = array();, but it is assigned to null before the insert query. So it means, literally you are inserting nothing into the database. So check your array well, maybe it was to be like $arr = array('name'=>'My Name', 'url'=>'url', 'email'=>'my email',..
Convert AWK command to sqlite query
sql,awk,sqlite3
SQLite is an embedded database, i.e., it is designed to be used together with a 'real' programming language. It might be possible to import that log file into a database file, but the whole point of having a database is to store the data, which is neither a direct goal..
I Want to fetch SQL Records in MySQL of current Year
Try this: SELECT count(enq.`enquiryId`), Month(enq.`date`), Year(enq.`date`) FROM enquiry enq WHERE Year(enq.date)=somevalue --2015 for example GROUP BY MONTH(enq.`date`) ..
Pull information from SQL database and getting login errors
php,sql,database
change $username = 'rylshiel_order'; to $username = 'rylshiel_order'; and you should be through. You are passing on an extra single quote here. ..
concatenate field names in a mysql update with inner join
php,mysql
You can try as per below- UPDATE products pr INNER JOIN sub_categories sc ON sc.id = pr.sub_category SET slug = REPLACE(TRIM(LOWER(CONCAT(sc.subcat_name,'.',products.product_name))),' ', '-'); ..
Fastest way to add a grouping column which divides the result per 4 rows
sql,sql-server,tsql,sql-server-2012
Try this: SELECT col, (ROW_NUMBER() OVER (ORDER BY col) - 1) / 4 + 1 AS grp FROM mytable grp is equal to 1 for the first four rows, equal to 2 for the next four, equal to 3 for the next four, etc. Demo here Alternatively, the following can..
Can Rails deal with DB uniqueness without index?
mysql,ruby-on-rails,rdbms
Because there is no need for other ways. Under the hood it's all the same: when you define a UNIQUE constraint, a UNIQUE index is created on that table to enforce it. Question from DBA.SE: When should I use a unique constraint instead of a unique index? So for a..
T-SQL Ordering a Recursive Query - Parent/Child Structure
sql,tsql,recursion,order,hierarchy
The easiest way would be to pad the keys to a fixed length. e.g. 038,007 will be ordered before 038,012 But the padding length would have to be safe for the largest taskid. Although you could keep your path trimmed for readability and create an extra padded field for sorting..
Fatal error in if stament [duplicate]
php,mysql,mysqli
Just change the condition to: if(isset($_REQUEST['userid']) && $_REQUEST['userid'] > $user_hack) isset tells is a variable is set, while this statement may be true or false, on which you cannot call isset function. Until you check if(isset($_REQUEST['userid'])), you cannot assign it to $userid variable..
Retrieve Values As Column
mysql,sql
If types are fixed (just IMPRESSION and CLICK), you could use a query like this: SELECT headline, SUM(tracking_type='IMPRESSION') AS impressions, SUM(tracking_type='CLICK') AS clicks FROM tracking GROUP BY headline ..
MySQL substring match using regular expression; substring contain 'man' not 'woman'
mysql,regex
A variant of n-dru pattern since you don't need to describe all the string: SELECT '#hellowomanclothing' REGEXP '(^#. [^o] [^w]o)man'; Note: if a tag contains 'man' and 'woman' this pattern will return 1. If you don't want that Gordon Linoff solution is what you are looking for..
The column name “FirstName” specified in the PIVOT operator conflicts with the existing column name in the PIVOT argument
sql,sql-server,sql-server-2008
You could use CTE to define your null values and then pivot the data something like this: ;WITH t AS ( SELECT isnull(jan, 0) AS jan ,isnull(feb, 0) AS feb ,sum(data) AS amount FROM your_table --change this to match your table name GROUP BY jan,feb ) SELECT * FROM (..
Having two arrays in variable php
php,mysql,arrays,variables,multidimensional-array
The explode function is being used correctly, so your problem is further up. Either $data[$i] = mysql_result($result,$i,'data'); isn't returning the expected string '2015-06-04' from the database OR your function $data[$i] = data_eng_to_it_($data[$i]); isn't returning the expected string '04 June 2015' So test further up by echo / var_dump after both..
SQL varchar variable inserts question mark
sql,sql-server-2012
there is un recognizable character in your string that is giving that ?. Delete the value and retype. see my above screen shot..
Add 1 to datediff result if time pass 14:30 or 2:30 PM
sql,ms-access,ms-access-2007
Could be: SELECT reservations.customerid, DateDiff('d',reservations.checkin_date, Date()) + Abs(DateDiff('s', #14:30#, Time()) > 0)AS Due_nights FROM reservations ..
How to search images by name inside a folder?
php,mysql,image
This looks like a job for glob, which returns an array of file names matching a specified pattern. I'm aware of the other answer just posted, but let's provide an alternative to regex. According to the top comment on the docs page, what you could do is something like this:..
Query how often an event occurred at a given time
mysql,sql
This could be done using user defined variable which is faster as already mentioned in the previous answer. This needs creating incremental variable for each group depending on some ordering. And from the given data set its user and date. Here how you can achieve it select user, date, purchase_count..
SQL Customized search with special characters
sql,sql-server,sql-server-2008 https://newdollars359.weebly.com/blog/java-mac-106-download.
Here is my attempt using Jeff Moden's DelimitedSplit8k to split the comma-separated values. First, here is the splitter function (check the article for updates of the script): CREATE FUNCTION [dbo].[DelimitedSplit8K]( @pString VARCHAR(8000), @pDelimiter CHAR(1) ) RETURNS TABLE WITH SCHEMABINDING AS RETURN WITH E1(N) AS ( SELECT 1 UNION ALL SELECT..
Ignore Group if LIMIT is not reached in MySQL
You can use the having clause to filter out the groups you don't need, keeping only the groups where there are more than 4 dates: SELECT Fruits, SUM(Ordered), Date FROM table GROUP BY Date HAVING COUNT(Date) > 4 ..
C# - Can't connect to remote MySQL server
c#,mysql
When connecting to a MySQL-Database I always used the MySQL Connector you can get here: https://dev.mysql.com/downloads/connector/net/6.9.html You have to import the MySQL namespaces to your project and then you can use the MySQLConnection instead of the SQLConnection that is, as far as I know, only for MSSQL servers. http://www.codeproject.com/Tips/423233/How-to-Connect-to-MySQL-Using-Csharp..
MySQL - How can I know my query is tuned?
mysql,performance,explain
Except for trivial queries, there is no way to know if you have the optimal query & indexes. Nor can you get a metric for how well designed the schema and application are. 3 seconds on a cold system for a 3-way JOIN with 'Rows' of 409, 45, 1 is..
Title search in SQL With replacement of noice words [on hold]
sql,sql-server,sql-server-2008
I think you want something like this: DECLARE @nw TABLE ( sn INT, [key] VARCHAR(100) ) INSERT INTO @nw VALUES ( 1, 'and' ), ( 2, 'on' ), ( 3, 'of' ), ( 4, 'the' ), ( 5, 'view' ) DECLARE @s VARCHAR(100) = 'view This of is the Man';..
Notice: Array to string conversion in “path of php file” on line 64
php,mysql,arrays,oracle
Curly brackets are your friend when inserting variables into double quoted strings: $main_query=oci_parse($connection,'INSERT INTO ROTTAN(NAME,ROLLNO) VALUES('{$array[$rs][0]}','{$array[$rs][1]}')'); ..
Can someone explain to me how this statement is an exclude?
sql,sql-server,tsql
I can explain.. a query that's very close to yours. Let me alter it to: SELECT * FROM [table].[dbo].[one] AS t1 LEFT JOIN [table].[dbo].[one] AS t2 ON (t1.ColumnX = t2.ColumnX AND t2.columnY = 1) WHERE t2.tableID IS NULL This query retrieves all rows from t1, then checks to see if..
mysql_real_escape_string creates in server only not in local
php,sql
Your server has magic quotes enabled and your local server not. Remove it with the following sentence set_magic_quotes_runtime(0) As this function is deprecated and it will be deleted in PHP 7.0, I recommend you to change your php.ini with the following sentencies: magic_quotes_gpc = Off magic_quotes_runtime = Off If you..
MySQL Query returning strange values
php,mysql
You need to join by account_id and also question_id SELECT * FROM `quiz_questions` INNER JOIN `quiz_answers` ON `quiz_questions`.`account_id` = `quiz_answers`.`account_id` AND `quiz_questions`.`question_id` = `quiz_answers`.`question_id` WHERE `quiz_questions`.`account_id` = '1840979156127491' ORDER BY `quiz_questions`.`question_id` ASC LIMIT 5 ..
How to create multiple jquery form fields and insert in mysql database, without using mysql_real_escape_string
javascript,php,jquery,mysql
Your problem has nothing to do with jQuery and the form. It is just highly recommended to prevent SQL injection, an attack in which an attacker injects SQL commands into your DB Query by posting it in your form. That's why any data that comes from an untrusted source (eg..
C# MySQL Parameters.AddWithValue
c#,mysql
You try to add all your 52 parameter and their values with one AddWithValue method. You can't do that. First of all, you need to define all your parameters in your command with your column names like; command.CommandText = 'INSERT INTO tb_mitarbeiter (Vorname, id, projectnummber..) VALUES (?name, ?id, ?projektnummer..)'; Then..
How to call MySQL view in Struts2 or Hibernate
java,mysql,hibernate,java-ee,struts2
You can simply create an Entity, that's mapping the database view: @Entity public class CustInfo { private String custMobile; private String profession; private String companyName; private Double annualIncome; } Make sure you include an @Id in your view as well, if that's an updatable view. Then you can simply use..
Take thousand value in SQL
sql,sql-server
SELECT CONVERT(INT,YourColumn) % 1000 FROM dbo.YourTable ..
MySQL: Select several rows based on several keys on a given column
mysql,sql,database
If you are looking to find the records matching with both the criteria here is a way of doing it select `item_id` FROM `item_meta` where ( `meta_key` = 'category' and `meta_value` = 'Bungalow' ) or ( `meta_key` = 'location' AND `meta_value` = 'Lagos' ) group by `item_id` having count(*)=2 ..
SQL: overcoming no ORDER BY in nested query
sql,sqlite
Use a join instead: SELECT a, b FROM t JOIN (SELECT DISTINCT date FROM t ORDER BY date DESC LIMIT 2) tt on t.date = tt.date; ..
How to program a recurring billing/invoice system using PHP and MySQL
php,mysql
Cron sounds good. (Also it is worth to mention the MySQL Event Scheduler, but again I would go for a Cronjob) A copy would be something like this SQLFIDDLE: create table t ( id int, d date ); insert into t values( 0, CURDATE() ); insert into t values( 2,..
PHP / MySQLi: How to prevent SQL injection on INSERT (code partially working)
php,mysql,mysqli,sql-injection,sql-insert
In the New PHP code snippet, you are still vulnerable to injections. You are using a prepared statement in the insert part, but you are not actually using the preparations strengths correctly. When creating a prepared statement, you create a query in which you add placeholders instead of the raw..
Sql Server Auto Generated Primary Keyboard
How to select next row after select in SQL Server?
sql,sql-server
The query can be written as: ; WITH Base AS ( SELECT *, ROW_NUMBER() OVER (ORDER BY Shift_Date) RN FROM #Table1 ) , WithC AS ( SELECT * FROM Base WHERE Shift2 = 'C' ) SELECT * FROM WithC UNION SELECT WithCNext.* FROM WithC C LEFT JOIN Base WithCNext ON..
Get unique row by single column where duplicates exist
sql,sql-server
SELECT MIN(date),thread_id FROM messages GROUP BY thread_id HAVING COUNT(thread_id) > 1 ..
How do I display my mysql table column headers in my php/html output?
php,html,mysql,table,data
Note: You can just make a single file out of it to achieve your wanted output Use mysql_real_escape_string() to sanitize the passed-on value to prevent SQL injections You should use mysqli_* instead of the deprecated mysql_* API Form them in a single file like this (display.php): <html> <form method='post' name='display'..
If I export my database with phpmyadmin will it lock my tables or take my database down?
mysql,database,phpmyadmin
The answer is no, tables won't be locked, database won't be down. But, if your database is large and it takes long time to backup it, you can sometimes expect performance degradation(slow SQL queries from your application).
SQL Multiple LIKE Statements
sql,sql-server,tsql,variables,like
WITH CTE AS ( SELECT VALUE FROM ( VALUES ('B79'), ('BB1'), ('BB10'), ('BB11'), ('BB12'), ('BB18'), ('BB2'), ('BB3'), ('BB4'), ('BB5'), ('BB6'), ('BB8'), ('BB9'), ('BB94'), ('BD1'), ('BD10'), ('BD11'), ('BD12'), ('BD13'), ('BD14'), ('BD15'), ('BD16'), ('BD17'), ('BD18'), ('BD19'), ('BD2'), ('BD20'), ('BD21'), ('BD22'), ('BD3'), ('BD4'), ('BD5'), ('BD6') ) V(VALUE) ) SELECT * FROM tbl_ClientFile..
Php Mysql Query not working properly
php,mysql
No need to use union as it will give a lots of duplicate data What you want to achieve can be done with simple left join or inner join SELECT m.issue_name ,m.issue_type , m.priority ,m.status,m.description , m.start_date,m.end_date,m.duration, s.name as server_name,p.name as product_name from mod_networkstatus as m LEFT JOIN tblservers as..
compare today's date with unix timestamp value in database
php,mysql
If I understand correctly you have a unix timestamp in a varchar field and you can't change this. If you compare the unix timestamp directly you will only get results that match the exact second of the timestamp. You can use FROM_UNIXTIME() to convert the timestamp in a date value..
Temporarily Number Rows to Group by in MYSQL
You can do this using variables: select s.*, floor( ((@rn := @rn + 1) - 1) / 5) as group_number from sample s cross join (select @rn := 0) params order by date; ..
Foreign key in C#
c#,sql,sql-server,database
You want create relationship in two table Refer this link http://www.c-sharpcorner.com/Blogs/5608/create-a-relationship-between-two-dataset-tables.aspx..
oracle sql error Case When Then Else
sql,oracle,oracle11g
Sql Server Auto Generated Primary Key Data
Perhaps this is what you want? If there are rows in SecondTable, then do the second EXISTS: SELECT * FROM FirstTable WHERE RowProcessed = 'N' AND (NOT EXISTS (SELECT 1 from SecondTable) OR EXISTS (SELECT 1 FROM SecondTable WHERE FirstTable.Key = SecondTable.Key and SecondTable.RowProcessed = 'Y')) AND OtherConditions ..