why would i need to create an index when writing a query in t-sql?
951907
You would need to create an index if the query you are running was taking to long to run.  By creating an index you can greatly improve the responsiveness of queries.\n\nCreating the index is a one time thing, it is not something you have to do each time the t-sql is executed.\n\nIf you are using dynamic T-SQL then it may be very difficult to know how to construct the indexes since your T-SQL will change every time, sometimes just a little, but it could be significant.
1122479
Firstly off 'now()' isn't a function call, it's a string therefore you CANNOT use it for a default value.\n\nYou have to insert a value upon insertion to that table withe the now() string.\n\nLook Carefully about the datatypes of different Time Structures\n==============================\n\n--\n-- Lets use the test Database\n--\nmysql> use test;\nDatabase changed\n\n--\n-- Lets create a Table (temp for this example)\n--\nmysql> create table a (\n    -> x date,\n    -> y timestamp,\n    -> z datetime);\nQuery OK, 0 rows affected (0.02 sec)\n\n--\n-- Lets insert only at column x so rest are default\n--\nmysql> insert into a(x) values(now());\nQuery OK, 1 row affected (0.00 sec)\n\n--\n-- Lets insert only at column y so rest are default\n--\nmysql> insert into a(y) values(now());\nQuery OK, 1 row affected (0.00 sec)\n\n--\n-- Lets insert only at column z so rest are default\n--\nmysql> insert into a(z) values(now());\nQuery OK, 1 row affected (0.00 sec)\n\n--\n-- Lets Select everything in that table\n--\nmysql> select * from a;\n+------------+---------------------+---------------------+\n| x          | y                   | z                   |\n+------------+---------------------+---------------------+\n| 2006-06-15 | 2006-06-15 00:42:17 | NULL                |\n| NULL       | 2006-06-15 00:42:23 | NULL                |\n| NULL       | 2006-06-15 00:42:26 | 2006-06-15 00:42:26 |\n+------------+---------------------+---------------------+\n3 rows in set (0.00 sec)\n==============================\n\nNow notice what happened... TIMESTAMP by default updates the current time...,  The others are null. So change your datatype to be TIMESTAMP if you care so much about the default value.
1044851
One question: when you type "2" in the field t4f3, what do you want to lookup in table 2? Fields? Records? All fields? All records? I believe you're not designing the right way your database. Please be more specific.\n\nMakiavel
601078
You need to create an extremely large varchar variable and then you need to cursor thru your table and find all the Note Times that are 5/2/2006 or whatever note time you want them to be. Then you need to append the note time and the note to the end of your varchar variable.\n\nGoogle Cursors in SQL to see how cursoring thru a table is done.\n\nNOTE: As a software developer I cannot in my professional capacity condone the use of cursors. Personally I think they are a huge waste of performance.
254673
The best way to learn SQL is by using it. The four basic things you need to know are Select, Insert, Update, and Delete. \n\nSELECT [columns to select] FROM [table] [where] [order]\n\nINSERT INTO [table] ([names of fields to insert]) VALUES ([values to insert into respective fields])\n\nUPDATE [table] SET [field name 1]=[value 1], [field name 1]=[value 1] [where]\n\nDELETE FROM [table] [where]\n\n\n[table]-> the table you want to modify\n[field or column name]-> the column in the table you want to modify\n[where]-> tell which rows of the table to modify. Usually looks like "WHERE ID=1" or "WHERE ID like '%1%'" or "WHERE ID IN (1,2,3,4,5)"\n[order]-> the order by clause tells how the values should be ordered. "ORDER BY Create_Date" or "ORDER BY Create_Date DESC"\n\nThat should get you started. Then look at Joins, nesting, etc. Good luck!!
892672
It's very easy if you build a stored procedure to get the output like that .\nStored Procedure is created to handle all complex query and the most impossible query, so always find the alternative in Stored Procedure if you ever get stuck with your query.\n\nin stored procedure :\n- make a loop query : 'Select distinct ID1, ID2 from myTable'\n- and in the loop, make query like : 'Select GUID from myTable where ID1=:ID1 and ID2=:ID2 order by GUID rows 1' \n\nthis stored procedure is for Interbase/firebird structure :\nCREATE PROCEDURE WEIRD_QUERY\nReturns\n( \n ID1 integer,\n ID2 integer,\n GUID varchar(30)\n)\nas\nbegin\n  For\n  Select distinct ID1, ID2 from myTable into ID1, ID2 \n    do begin\n       Select GUID from myTable\n       where ID1=:ID1 and ID2=:ID2 order by GUID rows 1\n       into GUID \n       end\n   suspend;\nend;\n\nNotes :\n- Rows 1 is to limit our query result,  in mySql 'rows' is 'Limit' \n- suspend is a loop query\n\ntry to convert my stored procedure structure to your Microsoft Sql Server structure\nor\nyou can keep searching the way to build your query without using stored procedure.\n\nsorry, this is the best i can help.
63777
hi\n\nactually according  to core SQL Query syntax "[]" is should be use for column and table name. because both table ane column name can have space. for ex table name can be "doctor master" , but if u have table or column name without space u can omit "[]"\n\nSo both syntax are correct and  same
527678
Both will work perfectly...\n\nIn case one, you make sure that INSERT INTO's are done right away, in case two, you let let the SQL server to decide when to do the actual insertion.\n\nIn case one, INSERTing the second record is not held before the first one finishes; that's the only difference.\n\nCASE 1:\nINSERT INTO: Handle the query and check what to do.\nCOMMIT: Insert (the actual disk action).\nINSERT INTO: Handle the query and check what to do.\nCOMMIT: Insert (the actual disk ...)\n... goes on like this.\n\nCASE 2:\nINSERT INTO: Handle the query and check what to do.\n#2: Handle the next query and check what to do\n#3: Handle ...\n...\nCOMMIT: Actually INSERT (do the disk operation).
777696
Well, that's one of the first questions i asked when i worked with value sets in oracle applications.. The answer is no way.. you cannot make a distinct select out of value set..\nThere is a work arround though, you can create a veiw with the required data, and then add the distinct keyword to it.. When you create the value set, choose this view and add it's columns and conditions if any.. I'll be glad to go further with that if needed\n\n\nThanks
852284
It is sufficient, but it is not optimal.  Calculations of distance (for instance) in a GIS require formulas to be written within the SQL.  A QL that has these calculations supplied is better.
455478
Create a query and paste this into it's SQL sheet:\n\nSELECT tblData.TICKER, tblData.DATE, tblData.CLOSE, (SELECT Sum([CLOSE])/Count([ticker]) AS Expr1 FROM tblData AS sub WHERE \n(((sub.TICKER)=[tblData]![Ticker]) AND ((sub.DATE) Between [tbldata]![date]-19 And [tbldata]![date]));) AS avg20 FROM tblData;\n\nIt will show the 20-day average for each stock for each date, but if there are fewer days of data available for a stock, it will show a real average for those days.\n\nI don't know what's causing the "..." in the middle of the SQL, but just replace it with "er]) "\n\nThis is what's called a Subquery
1134728
It's dreaded timestamp in SQL server. Though the name says it is timestamp, it's really not. So don't do a read/write on that column. It's a kind of rowid precisely. By definition from SQL server books:\n"\nThe SQL Server timestamp data type has nothing to do with times or dates. SQL Server timestamps are binary numbers that indicate the relative sequence in which data modifications took place in a database. The timestamp data type was originally implemented to support the SQL Server recovery algorithms.\n\nIt further states Never use timestamp columns in keys, especially primary keys, because the timestamp value changes every time the row is modified.\n"\nHope it helps.
168991
I am assuming you are the one asking the questions.  I have found that the biggest failing database developers have is not using the resources available for code samples. \n\nQ:  Where would you turn for help with creating a stored procedure or a select statement that was erroring out?
1100890
you should use an sql connection object from the component toolbar n make a connected architecture.. and then drag n drop an sql command object . in that object's property set type = stored procedure and then proceed. it's as simple as that in visual studio..\nin query builder u'll get the type as : create procedure <> as.... GO.\nthen in ur code for d form write:\nSqlCommand cmd = new SqlCommand (<proc.name>, <connection name>);\ncmd.CommandType=CommandType.StoredProcedure;\ncmd.Parameters.Add( new SqlParameter("@<pname>",<type>,value,<col name>));\ncmd.ExecuteNonQuery();\nhope thiswill work for u?
246287
The field names do not matter. It is the types that matter. For instance, you cannot copy a string (e.g. "abc") to a number field. Also, certain autogenerated fields should not be copied.\n\nI am not sure what you mean by dynamic, but the kind of query you would use looks something like this:\nINSERT INTO Table2 ( Field7, Field8, Field9 )\nSELECT Table1.Field1, Table1.Field2, Table1.Field3\nFROM Table1;
711292
This is really wide question. Especially which Database server are you using? Depending on all of them, you will have different techniques to do it. For example, I will answer for SQL Server since I work on them. Out of box, SQL Server provides Database Tuning Adviser (SQL 2005) and Index tuning wizard (SQL 2000) to help you tune indexes to make your stored procs or queries run faster. Some times you could rewrite the queries in different way, break them up etc to make them run faster. Few of the things I would list out here are web casts and articles which may help you get started on this. And you probably should buy some DBA handbook or tuning guides which may show you little tricks etc as well. The links I am putting here are largely for sQL Server again. You could find info for other database servers on their respective sites OR some other expert sites.
1054967
certain databases like mysql achieves this by using the dot operator "."\nsay db1 has table1(field1) and\ndb2 has table2(field2)then \n\nselect a.*,b.* from db1.table1 a,db2.table2 b where\na.field1 = b.field2